Configure master-slave replication for read/write splitting in MySql

Does MySql configure master-slave replication read/write splitting? Mysql version: 5.6.13OS: windowsserver2008Master configuration? In the my. ini configuration file, the master-slave replication must be performed through the binary log file. The binary log function must be enabled and the ServerID must be configured? [Mysqld] The log-binmysql-binserver-id1server-id is 1-232? -1? Of

Does MySql configure master-slave replication read/write splitting? Mysql version: 5.6.13 OS: windows server 2008 Master configuration? In the my. ini configuration file, the master-slave replication must be performed through the binary log file. The binary log function must be enabled and the Server ID must be configured? [Mysqld] log-bin = mysql-binserver-id = 1 server-id is 1-2 32? -1? Of

Configure master-slave replication read/write splitting for MySql

?

Mysql version: 5.6.13

OS: windows server 2008

Master Configuration

?

In the my. ini configuration file

Master-slave replication must be performed through binary log files. You must enable the binary log function and configure the Server ID.

?

[mysqld]log-bin=mysql-binserver-id=1

Server-id is 1-232? -1? The default value is 0 (deny all slave)

?

Enhanced innodb Engine Durability

Innodb_flush_log_at_trx_commit = 1? And?sync_binlog=1

?

For the greatest possible durability and consistency in a replication setup using?InnoDB? With transactions, you shoshould use?innodb_flush_log_at_trx_commit=1? And?sync_binlog=1? In the master?my.cnf? File.

?

? Slave Configuration

server-id=2

Prevents modification of server host names

? Relay-log = mysqld-relay-bin

?

Master

Create an account repl with the copy permission

Grant replication slave on *.*
-> TO 'repl' @ 'remotehost .mydomain.com 'identified by 'slavepass ';

?

mysql> FLUSH TABLES WITH READ LOCK;

ForInnoDBTable notes?FLUSH TABLES WITH READ LOCKBlock?COMMITOperation.

Keep the client sending?FLUSH TABLESStatement, so that the read lock is still valid. If you exit the client, the lock is released.

?

?

?

Use?SHOW MASTER STATUSStatement to determine the name and location of the current binary log file:

?

Mysql>SHOW MASTER STATUS;+ Bytes + ---------- + -------------- + ---- ------------ + | "file" | "location | Binlog_Do_DB | bytes | + bytes + ---------- + -------------- + ---- -------------- + MYSQL-bin.000003 | 73 | test | manual, MYSQL | + ------------------ + ---------- + -------------- + ---- -------------- +

?

FileThe name andLocationPosition in the file. In this example, what is the binary log file?Of mysql-bin.000003The location is 73. Record these values. You need them, and later you set up slaves. They indicate that the slave copy operator should start processing new updates from the master.

?

If the master has run and no binary log is enabled before, useSHOW MASTER STATUSOrMaster Data of mysqldumpThe log file name and location value are empty. In this case, you need to use the log file of the specified slave and the value after the location is a null string ('') And4.

?

Now you have the information you need to copy the binary logs that are read from the beginning at the correct position.

?

Set master-slave connection information

?

mysql> CHANGE MASTER TO    ->     MASTER_HOST='master_host_name',    ->     MASTER_USER='replication_user_name',    ->     MASTER_PASSWORD='replication_password',    ->     MASTER_LOG_FILE='recorded_log_file_name',    ->     MASTER_LOG_POS=recorded_log_position;

View the connection status show slave status \ G;

?

? Read/write splitting Configuration

Windows SettingsPATH?

?

Create proxy 222 as Master 223 as read-only

?

sc create "Proxy" DisplayName= "MySQL Proxy" start= "auto" binPath= "F:\tool\mysql-proxy-0.8.2-winx64-x86\bin\mysql-proxy-svc.exe --proxy-address=localhost:3306 --proxy-backend-addresses=10.133.10.222:3306 --proxy-read-only-backend-addresses=10.133.10.223:3306"

?

 net start proxy

C:\> sc delete proxy

Test After configuration, stop slava from the server

Execute the insert operation through the proxy and perform the query. Then start slave. There is no problem with master-slave replication.

Read/write splitting is not tested yet