Linux: Back Up a MySQL Database From the Command Line

If your website, app, or business depends on a database, one very important step you must take is backing up that data. Without a solid backup, you could find yourself in dire straits with corrupted data and no way to replace it.

Fortunately, backing up MySQL databases is not nearly as hard as you might think, and the required tools are all built in.

Imagine logging into that app or site and seeing a failed connection to a database or (worse) corrupt data. Or maybe you need to migrate your database to a new server without losing a scintilla of information.

That’s important, and it’s a skill you need to know.

That’s why I’m here: to show you how to back up that MySQL database.

Are you ready for this?

Let’s get database-y.

What You’ll Need for This

For this tutorial, you’ll want to have a MySQL database to back up and a user with admin privileges. I’ll demonstrate the process on an Ubuntu server, so the user in question will need to have sudo rights. I will also show you how to back up a MariaDB database.

Backing up a MySQL Database

Before you run the backup, you’ll need to know the precise name of your database. To do that, first access the MySQL console with the command:

sudo mysql -u root -p

Once at the console, list your databases with the command:

SHOW DATABASES;

You should see the database you want to back up in the list. Let’s say the database is called `newstack`. Let’s back that up.

Exit from the MySQL console with:

EXIT;

You can now use the mysqldump command to back up the database like so:

The above command will dump the contents of newstack into newstack-backup.sql.

Restoring That Backup

Let’s say you’ve migrated to a new server and want to restore the `newstack` database. To do that, log into the new server (with MySQL properly installed and set up) and issue the command:

The data from `newstack-backup.sql` should now be found in the newstack database.

How To Automate the Backup

You don’t want to have to remember to back up the database manually, so let’s make this happen automatically with the help of cron.

We’ll set the backup to run every day at 1 a.m. To do this, open your crontab file for editing with the command:

crontab -e

At the bottom of that file, we’ll add the line:

Where:

• PASSWORD is your MySQL root user password.
• USER is your username on the local Linux system.

Save and close the file. Now, your MySQL database will be backed up every day at 1 a.m. into the /home/USER directory. You can modify the above command to dump the backup into whatever directory you choose.

One thing to keep in mind is that the crontab entry will overwrite the backup every night, so you could create another crontab job to then move and rename the database file. Such a crontab entry might look like this:

That would rename the file from `newstack-backup.sql` to `newstack-backup-YYYYMMDD.sql` (where YYYY is the year, MM is the month, and DD is the day).

How to Back Up a MariaDB Database

A derivative of MySQL, MariaDB has a built-in backup tool called `mariadb-dump` that’s pretty easy to use as well. Let’s say we want to back up a database with the same name. For this, you’ll need a MariaDB user who has rights to the database to be backed up.

With that in place, you could back up the newstack database like this:

Where:

• PASSWORD is the MariaDB admin user password.
• USER is your Linux username.

You can then restore the backup like so:

How To Automate the MariaDB Backup

This is done in the same way the MySQL backup is handled. Open your crontab file for editing with:

crontab -e

If you want the MariaDB backup to run at 1 a.m., the entry would look like this:

• PASSWORD is your MariaDB root user password.
• USER is your username on the local Linux system.

You can then create another cron job that moves and renames the backup file the same way you did above with MySQL.

Creating a solid backup system for your databases is as important as creating a standard data backup. And although you might think it’s easy to simply depend on a regular backup process to include your databases, using the methods shown above ensures that the data will not only be backed up, but also easily restored. That peace of mind cannot be beat.