Skip to content

How to backup databases on a server

In order to ensure that our applications are resilient in the case of an catastrophic bug, we regularly back up databases on both staging and production servers. Code should not need to be backed up, as it is held on GitLab, and can be redeployed by re-running a pipeline.

Create a new user for backups

For security reasons, we don't want to use the root user to perform back-ups. So, we create a new user with minimal permissions that will be called backup. The $HOME directory for the new user should be /var/backups and its shell should be set to /usr/sbin/nologin.

Start by generating a random password for the new user:

date | md5sum

Make sure you write the password down somewhere safe -- do NOT put it online anywhere!

Creating a new user

On Ubuntu you should be able to find a user called backup, which has no $HOME, in /etc/passwd.

If you are not using Ubuntu, you may need to create a new user:

sudo adduser --system --disabled-password backup

On CentOS, where adduser is not available, you will need to use useradd:

sudo useradd -m -d /var/backups -s /usr/sbin/nologin backup

and set its password, using the string you generated above:

$ sudo passwd backup
Changing password for user backup.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
$

Make sure that the new home directory has the correct ownership and permissions:

chmod +rx /var/backups
chown root.root /var/backups

Create a read-only mysql user

On Ubuntu, you should find that MySQL uses UNIX sockets to authenticate. This means that you should be able to run sudo mysql -uroot and use the MySQL CLI without a password.

If you are not using Ubuntu, you may need to create a separate MySQL account for backups:

mysql> GRANT LOCK TABLES, SELECT ON *.* TO 'backup'@'localhost' IDENTIFIED BY 'PASSWORD';
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> Bye

where PASSWORD is the plain text password that you generated in the previous step.

Create a .my.cnf file in /root/

So that the new user can run mysqldump to write out a database to disk, you need to place a configuration file in the home directory of the root user:

sudo su
cd
umask 0577
sudo tee .my.cnf << EOF
[mysqldump]
user=backup
password=PASSWORD
EOF

where PASSWORD is the plain text password that you generated in the steps above.

Before you move on, check that the file has the file has the right permissions, then go back to your own user:

# ls -lh .my.cnf
-r--------. 1 root root 67 Jan 25 18:53 .my.cnf
# # Press Ctrl+d
$

Create a cron job to performs the backups

Databases should be backed up regularly. We have many servers, and so we don't have time to log into each one and back up all the databases manually. Instead, we use a cron job to automate the backups on each machine. By convention, we back up all databases on each server daily.

Before you create a cron job, you need to know the name of the database for your web application. Find out where the current version of your app is on disk. Try /srv/http/SITE.aston.ac.uk/APP/TOKEN on Centos or /var/www/SITE.DOMAIN.com/APP/TOKEN/ on Ubuntu. Go to the relevant directory and look inside its .env file, which specifies the environment variables that the application needs (similar to the variables that GitLab pipelines use). The name of the database will be on a line that starts DB_DATABASE=....

Go to the /etc/cron.daily/ directory, and add this file:

#!/bin/sh

# Rotation is done through logrotate.
# Username and password can be found in /root/.my.cnf
umask 0577
mysqldump --no-tablespaces DB_DATABASE | gzip -9c > /var/backups/DB_DATABASE.sql.gz

# Add similar lines here if there is more than one database on
# the server (e.g. production, staging).

Note that newer versions of MySQL require --no-tablespaces.

Then make the file executable:

sudo chmod +x backup

Before you move on, check that the file permissions are correct:

$ ls -lh backup
-rwxr-xr-x. 1 root root 169 Jan 25 18:56 backup
$

and run the script to check that it works:

./backup
ls -lh /var/backups/

If you want to download the resulting backup file, copy it to your home directory on the server and run chmod +r DB_NAME.sql.gz before downloading with scp.

Rotating logs

We will be creating one backup file every day, for every database on the server. If we don't take care to manage our back-up files, we will eventually run out of disk space. To take care of this, we use a tool called logrotate, which was originally intended to manage log files.

Imagine you have a log file called /var/myservice/error.log. logrotate would move the file every day, week, month (or whatever schedule you set), so that you might end up with files: error.log, error.log.1, error.log.2, error.log.3, error.log.4. Each time the logs are rotated, error.log.4 will be deleted, error.log.3 will become error.log.4, and so on. The new error.log will be empty.

Create a file in /etc/logrotate.d/ called backups and place the rotate script inside:

cd /etc/logrotate.d
sudo tee backups << EOF
/var/backups/*.sql.gz
{
        create 440 root root
        rotate 21
        daily
        missingok
        notifempty
        nocompress
        dateext
        sharedscripts
}
EOF

Before you move on, check that the file permissions are correct:

$ ls -lh backups
-rw-r--r-- 1 root root 167 Oct 25 11:57 backups

and check that the rotation completes without raising an error:

sudo logrotate backups

SELinux in CentOS

If you are using CentOS, by default SELinux (Security Enabled Linux) will prevent logrotate from accessing files in /var/, because they have the wrong security context. You can see (and compare) the security context of the files in /var/log and /var/backups like this:

ls --scontext /var/log
ls --scontext /var/backups

To fix this, you need to change and then reload the security context of the relevant files. Wait until you have at least one backup file before you do this (perhaps by forcing cron.daily to run, or waiting over night). Then run semanage:

sudo semanage fcontext -a -t var_log_t /var/backups
sudo semanage fcontext -a -t var_log_t /var/backups/FILE1
sudo semanage fcontext -a -t var_log_t /var/backups/FILE2
sudo semanage fcontext -a -t var_log_t /var/backups/...

sudo semanage fcontext -a -t var_log_t "/var/backups(/.*)?"

Then reload the security context:

sudo restorecon /var/backups
sudo restorecon /var/backups/FILE1
sudo restorecon /var/backups/FILE2
sudo restorecon /var/backups/...

Checking the backup has worked

Now you have set up your database backups, you should wait a day or two and check that the backup worked.

On the server, check the contents of the /var/backups directory, and copy the latest backup to your home directory:

cd
sudo cp /var/backups/DB_NAME.sql.gz .
sudo chmod +r DB_NAME.sql.gz

From your development machine (i.e. not on the server) copy the backup over, and unzip it:

scp USERNAME@SERVER_HOSTNAME:~/DB_NAME.sql.gz .
gunzip DB_NAME.sql.gz

Next, start mysql and create the database, if it doesn't already exist:

$ sudo mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.

...

mysql> CREATE DATABASE DB_NAME;
Query OK, 1 row affected (0.00 sec)

mysql> Bye

Then import the SQL dump:

sudo mysql -u root DB_NAME < DB_NAMEsql

Then start mysql and check the imported data:

$ sudo mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.

...

mysql> USE DB_NAME;
mysql> SHOW TABLES;

...

Troubleshooting

Debugging cron

If you have edited /etc/crontab be sure to check whether /etc/anacrontab might also need editing.

Remember that scripts inside /etc/cron.daily and similar directories are run in alphabetical order. If you want to be certain that scripts are running in a particular order (e.g. backups run before logrotate) then rename the scripts with numbers at the start of their names:

$ ls /etc/cron.daily
0yum-daily.cron  1backup.cron  2logrotate.cron

If you are not sure whether cron.daily is running correctly, debugging is much easier if you move your scripts to /etc/cron.hourly to get more frequent debug output.

You should also set up Postfix and make sure that /etc/crontab and /etc/anacrontab contain the line:

MAILTO=tech@beautifulcanoe.com

so that debug output is read by an administrator and does not just sit on the server.

Debugging logrotate

To debug logrotate, start by checking the status file in /var/lib/logrotate/logrotate.status which will tell you when each log was last rotated.

The file /etc/cron.daily/logrotate.cron calls the logrotate script directly. Editing that file to call the script with the -v switch will cause verbose output to be generated. If cron is set up to send an external email (see above), this will give you useful output each time the logs are rotated.

logrotate options: nocompress and delaycompress

Note that we have used the nocompress option in the logrotate script above. If you do choose to compress logs, make sure that you also turn on the delaycompress option. That way you will avoid this bug, which causes error messages such as:

/etc/cron.daily/logrotate:

gzip: stdin: file size changed while zipping