MySQL master-slave synchronization under Windows

Halfway decent to Java, just started to hear that the program supports read and write separation feeling particularly tall, also has not been contacted

When the chance comes into contact, it must be written down.

Let's talk about the master-slave synchronization of the database today, two benefits:

One is that the separation of read and write can be used. For example, write operations to the main database, read from the database read from the

The second is purely to the database backup, in case the hard disk completely collapsed

Master-Slave database operation preparation:

Both computers are installed MySQL version 5.5 and above, the best two computers are installed in the same version of the database, and can be used in a separate normal

Remember the IP address of two computers:

Take my home computer as an example: Main database: 192.168.0.102 from database: 192.168.0.104

"Operations under the primary database"

Step One:

Locate the My.ini file in the main database MySQL installation directory, and under Mysqld, add the master server configuration

Server-id=1 #服务器 ID
Log-bin=c:\program files\mysql\mysql Server 5.5\mysql-bin #二进制文件存放路径
Binlog-do-db=test1 #待同步的数据库

After you save and restart the MySQL service, you can see Mysql-bin.index files and mysql-bin.000001 files under C:\Program files\mysql\mysql Server 5.5

Enter user name and password: mysql-u user name-p password (mysql-uroot-proot)

Step Two,

To set permissions from the server to be connected: Grant Replication Slave,reload,super on * * to [email protected] identified by ' Root ';

PS: To the host 192.168.0.104 add permissions, user name: Slave, Password: root; (just enter it once)

Step Three,

Enter the command show master status; # Find the value of file and Position record down;

"Operation from Database"

Step Four:

Under MySQL, locate the My.ini file, and under its [mysqld], add the configuration from the server side:

server-id=2 #服务器 ID and cannot be consistent with the primary server
Replicate-do-db=test1 #待同步的数据库

Save and restart the MySQL service

Step Five:

Test from the server: Mysql-u slave-p root-h 192.168.0.102 to see if the primary database can be connected successfully,

Then enter Exi to exit the MySQL command line or close the cmd window to open a new cmd

Step Six:

Modify the parameters of the connection to the primary database: Mysql>change master to master_host= ' 192.168.0.104 ', master_user= ' slave ', master_password= ' root ', Master_log_file= ' mysql-bin.000001 ', master_log_pos=107; (the mysql-bin.000001 and 107 entered here are replaced by the values queried in step three , otherwise they will not be synchronized)

PS: May be reported an error, probably means that the slave thread is running, can not be set, so that the execution of Mysql>stop slave; Stop the slave thread, and then set the parameters for the connection;

Step Seven:

After Setup, execute mysql> start slave; Open slave thread; Execute mysql> show slave status\g (no semicolon), view

Slave_IO_Running:YesSlave_SQL_Running:Yes This is the detailed steps, if not successful please contact me to help you solve, follow this step as long as two computer network interoperability, should not have a problem

However, it is important to note that:

If the modification from the database is not synchronized to the primary database modification, so that the master-master synchronization should be used, similar methods,

So the insert,delete update should be an operation on the primary database, while the select operation will operate from the table

Specific program how to achieve read and write separation, follow-up I will be presented in a specific demo form

MySQL master-slave synchronization under Windows