Backing up MySQL / MariaDB with mysqldump

This method is as far as I know the quickest option to backup databases – and the restore (in case ever needed) is as easy as the backup and works for sure.

Backing up a single database

To backup a single database use

mysqldump -u USERNAME -p DATABASE-NAME > DATABASENAME-$(date +%F).sql

You will be asked for the password and the sql file will be stored right from where you are in the file system. Be sure to replace the names in CAPITALS with the real user / database name for example

mysqldump -u root -p devolutions > devolutions-$(date +%F).sql

Backing up the whole DatabaseManagementSystem

To backup all databases use:

mysqldump --all-databases --single-transaction --quick --lock-tables=false > full-backup-$(date +%F).sql -u root -p

Restoring

Let’s hope you will never need it – but here is how to restore a database with mysqldump. Navigate to the folder where you store the backed up database sql files. The execute the following

mysql -u USERNAME -p DATABASE-NAME < BACKUP-NAME.sql

Restoring the whole DatabaseManagementSystem

mysql -u root -p < full-backup.sql