Demo

在我的demo中，我是在docker 中创建了两个docker container。

# 这里：
# 1. --restart=unless-stopped：container 会自动启动
# 2. MYSQL_ROOT_HOST=%：root用户可以在任何host上登录
# 3. MYSQL_ROOT_PASSWORD=1234：root 密码为1234
# 4. 3307:3306：将本机的 3307 映射到 container 内部的 3306
$ docker run --name sw-mysql1 --restart=unless-stopped -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=1234 -p 3307:3306 -d mysql

$ docker run --name sw-mysql2 --restart=unless-stopped -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=1234 -p 3308:3306 -d mysql

值得一提的是，这两个container在默认情况下并不能相互通讯

$ docker network create myNetwork
$ docker network connect myNetwork sw-mysql1
$ docker network connect myNetwork sw-mysql2

Now you connect from sw-mysql1 to sw-mysql2 container or the other way round.

Use the internal network IP addresses which you can find by running:

$ docker network inspect myNetwork
# sw-mysql1: 172.18.0.2
# sw-mysql2: 172.18.0.3

Note that only internal IP addresses and ports are accessible to the containers connected by the network bridge.

# 监测连通性
# 进入sw-mysql2并访问sw-mysql1
$ docker exec -it sw-mysql2 bash
root@30761119e560:/# mysql -uroot -h 172.18.0.2 -p1234
mysql> show databases; 
... # 一切正常

配置source

source的my.cnf

$ docker exec -it sw-mysql1 bash
root@082ac9314148:/# apt update; apt install vim;
root@082ac9314148:/# vim /etc/mysql/my.cnf
[mysqld]
# 主从备份相关配置
server-id = 1                # 服务器 id 号，不要和其他服务器重复 -> Setting the Replication Source 
log-bin=mysql-bin            # 开启binlog（虽然默认也是开启的）

restart replica node

$ docker restart sw-mysql1

设置 source

$ docker exec -it sw-mysql1 bash
root@082ac9314148: mysql -uroot -p1234

# 创建主从复制账号，并授予REPLICATION replica权限
mysql> CREATE USER 'sw_replica_user'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION replica ON *.* TO 'sw_replica_user'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;

# 检查binlog是否已经开启
mysql> show variables like "log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

Obtaining the Replication Source Binary Log Coordinates

# 检查source状态，记录下当前 File（binlog.000218）和 Position（1898）
mysql> show source status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |     677 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

The File column shows the name of the log file and the Position column shows the position within the file. In this example, the binary log file is mysql-bin.000003 and the position is 73. Record these values. You need them later when you are setting up the replica. They represent the replication coordinates at which the replica should begin processing new updates from the source.

Ref

Create a new DB:

mysql> create database sw_db;
Query OK, 1 row affected (0.00 sec)

配置replicas

replica的my.cnf

$ docker exec -it sw-mysql2 bash
root@30761119e560:/# apt update; apt install vim;

root@30761119e560:/# vim /etc/mysql/my.cnf
# 主从备份相关配置 - 从服务器
 server-id = 2                   # 服务器 id 号，不要和其他服务器重复
 
## 开启binlog功能，以备replica作为其它replica的source时使用
log-bin=mysql-replica-bin   
## relay_log配置中继日志
relay_log=edu-mysql-relay-bin

Binary logging is enabled by default on all servers. A replica is not required to have binary logging enabled for replication to take place. However, binary logging on a replica means that the replica’s binary log can be used for data backups and crash recovery. Replicas that have binary logging enabled can also be used as part of a more complex replication topology. For example, you might want to set up replication servers using this chained arrangement:

A -> B -> C

Here, A serves as the source for the replica B, and B serves as the source for the replica C. For this to work, B must be both a source and a replica. Updates received from A must be logged by B to its binary log, in order to be passed on to C

Refer to https://dev.mysql.com/doc/refman/8.0/en/replication-howto-slavebaseconfig.html for detail.

restart replica node

$ docker restart sw-mysql2

设置 replica

$ docker exec -it sw-mysql2 bash
root@30761119e560:/# mysql -uroot -p1234
# 创建DB，如果没有创建DB，会报错，因为主从同步并不会同步DB的DDL
mysql> create database sw_db;
Query OK, 1 row affected (0.00 sec)

# 连接source
# source_log_file：指定 replica 从哪个日志文件开始复制数据，即上文中提到的 File 字段的值
# source_log_pos：从哪个 Position 开始读，即上文中提到的 Position 字段的值
mysql> CHANGE source TO
                source_HOST='172.18.0.2',
                source_USER='sw_replica_user',
                source_PASSWORD='1234',
                source_LOG_FILE='mysql-bin.000001',
                source_LOG_POS=677;
Query OK, 0 rows affected, 7 warnings (0.05 sec)

# 观察replica状态
# 此时，replica_IO_Running 和 replica_SQL_Running 都是 No，因为我们还没有开启主从复制
mysql> show replica status \G;
*************************** 1. row ***************************
               replica_IO_State:
                  source_Host: 172.18.0.2
                  source_User: sw_replica_user
                  source_Port: 3306
                Connect_Retry: 30
              source_Log_File: mysql-bin.000001
          Read_source_Log_Pos: 677
               Relay_Log_File: edu-mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_source_Log_File: mysql-bin.000001
             replica_IO_Running: No
            replica_SQL_Running: No
            ...
      replica_SQL_Running_State:
      ...
      
# 开启replica
mysql> start replica;
Query OK, 0 rows affected, 1 warning (0.02 sec)  

# 再次观察replica状态
# 此时，如果 replica_IO_Running 和 replica_SQL_Running 都是 Yes，且 replica_SQL_Running_State 是Replica has read all relay log; waiting for more updates，主从复制开启成功了
mysql> show replica status \G;
*************************** 1. row ***************************
               replica_IO_State: Waiting for source to send event
                  source_Host: 172.18.0.2
                  source_User: sw_replica_user
                  source_Port: 3306
                Connect_Retry: 30
              source_Log_File: mysql-bin.000001
          Read_source_Log_Pos: 677
               Relay_Log_File: edu-mysql-relay-bin.000002
                Relay_Log_Pos: 324
        Relay_source_Log_File: mysql-bin.000001
             replica_IO_Running: Yes
             replica_SQL_Running: Yes
             ...
      replica_SQL_Running_State: Replica has read all relay log; waiting for more updates

进行同步

# 进入 source
$ docker exec -it sw-mysql1 bash
root@082ac9314148: mysql -uroot -p1234
mysql> use sw_db;
Database changed
mysql>  CREATE TABLE `tb_person` (
               `id` int(11) NOT NULL AUTO_INCREMENT,
                    `name` varchar(36) NOT NULL,
                    `address` varchar(36) NOT NULL DEFAULT '',
                    `sex` varchar(12) NOT NULL DEFAULT 'Man' ,
                `other` varchar(256) NOT NULL ,
                    PRIMARY KEY (`id`)
                  ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql>  insert into tb_person  set name="name1", address="beijing", sex="man", other="nothing";
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb_person  set name="name2", address="beijing", sex="man", other="nothing";
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb_person  set name="name3", address="beijing", sex="man", other="nothing";
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb_person  set name="name4", address="beijing", sex="man", other="nothing";
Query OK, 1 row affected (0.00 sec)

不断的插入数据，可以看到新数据会被自动同步到replica node

$ docker exec -it sw-mysql2 bash
root@30761119e560:/# mysql -uroot -p1234
mysql> use sw_db;
Database changed

mysql> select * from tb_person;
+----+-------+---------+-----+---------+
| id | name  | address | sex | other   |
+----+-------+---------+-----+---------+
|  1 | name1 | beijing | man | nothing |
|  2 | name2 | beijing | man | nothing |
+----+-------+---------+-----+---------+
2 rows in set (0.00 sec)

Troubleshooting

Reset

mysql> stop replica;
Query OK, 0 rows affected, 1 warning (2.01 sec)

mysql> reset replica;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> start replica;
Query OK, 0 rows affected, 1 warning (2.01 sec)

观察 Logs

$ docker logs sw-mysql1

$ docker logs sw-mysql2

View Binlogs

$ docker exec -it sw-mysql1 bash
root@082ac9314148:/# mysql -uroot -p1234
# view the binlog folder
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)

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


root@082ac9314148:/# cd /var/lib/mysql/
root@082ac9314148:/# mysqlbinlog --base64-output=decode-rows --verbose binlog.000002

# 在slave
mysql> select * from tb_person;
+----+-------+---------+-----+---------+
| id | name  | address | sex | other   |
+----+-------+---------+-----+---------+
|  1 | name1 | beijing | man | nothing |
|  2 | name2 | beijing | man | nothing |
|  3 | name3 | beijing | man | nothing |
+----+-------+---------+-----+---------+
2 rows in set (0.00 sec)

# 在master 执行一条插入
mysql> insert into tb_person  set name="name3", address="beijing", sex="man", other="nothing";
Query OK, 1 row affected (0.00 sec)

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

查看master的binlog：

root@082ac9314148:/var/lib/mysql# mysqlbinlog --base64-output=decode-rows --verbose mysql-bin.000001
# at 2457
#211031 14:58:25 server id 100  end_log_pos 2524 CRC32 0x9a05db89 	Write_rows: table id 94 flags: STMT_END_F
### INSERT INTO `sw_db`.`tb_person`
### SET
###   @1=4
###   @2='name3'
###   @3='beijing'
###   @4='man'
###   @5='nothing'
# at 2524
#211031 14:58:25 server id 100  end_log_pos 2555 CRC32 0x45f1de16 	Xid = 58
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

可以看到这一条 insert语句。

可能的错误

Error 1 - ERROR 1872 (HY000): replica failed to initialize relay log info structure from the repository

mysql> start replica;
ERROR 1872 (HY000): replica failed to initialize relay log info structure from the repository

mysql> reset replica;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> start replica;
Query OK, 0 rows affected, 1 warning (0.04 sec)

Error 2 - Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection.

在从库配置好change source 信息后，start replica，查看复制状态，发生以下错误：

error connecting to source 'sw_replica_user@172.18.0.2:3306' - retry-time: 60 retries: 2 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

caching_sha2_password

To connect to the source using a user account that authenticates with the caching_sha2_password plugin, you must either set up a secure connection as described in Section 17.3.1, “Setting Up Replication to Use Encrypted Connections”, or enable the unencrypted connection to support password exchange using an RSA key pair. The caching_sha2_password authentication plugin is the default for new users created from MySQL 8.0 (for details, see Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”). If the user account that you create or use for replication (as specified by the MASTER_USER option) uses this authentication plugin, and you are not using a secure connection, you must enable RSA key pair-based password exchange for a successful connection.

Refer to

# solution 1 - 禁用 caching_sha2_password
mysql> CREATE USER 'sw_replica_user'@'%' IDENTIFIED WITH 'mysql_native_password' BY '1234'; 
mysql> GRANT REPLICATION replica ON *.* TO 'sw_replica_user'@'%'; 
mysql> FLUSH PRIVILEGES;

# 检查复制账户
mysql> use mysql;
mysql> select user,host,plugin,authentication_string from user;

# 验证：在sw-mysql2上使用主从同步账号 sw_replica_user 尝试直接登录
$ docker exec -it sw-mysql2 bash
root@30761119e560:/# mysql -h 172.18.0.2 -u sw_replica_user -p1234
mysql> show databases; # 登录成功，则说明已经可以使用 mysql_native_password 来登录

【MySQL】搭建基于 Binlog Position 的主从复制