MySQL 主从同步

启动两个 mysql 容器，一个做主节点，一个做从节点

docker run --detach --restart always \
       --name mysql-master \
       --publish 31100:3306 \
       --env 'MYSQL_ROOT_PASSWORD=root' \
       --env 'MYSQL_DATABASE=test01' \
       --env 'MYSQL_USER=user01' \
       --env 'MYSQL_PASSWORD=user01' \
       --volume /srv/mysql-master/conf:/etc/mysql/conf.d \
       mysql:5.7.26

docker run --detach --restart always \
       --name mysql-slave \
       --publish 31101:3306 \
       --env 'MYSQL_ROOT_PASSWORD=root' \
       --env 'MYSQL_DATABASE=test01' \
       --env 'MYSQL_USER=user01' \
       --env 'MYSQL_PASSWORD=user01' \
       --volume /srv/mysql-slave/conf:/etc/mysql/conf.d \
       mysql:5.7.26

配置 slave 节点启动参数，指定需要同步和复制的数据库

[mysqld]
server-id           = 101
log-bin             = binlog
replicate-do-db     = test01
replicate-ignore-db = information_schema
replicate-ignore-db = mysql
replicate-ignore-db = performance_schema
replicate-ignore-db = sys

master 上授权给 slave 复制权限

grant replication slave on *.* to 'repl'@'%' identified by 'repl';
flush privileges;

slave 指向 master 的用户名和密码是在 slave 节点中动态配置

-- 配置主库同步的用户信息
change master to
master_host='172.17.0.2',
master_port=3306,
master_user='repl',
master_password='repl';

-- 指定 binlog 文件
change master to
master_host='172.17.0.2',
master_port=3306,
master_user='repl',
master_password='repl'
master_log_file='binlog.000004';

启动 slave 节点中 slave 服务

stop slave;
start slave;

查看 slave 状态，确保参数 Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes

show slave status\G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.2
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000003
          Read_Master_Log_Pos: 1099
               Relay_Log_File: 76af8d33cb4e-relay-bin.000007
                Relay_Log_Pos: 582
        Relay_Master_Log_File: binlog.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test01
          Replicate_Ignore_DB: information_schema,mysql,performance_schema,sys
           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: 1099
              Relay_Log_Space: 959
              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: 100
                  Master_UUID: c0083b30-dbd5-11eb-a18a-0242ac110002
             Master_Info_File: /var/lib/mysql/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: