Master-slave replication of mysql5.6.13 installed under Windows

The following operations are successful in Winows XP testing in VMware virtual machines

In the middle of a lot of detours, online a lot of information is for 5.1 of the previous version, in the new edition can not be used at all, so according to their own practice to organize this article

Main service: 192.168.131.21

From server: 192.168.131.22

1, the configuration of the host

Configuration in My.ini:

Server-id = 1

Binlog_format = "ROW"

Log-bin=mysql-bin

After starting the service, go to the MySQL command line and do the following:

GRANT all on * * to ' slave1 ' @ ' 192.168.131.22 ' identified by ' 123456 ';

If you need to synchronize a database, it is best to create only the appropriate database permissions, such as:

GRANT all on test1.* to ' slave1 ' @ ' 192.168.131.22 ' identified by ' 123456 ';

This will only synchronize the contents of the database.

2. Setting from server

My.ini:

Server-id = 2

Relay-log-purge=1

Skip-slave-start

Replicate-ignore-db=mysql #此处为不同步mysql数据库的内容此处如果为多数据库的话中间以逗号分开

Then launch MySQL into the command line and execute the following command

Change MASTER to master_host= ' 192.168.131.21 ', master_user= ' slave1 ', master_password= ' 123456 ';

3, start the master-slave replication

Start the service of the primary server first

Next start the service from the server

Then execute from the server command line

Start slave;

Then execute the following command to view the status from the server

SHOW SLAVE Status\g

1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.131.21
Master_user:slave1
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000007
read_master_log_pos:120
relay_log_file:rainpetlab2-relay-bin.000010
relay_log_pos:283
relay_master_log_file:mysql-bin.000007
Slave_io_running:yes
Slave_sql_running:yes
replicate_do_db:
Replicate_ignore_db:mysql
Replicate_do_table:
Replicate_ignore_table:
Replicate_wild_do_table:
Replicate_wild_ignore_table:
last_errno:0
Last_error:
skip_counter:0
exec_master_log_pos:120
relay_log_space:625
Until_condition:none
Until_log_file:
until_log_pos:0
Master_ssl_allowed:no
Master_ssl_ca_file:
Master_ssl_ca_path:
Master_ssl_cert:
Master_ssl_cipher:
Master_ssl_key:
seconds_behind_master:0
Master_ssl_verify_server_cert:no
last_io_errno:0
Last_io_error:
last_sql_errno:0
Last_sql_error:
Replicate_ignore_server_ids:
Master_server_id:1
Master_uuid:2ab70a0e-a1c4-11e2-9c60-60eb69d711ca
Master_info_file:d:\wamp\data5\master.info
sql_delay:0
Sql_remaining_delay:null
Slave_sql_running_state:slave have read all relay log; Waiting for the SLA
ve I/O thread to update it
master_retry_count:86400
Master_bind:
Last_io_error_timestamp:
Last_sql_error_timestamp:
MASTER_SSL_CRL:
Master_ssl_crlpath:
Retrieved_gtid_set:
Executed_gtid_set:
auto_position:0
1 row in Set (0.00 sec)

ERROR:
No query specified

Note: Slave_io_running:yes
Slave_sql_running:yes must be yes.

It is also important to note that:

I am doing from the server, is to stop the service, and then the overall copy of the data directory, if not made any changes, will appear the following prompt:

[ERROR] Slave I/o: Fatal error:the Slave I/O thread stops because master and Slave have equal MySQL server UUIDs; These uuids must is different for replication to work. error_code:1593
121122 17:40:58 [Note] Slave I/O thread exiting, read up to log ' first ', Position 4

At this point, you should remove the AUTO.CNF from the data directory and restart the service

Execute start slave;

Then create the database on the master server

You can see the synced data from the server again.

And if you change the machine name from the server, you can execute the command like this:

Stop slave;

Reset slave;

Start slave;

Master-slave replication of mysql5.6.13 installed under Windows