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
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 /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
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