We all know that when the related data volume of the MySQL database is frequently read/written, we mainly consider separating the actual read/write of the MySQL database. This method is used to improve the efficiency of database usage, (Of course, there are other uses, such as backing up data). In this case, we need to use a MySQL function to synchronize databases.
When the data volume of the database is frequently read/written, we should consider separating the read/write of the MySQL database to improve the efficiency of database use (of course, there are other uses such as backing up data ), at this time, we need to use a MySQL function, database synchronization. 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
MySQL 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 MySQL database (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
From MySQL 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 MySQL 156 to check whether the data has been added from the MySQL 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. The data is synchronized.