Backup MySQL/MariaDB Databases Using the Command Line

Even with automated backups of databases, which everyone should be using, sometimes you just need to quickly make a backup before making a change on your website. It’s always best to have an up-to-date backup on hand before making changes; even something as simple as a WordPress plugin update can break your website if it’s coded in such a way that conflicts with your environment.

I personally need to look at my documents in my company’s Google Drive when I haven’t done something in a while, as a refresher, before it clicks and I’m like “oh yeah, I remember that now.” And, admittedly, I need to do a little bit of house cleaning in there — it’s becoming a jumbled mess because I have so many files and documents that seem to be out of place. I can be unorganized at times.

As a little refresher for myself, I figured I’d also put this information in a public place for you to read as well. That, and I’ll easily be able to come back here and search for what I need (at least until I clean up those darn files).

How to Create a MySQL Backup

The command you’ll want to use is mysqldump, which will create a text file of the database that will allow you to recreate it completely. Of course, as MariaDB is a drop-in replacement to MySQL, all of the commands will be the same.

Backing up one MySQL database

To make a backup of just one database, use the command

sudo mysqldump -uroot -p your_datase_name > your_database_backup_name.sql.gz

Note that you can either make it a .sql file, or even compress it by adding .gz to the end of it. This will make it a smaller file, which is handy if your database is quite large.

Backing up multiple MySQL databases

To make a backup of more than one database, use

sudo mysqldump -uroot -p --databases first_database second_database third_database > databases_backup.sql.gz

The above will let you define which databases you want to back up and compress them all into a single backup.

Backing up all of your databases

And, finally, to make a backup of all of the databases on your installation, use

sudo mysqldump -uroot -p --all-databases > every_database.sql.gz

Of course, with all of the commands above, you can name your backup file whatever you’d like.

How to Restore Your Database

Should something go wrong and you need to restore your database, you’ll first want to create a new database to toss your backup dump back into. You can do this either by logging into MySQL first, or using mysqladmin. We’ll use the later as the example here.

sudo mysqladmin -uroot -p create yournewdatabase

You’ll be prompted to enter your password, then the new database will be created.

sudo gunzip < your_database_backup.sql.gz | mysql -uroot -p yournewdatabase

Note that we’re using gunzip here as the backup was compressed in the steps prior. If you didn’t compress your database, use

sudo mysql -uroot -p yournewdatabase < your_database_backup.sql

Alternatively, you can upload your database using cPanel as well, which is a very handy interface to have even if you’re comfortable with using the command line.

Now you’re locked and loaded with what you need to backup and restore your MySQL/MariaDB databases using the command line.