mysql backup mysqldump

How to Backup and Restore with mysqldump

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