MySQL supports one-way, asynchronous replication, where one server acts as the master while one or more other servers act as slaves. The master server writes updates to a binary log file and maintains an index of that log file to track log rotation.
The slave keeps track of the last successfully read update position from the master’s logs. The slave receives any updates that have occurred since that point, then blocks and waits for the master to notify it of the next update.
In real-world projects, two servers located in different geographical locations each have a MySQL database installed. The two servers are configured as mutual primary and backup. The client requires that when one machine fails, the other can take over the applications running on it. This requires the data in both databases to remain consistent in real-time. Here, we use MySQL’s synchronization feature to achieve dual-machine synchronous replication.
The following is an operational example:
1. Database Synchronization Setup
Server OS: RedHat Enterprise Linux 5
Database Version: MySQL Ver 14.12 Distrib 5.0.22
Prerequisite: MySQL database is running normally
Assume the two server addresses are:
ServA: 172.18.3.3
ServB: 172.18.3.4
1.1 Configure the Synchronization Account
On ServA, add an account that ServB can use to log in:
MySQL>GRANT all privileges ON *.* TO tongbu@'172.18.3.4' IDENTIFIED BY '123456'; (Note: tongbu is the account used for synchronization, and 123456 is the password for tongbu)
On ServB, add an account that ServA can use to log in:
MySQL>GRANT all privileges ON *.* TO tongbu@'172.18.3.3' IDENTIFIED BY '123456';
1.2 Configure Database Parameters
1. Log in to ServA as root and modify ServA’s my.cnf file
vi /etc/my.cnf
Add the following configuration under the [MySQLd] section:
1 default-character-set=utf8
2
3 log-bin=MySQL-bin
4
5 relay-log=relay-bin
6
7 relay-log-index=relay-bin-index
8
9 server-id=1
10
11 master-host=172.18.3.4 # IP of the peer synchronization server
12
13 master-user=tongbu # Account used for synchronization
14
15 master-password=123456 # Password for the synchronization account
16
17 master-port=3306 # Port of the peer synchronization server
18
19 master-connect-retry=30 # Synchronization retry interval, in seconds
20
21 binlog-do-db=umsdb # Database to be synchronized
22
23 replicate-do-db=umsdb # Database to be synchronized
24
25 replicate-ignore-table=umsdb.boco_tb_menu
26
27 replicate-ignore-table=umsdb.boco_tb_connect_log
28
29 replicate-ignore-table=umsdb.boco_tb_data_stat
30
31 replicate-ignore-table=umsdb.boco_tb_log_record
32
33 replicate-ignore-table=umsdb.boco_tb_workorder_record
2. Log in to ServB as root and modify ServB’s my.cnf file
vi /etc/my.cnf
Add the following configuration under the [MySQLd] section:
1 default-character-set=utf8
2
3 log-bin=MySQL-bin
4
5 relay-log=relay-bin
6
7 relay-log-index=relay-bin-index
8
9 server-id=2
10
11 master-host=10.240.136.9
12
13 master-user=tongbu
14
15 master-password=123456
16
17 master-port=3306
18
19 master-connect-retry=30
20
21 binlog-do-db=umsdb
22
23 replicate-do-db=umsdb
24
25 replicate-ignore-table=umsdb.boco_tb_menu
26
27 replicate-ignore-table=umsdb.boco_tb_connect_log
28
29 replicate-ignore-table=umsdb.boco_tb_data_stat
30
31 replicate-ignore-table=umsdb.boco_tb_log_record
32
33 replicate-ignore-table=umsdb.boco_tb_workorder_record
1.3 Manually Execute Database Synchronization
Assuming ServA is the master server, restart MySQL on ServB:
service MySQLd restart
Log in to MySQL on ServB as root and execute:
MySQL> stop slave;
MySQL> load data from master;
MySQL> start slave;
Restart MySQL on ServA:
service MySQLd restart
1.4 Check Database Synchronization Status
Execute at the MySQL command prompt:
MySQL> show slave status/G
This will display the status of the synchronization process. As shown below, the two lines in blue font represent the slave process statuses; if both are yes, it indicates normal operation. The red font indicates synchronization errors; if there is an issue, an error message will appear here:
1 *************************** 1. row ***************************
2
3 Slave_IO_State: Waiting for master to send event
4
5 Master_Host: 10.21.2.90
6
7 Master_User: tongbu
8
9 Master_Port: 3306
10
11 Connect_Retry: 30
12
13 Master_Log_File: localhost-bin.000005
14
15 Read_Master_Log_Pos: 39753882
16
17 Relay_Log_File: localhost-relay-bin.000062
18
19 Relay_Log_Pos: 9826663
20
21 Relay_Master_Log_File: localhost-bin.000005
22
23 Slave_IO_Running: Yes
24
25 Slave_SQL_Running: Yes
26
27 Replicate_Do_DB: bak,umsdb
28
29 Replicate_Ignore_DB:
30
31 Replicate_Do_Table:
32
33 Replicate_Ignore_Table: umsdb.boco_tb_connect_log,umsdb.boco_tb_menu,umsdb.boco_tb_workorder_record,
umsdb.boco_tb_data_stat,umsdb.boco_tb_log_record
34
35 Replicate_Wild_Do_Table:
36
37 Replicate_Wild_Ignore_Table:
38
39 Last_Errno: 0
40
41 Last_Error:
42
43 Skip_Counter: 0
44
45 Exec_Master_Log_Pos: 39753882
46
47 Relay_Log_Space: 9826663
48
49 Until_Condition: None
50
51 Until_Log_File:
52
53 Until_Log_Pos: 0
54
55 Master_SSL_Allowed: No
56
57 Master_SSL_CA_File:
58
59 Master_SSL_CA_Path:
60
61 Master_SSL_Cert:
62
63 Master_SSL_Cipher:
64
65 Master_SSL_Key:
66
67 Seconds_Behind_Master:
3. Database Synchronization Testing
After configuration, test the database. First, test under normal network conditions. Perform database operations on ServA and on ServB; data should synchronize successfully to the other server in both directions.
Disconnect the network cable on ServB, perform some database operations on ServA, and then restore ServB’s network connection. However, the synchronized data does not appear on ServB. Check using the command show slave status/G and find that the Slave_IO_Running status is No. This status persists for a long time before data finally syncs to ServB. What could be causing this issue? The synchronization delay shouldn’t be this large. After searching for related information online, I found a parameter related to synchronization delay:
–slave-net-timeout=seconds
Parameter meaning: When the slave fails to read log data from the master, how long it waits before re-establishing a connection and fetching data.
So, I added this parameter to the configuration file, setting it to 60 seconds:
slave-net-timeout=60
After restarting the MySQL database and testing, the issue was resolved.
4. Resolving Database Synchronization Failures
When the data synchronization process fails, first manually check if the log file currently used by the slave’s backup exists on the master server. Run on the slave server:
MySQL> show slave status/G
Generally, you will get information like this:
1 *************************** 1. row ***************************
2
3 Slave_IO_State: Waiting for master to send event
4
5 Master_Host: 172.18.3.3
6
7 Master_User: tongbu
8
9 Master_Port: 3306
10
11 Connect_Retry: 30
12
13 Master_Log_File: MySQL-bin.000001
14
15 Read_Master_Log_Pos: 360
16
17 Relay_Log_File: localhost-relay-bin.000003
18
19 Relay_Log_Pos: 497
20
21 Relay_Master_Log_File: MySQL-bin.000001
22
23 Slave_IO_Running: Yes
24
25 Slave_SQL_Running: Yes
26
27 Replicate_Do_DB: bak
28
29 Replicate_Ignore_DB:
30
31 Replicate_Do_Table:
32
33 Replicate_Ignore_Table:
34
35 Replicate_Wild_Do_Table:
36
37 Replicate_Wild_Ignore_Table:
38
39 Last_Errno: 0
40
41 Last_Error:
42
43 Skip_Counter: 0
44
45 Exec_Master_Log_Pos: 360
46
47 Relay_Log_Space: 497
48
49 Until_Condition: None
50
51 Until_Log_File:
52
53 Until_Log_Pos: 0
54
55 Master_SSL_Allowed: No
56
57 Master_SSL_CA_File:
58
59 Master_SSL_CA_Path:
60
61 Master_SSL_Cert:
62
63 Master_SSL_Cipher:
64
65 Master_SSL_Key:
66
67 Seconds_Behind_Master: 0Here, Master_Log_File describes the log file on the master server.
Check the current list of database logs on the master:
MySQL> show master logs;
The resulting log list is as follows:
+———————-+———–+
| Log_name | File_size |
+———————-+———–+
| localhost-bin.000001 | 495 |
| localhost-bin.000002 | 3394 |
+———————-+———–+
If the file corresponding to the Master_Log_File used by the slave exists in the master’s log list, you can start the slave server thread on the slave to synchronize automatically:
MySQL> start slave;
If the log file on the master server no longer exists, you must first restore all data from the master server, then start the synchronization mechanism.
Run on the slave server:
MySQL> stop slave;
Run on the master server:
MySQL> stop slave;
Run on the slave server:
MySQL> load data from master;
MySQL> reset master;
MySQL> start slave;
Run on the master server:
MySQL> reset slave;
MySQL>start slave;
Note: LOAD DATA FROM MASTER is currently only effective on databases where all tables use the MyISAM storage engine.
Fault Symptom:
Log in to the slave server and run:
mysql> show slave status/G
…….
Relay_Log_File: localhost-relay-bin.000535
Relay_Log_Pos: 21795072
Relay_Master_Log_File: localhost-bin.000094
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
……
Solution 1:
Slave_SQL_Running: No
1. The application may have performed write operations on the slave.
2. It may also be caused by transaction rollback after the slave machine restarted.
It is usually caused by transaction rollback:
Solution:
mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;
Solution 2:
First, stop the Slave service: slave stop
Go to the master server and check the master status:
Record the values for File and Position
Enter the master:
mysql> show master status;
+———————-+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+———————-+———-+————–+——————+
| localhost-bin.000094 | 33622483 | | |
+———————-+———-+————–+——————+
1 row in set (0.00 sec)
Then go to the slave server and execute manual synchronization:
mysql> change master to
> master_host='master_ip',
> master_user='user',
> master_password='pwd',
> master_port=3306,
> master_log_file=localhost-bin.000094',
> master_log_pos=33622483 ;
1 row in set (0.00 sec)
mysql> slave start;
1 row in set (0.00 sec)
mysql> show slave status/G
*************************** 1. row ***************************
……..
Master_Log_File: localhost-bin.000094
Read_Master_Log_Pos: 33768775
Relay_Log_File: localhost-relay-bin.000537
Relay_Log_Pos: 1094034
Relay_Master_Log_File: localhost-bin.000094
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Manual synchronization requires stopping write operations on the master!
"Slave_IO_Running: No" MYSQL Not Syncing
"Slave_IO_Running: No" MYSQL Not Syncing
Today, I found that MySQL was not syncing. I checked the sync status with show slave status and got the following:
Slave_IO_Running: No
Slave_SQL_Running: Yes
Using start slave didn’t work either. Later, I ran stop slave first, then reset slave, and then start slave, and it returned to normal.