Detailed Windows Mysql under the master-slave synchronization _mysql

Halfway decent to Java, just started to hear that the program to support read and write separation feel particularly tall, also has been no contact

Accidental opportunity to contact, you must write down.

Today, let's talk about database master-slave synchronization, two benefits:

• is read-write separation can be used. such as writing to the main database, read from the database read
• is to simply back up the database in case the hard drive collapses completely.

Master-Slave database operation preparation:

Two computers, are installed MySQL 5.5 and above version, preferably both computers are installed the same version of the database, and can be used separately

Remember the IP address of two computers:

Take my home computer for example: Primary database: 192.168.0.102 from database: 192.168.0.104

"Operation under Primary Database"

Step One:

In the main database MySQL installation directory to find the My.ini file open, add the master server configuration under Mysqld

server-id=1 #服务器 ID 
log-bin=c:\program files\mysql\mysql Server 5.5\mysql-bin #二进制文件存放路径
binlog-do-db=test1 # Databases to be synchronized

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 username and password: mysql-u user name-p password (mysql-uroot-proot)

Step Two,

Set permissions on the server to be connected: Grant replication Slave,reload,super on *.* to slave@192.168.0.104 identified by ' root ';

PS: Add permissions to host 192.168.0.104, username: Slave, password: root; (just type it once.)

Step Three,

Enter command show master status; # Find the values of file and Position and record them;

"Operation from Database"

Step Four:

Locate the My.ini file under MySQL and add the server-side configuration under its [mysqld]:

server-id=2 #服务器 ID, 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 you can connect to the master database successfully.

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

Step Six:

To modify the parameters of a 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; ( input mysql-bin.000001 and 107 here. Replace with the value from the query in step three otherwise it will not be synchronized ）

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

Step Seven:

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

Slave_io_running:yes
Slave_sql_running:yes

This is a detailed step, if not successful please contact me to help you solve, according to this step as long as two computer network interoperability, there should be no problem

However, it should be noted that:

If modifications are made to the database, the primary database is not synchronized, so the master-master synchronization should be used, and the method is similar.

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

The specific program how to achieve read-write separation, follow-up I will be specific demo form presented

The above is the entire content of this article, I hope the content of this article for everyone's study or work can bring some help, but also hope that a lot of support cloud Habitat community!