MySQL database Two host synchronous combat (Linux) _mysql

When a server connects to the primary server, it notifies the primary server of the location of the last successful update that was read from the server in the log. Receive any updates from the server that have occurred since then, and then block and wait for the master server to notify the next update.

In the actual project, the two distributed in the remote host installed MySQL database, two servers are based on each other, customer requirements when one of the machine failure, the other can take over the application of the server, which requires two database data to be consistent in real time, Use MySQL's sync function here to achieve synchronous replication of two machines.

The following are examples of actions:

1. Database synchronization Settings

Host operating system: RedHat Enterprise Linux 5

Database version: MySQL Ver 14.12 Distrib 5.0.22

Prerequisite: MySQL database start normally

Suppose the two host addresses are:

serva:10.240.136.9

servb:10.240.136.149

1.1 Configure Sync Account

Add an account on the Serva that servb can log on to:

Mysql>grant all privileges in *.* to tongbu@ ' 10.240.136.149 ' identified by ' 123456 ';

Add an account on the servb that Serva can log on to:

Mysql>grant all privileges in *.* to tongbu@ ' 10.240.136.9 ' identified by ' 123456 ';

1.2 Configuration database Parameters

1, the root user login Serva, modify Serva my.cnf files

Vi/etc/my.cnf

Add the following configuration to the configuration items in [MySQLd]:

1 Default-character-set=utf8

3 Log-bin=mysql-bin

5 Relay-log=relay-bin

7 Relay-log-index=relay-bin-index

9 server-id=1

One master-host=10.240.136.149

Master-user=tongbu

master-password=123456

master-port=3306

Master-connect-retry=30

Binlog-do-db=umsdb

Replicate-do-db=umsdb

Replicate-ignore-table=umsdb.boco_tb_menu

Replicate-ignore-table=umsdb.boco_tb_connect_log

Replicate-ignore-table=umsdb.boco_tb_data_stat

Replicate-ignore-table=umsdb.boco_tb_log_record

Replicate-ignore-table=umsdb.boco_tb_workorder_record

2, the root user login servb, modify servb my.cnf files

Vi/etc/my.cnf

Add the following configuration to the configuration items in [MySQLd]:

1 Default-character-set=utf8

3 Log-bin=mysql-bin

5 Relay-log=relay-bin

7 Relay-log-index=relay-bin-index

9 server-id=2

One master-host=10.240.136.9

Master-user=tongbu

master-password=123456

master-port=3306

Master-connect-retry=30

Binlog-do-db=umsdb

Replicate-do-db=umsdb

Replicate-ignore-table=umsdb.boco_tb_menu

Replicate-ignore-table=umsdb.boco_tb_connect_log

Replicate-ignore-table=umsdb.boco_tb_data_stat

Replicate-ignore-table=umsdb.boco_tb_log_record

Replicate-ignore-table=umsdb.boco_tb_workorder_record

1.3 Performing database synchronization manually

Assume the Serva as the primary server and restart MySQL on the servb:

Service MySQLd Restart

Log on to MySQL with root user on servb, execute:

mysql> stop Slave; mysql> load data from master; mysql> start slave;

To restart MySQL on the Serva:

Service MySQLd Restart

1.4 View Database Sync status

Execute at the MySQL command prompt:

Mysql> Show Slave Status\g

The status of the synchronization process is displayed, as shown below, the two lines of blue font are slave process state, and if yes, the red font indicates a sync error indication, and 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
One connect_retry:30
12
master_log_file:localhost-bin.000005
14
read_master_log_pos:39753882
16
relay_log_file:localhost-relay-bin.000062
18
relay_log_pos:9826663
20
relay_master_log_file:localhost-bin.000005
22
Slave_io_running:yes
24
Slave_sql_running:yes
26
Replicate_do_db:bak,umsdb
28
replicate_ignore_db:
30
Replicate_do_table:
32
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
Replicate_wild_do_table:
36
Panax replicate_wild_ignore_table:
38
last_errno:0
40
Last_error:
42
skip_counter:0
44
exec_master_log_pos:39753882
46
relay_log_space:9826663
48
Until_condition:none
50
Wuyi Until_log_file:
52
until_log_pos:0
54
Master_ssl_allowed:no
56
Master_ssl_ca_file:
58
Master_ssl_ca_path:
60
Master_ssl_cert:
62
Master_ssl_cipher:
64
Master_ssl_key:
66
Seconds_behind_master:

3. Database Synchronization Test

After configuring the database to test, first in the network under normal conditions, the database operation on the Serva, and on the SERVB database operation, the data can be synchronized past.

Unplug the network cable on the SERVB host, and then do some database operations on the Serva, then restore the SERVB environment, but the servb can not see the synchronized data, through the command show slave status\g See the discovery Slave_io_ Running state is no, this state lasts a long time, the data can synchronize to servb up. What's the problem? The synchronization delay is not that big. Later, through the Internet to find relevant data, to find a synchronization delay-related parameters:

--slave-net-timeout=seconds

Parameter meaning: When slave reads log data from the primary database fails, wait for how long to re-establish the connection and fetch the data.

The parameter is then added to the configuration file, set to 60 seconds

Slave-net-timeout=60

After restarting the MySQL database test, the problem is resolved.

4, database synchronization failure to solve

When the data synchronization process expires, first manually check that the database log files currently backed up by the slave host are present on the master host and run on the slave host:

Mysql> Show Slave Status\g

Generally obtain 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
One connect_retry:30
12
master_log_file:mysql-bin.000001
14
read_master_log_pos:360
16
relay_log_file:localhost-relay-bin.000003
18
relay_log_pos:497
20
relay_master_log_file:mysql-bin.000001
22
Slave_io_running:yes
24
Slave_sql_running:yes
26
Replicate_do_db:bak
28
replicate_ignore_db:
30
Replicate_do_table:
32
Replicate_ignore_table:
34
Replicate_wild_do_table:
36
Panax replicate_wild_ignore_table:
38
last_errno:0
40
Last_error:
42
skip_counter:0
44
exec_master_log_pos:360
46
relay_log_space:497
48
Until_condition:none
50
Wuyi Until_log_file:
52
until_log_pos:0
54
Master_ssl_allowed:no
56
Master_ssl_ca_file:
58
Master_ssl_ca_path:
60
Master_ssl_cert:
62
Master_ssl_cipher:
64
Master_ssl_key:
66
seconds_behind_master:0
Where Master_log_file describes the log files on the master host.

Check the current list of databases on 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 master_log_file corresponding file used on the slave host exists in the log list for master, it can be synchronized automatically after the slave server thread is opened on the slave host:

mysql> start slave;

If the log files on the master host do not already exist, you need to first recover all the data from the master host and then turn on the synchronization mechanism.

Running on the slave host:

mysql> stop Slave;

To run on the master host:

mysql> stop Slave;

Running on the slave host:

mysql> load data from master; mysql> Reset Master; mysql> start slave;

To run on the master host:

mysql> Reset Slave; Mysql>start slave;

Note: The LOAD DATA from master is currently valid only on databases where all tables use the MyISAM storage engine.