MySQL-主从复制
一、MySQL复制介绍
1.1、主从复制简介
1）MySQL复制允许将主实例(master)上的数据同步到一个或多个从实例(slave)上，默认情况下复制是异步进行的，从库也不需要一直连接到主库来同步数据
2）MySQL复制的数据粒度可以是主实例上所有的数据库，也可以是指定的一个或多个数据库，也可以是一个数据库里的指定的表
1.2、主从复制优势
1）扩展能力：通过复制功能可以将MySQL的性能压力分担到一个或多个slave上。这要求所有的写操作和修改操作都必须在Master上完成，而读操作可以被分配到一个或多个slave上。将读写分离到不同服务器执行之后，MySQL的读写性能得到提升
2）数据库备份：由于从实例是同步主实例的数据，所以可以将备份作业部署到从库
3）数据分析和报表：同样，一些数据分析和报表的实现可以在从实例执行，以减少对主库的性能影响
4）容灾能力：可以在物理距离较远的另一个数据中心建立一个slave，保证在主实例所在地区遭遇灾难时，在另一个数据中心能快速恢复
1.3、MySQL复制方法
1）传统方式：基于主库的bin-log将日志事件和事件位置复制到从库，从库再加以应用来达到主从同步的目的
2）Gtid方式：global transaction identifiers是基于事务来复制数据，因此也就不依赖日志文件，同时又能更好的保证主从库数据一致性
1.4、MySQL复制类型
1）异步复制：一个主库，一个或多个从库，数据异步同步到从库
2）同步复制：在MySQL Cluster中特有的复制方式
3）半同步复制：在异步复制的基础上，确保任何一个主库上的事务在提交之前至少有一个从库已经收到该事务并日志记录下来
4）延迟复制：在异步复制的基础上，人为设定主库和从库的数据同步延迟时间，即保证数据延迟至少是这个参数
1.5、复制的原理
复制的工作原理是数据库修改事件记录到bin log中并传递到slave，然后slave在本地还原的过程。而事件记录到bin log的格式会有所不同。
1.6、复制的格式
1）基于语句的复制(statement based replication)：基于主库将SQL语句写入到bin log中完成复制
2）基于行数据的复制(row based replication)：基于主库将每一个行数据变化的信息作为事件写入到bin log中完成日志
3）混合复制(mixed based replication)：上述两者的结合。默认情况下优先使用基于语句的复制，只有当部分语句如果基于语句复制不安全的情况下才会自动切换为基于行数据的复制
二、MySQL传统复制：基于binlog的复制
2.1、简介
1）基于binary log的复制是指主库将修改操作写入到bin log中，从库负责读取主库的bin log，并在本地复制一份，然后将里面的操作在从库执行一遍
2）每个从库会保存目前读取主库日志的文件名和日志位置
3）主库和每个从库都必须有一个唯一ID，叫server-id配置在配置文件中
2.2、主从复制前提
1）两台以上mysql实例
2）主库开启二进制日志
3）专用的复制用户
4）保证主从开启之前的某个时间点,从库数据是和主库一致（主库全备导入到从库）
5）告知从库,复制用户信息,IP port,以及复制起点(change master to)
6）线程(三个):
- master线程：Dump thread
- slave线程： IO thread ，SQL thread -->开启(start slave)
2.2、传统复制原理
文字说明：
1. Slave 节点,通过IO线程,读取master.info 的信息(IP port user,passwd,file+position ) 2. IO Thread 通过连接信息,连接上主库,主库会生成DUMP THREAD 3. IO 通过 master.info 提供的 file+position,找主库请求比这个新的二进制日志事件 4. Master DUMP thread 截取全新的二进制日志"事件",按照事件为单元,串行传送给 SLAVE IO THREAD 5. SLAVE IO THREAD收到发过来的二进制日志事件,缓存到 TCP/IP cache中,立即返回ACK 给MASTER. 6. SLAVE IO线程,更新master.info中二进制日志信息(已经获取过的二进制日志信息),并且会将TCP/IP缓存 数据写入relay-log日志中 7. SLAVE SQL 线程,读取relay-log.info ,获取到上次已经执行过的relaylog 位置号 8. SQL 线程根据relaylog历史记录,往后继续(串行)执行relaylog,执行完成后,再次更新relay-log.info信息 9. relay-log被应用完成后,会定时自动清理
2.3、配置过程
1）主库需要开启bin-log，并且指定一个唯一的server-id，重启数据库
[mysqld] datadir = /data/data log-bin = mysql-bin server-id = 1 #在同一个复制组下的所有实例的server_id都必须是唯一的，而且取值必须是正整数，取值范围是1~(232)−1 确保主库的my.cnf中skip-networking参数为非开启状态，否则会导致主从库不能通信而复制失败 [root@master ~]# /etc/init.d/mysql.server start [root@master ~]# mysql -uroot -pmysql mysql> show variables like '%log_bin%'; +---------------------------------+----------------------------+ | Variable_name | Value | +---------------------------------+----------------------------+ | log_bin | ON | | log_bin_basename | /data/data/mysql-bin | | log_bin_index | /data/data/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+----------------------------+ [root@master data]# ls mysql-bin.* mysql-bin.000001 mysql-bin.index
2）在主库创建一个专门用来做复制的数据库用户，这样所有从库都可以用这个用户来连接主库，也可以确保这个用户只有复制的权限。虽然可以用任何拥有复制权限的MySQL用户来建立复制关系，但由于被使用的用户名和密码会明文保存在备库的master.info文件中，所以为安全起见，最好是使用仅有复制权限的独立用户
mysql> CREATE USER 'repl'@'10.0.0.%' IDENTIFIED BY 'mysql'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%';
3）从slave库验证远程连接主库是否正常
[root@slave ~]# mysql -urepl -pmysql -P 3306 -h 10.0.0.51
4）获取主库的日志信息
为了确保建立的备库能从正确的bin log位置开启复制，要首先获取主库的bin log信息，包括当前的日志文件名和日志文件内的位置
mysql> FLUSH TABLES WITH READ LOCK; mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+ | mysql-bin.000001 | 770 | | | 3413f1ef-ee30-11e8-aeb5-000c29e5974b:1-18, 36dbc733-f24d-11e8-8988-000c2966cef5:1-3 | +------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+ --------------------------------------------------------------- #当加锁后 mysql> drop table temp; ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock mysql> create database temp2; ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock mysql> insert into temp values(now()); ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
5）主库数据生成镜像并上传到从库
两种方式生成镜像
- mysqldump，是innodb存储引擎推荐的方式；
- 数据文件从主库拷贝到从库，这种方式效率更高(省去了dump/import过程中insert语句执行导致的更新index的行为)，但innodb不推荐使用
①、使用mysqldump方式
[root@master ~]# mysqldump --all-databases --master-data -u root -pmysql -P 3306 > dbdump.db [root@master ~]# scp -rp dbdump.db root@10.0.0.52:~ mysql> unlock tables; #主库释放锁 #mysqldump方式导出所有数据库数据到dbdump.db文件， #--master-data表示导出数据直接加上change master to参数以便备库使用 --------------------------------------------------------------------------------- [root@db01 data]# mysqldump -S /data/3307/mysql.sock -A --master-data=2 -R --triggers --single-transaction >/backup/full.sql [root@db01 data]# vim /backup/full.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=775;
②、数据文件拷贝（需要停主库）
需要将主库临时关闭，并将数据文件（data目录）拷贝到从库上
6）从库配置唯一server-id，并重启mysql实例，从库的bin log属性可以打开也可以不打开
[mysqld] server-id=2
7）从库应用主库的数据镜像
Mysqldump的镜像，通过source命令执行，若时原始数据文件拷贝的镜像，将文件复制到和主库相同的目录下
mysql> source /root/dbdump.db;
8）从库指定主库的日志信息和链接信息
CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='mysql', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=770; ---------------------------------------------- CHANGE MASTER TO MASTER_HOST='master2.mycompany.com', #主库的主机名 MASTER_USER='replication', #复制的数据库用户名 MASTER_PASSWORD='bigs3cret', #复制的数据库用户密码 MASTER_PORT=3306, #主库的端口 MASTER_LOG_FILE='master2-bin.001', #主库的日志文件名 MASTER_LOG_POS=4, #主库的日志文件位置 MASTER_CONNECT_RETRY=10; #主库连接重试次数
9）从库启动复制进程并查看状态
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 770
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 770
Relay_Log_Space: 527
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 5874e7c6-e753-11e9-91e7-000c29db13e4
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:10） 验证复制工作正常：基本方法是在主库添加任意数据，查看从库是否能查询到
11）查看主从线程
#主库上执行 mysql> show processlist; +----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+ | 7 | root | localhost | NULL | Query | 0 | starting | show processlist | | 8 | repl | 10.0.0.52:50994 | NULL | Binlog Dump | 1227 | Master has sent all binlog to slave; waiting for more updates | NULL | +----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+ #从库上执行 mysql> show processlist; +----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+ | 3 | root | localhost | world | Query | 0 | starting | show processlist | | 4 | system user | | NULL | Connect | 1244 | Waiting for master to send event | NULL | | 5 | system user | | NULL | Connect | 473 | Slave has read all relay log; waiting for more updates | NULL | +----+-------------+-----------+-------+---------+------+--------------------------------------------------------+------------------+
2.4、常见错误及修复
2.4.1、错误一
Last_IO_Errno: 1593 Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. 解决办法：删除备库的auto.cnf文件，重启mysql，生成新的UUID
2.4.2、错误二
Last_IO_Errno: 2003 Last_IO_Error: error connecting to master 'repl@10.0.0.51:3306' - retry-time: 60 retries: 19 #解决办法：备库连接主库失败，检查防火墙，用户密码端口是否设置正确
2.4.3、错误三
#复制过程中由于备份之前没有lock全表而导致的数据复制异常 #主库上执行： Delimiter // Create procedure proc1() Begin Declare n int default 1; while n<=20000 do Insert into temp values(n, 'mike'); Set n=n+1; End while; End; // delimiter ; mysql> show master status; mysql> call proc1(); 存储过程执行过程中开始mysqldump，并建立从库
2.4.4、IO线程故障
1）连接主库的用户，密码，IP，port错误
Last_IO_Errno: 1045 Last_IO_Error: error connecting to master 'repl@10.0.0.51:3307' - retry-time: 10 retries: 2 #解决方法：手动登录复制用户，看是否能连接 [root@db01 data]# mysql -urepl -p123456 -h 10.0.0.51 -P 3307 #密码其实是错误的 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'repl'@'db01' (using password: YES) 连接不上说明相关信息错误 处理方法： stop slave reset slave all change master to #填写正确的信息 start slave #反向解析问题解决：在配置文件中添加skip_name_resolve并重启 [root@db01 data]# vim /data/3307/my.cnf [mysqld] skip_name_resolve
2）主库连接数已经达到上限（或主库太繁忙）
#报错： Last_IO_Errno: 1040 #与上面的报错信息相同，但报错代码不一样 Last_IO_Error: error reconnecting to master 'repl@10.0.0.51:3307' - retry-time: 10 retries: 7 #实验模拟： mysql> show variables like '%max_connections%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | #最大连接数 +-----------------+-------+ 1 row in set (0.01 sec) #将最大连接数调小测试 mysql> set global max_connections=3; #其实可以连接的最大连接数是4个 #同时开多个mysql连接（4个），再次使用复制用户连接 [root@db01 ~]# mysql -urepl -p123 -h 10.0.0.51 -P 3307 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1040 (HY000): Too many connections 解决方法：调大最大连接数的大小 mysql> set global max_connections=300;
3）防火墙阻挡，网络不通
4）从库请求不到二进制日志（主库缺少日志） #严令禁止主库中reset master;
#当在主库上执行reset master命令，会清空binlog日志，报错如下： Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'could not find next log; the first event 'mysql-bin.000002' at 154, the last event read from '/data/3307/data/mysql-bin.000002' at 123, the last byte read from '/data/3307/data/mysql-bin.000002' at 154.' #解决方法：重新构建主从 stop slave reset slave all change master to start slave #注意：在主从复制环境中,严令禁止主库中reset master; 可以选择expire进行定期清理主库二进制日志 mysql> show variables like '%expire%'; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | disconnect_on_expired_password | ON | | expire_logs_days | 0 | #一般设置为全备日期加1天 +--------------------------------+-------+
5）从库change master to位置点不对
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider out of disk space on master; the first event 'mysql-bin.000001' at 158, the last event read from '/data/3307/data/mysql-bin.000001' at 123, the last byte read from '/data/3307/data/mysql-bin.000001' at 201.' #解决方法：在master上找到位置点，重新在从库上执行change master to
2.4.5、SQL线程故障
故障原因：
1）读写relay-log.info故障（很少） 2）relay-log损坏，断节，找不到（很少） 3）接受的SQL无法执行 1、SQL_MODE影响 2、要创建的数据库对象,已经存在 3、要删除或修改的对象不存在 4、DML语句不符合表定义及约束时. #归根揭底的原因都是由于从库发生了写入操作
故障模拟：
#故障模拟： 1.构建正确的主从关系 2.在从库上建库测试 mysql> create database test; 3.在主库创建test库，此时会同步从库 mysql> create database test; 4.查看从库状态（报错） mysql> show slave status\G Last_SQL_Errno: 1007 Last_SQL_Error: Error 'Can't create database 'test'; database exists' on query. Default database: 'test'. Query: 'create database test' #解决方法： 1、以从库为核心的处理方案：同步指针向下移动（可以执行多次） stop slave; set global sql_slave_skip_counter = 1; start slave; #可以编辑配置文件，跳过错误（有风险，最安全的方法是重新构建主从） /etc/my.cnf slave-skip-errors = 1032,1062,1007 #跳过指定的错误 常见的错误代码： 1007:对象已存在 1032:无法执行DML 1062:主键冲突,或约束冲突 2、设置从库只读，添加中间件实现读写分离 mysql> show variables like '%read_only%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_read_only | OFF | #对超级管理员有效 | read_only | OFF | #只对普通用户有效 | super_read_only | OFF | #对超级管理员有效 | transaction_read_only | OFF | | tx_read_only | OFF | +-----------------------+-------+
2.4.6、主从延时故障
#从库延时较长原因： 1、主库写binlog不及时 控制binlog从内存写入磁盘的控制开关sync_binlog sync_binlog=1 #每次事务提交都立即刷新binlog到磁盘(双一标准中的其一) sync_binlog=0 #每次事务提交不立即写入磁盘,靠操作系统判断什么时候写入 2、dump线程导致，系统资源压力大 从库越多,dump线程压力越大 解决方法： 1）减少频繁小事务,减少超大事务 2）group commit 3、IO线程阻塞 大事务拆成小事务 事务量大(主库压力大) 1) group commit可以缓解 2) 业务的分离和分布式(Mycat,InnoDB Cluster) 4、SQL线程慢 从库 默认只有一个SQL线程,从库中的事务都是一个一个串行来执行的，主库可以并行多个事务，但会造成从库延时 #传统复制 主从延时原因小结 1. 主库binlog日志写入不及时 2. dump繁忙 (串行传送日志) 3. IO线程阻塞(主库大事务,频繁事务,串行接收日志) 4. SQL线程 (串行的执行事务) #解决方案: group commit(DUMP和IO) 多线程复制(SQL多线程) #如何监控主从延时: show slave status \G (1) Seconds_Behind_Master #从库延时秒数 (2) 对比从库中master.info和relay.info, show relaylog events in 'db01-relay-bin.000006'; 监控延时的日志量 (3)pt-heartbeat
2.4.7、主从重点关注
#线程相关监控 #主库: show full processlist; #每个从库都会有一行dump相关的信息 HOSTS: db01:47176 State: Master has sent all binlog to slave; waiting for more updates #如果现实非以上信息,说明主从之间的关系出现了问题 #从库: show slave status \G; db01 [(none)]>show slave status \G *************************** 1. row *************************** #主库有关的信息(master.info):重点关注 Master_Host: 10.0.0.51 Master_User: repl Master_Port: 3307 Connect_Retry: 10 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 444 #从库relaylog的信息(已经执行过的relaylog的位置点):重点关注 Relay_Log_File: db01-relay-bin.000002 Relay_Log_Pos: 320 #从库 两个线程的基本状态:(重点关注) Slave_IO_Running: Yes Slave_SQL_Running: Yes #说明:在正常的主从复制关系中,都是YES #过滤复制有关的参数 Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: #最后一次错误的代号及具体的报错信息(了解) Last_Errno: 0 Last_Error: #是否自动跳过复制错误(谨慎使用) Skip_Counter: 0 #从库和主库延时的秒数: Seconds_Behind_Master: 0 #分别记录的IO和SQL报错的具体信息(重点关注) Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: #延时从库的状态 SQL_Delay: 0 SQL_Remaining_Delay: NULL #GTID复制有关的状态 Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0
2.5、拓展slave数量
1）当第一个slave创建好之后，如果还想创建其他的slave，则可以直接使用先前使用的备份文件，分别执行：
- a)在slave的my.cnf上分配新的server_id
- b)从库应用主库的数据镜像
- a)利用相同的change master命令将从库指定主库的日志信息和链接信息
- c)Slave start
2）如果想在事后再增加一个slave，但之前的备份文件已经不存在，或者主库的日志文件已经被清除了的情况下，考虑使用如下办法： 在已经建立好的复制环境中新增一个从库，则不需要关闭主库复制数据，而是用已有的从库复制数据即可
- a)关闭现有的从库
- mysqladmin shutdown
- b)拷贝从库的文件到新的从库，包括log文件和relay log文件，其中如果relay log使用了从库的主机名，则需要调relay-log-index参数
- c)为新的从库分配一个唯一的server-id，注意需要删除auto.cnf
- d)新的从库启动slave进程
2.6、复制相关系统变量
1）server_id
是必须设置在master和每个slave上的唯一标识ID，其取值范围是1~4294967295之间，且同一个复制组之内不能重复
2）server_uuid
server_uuid会在GTID复制中使用。当MySQL启动之后，会首先到数据文件目录下的auto.cnf中寻找是否有指定的server_uuid，如果没有找到，则自己生成一个server_uuid并保存到这个文件中
3）log_slave_updates
该参数用来控制是否将收到的主库的更新数据的语句也记录在slave自己的bin log中。正常情况下是不需要记录的，但如果是想创建级联复制关系，比如A -> B -> C，这其中B既要作为A的从库，也要作为C的主库，则需要既开启log-bin参数，也要开启log_slave_updates参数
4）relay-log
该参数用来指定relay-log文件的基础名称，默认的名称为host_name-relay-bin.xxxx，其中的xxxx结尾是依次递增的数字
5）replicate-do-db
该参数用来指定需要复制的数据库。
①在基于语句statement复制的环境中，指定该参数之后，则slave的SQLthread进程只会应用在本数据库下的对象相关的语句。如果有多个数据库需要复制，则这个参数要使用多次。但如果是涉及到跨库操作语句，则复制会丢失，比如：
#my.cnf配置 replicate-do-db=sales #sql执行语句 USE prices; UPDATE sales.january SET amount=amount+1000;
②在基于行row复制的环境中，只要数据库对象是指定的库，则复制都能正常，比如上述update语句由于january表是属于sales库的，则slave会复制并应用，同样下面的语句在基于行复制的环境中也不会执行：
USE sales; UPDATE prices.march SET amount=amount-25; #不会执行
#在slave的my.cnf上设置replicate-do-db=test，重启mysql #查看从库的状态信息： mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: test #在主库上执行: mysql> use test; mysql> insert into temp values(5,'e'); mysql> use test2; mysql> insert into temp values(13,'dd'); #在备库上查看复制情况： mysql> use test; mysql> select * from temp; ##数据有复制 +------+------+ | id | name | +------+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 5 | e | +------+------+ mysql> use test2; mysql> select * from temp; ##数据没有复制 ---------------------------------------------------- #在语句statement复制环境下查看对指定数据库的修改操作： [mysqld] binlog-format=statement #主库上执行： mysql> use test; mysql> update test2.temp set name='ddd'; mysql> use test2; mysql> update test.temp set name='eee'; #在从库上查看复制结果： mysql> use test; mysql> select * from temp; ##虽然是指定的同步数据库但并没有同步 +------+------+ | id | name | +------+------+ | 1 | abc | | 2 | abc | | 3 | abc | | 4 | abc | | 5 | abc | +------+------+ mysql> use test2; mysql> select * from temp; ##虽然不是指定的同步数据库但数据有同步 +------+------+ | id | name | +------+------+ | 10 | ddd | | 11 | ddd | | 12 | ddd | ------------------------------------------------------------ #在行复制环境下查看对指定数据库的修改操作： mysql> show variables like '%binlog_format%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ #主库上执行： mysql> use test; mysql> update test2.temp set name='bcd'; mysql> use test2; mysql> update test.temp set name='abc'; #在从库上查看复制结果： mysql> use test; mysql> select * from temp; ##数据已复制 +------+------+ | id | name | +------+------+ | 1 | abc | | 2 | abc | | 3 | abc | | 4 | abc | | 5 | abc | +------+------+ mysql> use test2; mysql> select * from temp; ##数据未复制 +------+------+ | id | name | +------+------+ | 10 | aa | | 11 | bb | | 12 | cc | +------+------+ 另一个基于SQL语句复制和基于行复制的区别在于当语句中包含对多个数据库的表进行操作时。 比如设置 replicate-do-db=db1, USE db1; UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2 = 20; 基于SQL语句的复制会将table1和table2都在备库修改， 而基于行的复制只会在备库修改table1表 USE db4; UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2 = 20; 而对于上述语句来说，基于SQL语句的复制不会在备库修改任何表，而基于行的复制会在备库修改table1表 如果希望跨库的update语句在多个库上都起作用，可以使用replicate-do-table=db_name.tbl_name
6）replicate-ignore-db
该参数决定了忽略指定数据库的复制，其行为和replicate-do-db正好相反
7）replicate-do-table=db_name.tbl_name
通过该参数告知slave的SQL thread仅复制指定表上的数据。如果有多个表，则该参数要使用多次
8）replicate-ignore-table=db_name.tbl_name
通过该参数告知slave的SQL thread将指定表上的数据过滤掉
9）replicate-wild-do-table=db_name.tbl_name
通过该参数告知SQL的SQL thread仅复制符合匹配的表，可以使用_和%作为通配符。比如replicate-wild-do-table=foo%.bar%表示复制以foo打头的数据库下所有bar打头的表数据。如果是replicate-wild-do-table=foo%.%,则表示即复制foo打头的所有表的数据，也复制create/drop/alter database foo打头的命令
10）replicate-wild-ignore-table=db_name.tbl_name
通过该参数告知SQL的SQL thread过滤掉符合匹配的表
11）slave-parallel-workers
该参数决定了slave上启动多个SQL thread线程来并行应用数据的。默认值是0代表不允许并行，取值范围可以是0~1024
[mysqld] slave-parallel-workers=5
12）skip-slave-start
该参数决定了在MySQL启动时是否先不启动slave线程，即暂停复制
[mysqld] skip-slave-start=1
13）slave-parallel-type=type
该参数决定了当启动了并行之后，采用什么粒度的并行方式。默认值database表示按照不同的数据库执行并行，LOGICAL_CLOCK则表示按照在binlog中的一组提交的事务作为并行粒度
14）slave-skip-errors=[err_code1,err_code2,...|all|ddl_exist_errors]
该参数决定了当slave的SQLthread执行过程中碰到何种错误时可以忽略并继续接下来的数据复制。正常情况下当有错误发生时，复制会停止而需要人工干预修复才能继续进行。除非非常自信可以忽略某些错误，否则不要使用这个参数，不然会导致虽然复制执行正常，但其实内部的数据已经完全不一致
15）sql_slave_skip_counter
代表在非GTID复制环境下，通过设置此参数来跳过多少个复制事件。设置完该参数并非立即生效，而是要等待下次start slave命令的执行生效，并将该参数再次设置为0
16）log-bin[=base_name]
该参数表示是否开启binary log。默认情况下MySQL会使用host_name-bin.xxxx作为文件的名字，其中xxxx是以数字递增的后缀。如果该参数指定了base_name，则二进制文件会以base_name.xxxx来命名
17）binlog-do-db=db_name
该参数决定了哪些库下的修改会被记录到bin log中。其行为与replicate-do-db类型，
1、在基于SQL语句复制的环境下，只记录在当前数据库下的修改。比如指定binlog-do-db=sales，以下语句不会被记录到bin log中： USE prices; UPDATE sales.january SET amount=amount+1000; 而以下语句则会被记录到bin log中： USE sales; UPDATE prices.discounts SET percentage = percentage + 10; 2、而基于行复制的环境下，只有属于指定数据的语句才会被记录到bin log中。比如下面的语句 会被记录： USE prices; UPDATE sales.february SET amount=amount+100; 而下面的语句则不会被记录： USE sales; UPDATE prices.march SET amount=amount-25; 3、针对跨库的语句来说，行为和replicate-do-db相同
18）binlog-ignore-db=db_name
该参数决定了在bin log中忽略的数据库，其行为与replicate-ignore-db类似
19）binlog_format
该参数决定了bin log中记录的格式，可以是statement,row,mixed，分别代表基于SQL语句的复制，基于行复制和基于混合复制。在5.7.7版本之前的默认设置是statement，在5.7.7及以后，则默认是row。当设置为混合模式时，则优先使用statement，只有当基于语句的复制无法保证复制的准确时会自动替换为row
2.7、检查复制状态方法
1）在slave上执行show slave status来检查复制是否正常工作
Slave_IO_State：代表当前slave的状态 Slave_IO_Running：代表负责读取主库bin log的IO线程是否是运行状态，正常情况下应该是YES Slave_SQL_Running：代表负责执行备库relay log的SQL线程是否是运行状态，正常情况下应该是YES Last_IO_Error, Last_SQL_Error: 分别代表最后一次IO线程和SQL线程所发生的错误，正常情况下应该是空代表没有错误 Seconds_Behind_Master：代表备库的SQL线程比主库的bin log晚多少秒。0代表目前没有复制延迟 (Master_Log_file, Read_Master_Log_Pos)：表示IO线程在主库bin log中的坐标位置 (Relay_Master_Log_File, Exec_Master_Log_Pos)：表示SQL线程在主库bin log中的坐标位置 (Relay_Log_File, Relay_Log_Pos)：表示SQL线程在备库relay log中的坐标位置
2）主库可以通过执行show processlist命令查看主库的bin log日志生成进程
mysql> show processlist; +----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+ | 7 | root | localhost | NULL | Query | 0 | starting | show processlist | | 8 | repl | 10.0.0.52:50994 | NULL | Binlog Dump | 1227 | Master has sent all binlog to slave; waiting for more updates | NULL | +----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
2.8、MySQL复制格式
2.9、MySQL复制线程
MySQL复制涉及三个线程，其中一个在主库，另两个在从库
- binlog dump thread：在主库创建，用来在从库链接过来时发送bin log的内容
- slave io thread：在备库创建，用来连接主库并请求发送新的bin log内容。该线程读取主库的bin log dump线程发送的更新内容并将此内容复制到本地的relay log中
- Slave sql thread：在备库创建，读取slave io线程在本地relay log中的内容并在本地执行内容中的事件
#主库执行 mysql> show processlist; +----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+ | 3 | repl | 10.0.0.52:48344 | NULL | Binlog Dump | 801 | Master has sent all binlog to slave; waiting for more updates | NULL | | 4 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+ #从库执行 mysql> show processlist; +----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+ | 1 | system user | | NULL | Connect | 836 | Waiting for master to send event | NULL | | 2 | system user | | NULL | Connect | 835 | Slave has read all relay log; waiting for more updates | NULL | | 6 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
复制启停方法：
#在slave暂停复制的方法： #整体停止的方法： mysql> STOP SLAVE; #停止指定线程的方法： mysql> STOP SLAVE IO_THREAD; mysql> STOP SLAVE SQL_THREAD; #整体开启的方法： mysql> START SLAVE; #开启指定线程的方法： mysql> START SLAVE IO_THREAD; mysql> START SLAVE SQL_THREAD;
2.10、MySQL复制使用场景
1）MySQL复制可以作为数据库备份的一种解决方案，由于主库的数据会复制到备库，所以可以在备库执行数据库备份作业而不用影响主库的性能 在备库的备份通常有两种选择：
①当数据库比较小时，可以采用mysqldump的方式。由于mysqldump出来的文件内容是SQL语句，所以可以很方便的将其中的一部分复制出来应用到其他数据库里。在执行mysqldump之前，为了保证数据的一致性，最好是把slave进程停掉。
shell> mysqladmin stop-slave #或者 shell> mysql -e 'STOP SLAVE SQL_THREAD;' shell> mysqldump --all-databases > fulldb.dump shell> mysqladmin start-slave
②当数据库比较大时，采用mysqldump方式的效率不高，所以可以使用物理文件拷贝的方式。为了保证数据的一致性，物理备份需要将备库关闭
shell> mysqladmin shutdown shell> tar cf /tmp/dbbackup.tar ./data /etc/init.d/mysql.server start
2）MySQL复制可以用在主库和从库采用不同的存储引擎的情况下。这样做的目的通常是在主库和从库可以分别利用不同存储引擎的优势，比如在主库使用InnoDB是为了事务功能，而从库使用MyISAM因为是只读操作而不需要事务功能
①当使用mysqldump方式来创建备库时，改变备库的表存储引擎的方式就是在应用dump文件之前先修改文件里的所有关于表存储引擎的地方
②如果是使用文件拷贝的方式来创建备库时，则唯一修改备库表存储引擎的方式就是在启动备库之后使用alter table命令修改
mysql> STOP SLAVE; 执行ALTER TABLE ... ENGINE='engine_type'命令 mysql> START SLAVE;
3）MySQL复制可以用来做负载均衡功能的水平扩展，最主要是将数据库的读压力分担到多个MySQL slave实例上，这种情况适用在读多写少的环境中。比如一个基本的WEB架构
4）MySQL复制可以用在当需要将主库上的不同数据库复制到不同的slave上，以便在不同的slave上执行不同的数据分析任务时。 可以在每个slave上配置不同的参数来约束复制过来的数据，通过replicate-wild-do-table参数或者replicate-do-db参数
slave1上应该配置参数replicate-wild-do-table=databaseA.% slave2上应该配置参数replicate-wild-do-table=databaseB.% slave3上应该配置参数replicate-wild-do-table=databaseC.% 每个slave其实是接收到完整的bin log日志，但在应用环节中会进行过滤，仅应用符合参数配置的事件 在配置完参数之后，通过mysqldump的方式将对应数据库在slave应用起来，再启动slave线程
2.11、延迟复制
2.11.1、延迟复制介绍
延迟复制是指定从库对主库的延迟至少是指定的这个间隔时间，默认是0秒。可以通过change master to命令来指定 CHANGE MASTER TO MASTER_DELAY = N; 其原理是从库收到主库的bin log之后，不是立即执行，而是等待指定的秒数之后再执行
2.11.2、延迟复制的使用场景
1）确保在主库上被错误修改的数据能及时找回
2）测试在从库IO集中在恢复bin log过程中对应用程序的访问影响
3）保留一份若干天前的数据库状态，和当前状态可以做对比
4）show slave status中SQL_Delay值表明了设置的延迟时长
2.11.3、延迟复制配置
#在slave上执行
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_DELAY = 60;
mysql> start slave;
#在主库上执行
mysql> insert into temp values(3,'cc');
mysql> insert into temp values(4,'dd');
#在slave上查看复制情况
mysql> select * from temp; #数据没有同步过来
+------+------+
| id | name |
+------+------+
| 1 | aa |
| 2 | bb |
+------+------+
mysql> show slave status\G
Exec_Master_Log_Pos: 784
Seconds_Behind_Master: 36
SQL_Delay: 60 #设置的主从延时秒数
SQL_Remaining_Delay: 24 #延迟时间实时显示
Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event
#等待60s后数据同步过来了
mysql> select * from temp;
+------+------+
| id | name |
+------+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
| 4 | dd |
+------+------+
mysql> show slave status\G
Seconds_Behind_Master: 0
SQL_Delay: 60
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates2.12、MySQL复制主从切换
1）如果是使用GTID的复制方式，可以使用mysqlfailover工具做主从复制状态的监控和自动切换；
2）如果是使用非GTID模式，则需要使用其他的方式做监控和切换 当新的master产生之后，需要通过在其他slave上执行change master to语句来对应到新的master上。slave不会检查自己的数据库和新的master上是否一致，而是直接获取master上的二进制日志并继续自己的复制功能
3）新当选master的实例需要运行在log_bin模式下
4）配置方法
1、新的master上开启log-bin=mysql-bin，Master上查看bin log信息 mysql> show master status; 2、在slave上执行： mysql> reset slave all; mysql> stop slave; CHANGE MASTER TO MASTER_HOST='192.168.237.130', #新的master MASTER_PORT=3308, MASTER_USER='repl', MASTER_PASSWORD='mysql', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154; mysql> start slave;
三、MySQL半同步复制
3.1、半同步复制介绍
1）默认创建的MySQL复制是异步的，意味着主库将数据库修改事件写入到自己的bin log，而并不知道从库是否获取了这些事件并应用在自己身上。所以当主库崩溃导致要主从切换时，有可能从库上的数据不是最新的
2）从5.7版本开始MySQL通过扩展的方式支持了半同步复制 ，当主库执行一个更新操作事务时，提交操作会被阻止直到至少有一个半同步的复制slave确认已经接收到本次更新操作，主库的提交操作才会继续
3）半同步复制的slave发送确认消息只会在本次更新操作记录已经记录到本地的relay log之后，如果没有任何slave发送确认消息而导致超时时，半同步复制会转换成异步复制
4）半同步复制会对MySQL性能产生影响，因为主库的提交动作只有在收到至少一个从库的确认消息之后才能执行。但这个功能是性能和数据可靠性方面的权衡
3.2、半同步复制原理
半同步复制工作原理的变化 1. 主库执行新的事务,commit时,更新 show master status\G ,触发一个信号给dump线程 2. dump线程接收到主库的 show master status\G信息,通知从库日志更新了 3. 从库IO线程请求新的二进制日志事件 4. 主库会通过dump线程传送新的日志事件,给从库IO线程 5. 从库IO线程接收到binlog日志,当日志写入到磁盘上的relaylog文件时,发送信号给主库ACK_receiver线程 6. ACK_receiver线程触发一个事件,告诉主库commit可以成功了 7. 如果ACK达到了我们预设值的超时时间,半同步复制会切换为原始的异步复制
3.2、半同步复制相关参数
1）rpl_semi_sync_master_wait_point参数用来控制半同步复制的行为：
- AFTER_SYNC：默认值
- AFTER_COMMIT
2）需要配置的系统参数包括：
- rpl_semi_sync_master_enabled：在主库配置，确保主库的半同步复制功能开启
- rpl_semi_sync_master_timeout：配置主库等待多少毫秒时间来保证接收备库的确认消息，当超过这个时间时，半同步变成异步方式
- rpl_semi_sync_slave_enabled：在从库配置，确保从库的半同步复制功能开启
3.3、半同步配置过程
半同步复制是通过插件的方式建立，要分别在主库和从库安装一个插件 show plugins;
前提条件：
- 5.5版本及以上
- have_dynamic_loading参数必须是YES代表可以安装插件并动态加载 mysql> show variables like '%have_dynamic_loading%';
- 事先建立好异步复制关系
1）主库安装插件
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%'; +----------------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +----------------------+---------------+ | rpl_semi_sync_master | ACTIVE | +----------------------+---------------+
2）从库上安装插件
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%'; +---------------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +---------------------+---------------+ | rpl_semi_sync_slave | ACTIVE | +---------------------+---------------+
3）在主库上开启半同步复制
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1; mysql> SET GLOBAL rpl_semi_sync_master_timeout = 60000; #N是毫秒，默认是10000，代表10秒
4）在备库上开启半同步复制
mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1;
5）在备库上重启slave进程
mysql> STOP SLAVE IO_THREAD; mysql> START SLAVE IO_THREAD;
6）查看状态
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 1 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | +--------------------------------------------+-------+
3.4、半同步复制监控参数
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%'; #查看各个参数的状态 #半同步复制监控参数： Rpl_semi_sync_master_clients：检查半同步的slave个数 Rpl_semi_sync_master_status：1表示主库的半同步功能开启并且运行正常，0表示主库的半同步功能关闭或者半同步复制已经变成了异步复制 Rpl_semi_sync_master_no_tx：表示有多少提交没有收到slave的确认消息 Rpl_semi_sync_master_yes_tx：表示有多少个提交收到了slave的确认消息 Rpl_semi_sync_slave_status：1表示备库上slave功能开启并且运行正常，0表示功能未开启或者运行异常
3.5、半同步测试
#从库关闭IO线程 mysql> STOP SLAVE IO_THREAD; #主库执行update数据操作，需要等待60秒才能返回 mysql> update temp set name='ddd' where id=1; Query OK, 1 row affected (1 min 0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 #超时返回之后，从库的半同步状态变成OFF状态 mysql> show status like '%Rpl_semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 0 | | Rpl_semi_sync_master_status | OFF | #当从库同步正常后，半同步状态显示正常 mysql> START SLAVE IO_THREAD; mysql> show status like '%Rpl_semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 1 | | Rpl_semi_sync_master_status | ON | --------------------------------------------------------------------------------- #当有两个从库都开启半同步复制时，停止其中一个的slave IO线程，再在主库上执行插入，操作很快返回 #当把第二个从库的slave IO线程关闭时，则主库插入数据需要等待60秒才能返回
四、MySQL基于GTID的复制
传统主从复制弊端：
Failover（故障转移）问题？
1、监控问题？ 主机、mysql实例
2、处理里的问题，需要人为
3、数据补偿---->GTID
---------------------------------------------------
#描述环境：
1、1主2从的wordpress 生产环境，（db01(M) db02(s1) db03(s2)）
2、wordpress应用默认是连接到db01这个主库的
3、db01宕机
4、wordpress应用是没有能力和责任监控到db01宕机。
5、人为的实时监控到db01宕机。
6、选择一个新的主库（New Master）,原则更加接近主库数据状态的从库
7、选择方法：在每个从库重运行：
show slave status\G
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 120
8、假设s1从库被选为新主库，需要进行数据补偿。
8.1如果能通过ssh连接上主库，
(1)立即保存缺失部分的二进制日志
(2)s2做同样的操作即可
8.2 如果不能ssh到主库
(1)计算s2和s1之间的relay-log的差异日志（在传统的复制环境中，可能需要比较复杂的对比过程）
所以我们可以考虑使用GTID方式构建主从，主库发生的所有事务都会打上一个唯一的标标签，而且是全局
唯一的。基于GTID全局唯一的特性，很容易判断s1和s2 relay-log数据差异。
（2）S2 获取差异部分的relay-log日志，恢复到当前库，两个从库之间的数据就一致了。
9、s2重新指向（change master to 到s1）s1传统复制与GTID复制区别：
#传统复制
CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=444,
MASTER_CONNECT_RETRY=10;
#GTID复制
change master to
master_host='10.0.0.51',
master_user='repl',
master_password='123' ,
MASTER_AUTO_POSITION=1;
--------------------------------------------------------------------------
start slave;
（0）在主从复制环境中，主库发生过的事务，在全局都是由唯一GTID记录的，更方便Failover
（1）额外功能参数（3个）：
gtid-mode = on
enforce-gtid-consistency = on
log_slave_updates（5.6需要开启）
（2）change master to 的时候不再需要binlog 文件名和position号,MASTER_AUTO_POSITION=1;
（3）在复制过程中，从库不再依赖master.info文件，而是直接读取最后一个relaylog的 GTID号
（4） mysqldump备份时，默认会将备份中包含的事务操作，以以下方式
SET @@GLOBAL.GTID_PURGED='8c49d7ec-7e78-11e8-9638-000c29ca725d:1';
告诉从库，我的备份中已经有以上事务，你就不用运行了，直接从下一个GTID开始请求binlog就行。
4.1、GTID简介
1）GTID(global transaction identifiers)复制是完全基于事务的复制，即每个在主库上执行的事务都会被分配一个唯一的全局ID并记录和应用在从库上，这种复制方式简化了建立slave和master/slave之间切换的工作，因为其完全不需要找当前执行的bin log和log中的位置完成切换 ==>核心特性: 全局唯一,具备幂等性
2）一个GTID是master上执行的任何commit事务所分配的全局唯一ID标示，其由两部分组成
GTID = source_id:transaction_id #Source_id代表主库的server_uuid #transaction_id代表事务按顺序提交的ID，比如第一个提交则是1，第十个提交的事务就是10 #GTID集合代表一组GTID
4.2、GTID复制原理
1）当一个事务在主库提交时，该事务就被赋予了一个GTID，并记录在主库的binary log
2）主库的binary log会被传输到从库的relay log中，从库读取此GTID并生成gtid_next系统参数
3）从库验证此GTID并没有在自己的binary log中使用，则应用此事务在从库上
4.3、MySQL5.6与5.7 GTID区别
1）MySQL5.6的GTID复制模式，slave必须开启bin-log和log_slave_updates参数，否则启动就报错，因为需要在binlog找到同步复制的信息（UUID:事务号） （注：开启log_slave_updates参数，是把relay-log里的日志内容再记录到slave本地的binlog里。）
2）但在MySQL5.7里，官方做了调整，用一张gtid_executed系统表记录同步复制的信息（UUID:事务号），这样就可以不用开启log_slave_updates参数，减少了从库的压力
3）从MySQL5.7.4版本开始，GTID会存放在mysql系统库的gtid_executed表中
CREATE TABLE gtid_executed ( source_uuid CHAR(36) NOT NULL, interval_start BIGINT(20) NOT NULL, interval_end BIGINT(20) NOT NULL, PRIMARY KEY (source_uuid, interval_start) )
4.4、GTID复制配置
假定两个数据库实例间的主从关系已经通过传统模式创建好了
1）关闭主库和从库
shell> mysqladmin -uusername -p shutdown
2）设置主从库GTID后启动 并暂时关闭slave进程
#主库 [root@master ~]# cat /etc/my.cnf [mysqld] gtid-mode = on enforce-gtid-consistency = on #从库 [root@slave ~]# cat /etc/my.cnf [mysqld] gtid-mode = on enforce-gtid-consistency = on #是确保只有对gtid复制机制安全的语句才会被log
3）重启主从实例，重新设置主从库的复制关系
如果之前是传统主从复制，需要在从库上执行：stop slave;和reset slave all;
CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='mysql', MASTER_AUTO_POSITION = 1; #添加这一行
4）启动slave
mysql> START SLAVE;
7）测试
#从库上gtid表（主库和从库都有） mysql> select * from mysql.gtid_executed; +--------------------------------------+----------------+--------------+ | source_uuid | interval_start | interval_end | +--------------------------------------+----------------+--------------+ | 21d792f0-ebef-11e9-9252-000c29db13e4 | 1 | 4 | | 21d792f0-ebef-11e9-9252-000c29db13e4 | 5 | 5 | | 21d792f0-ebef-11e9-9252-000c29db13e4 | 6 | 6 | | 21d792f0-ebef-11e9-9252-000c29db13e4 | 7 | 7 | +--------------------------------------+----------------+--------------+
4.5、使用GTID复制的限制条件
1）由于GTID复制是依赖于事务的，所以MySQL的一些属性不支持。当一个事务中既包含对InnoDB表的操作，也包含对非事务型存储引擎表(MyISAM)的操作时，就会导致一个事务中可能会产生多个GTID的情况；或者是当master和slave的表使用的存储引擎不一样时，都会导致GTID复制功能不正常
mysql> create table temp2(id int,name varchar(10)) engine=myisam; Query OK, 0 rows affected (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into temp2 select * from temp; Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update temp set name='abc'; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> insert into temp2 select * from temp; ERROR 1785 (HY000): Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.
2）create table…select语句在基于语句复制的环境中是不安全的，在基于行复制的环境中，此语句会被拆分成两个事件，一是创建表，二是insert数据，在某些情况下这两个事件会被分配相同的GTID，而导致insert数据的操作被忽略，所以GTID复制不支持create table … select语句
mysql> create table temp2 select * from temp; ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.
3）create/drop temporary table语句在GTID复制环境中不能放在事务中执行，只能单独执行，并且autocommit要开启
4）sql_slave_skip_counter语句是不支持的，如果想要跳过事务，可以使用gtid_executed变量
4.6、GTID故障处理
4.6.1、GTID 从库误写入操作处理
报警信息：
#从库写入 Last_SQL_Errno: 1007 Last_SQL_Error: Error 'Can't create database 'mmm'; database exists' on query. Default database: 'mmm'. Query: 'create database mmm' Retrieved_Gtid_Set: 21d792f0-ebef-11e9-9252-000c29db13e4:6-11 Executed_Gtid_Set: 21d792f0-ebef-11e9-9252-000c29db13e4:1-10
解决方法:注入空事务
stop slave; set gtid_next='21d792f0-ebef-11e9-9252-000c29db13e4:11'; begin;commit; set gtid_next='AUTOMATIC'; start slave; #这里的xxxxx:N 也就是你的slave sql thread报错的GTID，或者说是你想要跳过的GTID。 #最好的解决方案：重新构建主从环境
-------------------------------------------
个性签名：独学而无友，则孤陋而寡闻。做一个灵魂有趣的人！
