MySQL 备份

浏览数：20 / 时间：2015年06月12日

http://rimuhosting.com/howto/mysqlbackup.jsp

Automated MySQL Database Backup

Version 1

Want to backup your MySQL databases to another machine on a nightly basis?

Then create a /etc/cron.daily/mysqlbackup.sh job like this:

mysqldump --compress -u root -p$pw -h $currenthost --add-drop-table --extended-insert --quote-names --databases db1 db2| mysql -u root -p$pw -h $remotehost

Run chmod +x /etc/cron.daily/mysqlbackup.sh. And change the $ parameters to the appropriate ‘real‘ values.

Or if you are not backing up to a separate host, run:

mysqldump -u root -p$pw -h $currenthost --add-drop-table --extended-insert --quote-names --databases db1 db2 > /var/log/mysql.backup.$(date +"%Y%m%d").sql

Version 2

This one is slightly more advanced. It‘s a bash script that will basically do the same as above but also only keep a certain number of old copies.

Note that this script is prepared to backup as the root user, if you are going to be running this as a non root user you should modify paths to reflect that.

#!/bin/sh

# List of databases to be backed up separated by space
dblist="db_name_1 db_name_2 db_name_3 etc."

# Directory for backups
backupdir=/root/mysql_dumps

# Number of versions to keep
numversions=4

# Full path for MySQL hotcopy command
# Please put credentials into /root/.my.cnf
#hotcopycmd=/usr/bin/mysqlhotcopy
hotcopycmd="/usr/bin/mysqldump --lock-tables --databases"

# Create directory if needed
mkdir -p "$backupdir"
if [ ! -d "$backupdir" ]; then
   echo "Invalid directory: $backupdir"
   exit 1
fi

# Hotcopy begins here
echo "Dumping MySQL Databases..."
RC=0
for database in $dblist; do
   echo
   echo "Dumping $database ..."
   mv "$backupdir/$database.gz" "$backupdir/$database.0.gz" 2> /dev/null
   $hotcopycmd $database | gzip > "$backupdir/$database.gz"

   RC=$?
   if [ $RC -gt 0 ]; then
     continue;
   fi

   # Rollover the backup directories
   rm -fr "$backupdir/$database.$numversions.gz" 2> /dev/null
   i=$numversions
   while [ $i -gt 0 ]; do
     mv "$backupdir/$database.`expr $i - 1`.gz" "$backupdir/$database.$i.gz" 2> /dev/null
     i=`expr $i - 1`
   done
done

if [ $RC -gt 0 ]; then
   echo "MySQL Dump failed!"
   exit $RC
else
   # Hotcopy is complete. List the backup versions!
   ls -l "$backupdir"
   echo "MySQL Dump is complete!"
fi
exit 0

So this system has three parts. First is the file that holds the credentials, e.g. /root/.my.cnf and it‘s in standard mysql config format like so:

# cat /root/.my.cnf [client] user=root password=whatever

Second is the script itself, as listed above. Enter the names of your dbs, how many copies to keep, and where to put them.

Third is the crontab entry that will run this script for you. For example, once per day:

# (Use to post in the top of your crontab)
# ----------------- minute (0 - 59)
# |  -------------- hour (0 - 23)
# |  |  ----------- day of month (1 - 31)
# |  |  |  -------- month (1 - 12)
# |  |  |  |  ----- day of week (0 - 7) (Sunday=0 or 7)
# |  |  |  |  |
# *  *  *  *  *  command to be executed

35 01 * * * /root/mysql_backup.sh

完！

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

MySQL 备份