// you’re reading...

Linux, Unix, and Solaris

Backing up with mysqldump part deux

MYSQLDUMP is a utility for outputting database contents into text files. This is nice, but has many configuration options. One, often missed detail is the specific permissions required to accomplish the job. I made a note on the mysql manual (bottom of page) to illustrate what I consider to be a best practice for automated backup user accounts. Here is the URL: http://dev.mysql.com/doc/refman/5.1/en/grant.html

In case they remove it for some reason, I wrote:
I thought I’d add that if you script out your backups, you’ll want to create a user with the ‘LOCK TABLES’ privilege for occasions when you’re doing complete backups.

E.g.
In mysql:

mysql> GRANT SELECT,LOCK TABLES,SHOW VIEW,TRIGGER ON *.* TO backuprobot@'localhost' IDENTIFIED BY 'password'; exit;

In your bash shell:

# mysqldump -c --routines --triggers --all-databases -u backuprobot -ppassword | gzip > all.sql.gz

Once you have a created a backup user account, then create a script to automate the obvious. Here’s a down and dirty example.

#/bin/bash
#
# Fast mysqldump script for nightly backups.
# by Randy Melder (c)
# http://randymelder.com/
#
USERNAME=backuprobot
PASSWORD=password
HOST=localhost
PORT=3306
LOGFILE=/var/log/mysqldumps.log
PIDFILE=/var/run/mysqldumps.pid
BACKUPDIR=/var/log/backups
#
# # # # # # # # # # DO NOT EDIT BELOW THIS LINE # # # # # # # # # # # # 
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
#
# Get the date
NOW=`date +"%Y%m%dT%H%M%S"`
if [ -e $PIDFILE ]; then
echo "[error] $NOW $PIDFILE already exists, exiting." >> $LOGFILE
exit
fi
#
if [ ! -d $BACKUPDIR ]; then
echo "[error] $NOW $BACKUPDIR does not exist, exiting." >> $LOGFILE
exit
fi
cd $BACKUPDIR
#
touch $LOGFILE
#
echo $$ > $PIDFILE
echo "[normal] $NOW Process $$ starting, getting schema list…" >> $LOGFILE
#
SCHEMA=`mysql -u $USERNAME -p$PASSWORD -s -h $HOST -P$PORT -e "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA"`
#
echo "[normal] $NOW Process $$ starting schema back up." >> $LOGFILE
#
for dbname in $SCHEMA; do 
NOW=`date +"%Y%m%dT%H%M%S"`
echo "[normal] $NOW Backing up $BACKUPDIR/$dbname" >> $LOGFILE
mysqldump -c -i --routines --triggers --opt --force --create-options \
--add-drop-database -u $USERNAME -p$PASSWORD -h $HOST -P$PORT $dbname \
| gzip > $dbname.$NOW.sql.gz; 
done;
#
NOW=`date +"%Y%m%dT%H%M%S"`
echo "[normal] $NOW Process $$ completed, finalizing." >> $LOGFILE
#
rm -f $PIDFILE 
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
#                                                                      
#                   |               |        |                          
#,---.,---.,---.,---|,   .,-.-.,---.|    ,---|,---.,---. ,---.,---.,-.-.
#|    ,---||   ||   ||   || | ||---'|    |   ||---'|     |    |   || | |
#`    `---^`   '`---'`---|` ' '`---'`---'`---'`---'`    o`---'`---'` ' '
#                    `---'                                              
#License: use at your own risk!

Discussion

No comments for “Backing up with mysqldump part deux”

Post a comment

Help support my site and buy a domain name at http://domainsemailhosting.com/