Backup a MySQL Database

By: Luke Rawlins Dec 4, 2016 | 5 minutes read
Share this:

Tags: Linux, MariaDB, MySQL

database-mysql-svg 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 mysqldump command 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.

  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 means use your username; so cd /home/ 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.

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.

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.

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 using your favorite text editor.

It should look something like this:

[mysqldump]

user=root
password=your_db_root_password_here

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

chmod 600 .my.cnf

If your password has a “#” in it you will want to put your entire password in quotes, otherwise the # will be read as a comment and the password will be truncated.

For example: password="Thishasa#init" If you had tried to use this password without quotes it would be read by mysql as “Thishasa” which would make authentication impossible.

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 and -p 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

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.

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.

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>.sql

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.

Related Posts


Find services that require a restart

Ubuntu offers a live patching utility that allows kernel patches to be installed without requiring a system restart to be applied. Read more about online patching in this post about patching. That said, in many cases other services or processes on your system may need to be restarted after an upgrade. Finding services that need to be restarted in Ubuntu Install debian-goodies sudo apt update sudo apt install debian-goodies Now just run Read more

Change the Default Text Editor in Ubuntu

Change the Default Text Editor in Ubuntu So I’m a huge advocate of Ubuntu. It has long term support releases, more packages than you would ever need, free online unattended patching, and you always have an in-place upgrade path to the next LTS version. What more could you ask for? I’d like to ask that nano lose its privileged status as the default text editor! When making changes to sudoers, passwd, or group files you should really be using the built-in tools visudo, vipw, and vigr. Read more

Free SSL Certificate with Let’s Encrypt

Free SSL Certificate with Let’s Encrypt If you have ever installed an SSL certificate you know that it can be a tedious process. Let’s Encrypt makes this easy. Just call the letsencrypt command from the terminal and point it at your domain. Securing your website with a valid ssl certificate from a recognized and trusted vendor shows your website visitors that information transmitted between your site and their browser is encrypted. Read more


Contact

If you’d like to get in touch, contact with me via email - or follow on Twitter.

[email protected]