Go Getting started with MySQL master-slave replication

1.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. 】

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

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

From: http://zhangfengzhe.blog.51cto.com/8855103/1563032

Go Getting started with MySQL master-slave replication