MySQL database two hosts synchronously combat of linux
- 2020-05-07 20:34:50
- OfStack
When a slave server connects to the master server, it notifies the master server where the slave server read the last successful update in the log. The slave server receives any updates that have occurred since then, then blocks and waits for the master server to notify the next update.
In actual project, two distributed in different MySQL database are installed on the host, two servers is given priority to, each customer requirement when one machine malfunction, the other one can take over application on the server, this would require the two, to maintain its 1 to real-time database of data used here MySQL synchronization function realizes the double machine synchronous replication.
Here is an example of the operation:
1. Set for database synchronization
Host operating system: RedHat Enterprise Linux 5
Database version: MySQL Ver 14.12 Distrib 5.0.22
Premise: MySQL database starts normally
Assume that the addresses of the two hosts are:
ServA: 10.240.136.9
ServB: 10.240.136.149
1.1 configure the sync account
Add one account that ServB can log into:
MySQL > GRANT all privileges ON *.* TO tongbu@'10.240.136.149' IDENTIFIED BY '123456';
Add one account on ServB that ServA can log in:
MySQL > GRANT all privileges ON *.* TO tongbu@'10.240.136.9' IDENTIFIED BY '123456';
1.2 configure the database parameter
1. Log in ServA as root user and modify my.cnf file of ServA
vi /etc/my.cnf
Add the following configuration to the configuration entry 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 in ServB as root user and modify 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 performs database synchronization manually
Suppose ServA is the main server and MySQL is restarted on ServB:
service MySQLd restart
Log in MySQL with root user on ServB, execute:
MySQL > stop slave;
MySQL > load data from master;
MySQL > start slave;
Restart MySQL on ServA:
service MySQLd restart
1.4 view the database synchronization status
Execute at the MySQL command prompt:
MySQL > show slave status\G
The status of the synchronous process will be displayed, as shown below. The two lines of blue font are slave process status, if both are yes, it is normal. The red font indicates synchronization error. If there is a problem, there will be an error:
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 configuring the database, test it first under normal network conditions. Database operation on ServA and database operation on ServB can synchronize the data.
Unplug the network cable on ServB host, then do some database operations on ServA, and then restore ServB network environment, but the synchronous data is not seen on ServB, Slave_IO_Running state is No through the command show slave status\G, Slave_IO_Running state is No, this state lasted for a long time, data can be synchronized to ServB up. What's the problem? The synchronization delay isn't that big. Later, I searched related information on the Internet and found 1 parameter related to synchronization delay:
--slave-net-timeout=seconds
Parameter meaning: when slave fails to read log data from the master database, how long to wait to reestablish the connection and get the data.
Add this parameter to the configuration file and set it to 60 seconds
slave-net-timeout=60
Test after restart MySQL database, this problem is resolved.
4, database synchronization failure resolution
When the data synchronization process fails, first manually check whether the database log files currently backed up by slave host exist on master host and run on slave host:
MySQL > show slave status\G
1. Get the following information:
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
Where Master_Log_File describes the log file on the master host. < / pre >
Check the current database list on 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 corresponding file of Master_Log_File used on slave host exists in the log list of master, the slave server thread can be automatically synchronized after starting on slave host:
MySQL > start slave;
If the log files on the master host no longer exist, you will need to restore all the data from the master host before turning on the synchronization mechanism.
Running on slave host:
MySQL > stop slave;
Run on master host:
MySQL > stop slave;
Running on slave host:
MySQL > load data from master;
MySQL > reset master;
MySQL > start slave;
Run on master host:
MySQL > reset slave;
MySQL > start slave;
Note: LOAD DATA FROM MASTER is currently only available on all databases whose tables use MyISAM storage engine.