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.
$ which 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.
$ mysqldump -u [username] -p[password] [database_name] [table_name1] [table_name2] > [path_to_save_backup].sql
$ 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
$ head [path_to_save_backup].sql
The first few lines should display comments regarding the MySQL version and the creation date.
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.
Comment anonymously. Login not required.