1. Introduction to master-slave replication
1.1. Advantages of master-slave replication
-
Separation of reading and writing. In a system with complex business, there is a scenario where a SQL statement requires a table lock, resulting in the temporary inability to use the read service, which greatly affects the running business. Use master-slave replication to let the master database be responsible for writing and the slave database. Responsible for reading, so that even if the master database locks the table, the normal operation of the business can be ensured by reading from the slave database.
-
Do hot backup of data
-
Architecture extensions. The business volume is getting larger and larger, and the frequency of I/O access is too high, which cannot be satisfied by a single machine. At this time, multi-database storage is used to reduce the frequency of disk I/O access and improve the I/O performance of a single machine.
1.2. Master-slave replication type
-
Statement-based replication. Execute SQL statements on the server and execute the same statements on the slave server. MySQL uses statement-based replication by default, which has high execution efficiency.
-
Row-based replication. Copy the changed content instead of executing the command again on the slave server.
-
Mixed types of replication. Statement-based replication is used by default. Once it is found that statement-based replication cannot be exact, row-based replication will be used.
1.3. Principle of master-slave replication
MySQL’s master-slave replication is an asynchronous replication process. Data will be copied from one MySQL database (Master) to another MySQL database (Slave). The entire master-slave replication process between Master and Slave is participated by three threads. Completed.
-
There are two threads on the Slave side: SQL thread and IO thread.
-
There is one thread on the Master side: the I/O thread.
To implement MySQL master-slave replication, you must first turn on the binlog recording function on the Master side, otherwise it will not be possible. binlog: binary log is a binary file that saves all update event logs in the main library. Because the entire replication process is actually that the Slave obtains the binlog log from the Master, and then performs various SQL operations on the Slave in the same order to record the records in the obtained binlog log.
-
Update data on the Master, write it to the binary log (Binary log), and notify the storage engine to commit the transaction.
-
Slave enables the I/O thread to copy the Master’s binary log to the Slave’s relay log (Relay log).
-
Slave enables the SQL thread to read events from the Relay log and replay the events to update the Slave data to make it consistent with the data in the Master. As long as the thread is consistent with the I/O thread, the relay log will usually be located in the OS cache. , so the overhead of relaying logs is very small.
1.4. Basic principles of master-slave replication
-
Each Slave has only one Master
-
Each Slave can only have one unique service ID
-
Each Master can have multiple Slaves
2. Master-slave replication case
2.1. Environment preparation
-
CentOS 7.5 (three virtual machines: one master and two slaves)
-
MySQL 5.7.30
-
JDK-8u202
2.2. CentOS 7.5 environment
| IP address | Host name | |
|---|---|---|
| master | 192.168.8.30 | mysql30 |
| slave1 | 192.168.8.31 | mysql31 |
| slave2 | 192.168.8.32 | mysql32 |
MySQL has been installed on the master. The remaining two will be cloned directly.
After the first slave1 clone is completed, perform the following operations.
Set hostname
# Set hostname hostnamectl set-hostname mysql31 # Check the host name hostname
Set IP address (virtual machine network is set to NAT)
# vim /etc/sysconfig/network-scripts/ifcfg-ens33 IPADDR=192.168.8.31
Restart network service
systemctl restart network # Test network ping www.baidu.com
Shut down the firewall (temporarily closed for experimental needs, but still open for production environments)
systemctl stop firewalld # Stop the firewall service systemctl disable firewalld # Do not start the firewall service at boot
Key points
There is an auto.cnf file in the /usr/local/mysql/data/ directory. The MySQLserver-uuid in the two virtual machines must not be different.
# slave slave server [root@mysql31 ~]# cd /usr/local/mysql/data [root@mysql31 data]# cat auto.cnf [auto] server-uuid=596c15fb-b4ee-11ec-aec9-000c2930ab81 [root@mysql31 data]# vim auto.cnf [root@mysql31 data]# cat auto.cnf [auto] server-uuid=596c15fb-b4ee-11ec-aec9-000c2930ab82
Restart the MySQL service
[root@mysql31 data]# systemctl stop mysqld [root@mysql31 data]# systemctl start mysqld [root@mysql31 data]#
2.3. Master environment configuration
Configuration options:
Configure a unique server.id (it is recommended to use the last 3 digits of the IP)
Enable binary logging
Get the master binary file name and location
Create a user account for slave and master communication
Configuration file my.cnf modification
[root@mysql30 mysql]# vim /etc/my.cnf # Add in [mysqld]: server-id=1 log_bin=mysql-bin log_bin_index=mysql-bin.index binlog_ignore_db=mysql # Remark: # server-id: unique identifier of the server. # log_bin: Start the MySQL binary log, that is, data synchronization statements. These statements will be executed one by one from the database. # binlog_do_db: Specify the database that records the binary log, that is, the name of the database that needs to be copied. If you copy multiple databases, just set this option repeatedly. # binlog_ignore_db specifies a database that does not record binary logs, that is, a database name that does not need to be copied. If there are multiple databases, just set this option repeatedly. # It should be noted that binlog_do_db and binlog_ignore_db are mutually exclusive options, generally only one is required.
Create users and permissions from the server
# Start the MySQL service [root@mysql30 ~]# systemctl status mysqld # Log in to mysql database #Enter mysql database [root@mysql30 ~]# mysql -uroot -p Enter password: #Create the masterbackup user and permissions from the database mysql> grant replication slave on *.* to masterbackup@'192.168.8.%' identified by '000000'; Query OK, 0 rows affected, 1 warning (0.01 sec) # 192.168.8.% wildcard, indicating that IPs from 0 to 255 can access the master server. For formal environment, please configure the specified slave server IP. # If 192.168.8.% is changed to %, any IP can be used as its slave database to access the main server. #Exit mysql mysql> exit; Bye
Restart the MySQL service
[root@mysql30 ~]# systemctl restart mysqld
[root@mysql30 ~]# systemctl status mysqld
● mysqld.service – MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2022-04-10 21:51:06 CST; 12s ago
Docs: man:mysqld(8)
Main PID: 1509 (mysqld_safe)
CGroup: /system.slice/mysqld.service
├─1509 /bin/sh /usr/local/mysql/bin/mysqld_safe
└─1856 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/...
Apr 10 21:51:06 mysql30 systemd[1]: Stopped MySQL Server.
Apr 10 21:51:06 mysql30 systemd[1]: Started MySQL Server.
Apr 10 21:51:06 mysql30 mysqld_safe[1509]: 2022-04-10T13:51:06.509340Z mysqld_safe Log...'.
Apr 10 21:51:06 mysql30 mysqld_safe[1509]: 2022-04-10T13:51:06.547444Z mysqld_safe Sta...ta
Hint: Some lines were ellipsized, use -l to show in full.
View master server status
mysql> show master status; + ------------------- + ---------- + --------------- + --- --------------- + ------------------ + | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | + ------------------- + ---------- + --------------- + --- --------------- + ------------------ + | master-bin.000002 | 154 | | mysql | | + ------------------- + ---------- + --------------- + --- --------------- + ------------------ + 1 row in set (0.00 sec) mysql>
2.4. Configuration of slave server
Configuration options:
Configure a unique server.id (it is recommended to use the last 3 digits of the IP)
Read the master binary log using the user account assigned by the master
Start the slave service
Modification of configuration file my.cnf
[root@mysql31 ~]# vim /etc/my.cnf #Add in [mysqld]: server-id=2 log-bin=/usr/local/mysql/data/binlog/mysql-bin relay-log=/usr/local/mysql/data/relay/slave-relay-bin relay-log-index=/usr/local/mysql/data/relay/slave-relay-bin.index #replicate-do-db=db1 #replicate-do-db=db2 #Remark: # server-id: The unique identifier of the server. If there are multiple slave servers, the server-id of each server cannot be repeated. It is the same unique identifier as the IP. If you do not set the server-id or set it to 0, the slave server will not Connect to the main server. # log-bin: Enable binary log function, binlog data location. If you only write "log-bin=mysql-bin", the file is placed under the "/usr/local/mysql/data/" directory # relay-log: Start the MySQL binary log, which can be used for data backup and crash recovery. Or if the master server hangs up, this slave server will be used as the master server of other slave servers. If you only write "relay-bin=slave-relay-bin", the file is placed under the "/usr/local/mysql/data/" directory # relay-log-index: The binlog is transferred to the standby machine and written into the relaylog. The slave sql thread of the standby machine reads from the relaylog and then applies it locally. #replicate-do-db: Specify the database to be synchronized. If multiple databases are replicated, just set this option repeatedly. If binlog-do-db is not specified on the master side, replication-do-db can be used to filter on the slave side. # replicate-ignore-db: Databases that do not need to be synchronized. If there are multiple databases, just set this option repeatedly. # Note: replicate-do-db and replicate-ignore-db are mutually exclusive options, generally only one is required.
Restart mysql service
# Start service
[root@mysql31 ~]# systemctl start mysqld
# Check mysql service status
[root@mysql31 ~]# systemctl status mysqld
● mysqld.service – MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2022-04-10 21:57:48 CST; 6s ago
Docs: man:mysqld(8)
Main PID: 1145 (mysqld_safe)
CGroup: /system.slice/mysqld.service
├─1145 /bin/sh /usr/local/mysql/bin/mysqld_safe
└─1480 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/...
Apr 10 21:57:48 mysql31 systemd[1]: Started MySQL Server.
Apr 10 21:57:48 mysql31 mysqld_safe[1145]: 2022-04-10T13:57:48.653093Z mysqld_safe Log...'.
Apr 10 21:57:48 mysql31 mysqld_safe[1145]: 2022-04-10T13:57:48.699989Z mysqld_safe Sta...ta
Hint: Some lines were ellipsized, use -l to show in full.
Connect to the master server
#Enter mysql database [root@mysql31 ~]# mysql -uroot -p Enter password: #Connect to the master server mysql> change master to master_host='192.168.8.30',master_port=3306,master_user='masterbackup',master_password='000000',master_log_file='master-bin.000002',master_log_pos=154; # Remark: # master_host corresponds to the IP address of the master server. # master_port corresponds to the port of the master server. # master_log_file corresponds to the File column displayed by show master status: master-bin.000002. # master_log_pos corresponds to the Position column displayed by show master status: 154.
Start slave data synchronization
mysql> start slave; # Stop slave data synchronization. # mysql> start slave;
Start slave
[root@mysql31 data]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.30-log MySQL Community Server (GPL)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ********************* *******
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.8.30
Master_User: masterbackup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: slave-relay-bin.000003
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000002
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: 154
Relay_Log_Space: 528
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: 596c15fb-b4ee-11ec-aec9-000c2930ab81
Master_Info_File: /usr/local/mysql/data/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:
1 row in set (0.00 sec)
ERROR:
No query specified
If both Slave_IO_Running and Slave_SQL_Running are YES, the synchronization is successful.
2.5. Testing
Create test data on master
mysql> create database db410; Query OK, 1 row affected (0.00 sec) mysql> mysql> use db410; Database changed mysql> mysql> create table t1(id int); Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values (1),(2),(3); Query OK, 3 rows affected (0.08 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t1; + ------ + | id | + ------ + | 1 | | 2 | | 3 | + ------ + 3 rows in set (0.00 sec)
Query testing on slave
mysql> show databases; + -------------------- + | Database | + -------------------- + | information_schema | |db1| | db410 | | mysql | | performance_schema | | sys | + -------------------- + 6 rows in set (0.00 sec) mysql> mysql> use db410; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> mysql> select * from t1; + ------ + | id | + ------ + | 1 | | 2 | | 3 | + ------ + 3 rows in set (0.00 sec) mysql>
3. Others
3.1. Frequently Asked Questions
3.1.1. The UUIDs of the master and slave databases are the same
Generally, the reasons why Slave_IO_Running is No:
-
The master-slave network is unavailable;
-
There is a problem with the my.cnf configuration file;
-
The password, file name, and pos offset are incorrect;
-
The firewall is not turned off;
-
The service-uuid in the auto.cnf file is the same as the master and slave.
View slave information
mysql> show slave status\G;
The UUIDs of the master and slave databases are the same, that is, the server-uuid values in auto.cnf in the data directory are the same. Just find a letter and change it, and then restart the database.
# master master server [root@mysql30 ~]# cd /usr/local/mysql/data/ [root@mysql30 data]# cat auto.cnf [auto] server-uuid=596c15fb-b4ee-11ec-aec9-000c2930ab81 [root@mysql30 data]# # slave slave server [root@mysql31 data]# cat auto.cnf [auto] server-uuid=596c15fb-b4ee-11ec-aec9-000c2930ab81 [root@mysql31 data]# vim auto.cnf [root@mysql31 data]# cat auto.cnf [auto] server-uuid=596c15fb-b4ee-11ec-aec9-000c2930ab82
Restart the MySQL service
[root@mysql31 data]# systemctl stop mysqld [root@mysql31 data]# systemctl start mysqld [root@mysql31 data]#
3.1.2. ERROR 1198
Master-slave synchronization has been set up in the database before, and master-slave synchronization has also been turned on.
stop slave; change master to ... (check the latest File and Position)
3.1.3. ERROR 1201
The database had previously set up master-slave synchronization, causing this problem to occur.
Solution: Close and recharge slave (executed in slave mysql.)
stop slave; reset slave; change master to ... (check the latest File and Position)
3.2. Slave restore independent server
slave stops service
systemctl stop mysqld
Delete slave library file
[root@mysql31 ~]# rm -rf /var/lib/mysql/master.info [root@mysql31 ~]# rm -rf /var/lib/mysql/mysql53-relay-bin.* [root@mysql31 ~]# rm -rf /var/lib/mysql/relay-log.info
Modify slave configuration file
[root@mysql31 ~]# vim /etc/my.cnf [mysqld] #server_id=2 //Delete this line
Restart the service to check the master-slave status
[root@mysql31 ~]# systemctl start mysqld [root@mysql31 ~]# mysql -uroot -proot -e "show slave status\G"
3.3. Detailed explanation of show slave status parameters
mysql> show slave status\G; *************************** 1. row ********************* ******* # Wait for events to occur on master Slave_IO_State: Waiting for master to send event # Current main server host Master_Host: 192.168.8.30 # The current user used to connect to the main server Master_User: masterbackup # Current main server interface Master_Port: 3306 # Current value of master-connect-retry option Connect_Retry: 60 # The name of the main server binary log file currently being read by the "I/O thread". Master_Log_File: master-bin.000002 # In the current main server binary log, the position that the I/O thread has read Read_Master_Log_Pos: 154 #The name of the relay log file currently being read and executed by the SQL thread Relay_Log_File: slave-relay-bin.000003 # In the current relay log, the position where the SQL thread has read and executed Relay_Log_Pos: 321 # The name of the main server binary log file executed by the SQL thread that contains the most recent events Relay_Master_Log_File: master-bin.000002 # Whether the I/O thread is started and successfully connected to the main server. (Yes means normal, No means abnormal) Slave_IO_Running: Yes # Whether the SQL thread is started Slave_SQL_Running: Yes # Current value of replicate-do-db option Replicate_Do_DB: # Current value of replicate-ignore-db option Replicate_Ignore_DB: # Current value of replicate-do-table option Replicate_Do_Table: # Current value of replicate-ignore-table option Replicate_Ignore_Table: # Current value of replicate-wild-do-table option Replicate_Wild_Do_Table: # Current value of replicate-wild-ignore_table option Replicate_Wild_Ignore_Table: #Latest error code Last_Errno: 0 #Latest error content Last_Error: # Recently used value for SQL_SLAVE_SKIP_COUNTER Skip_Counter: 0 # The last time position of the binary log from the master server executed by the SQL thread (Relay_Master_Log_File). #(Relay_Master_Log_File,Exec_Master_Log_Pos) in the main server's binary log corresponds to (Relay_Log_File,Relay_Log_Pos) in the relay log Exec_Master_Log_Pos: 154 # The total size of all original relay logs combined Relay_Log_Space: 528 # If the UNTIL clause is not specified, there is no value. The value is Master if the slave is reading until the given position in the master's binary log is reached. # Value is Relay if the slave is reading until the given position in its relay log is reached. Until_Condition: None # Used to indicate the log file name. The log file name and position value define the point at which the SQL thread terminates execution. Until_Log_File: # Used to indicate the log position value. The log file name and position value define the point at which the SQL thread terminates execution. Until_Log_Pos: 0 # If SSL connections to the master server are allowed, the value is Yes. If SSL connections to the master server are not allowed, the value is No. If SSL connections are allowed, but the slave server does not have SSL support enabled, the value is Ignored. Master_SSL_Allowed: No # Master_SSL_CA_File: # Master_SSL_CA_Path: # Master_SSL_Cert: # Master_SSL_Cipher: # Master_SSL_Key: # This field is an indication of how "lagging" the slave server is. While the slave SQL thread is running (processing updates), this field is the number of seconds that have elapsed since the timestamp of the most recent event executed by this thread on the master server. This field is zero when this thread is caught up by the slave server I/O thread and enters an idle state, waiting for more events from the I/O thread. 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: 596c15fb-b4ee-11ec-aec9-000c2930ab81 Master_Info_File: /usr/local/mysql/data/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:
3.4. Commonly used statements
# Check the status of the master, especially the current log and location show master status; # Check the status of slave show slave status; #Reset slave status reset slave; # Start slave state start slave; # Pause slave state stop slave;
3.5 Determine the active and backup non-delay solution
The first
Each time before executing a query request from the library, first determine whether
seconds_behind_masteris equal to 0. If it is not equal to 0, you must wait until this parameter becomes 0 before executing the query request.
The second kind
-
Master_Log_File and Read_Master_Log_Pos represent the latest position of the main library read
-
Relay_Master_Log_File and Exec_Master_Log_Pos represent the latest position of the standby database execution
If the two sets of values of Master_Log_File and Read_Master_Log_Pos, Relay_Master_Log_File and Exec_Master_Log_Pos are exactly the same, it means that the received log has been synchronized.
The third kind
Compare the GTID set to ensure no delay between active and standby:
-
Auto_Position=1 indicates that this set of primary and secondary relationships uses the GTID protocol.
-
Retrieved_Gitid_Set is the GTID set of all logs received by the standby database
-
Executed_Gitid_Set is a set of all GTIDs that have been executed in the standby database.
If the two sets are the same, it also means that the logs received by the standby database have been synchronized.
The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. MySQL entry skill treeSQL advanced skillsCTE and recursive query 76964 people are learning the system