MySql configuration master-slave replication read/write splitting _ MySQL

MySql configuration master-slave replication read/write splitting bitsCN.com

Configure master-slave replication read/write splitting for MySql

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-bin

Server-id = 1

Server-id: 1-232-1; default value: 0 (reject 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 shocould 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;

Note the flush tables with read lock block COMMIT operation for InnoDB TABLES.

Keep the client from issuing the flush tables statement, so that the read lock is still valid. If you exit the client, the lock is released.

Use the show master status statement to determine the name and location of the current binary log file:

Mysql> show master status;

+ ------------------ + ---------- + -------------- + ---- -------------- +

| "File" | "location | Binlog_Do_DB | Binlog_Ignore_DB |

+ ------------------ + ---------- + -------------- + ---- -------------- +

MYSQL-bin.000003 | 73 | test | manual, MYSQL |

+ ------------------ + ---------- + -------------- + ---- -------------- +

The name and location of the log file displayed in the file column are displayed in the file. In this example, the location where the binary log file is mysql-bin.000003 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 no binary log is enabled before the MASTER node is running, the name and location value of the log file will be blank in the "show master status" or "mysqldump"-primary data display. In this case, you need to use the log file of the specified slave and the value after the location is null string ('') and 4.

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

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

Set PATH in windows

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

BitsCN.com