MySQL master-slave replication and read-write separation

We know that the application of access to the database is typically read in most cases, with only a small portion of the write. Therefore, read/write separation (read-write-splitting) can effectively reduce the pressure of the main library, thus solving the first database bottleneck encountered in the development of the website.

Master-slave replication

The master library must be bin-log first, because MySQL's master-slave replication is asynchronous, so the master library must log the update operation for the slave library to read.
Suppose there are now a, b two machines, A is master and B is slave.

Master

SSH to a server, log in to MySQL, create a replication dedicated user repl :

GRANT REPLICATION SLAVE ON *.* TO ‘repl‘@‘B的IP‘ IDENTIFIED BY ‘111111‘;

my.cnfto modify a file, turn on Bin-log and set Server-id:

[mysqld]log-bin = /XXXX/mysql-bin.logserver-id = 1

Restart MySQL for the configuration to take effect. Then set the read lock to ensure that the Master library has no read and write operations before configuring the slave library:

lock;

To view the file name and offset of the current bin-log of the Master library:

show master status;+------------------+----------+--------------+------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000002 |     1075 |              |                  |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)

Note the file name and offset. At this point, Master has stopped all the data update operations, this time we want to back up the master library data, and then restore to the slave library. It is recommended to mysqldump complete the operation by command. After the master backup is complete, the lock can be unlocked:

unlock tables;

Slave

SSH to B server, modify the configuration file:

[mysqld]server-id = 2

By mysqld_safe starting from the library, add the --skip-slave-start parameters:

--skip-slave-start

The purpose of this is not to start the replication thread from the start of the library because we have not yet configured the main library information.

mysql> CHANGE MASTER TO-> MASTER_HOST=‘主库地址‘,-> MASTER_PORT=3306,-> MASTER_USER=‘repl‘,-> MASTER_PASSWORD=‘111111‘,-> MASTER_LOG_FILE=‘mysql-bin.000002‘, -> MASTER_LOG_POS=1075;

To start the slave thread:

start slave;

The configuration is now complete from the library. If all goes well, an update operation is performed in the main library, and the library will follow up immediately.
If you find a problem, you can perform

show slave status;

View more information.

Read/write separation

There are two scenarios for read-write separations:

1. The control application, the write operation connects the main library, the read operation connects from the library.
2. Introduction of database Middleware. As the official mysql-proxy . The advantage is that the read-write separation is completely transparent to the application and does not require any modification to the program code. However, there are mysql-proxy still only alpha versions available, and it is not recommended for use in production environments.

In fact, there is a more elegant solution, which is to use ReplicationDriver . MySQL's JDBC driver comes with the ReplicationDriver ability to conn.setReadOnly(true) route all the connections in JDBC to the slave library, so that we do not have to operate on program code. With spring, we can use @Transactional(readOnly = true) annotations. Because MySQL master-slave replication has a delay, we can set it to allow the data to be read from the main library for operations with high real-time requirements, readOnly false ReplicationDriver which is an acceptable scenario.

Example configuration:

<beanID="DataSource" class="Org.apache.commons.dbcp.BasicDataSource"> < Property name="Driverclassname"Value="Com.mysql.jdbc.ReplicationDriver"/> < Property name="url"Value="jdbc:mysql:replication://Main Library ip:3306, ip:3306/test from library"/> < Property name="username"Value="Root"/> < Property name="Password"Value="Root"/> </bean>

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL master-slave replication and read-write separation