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