Create a MySQL user BackupAdmin that has only BackupAdmin permissions.
mysql -u root -p
GRANT SHOW DATABASES, SELECT, LOCK TABLES, RELOAD ON *.* to BackupAdmin@localhost
IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
===========================================
mkdir /mnt/local/sdb1/mysql_bkup
chown root:root /mnt/local/sdb1/mysql_bkup
chmod 700 /mnt/local/sdb1/mysql_bkup
drwx—— 2 root root 4096 Feb 24 13:46 mysql_bkup
vi /root/.my.cnf
[mysqldump]
user = BackupAdmin
password = password
vi /root/mysql.backup.sh #!/bin/sh DIR=/mnt/local/sdb1/mysql_bkup
DATESTAMP=$(date +%Y%m%d)
DB_USER=BackupAdmin
# remove backups older than $DAYS_KEEP
DAYS_KEEP=30
find ${DIR}/* -mtime +$DAYS_KEEP -exec rm -f {} \; 2> /dev/null # create backups securely
umask 077 # list MySQL databases and dump each
ENVS="test prod demo"
for DB in $ENVS ; do
echo "Backing up database: ${DB}"
#FILENAME=${DIR}/backup.${DB}.${DATESTAMP}.sql.gz
FILENAME=${DIR}/backup.${DB}.${DATESTAMP}.sql # mysqldump -u ${DB_USER} –single-transaction –opt –databases ${DB} | gzip > ${FILENAME}
mysqldump -u ${DB_USER} –single-transaction –opt –databases ${DB} > ${FILENAME}
done
chmod 700 /root/mysql.backup.sh
crontab -e
0 3 1,16 * * /root/mysql.backup.sh ## run @ 3AM on the 1st and 16th of every month