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
- Log into MySQL with an administrator account
- 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';
- To apply our new permissions you will need to flush the old ones.
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.
- Become Root
- Open crontab
- Add the following line
0 4 * * * /root/backup_script.sh
- Save the changes and exit the editor