MySQL主从复制原理
一、主从复制涉及的文件
主库: binlog
从库:relaylog 中继日志
master.info 主库信息文件
relaylog.info relaylog应用信息
二、主从复制中涉及的线程
主库: binlog_dump Thread 简称:DUMP_T
从库:SLAVE_IO_THREAD 简称:IO_T
SLAVE_SQL_THREAD
三、主从复制原理
(1)主库执行change master to 命令 (主库的链接信息+复制的起点);
(2)从库会将以上信息记录到master.info
(3)从库执行start slave 从库立即开启 SLAVE_IO_THREAD 和SLAVE_SQL_THREAD
(4)从库IO_T获取到master.info中的user,port,pass,binlog的位置信息
(5)从库IO_T请求连接主库,主库专门提供一个Binlog_DUMP Thread 负责和SLAVE_IO Thread交互
查看主库的连接进程
mysql> show processlist\G *************************** 1. row *************************** Id: 2 User: replicate Host: 192.168.43.7:56458 db: NULL Command: Binlog Dump Time: 16 State: Master has sent all binlog to slave; waiting for more updates Info: NULL
(6)IO_T根据 change master to中的日志信息,请求新的binlog
(7)主库通过Binglog_DUMP Thread将最新的binlog,通过网络传输给从库的SLAVE_IO_THREAD
(8)SLAVE_IO_THREAD接收到新的binlog,存储到TCP/IP缓存中,从库立即返回ACK给主库,并更新master.info
(9)SLAVE_IO THREAD将TCP/IP中的数据转储到磁盘的relylog中
(10)SLAVE_SQL_THREAD读取relylog中,获取到上次执行过的位置信息,从上次的位置往后回放最新的relylog,并根新relylog信息
(12)从库会自动purge应用过的relylog并清理
(13)一旦主从构建成功,主库发生新的变化,会通过Binglog_DUMP进程,发送信号给
四、监控主从复制
mysql> show slave status\G *************************** 1. row ***************************
主库相关的信息:
Slave_IO_State: Waiting for master to send event Master_Host: 192.168.43.6 Master_User: replicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 318
从库relay相关的应用信息:
Relay_Log_File: MySQLSlver-relay-bin.000005 Relay_Log_Pos: 531 Relay_Master_Log_File: mysql-bin.000005
从库运行线程状态:
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: 318 Relay_Log_Space: 909 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: d67bf815-0ff1-11ed-a11f-000c29aa5c2f Master_Info_File: /data/mysql/data/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: 1 row in set (0.00 sec)
五、主从复制故障
从库IO线程故障:
(1)连接主库 :网络故障、连接信息变更、防火墙、连接数上线、连接故障的话状态变为connecting
解决办法:stop slave; reset slave all; change master to ; start slave
(2)请求Binlog :主库没有开启binlog;binlog损坏,reset master,
(3)存储Binlog
从库有数据,主库新增重复的数据: sql语句在从库不能执行,
解决办法一:一切以主库为准
解决办法二跳过报错:stop slave;set global sql_slave_skip_counter=1; start slave;
解决办法三:在配置文件中直接跳过
vi /etc/my.cnf
slave-skip-errors=1032,1062,1007 #1007对象已经存在,1032无法执行DML,1062主键冲突,约束冲突;
为了避免SQL线程故障,解决办法:
从库只读;
使用读写分离中间件;
六、主从延时
主库方面原因:(1) binlog写入不及时,查看设置(sync_binlog=1)
(2)默认情况下dump_t是串行传输日志,在并发量大的情况下,由于 dump_t是串行工作的,导致binglog传输慢
解决办法:开启GTID,使用Group commit方式,可以支持DUMP_T并行传输
(3)主库及其繁忙,
(4)锁等待;
(5)网络繁忙
从库方面的原因:(1)传统复制中,由于是单SQL线程,不管传多少日志,只能一次执行一个事务
(2)MySQL5.7,有了增强的GTID,增加了新型的SQL并发线程;
(3)主从硬件、配置、索引有差异;
查看relylog和binlog之间的对应关系:
[root@MySQLSlver data]# cat relay-log.info 7 ./MySQLSlver-relay-bin.000009 367 mysql-bin.000009 154 0 0 1
七、延时从库配置
7.1延时从库
SQL线程延时时候,数据已经写入relylog中,SQL线程慢点运行,一般建议3-6小时;
配置从库延时:
mysql> stop slave; Query OK, 0 rows affected (0.02 sec) mysql> change master to master_delay=300; Query OK, 0 rows affected (0.09 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status \G SQL_Delay: 300 SQL_Remaining_Delay: NULL