MySQL master-slave synchronization under Windows

MySQL master-slave synchronization:

1. Why the master-slave synchronization?

in Web Application system, database performance is one of the main causes of system performance bottleneck. Especially in large-scale systems, database clustering has become one of the necessary configurations. The main benefits of clustering are: query load, database copy backup, and so on. where Master is responsible for the load of the write operation, which means that everything written is done on master, while the read operation is distributed on slave. This can greatly improve the efficiency of reading. writing involves locking, whether it is a row or a table or a block lock, is a comparison to reduce the efficiency of the system to do things. Our separation is to focus the write operation on one node, while the read operation of its other n nodes, from another aspect effectively improve the efficiency of reading, ensure the high availability of the system .

2.mysql Master-Slave synchronization

The two MySQL database versions that I want to synchronize are mysql5.6, and both computers are in the same network segment.

① Modifying the primary database My.ini configuration:

Server-id = 1 This is the database ID, this ID is unique, the ID value cannot be duplicated, otherwise synchronization error occurs;

Log-bin = mysql-bin binary log file, this entry is required, otherwise the data can not be synchronized, if not named, then the name of the computer will be numbered to name the binary file;

Binlog-do-db = testcreate need to synchronize the database, if also need to synchronize additional database, then continue to add, if not write, then the default synchronization of all databases;

binlog-ignore-db = MySQL does not need to synchronize the database;

Restart the MySQL service after the modification is complete.

Note: Under Linux, only need to modify the/ETC/MY.CNF, but under Windows, I changed the installation directory under the My.ini, found that the configuration does not take effect, and then I saw the service of the MySQL service properties, found that the sentence:"\ C Program Files\mysql\mysql server 5.6\bin\mysqld.exe "--defaults-file=" C:\ProgramData\MySQL\MySQL server 5.6\my.ini " /c3> MySQL56, the original MySQL service read the configuration file here, later modified the path under the My.ini, restart the service, the configuration will take effect.

② Add the primary database for the synced account:

to authorize a user who can replicate to the primary database, execute the following command:

Grant Replication Slave on * * to ' slave ' @ '% ' identified by ' 123 ';

③ Displays synchronization information for the primary database:

It can be seen that the binary log file information has been generated, MySQL synchronization is through the binary log file synchronization, the main database to the operation of the database instructions are logged to the log file, from the database by reading the file, to the data from the database to modify, So as to achieve the effect of master-slave synchronization.

④ configuration from the My.ini of the database

From the database, you only need to configure SERVER-ID,BINLOG-DO-DB,BINLOG-IGNORE-DB.

⑤ Setting a link from a database to the primary database

Execute the slave stop command under MySQL to stop the slave service;

Mysql> Change Master to

Master_host= ' 192.168.1.189 ',

Master_user= ' slave ',

-master_password= ' 123 ',

Master_log_file= ' mysql-bin000014.000001 ',

master_log_pos=107;

Note : The value of Master_log_file,master_log_pos here is the same as the value of master. Otherwise, you will not be able to synchronize.

Execute the slave start command to start the service.

Note : You may not be able to link to the primary database here, you need to see whether bind 127.0.0.1 is not commented on My.ini in the primary database, and if not, you can only log on natively and not use remote login mode.

⑥ Verify synchronization:

Execute show slave status\g;

The following results indicate that the synchronization was successful.

You can also do crud operations in the table where the primary database is synchronized to see if there is any change in the data from the database.

MySQL master-slave synchronization under Windows