MySQL Master-slave replication (under Windows)

Simple MySQL master-slave server (two-way replication), for learning purposes only.

1. Download Mysql-advanced-5.6.16-winx64.zip

After the download is done, rename it to MySQL, put it into two servers (see the 2nd cluster description) under the D packing directory, namely: D:\mysql\bin

2. master-Slave Server description

Server a:192.168.121.132 (Master/slave)

Server b:192.168.121.169 (Master/slave)

3. Create a configuration file
A server:

[Mysqld]

Log_bin

Basedir = D:/mysql
DataDir = D:/mysql/data
Port = 3306
server_id = 1
Character-set-server = UTF8

Sql_mode=no_engine_substitution,strict_trans_tables

[Mysqld_safe]

Log-error=d:/mysql/log/mysqld.log
Pid-file=d:/mysql/run/mysqld/mysqld.pid

B Server:

[Mysqld]

Log_bin

Basedir = D:/mysql
DataDir = D:/mysql/data
Port = 3306
server_id = 2
Character-set-server = UTF8

Sql_mode=no_engine_substitution,strict_trans_tables

[Mysqld_safe]

Log-error=d:/mysql/log/mysqld.log
Pid-file=d:/mysql/run/mysqld/mysqld.pid

4. Install MySQL (a server and B server)

Start the cmd command line on the a server and enter the directory D:\mysql\bin
Run command: Mysqld-install MySQL (install)
Run command: net start MySQL (start service)

b Server is consistent with a server operation.

5. Add a user for the master-slave synchronization for two servers separately:

A server:

Execute command: D:\mysql\binmysql-h localhost-u root-p (password is empty)
Execute command: Create user[email protected]'% ' identified by ' root ';
Execute command: Grant replication slave on * * to[email protected]'% ' identified by ' root ';
Execute command: Grant all privileges on * * to[email protected]'% ' identified by ' root ';
Execution command: Flush privileges;

B Server:

Start the cmd command to enter the D:\mysql\bin directory
Execute command: D:\mysql\binmysql-h localhost-u root-p (password is empty)
Execute command: Create user[email protected]'% ' identified by ' root ';
Execute command: Grant replication slave on * * to[email protected]'% ' identified by ' root ';
Execute command: Grant all privileges on * * to[email protected]'% ' identified by ' root ';
Execution command: Flush privileges;

6. Start Master-Slave synchronization

A server:

Start the cmd command to enter the D:\mysql\bin directory
Execute command: D:\mysql\bin\mysql-h 192.168.121.132-u pig-p Root
Execute command: Change master to master_host= ' 192.168.121.169 ',
Master_user= ' Pig ',
master_password= ' root ';
Execution command: start slave;

So far, the model of the master-->a has been built successfully.

Execute command: Show slave status \g (view slave case)

B Server:

Start the cmd command to enter the D:\mysql\bin directory
Execute command: D:\mysql\bin\mysql-h 192.168.121.132-u pig-p Root
Execute command: Change master to master_host= ' 192.168.121.132 ',
Master_user= ' Pig ',
master_password= ' root ';
Execution command: start slave;

At this end a master-->b from the model has been built successfully.

Execute command: Show slave status \g (view slave case)
Execute command: Show Master status \g (view host case)

7. Testing

A on the server to create the database, you can go to B to see if the synchronization was successful.
b server to create the database, you can go to a to see if the synchronization is successful.

Description: A real-time view of the Slave condition (show slave status \g) is required, and once an exception occurs, the one-way replication service is interrupted and will not be skipped.

8. Error description

Error One: master-slave server_id consistent
Execute command: Show variables like ' server_id '; View server_id
Execute command: Set global server_id=100; Modify server_id

Error two: master-slave UUIDs consistent, this is due to two of database replication.
Modify file: D:\mysql\data\auto.cnf The hexadecimal value of a random change to become, to ensure that the master-slave inconsistency on the line.

MySQL Master-slave replication (under Windows)