Create a read-only backup account for MySQL

Your data is important and it needs to be protected. Keeping a regular backup schedule means you will need to automate the process. In order to do so, you are likely running a script to dump the database, leaving your credentials vulnerable to be being exposed. Limit your exposure by using a read-only account to perform your backups

Rather than using an admin account or one with write permissions, your data is exposed to being changed.Providing a read-only user to perform your database backups limits your exposure to being attacked.

Creating the Read-Only Account

1. Log into MySQL with an administrator account
2. Create a new user with LOCK TABLES and SELECT permissions for the database you want to be protected.

   GRANT LOCK TABLES, SELECT ON DATABASE_NAME.* TO 'BACKUP_USER'@'%' IDENTIFIED BY 'PASSWORD';

3. To apply our new permissions you will need to flush the old ones.

   FLUSH PRIVILEGES;

Automating Backup Script

The following is a simple script to dump a database to the local filesystem. It will retain a history of the 10 most recent backups. Anything older will be purged, to keep storage requirements down.

#!/bin/bash

# Set the backup date
BACKUP_DATE=`date +%Y-%m-%d`
BACKUP_DIR=/var/mysql_backups
BACKUP_HISTORY=10

# Dump the database
mysqldump -u BACKUP_USER -p'PASSWORD' DATABASE_NAME > $BACKUP_DIR/dump_$DATE.sql

# Remove all but the latest backups
cd $BACKUP_DIR
ls -t | tail -n +$BACKUP_HISTORY | xargs rm --

Ensure that the backup file can only be read, written and executed by an administrator or backup owner. For example, only permit root access to the file.

chcon 0600 backup_script.sh

Scheduling the Automated Backup Job

Automating your backup jobs using a script is as simple as creating a cron job. The frequency you run the backup job depends on how often your data changes. For this example, we will perform a daily backup at 4:00 AM.

1. Become Root

   sudo -s

2. Open crontab

   crontab -e

3. Add the following line

   0 4 * * * /root/backup_script.sh

4. Save the changes and exit the editor