Backing up and restoring a MySQL or MariaDB database is super easy. I’ve listed out a few different ways you can do it.
Example MariaDB/MySQL mysqldump Backup Commands
Simple Backup
mysqldump --quick your_database > /home/your_database.sql
Backup and Compress with gzip in a Single Command
mysqldump --quick your_database | gzip -c > /home/your_database.sql.gz
Backup and Compress With Gzip.
Same as above, but also drop and recreate database on restore.
mysqldump --add-drop-database --quick your_database | gzip -c > /home/your_database.sql.gz
Plus Single Transaction
Same as above, but also backup the database and restore it in a single transaction. Which can lock some tables and cause issues for any writes you’re trying to do during backup time.
mysqldump --single-transaction --add-drop-database --quick your_database | gzip -c > /home/your_database.sql.gz
Bzip Instead of Gzip.
Same as above, but use bzip for compression. Bzip is slower compression so backup will take longer but results in a smaller file.
mysqldump --single-transaction --quick --add-drop-database your_database | bzip2 -c > /home/your_database.sql.bz2
Restore SQL
mysql your_database < /home/your_database.sql
Restore Gzip
gzip -d /home/your_database.sql.gz; mysql your_database < /home/your_database.sql
Restore Bzip
bzip -d /home/your_database.sql.bz2; mysql your_database < /home/your_database.sql
