"Database" read-write separation, master-slave replication

1. Database read/write separation

Read and write separation, the basic principle is to let the master database (Master) processing transactional increment, delete, change operation (Insert,delete,update), from the database (slave) processing select query operation. Database replication is used to synchronize changes caused by transactional operations to the slave database in the cluster.

The main library is responsible for writing data and reading data. The Read library is only responsible for reading data. Each time there is a write library operation, update the cache synchronously, read the cache at the first read the DB

The basic principle of MySQL read and write separation is to have the master database handle the write operation and slave the database for read operations. Master synchronizes the change of the write operation to each slave node (resolves the master-slave database synchronization delay issue).

MySQL read-write separation can improve system performance because:

• Increased physical servers and improved machine handling capabilities. Get hardware for performance.
• The master and slave are only responsible for their own reading and writing, greatly easing the X lock and S lock contention.
• Slave can configure the MYIASM engine to improve query performance and save system overhead.
• Master Direct write is concurrent, slave is sent through the main library Binlog recovery data is asynchronous.
• Slave can set some parameters individually to improve the performance of their reads.
• Increase redundancy and increase availability.

Mysqlproxy Introduction

The following uses the MySQL official Database agent layer product Mysqlproxy build read and write separation.
Mysqlproxy actually establishes a connection pool between the client request and the MySQLServer. All client requests are sent to Mysqlproxy and then analyzed by Mysqlproxy to determine whether the read or write operation is distributed to the corresponding MySQLServer. For multi-node slave clusters, load balancing can also be achieved.

  2.MySQL Getting Started with master-slave replication

First, let's look at a diagram:

Operations that affect the MYSQL-A database are written to the local log system A after the database is executed.

Suppose that the real-time database event operation in the log system will change, on the Mysql-a 3306 port, sent to Mysql-b via the network.

After the Mysql-b is received, the local log system B is written, and then the database events are completed in the database.

Then, mysql-a changes, Mysql-b will also change, this is called the replication of MySQL, that is, MySQL replication.

In the above model, MYSQL-A is the primary server, that is, Master,mysql-b is from the server, that is, slave.

Log system A, which is actually a binary log in the log type of MySQL, is specifically used to save all the actions that modify the database table, namely bin log. Note that MySQL writes the binary log to ensure transaction security before releasing the lock after executing the statement.

Log system B, not a binary log, because it is copied from the Mysql-a binary log, not its own database changes generated, a bit of the sense of relay, called the relay log, that is, relay log.

It can be found that through the above mechanism, MYSQL-A and Mysql-b database data can be guaranteed to be consistent, but there must be a delay in time, that is, the mysql-b data is lagging.

"MYSQL-A database operations can be performed concurrently, even without considering what the network is doing, but Mysql-b can only read one from relay log," he writes. So Mysql-a writes very frequently, Mysql-b probably can't keep up. 】

3. Several ways of master-slave replication

Synchronous replication

The so-called synchronous replication, meaning master changes, must wait for slave-1,slave-2,..., Slave-n to complete before returning.

This is obviously undesirable and is not the default setting for MySQL replication. For example, on a web front-end page, users add a record and wait a long time.

Asynchronous replication

Just like an AJAX request. Master only needs to complete its own database operation. As to whether the slaves received the binary log, whether to complete the operation, do not care. The default settings for MySQL.

Semi-synchronous replication

Master only guarantees that one operation in slaves succeeds and returns, and the other slave regardless.

This feature was introduced by Google for MySQL.

  4. Master-Slave Replication Analysis

Problem 1:master write operation, slaves the same operation as passive, maintain data consistency, then slave can be active write operation?

Assuming that slave can take the initiative to write, slave cannot notify Master, which results in inconsistent master and slave data. Therefore, slave should not write, at least slave the database involved in replication cannot be written. In fact, the concept of read and write separation has been revealed here.

Question 2: Master-slave replication, there can be n slave, but these slave can't write operations, what do they do?

Data backup can be achieved.

Similar to high-availability features, once master hangs up, you can let the slave top up, while the slave is promoted to master.

Disaster recovery, such as master in Beijing, the earthquake hung, then the slave in Shanghai can continue.

It is mainly used to implement scale out, load sharing, and can spread the read task to slaves.

"It is possible that a system reads much more than writes, so the write operation is sent to master, and the read operation is sent to slaves."

Issue 3: Master,slave1,slave2,... in master-slave replication Wait so many MySQL databases, such as which database should a Java Web application connect to?

Of course, we can do this in the application, insert/delete/update these updates to the database, using connection (for master), and select with connection (for slaves) operation. Our application will also complete how to select one from slaves to perform a select, such as a simple round robin algorithm.

In this case, the equivalent of the application to complete the SQL statement routing, and the MySQL master-slave replication schema is very associated with, once master hangs, some slave hang, then the application will be modified. Is it possible to have an application that doesn't have much to do with MySQL's master-slave replication architecture? You can look at the following figure:

To find a component, application program only needs to deal with it, use it to complete the MySQL proxy, and implement the routing of the SQL statement.

MySQL Proxy is not responsible, how to choose one from the numerous slaves? Can be handed to another component (such as Haproxy) to complete.

This is called a read-write separation of MySQL read write Splite,mysql.

Question 4: What if MySQL proxy, direct, master some of them hang up?

The president will usually get a vice president, safekeeping. Similarly, a backup can be given to these critical nodes.

Question 5: When Master's binary log generates an event, it needs to be sent to slave, if we have n slave, is it n times or only once?

If only sent once, sent to Slave-1, that slave-2,slave-3,... What about them?

Obviously, it should be sent n times. In fact, inside MySQL master, maintaining n threads, each thread is responsible for sending the binary log files to the corresponding slave. Master is both responsible for writing and maintaining n threads, and the burden is heavy. So, Slave-1 is master from, Slave-1 is slave-2,slave-3,... , while Slave-1 is no longer responsible for select. Slave-1 transfers the burden of the master copy thread to its own body. This is called the concept of multi-level replication.

Issue 6: When a select is sent to the MySQL proxy, this time the slave-2 response, the next time by the slave-3 response, so that the query cache can not be exploited.

Should find a shared cache, such as memcache to solve. The slave-2,slave-3,... The results of these queries are cached in Mamcache.

Question 7: As the application grows and reads a lot, we can extend the slave, but what if master cannot satisfy the write operation?

Scale on? Better server? No best, only better, too expensive ...

Scale out? The master-slave replication architecture has not been satisfied.

You can split the library vertically, splitting the table horizontally.

  5 Vertical segmentation and horizontal segmentation of databases

Data segmentation can be physical, the data through a series of segmentation rules to distribute the data to different DB servers, routing rules to access a specific database, so that each access to face is not a single server, but the n server, which can reduce the load pressure on a single machine.

Data segmentation can also be within the database, the data through a series of segmentation rules, the data distributed to a database of different tables, such as the article into article_001,article_002 and other sub-tables, A number of sub-tables are flattened horizontally and have a complete article table that makes up the logic, which is actually very simple to do. For example, for example, the article table now has 5000w data, at this point we need to add (insert) a new data in this table, after the insert is completed, the database will be re-indexed to this table, 5000w rows of data indexing system overhead is not negligible. But conversely, if we divide the table into 100 tables, from article_001 to article_100,5000w, there is only 500,000 rows of data in each sub-table, At this time, we are going to a table with only 50w rows of data after the Insert data index will be a magnitude decline, greatly improving the efficiency of the DB runtime, increase the concurrency of the DB. Of course, the benefits of the table are unknown, and there are many obvious benefits to be found, such as the lock operation of the write operation.

In conclusion, the sub-Library reduces the load of the single point machine, and the table increases the efficiency of the data operation, especially the write operation.

"Database" read-write separation, master-slave replication