1直接拷贝数据库文件
直接拷贝数据文件最为直接、快速、方便，但缺点是基本上不能实现增量备份。为了保证数据的一致性，需要在备份文件前，执行以下 SQL 语句：
FLUSH TABLES WITH READ LOCK;
也就是把内存中的数据都刷新到磁盘中，同时锁定数据表，以保证拷贝过程中不会有新的数据写入。这种方法备份出来的数据恢复也很简单，直接拷贝回原来的数据库目录下即可。
2使用mysqlhotcopy备份数据库
mysqlhotcopy 是一个 PERL 程序，最初由Tim Bunce编写。它使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp 来快速备份数据库。它是备份数据库或单个表的最快的途径，但它只能运行在数据库文件（包括数据表定义文件、数据文件、索引文件）所在的机器上，并且mysqlhotcopy 只能用于备份 MyISAM表。
本备份策略适合于小型数据库的备份，数据量不大，可以采用mysqlhotcopy程序每天进行一次完全备份。
备份策略布置：
（1）、安装DBD-mysql perl模块，支持mysqlhotcopy脚本连接到MySQL数据库。
shell> tar -xzvf DBD-mysql-4.005.tar.gz
shell> cd DBD-mysql-4.005
shell> unset LANG
shell>perl Makefile.PL -mysql_config=/usr/local/mysql/bin/mysql_config -testuser=root -testpassword=UserPWD
shell> make
shell> make test
shell> make install
（2）、设置crontab任务，每天执行备份脚本
shell> crontab -e
0 3 * * * /root/MySQLBackup/mysqlbackup.sh >/dev/null 2>&1
每天凌晨3:00执行备份脚本。
mysqlbackup.sh注释：
#!/bin/sh
# Name:mysqlbackup.sh
# PS:MySQL DataBase Backup,Use mysqlhotcopy script.
# Write by:i.Stone
# Last Modify:
2008-08-15
#
# 定义变量，请根据具体情况修改
# 定义脚本所在目录
scriptsDir=`pwd`
# 数据库的数据目录
dataDir=/usr/local/mysql/data/
# 数据备份目录
tmpBackupDir=/tmp/tmpbackup/
backupDir=/tmp/mysqlbackup/
# 用来备份数据库的用户名和密码
mysqlUser=root
mysqlPWD=111111
# 定义eMail地址
eMail=alter@somode.com
# 如果临时备份目录存在，清空它，如果不存在则创建它
if [[ -e $tmpBackupDir ]]; then
rm -rf $tmpBackupDir/*
else
mkdir $tmpBackupDir
fi
# 如果备份目录不存在则创建它
if [[ ! -e $backupDir ]];then
mkdir $backupDir
fi
# 清空MySQLBackup.log
if [[ -s MySQLBackup.log ]]; then
cat /dev/null >MySQLBackup.log
fi
# 得到数据库备份列表，在此可以过滤不想备份的数据库
for databases in `find $dataDir -type d | /
sed -e "s///usr//local//mysql//data" | /
sed -e "s/test//"`; do
if [[ $databases == "" ]]; then
continue
else
# 备份数据库
/usr/local/mysql/bin/mysqlhotcopy --user=$mysqlUser --password=$mysqlPWD -q "$databases" $tmpBackupDir
dateTime=`date "+%Y.%m.%d %H:%M:%S"`
echo "$dateTime Database:$databases backup success!" >>MySQLBackup.log
fi
done
# 压缩备份文件
date=`date -I`
cd $tmpBackupDir
tar czf $backupDir/mysql-$date.tar.gz ./
# 发送邮件通知
if [[ -s MySQLBackup.log ]]; then
cat MySQLBackup.log | mail -s "MySQL Backup" $eMail
fi
# 使用smbclientmv.sh脚本上传数据库备份到备份服务器
# $scriptsDir/smbclientmv.sh
smbclientmv.sh注释
#!/bin/sh
# Name:smbclientmv.sh
# PS:Move the data to Backup Server.
# Write by:i.Stone
# Last Modify:
2008-08-15
#
# 定义变量
# 备份服务器名
BackupServer="BackupServerName"
# 共享文件夹名
BackupShare="ShareName"
# 备份服务器的访问用户名和密码
BackupUser="SMBUser"
BackupPW="SMBPassword"
# 定义备份目录
BackupDir=/tmp/mysqlbackup
date=`date -I`
# Move the data to BackupServer
smbclient //$BackupServer/$BackupShare /
$BackupPW -d0 -W WORKGROUP -U $BackupUser /
-c "put $BackupDir/mysql-$date.tar.gz /
mysql-$date.tar.gz"
# Delete temp files
rm -f $BackupDir/mysql-$date.tar.gz(3)、恢复数据库到备份时的状态
mysqlhotcopy 备份出来的是整个数据库目录，使用时可以直接拷贝到 mysqld 指定的 datadir (在这里是 /usr/local/mysql/data/)目录下即可，同时要注意权限的问题，如下例：
shell> cp -rf db_name /usr/local/mysql/data/
shell> chown -R mysql:mysql /usr/local/mysql/data/ (将 db_name 目录的属主改成 mysqld 运行用户)
本套备份策略只能恢复数据库到最后一次备份时的状态，要想在崩溃时丢失的数据尽量少应该更频繁的进行备份，要想恢复数据到崩溃时的状态请使用主从复制机制(replication)。
3使用mysqldump备份数据库
mysqldump 是采用SQL级别的备份机制，它将数据表导成 SQL 脚本文件，在不同的 MySQL 版本之间升级时相对比较合适，这也是最常用的备份方法。mysqldump 比直接拷贝要慢些。
对于中等级别业务量的系统来说，备份策略可以这么定：第一次完全备份，每天一次增量备份，每周再做一次完全备份，如此一直重复。而对于重要的且繁忙的系统来说，则可能需要每天一次全量备份，每小时一次增量备份，甚至更频繁。为了不影响线上业务，实现在线备份，并且能增量备份，最好的办法就是采用主从复制机制(replication)，在 slave 机器上做备份。
备份策略布置：
（1）、创建备份目录
Shell> mkdir /tmp/mysqlbackup
Shell> mkdir /tmp/mysqlbackup/daily
（2）、启用二进制日志
采用 binlog 的方法相对来说更灵活，省心省力，而且还可以支持增量备份。
启用 binlog 时必须要重启 mysqld。首先，关闭 mysqld，打开 /etc/my.cnf，加入以下几行：
[mysqld]
log-bin
然后启动 mysqld 就可以了。运行过程中会产生 HOSTNAME-bin.000001 以及 HOSTNAME-bin.index，前面的文件是 mysqld 记录所有对数据的更新操作，后面的文件则是所有 binlog 的索引，都不能轻易删除。关于 binlog 的更详细信息请查看手册。
（3）、配置SSH密钥登录，用于将MySQL备份传送到备份服务器（如果备份服务器为Windows，请跳过此部）。
1）、在MySQL所在服务器（192.168.0.20）生成SSH密钥
[root@lab ~]# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): //直接回车
Enter passphrase (empty for no passphrase): //直接回车，不使用密码
Enter same passphrase again: //直接回车，不使用密码
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
c2:96:
9f
:2d:
5a
:8e:08:42:43:35:
2f
:85:5e:72:f8:
1c
root@lab
2）、在备份服务器（192.168.0.200）上创建目录，修改权限，并传送公钥。
[root@lab ~]# ssh 192.168.0.200 "mkdir .ssh;chmod 0700 .ssh"
The authenticity of host '192.168.0.200 (192.168.0.200)' can't be established.
RSA key fingerprint is 37:57:55:c1:32:f1:dd:bb:1b:
8a
:13:
6f
:89:fb:b8:9d.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.0.200' (RSA) to the list of known hosts.
root@192.168.0.200's password: //输入备份服务器的root密码
[root@lab ~]# scp .ssh/id_rsa.pub 192.168.0.200:.ssh/authorized_keys2
root@192.168.0.200's password:
id_rsa.pub 100% 218 0.2KB/s 00:00
3）、测试SSH登录
[root@lab ~]# ssh 192.168.0.200 //测试SSH登录
Last login: Fri Nov 16 10:34:02 2007 from 192.168.0.20
[root@lib ~]#
（4）、设置crontab任务，每天执行备份脚本
shell> crontab -e
#每个星期日凌晨3:00执行完全备份脚本
0 3 * * 0 /root/MySQLBackup/mysqlFullBackup.sh >/dev/null 2>&1
#周一到周六凌晨3:00做增量备份
0 3 * * 1-6 /root/MySQLBackup/mysqlDailyBackup.sh >/dev/null 2>&1
mysqlFullBackup.sh注释：
#!/bin/sh
# Name:mysqlFullBackup.sh
# PS:MySQL DataBase Full Backup.
# Write by:i.Stone
# Last Modify:
2008-8-15
#
# Use mysqldump --help get more detail.
#
# 定义变量，请根据具体情况修改
# 定义脚本目录
scriptsDir=`pwd`
# 定义数据库目录
mysqlDir=/usr/local/mysql
# 定义用于备份数据库的用户名和密码
user=root
userPWD=111111
# 定义备份目录
dataBackupDir=/tmp/mysqlbackup
# 定义邮件正文文件
eMailFile=$dataBackupDir/email.txt
# 定义邮件地址
eMail=alter@somode.com
# 定义备份日志文件
logFile=$dataBackupDir/mysqlbackup.log
DATE=`date -I`
echo "" > $eMailFile
echo $(date +"%y-%m-%d %H:%M:%S") >> $eMailFile
cd $dataBackupDir
# 定义备份文件名
dumpFile=mysql_$DATE.sql
GZDumpFile=mysql_$DATE.sql.tar.gz
# 使用mysqldump备份数据库，请根据具体情况设置参数
$mysqlDir/bin/mysqldump -u$user -p$userPWD /
--opt --default-character-set=utf8 --extended-insert=false /
--triggers -R --hex-blob --all-databases /
--flush-logs --delete-master-logs /
--delete-master-logs /
-x > $dumpFile
# 压缩备份文件
if [[ $? == 0 ]]; then
tar czf $GZDumpFile $dumpFile >> $eMailFile 2>&1
echo "BackupFileName:$GZDumpFile" >> $eMailFile
echo "DataBase Backup Success!" >> $eMailFile
rm -f $dumpFile
# Delete daily backup files.
cd $dataBackupDir/daily
rm -f *
# Delete old backup files(mtime>2).
$scriptsDir/rmBackup.sh
# 如果不需要将备份传送到备份服务器或备份服务器为Windows，请将标绿的行注释掉
# Move Backup Files To Backup Server.
#适合Linux（MySQL服务器）到Linux（备份服务器）
$scriptsDir/rsyncBackup.sh
if (( !$? )); then
echo "Move Backup Files To Backup Server Success!" >> $eMailFile
else
echo "Move Backup Files To Backup Server Fail!" >> $eMailFile
fi
else
echo "DataBase Backup Fail!" >> $emailFile
fi
# 写日志文件
echo "--------------------------------------------------------" >> $logFile
cat $eMailFile >> $logFile
# 发送邮件通知
cat $eMailFile | mail -s "MySQL Backup" $eMail
mysqlDailyBackup.sh注释：
#!/bin/sh
# Name:mysqlDailyBackup.sh
# PS:MySQL DataBase Daily Backup.
# Write by:i.Stone
# Last Modify:
2008-8-15
#
# 定义变量，请根据具体情况修改
# 定义数据库目录和数据目录
scriptsDir=`pwd`
mysqlDir=/usr/local/mysql
dataDir=$mysqlDir/data
# 定义用于备份数据库的用户名和密码
user=root
userPWD=111111
# 定义备份目录，每日备份文件备份到$dataBackupDir/daily
dataBackupDir=/tmp/mysqlbackup
dailyBackupDir=$dataBackupDir/daily
# 定义邮件正文文件
eMailFile=$dataBackupDir/email.txt
# 定义邮件地址
eMail=alter@somode.com
# 定义日志文件
logFile=$dataBackupDir/mysqlbackup.log
# 得到数据库所在主机的主机名
HOSTNAME=`uname -n`
#
echo "" > $eMailFile
echo $(date +"%y-%m-%d %H:%M:%S") >> $eMailFile
#
# 刷新日志，使数据库使用新的二进制日志文件
$mysqlDir/bin/mysqladmin -u$user -p$userPWD flush-logs
cd $dataDir
# 得到二进制日志列表
fileList=`cat $HOSTNAME-bin.index`
iCounter=0
for file in $fileList
do
iCounter=`expr $iCounter + 1`
done
nextNum=0
iFile=0
for file in $fileList
do
binLogName=`basename $file`
nextNum=`expr $nextNum + 1`
# 跳过最后一个二进制日志（数据库当前使用的二进制日志文件）
if [[ $nextNum == $iCounter ]]; then
echo "Skip lastest!" > /dev/null
else
dest=$dailyBackupDir/$binLogName
# 跳过已经备份的二进制日志文件
if [[ -e $dest ]]; then
echo "Skip exist $binLogName!" > /dev/null
else
# 备份日志文件到备份目录
cp $binLogName $dailyBackupDir
if [[ $? == 0 ]]; then
iFile=`expr $iFile + 1`
echo "$binLogName Backup Success!" >> $eMailFile
fi
fi
fi
done
if [[ $iFile == 0 ]];then
echo "No Binlog Backup!" >> $eMailFile
else
echo "Backup $iFile File(s)." >> $eMailFile
echo "Backup MySQL Binlog OK!" >> $eMailFile
# 如果不需要将备份传送到备份服务器或备份服务器为Windows，请将标绿的行注释掉
# Move Backup Files To Backup Server.
#适合Linux（MySQL服务器）到Linux（备份服务器）
$scriptsDir/rsyncBackup.sh
if [[ $? == 0 ]]; then
echo "Move Backup Files To Backup Server Success!" >> $eMailFile
else
echo "Move Backup Files To Backup Server Fail!" >> $eMailFile
fi
fi
# 发送邮件通知
cat $eMailFile | mail -s "MySQL Backup" $eMail
# 写日志文件
echo "--------------------------------------------------------" >> $logFile
cat $eMailFile >> $logFile
rsyncBackup.sh注释：
#!/bin/sh
# Name:rsyncBackup.sh
# PS:Move Backup Files To Backup Server.
# Write by:i.Stone
# Last Modify:
2008-8-15
#
# 请根据具体情况修改，注意最后有“/”
# 定义数据库备份目录
dataBackupDir=/tmp/mysqlbackup/
# 定义备份服务器上存放备份数据的目录
backupServerDir=/root/mysqlbackup/
# 定义备份服务器
backupServer=192.168.0.200
#
# 同步备份文件到备份服务器
rsync -a --delete $dataBackupDir -e ssh $backupServer:$backupServerDir > /dev/null 2>&1
rmBackup.sh注释：
#!/bin/sh
# Name:rmBackup.sh
# PS:Delete old Backup.
# Write by:i.Stone
# Last Modify:
2008-8-15
#
# 定义备份目录
dataBackupDir=/tmp/mysqlbackup
# 删除mtime>2的日志备份文件find $dataBackupDir -name "mysql_*.gz" -type f -mtime +2 -exec rm {} /; > /dev/null 2>&1
（5）、恢复数据库到备份时的状态
用 mysqldump 备份出来的文件是一个可以直接倒入的 SQL 脚本,直接用 mysql 客户端导入就可以了。
/usr/local/mysql/bin/mysql -uroot -pUserPWD db_name < db_name.sql
对于任何可适用的更新日志，将它们作为 mysql 的输入：
% ls -t -r -1 HOSTNAME-bin* | xargs mysqlbinlog | mysql -uUser -pUserPWD
ls 命令生成更新日志文件的一个单列列表，根据服务器产生它们的次序排序（注意：如果你修改任何一个文件，你将改变排序次序，这将导致更新日志以错误的次序被运用。）
本套备份策略只能恢复数据库到最后一次备份时的状态，要想在崩溃时丢失的数据尽量少应该更频繁的进行备份，要想恢复数据到崩溃时的状态请使用主从复制机制(replication)。如果使用本套备份脚本，将日志文件和数据文件放到不同的磁盘上是一个不错的主义，这样不仅可以提高数据写入速度，还能使数据更安全。
4利用SELECT ... OUTFILE来备份MySQL数据库
我写这个脚本的目的是利用MySQL的select * from tablename into outfile ...语句来备份MySQL数据库，虽然没有MYSQLDUMP导出数据快，可是恢复的时候却非常快。
注意：
1）、我这个不包含表结构的备份，所以如果用之前先备份一下表结构。
2）、运行此脚本的用户必须具有select,insert,以及GLOBAL的FILE权限。
3）、注意导入时候的字符集要跟你的库一致。
1、备份脚本内容：
[root@localhost mysql]# cat fast_full_backup
#!/bin/sh
#
# Created by david yeung.
#
# 20080807.
#
# Use outfile syntax to backup mysql's full data.
#
DBNAME=$1
BACKUPDIR=/home/mysql/backup
USERNAME=backup_file_user
PASSWD=123456
TARNAME=$1`date '+%Y%m%d'`.tar
# Add your own database name here.
case "$1" in
t_girl);;
*) exit;;
esac
# Get all the tables' name.
NUM=`/usr/local/mysql/bin/mysql -u$USERNAME -p$PASSWD -s -vv -e "show tables" -D $DBNAME|wc -l`
HEADNUM=`expr ${NUM} - 3`
TAILNUM=`expr ${NUM} - 7`
ARR1=`/usr/local/mysql/bin/mysql -u$USERNAME -p$PASSWD -s -vv -e "show tables" -D $DBNAME| head -n"$HEADNUM" | tail -n "$TAILNUM"`
ARR2=($ARR1)
i=0
while [ "$i" -lt "${#ARR2[@]}" ]
do
tmpFileName=${ARR2[$i]}
# The real dump process.
/usr/local/mysql/bin/mysql -u$USERNAME -p$PASSWD -D$DBNAME -vv -e "select * from $tmpFileName into outfile '"$BACKUPDIR/$tmpFileName".dat' fields terminated by ',' enclosed by '/"' lines terminated by '/n'"
let "i++"
done
# Compress all the files.
#
cd $BACKUPDIR
tar cvf $TARNAME `ls *.dat`
gzip -f $TARNAME
rm -rf *.dat
2、恢复脚本内容：
[root@localhost mysql]# cat fast_full_recovery
#!/bin/sh
#
# Created by david yeung.
#
# 20080807.
#
# Use outfile syntax to restore mysql's full data.
#
DBNAME=$1
GZNAME=$2
GZDIR=`dirname $GZNAME`
USERNAME=backup_file_user
PASSWD=123456
if [ -z ${DBNAME} ]
then
exit
fi
if [ -z ${GZNAME} ]
then
exit
fi
TARNAME=`gzip -l "$GZNAME" | awk '{ print $4 }'|tail -n1`
gzip -d "$GZNAME"
tar xvf "$TARNAME" -C "$GZDIR"
ARR1=(`ls "$GZDIR" | grep '.dat' | grep -v 'grep'`)
i=0
while [ "$i" -lt "${#ARR1[@]}" ]
do
TMPFILENAME=${ARR1[$i]}
TBNAME=`echo $TMPFILENAME | cut -d '.' -f1`
/usr/local/mysql/bin/mysql -u$USERNAME -p$PASSWD -D$DBNAME -vv -e "load data infile '"$GZDIR"/$TMPFILENAME' ignore into table "$TBNAME" character set utf8 fields terminated by ',' enclosed by '/"' lines terminated by '/n'"
let "i++"
done
rm -rf "$GZDIR"/*.dat
3、实际运行例子：
1）、备份过程：
[root@localhost mysql]# ./fast_full_backup t_girl
--------------
select * from admin into outfile '/home/mysql/backup/admin.dat' fields terminated by ',' enclosed by '"' lines terminated by '/n'
--------------
Query OK, 0 rows affected (0.00 sec)
Bye
...
Bye
--------------
select * from ww into outfile '/home/mysql/backup/ww.dat' fields terminated by ',' enclosed by '"' lines terminated by '/n'
--------------
Query OK, 9 rows affected (0.00 sec)
Bye
admin.dat
...
ww.dat
[root@localhost mysql]#
2）、恢复过程：
[root@localhost mysql]# ./fast_full_recovery t_girl /home/mysql/backup/t_girl20080707.tar.gz
admin.dat
...
ww.dat
--------------
load data infile '/home/mysql/backup/admin.dat' ignore into table admin character set utf8 fields terminated by ',' enclosed by '"' lines terminated by '/n'
--------------
Query OK, 0 rows affected (0.00 sec)
Records: 0 Deleted: 0 Skipped: 0 Warnings: 0
Bye
...
Query OK, 9 rows affected, 3 warnings (0.00 sec)
Records: 9 Deleted: 0 Skipped: 0 Warnings: 0
Bye
[root@localhost mysql]#
4、与MYSQLDUMP导出导入时间比较：
前提：
2G
的数据量。
1）、用OUTFILE 方式花费。
导出：
real
user
sys
导入：
real
user
sys
2）、用MYSQLDUMP 方式花费。
导出：
real
user
sys
导入：
real
user
sys
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
