How to synchronize two hosts in MySQL

MySQL supports unidirectional and asynchronous replication. One server acts as the master server during the replication process, and one or more other servers act as slave servers. The master server writes updates to the binary log file and maintains an index of the log file to track log loops.

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 '123 ';

Add an account that can be logged on to ServB:

MySQL> GRANT all privileges ON *. * TO tongbu @ '10. 240.136.9 'identified by '123 ';

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 check command to find that 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;

Log list

++-+

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.