MySQL master-slave replication principle and practice detailed explanation

Note:
- Master-slave replication is asynchronous and logical SQL statement-level replication;
- During replication, the master has one I/O thread, and the slave has two threads, the I/O and SQL threads;
- The necessary condition for implementing master-slave replication is that the master database must enable the binlog function;
- The server-id of all MySQL nodes used as replication cannot be the same;
- The binlog file only records SQL statements that change the data content, and does not record any query statements.

Mac: 10.15.1
Docker: 2.0.0.3 // Using Docker to install MySQL is mainly for easy management and maintenance, independent IP, and startup in seconds MySQL-master: 5.7.29 // Main server MySQL-master IP: 172.17.0.3
MySQL-slave: 5.7.29 // MySQL-slave IP from server: 172.17.0.4

1. Create the directory structure:
master/conf, master/data, master/logs
2. Start the running instance container:
docker run --name mysql-master
> -p 3310:3306
> -v ~/docker/master/conf:/etc/mysql/conf.d
> -v ~/docker/master/data:/var/lib/mysql
> -v ~/docker/master/logs:/var/log/mysql
> -e MYSQL_ROOT_PASSWORD=123456
> -d mysql:5.7
3. Enter the container docker exec -it mysql-master bash
4. Log in to MySQL
mysql -uroot -p

1. Create the directory structure:
slave/conf, slave/data, slave/logs
2. Start the running instance container:
docker run --name mysql-slave
> -p 3310:3306
> -v ~/docker/slave/conf:/etc/mysql/conf.d
> -v ~/docker/slave/data:/var/lib/mysql
> -v ~/docker/slave/logs:/var/log/mysql
> -e MYSQL_ROOT_PASSWORD=123456
> -d mysql:5.7
3. Enter the container docker exec -it mysql-slave bash
4. Log in to MySQL
mysql -uroot -p

Primary Server:

[mysqld]
port = 3306
server-id = 1
#Database to be synchronized binlog-do-db = school
#To generate binary log files, the master server must enable log-bin = mysql-bin

Restart MySQL: docker restart mysql-master

From the server:

[mysqld]
port = 3306
server-id = 2
#Database to be synchronized binlog-do-db = school
#To generate binary log files (optional from the server)
log-bin = mysql-bin

Restart MySQL: docker restart mysql-slave

create user 'slave'@'%' identified by '123456'; //Create user grant replication slave,replication client on *.* to 'slave'@'%'; //Set user privileges flush privileges; //Refresh privileges show grants for 'slave'@'%'; //View user privileges

1. Log in to the master and execute the lock table operation mysql -uroot -p
FLUSH TABLES WITH READ LOCK;
2. Dump the data of the db that needs to be synchronized in the master mysqldump -uroot -p school > school.dump
3. Import data into slave
mysql -uroot -h172.17.0.4 -p school < school.dump
4. Unlock the master
UNLOCK TABLES;

1. Create a new data table and add data create table user( id int(10) auto_increment, name varchar(30), primary key (id) )charset=utf8mb4;
insert into user(name) values(222);
2. Master server binlog record status mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 769 | school | | |
+------------------+----------+--------------+------------------+-------------------+

1. Connect to the master server and set the starting node for replication mysql> change master to master_host='172.17.0.3',
-> master_port=3306,
-> master_user='slave',
-> master_password='123456',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=769;
2. Start replication start slave;
3. Check the replication status mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.3
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:mysql-bin.000001
Read_Master_Log_Pos: 961
Relay_Log_File: 87dc5224655d-relay-bin.000003
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes //Indicates that the I/O thread reads successfully Slave_SQL_Running: Yes //Indicates that the SQL thread executes successfully 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: 961
Relay_Log_Space: 892
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: 45540733-4e0c-11ea-b0ac-0242ac110003
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:
4. View the data table datamysql> show create table user\G
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4

mysql> select * from user;
+----+------+
| id | name |
+----+------+
| 1 | 222 |
+----+------+

Copy control related commands:
stop salve //Stop slave connection reset slave //Reset slave connection start slave //Open slave connection stop master //Stop master connection reset master //Reset master connection start master //Open master connection