本文转载自:http://www.linuxpromagazine.com/Online/Blogs/Productivity-Sauce/Back-up-MySQL-Databases-with-a-Simple-Bash-Script

(私人收藏,以备不时之需)

Back up MySQL Databases with a Simple Bash Script

If you host your own blog or any Web-based application running on the Apache/MySQL/PHP stack, you should have a backup system in place for keeping data stored in MySQL databases safe. There are several solutions that can help you with that, but nothing beats a simple Bash script I stumbled upon in a blog post comment. Here is the script in all its beauty:

 

  1. #!/bin/bash 
  2.  
  3. NOW=`date +"%Y-%m"`; 
  4. BACKUPDIR="location/of/your/backup/dir/$NOW"
  5.  
  6. ### Server Setup ### 
  7. #* MySQL login user name *# 
  8. MUSER="user"
  9.  
  10. #* MySQL login PASSWORD name *# 
  11. MPASS="pass"
  12.  
  13. #* MySQL login HOST name *# 
  14. MHOST="your-mysql-ip"
  15. MPORT="your-mysql-port"
  16.  
  17. # DO NOT BACKUP these databases 
  18. IGNOREDB=" 
  19. information_schema 
  20. mysql 
  21. test 
  22.  
  23. #* MySQL binaries *# 
  24. MYSQL=`which mysql`; 
  25. MYSQLDUMP=`which mysqldump`; 
  26. GZIP=`which gzip`; 
  27.  
  28. # assuming that /nas is mounted via /etc/fstab 
  29. if [ ! -d $BACKUPDIR ]; then 
  30.   mkdir -p $BACKUPDIR 
  31. else 
  32.  : 
  33. fi 
  34.  
  35. # get all database listing 
  36. DBS="$(mysql -u $MUSER -p$MPASS -h $MHOST -P $MPORT -Bse 'show databases')" 
  37.  
  38. # SET DATE AND TIME FOR THE FILE 
  39. NOW=`date +"d%dh%Hm%Ms%S"`; # day-hour-minute-sec format 
  40. # start to dump database one by one 
  41. for db in $DBS 
  42. do 
  43.         DUMP="yes"
  44.         if [ "$IGNOREDB" != "" ]; then 
  45.                 for i in $IGNOREDB # Store all value of $IGNOREDB ON i 
  46.                 do 
  47.                         if [ "$db" == "$i" ]; then # If result of $DBS(db) is equal to $IGNOREDB(i) then 
  48.                                 DUMP="NO";         # SET value of DUMP to "no" 
  49.                                 #echo "$i database is being ignored!"; 
  50.                         fi 
  51.                 done 
  52.         fi 
  53.  
  54.         if [ "$DUMP" == "yes" ]; then # If value of DUMP is "yes" then backup database 
  55.                 FILE="$BACKUPDIR/$NOW-$db.gz"
  56.                 echo "BACKING UP $db"; 
  57.                 $MYSQLDUMP --add-drop-database --opt --lock-all-tables -u $MUSER -p$MPASS -h $MHOST -P $MPORT $db | gzip > $FILE 
  58.         fi 
  59. done 

The best part is that you only need to specify a handful of parameters to make the script work. This includes BACKUPDIR (the destination for storing backups), MUSER (MySQL user), MPASS (MySQL user password), MHOST (the IP address of the MySQL server, e.g. localhost), and MPORT (the port the MySQL database is running on, default is 3306).

You can run the script manually, or you can set up a cron job which will perform backups on a regular basis. To do this, run the crontab -e command and add the following line (replace the sample path with the actual path and backup script name):

@daily /path/to/mysqlbackupscript.sh

Don't forget to make the script executable using the chmod a+x mysqlbackupscript.sh command.

 

Comments

Oh the horror

Mihai Jan 17, 2011 4:16pm GMT

The script has no validation on the dump, so you can continue to think that you have a good database and dump will fail as long as your information  , also if you have mysql 5 or higher --routing --triggers should be added.
 

A shorter script using regular expression support

Bilbo Jan 17, 2011 11:42am GMT

I didn't tried next code but using regular expression support in Bash the script can be made sorter with something like:

# DO NOT BACKUP these databases
IGNOREDB="#information_schema#mysql#test#"
...

for db in $DBS
do
if [ "${IGNOREDB}" =~ "#${db}#" ]; then continue; fi
FILE="$BACKUPDIR/$NOW-$db.gz";
echo "BACKING UP $db";
$MYSQLDUMP --add-drop-database --opt --lock-all-tables -u $MUSER -p$MPASS -h $MHOST -P $MPORT $db | gzip > $FILE
done

Avoiding the second inner loop and the $DUMP variable.

My two cents!
 

Backing up large databases

rcw Jan 17, 2011 4:30am GMT

This script will fail for large databases because it'll exceed typical max_packet_size settings.

You'll want to add something like --max_allowed_packet=500M to the mysqldump command line to work around that.