MySQL 备份和恢复策略
在数据库表丢失或损坏的情况下,备份你的数据库是很重要的。如果发生系统崩溃,你肯定想能够将你的表尽可能丢失最少的数据恢复到崩溃发生时的状态。本文主要对MyISAM表做备份恢复。</span></span> 备份策略一:直接拷贝数据库文件(不推荐) 备份策略二:使用mysqlhotcopy备份数据库(完全备份,适合小型数据库备份) 备份策略三:使用mysqldump备份数据库(完全+增量备份,适合中型数据库备份) 备份策略四:使用主从复制机制(replication)(实现数据库实时备份) 备份策略一、直接拷贝数据库文件 直接拷贝数据文件最为直接、快速、方便,但缺点是基本上不能实现增量备份。为了保证数据的一致性,需要在备份文件前,执行以下 FLUSH 也就是把内存中的数据都刷新到磁盘中,同时锁定数据表,以保证拷贝过程中不会有新的数据写入。这种方法备份出来的数据恢复也很简单,直接拷贝回原来的数据库目录下即可。 备份策略二、使用mysqlhotcopy备份数据库 mysqlhotcopy 本备份策略适合于小型数据库的备份,数据量不大,可以采用mysqlhotcopy程序每天进行一次完全备份。 备份策略布置: (1)、安装DBD-mysql shell> shell> shell> shell> shell> shell> shell> (2)、设置crontab任务,每天执行备份脚本 shell> 0 每天凌晨3:00执行备份脚本。 mysqlbackup.sh注释: #!/bin/sh # Name:mysqlbackup.sh # PS:MySQL DataBase Backup,Use mysqlhotcopy script. # Write by:i.Stone # Last Modify:2007-11-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 else fi # 如果备份目录不存在则创建它 if [[ ! -e $backupDir ]];then fi if [[ -s MySQLBackup.log ]]; then fi for databases in `find $dataDir -type d | \ # 备份数据库 done date=`date -I` cd $tmpBackupDir tar czf $backupDir/mysql-$date.tar.gz ./ if [[ -s MySQLBackup.log ]]; then fi # $scriptsDir/smbclientmv.sh smbclientmv.sh注释 #!/bin/sh # Name:smbclientmv.sh # PS:Move the data to Backup Server. # Write by:i.Stone # Last Modify:2007-11-15 # # 定义变量 # 备份服务器名 BackupServer="BackupServerName" # 共享文件夹名 BackupShare="ShareName" # 备份服务器的访问用户名和密码 BackupUser="SMBUser" BackupPW="SMBPassword" # 定义备份目录 BackupDir=/tmp/mysqlbackup date=`date -I` smbclient //$BackupServer/$BackupShare \ $BackupPW -d0 -W WORKGROUP -U $BackupUser \ -c "put $BackupDir/mysql-$date.tar.gz \ mysql-$date.tar.gz" rm -f $BackupDir/mysql-$date.tar.gz (3)、恢复数据库到备份时的状态 备份策略三、使用mysqldump备份数据库 mysqldump 对于中等级别业务量的系统来说,备份策略可以这么定:第一次完全备份,每天一次增量备份,每周再做一次完全备份,如此一直重复。而对于重要的且繁忙的系统来说,则可能需要每天一次全量备份,每小时一次增量备份,甚至更频繁。为了不影响线上业务,实现在线备份,并且能增量备份,最好的办法就是采用主从复制机制(replication),在 备份策略布置: (1)、创建备份目录 Shell> Shell> (2)、启用二进制日志 采用 启用 [mysqld] log-bin 然后启动 (3)、配置SSH密钥登录,用于将MySQL备份传送到备份服务器(如果备份服务器为Windows,请跳过此部)。 1)、在MySQL所在服务器(192.168.0.20)生成SSH密钥 [root@lab Generating Enter Enter Enter Your Your The c2:96:9f:2d:5a:8e:08:42:43:35:2f:85:5e:72:f8:1c 2)、在备份服务器(192.168.0.200)上创建目录,修改权限,并传送公钥。 [root@lab The RSA Are Warning: root@192.168.0.200's [root@lab root@192.168.0.200's id_rsa.pub 3)、测试SSH登录 [root@lab Last [root@lib (4)、设置crontab任务,每天执行备份脚本 shell> #每个星期日凌晨3:00执行完全备份脚本 0 #周一到周六凌晨3:00做增量备份 0 mysqlFullBackup.sh注释: #!/bin/sh # Name:mysqlFullBackup.sh # PS:MySQL DataBase Full Backup. # Write by:i.Stone # Last Modify:2007-11-17 # # 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 $(date +"%y-%m-%d %H:%M:%S") >> $eMailFile cd $dataBackupDir # 定义备份文件名 dumpFile=mysql_$DATE.sql GZDumpFile=mysql_$DATE.sql.tar.gz $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 # Move Backup Files To Backup Server. #适合Linux(MySQL服务器)到Linux(备份服务器) fi # 写日志文件 echo "--------------------------------------------------------" >> $logFile cat $eMailFile >> $logFile # 发送邮件通知 cat $eMailFile | mail -s "MySQL Backup" $eMail #!/bin/sh # 如果不需要将备份传送到备份服务器或备份服务器为Windows,请将标绿的行注释掉 rsyncBackup.sh注释: #!/bin/sh rmBackup.sh注释: #!/bin/sh (5) 用 /usr/local/mysql/bin/mysql 对于任何可适用的更新日志,将它们作为 % ls 本套备份策略只能恢复数据库到最后一次备份时的状态,要想在崩溃时丢失的数据尽量少应该更频繁的进行备份,要想恢复数据到崩溃时的状态请使用主从复制机制(replication)。如果使用本套备份脚本,将日志文件和数据文件放到不同的磁盘上是一个不错的主义,这样不仅可以提高数据写入速度,还能使数据更安全。 备份策略四、使用主从复制机制(replication) MySQL支持单向、异步复制,复制过程中一个服务器充当主服务器,而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件,并维护日志文件的一个索引以跟踪日志循环。当一个从服务器连接到主服务器时,它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新,然后封锁并等待主服务器通知下一次更新。 为什么使用主从复制? 1、主服务器/从服务器设置增加了健壮性。主服务器出现问题时,你可以切换到从服务器作为备份。 2、通过在主服务器和从服务器之间切分处理客户查询的负荷,可以得到更好的客户响应时间。但是不要同时在主从服务器上进行更新,这样可能引起冲突。 3、使用复制的另一个好处是可以使用一个从服务器执行备份,而不会干扰主服务器。在备份过程中主服务器可以继续处理更新。 MySQL使用 3个线程来执行复制功能(其中1个在主服务器上,另两个在从服务器上。当发出START SLAVE时,从服务器创建一个I/O线程,以连接主服务器并让主服务器发送二进制日志。主服务器创建一个线程将二进制日志中的内容发送到从服务器。从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中,即中继日志。第3个线程是SQL线程,从服务器使用此线程读取中继日志并执行日志中包含的更新。SHOW PROCESSLIST语句可以查询在主服务器上和从服务器上发生的关于复制的信息。 默认中继日志使用host_name-relay-bin.nnnnnn形式的文件名,其中host_name是从服务器主机名,nnnnnn是序列号。用连续序列号来创建连续中继日志文件,从000001开始。从服务器跟踪中继日志索引文件来识别目前正使用的中继日志。默认中继日志索引文件名为host_name-relay-bin.index。在默认情况,这些文件在从服务器的数据目录中被创建。中继日志与二进制日志的格式相同,并且可以用mysqlbinlog读取。当SQL线程执行完中继日志中的所有事件后,中继日志将会被自动删除。 从服务器在数据目录中另外创建两个状态文件--master.info和relay-log.info。状态文件保存在硬盘上,从服务器关闭时不会丢失。下次从服务器启动时,读取这些文件以确定它已经从主服务器读取了多少二进制日志,以及处理自己的中继日志的程度。 设置主从复制: 1、确保在主服务器和从服务器上安装的MySQL版本相同,并且最好是MySQL的最新稳定版本。 2、在主服务器上为复制设置一个连接账户。该账户必须授予REPLICATION SLAVE权限。如果账户仅用于复制(推荐这样做),则不需要再授予任何其它权限。 mysql> GRANT REPLICATION SLAVE ON *.* 3、执行FLUSH TABLES WITH READ LOCK语句清空所有表和块写入语句: mysql> FLUSH TABLES WITH READ LOCK; 保持mysql客户端程序不要退出。开启另一个终端对主服务器数据目录做快照。 shell> cd /usr/local/mysql/ shell> tar -cvf /tmp/mysql-snapshot.tar ./data 如果从服务器的用户账户与主服务器的不同,你可能不想复制mysql数据库。在这种情况下,应从归档中排除该数据库。你也不需要在归档中包括任何日志文件或者master.info或relay-log.info文件。 当FLUSH TABLES WITH READ LOCK所置读锁定有效时(即mysql客户端程序不退出),读取主服务器上当前的二进制日志名和偏移量值: mysql > SHOW MASTER STATUS; +---------------+----------+--------------+------------------+ | File +---------------+----------+--------------+------------------+ | mysql-bin.003 +---------------+----------+--------------+------------------+ File列显示日志名,而Position显示偏移量。在该例子中,二进制日志值为mysql-bin.003,偏移量为73。记录该值。以后设置从服务器时需要使用这些值。它们表示复制坐标,从服务器应从该点开始从主服务器上进行新的更新。 如果主服务器运行时没有启用--logs-bin,SHOW MASTER STATUS显示的日志名和位置值为空。在这种情况下,当以后指定从服务器的日志文件和位置时需要使用的值为空字符串('')和4. 取得快照并记录日志名和偏移量后,回到前一中端重新启用写活动: mysql> UNLOCK TABLES; 4、确保主服务器主机上my.cnf文件的[mysqld]部分包括一个log-bin选项。该部分还应有一个server-id=Master_id选项,其中master_id必须为1到232–1之间的一个正整数值。例如: [mysqld] log-bin server-id=1 如果没有提供那些选项,应添加它们并重启服务器。 5、停止从服务器上的mysqld服务并在其my.cnf文件中添加下面的行: [mysqld] server-id=2 slave_id值同Master_id值一样,必须为1到232–1之间的一个正整数值。并且,从服务器的ID必须与主服务器的ID不相同。 6、将数据备据目录中。确保对这些文件和目录的权限正确。服务器 MySQL运行的用户必须能够读写文件,如同在主服务器上一样。 Shell> chown -R mysql:mysql /usr/local/mysql/data 7、启动从服务器。在从服务器上执行下面的语句,用你的系统的实际值替换选项值: 8、启动从服务器线程: 执行这些程序后,从服务器应连接主服务器,并补充自从快照以来发生的任何更新。 9、如果出现复制错误,从服务器的错误日志(HOSTNAME.err)中也会出现错误消息。 10、从服务器复制时,会在其数据目录中发现文件master.info和HOSTNAME-relay-log.info。从服务器使用这两个文件跟踪已经处理了多少主服务器的二进制日志。不要移除或编辑这些文件,除非你确切知你正在做什么并完全理解其意义。即使这样,最好是使用CHANGE MASTER TO语句。
# 如果临时备份目录存在,清空它,如果不存在则创建它
# 清空MySQLBackup.log
# 得到数据库备份列表,在此可以过滤不想备份的数据库
# 压缩备份文件
# 发送邮件通知
# 使用smbclientmv.sh脚本上传数据库备份到备份服务器
# Move the data to BackupServer
# Delete temp files
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)。
echo "" > $eMailFile
# 使用mysqldump备份数据库,请根据具体情况设置参数
# 压缩备份文件
# Delete daily backup files.
# Delete old backup files(mtime>2).
# 如果不需要将备份传送到备份服务器或备份服务器为Windows,请将标绿的行注释掉
else
# Name:mysqlDailyBackup.sh
# PS:MySQL DataBase Daily Backup.
# Write by:i.Stone
# Last Modify:2007-11-17
#
# 定义变量,请根据具体情况修改
# 定义数据库目录和数据目录
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
done
nextNum=0
iFile=0
for file in $fileList
do
# 跳过最后一个二进制日志(数据库当前使用的二进制日志文件)
# 跳过已经备份的二进制日志文件
# 备份日志文件到备份目录
done
if [[ $iFile == 0 ]];then
else
# Move Backup Files To Backup Server.
#适合Linux(MySQL服务器)到Linux(备份服务器)
# 发送邮件通知
cat $eMailFile | mail -s "MySQL Backup" $eMail
# 写日志文件
echo "--------------------------------------------------------" >> $logFile
cat $eMailFile >> $logFile
# Name:rsyncBackup.sh
# PS:Move Backup Files To Backup Server.
# Write by:i.Stone
# Last Modify:2007-11-17
#
# 请根据具体情况修改,注意最后有“/”
# 定义数据库备份目录
dataBackupDir=/tmp/mysqlbackup/
# 定义备份服务器上存放备份数据的目录
backupServerDir=/root/mysqlbackup/
# 定义备份服务器
backupServer=192.168.0.200
#
# 同步备份文件到备份服务器
rsync -a --delete $dataBackupDir -e ssh $backupServer:$backupServerDir > /dev/null 2>&1
# Name:rmBackup.sh
# PS:Delete old Backup.
# Write by:i.Stone
# Last Modify:2007-11-15
#
# 定义备份目录
dataBackupDir=/tmp/mysqlbackup
# 删除mtime>2的日志备份文件
find $dataBackupDir -name "mysql_*.gz" -type f -mtime +2 -exec rm {} \; > /dev/null 2>&1
配置MySQL主从复制(Replication)
------整理汇总自网上
转自:http://blog.csdn.net/xujinyang/article/details/7378198