Teach you how to implement MySQL dual-host data synchronization (1)

Editor's note: many friends may not know where to start when getting started with MySQL's dual-machine synchronization requirements. In fact, this is one of the functions supported by MySQL itself. This article provides a preliminary idea about MySQL master-slave synchronization for your reference.

I. Requirements

Suppose there are currently two MySQL database servers. How can we achieve data synchronization between these two machines? That is, after the database is modified on one machine, the other machine will synchronously update the modified information.

2. Solutions

I found that MySQL supports one-way, asynchronous replication. During the replication process, one server acts as the master server, and the other or multiple other servers act as the slave server.

The principle is as follows:

The master server writes updates to binary log files and maintains an index of the files to track log loops. These logs can record updates sent to the slave server. When a slave server connects 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 accepts any updates that have occurred since then, blocks them, and waits for the master server to notify the new updates.

2.1 test environment

 
 

  
  
Master : 192.168.7.67 (CentOS 5.5  x86_64 )   MySQL Version  :  5.0.77  
  
  
Slave: 192.168.56.103 (CentOS 5.3 i386)  MySQL  Version : 5.0.45 
 
 

Note:

The MySQL version of Master and slave must be the same, or the version of Master is higher than that of Slave.

2.2 configuration process

2.2.1 Master side settings

Start MySQL and create a test database abc:

 
 

  
  
Root @ camlit ~ :/Etc/init. d/mysqld start
  
  
Jian. ma @ camlit ~ : Mysql-u root-p
  
  
Enter password: xxxx
  
  
Welcome to the MySQL monitor. Commands end with; or \ g.
  
  
Your MySQL connection id is 3
  
  
Server version: 5.0.77 Source distribution
  
  
 
  
  
Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.
  
  
 
  
  
Mysql> create database abc;
  
  
Query OK, 1 row affected (0.31 sec)
  
  
 
  
  
### Create a user for synchronization, specifying that the user can only log on at 192.168.56.103
  
  
### Replication slave: Enable replication slaves to read binary log events from the master
  
  
 
  
  
Mysql> grant replication slave on *. * to 'test1' @ '192. 168.56.103 'identified by 'test1 ';
  
  
Query OK, 0 rows affected (0.16 sec)
 
 

Modify the configuration file:

 
 

  
  
root@camlit ~: vi /etc/my.cnf 
 
 

Note: Back up the configuration file before modifying it.

 
 

  
  
[Mysqld]
  
  
Datadir =/var/lib/mysql
  
  
Socket =/var/lib/mysql. sock
  
  
User = mysql
  
  
Old_passwords = 1
  
  
 
  
  
# Add the following content
  
  
Server_id = 1 ### 1 indicates master, 2 indicates slave binlog-do-db = abc ### database to be synchronized, if there are multiple databases, binlog-ignore-db = mysql ### Database log-bin = mysql-bin that does not need to be synchronized per database line
  
  

  
  
[Mysqld_safe]
  
  
Log-error =/var/log/mysqld. log
  
  
Pid-file =/var/run/mysqld. pid
 
 

Restart the service:

 
 

  
  
root@camlit ~: /etc/init.d/mysqld restart  
 
 

2.2.2 Slave client settings

Create the same database as the master: abc

 
 

  
  
Enter password:   
  
  
Welcome to the MySQL monitor.  Commands end with ; or \g.   
  
  
Your MySQL connection id is 5   
  
  
Server version: 5.0.45-log Source distribution   
  
  
   
  
  
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.   
  
  
   
  
  
mysql> create database abc;   
  
  
Query OK, 1 row affected (0.31 sec) 
 
 

Modify the configuration file:

 
 

  
  
root@test2 ~: vi /etc/my.cnf 
 
 

 
 

  
  
[Mysqld]
  
  
Datadir =/var/lib/mysql
  
  
Socket =/var/lib/mysql. sock
  
  
User = mysql
  
  
Old_passwords = 1
  
  

  
  
### Add the following content
  
  
Server_id = 2 log-bin = mysql-bin master-host = 192.168.7.67 master-user = test1 master-password = test1 master-port = 3306 master-connect-retry = 10 ### connection count replicate-do-db = abc ### accepted database name replicate-ignore-db = mysql ### unacceptable Database
  
  

  
  
[Mysqld_safe]
  
  
Log-error =/var/log/mysqld. log
  
  
Pid-file =/var/run/mysqld. pid
 
 

Restart the service:

 
 

  
  
root@test2~: /etc/init.d/mysqld restart  
 
 

Note:

After the configuration is successful, the master.info file is generated in the mysql directory (/var/lib/mysql/). To change the slave settings, you must delete the file before it takes effect.

Enter mysql and enter the following command:

 
 

  
  
Root @ test2 ~ : Mysql-u root-p
  
  
Enter password:
  
  
Welcome to the MySQL monitor. Commands end with; or \ g.
  
  
Your MySQL connection id is 4
  
  
Server version: 5.0.45-log Source distribution
  
  

  
  
Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.
  
  

  
  
Mysql> slave start;
  
  
Query OK, 0 rows affected, 1 warning (0.00 sec)
  
  
### View synchronization conditions
  
  
Mysql> show slave status; or show master status;
 
 

2.3 Result Test

Perform operations on Database abc on the Master side as follows:

 
 

  
  
jian.ma@camlit ~: mysql  -u root -p   
  
  
Enter password:   
  
  
Welcome to the MySQL monitor.  Commands end with ; or \g.   
  
  
Your MySQL connection id is 3   
  
  
Server version: 5.0.77-log Source distribution   
  
  
   
  
  
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.   
  
  
   
  
  
mysql> use abc;   
  
  
Database changed   
  
  
mysql> create table test1 (IP VARCHAR(20),USER VARCHAR(100), MAIL   
  
  
VARCHAR(100));   
  
  
Query OK, 0 rows affected (1.20 sec)   
  
  
mysql> insert into test1(IP,USER,MAIL) values('192.168.7.66', 'test', 'test@test.com.cn');   
  
  
Query OK, 1 row affected (0.06 sec)  
 
 

Check whether the Server Load balancer instance can be updated:

 
 

  
  
root@test2 ~: mysql -u root -p   
  
  
Enter password:   
  
  
Welcome to the MySQL monitor.  Commands end with ; or \g.   
  
  
Your MySQL connection id is 6   
  
  
Server version: 5.0.45-log Source distribution   
  
  
   
  
  
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.   
  
  
   
  
  
mysql> show databases;   
  
  
+--------------------+   
  
  
| Database   |   
  
  
+--------------------+   
  
  
| information_schema |   
  
  
| foo|   
  
  
| mysql  |   
  
  
| test   |   
  
  
|abc  |   
  
  
+--------------------+   
  
  
5rows in set (0.00 sec)   
  
  
mysql> use abc;   
  
  
Reading table information for completion of table and column names   
  
  
You can turn off this feature to get a quicker startup with -A   
  
  
Database changed   
  
  
mysql> show tables;   
  
  
+---------------+   
  
  
| Tables_in_abc |   
  
  
+---------------+   
  
  
| test1 |   
  
  
+---------------+   
  
  
1 row in set (0.03 sec)   
  
  
 mysql> select * from test1;   
  
  
+--------------+------+------------------+   
  
  
| IP   | USER | MAIL |   
  
  
+--------------+------+------------------+   
  
  
| 192.168.7.66 | test | test@test.com.cn |   
  
  
+--------------+------+------------------+   
  
  
1 row in set (0.00 sec)  
 
 

From the above results, we can see that the data on the Master side can be synchronized to the Slave side. It indicates that the synchronization problem of the master-slave database has been successfully solved.