HOME/Technical Articles/What is the best backup method for MySQL 8.0?

What is the best backup method for MySQL 8.0?

2023/1/25 18:272024/9/20 9:03

This is Oniumi (@fat47) from the Service Reliability Group (SRG) of the Technology Headquarters.

#SRG(Service Reliability Group) is a group that mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, and contributing to OSS.

This article is located in SRGDBWGWe will publish materials regarding the database provided by the DB Working Group for the entire company.

I hope this helps in some way.

Introduction conclusion Backup Methods mysqldump mysqlpump MySQL Shell Instance Dump Utility and Dump Loading Utility Percona XtraBackup CLONE PLUGIN Backup & Restore Speed Comparison Verification database Resulting graph mysqldump mysqlpump MySQL Shell Instance Dump Utility and Dump Loading Utility Percona XtraBackup CLONE PLUGIN Which backup tool should I use?Conclusion

Introduction

There are various backup methods available for MySQL 8.0, but we will consider which one is best to use.

conclusion

💡

Logical backup and restoreMySQL Shell Dump Utilityis the fastest. There is almost no difference in speed between XtraBackup and the Clone Plugin for physical backup and restore. XtraBackup isMySQL 8.0.29 or laterSoMajor constraintsNote that

Backup Methods

There are several ways to perform a full backup:

mysqldump

mysqlpump

Percona XtraBackup

MySQL Shell Instance Dump Utility and Dump Loading Utility

CLONE　PLUGIN

mysqldump

The mysqldump client is a utility that performs logical backups.

It generates a set of SQL statements that can be executed to recreate the original schema objects, table data, or both. Other output formats available include CSV and other delimited text.

MySQL :: MySQL 8.0 Reference Manual :: 4.5.4 mysqldump - A Database Backup Program

The mysqldump client utility performs logical backups, generating a set of SQL statements that can be executed to recreate the original database object definitions and table data. Dump one or more MySQL databases for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format. Consider using the MySQL Shell dump utilities, which provide parallel dumping with multiple threads, file compression, progress information display, and cloud features such as Oracle Cloud Infrastructure Object Storage streaming and MySQL Database Service compatibility checks and modifications. Dumps can be easily imported into a MySQL Server instance or MySQL Database Service DB system using the MySQL Shell load dump utilities. Instructions for installing MySQL Shell are available here

https://dev.mysql.com/doc/refman/8.0/ja/mysqldump.html

mysqlpump

It is included in the MySQL 5.7.8 package released in 2015 as the successor to mysqldump.

The differences from mysqldump include improvements such as progress display functionality and parallel processing capabilities.

MySQL :: MySQL 8.0 Reference Manual :: 4.5.6 mysqlpump - A Database Backup Program

The mysqlpump client utility performs logical backups and generates a set of SQL statements that can be executed to recreate the original database object definitions and table data. Dump one or more MySQL databases for backup or transfer to another SQL server. Consider using the MySQL Shell dump utilities, which provide parallel dumps with multiple threads, file compression, progress information display, and cloud features such as Oracle Cloud Infrastructure Object Storage streaming and MySQL Database Service compatibility checks and changes. Dumps can be easily imported into a MySQL Server instance or MySQL Database Service DB system using the MySQL Shell load dump utilities. Instructions for installing the MySQL Shell can be found here. mysqlpump features include: Parallel processing of databases and objects within databases to speed up the dump process

https://dev.mysql.com/doc/refman/8.0/ja/mysqlpump.html

MySQL Shell Instance Dump Utility and Dump Loading Utility

This feature was added to MySQL shell 8.0.21 in 2020.

In addition to performing backups in parallel, restores can also be performed in parallel.

MySQL Shell is also available for the 5.7 environment.

MySQL :: MySQL Shell 8.0 :: 8.6 Dump Load Utility

The MySQL Shell dump load utility, util.loadDump(), was introduced in MySQL Shell 8.0.21 and supports importing schemas or tables dumped using a MySQL Database Service DB system (MySQL DB system, for short) or MySQL Shell (Section 8.5, "Instance Dump Utility, Schema Dump Utility, and Table Dump Utility") into a MySQL Server instance. The dump load utility provides options for streaming data from remote storage, parallel loading of tables or table chunks, progress tracking, resume and reset capabilities, and concurrent loading while the dump is in progress. To import into a MySQL DB system, the MySQL Shell instance running the dump load utility must be installed on an Oracle Cloud Infrastructure Compute instance that has access to the MySQL DB system. If the dump file is in an Oracle Cloud Infrastructure Object Storage bucket, you can access the object storage bucket from your compute instance. If the dump file is on your local system, you must transfer it to your Oracle Cloud Infrastructure Compute instance using a copy utility of your choice, depending on the operating system you selected for your compute instance.

https://dev.mysql.com/doc/mysql-shell/8.0/ja/mysql-shell-utilities-load-dump.html

Percona XtraBackup

This is an open source backup tool released by Percona.

The backup tools mentioned so far are logical backups,

XtraBackup is a physical backup that copies the database files themselves.

Percona Software Documentation

Percona Distribution for MySQL The most stable, scalable and secure open-source MySQL distribution, with two download options: one based on Percona Server for MySQL and one based on Percona XtraDB Cluster. Percona Backup for MongoDB A fully supported, open source, community backup tool for performing consistent hot backups in MongoDB.

https://docs.percona.com/percona-xtrabackup/8.0/index.html

CLONE PLUGIN

This is a physical backup feature added in MySQL 8.0.17 in 2019.

You can back up data locally as a file or create a copy of the target server remotely. Note that backup targets are limited to tables in the InnoDB storage engine.

MySQL :: MySQL 8.0 Reference Manual :: 5.6.7 Clone Plugins

The Clone Plugin allows you to clone data from a local or remote MySQL server instance. The clone data is a physical snapshot of the data stored in InnoDB, including schemas, tables, tablespaces, and data dictionary metadata. The clone data consists of a fully functional data directory and is stored in MySQL...

https://dev.mysql.com/doc/refman/8.0/ja/clone-plugin.html

Backup & Restore Speed Comparison

Verification database

MySQL Ver	8.0.28
Data size	Approximately 42GB

Resulting graph

First, I'll post a graph of the results. The vertical axis is the number of seconds.

For logical backups, MySQL Shell Dump Utility is the fastest for both backup and restore.

For physical backups, there was no significant difference in speed between XtraBackup and the CLONE PLUGIN.

The backup and restore commands and execution times for each tool are as follows:

mysqldump

Backup execution results

Run Time: 4 minutes 22 seconds

Restore execution results

Run Time: 38 minutes 45 seconds

mysqlpump

Backup execution results

Run Time: 2 minutes 22 seconds

Restore execution results

Run Time: 41 minutes 51 seconds

MySQL Shell Instance Dump Utility and Dump Loading Utility

Backup execution results

Execution result: 1 minute 51 seconds

Restore execution results

Run Time: 15 minutes 35 seconds

Percona XtraBackup

Backup execution results

Run Time: 3 minutes 22 seconds

Restore execution results

Run Time: 3 minutes 41 seconds

CLONE PLUGIN

Backup execution results

Run Time: 3 minutes 56 seconds

Restore execution results

Run Time: 3 minutes 52 seconds

Which backup tool should I use?

MySQL Shell dump utility

If you need logical backup data, such as when migrating from an on-premises environment to Aurora, I recommend using this MySQL Shell dump utility.

XtraBackup

XtraBackup has a long history and comes with a variety of convenient features that allow you to create flexible backup plans.

For example, you can use Stream to upload the backup data you have acquired directly to the object storage of a cloud service.

However, caution is required when using XtraBackup with MySQL 8.0.29 or later.

MySQL8.0.29The specification of ALTER TABLE … ALGORITHM=INSTANT has changed fromThe InnoDB redo log format has also changed.

The default behavior when executing DDL has been changed to INSTANT, so to completely avoid this feature, you would need to specify ALGORITHM=INPLACE/COPY; for all DDL, which is not practical.

If there are tables that use this INSTANT function, you will not be able to back them up using XtraBackup.

To avoid this, as the error message states, you need to run OPTIMIZE TABLE or ALTER TABLE ALGORITHM=COPY on the target table.

Percona XtraBackup 8.0.29 and INSTANT ADD/DROP Columns - Percona Database Performance Blog

Oracle's MySQL 8.0.29 release extended the support for ALTER TABLE ... ALGORITHM=INSTANT to 1) allow users to instantly add columns in any position of the table, and 2) instantly drop columns. As part of this work, the InnoDB redo log format has changed for all DML operations on the server.

https://www.percona.com/blog/percona-xtrabackup-8-0-29-and-instant-add-drop-columns/

Backups using XtraBackup should be limited to MySQL 8.0.28 or later.

If you are using 8.0.17 or later, where the CLONE PLUGIN can be used, you may want to consider using the CLONE PLUGIN.

Conclusion

SRG is looking for people to work with us. If you're interested, please contact us here.

Recruitment Information - CyberAgent SRG #ca_srg

About SRG SRG (Service Reliability Group) is working to improve reliability by promoting the introduction of SRE to the media business as a cross-sectional SRE, based on the vision of "improving reliability across the media business." The work is centered around the following three areas: Consolidating and deploying the technical know-how of each business

https://ca-srg.dev/0d353a3a68ba4fb0a2e3443d7009a22b