MySQL master-slave replication and read-write separation

We know that the application of access to the database is mostly read, with only a very small percentage of the write. Therefore, read/write separation (read-write-splitting) can effectively reduce the main pool pressure, thus solving the first database bottleneck encountered during the development of the site.

Master-slave replication

You must first open the Master library's Bin-log, because MySQL's master-slave replication is asynchronous. So the master library must record 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‘;

Change the my.cnf file. Turn on Bin-log and set Server-id:

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

Restarting MySQL causes 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 mysqldump that the operation be completed by command. After the master backup is complete, you can cancel the lock:

unlock tables;

Slave

SSH to b server, change config file:

[mysqld]server-id = 2

By mysqld_safe starting from the library. Join --skip-slave-start Number:

--skip-slave-start

The purpose of this is not to allow the replication thread to start when it is started from the library. Since we have not configured the main library information yet.

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. Assuming everything goes well, an update operation is run in the main library and is immediately followed up from the library.

If you find a problem, you can run

show slave status;

View specific information.

Read/write separation

There are two options for reading and writing 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 application is completely transparent and does not require any changes to the program code.

   But mysql-proxy there is still only a alpha version number, and it is not recommended to use it in the production environment.

In fact, the solution is another elegant solution. That's the use ReplicationDriver .

MySQL's JDBC driver comes with it ReplicationDriver . It is able to route all conn.setReadOnly(true) of the connections in JDBC to the slave library, so that we do not have to do major surgery on the program code.

With spring, we are able to use @Transactional(readOnly = true) annotations.

Due to the delay in MySQL master-slave replication, we are able to readOnly set up to allow data to be read from the main library for operations requiring high real-time, false ReplicationDriver which is an acceptable scenario.

Configuration Demo Sample:

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

MySQL master-slave replication and read-write separation