authors: [“Luke Rawlins”]
date: 2016-12-04
draft: false
title: Backup a MySQL Database
description: “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.”
url: /backup-mysql-database/
tags:
– Linux
– MariaDB
– MySQL
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.
Contents
- Review the command
- Convert to script
- Run as nightly cron job
- 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.
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 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 —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 using your favorite text editor.
It should look something like this:
[mysqldump]
user=root
password=yourdbrootpasswordhere
Change permissions on .my.cnf to read/write only for yourself
chmod 600 .my.cnf
NOTE: 06-01-18
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.
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 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//.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//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_.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.