Database read/write separation, also said before this review let’s: master/slave replication, proxy read/write separation, JAR package read/write separation. The volume of the three ways is different, according to their own needs to find the most appropriate, say together.

What is Read-write Separation (1)

Our general application access to the database is nothing more than reading data, modify data, insert data, delete data.

The database is generally divided into: master (master library is also a write library) slave (slave library is also a read library). The separation of read and write means: all write operations (insert update delete) go to the master library, and other operations go to the slave library.

  • What is the purpose?

Our general application to the database is “read more write less”, that is to say to the database to read data pressure is relatively large. The main purpose of read/write separation is to reduce the stress on the main library. Reduce read stress on the main library. Just reduce the master library read pressure, not to say that you can not use the master library to query (order immediately query order status).

  • ② Prerequisite:
  1. Data on the slave read library must be consistent with that on the master write library.
  2. Write data must be written to evidence base.
  3. Data must be read from the read library. This is not necessarily true. Special service requirements may require the primary library to be read.
  • ③ The industry uses much

Master multi-slave mysql cluster solution, at least two libraries. One master, one subordinate.

  • ④ Primary/secondary replication

Master my.cnf configuration:

binlog-do-db=tlshop
binlog-ignore-db=mysql
binlog_format=mixed
log-bin=mysql-bin
server-id=1
Copy the code

Slave. My CNF configuration

replicate-do-db=tlshop
replicate-ignore-db=mysql
server-id=2
The following configuration is pre-5.6
# master - host = 192.168.0.15
#master-port=3306
#master-user=root
#master-password=123456
Copy the code

Slave Dynamically configures node information

1. Enter the master database

 show master status\G
Copy the code

2. Enter the slave database

change master to master_host='192.168.0.15', master_user='root', master_password='123456',
master_log_file='(enter 'File' where 'show master status' =' 1 '), master_log_pos= '(enter Position in 1 show master status)'; slave start; / / startCopy the code

Read/write Separation (2)

  • ① Industry Solutions

Proxy layer Proxy: Atlas open-source software

Application layer: Sharding-JDBC

  • ② Agent layer Atlas

Website github.com/Qihoo360/At…

Atlas is a data middle layer project based on MySQL protocol developed and maintained by Qihoo 360 Web Platform Infrastructure team. It is based on the official release of MySQL mysql-Proxy 0.8.2, which fixes a lot of bugs and adds many features. At present, this project has been widely used in 360 company. Many MySQL businesses have been connected to Atlas platform, and the number of read and write requests has reached billions every day. At the same time, more than 50 companies have Atlas in production, and more than 800 people have joined our developer community, and the numbers are growing.

The main function

  1. Read/write separation.
  2. Load balancing from the library.
  3. IP filter.
  4. Automatic sorting.
  5. DBA can smooth up and down DB.
  6. Automatically removes downed DB files.

The Atlas configuration

Forced route:

Comment out the main library. Alatas:

/*master*/ select * from table nameCopy the code

Business needs to order immediately check

Sharding-jdbc is an open source distributed database middleware that requires no additional deployment or dependencies and is fully compatible with JDBC and various ORM frameworks. Sharding-jdbc, as a development-oriented micro service cloud native basic class library, has fully realized the functions of library and table, read and write separation and distributed primary key, and preliminatively realized flexible transactions. Before have special article “the Internet architecture” (65) shardingsphere.apache.org/index_zh.ht…

<dependency>
  <groupId>io.shardingjdbc</groupId>
  <artifactId>sharding-jdbc-core-spring-namespace</artifactId>
  <version>${sharding-jdbc.version}</version>
</dependency>
<dependency>
  <groupId>io.shardingjdbc</groupId>
  <artifactId>sharding-jdbc-core</artifactId>
  <version>${sharding-jdbc.version}</version>
</dependency>

<master-slave:data-source id="dataSource" master-data-source-name="dataSourceMaster"
slave-data-source-names="dataSourceSlave,dataSourceSlave,dataSourceSlave" strategytype="ROUND_ROBIN" />
Copy the code

PS: Alatas:

  1. The program does not need to deal with the details of master/slave configuration
  2. The implementation principle is proxy, so performance degrades
  3. And you need to maintain high availability
  4. Reduced programmer skill requirements
  5. Only supports mysql

Sharding – JDBC:

  1. Master and slave configuration in the program, so increased the technical requirements of the programmer
  2. The implementation principle is JDBC enhancement, so any database type is supported with better performance than the one above
  3. And it requires no maintenance.
  4. Mysql、 Oracle、 sql server