Backup a MySQL Database

If you have a MySQL database working behind the scenes on your web site or app then creating and storing backup’s of that database can be vitally important to the operation of your business operations. A MySQL or MariaDB database uses the mysqldump command to create backups.

The mysqldumpcommand outputs a file that contains SQL statements that can be used to rebuild your database, with all of its data. Which could come in handy in the event of an unrecoverable crash or even to just move the database to a new server.

Following the steps in this guide should work on any distribution of Linux that is using MySQL or MariaDB.

Contents

1. Review the command
2. Convert to script
3. Run as nightly cron job
4. Restore database

Quick note:

When you see me use angle brackets <> it means you should alter the command to fit your needs. For example <user> means use your username; so cd /home/<user> should be cd /home/spidey for a user called spidey. Secondly all of the below commands should be run as a non-root user account.

Step 1 – Review the command

By default the mysqldump command will not build a SQL query to create or drop existing databases. So we will want to add some options to it in order to get the results we want.

mysqldump -uroot -p<root_password> testDB > myDB.sql

This command will output (to the myDB.sql file) all the SQL code required to rebuild all of the tables and data within the testDB database. Notice that there is no space between -u and root. The -u option in this command stands for user and -p is password again note that no spaces are needed between the option and the value. The one thing we do not get from this command is the ability actually create the database. So using this command to restore the database tables will only work if the database already exists on the server that you are restoring to.

Here’s what I would use instead:

mysqldump -uroot -p<root_password> --add-drop-table --databases testDB > myDB.sql

Adding the --add-drop-table --databasesto the command tells mysqldumpto build the statement with CREATE statements if the database doesn’t already exist, USE statements to then use the named database, and drop any tables that exist in that database before creating the new ones. This gives you a clean and full backup from the original database.

Step 2 – Use mysqldump in a backup script

Theoretically you could copy and paste the above command into a script to back up the database without alteration and it would work fine. A few problems, however, will need to be resolved in order to reduce the risk of dataloss, and to prevent malicious persons from getting access to your database.

Create a defaults file

The first step to securing our backup script is to pull the username and password out of the command while it’s running. With the above command inserted into a cron job anyone who runs ps aux during its execution will be able to see the root database password. We can avoid that by creating a file in our /home directory that contains the user/password details and pass that file into the command instead.

Change to your home directory

cd ~/

Create the “.my.cnf” file

vim .my.cnf
[mysqldump]
user=root
password=your_db_root_password_here

Change permissions on .my.cnf to read/write only for yourself

chmod 600 .my.cnf

 Update the command

With the .my.cnf file some distributions (I tested with Suse Enterprise 12 and Ubuntu) will automatically check for the existence of this file and we can remove the -u<user> and -p<password> options from the command. The updated command will look like this

mysqldump --add-drop-table --databases testDB > myDB.sql

Much shorter and far more secure. If your distribution doesn’t detect this file by default you can add

--defaults-file=/home/<user>/.my.cnf to the command string like this:

mysqldump --defaults-file=/home/<user>/.my.cnf --add-drop-table --databases testDB > myDB.sql

Use “date” to prevent over writes

We can’t put this in a script yet because the file name will remain the same every time we run the command. We will need to build our script with the ability to change the name based on date so that we can restore to a particular point in time.

We will use a variable to write part of the file name

mkdir scripts
mkdir backups
cd scripts

vi DBbackup.sh

Copy the following into this file to create a simple backup script that we can run everyday.

#!/bin/bash
today=$(date +"%m_%d_%Y")
mysqldump --add-drop-table --databases testDB > /home/user/backups/myDB_$today.sql

Next we need to add this to a cron job to run every night.

Step 3 – Run database backup as a cron job

As your user run the crontab -e command to bring up your users cron file. Add the following to the bottom of this file.

01 00 * * * /home/<user>/scripts/DBbackup.sh

This entry will run every day at 12:01 am and will produce a file in your home directory that contains the state of the database at the time it ran.

Step 4 – Restore the database

In the event that you need to restore this database MySQL makes this a pretty simple process. One command should be enough to recreate the database and insert all the data back into it.

mysql < myDB_<date>.sh

Depending upon how large your database is will determine how long this command takes to complete, however, once it is finished your database will be up and running in the same state that it was in when the backup was taken.

Luke has an RHCSA for Red Hat Enterpirse Linux 7 and currently works as a Linux Systems Adminstrator in Ohio.

This post, re-published here with permission, was originally published on Luke’s site here.