MySQL performance tuning and Architecture design--13th: The MySQL Replication of extensible design

13th. The extensible design of MySQL Replication

Objective:

MySQL Replication is a very distinctive feature of MySQL, and he is able to copy the data from one MySQL server Instance to the Instance of another MySQL server. Although the replication process is not real-time but asynchronous, due to its efficient performance design, latency is very small. MySQL's replication function is widely used in practical applications to ensure the security of system data and the extensible design of the system. This chapter provides a detailed introduction to how to use MySQL's Replication functionality to improve the scalability of your system.

The significance of 13.1 Replication in the design of extensibility

In the Internet Application system, the most convenient extension may be the most basic WEB application services. Because Web application services are stateless in most cases, there is little need to save too much data, and of course, this kind of information is quite an exception. Therefore, it is easy for basic WEB application servers to scale out by simply adding servers and replicating applications.

And because of its special nature, the database is not so easily accessible as scale out. Of course, the database vendors have been trying to be able to do their own database software like a regular application server to do a convenient scale out, but also did make some functions, can basically realize the same as the Web Application server scalability, such as the logical replication capabilities supported by many databases. The
MySQL database has also made a great effort to do this, and the MySQL Replication feature is primarily based on this purpose. With MySQL's Replication function, we can easily copy data from one database to many MySQL hosts, compose a MySQL cluster, and then use this MySQL cluster to provide services externally. In this way, each MySQL host needs to bear the load will be greatly reduced, the entire MySQL cluster processing power is also easy to be improved.
Why is it possible to achieve scale out with MySQL Replication? The main reason is that with MySQL Replication, you can copy the data from one MySQL to the MySQL database on multiple hosts at the same time, and the delay of this replication is not very long. When we have the same data on each server, application access will no longer be able to read data on only one database host, but access the same data on any one of the hosts in the entire MySQL cluster. Another important factor is that MySQL replication is very easy to implement and very easy to maintain. This is very important to implement a simple distributed database cluster, after all, a system after the implementation of the work is mainly maintenance, a complex maintenance system is certainly not a popular system.
 
13.2 Replication mechanism implementation principle

To use a good system, understand its implementation principle is very important thing, only understand the principle of its implementation, we can avoid weaknesses, reasonable use, only to build the most suitable for our own application environment system, only after the implementation of the system to better maintain him.
Let's analyze the implementation principle of MySQL replication.

13.2.1 Replication Thread

MySQL Replication is an asynchronous copy process, copied from one MySQL instace (we call master) to another MySQL instance (we call it Slave). The entire replication process between Master and Slave is done primarily by three threads, of which two threads (SQL thread and IO thread) are on the Slave side, and another thread (IO thread) on the master side.
To implement MySQL Replication, you must first turn on the binary Log (mysql-bin.xxxxxx) function on the Master side, otherwise it will not be possible. 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 Binary Log for MySQL can be opened by using the "-log-bin" parameter option in the process of starting MySQL Server, or by adding "Log-bin" in the mysqld parameter group in the MY.CNF configuration file (the Parameters section after the [mysqld] identity) "Parameter item.

The basic process for MySQL replication is as follows:

1. Slave the above IO line thread attached the Master, and requests the log content from the specified location (or from the beginning of the log) to the designated log file;
2. When Master receives a request from an IO thread from Slave, the IO thread that is responsible for the replication reads the log information from the specified log at the specified location based on the requested information and returns the IO thread to the Slave side. In addition to the information contained in the log, the return information includes the name of the binary log file on the Master side of the returned information and its location in binary log;
3. After the Slave IO thread receives the information, it writes the received log content to the end of the Relay log file (mysql-relay-bin.xxxxxx) on the Slave side, And the read to the master side of the Bin-log's file name and location to 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. When the Slave SQL thread detects a new addition to the Relay log, it immediately parses the contents of the log file into those executable query statements that are executed at the Master end, and executes the query itself. In this way, the same Query is actually executed on the Master and Slave ends, so the data on both ends is exactly the same.
In fact, in the old version, the replication implementation of MySQL was done on the Slave side not by the two threads of the SQL thread and IO thread, but by a single thread to do all the work. But MySQL engineers quickly found that there were a lot of risks and performance issues, mainly as follows:
First of all, if the work is done independently by a single thread, it makes it possible to replicate the Binary log log on the Master side and parse the logs, and then perform this process as a serial process, and the performance is naturally subject to greater limitations, which The delay of the Replication is naturally longer.
Second, after the copy thread from the Slave side gets the Binary Log from the master, it needs to parse the content back into the original Query executed by the master and execute it on its own. In this process, the master side is likely to have generated a lot of changes and generated a lot of Binary Log information. If the storage system at the Master end of this stage fails to repair, all changes made at this stage will be lost forever and cannot be found again. This potential risk is particularly pronounced when the Slave is at a high pressure ratio, because if the Slave pressure is large, the time taken to parse the logs and apply them will naturally be longer, and more data may be lost.
Therefore, in the late transformation, the new version of MySQL in order to minimize this risk, and improve the performance of replication, the Slave end of the replication to two threads to complete, that is mentioned earlier SQL thread and IO thread. The first to propose this improvement is Yahoo! 's engineer, "Jeremy Zawodny". Through such transformation, this solves the performance problem to a large extent, shortens the asynchronous delay time and reduces the potential data loss.
Of course, even with the two threads that are now working together, there is also the possibility of Slave data latency and data loss, after all, the replication is asynchronous. These problems exist as long as the data changes are not in one transaction.

If you want to completely avoid these problems, you can only use the MySQL Cluster to solve. But the MySQL cluster know the author writes this part of the time, still is a memory database solution, namely need to all the data including index all Load into memory, so the memory requirements are very large, for the general popularity of the application is not too large to implement. Of course, when I first learned about MySQL's CTO David, MySQL is now constantly improving its Cluster implementation, and one of the big changes is that it allows the data to be not all load into memory, but just the index all load into memory, I believe in the completion of the change MySQL Cluster will be more popular and more likely to be implemented.

13.2.2 Replication Implementation level

MySQL replication can be based on a single statement (Statement level), or based on a record (Row level), you can set the configuration parameters in the MySQL replication levels, different replication level settings will affect the master side of the Binary Log Recorded in different forms.
1. Row level:binary log is 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, Binary log can not record the execution of the SQL statement context-sensitive information, only need to record that one record has been modified, what to change. 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 Binary log when recorded, which may result in a large amount of log content, such as an UPDATE statement: Update Group_message SET group_id = 1 Where group_id = 2, after execution, the log does not record the event that corresponds to the UPDATE statement (MySQL logs the Binary log log in the form of an event), but rather the change of each record updated by this statement. This is recorded as many events that are updated by a number of records. Natural
The amount of Binary log logs will be very large. Especially when executing statements such as Alter TABLE, the amount of log generated is staggering. Because MySQL handles DDL change statements such as ALTER table by rebuilding all the data in the entire table, i.e. every record in the table needs to be changed, every record of that table is recorded in the log.
2. Statement level: Each Query that modifies the data is recorded in the Binary log of master. Slave the SQL thread will parse the same Query executed by the Sing Woo original Master at the time of replication.
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 the Binary log log volume, save the IO cost, 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 emergence of stored procedures, the replication of MySQL brings a new challenge. In addition, the official documentation says that, starting with version 5.1.8, MySQL provides a third replication mode, in addition to the statement level and row level: The Mixed level is actually a combination of the first two modes. In mixed mode, MySQL differentiates the log form of the treated record according to each specific Query 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, as in the case of table structure changes will be recorded in statement mode, if the Query statement is really UPDATE or DELETE When you modify the data, the changes are recorded for all rows.

13.3 Replication Common Architecture

MySQL replicaion itself is a relatively simple architecture, that is, a MySQL server (Slave) from another MySQL server (Master) to log the replication and then parse the log and apply to itself. A replication environment requires only two hosts running MySQL server, or even simpler, we can start two mysqld instance on the same physical server host, one as Master and the other as slave to complete the replication environment. But in the actual application environment, we can use the MySQL Replication function according to the actual business demand to build a variety of other more advantageous to the scale out of the replication architecture. such as dual Master architecture, cascading replication architecture, and so on. Here are some of the more typical three replication architectures to do some of the corresponding analysis.

13.3.1 Regular replication schema (master-slaves)

In real-world scenarios, MySQL replication over 90% is a master copy to one or more slave schema patterns, primarily for low-cost, scalable solutions to database-side applications with large read-stress ratios. 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 changed to two thread processing, but also reduced the Slave end of the delay problem. The benefit is that the real-time requirements of the data is not particularly Critical application, only through the cheap PC server to expand the number of Slave, read the pressure spread to multiple Slave machines, Can solve the reading performance bottleneck of the database by dispersing the reading pressure of the single database server, after all, the reading pressure in most database application system is much higher than the writing pressure. 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.
This architecture can be clearly demonstrated by:

A master replication of multiple Slave implementations is very simple, and there is no substantive difference between multiple Slave and the implementation of a single Slave. On the Master side is not care how many Slave connected to their own, as long as there is Slave IO thread through the connection authentication, to request the binary log information after the specified location, he will follow the requirements of the IO thread, read his binary log information, return The IO thread to Slave.
It should be clear to everyone, from a master node can be copied out of multiple Slave nodes, some might think, that one Slave node can be copied from more than the master node? At least for the time being, MySQL is not going to make it, and it is unclear whether it will support it in the future.
MySQL does not support a Slave node from multiple Master nodes to replicate the schema, mainly to avoid the problem of conflict, to prevent data conflicts between multiple data sources, resulting in inconsistencies in the final data. But I've heard that someone has developed a patch that allows MySQL to support a Slave node to replicate from multiple Master nodes as data sources, which is the benefit of MySQL's Open source nature.

For Replication configuration details, in the official MySQL document has been very clear, and even introduced a variety of implementation of Slave configuration, in the next section we will also use a specific example to demonstrate the building of a Replication The detailed process of the environment and precautions.

13.3.2 Dual Master Replication schema (Master-master)

Sometimes, a simple copy from one MySQL to another MySQL basic Replication architecture, may also need to be in some specific scenarios in the Master switch. If you need to perform some special maintenance operations on the Master side, you may need to stop the MySQL service. In this case, in order to minimize the application write service downtime, the best way is to switch our Slave node to Master to provide the service of writing.
However, our original Master node's data will be inconsistent with the actual data. When the original master start can provide services normally, due to inconsistent data, we have to reverse the original Master-slave relationship, re-build the Replication environment, and the original master as Slave to provide read services. Re-building the Replication environment will bring us a lot of extra work, and if there is no proper backup, it may make the Replication process very cumbersome.
To solve this problem, we can avoid a lot of problems by building Dual Master environment. What is the dual Master environment? In fact, two MySQL Server each other as their Master, their own as the other Slave to replicate. In this way, changes made by either party are applied to the other party's database through replication.
Perhaps some readers will have a concern, so that after the replication environment, will not cause two MySQL between the replication? In fact, MySQL has already thought of this, so in MySQL's Binary log the current MySQL Server-id is recorded, and this parameter is also when we build MySQL Replication must be explicitly specified, and the Master and The Slave Server-id parameter value is inconsistent to make the MySQL Replication build successfully. Once you have the value of Server-id, it is easy for MySQL to tell which MySQL server A change originated from, so it is easy to avoid a recurring replication situation. And, if we don't open the option (--log-slave-update) of logging Slave's binary log, MySQL will never record the changes in the copy process to binary log, not to mention the possibility of a recurring replication scenario.
A clearer display of the Dual Master replication architecture consists of:

by Dual Master replication architecture, we are not only able to avoid the re-establishment of Replication environments due to the downtime required for normal routine maintenance operations, since either side of us records where we are currently replicating to each other, and when the system is up, will automatically start to restart replication from the previous location, without the need for any intervention, greatly saving maintenance costs.
not only that, the Dual master replication architecture, combined with some third-party HA management software, can also quickly automatically switch the other end to provide the service, reducing the downtime caused by exceptional situations, after the exception of the master that we are currently using fails to provide services. And there is no need for human intervention at all.
Of course, we built a Dual Master environment, not for both ends to provide write services. Under normal circumstances, we will only open one end of the write service, the other end is just to provide read services, or completely do not provide any services, just as a spare machine exists. Why do we generally only open one end of it to provide write service? The main purpose is to avoid data conflicts and to prevent the inconsistency of data. Because Replication is an asynchronous implementation mechanism even if the modifications executed on both sides are sequential, it can also result in even late modifications being overwritten by earlier modifications, as in the following scenario:

Point in time	MySQL A	MySQL B
1	Update x table y records to 10
2		Update x table y records to 20
3		Get to a log and apply, update the Y record of the X table to 10 (not meeting expectations)
4	Get B log update x table y record 20 (meet expectations)

In this case, not only the data above the B-Library is not what the user expects, but the data on both sides of A and B are inconsistent.

Of course, we can also through the Special Convention, let some table write operation all at one end, and some other table writes all at the other end, guarantees the two sides not to operate the same table, thus can avoid the above problem occurrence.

13.3.3 Cascade Replication Architecture (Master-slaves-slaves ...)

In some applications, it may be that the pressure difference between reading and writing is large, the reading pressure is particularly large, and a Master may need 10 or more Slave to support the pressure of the reading. At this time, master will be more difficult, because only the Slave IO thread is more, so write a little bit more pressure, the Master side because replication will consume more resources, it is easy to cause replication delay.
How to solve this problem? At this point we can use MySQL to record the ability to copy Binary log information from the Slave side, that is, to turn on the-log-slave-update option. Then, a two-level (or more-level) copy is required to reduce the stress on the Master side as a result of replication. That is, we first replicate from Master through a handful of MySQL machines, which we would call the first level Slave cluster, and then the other Slave to replicate from the first level Slave cluster. From the first level Slave to replicate the Slave, I call it the second level Slave cluster. If necessary, we can continue to add more layers of replication. This makes it easy for us to control the number of Slave attached to each MySQL. This architecture, which I call the master-slaves-slaves architecture, is an easy solution to the risk of the Master side becoming a bottleneck because of the fact that the secondary Slave is too many. Shows the Replication architecture for multi-level replication.

Of course, if the conditions allow, I would prefer to recommend that you split into multiple Replication clusters to solve the above bottleneck problem. After all, Slave did not reduce the amount of write, all Slave actually still applied all the data change operations and did not reduce any write IO. Conversely, the more Slave, the greater the total amount of write IO for the entire cluster, the more we don't have a very obvious feeling, simply because it's scattered over multiple machines, so it's not easy to show.
In addition, increasing the cascade level of replication, the same change to the bottom of the Slave need to pass the MySQL will also be more, the same may cause a longer delay risk.
And if we were to solve it by splitting the cluster, it would probably be much better, and of course, a split cluster would require more complex technologies and more complex application architectures.

13.3.4 Dual Master in conjunction with cascading replication architecture (master-master-slaves)

cascading replication, to a certain extent, does solve the problem that Master has become a bottleneck because of the excessive number of Slave attached to it, but he does not solve the problem of manual maintenance and the possibility of re-building Replication after an exception needs to be switched. This naturally brings out the Dual Master and Cascade replication Replication architecture, which I call the master-master-slaves architecture and master-slaves-slaves architecture, the difference is just the first level The Slave cluster is replaced with a separate master, as the standby master, and then copied from this alternate master to a Slave cluster. The following image shows a clearer picture of how this architecture is composed:

The great benefit of this Dual master-to-cascade replication architecture is that the primary master's write operations are not affected by the replication of the Slave cluster, while the primary master does not have to be re-Replication in the event of a switchover. However, this architecture also has a disadvantage, that is, the backup master may become a bottleneck, because if the subsequent Slave cluster is larger, the standby master may be a bottleneck because of excessive Slave IO thread requests. Of course, the standby master does not provide any read service, the bottleneck is not particularly high probability, if there is a bottleneck, you can also re-cascade replication after the standby master, set up a multilayer Slave cluster. Of course, the more levels of Cascade replication, the more noticeable the data latency that may occur in the Slave cluster, so before considering multi-level replication, you need to evaluate the impact of the data latency on the application system.

13.4 Replication Construction realizes

MySQL replication environment is relatively simple to build, in general, is actually four steps, the first step is to do the master side of the preparation work. The second step is to get a "snapshot" backup of the Master end data. The third step is to restore the backup "snapshot" of Master on the slave side. The fourth step is to set the Master-related configuration on the Slave side and start replication. In this section, it is not a detailed process to set up a Replication environment, as this is already described in more detail in the MySQL official operating manual, and I mainly aim to introduce the various implementation methods that can be used in several major operational steps in the setup environment, Here we have a simple analysis of the four-step operation and the areas needing attention.

1. Master-side prep work
Before building the Replication environment, the first thing to ensure that the Master side MySQL record binary log option to open, because the MySQL Replication is through binary log to achieve. Let Master side MySQL record Binary log can use the-log-bin option when starting MySQL Server or configure Log-bin[=path for Binary log in MySQL configuration file my.cnf ] parameter options.
After the log Binary log function is turned on, we also need to prepare a MySQL user for replication.

You can either grant replication-related permissions to an existing account, or you can create a brand-new account that is dedicated to replication.

Of course, I recommend that you replicate with an account that is dedicated to replication. In the previous section, "MySQL Security Management" has also been introduced, to deal with specific categories of work through specific accounts, whether in terms of security policy is more advantageous, for maintenance is also more convenient. Implementing MySQL Replication only requires "Replication SLAVE" permissions. You can create this user in the following ways:
[email protected]: MySQL 04:16:18> CREATE USER ' repl ' @ ' 192.168.0.2 '
-Identified by ' password ';
Query OK, 0 rows Affected (0.00 sec)
[email protected]: MySQL 04:16:34> GRANT REPLICATION SLAVE on * *
To ' repl ' @ ' 192.168.0.2 ';
Query OK, 0 rows Affected (0.00 sec)
This first creates a user repl with only the most basic permissions through the Create User command, and then grants the user REPLICATION SLAVE permissions through the grant command. Of course, we can simply execute the second command above to create the user we need, as described in the MySQL Security Management Section.

2. Get Backup "snapshot" on Master side
The backup "snapshot" of the Master side is not specifically defined by the snapshot of software such as LVM, but rather the backup set that all data is based on a particular moment of data integrity and consistency can be guaranteed. It is also necessary to obtain the exact log Position of the Master-side Binary log at the time of the backup set, as it will be used when configuring Slave later. In general, we can get a consistent and complete backup set and the corresponding Log Position by the following centralized approach:

Cold backup via Database Full library
For a database that can be shut down, we can get the most complete backup set by shutting down the master MySQL and then copy all the data files and log files to the appropriate location in the host where Slave is needed. After the backup is done, then start MySQL on the Master side.
Of course, we just get a backup set that satisfies the requirements, and we also need the log location for that backup set to be available. For such a backup set, there are several ways to get to the corresponding log location. If, after Master has just started, there is no application connected to master, get the Log Position that we can use by executing the show Master STATUS command from the master side. If we are unable to control the connection of the application after Master boot, then we may have data written before we have time to execute the show Master STATUS command, so we can parse the master through the Mysqlbinlog client program. The latest Binary log to get its first valid log Position. Of course, if you are very aware of the MySQL version you are using, each new Binary log is the first valid log location, and you will not need to do anything.

"Hot backup" with snapshot-enabled software such as LVM or ZFS

If our Master is a database that needs to meet the 365 * 24 * 7 service, we will not be able to get the required backup set by taking a cold backup. This time, if our MySQL runs in support of

Snapshot function of the file system above (such as ZFS), or our file system does not support Snapshot, but our file system is running on the LVM, then we can use the relevant command to the MySQL data files and log files in the same directory as a Snapshot, so that you can get a basic and a full library cold similar backup set.
Of course, in order to ensure that our backup set data is complete and consistent, we need to lock all table writes with the associated command (FLUSH TABLES with READ Lock) during the snapshot process, and also include the commit action in the storage engine that supports the transaction. This will ensure that all the data in the snapshot is fully consistent. After finishing the Snapshot, we can UNLOCK TABLES. Some people may worry that if we lock down all the write operations, then our application will not be able to provide write service? Sure, this is unavoidable, but generally the time required for Snapshot operations is usually shorter, so it does not affect too much time.
What about the Log Position? Yes, by Snapshot Backup, you also need a Log Position for that backup to meet the requirements for building a Replication environment. However, in this way, we can get to the corresponding Log Position more easily than a cold backup. Because the database cannot perform any write operations from the start of a write operation that locks all the tables to the unlock, the exact Log Position can be obtained at any time within this time period by executing the SHOW MASTER STATUS.
Because this approach does not need to completely stop the Master in the implementation process, just to stop writing to do, so we can also call it "hot backup."

Through the Mysqldump client program

If our database is not shut down for cold backups, and MySQL is not running on a file system or management software that can be snapshot, then we need to dump the data from the database (or table) that the master side needs to replicate by using the Mysqldump tool. In order for our backup set to be consistent and complete, we have to keep the process of the dump data in the same transaction, or lock the write operations of all tables that need to be replicated. To do this, if we are using a transaction-enabled storage engine (such as InnoDB), we can do so by adding the-single-transaction option when executing the MYSQLDUMP program, but if our storage engine does not support transactions, or if we need to When the dump table has only partially supported transactions, we can only pause all write services with the FLUSH TABLES with READ LOCK command before dump the data. Of course, if we just need to dump a table of data, it does not need to be so troublesome, because the mysqldump program when the dump data is actually each table through a SQL to get the data, so a single table can always guarantee the consistency of the data taken.

The above operation we have just obtained the appropriate backup set, there is no corresponding to the backup set of the Log Position, so not fully meet the requirements of building Slave. Fortunately, the developer of the Mysqldump program has long considered this problem, so the mysqldump program added another parameter option to help us get to the corresponding Log Position, this parameter option is-master-data. When we add this parameter option, Mysqldump generates a change MASTER to command in the dump file, which records the detailed log Position information for the dump moment. As follows:
Test the Group_message table under the dump example database:
[Email protected]:~$ mysqldump--master-data-usky-p example Group_message >
Group_message.sql
Enter Password:
Then look through the grep command to find out:
[Email protected]:~$ grep "Change MASTER" Group_message.sql
Change MASTER to master_log_file= ' mysql-bin.000035 ', master_log_pos=399;
Even change MASTER to the command has been prepared for us, is really thoughtful, hehe.
If we were to dump multiple tables supporting transactions at once, many people might choose to add the-single-transaction option to ensure data consistency and integrity. This is indeed a good choice.
However, if we need to dump a large amount of data, it may produce a large transaction, and will last for a long time.

"Hot backup" via one of the existing Slave ends

This backup set is very easy to obtain if there are already Slave copied from the Master that we need to build the Replication environment. We can temporarily stop the existing Slave (if there are more than one), and execute the Flush TABLES command once to refresh all the table and index data. At this time there will be no more write operations on the Slave, we can either copy all the data files and log files to make a full backup, but also through the Snapshot (if supported) to the backup. Of course, if you support the Snapshot function, it is recommended that you do it through Snapshot, because this can make the Slave stop copying time greatly shortened, reduce the Slave data delay.
Through the existing Slave to get backup set way, not only to get the database backup way is very simple, even the need to Log Position, even the new Slave configuration and other related actions can be omitted, only need the new Slave completely based on this backup set to start, you can normal from Master is copied.
In the process, we just stop the replication thread of an existing Slave in a short period of time and have little impact on the normal service of the system, so this approach can also basically be called "hot backup".

3. Restore the Backup "snapshot" via the Slave side

In the second step, we've got the backup set we need, and all we need to do is restore the backup set from the previous step to MySQL on our Slave side.
There are also differences in recovery operations on the Slave side, depending on the backup set obtained from the previous four methods. Here is a simple explanation for the recovery of four backup sets:

Recovering a full-pool cold backup Set
Since this backup set is a complete physical backup of the database, we just need to copy this backup set via FTP or the network transfer software such as SCP to the host where Slave resides, according to the settings of my.cnf configuration file on Slave, the file is stored in the corresponding directory. Overwrite all existing data and logs and other related files, and then start the Slave end of MySQL, completed the entire recovery process.
Restore the backup set Snapshot to Master
For the backup set that is obtained by Snapshot the Master, it is essentially the same as the recovery method of the whole library cold, the only difference is that the Snapshot needs to be mount to a directory through the corresponding file system before subsequent file copy operations can be performed. After the related operation and restore the whole pool cold backup set basically consistent, no longer be described.

Restore the backup set mysqldump get

The backup set obtained through the MYSQLDUMP client program is significantly different from the previous two backup set recovery methods. Because all of the previous two backup sets belong to a physical backup, the backups made through the Mysqldump client program are logical backups. The way to restore the mysqldump backup set is to execute all the SQL statements in the backup file through the MySQL client program.
Before using the MySQL client program to recover from the Slave side, it is recommended that you copy the Change master to command section through-master-data, and then log off the part in the backup file before recovering.

Because the command is not a complete change MASTER to command, the statement cannot be completed effectively if the three parameters of Master_host,master_user,master_password are not configured in the configuration file (MY.CNF).
The way to restore backups through the MySQL client program is as follows:
[Email protected]:~$ mysql-u sky-p-dexample < Group_message.sql
This restores the logical backup set previously made by the Mysqldump client program to the database.

Recovery of hot backups from existing Slave

The backup set obtained through the existing Slave is similar to the first or second backup set above. If it is the backup set that is obtained by copying the data and log files directly, then the same way as the whole library cold, if it is the backup set obtained through Snapshot, it is identical to the second method of backup recovery.

4. Configure and start Slave

After completing the previous three steps, the Replication environment will only need a final step, which is to configure and then start Slave with the change MASTER to command.
The change MASTER to command requires a total of 5 items to be set, respectively:
The hostname (or IP address) of the master_host:master;
Master_user:slave the user name of the connection Master, which is actually the REPL user created previously;
Master_password:slave the password of the user connecting to Master;
Master_log_file: The name of the log file to begin copying;
Master_log_pos: The location of the log file to begin copying, that is, the log Position that was consistently mentioned in the previous introduction of the backup set process.

The following is an example of a complete change MASTER to command:
Change MASTER to
[email protected]: MySQL 08:32:38> change MASTER to
Master_host= ' 192.168.0.1 ',
Master_user= ' Repl ',
Master_password= ' PASSWORD ',
Master_log_file= ' mysql-bin.000035 ',
master_log_pos=399;
After executing the change MASTER to command, you can start SLAVE with the following command:
[email protected]: MySQL 08:33:49> START SLAVE;
At this point, our Replication environment is built. Reader friends can do their own tests to try to build, if you need to understand the MySQL replication build process More detailed steps, you can check the official MySQL manual.

13.5 Summary

In practical scenarios, MySQL Replication is one of the most widely used design tools to improve system scalability. Many MySQL users through the Replication function to enhance the expansion of the system, through the simple increase in inexpensive hardware equipment multiplied even to increase the number of levels of the original system performance, is the majority of MySQL low-end users of the most favorite feature, is also a large number of MySQL One of the most important reasons for users to choose MySQL.

Excerpt from: "MySQL performance tuning and architecture design" Jane Chaoyang

Reprint please specify the source:

Jesselzj
Source: http://jesselzj.cnblogs.com

MySQL performance tuning and architecture design-13th. Extensible Design MySQL Replication