MySQL Replication

0 序言

MySQL Replication 是 MySQL 中的一个功能，允许从一个 MySQL 数据库服务器（称为主服务器或 master）复制数据和数据库结构到另一个服务器（称为从服务器或 slave）。这种复制是异步的，意味着从服务器不需要一直连接到主服务器。主服务器上的数据更改（如 INSERT、UPDATE、DELETE 语句）会被记录在一个叫做二进制日志（binary log）的特殊日志文件中。然后，从服务器可以连接到主服务器并读取这些更改，并在自己的数据库上应用这些更改。

MySQL数据库复制操作大致可以分成3个步骤：

步骤01 主服务器将数据的改变记录到二进制日志（binary log）中。

步骤02 从服务器将主服务器的binary log events复制到它的中继日志（relay log）中。

步骤03 从服务器重做中继日志中的事件，将数据的改变与从服务器保持同步。

1 实现主从复制

1.1 配置主服务器（Master）

设置唯一的服务器 ID：在 my.cnf 或 my.ini 配置文件中，为每个服务器设置一个唯一的 server-id。
启用二进制日志：确保 log-bin 选项已启用，以记录所有更改。
创建复制用户：在主服务器上创建一个专门的复制用户，并为其分配复制权限。

1.2 配置从服务器（Slave）

设置唯一的服务器 ID：同样，在 my.cnf 或 my.ini 配置文件中，为每个从服务器设置一个唯一的 server-id。
指定主服务器信息：在从服务器上，配置 master_host、master_user、master_password、master_log_file 和 master_log_pos 等参数，以指定主服务器的位置和复制起始点。
启动从服务器复制线程：在从服务器上执行命令，启动 SQL 线程和 IO 线程，开始复制过程。

2 日常管理和维护

2.1 了解服务器的状态

SHOW SLAVE STATUS\G

在SHOW SLAVE STATUS的输出中，关注以下几个字段来了解复制进度：

Slave_IO_Running: 显示IO线程的状态。它应该是Yes，表示IO线程正在运行并读取主服务器的二进制日志。
Slave_SQL_Running: 显示SQL线程的状态。它也应该是Yes，表示SQL线程正在运行并执行从IO线程读取的中继日志中的事件。
Seconds_Behind_Master: 显示从服务器复制延迟的秒数。如果这个数字很大，表示从服务器落后于主服务器很多。
Relay_Log_File 和 Relay_Log_Pos: 显示当前正在读取的中继日志文件和位置。
Master_Log_File 和 Master_Log_Pos: 显示主服务器上二进制日志的当前位置和文件名，从服务器将从这个位置开始读取。

2.2 服务器复制出错的原因

问题一：出现“log event entry exceeded max_allowed_pack”错误。

如果在应用中使用大的BLOG列或者长字符串，那么在从服务器上回复时可能会出现“log event entry exceeded max_allowed_pack”错误，这是因为含有大文本的记录无法通过网络进行传输，解决方法是在主从服务器上添加max_allowed_packet参数（默认设置是1MB），具体如下：

同时，在my.cnf里设置max_allowed_packet=16MB，数据库重新启动之后该参数将有效。

问题二：多主复制时的自增长变量冲突问题。

大多数情况下使用一台主服务器对一台或者多台从服务器，但是在某些情况下可能会将多个服务器配置为复制主服务器，所以使用auto_increment时应采取特殊步骤以防止键值冲突，否则插入行时多个主服务器会试图使用相同的auto_increment值。

在多主服务器复制到从服务器过程中，迟早会发生主键冲突，为了解决这种情况，可以重新设置不同主服务器的这两个参数，比如在A数据库服务器上设置auto_increment_increment=1、auto_increment_offset=1，在B数据库服务器上设置auto_increment_increment=1、auto_increment_offset=0。