07-异步复制

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

（1）确保主从库上安装了相同版本的数据库。因为复制的功能在持续的改进中，所以在可能的情况下推荐安装最新的稳定版本。

（2）在主库上，设置一个复制使用的账户，并授予REPLICATION SLAVE权限。这里创建一个复制用户rep1，可以从IP为192.168.7.200的主机进行连接：

mysql> GRANT REPLICATION SLAVE ON . TO 'repl'@'192.168.7.200' IDENTIFIED BY '1234test';

Query OK, 0 rows affected (0.00 sec)

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

在my.cnf中修改如下：

[mysqld]

log-bin = /home/mysql/log/mysql-bin.log

server-id = 1

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

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

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

mysql> show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000039 | 102 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

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

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

data/

data/test1/

data/test1/db.opt

…

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

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

（8）将主数据库的一致性备份恢复到从数据库上。如果是使用.tar 打包的文件包，只需要解开到相应的目录即可。

（9）修改从数据库的配置文件my.cnf，增加server-id参数。注意server-id的值必须是唯一的，不能和主数据库的配置相同，如果有多个从数据库服务器，每个从数据库服务器必须有自己唯一的server-id值。

在my.cnf中修改如下：

[mysqld]

server-id = 2

（10）在从库上，使用--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/data

（11）对从数据库服务器做相应设置，指定复制使用的用户，主数据库服务器的 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.7.83',

-> 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)

（12）在从库上，启动slave线程：

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

（13）这时 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，并开始接受并执行日志。

（14）也可以测试复制服务的正确性，在主数据库上执行一个更新操作，观察是否在从数据库上同步。在主数据库上的test数据库中创建一个测试表，插入数据：

mysql> use test

Database changed

mysql> show tables;

Empty set (0.00 sec)

mysql> create table repl_test (id int);

Query OK, 0 rows affected (0.03 sec)

mysql> insert into repl_test values(1),(2),(3),(4),(5);

Query OK, 5 rows affected (0.00 sec)

Records: 5 Duplicates: 0 Warnings: 0

（15）在从数据库上检查新表是否被创建，数据是否同步：

mysql> use test

Database changed

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| repl_test |

+----------------+

1 row in set (0.00 sec)

mysql> select * from repl_test;

+------+

| id |

+------+

| 1|

| 2|

| 3|

| 4|

| 5|

+------+

5 rows in set (0.00 sec)

可以看到数据可以正确同步到从数据库上，主从复制服务配置成功完成。

MySQL主从异步复制是最常见和最简单的复制场景，复制的 3个线程Binlog Dump、I/O、SQL 之间都是独立的，相互之间没有依赖关系。数据的完整性完全依赖于主库的 Binlog 的不丢失，只要主库的Binlog不丢失，那么就算主库宕机了，我们还可以通过Binlog把丢失的部分数据通过手工同步到从库上去。

注意：主库宕机的情况下，DBA可以通过mysqlbinlog工具手工访问主库宕机之前正在写的Binlog抽取缺失的日志并同步到从库上去；也可以通过配置高可用 MHA 架构来自动抽取缺失的部分补全从库，高可用的MHA架构会在其他章节介绍。或者启用MySQL 5.6的global transaction identifiers（GTID）特性来做自动抽取缺失Binlog到从库，此处不对MySQL 5.6的特性做详细介绍。

MySQL在事务（或SQL语句）提交但尚未释放锁的时候，在Binlog中记录事务（或SQL语句），也就是说对于支持事务的引擎（例如InnoDB）来说，每个事务提交时都需要写Binlog；对于不支持事务的引擎（例如MyISAM）来说，每个SQL语句执行完成时，都需要写Binlog。为了保证Binlog的安全，MySQL引入sync_binlog参数来控制Binlog刷新到磁盘的频率：

mysql> show variables like '%sync_binlog%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| sync_binlog | 0|

+---------------+-------+

1 row in set (0.00 sec)

在默认情况下，sync_binlog=0表示MySQL不控制Binlog的刷新，由文件系统自己控制文件系统的缓存的刷新。

如果sync_binlog>0，则表示每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将Binlog刷到磁盘。比如，sync_binlog=1，表示每一次事务提交，MySQL都需要把binlog刷新到磁盘，这样的话，数据库主机的操作系统崩溃或者主机突然掉电的情况下，系统最多损失最近的一个事务的数据（因为上一次的事务提交的时候已经把Binlog刷新到磁盘，当前事务提交时Binlog有可能没有刷新到磁盘，也可能已经刷新到磁盘）；设置sync_binlog=1尽最大可能保证数据安全，但是在多个事务并发提交时，sync_binlog=1使得MySQL不得不按顺序处理请求，同时高频率的刷新binlog对I/O的影响明显，很大的影响了MySQL的性能。

所以一般线上系统MySQL的sync_binlog不会设置为最安全的1，而是其他值或者是0，在数据安全性和更高的并发和性能中间获取一个平衡。

为了更好地保证数据的完整性和安全性，其实就是更好地保证二进制日志Binlog的完整性和安全性，MySQL 5.5引入了半同步复制来部分地解决问题。