主从复制

参考 「二进制包形式安装MySQL5.7」在两台主机上安装 MySQL，我这里准备如下两台主机。

主机名	IP	描述
A	10.0.1.200	主节点
B	10.0.1.201	从节点

主节点

1、在主节点中启用二进制日志，并配置一个全局唯一的 server-id：

# 重启生效
$ vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
server-id=1
log-bin=master-bin

2、重启 MySQL，检查是否启用了二进制日志与 server-id：

mysql> show master logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000001 |       154 |
+-------------------+-----------+
1 row in set (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

3、创建一个拥有复制权限的账号：

mysql> create user repluser@'10.0.1.201' identified by '123';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave,replication client on *.* to repluser@'10.0.1.201';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

从节点

要完成主从复制的功能从节点需要开启如下两个线程：

• I/O 线程：从主节点请求二进制日志信息，并保存至中继日志（relay）；
• SQL 线程：从中继日志中读取日志信息，在本地完成重放；

下面看其实现步骤。

---

1、在从节点中启用中继日志，并配置一个全局唯一的 server-id：

# 重启生效
$ vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
relay-log=relay-log
server-id=2

2、检查中继日志与 server-id 是否正常配置并已启用：

mysql> select @@relay_log;
+-------------+
| @@relay_log |
+-------------+
| relay-log   |
+-------------+
1 row in set (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_Id |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

3、配置从节点的主节点为 A 主机：

-- master_host：主节点 IP；
-- master_user：主节点中拥有复制权限的用户名；
-- master_password：对应 master_user 的密码；
-- master_log_file：主节点当前正在使用的 binlog 文件；
-- master_log_pos：指定从何处开始复制，可通过 show master status 查看当前 binlog 所处位置；
mysql> change master to master_host='10.0.1.200', master_user='repluser', master_password='123', master_log_file='master-bin.000001', master_log_pos=793;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

4、启动复制线程，并检查从节点状态是否正常：

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

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.1.200
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 775
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 942
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 775
              Relay_Log_Space: 1143
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: cc1a793f-941e-11ea-991d-000c29ec139d
             Master_Info_File: /data/3306/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

在完成主从配置后，从节点的数据目录下会生成如下两个文件：

• master.info：保存了当前从节点与主节点通信的相关信息；
• relay-log.info：保存了读取主节点 binlog 的 position，以及当前 relay log 写到 position；

上述的 Seconds_Behind_Master 的值描述的是从服务器是否落后于主服务器。

测试

1、在主节点测试创建 testdb 库，并新建测试表录入数据：

mysql> create database testdb;
Query OK, 1 row affected (0.01 sec)

mysql> use testdb;
Database changed

mysql> create table t1(id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

2、在从节点检查是否同步创建了 testdb 库及相关表数据：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.01 sec)

mysql> use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| t1               |
+------------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

至此，主从复制架构就搭建成功。

注意的问题

1、限制从服务器为只读。

一般来说，从节点是不应该接受写入操作的，因为从节点的数据并不能同步到主节点，所以在主从架构中推荐关闭从节点的写入功能。

要想实现该功能也很简单，只需要将从节点的 read_only 设为 ON 即可。但该种方式对拥有 SUPER 权限的用户是无效的，如果要组织所有用户，我们可以手动给所有表加上读锁，如下：

mysql> flush tables with read lock;

2、保证主从事复制的事务安全。

从节点为保证同步二进制日志的效率会在内存中开辟一块写入缓冲区，即从节点同步数据也可能出现数据丢失的情况。

为尽量避免这种情况，可做如下操作：

1. 在 master 节点启用 sync_binlog，以保证在主节点中有事务提交时立即将 binlog 缓冲区中的数据立即刷写到磁盘上的 binlog 文件；

2. 在 master 节点使用 innodb 引擎时，推荐启用 innodb_flush_log_at_trx_commit，它的作用是在事务提交时立即将事务日志缓冲区中与事务相关的数据刷写到磁盘中的事务日志；

当然，上述两个参数的启用也会增大磁盘的 I/O 压力，视情况而定吧。

主主复制

参考 「二进制包形式安装MySQL5.7」在两台主机上安装 MySQL，我这里准备如下两台主机。

主机名	IP	描述
A	10.0.1.200	与 B 互为主从
B	10.0.1.201	与 A 互为主从

配置主主复制有如下几个条件：

1. 各节点使用全局唯一的 server-id；
2. 都启用 binary log 和 relay log；
3. 创建拥有复制权限的用户账号；
4. 定义自动增长的 id 字段数值范围分别为奇偶；
5. 均把对方指定为主节点，并启动复制线程；

A 节点

1、在 A 节点中启用二进制日志和中继日志，并配置一个全局唯一的 server-id：

# 重启生效
$ vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
server-id=1
log-bin=master-bin
relay-log=relay-log
# 起始值为 1
auto_increment_offset=1
# 每次增长 2
auto_increment_increment=2

2、重启 MySQL，检查是否启用了二进制日志、中继日志与 server-id：

mysql> show master logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000001 |       154 |
+-------------------+-----------+
1 row in set (0.00 sec)

mysql> select @@relay_log;
+-------------+
| @@relay_log |
+-------------+
| relay-log   |
+-------------+
1 row in set (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

3、创建一个拥有复制权限的账号：

mysql> create user repluser@'10.0.1.201' identified by '123';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave,replication client on *.* to repluser@'10.0.1.201';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

4、配置 A 节点的主节点为 B 主机：

mysql> change master to master_host='10.0.1.201', master_user='repluser', master_password='123', master_log_file='master-bin.000001', master_log_pos=790;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

5、启动复制线程，并检查从节点状态是否正常：

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

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.1.201
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 790
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 790
              Relay_Log_Space: 522
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: ca83bdb3-941e-11ea-9974-000c29496b80
             Master_Info_File: /data/3306/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

B 节点

1、在 B 节点中启用二进制日志和中继日志，并配置一个全局唯一的 server-id：

$ vim /etc/my.cnf 
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
server-id=2
log-bin=master-bin
relay-log=relay-log
# 起始值为 1
auto_increment_offset=2
# 每次增长 2
auto_increment_increment=2

2、重启 MySQL，检查是否启用了二进制日志、中继日志与 server-id：

mysql> show master logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000001 |       154 |
+-------------------+-----------+
1 row in set (0.00 sec)

mysql> select @@relay_log;
+-------------+
| @@relay_log |
+-------------+
| relay-log   |
+-------------+
1 row in set (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_Id |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

3、创建一个拥有复制权限的账号：

mysql> create user repluser@'10.0.1.200' identified by '123';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave,replication client on *.* to repluser@'10.0.1.200';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

4、配置 B 节点的主节点为 A 主机：

mysql> change master to master_host='10.0.1.200', master_user='repluser', master_password='123', master_log_file='master-bin.000001', master_log_pos=790;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

5、启动复制线程，并检查从节点状态是否正常：

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

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.1.200
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 790
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 790
              Relay_Log_Space: 522
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: cc1a793f-941e-11ea-991d-000c29ec139d
             Master_Info_File: /data/3306/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

测试

1、在 A 节点测试创建 testdb 库，并新建测试表录入数据：

mysql> create database testdb;
Query OK, 1 row affected (0.01 sec)

mysql> use testdb;
Database changed

mysql> create table t1 (id int primary key auto_increment, name varchar(24));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1(name) values('zs'),('ls'),('ww');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

2、在 B 节点检查是否同步创建了 testdb 库及相关表数据：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.01 sec)

mysql> use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| t1               |
+------------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | zs   |
|  3 | ls   |
|  5 | ww   |
+----+------+
3 rows in set (0.00 sec)

3、在 B 节点往测试表插入数据：

mysql> use testdb;
Database changed

mysql> insert into t1(name) values('zl'),('xq'),('nb');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

4、在 A 节点检查是否同步了 B 节点插入的数据：

mysql> use testdb;
Database changed

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | zs   |
|  3 | ls   |
|  5 | ww   |
|  6 | zl   |
|  8 | xq   |
| 10 | nb   |
+----+------+
6 rows in set (0.00 sec)

至此，主主复制架构就搭建成功。

---

除了上述的主从复制和主主复制外，MySQL 还支持通过安装插件来实现半同步复制，可参考「MySQL半同步复制」。