How to create MySQL or MariaDB database backup

Creating database backups is an essential practice for every database administrator. Regular backups ensure that you can recover data quickly and efficiently in case of accidental deletions, hardware failures, or other unforeseen problems.

MySQL and MariaDB are two of the most popular open-source relational database management systems. While they started as the same system, they have since diverged in terms of features and performance enhancements. Nevertheless, the method to backup databases remains relatively consistent between the two.

Using the command-line tool mysqldump, one can generate a full database backup, or selectively backup specific tables. The generated output, typically a .sql file, contains SQL statements to recreate the database from scratch.

1. Open your terminal.
2. Ensure you have the mysqldump utility installed.

   $ which mysqldump

3. Backup an entire database using mysqldump.

   $ mysqldump -u [username] -p[password] [database_name] > [path_to_save_backup].sql

   Replace [username], [password], [database_name], and [path_to_save_backup] with appropriate values. Omitting the password will prompt you to enter it interactively.

4. To backup specific tables in a database, specify the table names.

   $ mysqldump -u [username] -p[password] [database_name] [table_name1] [table_name2] > [path_to_save_backup].sql

5. For compressed backups, use gzip utility.

   $ mysqldump -u [username] -p[password] [database_name] | gzip > [path_to_save_backup].sql.gz

   Compressed backups save storage space but require decompression before restoring. To decompress:

   $ gunzip [path_to_save_backup].sql.gz

6. Verify the backup file's integrity.

   $ head [path_to_save_backup].sql

   The first few lines should display comments regarding the MySQL version and the creation date.

7. Store the backup in a safe location, and consider creating periodic backups using a cron job or similar scheduling mechanism.

Staying consistent with backups and familiarizing oneself with the restoration process will ensure the health and longevity of your database operations. Always consider the need for backup storage, frequency of backups, and the implications of restoration times when planning your backup strategy.