What is MySQL master-slave
The master-slave database includes one master database and one or more slave databases. The data in the master database will be synchronized to each slave database.
Principle of master-slave replication
- 1. When the master node performs insert, update, and delete operations, it will be written to the binlog in order.
- 2. If the slave is connected to the master main library, the number of slave threads in the master will create the number of binlog dump threads.
- 3. When the binlog of the master node changes, the binlog dump thread will notify all the salve nodes and push the corresponding binlog content to the slave node.
- 4. After receiving the binlog content, the I / O thread writes it to the local relay log.
- 5. The SQL thread reads the relay log written by the I / O thread, and performs corresponding operations on the slave database according to the content of the relay log.
The role of master and slave
- Realize the separation of reading and writing
- Read write separation can improve the concurrency of database
- When backing up data
- High availability of database can be achieved by using appropriate middleware
Building a master-slave environment
Construction of main library
- Pull the database image, here is the mysql5.7 image
Create master library container
docker run -d --name master -p 3307:3306 -v /www/m2/master/conf/:/etc/mysql/conf.d -v /www/m2/master/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root mysql:5.7
Main library configuration my.conf
#MySQL configuration item [client] port=3306 user=mysql default-character-set=utf8mb4 [mysql] default-character-set=utf8mb4 [mysqld] port=3306 user=mysql character-set-server = utf8mb4 default-time_zone = '+8:00' server_id=1 log_bin=mysql-bin binlog_format=ROW slow-query-log-file=/logs/slow-query.log long_query_time=1
Enter the MySQL on the container link and add a test user to synchronize from the database
docker exec master -it /bin/bash mysql -uroot -proot #Configure test users and permissions (the password is also test) grant replication slave on *.* to 'test'@'%' identified by 'test'; flush privileges; #Here, grant replication slave is a command format, which means that the following accounts are given permission to copy.
View the binlog status of the master node
show master status\G; #Remember the values of file and position, which will be used in slave library configuration.
Build from library
docker run -d --name slave --privileged=true --link master:master -p 3308:3306 -v /www/m2/salve/conf/:/etc/mysql/conf.d -v /www/m2/salve/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root mysql:5.7
Configure my.conf from library
#MySQL configuration item [client] port=3306 user=mysql default-character-set=utf8mb4 [mysql] default-character-set=utf8mb4 [mysqld] port=3306 user=mysql character-set-server = utf8mb4 default-time_zone = '+8:00' server_id=2 log-bin=mysql-slave-bin relay_log=mysql-relay-bin
Enter the container and link to the main library
docker exec slave -it /bin/bash mysql -uroot -proot #MySQL command #Link master configuration change master to master_host='172.17.0.4', master_user='test', master_password='test', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=313, master_connect_retry=30; #Start slave Library start slave; #View slave library status show slave status\G;
- In the above change command, master_ user，master_ Password is the account number and password set for the slave database when configuring the master database; master_ Port is the port of MySQL in the main database container. Don’t write it as the port mapped from the host to the main database container; master_ log_ file，master_ log_ POS is the query value when the main database is configured, which specifies the synchronization location.
Show slave status description
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.17.0.4 Master_User: test Master_Port: 3306 Connect_Retry: 30 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 622 Relay_Log_File: edu-mysql-relay-bin.000003 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: 622 Relay_Log_Space: 1006 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: 1cdd11f9-a256-11eb-8d55-0242ac110004 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:
When slave_ IO_ Running , Slave_ SQL_ When running is all yes, it means the startup is successful. Operations in the master library can be successfully synchronized to the slave library.
Failed to link master library from library
Show slave status prompt:
Last_IO_Error: error connecting to master '[email protected]:3307' - retry-time: 30 retries: 1
Main reasons: host error; Account password error; Port error. Before, 3307 host mapping port was written, which led to link failure;
Slave_ SQL_ Running: no synchronous data interrupt
Method 1: skip the error that causes the interrupt mysql> stop slave ; mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; mysql> start slave ; Method 2: reset the synchronization position First, stop the slave service: MySQL > slave stop; Check the host status on the master server and record the corresponding values of file and position; Set the synchronization position from the library; Start synchronization.