浅析MySQL复制

168次阅读

没有评论

共计 5454 个字符，预计需要花费 14 分钟才能阅读完成。

MySQL 的复制是基于 binlog 来实现的。

流程如下

浅析 MySQL 复制

涉及到三个线程，主库的 DUMP 线程，从库的 IO 线程和 SQL 线程。

1. 主库将所有操作都记录到 binlog 中。当复制开启时，主库的 DUMP 线程根据从库 IO 线程的请求将 binlog 中的内容发送到从库。

2. 从库的 IO 线程接受到主库 DUMP 线程发送的 binlog 事件后，将其写到本地的 relay-log。

3. 从库的 SQL 线程重放 relay-log 中的事件。

实际上，在 MySQL 4.0 之前，复制只有两个线程，master 和 slave 端各一个。在 Slave 端，该线程同时负责接收主库发来的 binlog 事件，也负责事件的重放，所以没有使用 relay-log，这样容易导致，当 binlog 事件的重放速度较慢时，会影响 binlog 事件的接受。

复制的搭建

基本步骤如下：

1. 配置主库和从库

2. 创建复制的账号

3. 创建主库一致性快照

4. 根据主库的快照，建立从库

5. 开启复制

详细步骤如下

1. 配置主库和从库

主库

开启 binlog 并设置 server-id

[mysqld]
log-bin=mysql-bin
server-id=1

在一组复制结构中，每个服务器必须配置一个唯一的 server-id。该值的有效范围为 1~2³²-1。

如果 server-id 设置为 0 的话，则 MySQL 会自动将它更改为 1。此时，对复制没有影响。

如果 server-id 没有显式设置的话，则 MySQL 同样会将它设置为 1，但是从连接的时候，IO 线程会报错

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Misconfigured master - server_id was not set'

从库

设置 server-id

[mysqld]
server-id=2

在从服务器上，可以选择不开启 binlog。当开启了 binlog 后，如果想把重放的时间同样也记录到 binlog 中，可将 log_slave_updates 参数设置为 1。

设置完毕后，重启数据库

2. 创建复制账号

主库上执行

mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';

3. 创建主库一致性快照

在这里，我用 mysqldump 来创建数据库快照

# mysqldump –master-data=2 -R –single-transaction -A > 3306_20160815.sql

在生成的备份文件中，我们可以得到在对数据库执行一致性快照时主的状态，包括二进制文件的名称及位置

# head -30 3306_20160815.sql

...
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000016', MASTER_LOG_POS=120;
...

4. 根据主库的快照，建立从库

# mysql < 3306_20160815.sql

5. 开启复制

根据第 3 步获取到的主库状态执行 CHANGE MASTER TO 命令

mysql> CHANGE MASTER TO
         MASTER_HOST='master_host_name',
         MASTER_USER='replication_user_name',
         MASTER_PASSWORD='replication_password',
         MASTER_LOG_FILE='recorded_log_file_name',
         MASTER_LOG_POS=recorded_log_position;

在执行 CHANGE MASTER TO 命令后，从库并不会连接到主库，而只是将这些信息写入到从库数据目录下的 master.info 和 relay-log.info 中。如果没有显式指定 MASTER_LOG_FILE，则默认为空，因为此时还没有和主库建立连接，并不知道主库 binlog 的文件名称，只有等到和主库建立连接时才能知道。如果 MASTER_LOG_POS 没有显式指定，则默认为 4，即忽略 binlog 的头 4 个字节，从第一个事件开始读取。

开启复制功能

mysql> start slave;

查看复制的状态

mysql> show slave status\G

重点关注以下两个变量，如果为 YES，则代表复制搭建成功。

...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...

注：以上搭建场景是基于主库上已经有一定的数据。如果在全新的环境中搭建，实际上会更简单。

只需获取主库的状态信息即可

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000016 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec

MySQL 复制的格式

MySQL 的复制有三种格式：

Statement-based replication（SBR）

基于语句的复制，即 master 上执行的 SQL 语句原封不动的在 slave 上重放。该复制格式在 MySQL 3.23 即出现了。

优点：

1. 节省 binlog 的空间。

2. 可用于审核，毕竟所有的 DML 语句都是直接记录在 binlog 中。

缺点：

1. 很多函数在主从上执行的结果并不一致

LOAD_FILE()，UUID(), UUID_SHORT()，USER()，FOUND_ROWS()，SYSDATE()，GET_LOCK()，IS_FREE_LOCK()，IS_USED_LOCK()，MASTER_POS_WAIT()，RAND()，RELEASE_LOCK()，SLEEP()，VERSION()

2. DELETE 和 UPDATE 操作，带了 LIMIT 子句，却没有带 ORDER BY，可能导致主从执行的结果并不一致。

3. 相对于基于行的复制，master 上执行 INSERT … SELECT 操作需要更多的行锁。

4. 自定义函数（UDF）必须确保执行的结果是确定的。

Row-based replication（RBR）

基于行的复制，MySQL 5.1 引入的，相对于 SBR，它记录的是 DML 操作涉及到的行。

优点：

1. 安全。master 上所有的变更都能复制到 slave 上。

2. 在执行以下操作时，只需更少的行锁。

INSERT … SELECT

带有 AUTO_INCREMENT 列的 INSERT 操作

UPDATE 和 DELETE 中，WHERE 条件没有用上索引。

缺��：

1. 会产生大量的日志。

譬如一张表有 1w 条记录，如果我不带任何条件执行 delete 操作，则在基于 statement 的复制中，在 binlog 中只会记录 delete from table 这一条记录，但是在基于 row 的复制中，则会记录 1w 笔记录，每笔记录类似于 delete from table where ..。

这会带来以下问题

1> 如果利用 binlog 进行恢复，会需要更长的时间.

2> 在写数据到 binlog 中时，因为数据量大，会导致 binlog 的锁定时间较长，影响数据库的并发。

3> 较大的日志会对磁盘 IO 和网络 IO 产生较大的压力。

4> 增大 slave 的延迟。

2. 不会对二进制日志进行校验。

3. 不推荐基于库级别的复制

包括 –replicate-do-db, –replicate-ignore-db, –replicate-rewrite-db

MIXED

MIXED 是上述两者的的结合，会根据执行的语句和涉及的存储引擎自动在这两种模式间切换。默认情况下，采用的是基于语句的复制模式，在遇到 unsafe statements 时，会切换为基于行的复制模式。

关于切换的条件，可参考官方文档：http://dev.mysql.com/doc/refman/5.7/en/binary-log-mixed.html

在 MySQL 5.7.7 之前，默认的是基于语句的复制模式，从 MySQL 5.7.7 开始，默认基于行的复制模式。

复制中涉及的文件

relay-log

relay-log 保存着从库 IO 线程从主库读取到的 binlog 事件。和 binlog 格式一样，可通过 mysqlbinlog 解析其中的内容。

可通过配置 relay_log 和 relay_log_index 参数设置 relay-log 和 relay-log.index 文件的名称。

relay-log 在如下情况下会发生切换：

1. slave IO 线程启动的时候。

2. 执行 FLUSH LOGS 操作的时候。

3. 达到参数 max_relay_log_size 设置的大小，默认为 0，即以 max_binlog_size 的值作为 max_relay_log_size 的大小。

slave SQL 线程在重放完一个 relay-log 文件中的所有事件后，会自动删除 relay-log 文件（由 relay_log_purge 参数控制），所以没有显式删除 relay-log 的命令。

master.info

该文件保存了主库的主机名，端口，复制账号的用户名和密码，以及从库接受主库 binlog 事件的位置信息，通过这些位置信息，从库的 IO 线程知道下次从哪里获取主库的 binlog 事件。

该位置信息对应于 show slave status\G 中的 Master_Log_File 和 Read_Master_Log_Pos。

如下所示：

# cat master.info

23
mysql-bin.000014
120
192.168.244.10
repl
repl
3306
60
0





0
1800.000

0
cad449f2-5d4f-11e6-b353-000c29c64704
86400


0

relay-log.info

该文件记录从库的重放信息，这样即便数据库发生重启，SQL 线程也知道该从哪里开始重放。

该位置信息对应于 show slave status\G 中的 Relay_Master_Log_File 和 Exec_Master_Log_Pos。

# cat relay-log.info

7
./mysqld-relay-bin.000024
283
mysql-bin.000014
120
0
0
1
1

Crash-Safe Replication

slave 每次接受 binlog 或者应用 relay-log 时，都要修改 master.info 或 relay-log.info 的信息并同步到磁盘中，这会导致大量的磁盘操作，所以，一般都是采用异步方式来对这两个文件进行更改。虽然每次都会修改这些文件，但是持久化到磁盘却留给操作系统处理。在内存与磁盘中的信息不一致时，如果此时操作系统宕机，内存中的信息将无法及时同步到磁盘中。操作系统恢复后，master.info 和 relay-log.info 的数据依然是旧的，所以会从已经执行的部分重新执行。

MySQL 5.6 为了改善这个问题，提供了两个参数，可将 master.info 和 relay-log.info 中的内容保存在表中而不是磁盘文件中。

master-info-repository

可设置为 TABLE 或 FILE，如果使用 FILE，则复制的位置信息依旧保存在 master.info 中，如果设置为 TABLE，则信息报保存在 mysql.slave_master_info 中，默认为 FILE。

relay-log-info-repository

可设置为 TABLE 或 FILE，如果使用 FILE，则应用 relay-log 的信息会保存在 relay-log.info 中，如果设置为 TABLE，则保存在 mysql.slave_relay_log_info 中，默认为 FILE。

通过将上述两个变量设置为 TABLE，可实现“Crash-Safe Replication”，上述的相关操作都会放到一个事务中进行。

总结

1. 启用复制功能并不会增加服务器太多的开销，主要是开启 binlog 带来的开销，包括 binlog 文件的追加写操作开销，以及系统调用 fsync 带来的开销。

2. MySQL 的复制功能具有向后兼容性，因为较新版本的 MySQL 的 binlog 中会进入新的事件类型。所以，可以将较新版本的 MySQL 作为从库。

3. MySQL 复制是异步的。

4. MySQL 5.6 开始新增延迟复制功能，由 master_delay 参数来控制。

参考