How to create MySQL or MariaDB database backup

Creating a backup of your MySQL or MariaDB database is essential for protecting data from unexpected loss or corruption. Backups allow you to recover from failures or human errors by restoring the database to a previous state. The most common tool for performing backups is mysqldump, which exports the database to a SQL file. This backup can be compressed using gzip to save space and stored for future use.

Using the mysqldump utility allows you to create a complete backup of your MySQL or MariaDB database from the command line. It can export a full database, individual tables, or even multiple databases at once. Piping the output of mysqldump directly to gzip allows you to compress the backup on the fly, saving disk space without the need for a separate compression step.

This article outlines the process of creating a compressed backup of your MySQL or MariaDB database using mysqldump and gzip, ensuring that the backup files are both efficient in size and ready for restoration when needed.

1. Ensure that the mysqldump utility is installed on your system.

   $ mysqldump --version

   This command checks if mysqldump is installed and available for creating backups.

2. Create a compressed backup of your entire MySQL or MariaDB database and pipe it to gzip.

   $ mysqldump -u root -p your_database | gzip > /path/to/backup/your_database.sql.gz

   This command creates a compressed backup of the specified database. The output is directly compressed into a .gz file, saving disk space.

3. To back up a specific table and compress it on the fly, specify the table name.

   $ mysqldump -u root -p your_database your_table | gzip > /path/to/backup/your_table.sql.gz

   This command backs up a single table and compresses it immediately using gzip.

4. To back up multiple databases, use the --databases option and pipe the output to gzip.

   $ mysqldump -u root -p --databases database1 database2 | gzip > /path/to/backup/multiple_databases.sql.gz

   This command creates a compressed backup of multiple databases in one file.

5. Verify the compressed backup file is successfully created.

   $ ls /path/to/backup/

   Check that the .gz file exists in the specified directory and is ready for future use.