The actual synchronization procedure of the MySQL database in the mainstream Operating System

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 read

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 read

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.