Targeted at DBAs, this article offers an overview of CLI utilities for MySQL backup. The commands described can be implemented in Linux Shell (.sh) scripts, as well as in Windows cmd (.bat) and PowerShell (.ps1) scripts. In addition to creating backups, dumps and exporting data, we’ll also delve into MySQL restore commands.

• Logical Backups
  • mysqldump
  • Tools Based on mysqldump
    • AutoMySQLBackup
    • SqlBak-CLI
  • mydumper \ myloader
  • mysqlsh
• Physical Backups
  • xtrabackup & mariabackup
  • mysqlbackup (MySQL Enterprise Backup)

Logical Backups

Logical backups contain data in the same form as returned by SQL SELECT queries, typically as human-readable SQL dumps capturing tables. CLI utilities for this task include: mysqldump, mysqlsh, and mydumper.

mysqldump

mysqldump, bundled with the MySQL client, doesn’t need additional installation.

mysqldump command

mysqldump -u user -ppass db_name > name.sql

mysql restore dump

mysql -u user -ppass < name.sql

mysqldump table

mysqldump -u user -ppass db_name table_name > table_name.sql

mysqldump all databases

mysqldump -u user -ppass --all-databases

mysqldump with compression (gzip)

mysqldump -u user -ppass db_name | gzip > name.sql.gz

Restore compressed mysqldump

gunzip < name.sql.gz | mysql -u user -ppass

mysqldump comes with a wide range of parameters that tweak the dump’s format. Among other things, it can create specific dumps ideal for tasks like syncing with backups from binary logs or kickstarting database replication.

Tools Based on mysqldump

There are several tools like AutoMySQLBackup and sqlbak-cli that utilize mysqldump’s capabilities.

AutoMySQLBackup

AutoMySQLBackup is a favorite among many in the community and builds upon mysqldump. You can download it here.

It’s essentially a bash script, which means it’s designed for Linux and won’t work on Windows. The script uses configuration files and offers features like automatic deletion of old backups.

To get started, after downloading and extracting, run:

bash install.sh

In the automysqlbackup.conf file, you’ll need to set:

CONFIG_mysql_dump_username='user'
CONFIG_mysql_dump_password=''

If you use sudo for authorization, keep these settings commented.

You can test it right away:

bash automysqlbackup

Inside automysqlbackup.conf, there’s a wealth of commented-out settings waiting to be customized. One key area to look at is the “Rotation Settings” section, where you decide how long to retain backups:

# Rotation Settings

# Which day do you want monthly backups? (01 to 31)
# If the chosen day is greater than the last day of the month, it will be done
# on the last day of the month.
# Set to 0 to disable monthly backups.
#CONFIG_do_monthly='01'

# Which day do you want weekly backups? (1 to 7 where 1 is Monday)
# Set to 0 to disable weekly backups.
#CONFIG_do_weekly='5'

# Set rotation of daily backups. VALUE*24hours
# If you want to keep only today's backups, you could choose 1, i.e. everything older than 24hours will be removed.
#CONFIG_rotation_daily=6

# Set rotation for weekly backups. VALUE*24hours
#CONFIG_rotation_weekly=35

# Set rotation for monthly backups. VALUE*24hours
#CONFIG_rotation_monthly=150

And if your server is set up with a mail client, use the Notification setup section for email alerts. Also, there’s an Encryption section to password-protect your backup archives.

A quick heads-up: AutoMySQLBackup doesn’t run by itself, so remember to schedule it in your cron tasks.

mysqlsh

MySQL Shell (mysqlsh) is the next-generation shell for MySQL, developed by Oracle Corporation. mysqlsh can operate in either JavaScript or Python mode. Regardless of the chosen interpreter, you will have access to special objects: dba, utils, and shell. Through these objects, you can access various administrative tools.

Backups created using mysqlsh consist of multiple files, specifically SQL table descriptions, and either .tsv or .csv files containing the data.

For large databases, mysqlsh is considerably faster, especially on multi-core servers. This speedup is achieved through a combination of multi-threaded data extraction and on-the-fly compression using the zstd algorithm.

Installing mysqlsh

Unlike mysqldump and mysql client, mysqlsh doesn’t come preinstalled with MySQL.

For Linux, you first need to add the MySQL repository and then install mysqlsh using the package manager. Follow these installation instructions.

For Windows, simply download the MSI installer from the official site.

mysqlsh backup commands

Dumping an Instance

Use the methods of the utils object to create a database dump.

mysqlsh --mysql -u adm -h localhost -P 3306 -e "util.dumpInstance('/tmp/sample-backup-instane')"

Alternatively, you can use the special syntax of mysqlsh for object methods, which is more concise but not always applicable:

mysqlsh --mysql -u adm -h localhost -P 3306 -- util dump-instance /tmp/b-m

Restoring an Instance

mysqlsh --mysql -u adm -h localhost -P 3306 -- util load-dump /tmp/sample-backup-instane

Backup a Schema

mysqlsh --mysql -u adm -h localhost -P 3306 -- util dump-schemas database_name --output-url='/tmp/sample-backup-schema'

Restore a Schema

mysqlsh --mysql -u adm -h localhost -P 3306 -- util load-dump /tmp/sample-backup-schema

Backup Tables

You can only backup tables using the -e parameter.

mysqlsh --mysql -u adm -h localhost -P 3306 -e "util.dumpTables('database name', ['table_name_1','table_name_2'], '/tmp/sample-backup-tables')"

Restore Tables

mysqlsh --mysql -u adm -h localhost -P 3306 -- util load-dump /tmp/sample-backup-tables

mysqlsh --mysql -u adm -h localhost -P 3306 -- util dump-instance /tmp/b-m1 --compression='none' --dialect='csv'

mydumper \ myloader

mydumper is a community-developed command-line tool for MySQL. Leading contributors to this utility include employees from Oracle, MariaDB, and Percona, but it is not a product of these companies. Instead, it is an independent solution.

Although it creates logical backups, similar to mysqldump and dumps through mysqlsh, it has its unique features, such as the inclusion/exclusion of database objects using regex patterns.

Backups created with mydumper consist of a set of .sql files; each table has its file, while other objects (triggers, functions) are grouped into a single file.

Files created by mydumper can be restored using the mysql client since the dumps are just a set of .sql files. However, it’s more convenient to use myloader, which is installed alongside mydumper.

Installing mydumper

mydumper is available only for Linux. DEB and RPM packages can be downloaded from the GitHub Release Page.

mydumper Backup Commands

Dumping an entire MySQL instance

If you’re operating as root, you can run without parameters. The backup will be placed in a subdirectory of the current directory named export-date-time.

mydumper

For authentication using username and password, remember: space is required between the password and the -p parameter!

mydumper -u adm -p pass

Backing up a single database

mydumper --database=my_database

Backing up multiple tables

Note that tables in the --tables-list parameter should be in the database_name.table_name format. Multiple tables can be specified by separating them with commas.

mydumper --tables-list=testdb1.table1,testdb2.table2

Inclusion/exclusion tables via regex patterns

You can specify a more complex pattern for including or excluding schemas and tables in the dump by using the --regex parameter. This pattern should match the table name in the database_name.table_name format.

For instance, to exclude all tables named logs and temp from all schemas:

mydumper --regex '^(?!.[logs|temp]).$'

Restore

For restoration, just specify the directory:

myloader --directory=export-20230916-151241

If the objects you are restoring already exist in the database, there will be an error.

Restoring specific tables

Using the --overwrite-tables parameter can be helpful when restoring tables.

myloader --directory=export-20230916-151241 --overwrite-tables

Physical Backups

Physical backups involve directly copying the database files or making a byte-for-byte copy of data from the database files to a backup file. The primary advantage of these backups is their speed and minimal strain on the server.

However, they come with their own set of disadvantages. Typically, such backups can only be restored to the same server version. Additionally, they might be bulky in size since they copy data along with the index files.

xtrabackup & mariabackup

xtrabackup is a command-line utility for creating hot physical backups of MySQL servers. Though it’s open-source, its main maintainer is the Percona company, hence it’s often referred to as Percona xtrabackup. Historically, it’s also been known as innobackupex, but their functionalities have now been merged.

A fork named mariabackup exists for MariaDB and has slight differences. In practice, just replace the xtrabackup command with mariabackup.

Installation

xtrabackup

Xtrabackup can only be installed on Linux.

To install, the easiest method is to connect to the Percona repository.

For MySQL 5.*+, you should install xtrabackup 2.4

For MySQL 8.0+ need xtrabackup 8.0

MariaBackup

Mariabackup works on both Linux and Windows.

Linux

If you’ve installed MariaDB, then the necessary repositories are already added. Simply execute:

sudo apt install mariadb-backup

Windows

There’s an MSI installer on the official website.

xtrabackup examples

xtrabackup all databases

To back up all databases, use:

xtrabackup --user=adm --password=pass --backup --target-dir=/backup/folder/

xtrabackup restore all databases

Restoring a full backup isn’t straightforward. You have to stop the MySQL server, prepare the backup for restore, execute the –copy-back operation, grant the MySQL user rights to the MySQL data directory, and then start the MySQL server.

# stop the MySQL server
sudo service mysql stop 
# clear the MySQL data directory
sudo rm -rf /var/lib/mysql/*
# prepare the backup for restore
xtrabackup --prepare --target-dir=/backup/folder/
# restore the backup
xtrabackup --copy-back --target-dir=/backup/folder/
# set the right permissions
chown -R mysql:mysql /var/lib/mysql
# start the MySQL server
sudo service mysql start

xtrabackup partial backup

Besides full backups, xtrabackup allows you to back up only specific tables or databases. This is possible if the innodb_file_per_table variable is set.

SHOW VARIABLES LIKE 'innodb_file_per_table';

If this variable is set to true, each table will be stored in a separate file. By default, this option is enabled.

Thanks to the separate file storage, for data restoration you simply need to copy the table file back to the DB (provided an identical table exists).

xtrabackup database

For the --databases parameter, you need to provide a regular expression matching the database name. In the simplest case, it’s just the database name itself.

xtrabackup --user=root --password=pranastest-1 --backup --databases="^my_database$" --target-dir=/backup/my_database

xtrabackup table

You can also set a regex filter for tables (in the format database.table).

xtrabackup --user=root --password=pranastest-1 --backup --tables="^my_database\.my_table$" --target-dir=/backup/my_table

xtrabackup restore table

Restoring a table with xtrabackup involves copying the specific table file back to its place in the MySQL server directory structure. Naturally, this means that the target table should exist, and if it doesn’t, it has to be created exactly as it was at the time of the backup.

# Stop the MySQL server
sudo service mysql stop
# Prepare the table backup for restoration
xtrabackup --prepare --target-dir=/backup/my_table
# Replace the existing .ibd table file with the one from the backup
cp /backup/my_table /var/lib/mysql/my_database/t.ibd
# Start the MySQL server
sudo service mysql start

Xtrabackup is a comprehensive utility with numerous options. It allows for incremental backups, on-the-fly backup compression, or streaming backups. For more details, it’s advised to consult the official documentation.

mysqlbackup (MySQL Enterprise Backup)

mysqlbackup is a utility that is part of the commercial solution for MySQL server — MySQL Enterprise Edition.

This tool is designed for creating online physical backups. Technically, it has a lot in common with xtrabackup, hence many of their features overlap, such as on-the-fly compression, incremental backups, and streaming backups. A detailed comparison of the features of these utilities would reveal their similarities. However, in terms of command-line interface interaction (options and commands), they are entirely distinct.

mysqlbackup example

Backup

To create a backup, you need to specify the name of the backup file and the directory where you want to save the backup.

mysqlbackup --user=root --password --backup-image=backup.mbi --backup-dir=D:\Temp\EnterpriseBackup backup-to-image

Restore

The restore process is essentially the same as the backup, but with different parameters.

mysqlbackup --datadir="C:\ProgramData\MySQL\MySQL Server 8.0\Data\" --backup-image=backup.mbi --backup-dir="D:\Temp\EnterpriseBackup" copy-back-and-apply-log

So, What’s Best?

As always, it all depends on the task at hand. Do you simply need to export a database, or do you need a command for use in an automatic backup script? What’s the size of your database and how heavily is it loaded?

If you need to export a MySQL database by the command line, mysqldump is likely your best bet. It has around 125 options that allow you to tailor the output .sql file to your specific needs. This utility is available on both Windows and Linux right out of the box along with MySQL.

If you need regular automatic logical MySQL backups, then the mysqlsh or mydumper utilities are faster than mysqldump. If you only need a backup, then mydumper is simpler. However, if it’s just a part of your duties and you have a long list of MySQL administration tasks, it’s worth exploring mysqlsh.

Although logical backups do not block the database for InnoDB tables, they do place a noticeable load on the DBMS. For high-load databases, physical backups are more suitable. Your choice depends on where and how you use MySQL specifically.

For MySQL on Windows, the only suitable option is the enterprise solution, mysqlbackup.
For MariaDB on Windows and Linux, it’s best to use what MariaDB itself offers, namely mariabackup.
For MySQL on Linux, both mysqlbackup and xtrabackup will do. If you don’t need a specific unique feature of mysqlbackup, then xtrabackup is better due to its lower cost.

A Pro Tip as a Postscript

For large databases, mysqldump can also be used for backups. However, the backup should be done not on the production primary server but on a secondary replica. Even if the backup takes many hours, it won’t impact the primary server.