MySQL Master/Slave replication and read/write separation in Windows

MySQL Master/Slave replication and read/write separation in Windows

1. 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

After the configuration is complete, restart the MySQL service.

2. account and password authorized to synchronize data to the slave server (slave)

Grant replication slave on *. * TO 'root' @ '192. 168.174.131 'identified by '20140901 ';

Parameter description:

• Root: account used by slave to connect to the master
• Identified by '000000': password used BY slave to connect to the master
• 192.168.174.130: slave IP

Execute the command show master status \ G;

Note the File and Position in the result, which will be used when configuring the slave server (slave.

 

Ii. slave 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. Set the information for connecting to the master server (master)

Change master to master_host = '192. 168.174.130 ', master_user = 'root', master_port = 192, master_password = 'root', master_log_file = 'mysql-bin.000008', master_log_pos = '123'

Parameter description:

• Master_host: master IP address
• Master_user: account authorized by the master database through GRANT
• Master_port: port number used by the master database
• Master_password: Password authorized by the master database through GRANT
• Master_log_file: name of the File displayed in the master database through show master status \ G
• Master_log_pos: Position data displayed through show master status \ G in the master database

Restart the MySql service.

Run the command: start slave.

Run the command: show slave status \ G.

If both Slave_IO_Running and Slave_ SQL _Running are Yes, the configuration is successful.

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

 

Iii. use MySQL Proxy for read/write splitting

Use the configuration file here for configuration.

Configuration File mysql-proxy.conf content mainly includes:

[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

Run the following command:

Mysql-proxy-P 192.168.174.small: 4040 -- defaults-file = C:/mysql-proxy/bin/mysql-proxy.conf

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
16: 27: 40: (message) proxy listening on port 192.168.174.133: 4040
16: 27: 40: (message) added read/write backend: 192.168.174.130: 3306
16: 27: 40: (message) added read-only backend: 192.168.174.131: 3306

If the preceding log information is displayed, MySQL Proxy is successfully started, and read/write splitting can be implemented.

Note: Since the default value of min_idle_connections in the rw-splitting.lua is 4, read/write splitting is performed only when the number of sessions reaches 4, here we change it to 1, you can directly perform read/write splitting.

 

MySQL: http://yunpan.cn/cfWp4tZDACvnp extract code b0db

MySQL Proxy: http://yunpan.cn/cfWpikpQWCsxM extract code ad1c