MYSQLDUMP is a utility for outputting database contents into text files. This is nice, usage 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 ON *.* TO [email protected]'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
#
# # # # # # # # # # DO NOT EDIT BELOW THIS LINE # # # # # # # # # # # # 
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #  Ur  
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
# #  and  # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #  cheap mlb jerseys  # # # 
#
if [ -e $PIDFILE ]; then
    echo "[error] $NOW $PIDFILE already exists, exiting." >> $LOGFILE
    exit
fi
#
touch $LOGFILE
# Get the date
NOW=`date +"%Y%m%dT%H%M%S"`
#
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 
    mysqldump -c -i --routines --triggers --opt --create-options --add-drop-database -u $USERNAME -p$PASSWORD -h $HOST -P$PORT $dbname
# | gzip > $dbname.$NOW.sql.gz; 
done;
#
echo "[normal] $NOW Process $$ completed, finalizing." >> $LOGFILE
#
rm -f $PIDFILE 
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #  cheap nfl jerseys  
# # # # # # # # # # # # # # # # # # # #  Your  # # # # # # # # # # # # # # # # 
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
# # # # # # # # # # # # #  PHP  # # # # # # # # # # # # # # # # # # # # # # # 
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 
# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
#                   Demonstration                                                      
#                   |               |        |                          
#,---.,---.,---.,---|,   .,-.-.,---.|    ,---|,---.,---. ,---.,---.,-.-.
#|    ,---||   ||   ||   || | ||---'|    |   ||---'|     |    |   || | |
#`    `---^`   '`---'`---|` ' '`---'`---'`---'`---'`    o`---'`---'` ' '
#                    `---'                              wholesale jerseys                   
#License: use at your own risk!