⚠️⚠️⚠️ STG SRG Portal ⚠️⚠️⚠️/Technical article/What is the best backup method for MySQL 8.0?

What is the best backup method for MySQL 8.0?

2024/11/28 19:182024/11/28 19:18

This is Onikai (@fat47) from the Service Reliability Group (SRG) of the Technology Division.

#SRGThe Service Reliability Group primarily provides comprehensive support for the infrastructure surrounding our media services, focusing on improving existing services, launching new ones, and contributing to open-source software (OSS).

This article is located within SRG.DBWGThe DB Working Group is releasing database-related materials that it provides to the entire company.

I hope this is of some help.

Introduction conclusion Backup methods mysqldump mysqlpump MySQL Shell Instance Dump Utility and Dump Loading Utility Percona XtraBackup CLONE PLUGIN Backup and Restore Speed Comparison Database for verification Graph of results mysqldump mysqlpump MySQL Shell Instance Dump Utility and Dump Loading Utility Percona XtraBackup CLONE PLUGIN Which backup tool should I use?In conclusion

Introduction

MySQL 8.0 offers various backup methods, but the question remains: which one is the best to use?

conclusion

💡

Logical backup and restore isMySQL Shell Dump UtilityThis is the fastest. For physical backup and restore, there is almost no difference in speed between XtraBackup and CLONE PLUGIN. XtraBackup isMySQL 8.0.29 and laterSoMajor constraintsBe careful as this has happened.

Backup methods

There are several ways to perform a full backup, including the following:

mysqldump

mysqlpump

Percona XtraBackup

MySQL Shell Instance Dump Utility and Dump Loading Utility

CLONE　PLUGIN

mysqldump

The mysqldump client is a utility for performing logical backups.

It generates a set of SQL statements that can be executed to reproduce the original schema objects, table data, or both. Output is also possible in other formats, such as CSV or other delimited text.

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

The mysqldump client utility performs logical backups and generates a set of SQL statements that can be executed to reconstruct the original database object definitions and table data. It dumps 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 MySQL Shell dump utilities, which provide parallel dumps with multiple threads, file compression, progress 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 MySQL Shell load dump utilities. Installation instructions for MySQL Shell are available here.

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

mysqlpump

It has been included in the MySQL 5.7.8 package since 2015 as a successor to mysqldump.

The differences from mysqldump include improvements such as the ability to display progress and parallel processing.

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

The mysqlpump client utility performs logical backups and generates a set of SQL statements that can be executed to reconstruct the original database object definitions and table data. It dumps 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 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. MySQL Shell installation instructions are available 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 in MySQL shell 8.0.21 in 2020.

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

MySQL Shell can also be used in a 5.7 environment.

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

The MySQL Shell dump load utility util.loadDump(), introduced in MySQL Shell 8.0.21, supports importing schemas or tables dumped using the MySQL Database Service DB system (MySQL DB system, abbreviated) 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 data streaming from remote storage, parallel loading of tables or table chunks, progress tracking, resume and reset capabilities, and concurrent loading while the dump is running. 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, the compute instance can access the object storage bucket. If the dump file is on the local system, it must be transferred to the Oracle Cloud Infrastructure Compute instance using the selected copy utility, depending on the operating system selected for the 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 I've mentioned so far are for 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 that was added in MySQL 8.0.17 in 2019.

You can back up your data locally as files or create a remote copy of the target server. Note that backups are limited to tables using the InnoDB storage engine.

MySQL :: MySQL 8.0 Reference Manual :: 5.6.7 Clone Plugin

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

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

Backup and Restore Speed Comparison

Database for verification

MySQL Ver	8.0.28
Data size	Approximately 42GB

Graph of results

実行結果の比較 — Comparison of execution results

I've included the results graph below. The vertical axis represents seconds.

For logical backups, the 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 CLONE PLUGIN.

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

mysqldump

Backup execution results

Execution time: 4 minutes 22 seconds

Restore execution results

Execution time: 38 minutes 45 seconds

mysqlpump

Backup execution results

Execution time: 2 minutes 22 seconds

Restore execution results

Execution 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

Execution time: 15 minutes 35 seconds

Percona XtraBackup

Backup execution results

Execution time: 3 minutes 22 seconds

Restore execution results

Execution time: 3 minutes 41 seconds

CLONE PLUGIN

Backup execution results

Execution time: 3 minutes 56 seconds

Restore execution results

Execution time: 3 minutes 52 seconds

Which backup tool should I use?

MySQL Shell dump utility

When 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 equipped with various convenient features, allowing you to create a flexible backup plan.

For example, it's possible to directly upload acquired backup data to the object storage of a cloud service using Stream.

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

MySQL8.0.29The specifications for ALTER TABLE … ALGORITHM=INSTANT have changed.The InnoDB REDO log format has also been changed.

Since the default behavior when executing DDL has been changed to INSTANT, completely avoiding this feature would require specifying ALGORITHM=INPLACE/COPY; for every DDL, which is not practical.

If there are tables that have been saved using this INSTANT function, you will not be able to perform a backup using XtraBackup.

To avoid this, as indicated in the error message, you need to execute either 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 earlier.

If you are using an environment with version 8.0.17 or later that supports CLONE PLUGIN, you might consider using it.

In conclusion

SRG is looking for new team members. If you are interested, please contact us here.

Recruitment information - CyberAgent SRG #ca_srg

About SRG: SRG (Service Reliability Group) operates under the vision of "improving reliability across media businesses" and promotes the introduction of SRE into media businesses as a cross-functional SRE, working to improve reliability. Our work primarily revolves around the following three areas: Gathering and deploying technical know-how from each business.

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

What is the best backup method for MySQL 8.0?

Introduction

conclusion

Backup methods

mysqldump

mysqlpump

MySQL Shell Instance Dump Utility and Dump Loading Utility

Percona XtraBackup

CLONE PLUGIN

Backup and Restore Speed ​​Comparison

Database for verification

Graph of results

mysqldump

mysqlpump

MySQL Shell Instance Dump Utility and Dump Loading Utility

Percona XtraBackup

CLONE PLUGIN

Which backup tool should I use?

In conclusion

Backup and Restore Speed Comparison