Linux下Mysql数据库备份和恢复方法

一、 使用mysql相关命令进行简单的本地备份
1、mysqlldump命令
mysqldump是采用SQL级别的备份机制,它将数据表导成SQL脚本文件,在不同的MySQL版本之间升级时相对比较合适,这也是最常用的备份方法。使用 mysqldump进行备份非常简单,如果要备份数据库“ dbtest”,使用命令:
a.导出整个数据库:mysqldump -u 用户名 -p 数据库名 > 导出的文件名
#mysqldump -u root -p dbtest > /usr/backups/mysql/db_backup`date +%Y%m%d`.sql
还可以使用gzip命令对备份文件进行压缩:
#mysqldump -u root -p dbtest | gzip > /usr/backups/mysql/db_backup`date +%Y%m%d`.sql.gz
b.导出一个表 mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
## mysqldump -u root -p dbtest articles comments links > /usr/backups/mysql/db_backup`date +%Y%m%d`.sql
上面的命令会备份articles, comments, 和links 三个表。
c.导出一个数据库结构
#mysqldump -u root -p dbtest -d –add-drop-table>/usr/backups/mysql/db_backup`date +%Y%m%d`.sql
-d 没有数据,–add-drop-table 在每个create语句之前增加一个drop table语句
恢复数据使用命令:
#mysql –u -p dbtest </usr/backups/mysql/db_backup2011-12-16.sql
注意使用这个命令时必须保证数据库正在运行。
2、使用 SOURCE 语法
其实这不是标准的SQL语法,而是mysql客户端提供的功能,例如:
# SOURCE /tmp/db_name.sql;
这里需要指定文件的绝对路径,并且必须是mysqld运行用户有权限读取的文件。
3、mysqlhotcopy备份
mysqlhotcopy只能用于备份MyISAM,并且只能运行在linux、Unix和NetWare系统上。mysqlhotcopy支持一次性拷贝多个数据库,同时还支持正则表达。以下是几个例子:
#mysqlhotcopy -h=localhost -u=goodcjh -p=goodcjh db_name /tmp
(把数据库目录db_name拷贝到/tmp下)
注意,想要使用mysqlhotcopy,必须要有SELECT、RELOAD(要执行FLUSH TABLES) 权限,并且还必须要能够有读取datadir/db_name目录的权限。
还原数据库方法:
mysqlhotcopy备份出来的是整个数据库目录,使用时可以直接拷贝到mysqld指定的目录 (在这里是 /usr/local/mysql/data/)目录下即可,同时要注意权限的问题,另外首先应当删除数据库旧副本如下例:
# /bin/rm -rf /mysql-backup/**//*old
针对mysql数据表格式为MyISAM的还可以用如下方法:
假如数据文件在/var/lib/mysql,那么直接写个脚本
cp -r /var/lib/mysql /备份到的文件夹路径
二、使用网络备份
将MYSQL数据放在一台计算机上是不安全的,所以应当把数据备份到局域网中其他Linux计算机中。假设Mysql服务器IP地址是:192.168.1.3。局域网使用Linux的远程计算机IP地址是192.168.1.4;类似于windows的网络共享,UNIX(Linux)系统也有自己的网络共享,那就是NFS(网络文件系统),在linux客户端挂接(mount)NFS磁盘共享之前,必须先配置好NFS服务端。linux系统NFS服务端配置方法如下:   
(1)修改 /etc/exports,增加共享目录
/export/home/sunky 192.168.1.4(rw)
/export/home/sunky1 *(rw)
/export/home/sunky2 linux-client(rw)
注:/export/home/目录下的sunky、sunky1、sunky2是准备共享的目录,192.168.1.4、*、linux-client是被允许挂接此共享linux客户机的IP地址或主机名。如果要使用主机名linux-client必须在服务端主机/etc/hosts文件里增加linux-client主机ip定义。格式如下:192.168.1.4 linux-client
若修改/etc/export文件增加新的共享,应先停止NFS服务,再启动NFS服务方能使新增加的共享起作用。使用命令exportfs -rv也可以达到同样的效果。linux客户端挂接(mount)其他linux系统或UNIX系统的NFS共享。这里假设192.168.1.4是NFS服务端的主机IP地址,当然配置过IP定义后也可以使用主机名。/export/home/sunky为服务端共享的目录,如此就可以在linux客户端通过/mnt/nfs来访问其它linux系统或UNIX系统以NFS方式共享出来的文件了。
把MYSQL数据备份到使用Linux的远程计算机需要在两端都安装NFS协议(Network File System),远程NFS计算机安装NFS协议后还要修改配置文件:/etc/exports,加入一行:
/usr/backups/mysql/ 192.168.1.4 (rw, no_root_squash)
表示将/usr/backups/mysql/目录共享。这个目录具有远程root用户读写权限。保存NFS配置文件,然后使用命令:
#exportfs -a –r
然后重新启动NFS服务:
#service nfsd start
远程计算机设定后,在MYSQL服务器/mnt 目录下建立一个backup_share目录:
#mkdir /mnt/backup_share
将远程的Linux计算机的/usr/backups/mysql/目录挂载到MYSQL服务器的/mnt/backup_share目录下:
# mount -t nfs 192.168.1.4:/usr/backups/mysql /mnt/backup_share
将目录挂载进来后,只要进入/mnt/backup_share 目录,就等于到了IP地址192.168.1.4的/usr/backups/mysql目录中。下面使用mysqldump把“dbtest”备份到远程计算机:
# mysqldump -u root -p dbtest > /mnt/backup_share/db_backup`date +%Y%m%d`.sql
自动完成网络备份的方法:写一个shell脚本,结合crontab,定时备份数据库。建立一个shell脚本:sample_db_backup.sh
# At the very end the $(date +%F) 自动添加备份日期
mysqldump -u <username> -p <password> -h <hostname> dbtest > /mnt/backup_share/sample_db.$(date +%F)
#un-mount the filesystem
umount /mnt/backup_share
# mount \u2013o soft 192.168.1.4:/archive /mnt/backup_share
说明:mount NFS服务器的一个重要参数:hard(硬)mount或soft(软)mount。
硬挂载: NFS客户机会不断的尝试与NFS服务器的连接,直到挂载上为止。
软挂载:会在前台尝试与NFS服务器的连接,是默认的连接方式。当收到错误信息后终止mount尝试,并给出相关信息。
??? 对于到底是使用硬挂载还是软挂载的问题,这主要取决于访问什么信息。例如是想察看NFS服务器的视频文件时,你绝对不会希望由于一些意外的情况(如网络速度一下子变的很慢)而使系统输出大量的错误信息,如果此时你用的是硬挂载方式的话,系统就会等待,直到能够重新与NFS服务器建立连接传输信息。另外如果是非关键数据的话也可以使用软挂载方式,如FTP一些数据等,这样在远程机器暂时连接不上或关闭时就不会挂起你的会话过程。
下面建立脚本文件权限:chmod +x ./sample_db_backup.sh
然后使用将此脚本加到 /etc/crontab 定时任务中:
01 5 * * 0 mysql /home/mysql/ sample_db_backup.sh
表示每周日凌晨 5:01 系统就会自动运行sample_db_backup.sh文件通过网络备份MySQL数据库了。
三、实时恢复MySQL数据方法
在对MySQL数据和表格结构进行备份时,mysqldump是一个非常有用的工具。然而,通常情况下,一般一天只备份一次,或者在一个特定的间隔备份一次。如果在刚备份完成的一段时间以内数据丢失,那么这些数据很有可能无法恢复。有什么方法可以对数据进行实时性地保护呢?事实上,现在有几种方法都可以实现MySQL数据库的实时保护。这里介绍其中一种,即使用二进制日志进行数据恢复。
1、设置二进制日志方法
要想从二进制日志恢复数据,需要知道当前二进制日志文件的路径和文件名。一般可以从选项文件(即my.cnf or my.ini,取决于你的系统)中找到路径。如果未包含在选项文件中,当服务器启动时,可以在命令行中以选项的形式给出。启用二进制日志的选项为–log-bin。要想确定当前的二进制日志文件的文件名,输入下面的MySQL语句:
# SHOW BINLOG EVENTS \G
Linux下开启binlog
/etc/my.cnf中的mysqld部分加入:
[mysqld]
log-bin=../logs/mysql-bin
max-binlog-size=50M
windows下开启binlog
%mysql%/my.ini中的mysqld部分加入:
[mysqld]
log-bin =../logs/mysql-bin
max-binlog-size=50M
2、最简单的数据恢复
每天备份和运行二进制日志的确是一个在MySQL服务器中恢复数据的不错方法。比如,可以每天在深夜使用mysqldump对数据进行备份,如果某天在数据备份完成后的一段时间里,由于某种原因数据丢失,可以使用以下方法来对其进行恢复。首先,停止MySQL服务器,然后使用以下命令重新启动MySQL服务器。该命令将保证是惟一可以访问该数据库服务器的人:
# /etc/init.d/mysqld stop
Stopping MySQL: [ OK ]
# mysqld –socket=/tmp/mysql_restore.sock –skip-networking
这里,–socket选项将为Unix系统命名一个不同的Socket文件。一旦服务器处于独占控制之下,就可以放心地对数据库进行操作,而不用担心在进行数据恢复的过程中有用户尝试访问数据库而导致更多的麻烦。进行恢复的第一个步骤是恢复晚上备份好的dump文件:
#mysql -u root -pmypwd –socket=/tmp/mysql_restore.sock < /var/backup/20080120.sql
该命令可以将数据库的内容恢复至晚上刚刚完成备份的内容。要恢复dump文件创建后的数据库事务处理,可以使用mysqlbinlog工具。如果每天晚上进行备份操作时都对日志进行flush操作,则可以使用以下命令行工具将整个二进制日志文件进行恢复:
mysqlbinlog /var/log/mysql/bin.123456 \
| mysql -u root -pmypwd –socket=/tmp/mysql_restore.sock
3、针对某一时间点的恢复
对于MySQL4.1.4,可以在mysqlbinlog语句中通过–start-date和–stop-date选项指定DATETIME格式的起止时间。假设用户在2011-11-22上午10点执行的SQL语句删除了一个大的数据表,则可以使用以下命令进行恢复:要想恢复表和数据,你可以恢复前晚上的备份,并输入:
#mysqlbinlog –stop-date=”2011-11-22 9:59:59″
/var/log/mysql/bin.123456 |mysql -u root -pmypwd \–socket=/tmp/mysql_restore.sock
#mysql -u root -pmypwd
该语句将恢复所有给定–stop-date日期之前的数据。如果在执行某SQL语句数小时之后才发现执行了错误操作,那么可能还需要恢复之后输入的一些数据。这时,也可以通过mysqlbinlog来完成该功能:
#mysqlbinlog –start-date=”2011-11-22 10:01:00″ \
/var/log/mysql/bin.123456 \| mysql -u root -pmypwd \–socket=/tmp/mysql_restore.sock
#mysql -u root -pmypwd
在该行中,从上午10:01登录的SQL语句将运行。组合执行前夜的转储文件和mysqlbinlog的两行可以将所有数据恢复到上午10:00前一秒钟。你应检查日志以确保时间确切。
4、使用Position进行恢复
也可以不指定日期和时间,而使用mysqlbinlog的选项–start-position和–stop-position来指定日志位置。它们的作用与起止日选项相同,不同的是给出了从日志起的位置号。使用日志位置是更准确的恢复方法,特别是当由于破坏性SQL语句同时发生许多事务的时候。要想确定位置号,可以运行mysqlbinlog寻找执行了不期望的事务的时间范围,但应将结果重新指向文本文件以便进行检查。操作命令为:
mysqlbinlog –start-date=”2011-11-22 9:55:00″ –stop-date=”2011-11-22 10:05:00″
/var/log/mysql/bin.123456 > /tmp/mysql_restore.sql
该命令将在/tmp目录创建小的文本文件,将显示执行了错误的SQL语句时的SQL语句。可以用vi或者gedit文本编辑器打开该文件,寻找你不要想重复的语句。如果二进制日志中的位置号用于停止和继续恢复操作,应进行注释。用log_pos加一个数字来标记位置。使用位置号恢复了以前的备份文件后,应从命令行输入下面内容:
mysqlbinlog –stop-position=”368312″ /var/log/mysql/bin.123456| mysql -u root -pmypwd
mysqlbinlog –start-position=”368315″ /var/log/mysql/bin.123456| mysql -u root -pmypwd
上面的第1行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。因为mysqlbinlog的输出包括每个SQL语句记录之前的SET TIMESTAMP语句,恢复的数据和相关MySQL日志将反应事务执行的原时间。
5、其他方法
对于一个标准安装的MySQL,通过二进制日志完全恢复任何时刻丢失的数据是一件非常简单、快捷的事情。当然,如果无法忍受使用该方法的要求,比如在进行恢复操作时要锁住其他用户等,也可以使用其他方法来保护数据:
使用Mysql复制技术
http://dev.mysql.com/doc/mysql/en/replication.html
使用mysql集群技术
http://dev.mysql.com/doc/mysql/en/ndbcluster.html
四、备份示例
1、创建保存备份文件的目录backup/data
#mkdir /usr/local/backup/data
2、创建/usr/sbin/backup.sh文件
#vi /backup/backup.sh
#!/bin/sh
# mysql data backup script #
# use mysqldump –help,get more detail.
BakDir=/usr/local/backup/data
LogFile=/usr/local/backup/mysqlbak.log
DATE=`date +%Y%m%d`
echo $(date +”%y-%m-%d %H:%M:%S”) >> $LogFile
echo “——————————————-” >> $LogFile
cd $BakDir
DumpFile=$DATE.sql
GZDumpFile=$DATE.sql.tgz
mysqldump –quick –all-databases –flush-logs –delete-master-logs –lock-all-tables > $DumpFile echo “Dump Done” >> $LogFile tar czvf $GZDumpFile $DumpFile >> $LogFile 2>&1 echo “[$GZDumpFile]Backup Success!” >> $LogFile rm -f $DumpFile
#delete previous daily backup files:采用增量备份的文件,如果完整备份后,则删除增量备份的文件.
cd $BakDir/daily
rm -f *
cd $BakDir
echo “Backup Done!”
上面的脚本把mysql备份到本地的/backup/mysql目录,增量备份的文件放在/backup/mysql/daily目录下.
增量备份
增量备份的数据量比较小,但是要在完整备份的基础上操作增量备份使用binlog,脚本如下:
#!/bin/sh #
mysql binlog backup script /usr/bin/mysqladmin flush-logs DATADIR=/var/lib/mysql BAKDIR=/backup/mysql/daily
###如果你做了特殊设置,请修改此处或者修改应用此变量的行:缺省取机器名,mysql缺省也是取机器名 HOSTNAME=`uname -n` cd $DATADIR FILELIST=`cat $HOSTNAME-bin.index`
##计算行数,也就是文件数
COUNTER=0
for file in $FILELIST
do
COUNTER=`expr $COUNTER + 1 `
done
NextNum=0
for file in $FILELIST
do
base=`basename $file`
NextNum=`expr $NextNum + 1`
if [ $NextNum -eq $COUNTER ]
then echo “skip lastest”
else dest=$BAKDIR/$base
if(test -e $dest)
then echo “skip exist $base”
else echo “copying $base”
cp $base $BAKDIR
fi
fi
done
echo “backup mysql binlog ok”
增量备份脚本是备份前flush-logs,mysql会自动把内存中的日志放到文件里,然后生成一个新的日志文件,所以我们只需要备份前面的几个即可,也就是不备份最后一个. 因为从上次备份到本次备份也可能会有多个日志文件生成,所以要检测文件,如果已经备份过,就不用备份了.
数据还原:先还原最近的完全备份数据:
mysql -hhostname -uusername -ppassword databasename < backupfile.sql
再还原binlog:
./mysqlbinlog –start-date=”2011-11-10 17:30:05″ –stop-date=”2011-11-11 17:41:28″ /usr/local/mysql/data/mysql-bin.000002 |mysql -u root -p123456

tar zcvf /mysqldata/mysql_`date -d today +%w`.tar.gz /var/lib/mysql
或者写成
mysqldump –all-databases -u root -p密码 > /mysqldata/mysql_`date -d today +%w`.sql
/var/lib/mysql是数据库文件的目录,部分用户是/usr/local/mysql/data,每个人可能不同
/mysqldata/表示保存备份文件的目录,这个每个人也可以根据自己的要求来做。
3、修改文件属性,使其可执行
# chmod +x /usr/sbin/bakmysql.sh
4、修改/etc/crontab
#vi /etc/crontab
在下面添加
* 3 * * * root /usr/sbin/bakmysql
表示每天3点钟执行备份
5、重新启动crond
/etc/rc.d/init.d/crond restart
完成。

设置自动删除N天前备份
find /usr/local/backup -mtime +6 -name “*.*” -exec rm -rf {} \;
脚本是删除6天前的文件

打包tmp目录为file.tar
tar -cf file.tar tmp/
给file.tar文件加上密码123456 并删除原始文件
zip -P 123456 -m file.zip file.tar



无觅相关文章插件,快速提升流量

评论已关闭!