MySQL master-slave replication and read/write separation under Windows operating system

One, primary server (Master) configuration

1. Modify the MySQL configuration file My.ini

[Mysqld]

Log-bin=mysql-bin
Log-bin-index=mysql-bin.index
Server-id=1
Sync_binlog=1
Binlog_format=mixed
Binlog-do-db=test
Binlog-ignore-db=mysql
Binlog-ignore-db=performance_schema
Binlog-ignore-db=information_schema

Restart the MySQL service when the configuration is complete.

2, authorized to synchronize data from the server (slave) account password

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

Parameter description:

• Root:slave connect the account used by master
• Identified by ' 123456 ': Slave connection master uses the password
• 192.168.174.130:slave IP

Execute the command show Master Status\g;

Note the file and position in the results, which are used when configuring from the server (slave).

Second, from the server (slave) configuration

1. Modify the MySQL configuration file My.ini

[Mysqld]

server-id=2
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. Setting up the Connection master server (master) information

Change Master to master_host= ' 192.168.174.130 ', master_user= ' root ', master_port=3306,master_password= ' root ', master_ Log_file= ' mysql-bin.000008 ', master_log_pos= ' 170 '

Parameter description:

• Master_host:master 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

Restart the MySQL service.

Execute command: Start slave.

Execute command: show slave status\g.

The configuration succeeds when both slave_io_running and slave_sql_running are yes.

At this point, the data in the test database on the master server can be synchronized to the test database on the slave server.

Third, using MySQL proxy to achieve read and write separation

Configure this by using the configuration file in this way.

The contents of the configuration file mysql-proxy.conf mainly include:

[Mysql-proxy]
Admin-username=root
admin-password=123456
Admin-lua-script=c:/mysql-proxy/lib/mysql-proxy/lua/admin.lua
proxy-backend-addresses=192.168.174.130:3306
proxy-read-only-backend-addresses=192.168.174.131:3306
Proxy-lua-script=c:/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
Log-file=c:/mysql-proxy/log/mysql-proxy.log
Log-level=debug
Daemon=true
Keepalive=true

Execute command:

Mysql-proxy-p 192.168.174.133:4040--defaults-file=c:/mysql-proxy/bin/mysql-proxy.conf

To view the log file Mysql-proxy.log:

2014-12-19 16:27:40: (critical) plugin proxy 0.8.5 started
2014-12-19 16:27:40: (Debug) Max Open file-descriptors = 512
2014-12-19 16:27:40: (message) proxy listening on port 192.168.174.133:4040
2014-12-19 16:27:40: (message) added Read/write backend:192.168.174.130:3306
2014-12-19 16:27:40: (message) added Read-only backend:192.168.174.131:3306

The above log message indicates that the MySQL proxy started successfully, so that the read-write separation can be realized at this time.

Note: Because the default value for Min_idle_connections in Rw-splitting.lua is 4, that is, when the number of sessions reaches a minimum of 4 o'clock, read-write separation is performed, where we change it to 1, which allows for direct read-write separation.

MYSQL:HTTP://YUNPAN.CN/CFWP4TZDACVNP Extract Code b0db

MySQL Proxy:http://yunpan.cn/cfwpikpqwcsxm Extract Code ad1c

MySQL master-slave replication and read/write separation under Windows operating system