HOME/Articles/What is the best way to back up MySQL 8.0?

What is the best way to back up 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) mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, contributing to OSS, etc.

This article is located in SRGDBWGWe will publish materials regarding the database that the (DB Working Group) provides to the entire company.

I hope this helps in some way.

Introduction conclusion Backup Techniques mysqldump mysqlpump MySQL Shell Instance Dump Utility and Dump Loading Utility Percona XtraBackup CLONE PLUGIN Backup & Restore Speed Comparison Testing database Graph of results 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 Physical backup and restore is almost the same speed between XtraBackup and CLONE PLUGIN XtraBackup isMySQL 8.0.29 or laterSoMajor ConstraintsNote that

Backup Techniques

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 you can run to recreate the original schema objects, table data, or both. Other output formats 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 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. 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 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 MySQL Shell are available here

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

mysqlpump

It is the successor to mysqldump and is included in the MySQL 5.7.8 and later packages in 2015.

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 into a MySQL Server instance a schema or table that has been 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”. 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 the 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 have chosen 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 DB 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 locally as a file or create a copy of the target server remotely. Note that backup targets are limited to tables with 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. The clone data is a physical snapshot of the data stored in InnoDB, including the schema, tables, tablespaces and data dictionary metadata. The clone data consists of a fully functional data directory and is a MySQL ...

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

Backup & Restore Speed Comparison

Testing database

MySQL Ver	8.0.28
Data size	Approximately 42GB

Resulting graph

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

First, I'll post a graph of the results. The vertical axis is 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 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 result

Run Time: 38 minutes 45 seconds

mysqlpump

Backup execution results

Run Time: 2 minutes 22 seconds

Restore execution result

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 result

Run Time: 15 minutes 35 seconds

Percona XtraBackup

Backup execution results

Run Time: 3 minutes 22 seconds

Restore execution result

Run Time: 3 minutes 41 seconds

CLONE PLUGIN

Backup execution results

Run Time: 3 minutes 56 seconds

Restore execution result

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 useful features that allow you to create a flexible backup plan.

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 sinceThe InnoDB redo log format has also changed.

Because the default behavior when executing DDL has been changed to INSTANT, to completely avoid this feature, it would be necessary 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 perform a backup using XtraBackup.

To avoid this, as stated in the error message, 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 in an environment that uses 8.0.17 or later and can use the CLONE PLUGIN, you may want to consider using it.

Conclusion

SRG is looking for people to work with us. If you are 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 SREs to the media business as a cross-sectional SRE under the vision of "improving reliability across the media business." The work is centered around the following three pillars: Consolidating and deploying the technical know-how of each business

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