MySql5.6 read-Write separation under windows

Description

1. MYSQL Version 5.6

2. The master-slave server IP used in this example is: 192.168.120.211;192.168.120.209

First, configure the master server

1. Configuration

In My.ini [MYSQLD] is configured as follows:

Log-bin=mysql-bin
Log-bin-index=mysql-bin.index
Server-id = 1--Service ID, unlike slave's Server-id
Sync_binlog=1
Binlog_format=mixed
BINLOG-DO-DB = TestDB--The name of the database from which synchronization is
binlog-ignore-db = MySQL
Binlog-ignore-db = Performance_schema
Binlog-ignore-db = Information_schema

There's a saying on the web Max_allowed_packet need to configure a larger value. The cause is unknown.

Restart the MySQL service when the configuration is complete.

2. Authorization: Authorize slave to synchronize the account password of the data.

GRANT REPLICATION SLAVE on *.*to ' root ' @ ' 192.168.120.209 ' identified by ' root ';

Description

• Account used by Root:slave
• Identified by ' ROOT ': slave password used
• 192.168.120.209:slave Database IP

The master side displays the information through show Master Status\g.

Note the file in, and position. This information will be used in the Slave

First, configure the slave server

1. Modify the configuration file

In the configuration file of the slave database server, [MYSQLD] does the following:

server-id=2--Service ID, unlike Master's Server-id
Log-bin = Mysql-bin
Relay-log-index = Slave-relay-bin.index
Relay-log = Slave-relay-bin

Sync_master_info = 1
Sync_relay_log = 1
Sync_relay_log_info = 1

2. Associating the Master information

Use the following command:

Change Master to master_host= ' 192.168.120.211 ', master_user= ' root ', master_port=3306,master_password= ' root ', master_ Log_file= ' mysql-bin.000001 ', master_log_pos= ' 332 '.

Command Description:

• Master_host:master Database Server IP
• Master_user:master database via Grant authorized account
• Master_port:master the port number used by the database
• Master_password:master database via grant-authorized password
• The file name displayed in the Master_log_file:master database through show Master Status\g
• Position data displayed in the Master_log_pos:master database through show Master Status\g

Restarting the slave MySQL service

Enter start slave on the command line.

Enter show slave status\g on the command line, as shown below:

Note: Only slave_io_running and slave_sql_running are yes to configure success.

At this point, the configuration is complete. In this way, data from the TestDB on the master server can be synchronized to the slave TestDB database.

MySql5.6 read-Write separation under windows