MySQL的复制

一、概述

复制解决的基本问题是让一台服务器的数据与其他服务器保持同步。MySQL支持两种复制方式：基于行的复制和基于语句的复制。

基于语句的复制（也称为逻辑复制）早在MySQL3.23版本中就存在，而基于行的复制方式在5.1版本中才被加进来。

新版本的服务器可以作为老版本服务器的备库，但反过来是不可行的。

通过复制可以将读操作指向备库来获得更好的读扩展，但对于写操作，除非设计得当，否则并不适合通过复制来扩展写操作。在一主多备的架构中，写操作会被执行多次，这时候这个系统的性能取决于写入最慢的那部分。

复制比较常见的用途：

数据分布、负载均衡、备份、高可用性和故障切换、MySQL升级测试。

复制的三个步骤：

1、在主库上把数据更改记录到二进制日志(Binary Log)中。

2、备库将主库上的日志复制到自己的中继日志(Relay Log)中。

3、备库读取中继日志中的时间，将其重放到备库数据上。

MySQL的复制架构有一个弱点：在主库上并发运行的查询在备库只能串行化执行，因为只有一个SQL线程来重放中继日志中的事件。

 

二、配置复制

 为MySQL服务器配置复制非常简单，分为以下几步：

• 在每台服务器上创建复制账号。(严格来讲不是必须每台，但推荐这么做)。
• 配置主库和备库。
• 通知备库连接到主库并从主库复制数据。

1、创建复制账号

给复制线程特殊权限

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@'192.168.0.%' IDENTIFIED BY 'p4ssword';

 我们在主库和备库创建该账号。

注意：

• 把账号限制在本地网络，因为这是一个特权账号。
• REPLICATION SLAVE是全局权限，不能限定某个数据库或表。过滤备份用其他方法，后面会介绍到。
• 实际上主库只需要REPLICATION SLAVE权限，并且不是每一端服务器都需要REPLICATION CLIENT权限，这么配置时方便出现问题时，主备互换角色。

2、配置主库和备库

主库：

在my.cnf文件中增加或修改如下内容：

log_bin=mysql-bin

server_id=10

实际值由你决定。

必须明确指定一个唯一的服务器ID。通常的做法是使用服务器IP地址的末8位。重启MySQL服务。

可以使用SHOW MASTER STATUS命令检查输出是否如下：

 文件名和后面的数字可能有所不同。positon是当前日志文件的位置。

 

备库：

在my.cnf中增加类似的配置，并重启服务：

log-bin=mysql-bin

server_id=2

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

log_slave_update=1

read_only=1

从技术上来说，有些选项并不总是必要的。但推荐这样配置。

read_only可以阻止没有特权的线程修改数据。

 

其他推荐配置：

主库：

sync_binlog=1  #证服务器崩溃时不会丢失事件。

innodb_flush_log_at_trx_commit=1  #保证每个commit都及时写入日志

innodb_support_xa=1  #MySQL5.0及以后版本的配置，支持跨存储引擎及二进制日志事务

log_bin="/var/lib/mysql/mysql-bin"  #明确指定日志文件位置，对多台服务器的管理有帮助

 

备库：

relay_log="path/to/logs/relay-bin"   #指定明确日志位置，可以避免多版本MySQL复制的bug

skip_slave_start  #避免崩溃后自动启动日志，给修复赢取时间

如果使用MySQL5.5并且不介意额外的fsync()的性能开销，最好设置一下选项：

sync_master_info=1

sync_relay_log=1

sync_relay_log_info=1

 

3、启动复制

告诉备库连接到主库，这一步不要通过修改my.cnf来配置，而是使用CHANGE MASTER TO语句。

 

mysql> CHANGE MASTER TO MASTER_HOST='server1',
        -> MASTER_USER='repl',
        -> MASTER_PASSWORD='p4ssword',
        -> MASTER_LOG_FILE='mysql-bin.000001',
        -> MASTER_LOG_POS=0;

 MASTER_LOG_FILE和MASTER_LOG_POS是主服务器上SHOW MASTER STATUS命令看到的文件和位置。位置设置成0，即从日志头开始复制。

 

上面的命令是连接主备库，下面是开始复制的命令：

mysql> START SLAVE；

可以通过SHOW SLAVE STATUS;查看备库复制情况。

 

4、从已有数据的数据库复制

上面的步骤是主库和备库都是新库，在都没有数据是，先配置复制。如果从一个已有数据的服务器复制，先要将备库和主库的数据进行同步。

需要三个条件让主库和备库保持同步：

• 在某个时间点的主库的数据快照。
• 主库当前二进制日志文件，和获得数据快照时在该二进制日志文件中的偏移量，称为日志文件坐标。可以通过SHOW MASTER STATUS命令获取这些值。
• 从快照时间到现在的二进制日志

如何克隆数据可以讲本书，这里极其简略的列举一些方法：

• 使用冷备份

关闭主库，把数据复制到备库。重启主库后，会使用一个新的二进制日志文件，将备库通过CHANGE MASTER TO指向新的文件其实处。

• 使用热备份

如果仅使用MyISAM表，可以在主库运行时使用mysqlhotcopy或rsync来复制数据。

• 使用mysqldump

如果只包含InnoDB表，可以使用以下命令来转存主库数据并将其加载到备库：

$ mysqldump --single-transaction --all-database --master-data=1 --host=server1 | mysql --host=server2

选项--single-transaction使得转存的数据为事务开始前的数据。如果使用非事务表，可以使用--lock-all-tables选项来确保一致性。

• 使用Percona Xtrabackup

Percona的Xtrabackup是一款开元的热备份工具。它能够在备份时不阻塞服务器的操作。

• 使用MySQL Workbench 的utilities工具集

工具集中有很多有用的工具，其中mysqldbcopy.exe或mysqldbcopy.py可以用来备份数据。

下面这个命令可以从主库备份数据到备库，备份后，自动启动备库的复制线程。

$ mysqldbcopy --source=root@localhost:3310 --destination=root@localhost:3311 test123 --rpl=master --rpl-user=rpl

 

5、复制过滤器

一般主库记录所有内容，使用备库来进行过滤。

在备库上，可以通过设置下列replicate_*的选项从中继日志中读取事件时进行过滤。

replicate_do_db

replicate_do_table

replicate_ignore_db

replicate_ignore_table

replicate_rewrite_do

replicate_wild_do_table

replicate_wild_ignore_table

 

三、复制拓扑

可以在任意主库和备库之间建立复制，记住下面的旗本原则：

• 每一个备库只能有一个主库。
• 每个备库必须有一个唯一的服务器ID
• 一个主库可以有多个备库（或一个备库可以有多个兄弟备库）
• 如果打开了log_slave_updates选项，一个备库可以把其主库上的数据变化传播到其他备库。

1、一主库多备库

一主多备的结构和基本配置差不多简单，因为备库之间根本没有交互，它们仅仅是连接到同一个主库上。

这种结构非常灵活，有一下一些用途：

为不同的角色使用不同的备库

把一台备库当做代用的主库，除了复制没有其他数据传输。

将一台备库放到远程数据中心，用作灾难恢复。

延迟一个或多个备库，以备灾难恢复。

使用其中一个备库，作为备份、培训、开发或者测试使用服务器。

 

2、主动-被动模式下的主-主复制

简单来讲就是两台服务器互为对方的备库，并确保只有一台服务器可写。

在两台服务器上执行如下设置后，会使其拥有对称设置：

• 确保两台服务器上有相同的数据。
• 启用二进制日志，选择唯一的服务器ID，并穿件复制账号。
• 启用备库更新的日志记录，这是故障转移和恢复的关键。
• 把被动服务器配置成只读。
• 启动每个服务器的MySQL实例。
• 将每个主库设置为对方的备库，使用新常见的二进制日志开始工作。

这种结构某种意义上类似于创建一个热备份，但可以使用这个“备份”来提供性能，比如用它来执行读操作、备份、“离线”维护以及升级等。真正的热备份做不了这些事。

因为配置是几乎是相同的，所以很容易让主动库和被动库互换角色，这使得故障转移和故障恢复很容易。

 

切换角色的步骤：

• 停止主动服务器上的所有写入。
• 在主动服务器上执行SET GLOBAL read_only=1，同时在配置文件里也设置一个read_only，防止重启后失效。但记住这不会阻止拥有超级权限的用户更改数据。如果想阻止所有人更改数据，可以执行FLUSH TABLES WITH READ LOCK。如果没有这么做，你必须kill所有的客户端连接以保证没有长时间运行的语句或者未提交的事务。
• 在主动服务器上执行SHOW MASTER STATUS并记录二进制日志坐标。
• 使用主动服务器上的二进制日志坐标在被动服务器上执行SELECT MASTER_POS_WAIT()。该语句将阻塞住，知道复制跟上主动服务器。
• 在被动服务器上执行SET GLOBAL read_only=0，这样就变换成主动服务器。
• 修改应用的配置，使其写入到新的主动服务器中。