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 to read

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 to read

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.