How to automatically backup mysql database using mysqldump?

It's a really good idea to use a least-privilege approach to most system administration tasks, and especially automated ones. This post describes using a "read only" MySQL user to handle backing up MySQL databases.

We use mysqldump to backup our databases on a regular basis, using scripts like this one:

#!/bin/sh

DIR=/backup/mysql/
DATESTAMP=$(date +%Y%m%d)
DB_USER=backup
DB_PASS='readonly'

# remove backups older than $DAYS_KEEP
DAYS_KEEP=30
find ${DIR}* -mtime +$DAYS_KEEP -exec rm -f {} \; 2> /dev/null

# create backups securely
umask 006

# list MySQL databases and dump each
DB_LIST=`mysql -u $DB_USER -p"$DB_PASS" -e'show databases;'`
DB_LIST=${DB_LIST##Database}
for DB in $DB_LIST;
do
  FILENAME=${DIR}${DB}-${DATESTAMP}.sql.gz
  mysqldump -u $DB_USER -p"$DB_PASS" --opt --flush-logs $DB | gzip > $FILENAME
done

You'll note that this script uses the user 'backup' to do the dumping. This is because our production servers grant potentially dangerous permissions (such as DROP TABLE) on a per-database basis. In order to run an automated backup, however, we need a single user that has just enough permissions to read from all the databases, but not enough to pose a risk to them.

The MySQL permissions required for the script above are SHOW DATABASES, SELECT, LOCK TABLES, and RELOAD. Grant them by entering the mysql command line and issuing these commands (choosing a better password than 'readonly' of course)

GRANT SHOW DATABASES, SHOW VIEW, SELECT, LOCK TABLES, RELOAD ON *.* to backup@localhost
 IDENTIFIED BY 'readonly';
FLUSH PRIVILEGES;

You can now back up all your databases by way of a single MySQL account that has just enough access to do the job, and not enough to cause significant harm. Which is what least-privilege access is all about.

Credit to: Stevem