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
- Physical 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, bundled with the MySQL client, doesn’t need additional installation.
mysqldump -u user -ppass db_name > name.sql
mysql restore dump
mysql -u user -ppass < name.sql
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.
Advantages of mysqldump
- Simple: No need to revisit documentation after a few uses.
- Flexible: Numerous cmd options to fit any need.
- Pre-installed: No setup stress.
- Works on both Windows and Linux.
mysqldumpoperates single-threaded, limiting its speed and not leveraging multi-core processing advantages.
Tools Based on mysqldump
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:
In the automysqlbackup.conf file, you’ll need to set:
If you use sudo for authorization, keep these settings commented.
You can test it right away:
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.
MySQL Shell (
shell. Through these objects, you can access various administrative tools.
Backups created using mysqlsh consist of multiple files, specifically SQL table descriptions, and either
.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.
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
-pparameter), after which
mysqlshwill remember it.
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
You can only backup tables using the
mysqlsh --mysql -u adm -h localhost -P 3306 -e "util.dumpTables('database name', ['table_name_1','table_name_2'], '/tmp/sample-backup-tables')"
mysqlsh --mysql -u adm -h localhost -P 3306 -- util load-dump /tmp/sample-backup-tables
Advantages of mysqlsh for Backups
- Multithreaded backups.
- Official tool from MySQL, ensuring continued support and product development.
- Supports CSV: useful for data migration.
- Compatible with both Windows and Linux.
- Not preinstalled.
- It can be a bit complex: A mix of Python, JS, SQL, and shell commands can easily confuse users who just want to perform a simple backup.
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.
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.
For authentication using username and password, remember: space is required between the password and the
mydumper -u adm -p pass
Backing up a single 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.
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
For instance, to exclude all tables named logs and temp from all schemas:
mydumper --regex '^(?!.[logs|temp]).$'
For restoration, just specify the directory:
If the objects you are restoring already exist in the database, there will be an error.
Restoring specific tables
--overwrite-tables parameter can be helpful when restoring tables.
myloader --directory=export-20230916-151241 --overwrite-tables
Advantages of mydumper for Backups
- As simple as mysqldump.
- Multithreaded backups.
- Using regex patterns for table filtering.
- Split backups into multiple files: it’s easy to understand the backup content.
- Not pre-installed and works only on Linux.
- Has fewer data output customization options compared to mysqldump.
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
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 works on both Linux and Windows.
If you’ve installed MariaDB, then the necessary repositories are already added. Simply execute:
sudo apt install mariadb-backup
There’s an MSI installer on the official website.
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).
--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
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.
Advantages of xtrabackup
- Backup creation time: Faster than any logical backups.
- Restore time: A database recovers quickly because it’s simply copying files back.
- Server load: The database management system is hardly involved in the backup, ensuring the backup process does not affect the database performance, except indirectly through disk I/O competition.
- Built-in support for incremental backups.
- Backing up individual databases or tables can be nuanced and, in any case, requires stopping the MySQL Server.
- Not pre-installed on Linux, and only mariabackup is available for Windows.
- For incremental backups, it’s necessary to locally store the full backup as it is used for comparison.
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.
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
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
- Fast backups: Backups are created quickly.
- Fast restore: A database is restored as quickly as it is created.
- Simple interface with comprehensive and high-quality documentation.
- Can be installed on Windows and Linux.
- To try it out, an Oracle account is required (not straightforward).
- Post-trial usage requires purchasing the enterprise edition, priced at $5,000.
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.