MySQL automatic backup script

Http://www.51cto.com/html/2005/1028/9851.htm

You can put this script into crontab and execute it once every morning. The script is automatically backed up once at most once every day, and only the backups of the last five days are retained on the server.

#! /Bin/bash # This is a shellscript for auto DB Backup # powered by aspbiz #2004-09 # setting # Set the database name, database login name, password, backup path, and Log Path, data File Location # And backup mode # by default, the backup mode is tar, mysqldump, and mysqldotcopy # by default, use root (empty) to log on to the MySQL database, back up to/root/dbxxxxx. tgz dbname = MySQL dbuser = root dbpasswd = backuppath =/root/logfile =/root/DB. log dbpath =/var/lib/MySQL/# backupmethod = mysqldump # backupmethod = mysqlhotcopy # backupmethod = tar # setting end n Ewfile = "$ backuppath" DB $ (date + % Y % m % d ). tgz dumpfile = "$ backuppath" DB $ (date + % Y % m % d) oldfile = "$ backuppath" DB $ (date + % Y % m % d -- date = '5 days ago '). tgz echo "-----------------------------------------" >>$ logfile echo $ (date + "% Y-% m-% d % H: % m: % s")> $ logfile echo "------------------------"> $ logfile # delete old file if [-F $ oldfile] Then Rm-F $ oldfile> $ logfile 2> & 1 echo "[$ oldfile] delete old File success! ">>$ Logfile else echo" [$ oldfile] No old backup file! ">>$ Logfile Fi if [-F $ newfile] Then ECHO" [$ newfile] the backup file is exists, can't backup! ">>$ Logfile else case $ backupmethod in mysqldump) if [-Z $ dbpasswd] Then mysqldump-U $ dbuser -- opt $ dbname> $ dumpfile else mysqldump-U $ dbuser-p $ dbpasswd -- opt $ dbname> $ dumpfile fi tar czvf $ newfile $ dumpfile >>$ logfile 2> & 1 echo "[$ newfile] backup success! ">>$ Logfile Rm-RF $ dumpfile; mysqlhotcopy) rm-RF $ dumpfile mkdir $ dumpfile if [-Z $ dbpasswd] Then mysqlhotcopy-U $ dbuser $ dbname $ dumpfile> $ logfile 2> & 1 else mysqlhotcopy-U $ dbuser- p $ dbpasswd $ dbname $ dumpfile >>$ logfile 2> & 1 fi tar czvf $ newfile $ dumpfile >>>$ logfile 2> & 1 echo "[$ newfile] backup success! ">>$ Logfile Rm-RF $ dumpfile; *)/etc/init. d/mysqld stop>/dev/null 2> & 1 tar czvf $ newfile $ dbpath $ dbname> $ logfile 2> & 1/etc/init. d/mysqld Start>/dev/null 2> & 1 echo "[$ newfile] backup success! ">>$ Logfile; esac fi echo" ------------------------------------------- ">>> $ logfile