被问了一个数据库的问题, 主从复制需要 主库锁表 和 主库重启 吗?

Setting Up Binary Log File Position Based Replication
使用docker配置一遍, 测试一下记录了步骤, 有些东西还是 MySQL 的官方文档好用.

主库需要做的事情

  1. 需要有唯一的 server id.
    这个可以设置 variables , 不需要重启.
1
2
3
4
5
6
7
8
MySQL [(none)]> 
SHOW variables LIKE 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 198   |
+---------------+-------+
1 row in set (0.001 sec)
  1. 创建一个用户用来让从库同步数据. 这个也不需要重启.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
MySQL [(none)]> 
CREATE USER 'replication-user'@'172.31.62.236' IDENTIFIED BY '123123';
Query OK, 0 rows affected (0.006 sec)

MySQL [(none)]> 
GRANT REPLICATION SLAVE ON *.* TO 'replication-user'@'172.31.62.236';
Query OK, 0 rows affected (0.004 sec)

MySQL [(none)]> 
SHOW GRANTS FOR 'replication-user'@'172.31.62.236';
+----------------------------------------------------------------------+
| Grants for replication-user@172.31.62.236                            |
+----------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `replication-user`@`172.31.62.236` |
+----------------------------------------------------------------------+
1 row in set (0.000 sec)

MySQL [(none)]> 
FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.002 sec)

MySQL [(none)]> 
SELECT Host,User,authentication_string FROM mysql.user;
+---------------+------------------+------------------------------------------------------------------------+
| Host          | User             | authentication_string                                                  |
+---------------+------------------+------------------------------------------------------------------------+
| %             | root             | $A$005$CK=G-6GdO6+*f4IpBYW8v6zGb62QvkVJIztfMwOYRKYK4VHdrnOcmM/jOzB     |
| 172.31.62.236 | replication-user | $A$005$t4642jI)eV[+@6NFBplxAFoO/a1p1sHIdDMiRHvtyvKD4mUFajpDiZKUPA      |
| localhost     | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost     | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost     | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost     | root             | $A$005$y,)(7](i&=Mm+kO9Re/i.ywV/7DTELTS9.DN9Or6yCVIPlaVm/NOkg/RA       |
+---------------+------------------+------------------------------------------------------------------------+
6 rows in set (0.001 sec)
  1. 确保主库会记录binlog, 或者是特定的 database 会记录 binlog. MySQL 8.0 以后默认会启用 binlog, 所以这个实际也不会需要重启.
  2. 文档中有描述, 在这个位置应该上一个 主库 的 read lock, 是为了确保记住的 binlog 是不变的, 如果业务比较忙的情况下, 会导致 binlog position 一直动.
1
2
mysql> 
FLUSH TABLES WITH READ LOCK;
  1. 在主库上面查询 binlog 的状态, 也就是之前的 show master status; 现在这个命令已经被替换成:
1
2
3
4
5
6
7
8
9
MySQL [test]> 
SHOW BINARY LOG STATUS\G
*************************** 1. row ***************************
File: binlog.000002
Position: 1086
Binlog_Do_DB: test
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.000 sec)
  1. 看完位置之后就可以 unlock 了.
1
2
mysql> 
UNLOCK TABLES;

从库需要做的事情

  1. 确认可以连接到主库. 网络部分.
  2. 创建一个复制任务, 设置下面的信息.
1
2
3
4
5
6
7
8
9
mysql> 
CHANGE REPLICATION SOURCE TO
     SOURCE_HOST='172.31.54.198',
     SOURCE_USER='replication-user',
     SOURCE_PASSWORD='123123',
     SOURCE_LOG_FILE='binlog.000002',
     SOURCE_LOG_POS=4,
     GET_SOURCE_PUBLIC_KEY=1;   
     # 如果您使用的复制用户帐户通过 `caching_sha2_password` 插件（默认）进行身份验证，并且您没有使用安全连接，则必须指定此选项或 `SOURCE_PUBLIC_KEY_PATH` 选项向副本提供 RSA 公钥。
  1. 启动同步.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
mysql> 
START REPLICA;

mysql> 
SHOW PROCESSLIST;
+----+-----------------+---------------------+------+---------+------+----------------------------------------------------------+------------------+
| Id | User            | Host                | db   | Command | Time | State                                                    | Info             |
+----+-----------------+---------------------+------+---------+------+----------------------------------------------------------+------------------+
|  8 | event_scheduler | localhost           | NULL | Daemon  | 2062 | Waiting on empty queue                                   | NULL             |
| 16 | root            | 172.31.47.174:56736 | NULL | Query   |    0 | init                                                     | SHOW PROCESSLIST |
| 29 | system user     | connecting host     | NULL | Connect |  447 | Waiting for source to send event                         | NULL             |
| 35 | system user     |                     | NULL | Query   |   79 | Replica has read all relay log; waiting for more updates | NULL             |
| 36 | system user     |                     | NULL | Query   | 2149 | Waiting for an event from Coordinator                    | NULL             |
| 37 | system user     |                     | NULL | Connect |   79 | Waiting for an event from Coordinator                    | NULL             |
| 38 | system user     |                     | NULL | Connect |   79 | Waiting for an event from Coordinator                    | NULL             |
| 39 | system user     |                     | NULL | Connect |   79 | Waiting for an event from Coordinator                    | NULL             |
+----+-----------------+---------------------+------+---------+------+----------------------------------------------------------+------------------+
8 rows in set, 1 warning (0.00 sec)

mysql> 
SHOW REPLICA STATUS\G;
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 172.31.54.198
                  Source_User: replication-user
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: binlog.000002
          Read_Source_Log_Pos: 3612
               Relay_Log_File: ip-172-31-62-236-relay-bin.000002
                Relay_Log_Pos: 3823
        Relay_Source_Log_File: binlog.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: 3612
              Relay_Log_Space: 4045
              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: 198
                  Source_UUID: ddbc7571-4066-11ef-afce-02dece705732
             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: 10
                  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: 1
            Network_Namespace:
1 row in set (0.00 sec)

ERROR:
No query specified
  1. 错误的情况下需要重新配置, 清空IOTHREAD的配置.
1
2
mysql> 
STOP REPLICA IO_THREAD FOR CHANNEL '';

错误

文档对于命令的说明:
CHANGE REPLICATION SOURCE TO Statement

Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction ‘ANONYMOUS’ at source log binlog.000002, end_log_pos 340. See error log and/or

这个是没写 SOURCE_LOG_POS=4 造成的, 应该写, 写4进去就是从头开始同步.

requires secure connection. Error_code: MY-002061

参数 GET_SOURCE_PUBLIC_KEY 需要添加, 这个参数不添加的话, 就会有这个报错, 实际是需要信任主库.

DockerRun 命令

primary 的 docker 命令启动容器

1
2
3
4
5
6
7
8
9
10
11
12
#!/bin/bash
#

hostnamectl set-hostname mysql-master.aws.liarlee.site

docker run --name mysql-master -e MYSQL_ROOT_PASSWORD=123123 \
    --network=host \
    --restart=always \
    -u root \
    -v ./my.cnf:/etc/my.cnf \
    -v ./datadir/mysql:/var/lib/mysql \
    -d reg.liarlee.site/docker.io/mysql:8

replica 的 docker 命令启动容器

1
2
3
4
5
6
7
8
9
10
11
12
#!/bin/bash
#

 hostnamectl set-hostname mysql-slave.aws.liarlee.site
 
docker run --name mysql-slave -e MYSQL_ROOT_PASSWORD=123123 \
    --network=host \
    --restart=always \
    -u root \
    -v ./my.cnf:/etc/my.cnf \
    -v ./datadir/mysql:/var/lib/mysql \
    -d reg.liarlee.site/docker.io/mysql:8

Mycnf

primary

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.4/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 2G
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

server-id=1
read-only=0

host-cache-size=0
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql

pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/

Replica

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.4/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 2G
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

server-id=2
read-only=1

host-cache-size=0
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql

pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/

启用 GTID

gtid_mode=ON

primary

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.4/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 2G
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

server-id=1
read-only=0
gtid_mode=ON
enforce_gtid_consistency=ON
host-cache-size=0
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql
pid-file=/var/run/mysqld/mysqld.pid

[client]
socket=/var/run/mysqld/mysqld.sock
!includedir /etc/mysql/conf.d/

Replica same as Primary.

1
2
3
4
5
6
7
mysql> 
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = host,
    SOURCE_PORT = port,
    SOURCE_USER = user,
    SOURCE_PASSWORD = password,
    SOURCE_AUTO_POSITION = 1;

查看主从同步失败卡在什么位置

mysql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
mysql> table performance_schema.replication_applier_status_by_worker\G;
*************************** 1. row ***************************
                                           CHANNEL_NAME:
                                              WORKER_ID: 1
                                              THREAD_ID: NULL
                                          SERVICE_STATE: OFF
                                      LAST_ERROR_NUMBER: 1008
                                     LAST_ERROR_MESSAGE: Worker 1 failed executing transaction '31a0eafe-4354-11ef-b41c-02f9bff33582:1' at source log binlog.000005, end_log_pos 348; Error 'Can't drop database 'test_db'; database doesn't exist' on query. Default database: 'test_db'. Query: 'drop database test_db'
                                   LAST_ERROR_TIMESTAMP: 2024-07-17 05:53:07.291805
                               LAST_APPLIED_TRANSACTION:
     LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
    LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000
         LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
           LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 0000-00-00 00:00:00.000000
                                   APPLYING_TRANSACTION: 31a0eafe-4354-11ef-b41c-02f9bff33582:1
         APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2024-07-17 05:48:51.628448
        APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2024-07-17 05:48:51.628448
             APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 2024-07-17 05:53:07.289218
                 LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
   LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
  LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:

mysql> show binlog events in 'binlog.000005';
+---------------+--------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name      | Pos    | Event_type     | Server_id | End_log_pos | Info                                                                                                                                                                                                                                         |
+---------------+--------+----------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| binlog.000005 |      4 | Format_desc    |         1 |         127 | Server ver: 8.4.1, Binlog ver: 4                                                                                                                                                                                                             |
| binlog.000005 |    127 | Previous_gtids |         1 |         158 |                                                                                                                                                                                                                                              |
| binlog.000005 |    158 | Gtid           |         1 |         235 | SET @@SESSION.GTID_NEXT= '31a0eafe-4354-11ef-b41c-02f9bff33582:1'                                                                                                                                                                            |
| binlog.000005 |    235 | Query          |         1 |         348 | drop database test_db /* xid=55 */                                                                                                                                                                                                           |
| binlog.000005 |    348 | Gtid           |         1 |         425 | SET @@SESSION.GTID_NEXT= '31a0eafe-4354-11ef-b41c-02f9bff33582:2'                                                                                                                                                                            |
| binlog.000005 |    425 | Query          |         1 |         542 | create database test_bd /* xid=57 */                                                                                                                                                                                                         |
| binlog.000005 |    542 | Gtid           |         1 |         619 | SET @@SESSION.GTID_NEXT= '31a0eafe-4354-11ef-b41c-02f9bff33582:3'                                                                                                                                                                            |