MySQL复制优点、原理详解

OStack程序员社区-中国程序员成长平台 › 门户 › 编程›数据库

原作者: [db:作者] 来自: [db:来源] 收藏邀请

复制是将主数据库的DDL和DML操作通过二进制日志传到从库上，然后再从库重做，从而使得从库和主库保持数据的同步。MySQL可以从一台主库同时向多台从库进行复制，从库同时也可以作为其他从库的主库，实现链式复制。

MySQL复制的优点：

主库故障，可以快速切换至从库提供服务；
在从库执行查询操作，降低主库的访问压力；
在从库执行备份，避免备份期间对主库影响；

MySQL复制原理

1、MySQL主库在事务提交时会把数据变更作为事件Events记录在Binlog中，主库上的sync_binlog参数控制Binlog日志刷新到磁盘；

2、主库推送Binlog中的事件到从库的Relay Log，之后从库根据Relay Log进行重做，通过逻辑复制来达到主从库的数据一致；

MySQL通过3个线程来完成主从库间的数据复制：其中Binlog Dump线程运行在主库上，I/O线程和SQL线程运行在从库上。当在从库启动复制（Start Slave）时，首先创建I/O线程连接主库，主库随后创建Binlog Dump线程读取数据库事件并发送给I/O线程，I/O线程获取到事件数据后更新到从库的Relay Log中，之后从库上的SQL线程读取Relay Log中更新的数据库事件并应用，

如下图所示：

查看主库：

mysql> show processlist\G; 
*************************** 1. row *************************** 
   Id: 3 
  User: root 
  Host: 10.24.33.187:54194 
   db: NULL 
Command: Sleep 
  Time: 176 
 State:  
  Info: NULL 
*************************** 2. row *************************** 
   Id: 4 
  User: root 
  Host: 10.24.33.187:54195 
   db: NULL 
Command: Sleep 
  Time: 176 
 State:  
  Info: NULL 
*************************** 3. row *************************** 
   Id: 8 
  User: root 
  Host: localhost 
   db: test 
Command: Query 
  Time: 0 
 State: starting 
  Info: show processlist 
*************************** 4. row *************************** 
   Id: 12 
  User: repl 
  Host: dsz884.hcg.homecredit.net:39731 
   db: NULL 
Command: Binlog Dump  --Binlog Dump线程 
  Time: 87 
 State: Master has sent all binlog to slave; waiting for more updates --由此可见，以“推送”的方式同步 
  Info: NULL 
4 rows in set (0.00 sec) 
 
ERROR:  
No query specified

查看备库：

mysql> show processlist\G; 
*************************** 1. row *************************** 
   Id: 1 
  User: system user 
  Host:  
   db: NULL 
Command: Connect 
  Time: 4427 
 State: Waiting for master to send event 
  Info: NULL 
*************************** 2. row *************************** 
   Id: 2 
  User: system user 
  Host:  
   db: NULL 
Command: Connect 
  Time: 2044 
 State: Slave has read all relay log; waiting for more updates 
  Info: NULL

由此可见，MySQL复制是异步的，从库和主库存在一定的延时。

复制相关的日志

1、BinlogBinlog会记录mysql中所有的数据修改操作，可以通过如下方式查看Binlog的格式，对应有三种，分别为Statement、Row和Mixed：

mysql> show variables like '%binlog_format%'; 
+---------------+-------+ 
| Variable_name | Value | 
+---------------+-------+ 
| binlog_format | ROW  | 
+---------------+-------+ 
1 row in set (0.00 sec)

2、Relay LogRelay Log的文件格式、内容和Binlog一样，唯一区别是从库上的SQL线程执行完当前Relay Log中的事件后，SQL线程会自动删除该Relay Log，从而释放空间。为保证从库Crash重启后，从库的I/O线程和SQL线程仍能知道从哪里开始复制，从库默认会创建两个日志文件master.info和relay-log.info来保存复制的进度，这两个文件分别记录了从库的I/O线程当前读取主库Binlog的进度和SQL线程应用Relay Log的进度。

mysql> show slave status \G; 
*************************** 1. row *************************** 
        Slave_IO_State: Waiting for master to send event 
         Master_Host: 10.24.33.186 --主库IP 
         Master_User: repl --主库用于主从复制的用户账号 
         Master_Port: 3306 --主库端口 
        Connect_Retry: 60  
       Master_Log_File: mysql-bin.000005 --从库I/O线程当前读取主库Binlog文件名 
     Read_Master_Log_Pos: 4356 --从库I/O线程读取主库Binlog的位置 
        Relay_Log_File: strong-relay-bin.000006 --SQL线程正在应用的Relay Log 
        Relay_Log_Pos: 320 --Relay Log的位置 
    Relay_Master_Log_File: mysql-bin.000005 --Relay Log对应的Binlog 
       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: 4356 --SQL线程正在应用Relay Log的位置对应的Binlog的位置 
       Relay_Log_Space: 1153 
       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: 2a3e3fd9-0587-11e8-bdb8-0800272325a8 
       Master_Info_File: /usr/local/mysql-5.7.21-el7-x86_64/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) 
 
ERROR:  
No query specified 
 
mysql>

MySQL复制方式

Binlog的格式有三种，分别对应了MySQL复制的3种技术。

MySQL复制架构

MySQL复制的常见架构有一主多从复制架构、多级复制架构和双主复制（Dual Master）架构。

1、一主多从架构在主库读请求压力非常大的场景下，通过配置一主多从复制架构实现读写分离，把对实时性要求不是特别高的读取请求通过负载均衡分布到多个从库上，从而降低主库的读取压力，如图：

2、多级复制架构一主多从架构能解决大部分读请求压力特别大的场景的需求，由于MySQL的复制是主库推送Binlog到从库，主库的I/O压力和网络压力会随着从库的增加而增加（每个从库都会在主库上有一个独立的Binlog Dump线程来发送Binlog事件），而多级复制架构解决了一主多从场景下，主库额外的I/O和网络压力的场景，如图：

3、双主复制/Dual Master架构双主复制/Dual Master架构特别适合于DBA做维护需要主从切换的场景，通过该架构避免了重复搭建从库的麻烦，如图：