Teach you how to sync between two MySQL databases

Do development time to do MySQL database synchronization, two installed the same system, are FreeBSD5.4, installed Apache 2.0.55 and PHP 4.4.0,mysql version is 4.1.15, are the latest version.

1. Installation Configuration

Two servers, respectively installed MySQL, are installed in the/usr/local/mysql directory (installation steps omitted, please refer to the relevant documents), The IP of two servers is 192.168.0.1 and 192.168.0.2 respectively, we take 192.168.0.1 as master database, 192.168.0.2 as slave server, we adopt one-way synchronous way, that is master data is master's data, then slave main Move to master where to synchronize the data back.

As with the configuration of the two servers, we copy the key configuration files, the default profile is in the/usr/local/mysql/share/mysql directory, there are my-large.cnf, MY-MEDIUM.CNF, My-small.cnf and so on a few Wen Jia, we just test, use my-medium.cnf on the line. After the MySQL installation, the default configuration file is specified in the database storage directory, we use 4.1.X, so the configuration file should be in the/usr/local/mysql/var directory, so the configuration file copy the past:

Cp/usr/local/mysql/share/mysql/my-medium.cnf/usr/local/mysql/var/my.cnf

The two servers do the same copy configuration file operation.

2. Configure Master server

We're going to configure the 192.168.0.1 as the primary MySQL server (master), so we have to consider that we need to sync that database and use that user to sync, and we're going to use the root user to sync for simplicity, and just synchronize the database ABC.

To open a configuration file:

Vi/usr/local/mysql/var/my.cnf

Find information:

# required Unique ID between 1 and 2^32-1# defaults to 1 if master-host are not set# but would not function as a master if Omittedserver-id = 1//1 for master,2 Salve

Add two lines:

Sql-bin-update-same//sync form BINLOG-DO-DB = ABC//Database to sync

To restart the MySQL server for 192.168.0.1:

/usr/local/mysql/bin/mysqladmin Shutdown/usr/local/mysql/bin/mysqld_safe--user=mysql &

3. Configure the slave server

Our slave server is mainly active to the master server to synchronize data back, we edit the configuration file:

Vi/usr/local/mysql/var/my.cnf

Note : Please pay attention to the triple programming Tutorials section for more wonderful articles .