MySQL Database Data Synchronization Method

Description

MySQL DATAThe Database supports master-slave database clusters, master-slave database backup, and master-master mutual backup. The following describes the master-master synchronous backup. The so-called master-master synchronization refers to the synchronization of data between two or more databases from time to time. One is backup, and the other is shared.UserParallel access volume.

Configuration

Assume there are two database servers: 192.168.0.1 and 192.168.0.2,

1 indicates 168.0.1,

2 indicates 168.0.2

First configure 1
Use root to access MySQL and add a user authorized to access MySQL 2

Code:

Mysql> grant all privileges on *. * To tongbu @ '192. 168.0.2 'identified by '20140901 ';

Exit MySQL and edit MySQL ConfigurationFile

Code:

VI/etc/My. CNF

Add or modify the following content:

Code:

[Client] Default-character-set = utf8 [Mysqld] Default-character-set = utf8 Log-bin = mysql-bin Server-id = 1 Master-host = 192.168.0.2 Master-user = tongbu Masters-Password = 123456 Master-Port = 3306 Master-connect-retry = 30 # reconnection interval of 30 seconds after network disconnection BINLOG-do-DB = MySQL # database to be backed up Replicate-do-DB = MySQL Skip-name-resolve # Quick logon skips user resolution Back_log = 500 Max_connect_errorrs = 100 Max_connections = 1000

Restart MySQL Service

Code:

Service mysqld restart

So far 1 is configured, and then 2 is configured, steps are the same as 1

Code:

Mysql> grant all privileges on *. * To tongbu @ '192. 168.0.1 'identified by '123 ';

Code:

VI/etc/My. CNF

Code:

[Client] Default-character-set = utf8 [Mysqld] Default-character-set = utf8 Server-id = 2 Master-host = 192.168.0.1 Master-user = tongbu Masters-Password = 123456 Master-Port = 3306 BINLOG-do-DB = MySQL Replicate-do-DB = MySQL Skip-name-resolve Back_log = 500 Max_connections = 1000 Max_connect_errorrs = 100 Slave_skip_error = 1062 Sync_binlog = 1 # synchronize each operation Log-bin = mysql-bin

Code:

Service mysqld restart

Now 2 is configured
Note: The configuration in 2 is somewhat different because I use 1 as the production database and 2 as the backup only.

Check the Master Status in MySQL 1.

Code:

Mysql> show Master status;

Shown as follows:

Code:

+ ------------------ + ---------- + -------------- + ------------------ + | File      | Position | binlog_do_db | binlog_ignore_db | + ------------------ + ---------- + -------------- + ------------------ + | Mysql-bin.000019 | 11809679 | MySQL    |        | + ------------------ + ---------- + -------------- + ------------------ + 1 row in SET (0.00 Sec)

Let's write down file = mysql-bin.000019 and position = 11809679
Then go to MySQL 2

Code:

Mysql> stop slave;

Code:

Mysql> change master to master_log_file = 'mysql-bin.000019 ', master_log_pos = 11809679

Code:

Mysql> slave start;

View synchronous backup status

Code:

Mysql> show slave statusg

The

Code:

Slave_io_running: Yes Slave_ SQL _running: Yes

Yes, it is in the normal synchronization status.