MySQL performance tuning and Architecture Design-Chapter 1 Performance Design-MySQL Replication and replication Optimization

MySQL performance tuning and Architecture Design-Chapter 1 Performance Design-MySQL Replication and replication Optimization

Chapter 1 scalability design-MySQL Replication

Preface:

MySQL Replication is a unique feature of MySQL. It can completely copy data from one MySQL Server Instance to another MySQL Server Instance. Although the replication process is not implemented in real time but asynchronously, the latency is very low due to its efficient performance design. The Replication function of MySQL is widely used in practical application scenarios to ensure the security of system data and the scalable design of the system. This chapter describes how to use the Replication function of MySQL to improve system scalability.

13.1 significance of Replication for the design of scalability

In Internet application systems, the most convenient extension is the most basic Web application service. In most cases, Web application services are stateless and seldom need to store too much data. Of course, Session information is an exception. Therefore, it is easy for basic Web application servers to Scale Out by simply adding servers and copying applications.

Because of its special nature, the database is not so easy to Scale Out. Of course, various database manufacturers have been striving to make their own database software as convenient as the conventional application server, and have indeed made some functions, scalability is basically the same as that of Web application servers, such as logic replication supported by many databases.
MySQL Databases have made great efforts for this purpose. The MySQL Replication function is mainly based on this purpose. Through the Replication function of MySQL, we can easily copy data from a database to many MySQL hosts, form a MySQL cluster, and then provide external services through this MySQL cluster. In this way, the load of each MySQL host is greatly reduced, and the processing capability of the entire MySQL cluster is also easily improved.
Why can I Scale Out through MySQL Replication? The main reason is that through MySQL Replication, the data in one MySQL can be completely copied to the MySQL database on multiple hosts at the same time, and the Replication delay is not very long under normal circumstances. When we have the same data on each server, application access will no longer be able to read data only on one Database Host, instead, access the database on any host in the MySQL cluster to obtain the same data. Another important factor is that MySQL replication is very easy to implement and maintain. This is very important for implementing a simple distributed database cluster. After all, a system is mainly maintained, and a complicated system is certainly not a popular system.
 
13.2 Implementation principle of the Replication mechanism

To use a system, it is very important to understand its implementation principles. Only by understanding its implementation principles can we develop strengths, circumvent weaknesses, and make rational use of it, in order to build a system that best suits our own application environment, we can better maintain the system after implementation.
Next we will analyze the implementation principles of MySQL Replication.

13.2.1 Replication thread

Mysql Replication is an asynchronous Replication process, from one Mysql instace (we call it the Master) to another Mysql instance (we call it the Slave ). The entire replication process between the Master and Slave is mainly completed by three threads, two of which (SQL thread and IO thread) are on the Slave side, and the other (IO thread) on the Master side.
To implement Replication for MySQL, you must first enable the Binary Log (mysql-bin.xxxxxx) function on the Master side, otherwise it cannot be implemented. The whole replication process is actually because Slave obtains the log from the Master end and then executes the operations recorded in the log in full order on itself. To enable the Binary Log of MySQL, you can use the "-log-bin" parameter option during MySQL Server startup, or. the "log-bin" parameter item is added to the mysqld parameter group (the parameter section marked by [mysqld]) in the cnf configuration file.

The basic process of MySQL replication is as follows:

1. The IO thread on the Slave connects to the Master, and requests the log content after the specified location of the specified log file (or from the beginning of the log;
2. After the Master receives a request from the Slave IO thread, it reads the log information at the specified location of the specified Log Based on the Request Information and returns it to the Slave IO thread. In addition to the information contained in the Log, the returned information also includes the name of the Binary Log file on the Master end and its location in the 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) at the Slave end in sequence, and record the file name and location of the bin-log on the Master end to the master-info file, so that the next read can clearly show the High-Speed Master "I need to start from the location of a bin-log, please send it to me"
4. after the Slave SQL thread detects the newly added content in the Relay Log, it will immediately parse the content in the Log file into the executable Query statements during actual execution on the Master end, and execute these queries on your own. In this way, the same Query is actually executed on the Master and Slave ends, so the data at both ends is exactly the same.
In fact, in the old version, MySQL replication implementation on the Slave end is not completed by collaboration between the SQL thread and the IO thread, instead, a single thread is used to complete all the work. However, MySQL engineers quickly discovered that there were great risks and performance problems, mainly as follows:
First, if you use a single thread to independently implement this operation, you can copy the Master end, Binary Log, and parse the logs, then, the execution of this process becomes a serial process, and the performance will naturally be greatly limited. The Replication delay in this architecture is naturally longer.
Second, after the replication thread on the Slave side obtains the Binary Log from the Master side, it needs to parse the content, restore it to the original Query executed by the Master side, and then execute it on its own. In this process, the Master end may have produced a lot of changes and generated a lot of Binary Log information. If the Master-side storage system encounters an irreparable fault at this stage, all changes made at this stage will be lost forever and cannot be recovered. This potential risk is especially prominent when the Slave side is under great pressure, because if the Slave is under great pressure, it takes longer to parse logs and apply these logs, more data may be lost.
Therefore, in the later transformation, in order to minimize this risk and improve the replication performance, the new version of MySQL will replace the replication on the Slave end with two threads, that is, the SQL thread and IO thread mentioned above. Yahoo! was the first to propose this improvement solution! Jeremy Zawodny, an engineer in the project ". This transformation not only solves the performance problem to a large extent, shortens the asynchronous delay time, but also reduces the potential data loss.
Of course, even after two threads are switched to the current mode for collaborative processing, there is still the possibility of Slave Data latency and data loss. After all, this replication is asynchronous. As long as data changes are not in a transaction, these problems exist.

To completely avoid these problems, you can only use the MySQL Cluster to solve them. However, when I write this part of MySQL Cluster, it is still a memory database solution, that is, to Load all data, including indexes, to the memory, in this way, the memory requirements are very large, and the implementation of general popular applications is not too large. Of course, when talking with MySQL's CTO David, I learned that MySQL is constantly improving its Cluster implementation. One of the major changes is to allow data not to be fully loaded into the memory, I only want to Load all the indexes into the memory. I believe that after the transformation, the MySQL Cluster will be more popular and more feasible.

13.2.2 replication implementation level

MySQL replication can be based on a Statement (Statement Level) or a Row level. You can set this Level in MySQL configuration parameters, the setting of different replication levels will affect the Binary Log records on the Master end into different forms.
1. Row Level: the Binary Log records the modified form of each Row of data, and then modifies the same data on the Slave end.
Advantage: in Row Level mode, Binary Log does not record the context-related information of the executed SQL statement. You only need to record the record that has been modified and changed to what kind of information. Therefore, the Row-Level log records the details of each Row of data modification, which is easy to understand. In addition, stored procedures, functions, and trigger calls and triggers cannot be correctly copied in certain situations.
Disadvantage: in Row Level, when all statements executed are recorded in Binary Log, they are all recorded with modifications recorded in each line. This may produce a large amount of Log Content, for example, there is an update statement: UPDATE group_message SET group_id = 1 where group_id = 2. After execution, what is recorded in the Log is not the event corresponding to this update Statement (MySQL records Binary Log logs in the form of events), but the change of each record updated by this statement, in this way, many events are recorded and updated. Nature,
The amount of Binary Log is large. Especially when statements such as alter table are executed, the log volume generated is astonishing. Because MySQL processes DDL change statements such as alter table by recreating all data in the entire TABLE, that is, every record in the TABLE needs to be changed, each record of the table is recorded in the log.
2. Statement Level: each Query that modifies data is recorded in the Binary Log of the Master. When the Server Load balancer replicates data, the SQL thread parses the same Query that was previously executed by the Master to execute the statement again.
Advantages: Statement Level first solves the disadvantages of Row Level and does not need to record the changes of each Row of data. This reduces the Binary Log volume and saves I/O costs, improved performance. Because it only needs to record the details of the statements executed on the Master and the context information during statement execution.

Disadvantage: because it is a recorded execution statement, in order to make these statements run correctly on the slave end, it must also record some information about each statement during execution, that is, the context information to ensure that all the statements can get the same results as those executed on the master node when the slave terminal cup is executed. In addition, because Mysql is developing rapidly and many new features are constantly added, mysql replication has encountered great challenges. The more complex the content involved during natural replication, the more common the bug is. In statement level, many cases have been found to cause mysql replication problems, mainly when some specific functions or functions are used for data modification, for example, the sleep () function cannot be copied in some versions, and the last_insert_id () function is used in the stored procedure, which may lead to an inconsistent id between the slave and the master. Since the row level records changes based on each row, no similar problems will occur.
From the official documents, we can see that MySQL has only been in Statement-based replication mode until MySQL 5.1.5 starts to support Row-Level replication. Since MySQL 5.0, MySQL replication has solved the problem that many old versions cannot be copied correctly. However, the emergence of stored procedures poses a new challenge to MySQL replication. In addition, according to the official documentation, MySQL has provided the third replication mode except Statement Level and Row Level since version 5.1.8: Mixed Level, which is actually the combination of the first two modes. In Mixed mode, MySQL will differentiate the log format of the Record Based on each specific Query Statement executed, that is, select one between Statement and Row. The Statment level in the new version is the same as before, and only the statements executed are recorded. The Row Level mode of the new Mysql squadron has also been optimized. Not all modifications are recorded by Row Level. For example, when the table structure is changed, it will be recorded in statement mode, if the Query statement is indeed a statement that modifies data such as UPDATE or DELETE, changes to all rows will be recorded.
 
13.3 common Replication Architecture

MySQL Replicaion is a simple architecture, that is, a MySQL Server (Slave) replicates logs from another MySQL Server (Master), then parses the logs and applies them to itself. In a replication environment, only two hosts running MySQL Server are required. In a simpler environment, we can start two mysqld instances on the same physical Server host, one is the Master and the other is the Slave to build the replication environment. However, in the actual application environment, we can use the MySQL Replication function to build other Replication architectures that are more conducive to Scale-Out based on actual business needs. Such as Dual Master architecture and cascade replication architecture. Next, we will analyze and introduce the three typical replication architectures.

13.3.1 General replication architecture (Master-Slaves)

In practical application scenarios, MySQL replication over 90% is an architecture mode where one Master node is replicated to one or more Slave instances. It is mainly used for database Expansion Solutions for applications with high read pressure. As long as the pressure on the Master and Slave is not too high (especially on the Slave end), the latency of asynchronous replication is usually very small. Especially since the replication method on the Slave end is changed to two threads for processing, the delay Problem on the Slave end is reduced. The benefit is that the real-time data requirements are not especially Critical. You only need to use a low-cost pc server to expand the number of Slave and distribute the read pressure to multiple Slave machines, the read performance bottleneck of the database can be solved by dispersing the read pressure of a single database server. After all, the read Pressure in most database application systems is much higher than the write pressure. This solves the database pressure Bottlenecks of many small and medium-sized websites to a large extent, and even some large websites are using similar solutions to solve database bottlenecks.
This architecture can be clearly presented:

The architecture implementation of one Master node to copy multiple Slave instances is very simple. There is no substantial difference between the implementation of multiple Slave instances and a single Slave. On the Master end, the Master does not Care how many Slave instances are connected to itself. As long as the Slave IO thread passes connection authentication, it requests the Binary Log information after the specified location to him, it will read its Binary Log information according to the requirements of the IO thread and return it to the Slave IO thread.
It should be clear that multiple Slave nodes can be copied from a Master node. Some may wonder if the Slave node can be copied from multiple Master nodes? At least for the time being, MySQL cannot be achieved. It is unclear whether MySQL will support MySQL in the future.
MySQL does not support the replication architecture of a server Load balancer node from multiple Master nodes, mainly to avoid conflicts and prevent data conflicts between multiple data sources, as a result, the last data is inconsistent. However, I have heard that some related patches have been developed to allow MySQL to support the replication of a Slave node from multiple Master nodes as data sources. This is also the benefit of the open source nature of MySQL.

For details about the configuration of Replication, I have already made it very clear in the official MySQL documentation, and even introduced a variety of Slave configuration methods, in the next section, we will also use a specific example to demonstrate the detailed process and precautions for building a Replication environment.

13.3.2 Dual Master replication architecture (Master-Master)

Sometimes, the basic Replication architecture of simply copying data from one MySQL to another MySQL may also require Master switching in some specific scenarios. For example, you may need to stop the MySQL service when you need to perform some special maintenance operations on the Master. At this time, in order to minimize the downtime of the application system write service, the best practice is to switch our Slave node to the Master to provide the write service.
However, the data on the original Master node will be inconsistent with the actual data. When the original Master is started to provide services normally, due to data inconsistency, we had to reverse the original Master-Slave relationship and re-build the Replication environment, and use the original Master as the Slave to provide external read services. Re-building the Replication environment will bring us a lot of extra work. If there is no proper backup, it may make the Replication setup process very troublesome.
To solve this problem, we can avoid many problems by setting up the Dual Master environment. What is the Dual Master environment? In fact, the two MySQL servers use each other as their own Master and copy each other as their own Slave. In this way, any change made by either party will be copied and applied to the database of the other party.
Some readers may have a worry. After the replication environment is set up, won't circular replication between two MySQL instances? In fact, MySQL has long thought of this, so the current MySQL server-id is recorded in the MySQL Binary Log, and this parameter must be explicitly specified when we set up MySQL Replication, in addition, the server-id parameter values of Master and Slave must be different in order to successfully build MySQL Replication. Once the value of server-id is available, MySQL can easily determine the MySQL Server from which a change was originally generated, so it is easy to avoid loop replication. Moreover, if we do not enable the -- Log-Slave-update option for logging the slave Binary log, MySQL will not record the changes in the replication process to the Binary Log, you don't have to worry about the possibility of loop replication.
The following figure shows the Dual Master replication architecture:

With the Dual Master Replication architecture, we can not only avoid the re-building of the Replication environment due to the downtime required by normal routine maintenance operations, because all of us have recorded the location where we are copying data to the other party, when the system is up, it will automatically start copying again from the previous location, without human intervention, the maintenance cost is greatly reduced.
In addition, the Dual Master replication architecture is combined with some third-party HA management software. It can also be achieved after the Master we are currently using has an exception and cannot provide services, quickly switch the other end to provide corresponding services, reducing the downtime caused by exceptions, and requiring no manual intervention.
Of course, we build a Dual Master environment, not to provide write services at both ends. Under normal circumstances, we will only enable the write service for one end. The other end only provides the read service, or does not provide any service at all, but only serves as a backup machine. Why do we usually only open one end to provide the write service? The main purpose is to avoid data conflicts and avoid data inconsistency. Because Replication is an asynchronous implementation mechanism, even if the modifications executed on both sides are sequential, the modifications made later may be overwritten, it is like the following situation:

Time Point	MySQL	MySQL B
1	Update the y record of Table x to 10.
2		Update the y record of Table x to 20
3		Obtain and apply log A, and update the y record of Table x to 10 (not as expected)
4	Retrieve B log update x table y record is 20 (as expected)

In this case, not only is the data on Database B not the expected result, but the data on both sides of database A and database B is also inconsistent.

Of course, we can also use special conventions to make write operations on some tables all at one end, while write operations on some other tables all at the other end, so that the two ends do not operate on the same table, in this way, the above problem can be avoided.

13.3.3 cascade replication architecture (Master-Slaves ...)

In some application scenarios, the read/write pressure may be significantly different, And the read pressure may be extremely high. A Master may need 10 or more server load balancers to support the read injection pressure. At this time, the Master will be more difficult, because there are more Slave IO threads connected only, so when the write pressure is a little higher, the Master node consumes a lot of resources because of replication, which can easily lead to replication latency.
How can this problem be solved? At this time, we can use MySQL to record the changed Binary Log information generated by replication on the Slave side, that is, enable the-log-slave-update option. Second-level (or more) replication reduces the pressure on the Master node due to replication. That is to say, we first use a few MySQL instances to replicate from the Master. We call these machines the first-level Slave cluster, then, other Slave instances are copied from the first-level Slave cluster. The server Load balancer instance that replicates data from the first-level Server Load balancer instance, which is called the second-level Server Load balancer cluster. If necessary, we can continue to add more levels of replication. In this way, we can easily control the number of Slave attached to each MySQL. This architecture is called the Master-Slaves architecture. This multi-layer cascade replication architecture easily solves the risk of the bottleneck caused by the large number of Slave instances on the Master end. Demonstrate the Replication architecture of multi-layer cascade Replication.

Of course, if conditions permit, I would suggest you split the cluster into multiple Replication clusters to solve the above bottlenecks. After all, Slave does not reduce the write volume, and all Slave actually applies all data change operations without any write IO reduction. On the contrary, the more Slave, the more I/O writes to the entire cluster. We do not have a very obvious feeling. It is just because it is scattered across multiple machines, so it is not very easy to express.
In addition, the cascade level of replication is increased, and the same change is required to pass to the bottom-layer Slave, which may lead to a longer delay.
If we solve this problem by splitting the cluster, it may be much better. Of course, splitting the Cluster also requires more complex technologies and more complex application system architecture.

13.3.4 Dual Master and cascade replication architecture (Master-Slaves)

To a certain extent, cascade replication solves the bottleneck caused by the excessive number of Slave instances attached to the Master, however, it cannot solve the problem of manual maintenance and abnormal switchover. This naturally extends the Replication architecture that combines Dual Master with cascade Replication. I call it the Master-Slaves architecture and the Master-Slaves architecture, the difference is that the first-level Slave cluster is replaced by a separate Master, which serves as a backup Master, and then copied from the backup Master to a Slave cluster. The image below clearly shows the composition of this architecture:

This architecture combines Dual Master with cascade replication. The biggest benefit is that it can avoid the impact of write operations on the Master from the replication of the Slave cluster, at the same time, when the Master needs to be switched, Replication will basically not be redeployed. However, this architecture also has a drawback, that is, the Standby Master may become a bottleneck, because if the subsequent Slave cluster is large, the standby Master may become a bottleneck due to excessive Slave IO thread requests. Of course, when the standby Master does not provide any read services, the bottleneck may not be very high. If a bottleneck occurs, you can perform cascade replication again after the standby Master, build a multi-layer Slave cluster. Of course, the more levels of cascade replication, the more obvious the data delay that may occur in the Slave cluster. Therefore, before using cascade replication, you also need to evaluate the impact of data delay on the application system.
 
13.4 build and implement Replication

The establishment and implementation of the MySQL Replication environment is relatively simple. In general, there are actually four steps. The first step is to prepare the Master. The second step is to obtain the "snapshot" backup of the Master data. The third step is to restore the Master's backup "snapshot" on the Slave side ". Step 4 is to configure the Master on the Slave side, and then start replication. This section does not list a detailed process for building a Replication environment, because it has been described in more detail in the MySQL official operation manual, I will mainly introduce the implementation methods that can be used in several major operation steps in the environment. Below we will perform a simple analysis on these four steps and the areas needing attention.

1. Master preparation
Before setting up the Replication environment, you must first enable the option of recording Binary Log on the MySQL on the Master end, because MySQL Replication is implemented through Binary Log. Enable MySQL on the Master end to record Binary logs. You can use the-Log-bin option when starting MySQL Server or use the MySQL configuration file my. configure the log-bin [= path for binary log] parameter option in cnf.
After enabling the Binary Log record function, we also need to prepare a MySQL user for replication.

You can grant copy-related permissions to an existing account or create a new account dedicated to replication.

Of course, we recommend that you use an account dedicated to replication for replication. I have already introduced the "MySQL security management" section. Using a specific account to process a specific type of work is more advantageous in terms of security policies, it also provides greater convenience for maintenance. To implement MySQL Replication, you only need the "replication slave" permission. You can create this user as follows:
Root @ localhost: mysql 04:16:18> create user 'repl' @ '2017. 168.0.2'
-> Identified by 'Password ';
Query OK, 0 rows affected (0.00 sec)
Root @ localhost: mysql 04:16:34> grant replication slave on *.*
-> TO 'repl' @ '192. 168.0.2 ';
Query OK, 0 rows affected (0.00 sec)
The create user command is used to CREATE a USER repl with only the most basic permissions, and then GRANT the replication slave permission to the USER through the GRANT command. Of course, we can also simply execute the second command above to create the users we need. This has been introduced in the "MySQL security management" section.

2. Obtain the backup "snapshot" of the Master"
Here, the backup "snapshot" on the Master end is not a snapshot made by software such as LVM, but all data is based on a specific time, data integrity and consistency can be ensured by the backup set. At the same time, you also need to obtain the accurate Log Position of the Master-side Binary Log corresponding to the backup set time, because it will be used later When configuring Slave. In general, we can obtain a consistent and complete backup set and the corresponding Log Position through the following centralized method:

◆ Full database cold backup
For databases that can be shut down, we can shut down the Master MySQL, and then copy all the data files and log files to the appropriate location in the host where the Slave needs to be built, the resulting backup set is the most complete. After the backup is completed, start MySQL on the Master side.
Of course, we only get a backup set that meets the requirements. We also need the log location corresponding to the backup set. For such a backup set, we have multiple methods to obtain the corresponding log location. For example, after the Master is started and no application is connected to the Master, run the SHOW Master STATUS Command to obtain the available Log Position from the Master. If we cannot control the connection of the application after the Master node is started, data may be written into the application before we can execute the SHOW Master STATUS Command, at this time, we can use the mysqlbinlog client program to analyze the latest Binary Log of the Master to obtain its first valid Log Position. Of course, if you are very clear about the first valid Log location of each new Binary Log in your MySQL version, you do not need to perform any operations.

◆ Perform "Hot Backup" using LVM, ZFS, and other software with the snapshot function"

If our Master database is a database that meets the 365*24*7 service, we cannot obtain the required backup set through cold backup. At this time, if MySQL is running in support

Snapshot features on the file system (such as ZFS), or although our file system does not support Snapshot, our file system runs on the LVM, we can use related commands to create a Snapshot for the directory where MySQL Data Files and log files are located, so that we can obtain a backup set that is similar to the full-database cold backup.
Of course, to ensure that the data in our backup set can be complete and consistent, we need to use the related command (flush tables with read lock) in the Snapshot process to LOCK write operations on all TABLES, it also includes the commit action in the storage engine that supports transactions, so that all data of the Snapshot can be completely consistent. After Snapshot, we can unlock tables. Some may worry that if all write operations are locked, will our applications not be able to provide the write service? Indeed, this is unavoidable. However, the Snapshot operation usually takes a short time, so it will not take too long.
What should I do with Log Position? Yes, the backup made through Snapshot also requires a Log Position corresponding to the backup to meet the requirements for setting up the Replication environment. However, in this way, we can obtain the corresponding Log Position more easily than performing cold backup. The reason is that the database cannot perform any write operation until the write operation of all tables is locked, in this time period, you can obtain an accurate Log Position by executing the show master status Command at any time.
Because this method does not need to completely stop the Master during the implementation process, it only needs to stop writing, so we can also call it "Hot Backup ".

◆ Client program through mysqldump

If our database cannot be shut down for cold backup, and MySQL is not running on a file system or management software that can be used for Snapshot, then, we need to use mysqldump to dump the data of the database (or table) to be copied on the Master end. To ensure consistency and integrity of our backup set, we must keep the dump data in the same transaction or lock all the write operations on the tables to be copied. To do this, if we use a storage engine that supports transactions (such as Innodb), we can add the-single-transaction option when executing the mysqldump program, however, if our storage engine does not support transactions, or the dump table only needs to support some transactions, we can only use the flush tables with read lock command to suspend all write services, then dump the data. Of course, if we only need to dump the data of a table, we don't need to be so troublesome, because the mysqldump program actually obtains data from each table through an SQL statement when dumping data, therefore, data consistency can always be ensured for a single table.

In the above operation, we only obtained the appropriate backup set, and there is no Log Position corresponding to the backup set. Therefore, the Server Load balancer cannot be fully set up. Fortunately, developers of the mysqldump program have long considered this issue, so they added another parameter option to the mysqldump program to help us get the corresponding Log Position. This parameter option is-master-data. After this parameter option is added, mysqldump generates a change master to command in the dump file, which records the detailed Log Position information corresponding TO the dump time. As follows:
Test the group_message table in the dump example database:
Sky @ sky :~ $ Mysqldump -- master-data-usky-p example group_message>
Group_message. SQL
Enter password:
Then run the grep command to find out:
Sky @ sky :~ $ Grep "change master" group_message. SQL
Change master to MASTER_LOG_FILE = 'mysql-bin.000035', MASTER_LOG_POS = 399;
Even the command for changing master to has been prepared for us. It's really considerate.
If we want to dump multiple tables that support transactions at a time, many may choose to ensure data consistency and integrity by adding the-single-transaction option. This is indeed a good choice.
However, if we need to dump a large amount of data, a large transaction may be generated and will last for a long time.

◆ Perform "Hot Backup" on an existing Slave end"

If we already have a Server Load balancer instance that needs to be copied from the Master to which the Replication environment is to be set up, this backup set can be easily obtained. We can temporarily stop the existing Slave (if there are multiple Slave, we only need to stop one of them) and execute the flush tables command to refresh the data of all TABLES and indexes at the same time. At this time, no write operations will be performed on the Slave. We can copy all the data files and log files for a full backup, and also use Snapshot (if supported). Of course, if the Snapshot function is supported, we recommend that you use Snapshot, because this can greatly shorten the time for stopping the replication of Slave and reduce the data delay of the Slave.
Using the existing Slave to obtain the backup set is not only easy to obtain the database backup method, but also can be omitted for all operations such as the Log Position and the later configuration of the new Slave, the new Server Load balancer instance is started based on the backup set and can be copied from the Master normally.
In the whole process, we only stopped a replication thread of an existing Slave in a short period of time, which has little impact on the normal service of the system. Therefore, this method can be called "Hot Backup ".

3. Use the Slave client to restore the backup "snapshot"

In the second step above, we have obtained the required backup set. All we need to do in this step is to restore the backup set obtained in the previous step to our Slave-side MySQL.
The restoration operations on the Slave end vary depending on the backup sets obtained by the preceding four methods. Here is a simple description of the recovery of the four backup sets:

◆ Restore the full-database cold backup set
Because this backup set is a complete physical backup of the database, we only need to copy this backup set to the host where Slave is located through FTP or network transmission software such as SCP. set the cnf configuration file, store the file in the corresponding directory, overwrite all existing data, logs, and other related files, and then start MySQL on the Slave end to complete the recovery process.
◆ Restore the backup set obtained by Snapshot to the Master
The backup set obtained by performing Snapshot on the Master database is basically the same as the full-database cold Backup recovery method, the only difference is that you need to mount the Snapshot to a directory through the corresponding file system before you can perform subsequent file copy operations. Subsequent operations are basically the same as those for restoring a full-database cold backup set.

◆ Restore the backup set obtained by mysqldump

The backup set obtained through the mysqldump client is significantly different from the recovery methods of the previous two backup sets. Because both of the preceding backup sets belong to physical backup, the backup made by mysqldump client is a logical backup. The mysqldump backup set is restored by executing all SQL statements in the backup file through the mysql client program.
Before using the mysql client TO restore the Server Load balancer, we recommend that you copy the CHANGE master TO command obtained through-MASTER-data, and then unregister the CHANGE master TO command from the backup file, and then restore.

Because this command is not a complete change master to command, if. when the MASTER_HOST, MASTER_USER, and MASTER_PASSWORD parameters are not configured in cnf, the statement cannot be completed effectively.
You can use the mysql client to restore the backup as follows:
Sky @ sky :~ $ Mysql-u sky-p-Dexample <group_message. SQL
In this way, the logical backup set made by the mysqldump client can be restored to the database.

◆ Restore the Hot Backup obtained through the existing Slave

The backup set obtained through the existing Slave is similar to the first or second backup set above. If the backup set is obtained by directly copying data and log files, it is the same as the full-database cold backup method. If the backup set is obtained through Snapshot, this is exactly the same as the second backup recovery method.

4. Configure and start Slave

After completing the preceding three steps, you only need TO perform the last step TO build the Replication environment. That is, you can use the change master to command TO configure and then start Slave.
The change master to Command requires a total of five items:
MASTER_HOST: Master Host Name (or IP address );
MASTER_USER: the username used by the Slave to connect to the Master, which is actually the previously created repl user;
MASTER_PASSWORD: password of the user who connects to the Master using Slave;
MASTER_LOG_FILE: name of the log file to be copied;
MASTER_LOG_POS: the location of the Log file to be copied, that is, the Log Position mentioned in the backup set process.

The following is a complete example of the change master to command:
CHANGE MASTER
Root @ localhost: mysql 08:32:38> CHANGE MASTER
-> MASTER_HOST = '192. 168.0.1 ',
-> MASTER_USER = 'repl ',
-> MASTER_PASSWORD = 'Password ',
-> MASTER_LOG_FILE = 'mysql-bin.000035 ',
-> MASTER_LOG_POS = 399;
After the change master to command is executed, run the following command TO start SLAVE:
Root @ localhost: mysql 08:33:49> start slave;
So far, our Replication environment has been set up. Readers can perform tests on their own to try to build the MySQL Replication. If you need to learn more detailed steps during the MySQL Replication setup process, you can refer to the MySQL official manual.

Conclusion 13.5

In practical application scenarios, MySQL Replication is the most widely used design method to improve system scalability. After many MySQL users use the Replication function to improve the scalability of the system, the performance of the original system is improved by simply adding low-cost hardware devices to multiply or even order of magnitude, mySQL is one of the most popular features for low-end MySQL users and one of the most important reasons for a large number of MySQL users to choose MySQL.

 

 

From: MySQL performance tuning and architecture design, Jian Chaoyang

Reprinted please indicate the source:

Author: JesseLZJ
Source: http://jesselzj.cnblogs.com