Analysis of the Principle and Function of MySQL Database Master-Slave Replication

#Binary installation: https://blog.csdn.net/qq_47945825/article/details/116848970?spm=1001.2014.3001.5501
#Or install from a network repository: (generally binary installation)
https://blog.csdn.net/qq_47945825/article/details/116245442?spm=1001.2014.3001.5501

[root@mysql01 ~]# mysql -uroot -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
|mysql |
| performance_schema |
|sys|
+--------------------+
[root@mysql02 ~]# mysql -uroot -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
|mysql |
| performance_schema |
|sys|
+--------------------+

mysql> create user 'vvv'@'192.168.136.191' identified by 'vvv0917';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.*to 'vvv'@'192.168.136.191';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

[root@mysql02 ~]# mysql -uvvv -vvv0917 -h192.168.136.145
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

[root@mysql01 ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
log-bin=mysql_bin #Start binlog log server-id=10 #Database server unique identifier, id must be smaller than the slave database #Restart the service (this restart method requires that the mysqld.service file has been configured)
[root@mysql01 ~]# systemctl restart mysqld
Observe the status of the primary database:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000004 | 962 | | | |
+------------------+----------+--------------+------------------+---

[root@mysql02 ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql 
datadir = /opt/data 
socket = /tmp/mysql.sock 
port = 3307
user = mysql
pid-file = /opt/data/mysql.pid
skip-name-resolve
#skip-grant-tables 
server-id=20 #Server id, greater than the primary database id
relay-log=mysql_relay_log #Start relay log #log-bin=mysql-bin 
#Restart the service:
[root@mysql02 ~]# systemctl restart mysqld

[root@slave02 ~]# mysql -uroot -p
mysql> change master to
    -> master_host='192.168.136.145',
    -> master_user='vvv',
    -> master_password = 'vvv0917',
    -> master_log_file='mysql_bin.000004',
    -> master_log_pos=962;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave; #stop slave to shut down Query OK, 0 rows affected (0.01 sec)
#View the configuration status:
mysql> show slave status\G; 
   Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.136.145
                  Master_User: vvv
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000004
          Read_Master_Log_Pos: 962
               Relay_Log_File: mysql_relay_log.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            #Both must be yes here, which means the configuration is successful, otherwise it fails

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|mysql |
| performance_schema |
|sys|
+--------------------+

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|mysql |
| performance_schema |
|sys|
+--------------------+

mysql> create database clq;
Query OK, 1 row affected (0.00 sec)
mysql> create table clq01(id int(11)not null primary key auto_increment,name varchar(100)not null,age tinyint(4)); 
mysql> insert clq01(name,age) values('A',20),('B',21),('C',22);
Query OK, 3 rows affected (0.00 sec)

mysql> select * from clq01;
+----+------+------+s
| id | name | age |
+----+------+------s+
| 1 | A | 20 |
| 2 | B | 21 |
| 3 | C | 22 |
+----+------+------+
                              #Master-slave replication completed!

flush tables with read lock:
quit: Exit to unlock (unlock after backup is completed)

#First prepare the main database [root@mysql01 ~]# mysqldump -uroot -A > all-databases.sql 
#Copy data to the slave database [root@mysql01 ~]# ls /clq
all-databases.sql
[root@mysql01 ~]# scp /clq/all-databases.sql [email protected]:/clq/
The authenticity of host '192.168.136.193 (192.168.136.193)' can't be established.
ECDSA key fingerprint is SHA256:XIAQEoJ+M0vOHmCwQvhUdw12u5s2nvkN0A4TMKLaFiY.
Are you sure you want to continue connecting (yes/no/[fingerprint])yes
[email protected]'s password: 
all-databases.sql 100% 853KB 115.4MB/s 00:00  
[root@mysql02 clq]# ll
Total usage 896 #View from the database -rw-r--r--. 1 root root 873266 May 17 19:36 all-databases.sql

[root@mysql02 clq]# mysql -uroot -pHuawei0917@ < all-databases.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql02 clq]# mysql -uroot -pHuawei0917@ -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| clq |
|mysql |
| performance_schema |
|sys|
+--------------------+
Main library:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| clq |
|mysql |
| performance_schema |
|sys|
+--------------------+

[root@mysql01 ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
log-bin=mysql_bin #log file server-id=10 #unique identifier of service id
[root@mysql02 ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql 
datadir = /opt/data 
socket = /tmp/mysql.sock 
port = 3307
user = mysql
pid-file = /opt/data/mysql.pid
skip-name-resolve
#skip-grant-tables 
server-id=20 #Uniquely identifies the service id (larger than the main database)
relay-log=mysql_relay_log #Relay log#log-bin=mysql-bin 

mysql> show processlist;
+----+------+-----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| 5 | repl | 192.168.136.219:39788 | NULL | Binlog Dump | 1575 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 7 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-----------------------+------+-------------+------+---------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)