Build MySQL master-master Replication under Windows

1. First download the latest version of MySQL Server (http://dev.mysql.com/downloads/windows/installer/)

2. Install MySQL server on two machines

MYSQL 1:192.168.0.104 (hereinafter referred to as 104)

MYSQL 2:192.168.0.103 (hereinafter referred to as 103)

3. Configure MySQL server to start binary Logging.
Add the following configuration information in the 104 My.ini file (typically in C:\PROGRAMDATA\MYSQL\MYSQL Server 5.6\my.ini)

[Mysqld]
Log-bin=mysql-bin
Server-id=1

Add the following configuration information in the 103 My.ini file

[Mysqld]
Log-bin=mysql-bin
server-id=2

4. Restart MySQL

5. Create a user for replication and assign permissions

mysql> CREATE USER ' repl ' @ '% ' identified by ' repl ';

Mysql> GRANT REPLICATION SLAVE on * * to ' repl ' @ '% ';

6. Check the location of the current bin log for the two MySQL servers (as we only need to synchronize two servers from the current state of operation)

6.1 Execute the following commands on both machines:

FLUSH TABLES with READ LOCK;

SHOW MASTER STATUS;

103 will get the following result:

104 will get the following result:

The file in the figure represents the current binary log file name, position is the current location of the binary log files, and the replication should start from this location.

7. Release Lock

Execute the following command on both machines: Unlock Tables. Allow two MySQL databases to operate normally

8, set slave Replication.

8.1 Start the slave replication on 104.

8.1.1 Execution Stop Slave

8.1.2 executes the following command:

Change MASTER to master_host= ' 192.168.0.103 ', master_user= ' repl ', master_password= ' repl ', master_log_file= ' Mysql-bin.000001 ', master_log_pos=411;

8.1.3 Execute Start Slave

8.1.4 View Slave status, execute show Slave status\g;

8.2 Start the slave replication on 103.

8.2.1 Execution Stop Slave

8.2.2 executes the following command:

Change MASTER to master_host= ' 192.168.0.104 ', master_user= ' repl ', master_password= ' repl ', master_log_file= ' Mysql-bin.000001 ', master_log_pos=411;

8.2.3 Execute Start Slave

8.2.4 View Slave status, execute show Slave status\g;

9. Test replication Effect

9.1 Performed on 104:

mysql> CREATE DATABASE Repltest;

mysql> use repltest;

mysql> CREATE TABLE person (ID int,name varchar (255));

mysql> INSERT into person values (1, ' Jensen ');

9.2 Performed on 103:

mysql> use repltest;

Mysql> select * from person; Verify that the record inserted on 104 is already synchronized on 103

9.3 Inserting records on 103

mysql> INSERT into person values (2, ' Jensen ');

9.4 Verify the results on 104

Mysql> select * from person;

10. For the self-Increment ID column, the two servers synchronize with the same value and cause a conflict. Solution: Use MySQL's auto_increment_increment and auto_increment_offset to limit the IDs generated by each server to a certain range (e.g. 104 generate odd id,103 generate even ID)