Operation of 200GB large table backup under MySQL (using the transmission table space to solve the problem of shutting down the service and publishing table backup)

Problem background

When the server is discontinued and updated, a 200GB large table (about 20 billion rows of data) needs to be quickly backed up to prevent operational errors.

Because there have been development-written version statements,UPDATEStatementWHEREThe condition was written incorrectly. Originally, only a few rows of data were needed, which eventually led to the update of half of the data in the table.

The MySQL version is MySQL 8. In order to prevent this situation, it is necessary to pre-back up an important large table so that it can be rolled back, recovered in time, and fallback in time. There are roughly the following backup methods:

The core requirements of this scenario: The time for shutting down the service and updating is very limited, such as the update must be completed within 1 hour.

Operation process

The first two are relatively simple. You can back up old tables by transducing data. In case of problems, you can use the exported data for quick recovery. The third method is estimated to be less than people. The following are the specific operation methods.

• Source table and spare tire table structure

-- Source table（aa）
CREATE TABLE aa (
  id int(11) DEFAULT NULL,
  sname VARCHAR(100)
) ENGINE=InnoDB;
-- Spare tire watch（bb）
CREATE TABLE bb (
  id int(11) DEFAULT NULL,
  sname VARCHAR(100)
) ENGINE=InnoDB;
greatsql> INSERT INTO aa SELECT 1,"nihao";

2. Uninstall the table space of the spare tire table:

greatsql> ALTER TABLE bb DISCARD TABLESPACE;  -- Lock and generate configuration files

3. Perform table space export of source table:

greatsql> USE school;
greatsql> FLUSH TABLES aa FOR EXPORT;   

4. Copy the tablespace files (ibd and cfg files), and then re-assign permissions to ensure that there will be no problems when importing the tablespace.

$ cd /data/mysql/mysql3306/data/school
$ cp     
$ cp   
$ chown -R mysql:mysql /data/mysql/mysql3306/data/*

5. Under the same database, both the spare tire table and the source table are imported into the tablespace.

greatsql> USE school;
greatsql> UNLOCK TABLES;  
greatsql> ALTER TABLE bb IMPORT TABLESPACE;
greatsql> ALTER TABLE aa IMPORT TABLESPACE;

6. Query table data and verify data consistency

greatsql> USE school;
greatsql> SELECT * FROM bb;
greatsql> SELECT * FROM aa;

The query table data is normal, no problems

greatsql> SELECT * FROM aa;
+------+-------+
| id   | sname |
+------+-------+
|    1 | nihao |
+------+-------+
1 row in set (0.01 sec)
greatsql> SELECT * FROM bb;
+------+-------+
| id   | sname |
+------+-------+
|    1 | nihao |
+------+-------+
1 row in set (0.00 sec)

Check the table's data file, no problem

$ ll
total 228
-rw-r----- 1 mysql mysql 114688 Mar  4 16:51 
-rw-r----- 1 mysql mysql    781 Mar  4 16:52 
-rw-r----- 1 mysql mysql 114688 Mar  4 16:52 

7. Update and rollback of release version

-- Distribution operation（Example）
greatsql> UPDATE aa SET sname = 'new_value' WHERE id > 1;

8. If there is any problem with the release, directly exchange the table name to restore the data of the entire table as quickly as possible.

-- Rollback operation（Exchange table name）
greatsql> ALTER TABLE aa RENAME TO aa_temp; 
greatsql> ALTER TABLE bb RENAME TO aa; 

Summarize

The most important thing about the whole operation isStep 4, the operating system level copy completes the backup of the entire table, which is much faster than the data going back and forth. in addition,Step 5The spare tire table can also be not imported, only if there is a problem with the release version, it is also possible to import it again.

The key advantages of this approach are as follows

• Copy the .ibd file directly without copying the data line by line.
• Zero lock table time:FLUSH TABLES tablename FOR EXPORTOnly briefly locked (second level).
• Quick recovery: implements second-level rollback through table name exchange.

It is especially suitable for these scenarios: single instance environment without master-slave architecture, fast backup of large tables, and time-sensitive downtime.

Of course, if there is a master-slave architecture, it is more recommended to use itThe fourth typeThe method is more controllable in operation and can be ensured to be completed in a short time.

About GreatSQL

GreatSQL is a domestic independent open source database suitable for financial-grade applications. It has multiple core features such as high performance, high reliability, high ease of use, and high security. It can be used as an optional replacement for MySQL or Percona Server. It is used in an online production environment and is completely free and compatible with MySQL or Percona Server.

This is the article about 200GB large table backup under MySQL and using the transmission table space to solve the problem of shutting down the server and publishing table backup. For more information about using java code to obtain JVM information, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!