MySQL database synchronization in mainstream operating systems _ MySQL

Synchronization of MySQL databases in mainstream operating systems when the data volume of the database reads and writes frequently, we should consider separating the database read and write to improve the efficiency of database use, (Of course, there are other uses, such as backing up data). In this case, we need to use a MySQL function to synchronize databases. The implementation method is described as follows:

I. system environment

Master Database (master ):

System: Windows

Database: MySQL 5.0.51

IP: 192.168.1.156

Slave Database (slave ):

System: Linux

Database: MySQL 5.0.67

IP: 192.168.1.154

II. configuration parameters:

I found that the configurations of the two databases must be consistent and the two configuration files must be copied. It seems reasonable because some functions may be inconsistent and may cause synchronization errors. However, the two MySQL systems are different, so the default installation configuration is used.

Database preparation:

The database to be synchronized is called an account.

The account and data on the master server must be completely exported to the slave server.

Open an account on the master server to allow access from the server.

Master configuration:

Modify MySQL configuration file my. cnf,

Find the server-id line and change it as follows:

Server-id = 1

Add two lines of necessary information:

Log-bin = d:/log-bin.log # binary change location of the daily value

Binlog-do-db = account # database to be synchronized

Explanation: MySQL Replication on Slave has two threads: I/O thread and SQL thread, i/O is used to retrieve its binlog from the master Port 3306 (after the master has modified anything, write the modified information to your binlog for slave update) and write it to the local relay-log, while the SQL thread reads the local relay-log, the conversion cost is what MySQL can understand, so synchronization is completed step by step.

Modification to the master database is only available in this step.

Slave Database (slave) configuration:

Modify my. cnf in MySQL

Find the server-id line and change it as follows:

Server-id = 2 // slave database
　

Master-host = 192.168.1.156

Master-user = lzy

Master-password = lzy

Master-port = 3306

Replicate-do-db = account // database to be synchronized

Finally, restart both MySQL instances.

1. check whether there is any log generation under the configured log path.

2. add a piece of data on 156 to check whether the data has been added from the database. If not, check the slave database error log and debug it according to the error information.

After synchronization is configured, I find that the log location of log-bin is not good. I want to change the location to another disk. After MySQL is restarted, synchronization becomes unavailable. Super depressing. So I checked the MySQL error log,

"Count not find first log file name in binary log in ..."

I couldn't find the cause for a long time. I suddenly found a master.info file in the same directory of the error log. it should be a record of the data retrieved from the master server. Yes.

14

Log-bin.000003

337

192.168.1.156

Lzy

Lzy

3306

60

0

I deleted the file and restarted MySQL. OK. data is synchronized.