Mysql Replication(主从复制) 职责介绍： (1)、搭建主从复制 *** (2)、主从复制原理熟悉 ***** (3)、主从的故障处理 ***** (4)、主从延时 ***** (5)、主从的特殊架构的配置使用 **** (6)、主从架构的演变 **** 2、主从复制介绍 (1)、主从复制基于binlog来实现的 (2)、主库发生新的操作，都会记录binlog (3)、从库取得主库的binlog进行回放 (4)、主从复制的过程是异步 3、主从复制的前提（搭建主从复制的过程） (1)、两个或两个以上的数据库实例 (2)、主库需要开启二进制日志 (3)、server_id要不同，用来区分不同的节点 (4)、主库需要建立专用的复制用户(replication slave 权限) (5)、从库应该通过备份主库，恢复的方法进行"补课" (6)、人为告诉从库一些复制信息(ip,port,user,password,二进制日志起点) (7)、从库应该开启专门的复制线程 4、主从复制搭建过程(一台数据库192.168.189.128主库已有数据的情况下) 4.1、准备1台服务器（192.168.189.131从库），安装mysql 5.7.26 4.2、检查配置文件 主库：二进制日志是否开启，从库可以先不开。 主从节点：server_id不能相同，一般建议，主库的server_id比从库的server_id小 4.3、主库上创建复制用户 mysql -uroot -p -e "grant replication slave on *.* to repl@'192.168.%' identified by 'repl.com';"4.4、'补课'，就是把主库上面的最近一次全备数据恢复到从库上 备份主库的数据： mysqldump -uroot -p -A --master-data=2 --single-transaction -R -E --triggers >/tmp/master.sql scp -r /tmp/master.sql root@192.168.189.131:/tmp/ 从库：导入主库的备份 登录mysql set sql_log_bin=0; source /tmp/master.sql; set sql_log_bin=1; 4.5、告诉从库信息（主库的信息IP 端口，用户名和密码） vim /tmp/master.sql
change master to master_host='192.168.189.128',master_user='repl',master_password='repl.com',master_log_file='mysql-bin.000011',master_log_pos=485,master_connect_retry=5; master_connect_retry=5 ##重新连接的次数，如果主从数据库断开，从库会自动重连，超过10次，表示连接失败。 4.6、从库开启复制线程（从库上有两个线程，IO线程，SQL线程） start slave; 4.7、检查主从复制的状态 show slave status\G
5、主从复制原理 5.1、主从复制中涉及的文件 主库：binlog 从库： relaylog：中继日志 master.info：主库信息文件 relaylog.info：relaylog应用的信息 5.2、主从复制中涉及的线程 主库：binlog_dump Thread：简称：DUMP_T 从库：SLAVE_IO_THREAD 简称：IO_T SALVE_SQL_THREAD 简称：SQL_T 5.3、主从复制工作过程原理 (1)、从库执行change master to 命令(主库的连接信息+复制的起点) (2)、从库会将以上信息，记录到master.info文件中 (3)、从库执行 start slave 命令，立即开启IO线程和SQL线程 (4)、从库IO线程读取master.info文件中的信息，获取到IP，PORT，User，Password，binlog的位置信息 (5)、从库IO线程请求连接主库，主库专门提供一个DUMP线程，负责和从库的IO线程交互。 (6)、从库IO线程根据binlog的位置信息(mysql-bin.000011,485)，请求主库新的binlog (7)、主库通过DUMP线程，将最新的binlog,通过网络传输给从库的IO线程 (8)、从库的IO线程接收到新的binlog日志，存储到TCP/IP缓存中，立即返回ACK给主库，并更新从库的master.info (9)、从库的IO线程将TCP/IP缓存中数据，转储到磁盘的relaylog 中。 (10)、从库的SQL线程读取relay-log.info中的信息，获取到上次已经应用过的relaylog的位置信息 (11)、从库的SQL线程会按照上次的位置点，回放最新的relaylog，再次更新relay-log.info信息 (12)、从库会自动purge(清除)应用过relaylog，进行定期清理。 补充说明： 一旦主从复制构建成功，主库中的数据发生了新的变化，都会通过DUMP线程，发送一个信息给从库的IO线程，IO立即请求主库新的binlog，并进行数据同步。增强了主从复制的实时性，不过Mysql主从复制 依然是个异步过程。因为只有等主库上的日志写到磁盘上后，从库IO线程才能请求。 5.4、主从复制监控： show slave status\G ******* 主库有关的信息(master.info) ******** Master_Host: 192.168.189.128 Master_User: repl Master_Port: 3306 Connect_Retry: 5 Master_Log_File: mysql-bin.000011 Read_Master_Log_Pos: 955 ******* 从库relay 应用信息有关的(relay.info)： Relay_Log_File: dba01-relay-bin.000002 ##默认在，数据目录/data/mysql/data/主机名-relay-bin.000001开始，可自定义 Relay_Log_Pos: 790 ##上次SQL线程已经运行到这个position了 Relay_Master_Log_File: mysql-bin.000011 ******* 从库线程运行状态（排错） ***** Slave_IO_Running: Yes Slave_SQL_Running: Yes Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: ****** 过滤复制有关的信息： ***** Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: 从库延时主库的时间（秒）： Seconds_Behind_Master: 0 延时从库：就是和主库相差多久时间，才开始同步。 SQL_Delay: 0 SQL_Remaining_Delay: NULL ********* GTID复制有关的状态信息： ******* Retrieved_Gtid_Set: 7cd51543-8d03-11ea-83e7-000c29e6dd30:2-3 Executed_Gtid_Set: 7cd51543-8d03-11ea-83e7-000c29e6dd30:1-3,b3060a95-5e86-11ea-bdb9-000c29e6dd30:1-54 Auto_Position: 0 5.5、主从复制故障 ***** 从库： 5.5.1、IO线程故障： (1)、连接主库：（网络，连接信息错误或者变更了，防火墙，连接数上限。）IO状态为：connecting 排查思路： 用复制用户，手工登录mysql，根据错误信息判断问题所在。 解决： stop slave; reset slave all; change master to .......... start slave; (2)、请求主库上的binlog 主库上binlog 没开 主库上的binlog损坏，不存在 主库上执行了，reset master; binlog日志mysql-bin.000001 154 解决： 在从库上操作： stop slave; reset slave all; change master to .........master_log_file='mysql-bin.000001',master_log_pos=154 start slave; (3)、存储主库上的binlog到从库的relaylog 出现relaylog不能写入，这种情况很少。 处理： 修改权限 重启新启动主从 5.5.2、SQL线程故障：
处理方法： 把握一个原则，一切以主库为准，进行解决。 如果出现问题，尽量进行反操作 最直接稳妥办法，重新构建主从。 暴力的解决方法： 方法一： stop slave; set global sql_slave_skip_counter=1; start slave; #将同步指针向下移动一个，如果多次不同步，可以重复操作。 方法二： /etc/my.cnf slave-skip-errors=1032,1062,1007 重启服务，生效。 常见错误代码： 1007：对象已存在 1032：无法执行DML 1062：主键冲突，或约束冲突。
主库，从库上，找到有问题的主键值的，update从库上的值(一切已主库为准)，update之后，再跳过这个错误。 stop slave; set global sql_slave_skip_count=1; start slave; 主库上操作： use test; create table t1 (id int primary key not null auto_increment,name varchar(10)); insert into t1(name) values('zhangsan'),('lishi'),('wangwu'),('ysj'); commit;
从库上操作： use test; select * from t1;
insert into t1(name) values('scl');
++++++++ 这时，再回到主库上的 test库，插入一条记录 insert into t1(name) values('wfz');
++++++ 这时，在从库上，查看slave 状态：
解决方法： (1)、在从库上，更新test库t1表第5行值，为主库test 库t1表，第5行的值（一切以主库为主） use test; update t1 set name='wfz' where id=5; (2)、stop slave; set global sql_slave_skip_counter=1; start slave; show slave status\G ****** 如果开启了GTID 从库操作： stop slave; reset master; set @@global.gtid_purged='7cd51543-8d03-11ea-83e7-000c29e6dd30:2-8'; 7cd51543-8d03-11ea-83e7-000c29e6dd30:2-8 就是，报错的时候，Retrieved_Gtid_Set: 的内容 上面这些命令的用意是，忽略7cd51543-8d03-11ea-83e7-000c29e6dd30:2-8 这个GTID事务，下一次事务接着从 9 这个GTID开始，即可跳过上述错误。 start slave; show slave status\G
++++++++++++++++++++++ 为了很大程度的避免 SQL 线程故障: (1)、把从库设为只读 用参数来控制的 show variables like '%read_only%'; | read_only | OFF | 针对普通用户 | super_read_only | OFF | 针对root用户 添加到配置文件中，重启。现在一般用得少。 (2)、使用读写分离中间件技术，使用较多。 atlas mycat proxySQL MaxScale +++++++++++++++ 5.6、主从延时监控及原因 ****** 5.5.6.1、主库方面原因 (1)、binlog写入不及时 sync_binlog=1 # 1 ，事务提交后，binlog立即写入磁盘 (2)、默认情况下主库的DUMP线程是，串行(一个一个的传)传输binlog 的，在并发事务量大时或者大事务，由于DUMP线程是串型工作的，导致传送日志较慢。 如何解决问题？ 必须开启GTID，使用Group commit方式，可以支持DUMP线程 并行操作。 必须开启双1 标准。 (3)、主库及其繁忙 慢语句、、、、、 锁等待 从库的个数多 网络延时 5.6.2、从库方面的原因 (1)、传统复制(classic)中，如果主库并发事务量很大，或者出现大事务，由于是SQL单线程，导致不农牧民传的日志有多少，一次只能执行一个事务 5.6版本有了GTID，可以实现多SQL线程，全是只能基于不同库的事务，进行回放。 5.7版本中，有了增强的GTID，基于事务，增加了seq_no机制，增加了新型的并发SQL线程，叫逻辑时钟(logical_clcok)，实现了事务级别的回放，把这种从库多线程SQL，称之为MTS技术。（多线程复制） (2)、主从硬件差异太大 (3)、主从的参数配置不一样 (4)、从库和主库的索引不一样 (5)、主从的版本不一样 主从延时的监控： 从库延时主库的时间（秒）： Seconds_Behind_Master: 0 主库方面原因的监控： 主库：show master status;
从库：show slave status; Master_Log_File: mysql-bin.000011 Read_Master_Log_Pos: 2230 从库方面原因监控： 判断是不是SQL原因，首先我们要知道，执行了多少日志，请求了多少日志 show slave status; 拿了多少： Connect_Retry: 5 Master_Log_File: mysql-bin.000011 Read_Master_Log_Pos: 2230 执行了多少： Relay_Log_File: dba01-relay-bin.000003 Relay_Log_Pos: 360 ++++++++++++++++++++++++++++++ Exec_Master_Log_Pos: 2230 #主库的位置，如果这个值和 Read_Master_Log_Pos 相差大了，就是从库的SQL线程导致慢了。 Relay_Log_Space: 2478 ##这是relaylog的位置