MySQL 主从复制

2017年11月20日 MySQL 发表评论

主从复制功能通过在主服务器和从服务器之间切分处理客户查询的负荷，可以得到更好的客户响应时间， SELECT 查询可以发送到从服务器，以降低主服务器的查询处理负荷。修改数据的语句仍然发送到主服务器，以使主、从服务器保持同步。

1. 主从复制原理

主从复制通过 3 个过程实现，其中一个过程发生在主服务器上，另外两个过程发生在从服务器上。具体如下：

主服务器将用户对数据库更新的操作以二进制格式保存到 Binary Log 日志文件中，然后由 Binlog Dump 线程将 Binary Log 日志文件传输给从服务器。
从服务器通过一个 I/O 线程将主服务器的 Binary Log 日志文件中的更新操作复制到一个叫 Relay Log 的中继日志文件中。
从服务器通过另一个 SQL 线程将 Relay Log 中继日志文件中的操作依次在本地执行，从而实现主从之间数据的同步。

主从复制详细过程如图：

主从复制

BinLog Dump 线程：

BinLog Dump 线程运行在主服务器上，主要工作是把 Binary Log 二进制日志文件的数据发送给从服务器。

I/O 线程：

从服务器执行 START SLAVE 语句后，创建一个 I/O 线程。此线程运行在从服务器上，与主服务器建立连接，然后向主服务器发出更新请求。之后，I/O 线程将主服务器发送的更新操作复制到本地 Relay Log 日志文件中。

SQL 线程：

SQL 线程运行在从服务器上，主要工作是读取 Relay Log 日志文件中的更新操作，并将操作依次执行，从而使主从服务器数据得到同步。

2. 主从复制配置

确认主从服务器的 MySQL 版本。

MySQL 不同版本的 BinLog 格式可能不一样，最好采用相同版本。

在主服务器上为从服务器设置一个连接账户，授予 REPLICATION SLAVE 权限。

mysql> create user 'backup'@'%' identified by '123456';
mysql> grant replication slave on *.* to 'backup'@'%' identified by '123456';

配置主服务器。

打开二进制日志，指定唯一 Server ID。例如，在 my.cnf 配置文件中加入如下值：
```
[mysqld]        
log-bin = mysql-bin
server-id = 1
```
重启主服务器。

运行 show master status 语句，输出如图所示。

File 表示主服务器正在使用的 binlog 文件； Position 的值与 binlog 文件的大小相同，表示下一个被记录事件的位置； Binlog_Do_DB 和 Binlog_lgnore_DB 是主服务器控制写入 binlog 文件内容的过滤选项，默认为空，表示不做任何过滤。

File 和 Position 两个字段指明从服务器将从哪个 binlog 文件中复制，以及复制的开始位置，它们也是 CHANGE MASTER TO 语句的参数。
配置从服务器。

从服务器的配置与主服务器类似，必须提供一个唯一 Server ID （不能跟主服务器 ID 相同），配置完成后重启。
```
[mysqld]
log-bin = mysql-bin
server-id = 2
```

指定主服务器信息。

mysql> change master to 
-> master_host='主服务器ip',
-> master_user='backup',
-> master_password='123456',
-> master_port=3306,
-> master_log_file='mysql-bin.000001',
-> master_log_pos=0;

此处指定 master_log_pos 的值为 0，因为要从日志的开始位置开始读。

3. 连接主从服务器。

执行 start slave 语句开始复制。
```
mysql> start slave;
```
使用 show slave status \G 查看输出结果：

Slave_IO_Running 和 Slave_SQL_Running 值为 Yes 表明复制过程正常。
使用 show processlist 语句查看主、从服务器的线程状态。

在主服务器上：

第 1 行就是处理从服务器的 I/O 线程的连接。

在从服务器上：

第 1 行为 I/O 线程状态，图中没有 SQL 线程状态，因为 SQL 线程未开始运行。

扩展：

// 刷新权限
mysql> flush privileges;
// 查看用户权限
mysql> show grants for backup;
// 可赋予的权限
mysql> grant select,insert,update,delete,alter,create,drop,lock tables on dbname.* to 'backup'@'%';
// 取消所有权限
mysql> revoke all on dbname.* from 'backup'@'%';
// 获取全局读锁
mysql> flush tables with read lock;
// 释放锁
mysql> unlock tables;