mysql复制技术的特点_MySQL复制优点、原理详解

复制是将主数据库的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 processlistG;

*************************** 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 processlistG;

*************************** 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做维护需要主从切换的场景，通过该架构避免了重复搭建从库的麻烦，如图：