Analysis of MySQL example DTID master-slave principle

mysql> grant replication slave on *.* to 'slave'@'192.168.136.219' identified by 'slave';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#Turn off firewalls on both servers [root@mysql01 ~]# systemctl stop firewalld
[root@mysql01 ~]# setenforce 0
[root@mysql02 ~]# systemctl stop firewalld
[root@mysql02 ~]# setenforce 0
Test the connection from the library:
[root@mysql02 ~]# mysql -u slave -p'slave' -h192.168.136.239
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 

1. Lock the main database table mysql> flush tables with read lock;
2. Prepare the main database [root@mysql01 ~]# mysqldump -uroot -A > /clq/all-databases-20210519.sql
3. Copy to the slave host [root@mysql01 ~]# scp /clq/all-databases-20210519.sql [email protected]:/backup/
[root@mysql02 backup]# ll
-rw-r--r--. 1 root root 873527 May 19 16:40 all-databases-20210519.sql
4. Restore the master database from the database [root@mysql02 backup]# mysql -uroot -pHuawei0917@ < all-databases-20210519.sql 

[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
user = mysql
pid-file = /opt/data/mysql.pid
skip-name-resolve
#skip-grant-tables
log-bin = master_bin #Open the master database log server-id = 10 #The unique ID of the service
gtid-mode = on #GTID mode on enforce_gtid_consistency = on #Force gtid mode consistency log-slave-updates = 1 #Slave database allows update logs and synchronizes operation logs binlog_format = row #binlog log format is row format, the default is mixed mixed mode skip_slave_start = 1 #Skip slave database and start with master database #Restart systemctl restart mysqld 

[root@mysql02 data]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql 
datadir = /opt/data 
socket = /tmp/mysql.sock 
port = 3306
user = mysql
pid-file = /opt/data/mysql.pid
skip-name-resolve
#skip-grant-tables 
gtid_mode=on
enforce_gtid_consistency=on
server-id=20
log-bin=slave_binlog #Open slave log log_slave-updates=1 #Slave updates are allowed binlog_format=row #Format is row skip-slave_start=1   
#Restart systemctl restart mysqld 

mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
8 rows in set (0.00 sec)

#Configure the slave database as root #help change master to You can view the help document example mysql> change master to
    -> master_host='192.168.136.239',
    -> master_user='slave',
    -> master_password='slave',
    -> master_port = 3306, #Master library port-> master_auto_position = 1; #Position#master_use_gtid = current_pos
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;  
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G; 
Slave_IO_Running: Connecting
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
Ensure system consistency and authorization consistency

#Create a test database in the main databasemysql> create database test;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|mysql |
| performance_schema |
|sys|
| test |
+--------------------+
#Check the synchronization status from the database mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|mysql |
| performance_schema |
|sys|
| test |
+--------------------+
6 rows in set (0.00 sec)

#Create test02 database from the librarymysql> create database test02;
Query OK, 1 row affected (0.00 sec)
#View on the main databasemysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|mysql |
| performance_schema |
| sys | #There is no test02 library | test |
+--------------------+
5 rows in set (0.00 sec)

[root@mysql03 ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
user = mysql
pid-file = /opt/data/mysql.pid
skip-name-resolve
#skip-grant-tables
# replication config
log-bin = master_bin
server-id = 21 #id must be different from the previous one gtid-mode = on
enforce-gtid-consistency = on
log-slave-updates = 1
binlog-format = row
skip-slave-start = 1
#Check gtid statusmysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
#Since only one IP address was authorized before, authorize another IP address on the mysql01 master database
mysql> grant replication slave on *.* to 'slave'@'192.168.136.230' identified by 'slave';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#Test connection [root@mysql ~]# mysql -uslave -pslave -h192.168.136.239
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 17
Server version: 5.7.33-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> 
#mysql03 connects to the root user from the database and performs corresponding configuration [root@mysql03 ~]# mysql -uroot -p1
mysql> change master to
    -> master_host='192.168.136.239', #master database ip
    -> master_user='slave', #Ordinary user authorized by the master database-> master_password='slave',
    -> master_port=3306, #Master database port-> master_auto_position=1; #Position synchronization starts from 1#You can also view the help for configurationmysql> help change master to;
CHANGE MASTER TO
  MASTER_HOST='source2.example.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='password',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='source2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;
URL: https://dev.mysql.com/doc/refman/5.7/en/change-master-to.html

#Open mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.136.239
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master_bin.000002
          Read_Master_Log_Pos: 2172
               Relay_Log_File:mysql-relay-bin.000002
                Relay_Log_Pos: 2387
        Relay_Master_Log_File: master_bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes #If two yes are displayed, the operation is successful!
#mysql03 Check the database, and the database content is also synchronized successfullymysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|mysql |
| performance_schema |
|sys|
| test |
+--------------------+
5 rows in set (0.00 sec)

[root@master01 ~]# systemctl stop firewalld
[root@master01 ~]# systemctl disable firewalld
[root@master02 ~]# systemctl stop firewalld
[root@master02 ~]# systemctl disable firewalld
[root@slave ~]# systemctl stop firewalld
[root@slave ~]# systemctl disable firewalld

mysql> grant replication slave on *.* to 'user'@'192.168.136.%' identified by 'user';

[root@slave ~]# mysql -uuser -p'user' -h192.168.136.239
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 5
Server version: 5.7.33 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> grant replication slave on *.* to 'app'@'192.168.136.%' identified by 'app';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

[root@slave ~]# mysql -uapp -papp -h192.168.136.219
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 3
Server version: 5.7.33 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> 

#master01 host:
[root@master01 ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
user = mysql
pid-file = /opt/data/mysql.pid
skip-name-resolve
skip-grant-tables
log-bin = master_bin
server-id = 10
gtid-mode = on
enforce-gtid-consistency = on
log-slave-updates = 1
binlog-format = row
skip-slave-start = 1
#master02 host [mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
user = mysql
pid-file = /opt/data/mysql.pid
skip-name-resolve
                      #replication config
log-bin = master_bin
server-id = 11
gtid-mode = on
enforce-gtid-consistency = on
log-slave-updates = 1
binlog-format = row
skip-slave-start = 1
#slave host [mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
user = mysql
pid-file = /opt/data/mysql.pid
skip-name-resolve
log-bin = slave_bin
server-id = 13
gtid-mode = on
enforce-gtid-consistency = on
log-slave-updates = 1
binlog-format = row
skip-slave-start = 1

[root@master01 ~]# systemctl restart mysqld
[root@master02 ~]# systemctl restart mysqld
[root@slave ~]# systemctl restart mysqld

Method 1 (setting in mysql):
set global master_info_repository='table';
set global relay_log_info_repository='table';
Method 2 (set in /etc/my.cnf):
3. Set master_info_repository = TABLE in my.cnf 
relay_log_info_repository = TABLE   
#Check if the change is successfulmysql> show variables where variable_name in ('relay_log_info_repository','master_info_repository');
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| master_info_repository | TABLE |
| relay_log_info_repository | TABLE |
+---------------------------+-------+

#slave configures two master libraries GTID replication on the slave library mysql> change master to
    -> master_host='192.168.136.219', #mysql02 master database ip
    -> master_user='app', #Ordinary user authorized by mysql02 master database-> master_password='app', #Ordinary user password authorized by mysql02 master database-> master_port=3306, #Master database port-> master_auto_position=1 for channel 'master01'; #Position synchronization starts from 1, and the first slave is named master01
mysql> change master to
    -> master_host='192.168.136.239', #mysql01 master database ip
    -> master_user='user',              
    -> master_password='user',          
    -> master_port=3306, #Master library port-> master_auto_position=1 for channel 'master02'; #Position synchronization starts from 1, and the first slave is named master01
#Check the status of two slavesmysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.136.219
                  Master_User: app
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: slave02-relay-bin-master1.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             Slave_IO_Running: No
            Slave_SQL_Running: No #All are closed 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: 0
              Relay_Log_Space: 154
              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: NULL
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: 0
                  Master_UUID: 
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           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: b4326a77-0a31-11ec-a991-000c298d3571:1-2,
d68b404d-0a35-11ec-9df1-000c29581959:1
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: master1
           Master_TLS_Version: 
*************************** 2. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.136.239
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: slave02-relay-bin-master2.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 0
              Relay_Log_Space: 154
              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: NULL
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: 0
                  Master_UUID: 
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           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: b4326a77-0a31-11ec-a991-000c298d3571:1-2,
d68b404d-0a35-11ec-9df1-000c29581959:1
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: master2
           Master_TLS_Version: 
2 rows in set (0.00 sec)
#Open two slaves
mysql> start slave;
# Check the status again

#master01 main library creates a test databasemysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|mysql |
| performance_schema |
|sys|
| test |
+--------------------+
5 rows in set (0.00 sec)
#View the master02 database on mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|mysql |
| performance_schema |
| sys | #No content+--------------------+
4 rows in set (0.00 sec)
#slave view from the librarymysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|mysql |
| performance_schema |
|sys|
| test | #The test library has been synchronized+--------------------+
5 rows in set (0.00 sec)
#Create a RHCA database in the mysql02 main databasemysql> create database RHCA;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|RHCA|
|mysql |
| performance_schema |
|sys|
+--------------------+
5 rows in set (0.00 sec)
#slave from the library mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|RHCA|
|mysql |
| performance_schema |
| sys | #With the test library of mysql01 main library and the RHCA library of mysql02 | test |
+--------------------+
6 rows in set (0.00 sec)

mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)