MySQL 复制

MySQL 复制

MySQL 从 3.23 版本开始提供复制的功能。复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到复制服务器（也叫从服务器）上，然后在从服务器上对这些日志重新执行（也叫重做），从而使得从服务器和主服务器的数据保持同步。

MySQL 支持一台主服务器同时向多台从服务器进行复制，从服务器同时也可以作为其他服务器的主服务器，实现链状的复制。

MySQL 复制的优点主要包括以下 3 个方面：

• 如果主服务器出现问题，可以快速切换到从服务器提供服务；
• 可以在从服务器上执行查询操作，降低主服务器的访问压力；
• 可以在从服务器上执行备份，以避免备份期间影响主服务器的服务。

注意：由于 MySQL 实现的是异步的复制，所以主从服务器之间存在一定的差距，在从服务器上进行的查询操作需要考虑到这些数据的差异，一般只有更新不频繁的数据或者对实时性要求不高的数据可以通过从服务器查询，实时性要求高的数据仍然需要从主数据库获得。

安装配置

MySQL 的复制至少需要两个 MySQL 服务，这些 MySQL 服务可以分布在不同的服务器上，也可以在一台服务器上启动多个服务。复制配置的步骤比较简单，下面进行详细介绍。

• 确保主从服务器上安装了相同版本的数据库。因为复制的功能在持续的改进中，所以在可能的情况下推荐安装最新的稳定版本。
• 在主服务器上，设置一个复制使用的账户，并授予 REPLICATION SLAVE 权限。这里创建一个复制用户 rep1，可以从 IP 为 192.168.1.101 的主机进行连接：

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.101' IDENTIFIED BY '1234test';
Query OK, 0 rows affected (0.00 sec)

• 修改主数据库服务器的配置文件 my.cnf，开启 BINLOG，并设置 server-id 的值。这两个参数的修改需要重新启动数据库服务才可以生效。

[mysqld]
log-bin = /home/mysql/log/mysql-bin.log
server-id = 1

• 在主服务器上，设置读锁定有效，这个操作是为了确保没有数据库操作，以便获得一个一致性的快照：

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

• 然后得到主服务器上当前的二进制日志名和偏移量值。这个操作的目的是为了在从数据库启动以后，从这个点开始进行数据的恢复。

mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000039 | 102 | | |
+------------------+----------+--------------+------------------+
 1 row in set (0.00 sec)

• 现在主数据库服务器已经停止了更新操作，需要生成主数据库的备份，备份的方式有很多种，可以直接在操作系统下 cp 全部的数据文件到从数据库服务器上，也可以通过mysqldump 导出数据或者使用 ibbackup 工具进行数据库的备份，这些备份操作的步骤已经在第 27 章中有详细介绍，这里就不再一一说明。如果主数据库的服务可以停止，那么直接cp 数据文件应该是最快的生成快照的方法：

[mysql@db3 db]$ tar -cvf data.tar data
data/
data/test1/
data/test1/db.opt
...

• 主数据库的备份完毕后，主数据库可以恢复写操作，剩下的操作只需要在从服务器上执行：

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

• 将主数据库的一致性备份恢复到从数据库上。如果是使用.tar 打包的文件包，只需要解开到相应的目录即可。
• 修改从数据库的配置文件 my.cnf，增加 server-id 参数。注意 server-id 的值必须是唯一的，不能和主数据库的配置相同，如果有多个从数据库服务器，每个从数据库服务器必须有自己唯一的 server-id 值

[mysqld]
server-id = 2

• 在从服务器上，使用--skip-slave-start 选项启动从数据库，这样不会立即启动从数据库服务上的复制进程，方便我们对从数据库的服务进行进一步的配置：

[mysql@master1 mysql_home]$ ./bin/mysqld_safe --skip-slave-start &
[1] 8768
[mysql@master1 mysql_home]$ Starting mysqld daemon with databases from 
/home/mysql/sysdb/dat

• 对从数据库服务器做相应设置，指定复制使用的用户，主数据库服务器的 IP、端口以及开始执行复制的日志文件和位置等，具体语法如下：

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;

举例说明如下：

mysql> CHANGE MASTER TO
 -> MASTER_HOST='192.168.1.100', 
 -> MASTER_PORT=3306,
 -> MASTER_USER='repl',
 -> MASTER_PASSWORD='1234test',
 -> MASTER_LOG_FILE='mysql-bin.000039',
 -> MASTER_LOG_POS=102;
Query OK, 0 rows affected (0.10 sec)

• 在从服务器上，启动 slave 线程：

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

• 这时 slave 上执行 show processlist 命令将显示类似如下进程：

mysql> show processlist \G
*************************** 1. row ***************************
 Id: 1
 User: root
 Host: localhost
 db: NULL
Command: Query
 Time: 0
 State: NULL
 Info: show processlist
*************************** 2. row ***************************
 Id: 2
 User: system user
 Host: 
 db: NULL
Command: Connect
 Time: 68
 State: Waiting for master to send event
 Info: NULL
*************************** 3. row ***************************
 Id: 3
 User: system user
 Host: 
 db: NULL
Command: Connect
 Time: 168
 State: Has read all relay log; waiting for the slave I/O thread to update it
 Info: NULL
3 rows in set (0.00 sec)

这表明 slave 已经连接上 master，并开始接受并执行日志。

主要复制启动选项

log-slave-updates

log-slave-updates 这个参数用来配置从服务器上的更新操作是否写二进制日志，默认是不打开的。但是，如果这个从服务器同时也要作为其他服务器的主服务器，搭建一个链式的复制，那么就需要打开这个选项，这样它的从服务器将获得它的二进制日志以进行同步操作。这个启动参数需要和--logs-bin 参数一起使用

master-connect-retry

master-connect-retry 这个参数用来设置在和主服务器的连接丢失的时候，重试的时间间隔，默认是 60 秒，即每 60 秒重试一次

read-only

read-only 该参数用来设置从服务器只能接受超级用户的更新操作，从而限制应用程序错误的对从服务器的更新操作。下面创建了一个普通用户，在默认情况下，该用户是可以更新从数据库中的数据的，但是使用 read-only 选项启动从数据库以后，该用户对从数据库的更新会提示错误。

• 主数据库中创建用户：

mysql> grant ALL PRIVILEGES on test.* to 'lisa'@'%' identified by '1234';
Query OK, 0 rows affected (0.00 sec)

• 从服务器使用该用户登录，可以删除表中的记录：

[mysql@master1 log]$ mysql -ulisa -p1234 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 5.1.9-beta-lo
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select * from repl_test;
+------+---------------------+
| id | createtime |
+------+---------------------+
| 5 | 2007-07-24 15:31:37 |
| 5 | 2007-07-24 15:36:47 |
+------+---------------------+
2 rows in set (0.00 sec)
mysql> delete from repl_test;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from repl_test;
Empty set (0.00 sec)

• 然后关闭从服务器，使用 read-only 选项启动从数据库：

[mysql@master1 log]$ mysqladmin -uroot -p shutdown
Enter password: 
STOPPING server from pid file /home/mysql/sysdb/mysqld.pid
070724 17:13:06 mysqld ended
[mysql@master1 mysql_home]$ ./bin/mysqld_safe --read-only&
[1] 9814
[mysql@master1 mysql_home]$ Starting mysqld daemon with databases from 
/home/mysql/sysdb/data

• 然后再使用 Lisa 用户登录数据库，进行删除操作：

[mysql@master1 data]$ mysql -ulisa -p1234 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.1.9-beta-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select * from repl_test;
Empty set (0.00 sec)
mysql> select * from repl_test;
+------+---------------------+
| id | createtime |
+------+---------------------+
| 5 | 2007-07-24 16:29:49 |
+------+---------------------+
1 row in set (0.00 sec)
mysql> delete from repl_test;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot 
execute this statement

可以看到，使用 read-only 启动的从数据库会拒绝普通用户的更新操作，以确保从数据库的安全性。

slave-skip-errors

在复制过程中，由于各种原因，从服务器可能会遇到执行 BINLOG 中的 SQL 出错的情况（比如主键冲突），默认情况下，从服务器将会停止复制进程，不再进行同步，等待用户介入处理。这种问题如果不能及时发现，将会对应用或者备份产生影响。此参数的作用就是用来定义复制过程中从服务器可以自动跳过的错误号，这样当复制过程中遇到定义中的错误号时，便可以自动跳过，直接执行后面的 SQL 语句，以此来最大限度地减少人工干预。此参数可以定义多个错误号，或者通过定义成 all 跳过全部的错误。具体语法如下：

--slave-skip-errors=[err_code1,err_code2,... | all]

如果从数据库主要是作为主数据库的备份，那么就不应该使用这个启动参数，设置不当，很可能造成主从数据库的数据不同步。
但是，如果从数据库仅仅是为了分担主数据库的查询压力，且对数据的完整性要求不是很严格，那么这个选项的确可以减轻数据库管理员维护从数据库的工作量

主从服务器同步维护

在某些繁忙的 OLTP(在线事务处理)系统上，由于主服务器更新频繁，而从服务器由于各种原因（比如硬件性能较差）导致更新速度较慢，从而使得主从服务器之间的数据差距越来越大，最终对某些应用产生影响。在这种情况下，我们就需要定期地进行主从服务器的数据同步，使得主从数据差距能够减到最小。常用的方法是：在负载较低的时候暂时阻塞主数据库的更新，强制主从数据库更新同步。具体操作步骤如下。

• 在主服务器上，执行以下语句（注意，会阻塞主数据库的所有更新操作）：

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000039 | 974 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec

记录 SHOW 语句的输出的日志名和偏移量，这些是从服务器复制的目的坐标。

• 在从服务器上，执行下面语句，其中 MASTER_POS_WAIT()函数的参数是前面步骤中得到的复制坐标值：

mysql> select MASTER_POS_WAIT('mysql-bin.000039','974');
+-------------------------------------------+
| MASTER_POS_WAIT('mysql-bin.000039','974') |
+-------------------------------------------+
| 0 |
+-------------------------------------------+
1 row in set (0.00 sec)

这个 SELECT 语句会阻塞直到从服务器达到指定的日志文件和偏移量后，返回 0，如果返回-1，则表示超时退出。查询返回 0 时，则从服务器与主服务器同步。

• 在主服务器上，执行下面的语句允许主服务器重新开始处理更新：

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

切换主从服务器

假设有一个复制的环境，一个主数据库服务器 M，两个从数据库服务器 S1、S2 同时指向主数据库服务器 M。当主数据库 M 因为某种原因出现故障的时候，需要将其中的一个从数据库服务器（假设选中 S1）切换成主数据库服务器，同时修改另一个从数据库（S2）的配置，使其指向新的主数据库（S1）。此外还需要通知应用修改主数据库的 IP 地址，如果可能，将出现故障的主数据库（M）修复或者重置成新的从数据库。
下面详细介绍一下切换主从服务器的操作步骤。

• 首先要确保所有的从数据库都已经执行了 relay log 中的全部更新，在每个从服务器上，执行 STOP SLAVE IO_THREAD，然后检查 SHOW PROCESSLIST 的输出，直到看到状态是 Has read all relay log，表示更新都执行完毕。

mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW PROCESSLIST \G
*************************** 1. row ***************************
 Id: 2
 User: system user
 Host: 
 db: NULL
Command: Connect
 Time: 4137
 State: Has read all relay log; waiting for the slave I/O thread to update it
 Info: NULL
*************************** 2. row ***************************
……
2 rows in set (0.00 sec)

• 在从数据库 S1 上，执行 STOP SLAVE 停止从服务，然后 RESET MASTER 重置成主数据库。

mysql> STOP SLAVE;
Query OK, 0 rows affected (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.06 sec)

• 在 S2 上，执行 STOP SLAVE 停止从服务，然后执行 CHANGE MASTER TO MASTER_HOST= 'S1'重新设置主数据库，然后再执行 START SLAVE 启动复制。

mysql> STOP SLAVE;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_HOST = '192.168.1.101';
Query OK, 0 rows affected (0.05 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

• 通知所有的客户端将应用指向 S1，这样客户端发送的所有的更新语法写入到 S1 的二进制日志。
• 删除新的主数据库服务器上的 master.info 和 relay-log.info 文件，否则下次重启的时候还会按照从服务器启动。
• 最后，如果 M 服务器可以修复，则可以按照 S2 的方法配置成 S1 的从服务器。

注意：上面测试的步骤是默认 S1 是打开 log-bin 选项的，这样重置成主数据库后可以将二进制日志传输到其他从服务器。其次，S1 上没有打开 log-slave-updates 参数，否则重置成主数据库后，可能会将已经执行过的二进制日志重复传输给 S2，导致 S2 的同步错误。