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

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

主库需要做的事情

  1. 需要有唯一的 server id.
    这个可以设置 variables , 不需要重启.

    MySQL [(none)]> 
    SHOW variables LIKE 'server_id';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | server_id     | 198   |
    +---------------+-------+
    1 row in set (0.001 sec)
  2. 创建一个用户用来让从库同步数据. 这个也不需要重启.

    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)
  3. 确保主库会记录binlog, 或者是特定的 database 会记录 binlog. MySQL 8.0 以后默认会启用 binlog, 所以这个实际也不会需要重启.

  4. 文档中有描述, 在这个位置应该上一个 主库read lock, 是为了确保记住的 binlog 是不变的, 如果业务比较忙的情况下, 会导致 binlog position 一直动.

    mysql> 
    FLUSH TABLES WITH READ LOCK;
  5. 在主库上面查询 binlog 的状态, 也就是之前的 show master status; 现在这个命令已经被替换成:

    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)
  6. 看完位置之后就可以 unlock 了.

    mysql> 
    UNLOCK TABLES;

从库需要做的事情

  1. 确认可以连接到主库. 网络部分.
  2. 创建一个复制任务, 设置下面的信息.
    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 公钥。
  3. 启动同步.
    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
  4. 错误的情况下需要重新配置, 清空IOTHREAD的配置.
    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 命令启动容器

#!/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 命令启动容器

#!/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

# 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

# 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

# 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.

mysql> 
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = host,
    SOURCE_PORT = port,
    SOURCE_USER = user,
    SOURCE_PASSWORD = password,
    SOURCE_AUTO_POSITION = 1;

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

mysql

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'                                                                                                                                                                            |