MySQL复制

以下内容来自“高性能MySQL”的第10章。

 

一、 概述：复制解决的基本问题是让一台服务器的数据与其他服务器保持同步。一个主库可以对应多个备库，备库本身也可以配置成另一台服务器的主库。主备库之间可以有多重不同的组合方式。

通过复制可以将读操作指向备库来获取更好的读扩展，但是对于写操作，除非设计得当，否则并不适合通过复制来扩展写操作。

MySQL支持两种复制方式：基于行的复制和基于语句的复制。基于语句的复制也叫做逻辑复制，在MySQL3.23中就已经存在，而基于行的复制在5.1版中才被加入。

两种复制方式都是通过在主库上记录二进制日志，在备库重放日志的方式来实现异步的数据复制。因此，在同一时间点上备库可能与主库存在不一致。并且由于大的语句执行时间的不同，无法保证主备之间的延迟。

MySQL复制大部分是向后兼容的，新版本服务器可以做为老版本服务器的备库，但反过来通常是不行的，因为可能无法解析新版本采用的新特性或语法。

复制通常不会增加主库的开销。出于备份或数据恢复的目的，启用二进制日志的开销往往是必要的；备库对主库增加的负载主要是在请求从主库读取旧的二进制日志文件时，可能会造成跟高的I/O开销；锁竞争可能会阻碍事务的提交；如果从一个高吞吐量的主库上复制到多个备库时，唤醒多个复制线程发送时间的开销将会累加。

 

二、 复制比较常见的用途：

1. 数据分布：可以在不同的地理位置，不同的数据中心来分布书库备份。

2. 负载均衡：对于读密集型应用可以将读操作分布到多个服务器上。

3. 备份：对于备份，复制是一项很有意义的技术补充，但复制既不是备份也不能取代备份。

4. 高可用性和故障切换：复制能帮助应用程序避免MySQL单点失败。

5. MySQL升级测试：在升级全部数据库实例前在一个备库中进行测试，保证查询能够在备库按照预期执行。

 

三、 复制如何工作：

1. 主库上把数据更改记录到二进制日志中。MySQL会按照事务提交的顺序而不是每条语句执行的顺序来记录二进制日志。在记录二进制日志后，主库会告诉存储引擎可以提交事务了。

2. 备库将主库上的日志复制到自己的中继日志中。首先：备库会启动一个工作线程跟主库建立一个普通的客户端连接；然后在主库上启动一个特殊的二进制转储线程（该线程没有对应的SQL命令），该线程会读取主库上的二进制日志中的事件，但不会对事件进行轮询，如果该线程追上了主库则进入睡眠状态，直到主库发送新号量通知有新的事件产生时才会被唤醒；备库I/O线程会将接收到的事件记录到中继日志中。

3. 备库读取中继日志中的事件，将其重放到备库数据之上。备库中有单独的线程会将中继日志中读取事件并在备库执行。因为只有一个SQL线程来重放中级日志中的事件，因此会成为很多工作负载的性能瓶颈（可以搜索淘宝丁奇写的一篇“MySQL 主从原理、问题、解决方案和应用”的pdf，其中有解决方案）。

 

四、 复制配置：

假设有服务器server1（IP：192.168.0.1）和服务器server2（IP：192.168.0.2），需要进行的步骤如下：

1. 在每台服务器上创建复制帐号：MySQL会给复制线程赋予一些特殊的权限，备库的I/O线程会建立一个到主库的TCP/IP连接，因此必须由主库提供一个相当权限的用户。

可以通过以下语句创建：

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave_user@'192.168.0.%' IDENTIFIED BY 'password';

其中“REPLICATION CLIENT”权限不是必要的；对帐户slave_user的限制如果有需要可以具体到单独的IP上。

*复制帐户不一定要有主库上的“REPLICATION CLIENT”权限，但是：用来监控和管理复制的帐号需要“REPLICATION CLIENT”权限，且针对这两种目的使用同一帐号更容易；如果主库上建立帐户并将数据克隆到备库上，则备库也设置成了主库需要的配置，以后可以方便的交换主备库的角色。

2. 配置主库和备库：

（1）假定server1是主库，需要打开二进制日志并指定一个唯一的服务器ID。在配置文件my.cnf中（window系统中是my.ini）找到[mysqld]的位置，在其中加入以下内容：

server_id=1

log_bin=mysql-bin

如果其中的log_bin项是新加的则需要重新启动MySQL。启动后输入 show master status命令查看，如果显示有log文件的名称和对应的位置，则配置成功。

（2）在备库上需要调整的配置如下，同样需要在my.cnf中（window系统中是my.ini）找到[mysqld]的位置加入。

server_id=2

log_bin=mysql-bin

relay_log=/var/lib/mysql/mysql-relay-bin

log_slave_updates=1

read_only=1

其中relay_log指定了中继日志的位置和命名，log_slave_updates项表示允许备库将重放的事件记录到自身的二进制文件中。read_only选项会阻止任何没有特权权限的线程修改数据。

* 不要在配置文件中设置“master_host”或“mast_port”等选项，这些是老的配置方式，已经被废弃。

3. 通知备库连接到主库并从主库复制数据：通过在备库中执行以下语句来连接到主库：

CHANGE MASTER TO MASTER_HOST='192.168.0.1',MASTER_PORT=3306,MASTER_USER='slave_user',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=107;

STARTSLAVE;

第一条语句替换了在my.cnf中的配置，并且允许以后调整主库时无须重启备库。其中“MASTER_LOG_FILE”和“MASTER_LOG_POS”可以从主库执行“show master status”命令的结果中获取。

第二条语句表示可以开始进行复制。之后执行“show slave status”命令，如果结果中“Slave_IO_Running”和“Slave_SQL_Running”的结果都是YES，表明已经配置成功。可以在主库上执行一些更新操作来进行检验。

 

五、 备库初始化：如果是已经运行了一段时间的主库要设置备库，则要先处理好备库数据的初始化或者从其它服务器克隆数据到备库。具体可以根据实际情况使用备份或工具来进行。

 

六、 复制模式的比较：

在MySQL5.0及之前只支持基于语句的复制（逻辑复制），主库会记录造成数据更改的查询，备库读取并重放事件时相当于在备库上重新执行一遍。好处在于：简单，二进制日志里的事件更加紧凑。缺点在于：语句中可能有依赖于某些元数据信息（如时间戳）执行时会有偏差，且存储过程和触发器也可能存在问题，另外更新只能是串行的需要更多的锁而且不是所有的数据库引擎都支持。

从MySQL5.1开始支持基于行的复制，主库会将实际数据记录在二进制日志中（和其他数据库比较相像）。好处在于：可以正确的复制每一行，有些语句可以更加有效地复制。缺点在于：在使用条件更新批量调整数据时复制代价比较大。

MySQL能够在两种复制模式间动态切换，默认情况下使用的是基于语句的复制，但如果发现语句无法被正确复制就切换到基于行的复制模式。还可以根据需要设置会话级别的变量binlog_format，控制二进制日志格式。

两种模式的比较：

基于语句的复制模式：

优点：允许更加灵活的操作；服务器上的变更以更加容易理解的方式运行，当出现问题时容易定位。

缺点：如果正在使用触发器或存储过程，就不要使用基于语句的复制模式，除非能够清楚地确定不会碰到复制问题。

基于行的复制模式：

优点：几乎没有基于行的复制模式无法处理的场景；可能减少锁的使用，因为并不要求这种强串行化是可重复的；在一些情况下会记录发生改变前的数据，可能有利于某些数据恢复；可以占用更少的CPU；基于行的复制能够帮助更快找到并解决数据不一致的情况。

缺点：无法知道服务器正在做什么，因此出现问题时可能很难找到问题所在；无法处理诸如备库修改表的schema的情况。