【MySQL 8.0】搭建一个极简 MySQL 主从复制拓扑（基于 Binlog 行复制）

笔者之前在测试 Mycat2 时搭建了一个基于二进制日志行复制的 MySQL 主从（或源/副本）复制拓扑。因此，将搭建 MySQL 主从复制拓扑的过程归纳为这篇文章，供初学者参考。

先决条件

• 必须为主、从服务器配置 server_id 服务器系统变量。
• MySQL 8.0 默认开启 Binlog，且 binlog_format 默认为 ROW 。
• 如使用 MySQL 默认的密码认证方式 caching_sha2_password ，对 SSL安全认证没有要求，可改为 mysql_native_password ，搭建过程会更简单。
• MySQL 复制用户需要 REPLICATION SLAVE 权限。
• 如果源（Source）服务器使用已有数据的 MySQL Server 实例，建议在获取源服务器上的二进制文件和位置时，使用 FLUSH TABLES WITH READ LOCK; ，在创建数据快照后 UNLOCK TABLES; 。新建的 MySQL Server 实例不需要。

搭建

配置源、副本服务器选项文件

即配置主、从服务器选项文件。

Source 服务器 /etc/my.cnf 配置如下：

[mysqld@rep01Src]
port=3308
socket=/var/lib/mysql/rep01Src/mysql.sock
pid-file=/var/run/mysqld/mysqld-rep01Src.pid
tmpdir=/tmp
datadir=/var/lib/mysql/rep01Src
log_bin=bin-rep01Src.log
log-error=/var/log/mysqld-rep01Src.log
slow-query-log-file=/var/log/mysqld_rep01-slow.log
lower_case_table_names=1

innodb_buffer_pool_size=128M
innodb_redo_log_capacity=100M
#innodb_flush_method=O_DIRECT_NO_FSYNC
innodb_flush_log_at_trx_commit=1
sync_binlog=1

##replication 
server_id=3
###SSL authority
#ssl_ca=ca.pem
#ssl_cert=server-cert.pem
#ssl_key=server-key.pem

Replica 服务器 /etc/my.cnf 配置如下：

[mysqld@rep01Rep01]
port=3308
socket=/var/lib/mysql/rep01Rep01/mysql.sock
pid-file=/var/run/mysqld/mysqld-rep01Rep01.pid
tmpdir=/tmp
datadir=/var/lib/mysql/rep01Rep01
log_bin=bin-mycat.log
log-error=/var/log/mysqld-rep01Rep01.log
slow-query-log-file=/var/log/mysqld_rep01-slow.log
lower_case_table_names=1

innodb_buffer_pool_size=128M
innodb_redo_log_capacity=100M
#innodb_flush_method=O_DIRECT_NO_FSYNC
innodb_flush_log_at_trx_commit=1
sync_binlog=1

##replication 
server_id=4
###SSL authority
#ssl_ca=ca.pem
#ssl_cert=server-cert.pem
#ssl_key=server-key.pem

配置完成后分别启动两个 MySQL 实例：
Source 服务器：

[root@ic-source ~]$ systemctl start mysqld@rep01Src
[root@ic-source ~]$ systemctl status mysqld@rep01Src
● mysqld@rep01Src.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld@.service; disabled; vendor preset: disabled)
   Active: active (running) since 日 2023-04-30 23:22:10 CST; 5s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 75627 ExecStartPre=/usr/bin/mysqld_pre_systemd %I (code=exited, status=0/SUCCESS)
 Main PID: 75728 (mysqld)
   Status: "Server is operational"
   CGroup: /system.slice/system-mysqld.slice/mysqld@rep01Src.service
           └─75728 /usr/sbin/mysqld --defaults-group-suffix=@rep01Src

4月 30 23:21:47 ic-source systemd[1]: Starting MySQL Server...
4月 30 23:21:47 ic-source mysqld_pre_systemd[75627]: /usr/sbin/restorecon:  lstat(/var/lib/mysql/rep...ory
4月 30 23:22:10 ic-source systemd[1]: Started MySQL Server.
Hint: Some lines were ellipsized, use -l to show in full.

Replica 服务器：

[root@ic-replica1 ~]# systemctl start mysqld@rep01Rep01
[root@ic-replica1 ~]# 
[root@ic-replica1 ~]# systemctl status mysqld@rep01Rep01
● mysqld@rep01Rep01.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld@.service; enabled; vendor preset: disabled)
   Active: active (running) since 日 2023-04-30 23:30:03 CST; 7s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 48873 ExecStartPre=/usr/bin/mysqld_pre_systemd %I (code=exited, status=0/SUCCESS)
 Main PID: 48975 (mysqld)
   Status: "Server is operational"
   CGroup: /system.slice/system-mysqld.slice/mysqld@rep01Rep01.service
           └─48975 /usr/sbin/mysqld --defaults-group-suffix=@rep01Rep01

4月 30 23:29:23 ic-replica1 systemd[1]: Starting MySQL Server...
4月 30 23:30:03 ic-replica1 systemd[1]: Started MySQL Server.

然后修改 MySQL 的 root 用户的初始密码，并刷新权限。此处不加赘述。

创建复制用户

mysql> CREATE USER repl IDENTIFIED WITH mysql_native_password BY '密码';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@`%`;
Query OK, 0 rows affected (0.00 sec)

获取复制源服务器的二进制日志坐标

即获取主服务器的二进制日志坐标。

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: bin-rep01Src.000002
         Position: 2034
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

配置复制源

在从服务器上配置复制源为主服务器。

mysql> CHANGE REPLICATION SOURCE TO 
    -> SOURCE_HOST='source',
    -> SOURCE_PORT=3308,
    -> SOURCE_USER='repl',
    -> SOURCE_PASSWORD='Repl#123',
    -> SOURCE_LOG_FILE='bin-rep01Src.000002',
    -> SOURCE_LOG_POS=2034;
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> SHOW WARNINGS;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                              |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure.                                                                                                                                                                                                               |
| Note  | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

开启复制

在从服务器上开启复制。

mysql> START REPLICA;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: source
                  Source_User: repl
                  Source_Port: 3308
                Connect_Retry: 60
              Source_Log_File: bin-rep01Src.000002
          Read_Source_Log_Pos: 2034
               Relay_Log_File: ic-replica1-relay-bin.000002
                Relay_Log_Pos: 329
        Relay_Source_Log_File: bin-rep01Src.000002
           Replica_IO_Running: Yes
          Replica_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_Source_Log_Pos: 2034
              Relay_Log_Space: 545
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File: 
           Source_SSL_CA_Path: 
              Source_SSL_Cert: 
            Source_SSL_Cipher: 
               Source_SSL_Key: 
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Source_Server_Id: 3
                  Source_UUID: bbf4a3fa-e76a-11ed-b4a0-000c298d6cb9
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 86400
                  Source_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Source_SSL_Crl: 
           Source_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Source_TLS_Version: 
       Source_public_key_path: 
        Get_Source_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

测试

在主服务器上创建一个新的数据库 testdb ，并在其内创建表 t1 ，插入一些数据。

mysql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE TABLE testdb.t1(id INT PRIMARY KEY);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO testdb.t1 VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> TABLE testdb.t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

mysql> SELECT @@HOSTNAME,@@PORT;
+------------+--------+
| @@HOSTNAME | @@PORT |
+------------+--------+
| ic-source  |   3308 |
+------------+--------+
1 row in set (0.00 sec)

在从服务器上验证：

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.00 sec)

mysql> TABLE testdb.t1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

mysql> SELECT @@HOSTNAME,@@PORT;
+-------------+--------+
| @@HOSTNAME  | @@PORT |
+-------------+--------+
| ic-replica1 |   3308 |
+-------------+--------+
1 row in set (0.00 sec)

至此，已成功搭建了一个极简的主从复制拓扑。