MySQL database synchronization between two hosts (linux)

When an slave server is connected to the master server, it notifies the master server of the last successful update location read from the server in the log. The slave server receives any updates from that time, blocks them, and waits for the master server to notify you of the next update.

In the actual project, two hosts distributed in different regions are installed with MySQL databases, and the two servers are both active and standby. The customer requires that when one of the hosts fails, another application that can take over the server requires the data of the two databases to be consistent in real time. Here, the MySQL synchronization function is used to Achieve Synchronous replication between two machines.

The following are examples:

1. Database Synchronization settings

Host operating system: RedHat Enterprise Linux 5

Database Version: MySQL Ver 14.12 Distrib 5.0.22

Prerequisites: the MySQL database is started normally.

Assume that the addresses of the two hosts are:

ServA: 10.240.136.9

ServB: 10.240.136.149

1.1 configure synchronization account

Add an account on ServA that can be logged on to ServB:

MySQL>GRANT all privileges ON *.* TO tongbu@'10.240.136.149' IDENTIFIED BY '123456';

Add an account that can be logged on to ServB:

MySQL>GRANT all privileges ON *.* TO tongbu@'10.240.136.9' IDENTIFIED BY '123456';

1.2 configure database Parameters

1. log on to ServA as the root user and modify the my. cnf file of ServA.

vi /etc/my.cnf

Add the following configuration to the configuration item of [MySQLd:

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=10.240.136.149
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

2. log on to ServB as the root user and modify the my. cnf file of ServB.

vi /etc/my.cnf

Add the following configuration to the configuration item of [MySQLd:

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 manual Database Synchronization

Assume that the master server is ServA and MySQL is restarted on ServB:

service MySQLd restart

Log on to MySQL with the root user on ServB and execute:

MySQL> stop slave; MySQL> load data from master; MySQL> start slave;

Restart MySQL on ServA:

service MySQLd restart

1.4 view Database Synchronization status

Run the following command at the MySQL Command Prompt:

MySQL> show slave status\G

The status of the synchronization process is displayed, as shown in the following figure. The blue font of the two lines is the status of the slave process. If both are yes, the synchronization process is normal. The red font indicates a synchronization error. If any problem occurs, an error message is displayed:

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 Test

After the database is configured, perform the test. First, perform the test under normal network conditions, perform database operations on ServA, and perform database operations on ServB. All data can be synchronized.

Unplug the network cable from the Server Load balancer instance, perform database operations on the server Load balancer instance, and then restore the network environment of the Server Load balancer instance. However, the synchronized data is invisible to the Server Load balancer instance, run the show slave status \ G command to check whether the status of Slave_IO_Running is No. This status lasts for a long time before data can be synchronized to ServB. What's the problem? The synchronization latency is not that big. Later, I searched for relevant information on the Internet and found a synchronization latency parameter:

--slave-net-timeout=seconds

Parameter description: When slave fails to read log data from the primary database, how long will it take to re-establish a connection and obtain the data.

Add this parameter to the configuration file and set it to 60 seconds.

slave-net-timeout=60

Restart the MySQL database and test the problem.

4. Database Synchronization failure Solution

When the data synchronization process fails, first manually check whether the database log files currently backed up by the slave host exist on the master host and run the following on the slave HOST:

MySQL> show slave status\G

Generally, the following information is obtained:

1 **************************** 1. row ***************************
2
3 Slave_IO_State: Waiting for master to send event
4
5 Master_Host: 10.21.3.240
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: 0
Master_Log_File describes the log files on the master host.

Check the current database list on the master:

MySQL> show master logs;

The log list is as follows:

+ ---------------------- + ----------- +

| Log_name | File_size |

+ ---------------------- + ----------- +

| Localhost-bin.000001 | 495 |

| Localhost-bin.000002 | 3394 |

+ ---------------------- + ----------- +

If the file corresponding to Master_Log_File used on the slave host exists in the log list of the master, it can be automatically synchronized after the slave server thread is enabled on the slave Host:

MySQL> start slave;

If the log files on the master host do not exist, you must first recover all data from the master host and then enable the synchronization mechanism.

Run the following command on the slave Host:

MySQL> stop slave;

Run on the master Host:

MySQL> stop slave;

Run the following command on the slave Host:

MySQL> load data from master; MySQL> reset master; MySQL> start slave;

Run on the master Host:

MySQL> reset slave; MySQL>start slave;

Note: load data from master is only valid for all tables that use the MyISAM storage engine.