Elementary Introduction to MySQL Replication (replication) Fundamentals

1. MySQL Replication replication Process
mysql Replication (replication) is an asynchronous copy, copied from a MySQL instace (called master) to another MySQL instance (called slave). The implementation of the entire replication operation is mainly done by three processes, of which two processes are in slave (SQL process and IO process), and the other process is on master (IO process).
to implement replication, you must first turn on the binary log (Bin-log) function on the master side, otherwise it cannot be implemented. Because the entire replication process is actually a variety of operations that are logged in the execution log that slave obtains the log from the master side and then executes it in its own full sequence.
the basic process for MySQL replication replication is as follows:
1), slave above the IO process connected to master, and request from the specified log file at the specified location (or from the beginning of the log) after the contents of the log;
2), master receives a request from the IO process from slave, the IO process that is responsible for the replication reads the log information after the specified location of the log according to the requested information and returns the IO process to the slave. In addition to the information contained in the log, the returned information includes the name of the Bin-log file returned to the master side and the location of the Bin-log;
3), after the slave IO process receives the information, the received log content is added to the end of the Relay-log file on the slave side, and the file name and location of the Bin-log read to the master side are recorded in the Master-info file. So that the next time you read the high-speed master "I need to start from somewhere in the Bin-log log content, please send me";
4), slave's SQL process detects the new additions to the Relay-log, will immediately parse the contents of Relay-log as the real execution of the master side of the executable content, and execute on its own.
In fact, in the old version of MySQL replication implementation on the slave side is not two processes completed, but by a process to complete. However, it was later found that there were significant risks and performance issues, mainly as follows:
first, a process that makes replication Bin-log logs and parse logs and executes it on its own is a serial process that has limited performance and a long delay in asynchronous replication.
In addition, after the slave end obtains bin-log from the master side, it needs to parse the log content and execute it on its own. In this process, the master side may have made a lot of changes and claimed a large number of logs. If there is an uncorrectable error in the storage at the master end of this stage, all changes made at this stage will never be recovered. If the pressure on the slave side is relatively large, the process may take longer.
So, later versions of MySQL to address this risk and improve the performance of replication, the slave end of the replication to two processes to complete. The person who proposed the improvement was "Jeremy Zawodny", a Yahoo! Engineer. This solves the performance problem, shortens the asynchronous delay time, and reduces the amount of data loss that may exist. Of course, even if the two threads are processed now, there is also the possibility of slave data delay and data loss, after all, this replication is asynchronous. These problems will exist as long as the data changes are not in one thing. If you want to completely avoid these problems, you can only use the MySQL cluster to solve. However, the MySQL cluster is a memory database solution, need to load all the data into memory, so the memory requirements are very large, for the general application of the implementation is not too large.
2. MySQL replication replication Implementation level
mysql replication replication can be based on a single statement (Statement level) or based on a record (Row level), which can be set in MySQL configuration parameters, The settings at different replication levels affect the Bin-log records in different forms on the master side.
row level: Logs are recorded in the form of each row of data being modified, and then the same data is modified on the slave side.
Advantage: In row level mode, the Bin-log can not record the context-sensitive information of the executed SQL statement, just need to record that one record has been modified and changed to what. So the log content of row level will be very clear to record the details of each row of data modification, very easy to understand. There are no stored procedures, or function, and trigger calls and triggers that cannot be copied correctly in certain situations.
disadvantage: At the row level, all executed statements are recorded in the log when recorded, which may result in a large amount of log content, such as an UPDATE statement: Update product set Owner_member_ id = ' B ' where owner_member_id = ' A ', after execution, the log does not record the corresponding amount of the UPDATE statement (MySQL logs the Bin-log log as an event), but the change of each record updated by this statement, so that many of the records are updated many events. Naturally, the amount of Bin-log logs will be very large. Especially when executing statements such as ALTER TABLE, the amount of log generated is staggering. Because MySQL handles table structure change statements such as ALTER TABLE, each record in the entire table needs to be changed, in effect rebuilding the entire table. Then each record of the table is recorded in the log.
Statement level: Every SQL that modifies data is recorded in the Bin-log of master. Slave when replicating, the SQL process parses the same SQL that sing Woo the original master side to execute again.
Advantages: The advantages of statement level first is to solve the disadvantage of the row level, do not need to record each row of data changes, reduce bin-log log volume, save IO, improve performance. Because he only needs to record the details of the statements executed on master, and the context in which the statements are executed.
Cons: Because he is the execution statement of the record, so, in order for these statements to be executed correctly at the slave end, he must also record some relevant information of each statement at the time of execution, that is, contextual information, To ensure that all statements are executed at the slave end of the cup with the same results as they do at the master end. In addition, because MySQL is now developing relatively fast, a lot of new features continue to join, so that MySQL replication encountered a large challenge, natural replication involves more complex content, bugs will be more prone to appear. At the statement level, there are a number of things that have been found to cause MySQL replication problems, mainly when modifying the data when using certain functions or functions, such as: Sleep () function in some versions can not actually copy, The last_insert_id () function is used in stored procedures, which may cause inconsistent IDs on slave and master, and so on. Because the row level is recorded on a per-row basis, no similar problem occurs.
as you can see from the official documentation, the previous MySQL has been only based on the statement copy mode until the 5.1.5 version of MySQL started to support row level replication. Starting with 5.0, MySQL replication has resolved issues that are not correctly replicated in a large number of older versions. However, due to the advent of stored procedures, MySQL replication replication has brought more new challenges. In addition, the official documentation says that, starting with version 5.1.8, MySQL provides a third replication mode except for the statement level and row level: Mixed, which is actually a combination of the first two modes. In mixed mode, MySQL distinguishes between the log forms of treated records based on each specific SQL statement executed, that is, choosing between statement and row. The statment level in the new version is still the same as before, just record the executed statement. The new version of the MySQL Squadron row level mode has also been optimized, not all changes will be recorded at the row level, like when the table structure changes in the statement mode to record, If the SQL statement is really a statement that modifies data such as update or delete, then all rows are changed.
3, MySQL Replication replication Common Architecture
MySQL Replication replication environment more than 90% is a master with one or more slave architecture mode, mainly used for reading pressure ratio of the application of the low-cost extension of the database-side solutions. Because as long as the pressure of master and slave is not too great (especially the slave pressure), the delay of asynchronous replication is generally very small. Especially since the slave end of the replication mode to two process processing, but also reduce the delay of the slave end. The benefit is that the data real-time requirements are not particularly sensitive applications, only through the low-cost PC server to expand the number of slave, read pressure spread to multiple slave machines, can solve the database-side reading pressure bottleneck. This largely solves the current database pressure bottleneck of many small and medium-sized websites, and even some large websites are using similar schemes to solve the database bottleneck.
The implementation of a master with multiple slave is very simple, and the implementation of multiple slave and a single slave is not much different. On the master side is not care how many slave connected to the master side, as long as there is slave process through the connection authentication, request Binlog information to him, he will follow the IO process to connect up the requirements, read his binlog information, return to slave io process. For the configuration details of the slave, the official MySQL document has been very clear, and even introduced a variety of implementation slave configuration methods.
MySQL does not support a slave instance from a schema that belongs to more than one master. That is, a slave instance can only accept a master synchronization source, heard that patches can improve such features, but not practice. MySQL AB does not implement such a function, mainly to consider the problem of conflict resolution.
MySQL can also be built dual master mode, which means that two MySQL instance each other's master, but also for each other's slave. But in general this architecture is only one end to provide services to avoid conflict problems. Because even if the modifications executed on both sides are sequential, due to the asynchronous implementation mechanism of the replication, it is also possible that even late modifications may be overwritten by earlier modifications, as in the following scenario:
point in time MySQL A MySQL B
1 update x table y record is ten
2 Update x table y record as
3 Get to a log and apply, update x table's Y record to 10 (does not meet expectations)
4 Get B log update x table y record 20 (meet expectations)
in this way, not only the data above the B-Library is not what the user expects, but the data on both sides of A and B also appears inconsistent. It is not possible to avoid cross-writing until the write operation is fixed separately at the ends of a and b according to a certain condition, so that the above problem can be avoided.