Master-slave synchronization configuration of Mysql database

[root@localhost /]# mkdir -p /opt/docker/mysql1/conf/
[root@localhost /]# mkdir -p /opt/docker/mysql1/logs/
[root@localhost /]# mkdir -p /opt/docker/mysql1/data/

[root@localhost /]# docker run -d -p 6894:3306 --name mysql1 \
                    -v /opt/docker/mysql1/conf:/etc/mysql/ \
                    -v /opt/docker/mysql1/logs:/logs \
                    -v /opt/docker/mysql1/data:/var/lib/mysql \
                    --privileged=true \
                    -e MYSQL_ROOT_PASSWORD=qtykGhC29eP4Smpmysql:5.7

[root@localhost docker]# cp -r /opt/docker/mysql1/ /opt/docker/mysql2/

[root@localhost docker]# rm -f /opt/docker/mysql2/data/auto.cnf

[root@localhost docker]# docker run -d -p 6895:3306 --name mysql2 \
                    -v /opt/docker/mysql2/conf:/etc/mysql/ \
                    -v /opt/docker/mysql2/logs:/logs \
                    -v /opt/docker/mysql2/data:/var/lib/mysql \
                    --privileged=true \
                    -e MYSQL_ROOT_PASSWORD=qtykGhC29eP4Smpmysql:5.7

[root@localhost docker]# vim /opt/docker/mysql1/conf/my.cnf

[mysqld]
# Configuration of the master database server-id=1 # Uniqueness of the service id log-bin=mysql1-log # Enable binary log binlog-format=ROW # Logging mode replicate-do-db=db_docker # Name of the data to be replicated # replicate-ignore-db=db_docker # Name of the data that does not need to be replicated

[root@localhost docker]# vim /opt/docker/mysql2/conf/my.cnf

[mysqld]
# Configure from the library server-id=2 # Uniqueness of service id log-bin=mysql2-log # Enable binary log binlog-format=ROW # Logging mode binlog-do-db=db_docker # Name of data to be copied # binlog-ignore-db=db_docker # Name of data that does not need to be copied

[root@localhost docker]# docker restart mysql1
[root@localhost docker]# docker restart mysql2

-- Create a database CREATE DATABASE `db_docker`;
USE db_docker;
-- Create table CREATE TABLE `t_docker` (
    `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) DEFAULT NULL,
     PRIMARY KEY ( `id` )
) ENGINE = INNODB AUTO_INCREMENT = 0 DEFAULT CHARSET = utf8;

mysql> SHOW MASTER STATUS ;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 2223 | | | |
+------------------+----------+--------------+------------------+-------------------+

CHANGE MASTER TO
MASTER_HOST="192.168.101.59", # Host address your main server ip
Master_Port=6894, # Port MASTER_USER="root", # Account MASTER_PASSWORD="qtykGhC29eP4Smp", # Password MASTER_LOG_FILE="mysql-bin.000001", # The main library binary file name is filled in according to the actual situation MASTER_LOG_POS=377; # The main library binary file position is filled in according to the actual situation

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.101.59
                  Master_User: root
                  Master_Port: 6894
                Connect_Retry: 60
              Master_Log_File:mysql-bin.000001
          Read_Master_Log_Pos: 2223
               Relay_Log_File: 98394ee2fb48-relay-bin.000004
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_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_Master_Log_Pos: 2223
              Relay_Log_Space: 534
              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: 1
                  Master_UUID: aa58ab20-f500-11eb-aa65-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 logs; 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:

mysql>flush tables with read lock;

mysql>unlock tables;

mysql> STOP SLAVE;

mysql> RESET SLAVE;

docker exec -it mysql2 /bin/sh
#mysql2 container name can also be container id

mysql -u root -pqtykGhC29eP4Smp
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.35-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

[mysqld]
# Main database configuration # Specify a unique server ID. It can be 0 but the server will reject it, so the valid value is between 1 and 4294967295. Default value 1
# Official website https://dev.mysql.com/doc/refman/5.7/en/replication-options.html
server-id=1
# Enable binary logging. The binary file name can be a path, for example: /logs/mysql/log. However, you need to give the directory file permissions, otherwise MySQL will not have permission to write, resulting in an error.
log-bin=mysql1-log
# There are three logging modes # STATEMENT causes logging to be statement based.
# ROW causes logging to be row based. This is the default setting.
# MIXED causes logging to use the mixed format. Between the first two modes# Official website https://dev.mysql.com/doc/refman/5.7/en/binary-log-setting.html
binlog-format=ROW 
# The name of the database to copy. To specify multiple databases, you must use multiple instances of this option.
# Since database names can contain commas, if you provide a comma-separated list, the list will be treated as the name of a single database.
# Multiple instances:
# replicate-do-db=db_docker1 
# replicate-do-db=db_docker2 
replicate-do-db=db_docker 
#Data name that does not need to be replicated. Same configuration as above # replicate-ignore-db=db_docker #Data name that does not need to be replicated

[mysqld]
# Slave database configuration is the same as above. server-id=2 # Uniqueness of service id log-bin=mysql2-log # Open binary log binlog-format=ROW # Logging mode binlog-do-db=db_docker # Name of data to be copied # binlog-ignore-db=db_docker # Name of data that does not need to be copied

[auto]
server-uuid=aa58ab20-f500-11eb-aa65-0242ac110002

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.