MySQL Backup

This post explores how your MySQL database can be backed up manually and automatically.

Backup from phpMyAdmin

1. Connect to phpMyAdmin
2. Select your database
3. Navigate to the Export tab
4. Select the export format (SQL is good enough)
5. Click Go
6. Download the .sql file, this is your backup.

Backup from command line

Check your access to mysql

1. Connect to mysql with mysql -u <username> --password, we assume the user is myuser here.
2. Enter the password for your user (myuser here).
3. If you have access to MySQL with this user/password, you can go the next step.

Note that you can also use mysql -u <username> --password <db_name> if you want to select a specific database.

Use mysqldump

To backup only the database named wallabag:

mysqldump -u myuser --password -p wallabag > /tmp/wallabag_backup.sql

To compress the database:

mysqldump -u myuser --password -p -C wallabag > /tmp/wallabag_backup.sql.tgz

To backup all your databases:

mysqldump -u myuser --password -p --all-databases > /tmp/all_databases_backup.sql

Automate with crontab and remote machine

Here is how we automate the backup process:

1. On the server, a cron job regularly generates the backups with mysqldump and places them in /home/backupuser.
2. From a remote machine, we connect to the server with the user backupuser and use rsync to copy the backup files.

The user backupuser is in a chroot jail so that he can only:

• use the rsync command
• use the files in /home/backupuser

For more information about how to setup the jail and the user see Automating backups with a Raspberry Pi.

In this section we will only see how to create the cron job and a user for backing up the database.

Database backup user

Since the backup will be done automatically, the password will be put in clear in configuration files. To limit the attack surface we create a database user with only read rights to our database.

To list the existing user in mysql, first connect with :

mysql -u <yourUser> --password

Then use the command :

SELECT User FROM mysql.user

To create a new user named backupuser use :

CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'password';

If you get the message ERROR 1819 (HY000): Your password does not satisfy the current policy requirements, you can disable the verifications with uninstall plugin validate_password;. To reenable it use INSTALL PLUGIN validate_password SONAME 'validate_password.so';

To list the privileges of our newly created user we use:

mysql> SHOW GRANTS FOR 'backupuser'@'localhost';
+------------------------------------------------+
| Grants for backupuser@localhost                |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'backupuser'@'localhost' |
+------------------------------------------------+
1 row in set (0,00 sec)

We can see that our user only has the privilege USAGE on all databases, which is normal. The USAGE privilege means no privilege.

Now we grant the read privilege to backupuser on our database with :

GRANT SELECT, LOCK TABLES ON mydatabase.* TO 'backupuser'@'localhost';

We repeat the operation for every database we need to backup.

List all databases with SHOW databases;

Creating the cron job

Log as root to edit the root’s cron:

sudo su
crontab -e

Enter the following to backup your database myDatabase every day at 23:00 in the folder /home/backupuser/backup/myDatabase (and assuming your password is password):

0 23 * * * DATE=$(date +'\%Y-\%m-\%d-\%H\%M'); mysqldump -u backupuser -ppassword -C myDatabase > /home/backupuser/myDatabase/${DATE}_backup_myDatabase.sql.tgz

Note that you need to escape the % character inside the cron editor when formatting the date. You also need to reference the DATE variable with ${DATE} and not $DATE otherwise your command will not execute correctly !

Creating a clean up job

We set the BACKUP environment variable to /home/backupuser/backup. This command deletes every file in /home/backupuser/backup/myDatabase except for the last one we created with our automatic backup.

LAST=$(find ${BACKUP}/myDatabase -type f -printf '\%f\n' | sort -nr | head -1); find ${BACKUP}/myDatabase ! -name "${LAST}" -type f -exec rm -f {} +

We can set up a cronjob with this command to clean up our backup files regularly.