Shulou(Shulou.com)06/02 Report--

In this issue, the editor will bring you about the function of the MYSQL Clone plug-in. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

It is not a very simple problem to operate a copy of MYSQL in MYSQL. Of course, if you use XTRABACKUP, it is not troublesome, but if there is a simpler way who does not want to use it, MYSQL 8.017 provides this function. As a plug-in, he can generate a local clone or remotely transfer files to a destination. In fact, this way has been waiting for many years, and other databases actually operate such things. It is relatively simple, such as PG pg_basebackup, or PG with REPMGR installed, automatically generates a copy and establishes a connection every minute, which is much easier than MYSQL.

What about the features that have been waiting for so many years? whether we can upgrade to MYSQL 8? at present, it still looks like and so on. The main reason is that the recent update speed of MYSQL 8 is too fast, and many new features are still being released. If we upgrade rashly, we will miss more good features, such as HASH JOIN.

But the update of knowledge is necessary, so let's go

MySQL 8.0.17 introduced a clone SQL statement to make the current MySQL server a "clone" of another MySQL server running on different nodes. We call the server instance that executes the clone statement "recipient". The source server instance from which to clone data is called "Donor". Donors clone all data and metadata stored in the InnoDB storage engine with consistent snapshots to replace the data in the recipient. When the clone SQL statement is executed successfully, the receiving server is automatically restarted. Rebooting involves restoring the snapshot data of the clone, just as the data was copied in the old way. Once the recovery is complete, the recipient is the donor's clone.

Next, we select two machines, 205210. we clone the contents of the database on the 210s to 205s.

1 first install the clone plug-in INSTALL PLUGIN CLONE SONAME "mysql_clone.so" on both machines

(2) to create an account for the operation, it is recommended to set it to super to reduce the complexity of the operation. Actually, you need backup permission on one side and CLONE_ADMIN permission on the other side.

It's easy here.

3 create a donor_list in the recipient and then execute the command directly to start cloning

SET GLOBAL clone_valid_donor_list = "192.168.198.210purl 3306"

Clone instance from clone@192.168.198.210:3306 identified by "1234.Com"

4 in addition, you can execute the following statement on the receiver's side to get the progress of replication.

Select STAGE, STATE, CAST (BEGIN_TIME AS TIME) as "START TIME", CASE WHEN END_TIME IS NULL THEN LPAD (sys.format_time (POWER (10L12) * (UNIX_TIMESTAMP (now ())-UNIX_TIMESTAMP (BEGIN_TIME), 10,') ELSE LPAD (sys.format_time (POWER (10L12) * (UNIX_TIMESTAMP (END_TIME)-UNIX_TIMESTAMP (BEGIN_TIME)), 10,') END as DURATION LPAD (CONCAT (FORMAT (ROUND (ESTIMATE/1024/1024,0), 0), "MB"), 16,') as "Estimate", CASE WHEN BEGIN_TIME IS NULL THEN LPAD ('0% AS BINARY, 7,') WHEN ESTIMATE > 0 THEN LPAD (CONCAT (CAST (ROUND (DATA*100/ESTIMATE, 0) AS BINARY), "%"), 7,') WHEN END_TIME IS NULL THEN LPAD ('0% AS BINARY, 7,') ELSE LPAD ('100% blank, 7 '') END as "Done (%)" from performance_schema.clone_progress

So starting with mysql 8.017, copying a MYSQL is the easiest thing to do compared to other databases.

In the case of cloned progress, the main process is to delete data, copy of file, copy of page, copy of redo, recipient of file synchronous restart, and then reexecute data of logs that are not performed in the copy.

The operation is simple, and the question is what exactly is the principle? this is very important and needs to be understood.

Can be roughly analyzed, generally speaking, copying files for data replication is a simple thing, the troublesome thing is how to confirm when copying files, continue to write data, CLONE does not prohibit data writing.

So be sure to record the LSN number according to the log LSN number at the beginning of the file copy, and when the file is copied, record a LSN number, between which the dirty pages in memory will be recorded, and the data will not be refreshed to the data log. When the file is copied (in fact, there is a cache between it, and the non-checkpoint space_id and page_id recorded in the cache will be modified all the time), until the end of the file copy, the recorded LSN number, and the data that has not been checkpoint at this time will fall back into the physical file, and data will continue to be written during the period from refreshing the checkpoint data to the application checkpoint data. So after applying the log of file copy for this period of time, you still need to record this point, and copy the redo log between this point. When the application reaches the end of the overall copy data, it is consistent with the donor of the data. (still need to study)

The principle is similar to that of XTRBACKUP, but not exactly the same.

There are limits to the adoption of such technology.

1 the operation of DDL is not allowed in the operation

2 only one instance can be cloned at a time

3 the cloning method does not support the copy of MY,CNF files (compared with PG's pg_basebackup, PG's CLONE method can directly copy the configuration files)

4 will restart the MYSQL service of the MYSQL recipient, but there is no guarantee that it can be restarted under any circumstances

The scenario in which this function is used is also very clear. MGR, the future MGR may be the best way to distribute and maintain the database. Through CLONE, you can automatically generate a copy of MYSQL in the fastest way and automatically add it to the MGR cluster, which is unimaginable in traditional ORACLE and SQL SERVER.

MYSQL 8 is no longer the thin MYSQL that used to be, and all aspects are improving. Compared with the other three competitors, there are already some characteristics than other databases. Although parallelism and query methods still need to be improved, do not underestimate the power of MYSQL 8 single database, and you can indeed compete with other databases.

These are the functions of the MYSQL Clone plug-in shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.