MySQL replication principle--turn

Original address: http://www.codeweblog.com/mysql-replication-principle/

1, the replication process
MySQL Replication (replication) is an asynchronous replication, from a MySQL instace (called Master) to another MySQL inst ance (call it Slave). Implement the copy operation completed mainly by three processes, both processes in the Slave (SQL process and the IO Proce SS), another process in the Master (IO process) on the

To implement replication, you must first open the Master end of the binary log (bin-log) function, or can is not achieved. Because the whole process is actually copied from the Master Slave terminal access to the log and then himself fully in t He implementation of the order of log records in the various operations.
Copy The basic process is as follows:
(1) Slave IO above the process connection Master, and requests the specified log file from the specified location (or from The beginning of the log) after the log contents;
(2) Master Slave receives the IO process from request, through the IO was responsible for copying the information, upon req Uest, to read the development process of the that log after the log information specified location and return to the Slave of T He IO process. Return information In addition to the information contained in the log, but also including this information have been retur Ned to the Master end of the Bin-log file name and the bin-log position;
(3) Slave of the IO process of receiving information, would receive the log contents in turn added to the Slave end of the Relay-log files of the end, and read to the Master end of the Bin-log file name and location of the records to the master- Info file so next time can is clearly read the high-speed Master "I need a bin-log of where to begin later in the log, pl Ease send it to me ";
(4) Slave to SQL process detected a new Relay-log increase in the content, it'll immediately relay-log parsing the Conte NTS of a real implementation of the Master side executable content of those times, and in its implementation.

In fact the old version of Mysql replication to achieve and the processes in the Slave side are not do, but by a process to C Omplete. But later discovered there are a big risk to doing so and performance issues, notably the following:
First, a process to copy the Bin-log-log and the parse the log and its implementation process in a serial process, subject To certain restrictions on the performance, asynchronous replication of the delay would be longer.
In addition, slave-side from the Master-to-get bin-log over the side, the need-to-then-the-log, then-it-implemen Tation. In this process, the Master client may have a lot of changes and has claimed a large number of logs. Master-side at this stage if the storage can does not fix the error occurred and then generated at this stage all the changes would Never be recovered. If the pressure in the Slave side, when relatively large, this process could take longer.
Therefore, the latter version of Mysql to address this risk and improve the performance of replication, replication of the Slave-side to the processes to complete. The improved scheme proposed Yahoo! Who was an engineer "Jeremy Zawodny". This would not be solve the performance problem, but also shorten the delay time of asynchronous, but also reduces the AM Ount of possible loss of data. Of course, even if threads into this deal now after the slave data is also still exists the possibility of delay and D ATA loss, after all, the replication is asynchronous. As long as the data changes is not a thing, these problems would always exist. If you want to completely avoid these problems, the cluster can is only being used to solve the MySQL. However, the cluster is the memory MySQL database solution, you need to load all data into memory, so the memory requ Irements of very large, and for general applications isn't much sex can be implemented

2, copy to achieve level
Mysql replication can based on a statement (statement level), it can is based on a record (Row level), Configuration PA Rameters can is set in Mysql replication level of the different levels of replication settings affect the Master end of th e Bin-log records into different forms.
(1) Row Level:log data would be recorded as each of the line are a modified form, and then again in the slave side to modify the S AME data.
Benefits:in The row level mode, Bin-log records can be executed SQL statement without the context of relevant information , it's only need to record a record a and that's modified, what kind of a modified. Therefore, the contents of row level log record would be very clear details of each line of data modification are very easy To understand. And won't appear under certain circumstances a stored procedure or function, and the trigger and trigger a call to the Problem can not be copied correctly.
Disadvantages:row level, all statements executed when the records to the log, it also would record each line to record the Changes, this may produce a large number of log content, such as there was such an update statement:update product set ow ner_member_id = ' B ' where owner_member_id = ' A ', after the implementation of the log was not recorded in this update Statem ENT, the amount corresponding to the event (MySQL in the form of events to record Bin-log log), but this statement for EAC H of the Updated a record of the changes, so a lot of records into the records to be Updated many events. Nature, Bin-log log volume would be enormous. Especially when the implementation of ALTER TABLE statements like, when the log volume is staggering. Mysql ALTER TABLE because the table structure changes like handling statement was a record in the table was required for EA CH change, in fact, was to rebuild the entire table. Then the table would be recorded for each record to the log.
(2) Statement level:modifies the data in each record to the SQL would be is the bin-log in the master. Slave SQL process in the copy when the original master would resolve to the same end of SQL executed to run again.
Advantages:statement level benefits under the first was to solve the shortcomings of the next row level, the data does not r Ecord changes in each row, reducing the amount of bin-log log, save IO, improve performance. Because he recorded in the Master's statement on the implementation details and the implementation of the context whe n the information statement.
Disadvantages:since the implementation of his statement are recorded, so, in order to make these statements in the slave s IDE can is correctly implemented, then he must also record the time each statement in the implementation of some Informati On, that's, context information, to ensure this all statements when the slave side can be implemented and the Implementat Ion of the master side when the same results. The other are, you Yu MySQL are relatively fast development, many continue to add new features to make Mysql be reproduced E Ncountered no small challenge, when natural reproduction involves the more complex content, bug the easier it appears. The statement level, the current number of cases has been found in the copy would cause problems with MySQL, when the D ATA is modified to use some particular function or functions would occur when, for Example:sleep () function in some vers Ion can not is true copy, in the stored procedure used last_insert_id () function may cause the slave and the master ID and so on is inconsistent. The row level was based on each line to record the change, so no similar problem. 
Seen from the official documents, have been before the only MYSQL statement based replication mode, until the 5.1.5 version of Mysql started to support the row level replication. From 5.0, Mysql replication have been solved a lot of the old version can not appear in the correct copy of the issue. However, due to the emergence a stored procedure, a copy of Mysql brings even greater challenges. Also, see the official documents as the 5.1.8 version from the start, Mysql provides a Statement level and Row level tha n A third beyond the copy mode:mixed, is actually a combination of the first and both modes. In Mixed mode, Mysql based on the specific implementation of each SQL statement to distinguish between the log form of Tre Atment Records, that's, choose one between the Statement and Row. The new version of the statment level as before, only the implementation of the statement recorded. The new version of Mysql Squadron row level model have also been optimized to does, not all changes would bE to record the row level, as encountered when the table structure would change the mode to record the statement, if the sq L statement to does is update or DELETE statements modify data, etc., then the line would still record all the changes.

3, copy common framework
Mysql replication Environment is a more than 90% with one or more Master Slave architecture model, mainly used for reading The application of pressure for a low-cost expansion of the database-side solutions. Master and slave as long as the pressure is not a too great (especially the slave-side pressure), then asynchronous Replicat Ion is generally very little delay. Especially since the slave side of the replication processed into and processes, but also reduces the slave side of the de Lay. The benefits that real-time requirements for the data are not particularly sensitive to the application, just by cheap pc s Erver to expand the number of slave, would read the pressure distributed to multiple slave machines above, you can solve th E Database side Reading pressure bottleneck. This was in large-resolve the current pressure on many small and medium site ' s database bottlenecks, and even some Large sites is using similar programs to address the bottlenecks in the database. 
Master slave with a number of very simple implementation of the framework, a number of slave and implementation of a SINGL e slave, and there is not much difference. In the master does isn't care how many client connected to the master side slave, slave as long as the process of certificat Ion by the connection to the information he requested Binlog, he would connect up the IO process in accordance with the RE Quirements, read their own binlog information returned to the slave of the IO process. Configuration details for the slave, the official documents in the Mysql have been said above, very clearly, and even intro Duced a variety of methods to achieve the configuration of slave.

Mysql does the more than subordinate to the Master Slave instance of a structure. That's, a slave instance can only accept a master sync source, I heard a patch it can be improved features, and not PRA cticed. Mysql AB does not realized the reason what such a function, mainly on account of conflict resolution issues.

MySQL can be built to a dual master mode, which means that the both MySQL instance each other's master, also for the othe R side of the Slave. But the very most of this architecture are only one end of the service, to avoid conflicts. Even if both sides of the implementation of changes is on order, due to asynchronous replication mechanism to achieve the Same result even if the changes made in the evening could also be covered as early as the changes made, like the following S Cenario:
Time MySQL A MySQL B
An update is recorded as ten x table y
2 updates are recorded as X table y
3 A log and access to the application, update the x table y record is ten (does not meet expectations)
Get updated 4 x table y B log record is a (in line with expectations)
Thus, not only in the B library user data are not above the desired results, A and B also appeared on both sides of the DAT A inconsistency. Except under certain conditions can write separately in A and B fixed on both ends to ensure no cross-write, to is able to Avoid the above problems

MySQL replication principle--turn