mysqldump实现全备份和增量备份_懒人程序
支付宝赞助帐号：
mysqldump实现全备份和增量备份
mysqldump实现全备份和增量备份
一、如果你使用mysql，那么肯定也会考虑到备份的情况吧，那下面我们就讲讲mysql备份的知识。
在线备份简单的来讲分为：全备份和增量备份，下面我们讲解一下mysqlinnodb引擎的数据库备份：
二、利用mysqldump实现的全备份和增量备份：
1.利用mysql自带的工具mysqldump来备份：
mysqldump -uroot -p --all-databases --flush-logs --master-data=2 --single-transaction && full_back.sql
关键参数的含义：
--all-databases &, -A
导出全部数据库。
--flush-logs
开始导出之前刷新日志。
请注意：假如一次导出多个数据库(使用选项--databases或者--all-databases)，将会逐个数据库刷新日志。除使用--lock-all-tables或者--master-data外
。在这种情况下，日志将会被刷新一次，相应的所以表同时被锁定。因此，如果打算同时导出和刷新日志应该使用--lock-all-tables 或者--master-data&
和--flush-logs。
--master-data
该选项将binlog的位置和文件名追加到输出文件中。如果为1，将会输出CHANGE MASTER 命令；如果为2，输出的CHANGE &MASTER命令前添加注释信息。该选项
将打开--lock-all-tables 选项，除非--single-transaction也被指定（在这种情况下，全局读锁在开始导出时获得很短的时间；其他内容参考下面的--
single-transaction选项）。该选项自动关闭--lock-tables选项。
mysqldump &-uroot -p --host=localhost --all-databases --master-data=1;
mysqldump &-uroot -p --host=localhost --all-databases --master-data=2;
--single-transaction
该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎，仅InnoDB。本选项和--lock-tables 选项是互斥的，因为LOCK &TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用--quick 选项。
mysqldump提供在线逻辑备份，我们在备份的时候使用single-transaction参数的话，MYSQL为我们提供一致性地读，并且保证mysqldump所看见的数据不会更改。(其它客户端对InnoDB表进行的更改不会被mysqldump进程看见)该参数自动关闭--lock-tables。
如果我们还有其它类型的表，我们必须假定在备份过程中它们不会更改。
--master-data=2起到的作用
grep -C 10 CHANGE &full_back.sql &| more（下面的内容在备份文件的前面出现）
-------------------------------------------------------------------------------------------
-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.;, MASTER_LOG_POS=107;
-------------------------------------------------------------------------------------------
--flush-logs起到的作用，产生了新的binlog文件
-------------------------------------------------------------------------------------------
-rw-rw---- 1 mysql mysql & Apr 23 11:11 mysql-bin.000077
-rw-rw---- 1 mysql mysql & & & &107 Apr 23 11:11 mysql-bin.000078
-------------------------------------------------------------------------------------------
2.增量备份：
增量备份可以利用flush log，产生新的binlog，保存(拷贝备份)新binlog之前的binlog文件，如果binlog数据量小的话，可以使一个binlog，如果多的话，就会产生多个binlog，不过没有关系，我们可以安装时间来拷贝产生的binlog文件。
在bird数据库的Customer表中插入一行数据：
mysql& insert into Customer(ID,First_Name,Last_Name,Addr,City,Country,Birth_Date) values (3,'wen','zhao','king&
street','shanghai','China','');
Query OK, 1 row affected (0.07 sec
flush log：
mysqladmin -uroot -p flush-logs
可以看到产生的新log：
-rw-rw---- 1 mysql mysql & Apr 23 11:11 mysql-bin.000077
-rw-rw---- 1 mysql mysql & & & &452 Apr 23 14:03 mysql-bin.000078
-rw-rw---- 1 mysql mysql & & & &107 Apr 23 14:03 mysql-bin.000079
查看插入的数据是否在mysql-bin.000078中(mysqlbinlog mysql-bin.000078 | more)，经查看插入的语句确实在mysql-bin.000078里面。
3.测试恢复，全备份的频率可每周做一次，增量备份可以每天做一次
如果机器出现了故障，或者数据库出现了问题，不能修复的情况下，就需要导入全备份和增量备份。用test库进行测试：
mysql& create table kehu(id int, name1 varchar(20),city varchar(20));
Query OK, 0 rows affected (0.06 sec)
mysql& insert into kehu(id,name1,city)values(1,'xiaoming','beijing');
Query OK, 1 row affected (0.04 sec)
mysql& select *
+------+----------+---------+
| id & | name1 & &| city & &|
+------+----------+---------+
| & &1 | xiaoming | beijing |
+------+----------+---------+
1 row in set (0.00 sec)
实现全备份：
备份test库：mysqldump -uroot -p test --flush-logs --master-data=2 --single-transaction && test_back.sql
再次插入：
mysql& insert into kehu(id,name1,city)values(1,'dapeng','shanghai');
Query OK, 1 row affected (0.04 sec)
实现增量备份：
mysqladmin &flush-logs
----------------------------------------------------------------------------------------
-rw-rw---- 1 mysql mysql & & &368 Apr 24 10:55 mysql-bin.000004
-rw-rw---- 1 mysql mysql & & &107 Apr 24 10:55 mysql-bin.000005
----------------------------------------------------------------------------------------
备份mysql-bin.000004文件
Query OK, 1 row affected (0.02 sec)
+--------------------+
| Database & & & & & |
+--------------------+
| information_schema |
| mysql & & & & & & &|
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
恢复时间点的全备份：
Query OK, 1 row affected (0.00 sec)
Database changed
mysql& source /backup/test_back.
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
....................................
+----------------+
| Tables_in_test |
+----------------+
| kehu & & & & & |
+----------------+
1 row in set (0.00 sec)
mysql& select *
+------+----------+---------+
| id & | name1 & &| city & &|
+------+----------+---------+
| & &1 | xiaoming | beijing |
+------+----------+---------+
1 row in set (0.00 sec)
执行需要的增量备份：
mysqlbinlog mysql-bin.000004 | mysql
再次查看表的情况：
mysql& select *
+------+----------+----------+
| id & | name1 & &| city & & |
+------+----------+----------+
| & &1 | xiaoming | beijing &|
| & &1 | dapeng & | shanghai |
+------+----------+----------+
2 rows in set (0.00 sec)
已经恢复到了最新的配置了。
支持键盘 ←　→MYSQL全量和增量备份_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
MYSQL全量和增量备份
上传于|0|0|文档简介
&&mysql,备份
阅读已结束，如果下载本文需要使用0下载券
想免费下载更多文档？
定制HR最喜欢的简历
下载文档到电脑，查找使用更方便
还剩4页未读，继续阅读
定制HR最喜欢的简历
你可能喜欢lhflinux 的BLOG
用户名：lhflinux
文章数：35
评论数：59
访问量：471613
注册日期：
阅读量：5863
阅读量：12276
阅读量：403766
阅读量：1092901
51CTO推荐博文
&&&&&&&首先在这里我要感谢我的同事&&哥的指教。。。QQQ&&
&&&&&& 最近数据库的数据猛然增多，以前的每天一个正备份的方式显然压力越来越大，对备份的数据传输考验也随之加大，所以考虑改成每周二服务器维护的时候坐全备份，剩下每天做增量备份。
&工作原理就是把每次完整备份的binlog 和postion取出，做增量备份的时候取当时的binlog和postion，使用mysqlbinlog 把这之间的数据读出到另一个文件。
注：本文中可能有个别地方会有些纰漏，请大家自行调试，因为下面的脚本是我用在真实环境中copy过来的，只是临时改了里面个别的参数，例如文件夹位置，密码等。。。。如有问题敬请谅解
一、完整备份
vi full_mysqldump.sh
###########
#########DIR_INFO############
date=`date +%Y%m%d`
BACK_DIR=&/home/profiles/liuhaifeng/fullbackup&
FILE_DIR=&/home/profiles/liuhaifeng&
#############################3MYSQL_BACK#########################
if [ ! -d $BACK_DIR$date ];then
&mkdir -p $BACK_DIR$date
/usr/local/mysql/bin/mysql -uroot
-h 127.0.0.1 -P 3306 -e &FLUSH TABLES WITH READ LOCK&
/usr/local/mysql/bin/mysqldump -uroot
-h 127.0.0.1 -P 3306 --default-character-set=gbk --opt --triggers -R --hex-blob --flush-logs --master-data=2 --all-databases & $BACK_DIR$date/fullbackup.sql
/usr/local/mysql/bin/mysql -u&root
-h 127.0.0.1 -P 3306 -e &UNLOCK TABLES&
##########################
################get postion###############
sed -n &22p& a.sql | awk -F'=|,|;' '{print $2,$4}' & $FILE_DIR/postion
################# FTP TO 16.1 ###############&
################# FTP&&& INFO ###############
HOST=&192.168.16.1&
USER=&user&
PASSWD=&password&
FTP_DIR=&database&
ftp -n && ! &/dev/null 2&&1 &
open $HOST
user $USER $PASSWD
cd $FTP_DIR
echo &sql backup suecss!!&
##############################################
#####################TRANSFER INFL#############
cd $BACK_DIR$date
if [ ! -f fullbackup.sql ]; then
&&&&&&& echo &mysqldump back file faild !!! please check&
&&&&&&& exit 1
/bin/tar zcf fullbackup_$date.tar.gz fullbackup.sql
ftp_db fullbackup_$date.tar.gz
二、做增量备份，根据上面完整备份所获得的postion以及binlog 与服务器当前的postion和binlog做增量备份，备份后把当前的postion以及binlog 存到postion文件中，留坐下一次增量备份的其实点。
vi& zl_backup.sh
#####for&zengliang backup########
DATE=`date +%Y%m%d`
OLDDATE=`date --date='10 days ago' +%Y%m%d`
BACK_DIR=&/data/mysql2/&
FILE_DIR=&/home/profiles/liuhaifeng&
startbinlog=`awk '{print $1}' $FILE_DIR/postion`
startpostion=`awk '{print $2}' $FILE_DIR/postion`
cd $FILE_DIR
&& mysql -uroot&-p123456 -S /tmp/mysql.sock3306 -h 192.168.16.3 -e &show master status\G;&|awk '{print $2}' & nowpostion
stopbinlog=`sed -n '3p' $FILE_DIR/nowpostion`
stoppostion=`sed -n '4p' $FILE_DIR/nowpostion`
if [ &$startbinlog& == &$stopbinlog& ]; then
&&&&&& mysqlbinlog --start-position=$startpostion --stop-position=$stoppostion $BACK_DIR$startbinlog && ch_zlback_$DATE.sql
&&&& startline=`awk &/$startbinlog/{print NR}& $BACK_DIR/mysql-bin-bj1.index`
&&&& stopline=`wc -l $BACK_DIR/mysql-bin-bj1.index |awk '{print $1}'`
&&&& for i in `seq $startline $stopline`
&&&&& binlog=`sed -n &$i&p& $BACK_DIR/mysql-bin-bj1.index |sed 's/.\///g'`
&&&&& case &$binlog& in
&&&&&&& &$startbinlog&)
&&&&&&&&&&&&&&&&&& mysqlbinlog --start-position=$startpostion $BACK_DIR$binlog && ch_zlback_$DATE.sql
&&&&&&&&&&&&&&&&&& ;;
&&&&&&& &$stopbinlog&)
&&&&&&&&&&&&&&&&&& mysqlbinlog --stop-position=$stoppostion $BACK_DIR$binlog && ch_zlback_$DATE.sql
&&&&&&&&&&&&&&&&&& ;;
&&&&&&&&&&&&&&&&&&& *)
&&&&&&&&&&&&&&&&&& mysqlbinlog $BACK_DIR$binlog && ch_zlback_$DATE.sql
&&&&&& esac
####for ftp######
HOST=&192.168.16.1&
USER=&user&
PASSWD=&password&
FTP_DIR=&database&
ftp -n && ! &/dev/null 2&&1 &
open $HOST
user $USER $PASSWD
cd $FTP_DIR
echo &sql backup suecss!!&
if [ -f ch_zlback_$DATE.sql ];then
&&& tar czf ch_zlback_$DATE.tar.gz ch_zlback_$DATE.sql
&&& sleep 3
&&& ftp_db ch_zlback_$DATE.tar.gz
&&& echo &$stopbinlog $stoppostion& & $FILE_DIR/postion
&&& rm -rf ch_zlback_$OLDDATE.sql ch_zlback_$OLDDATE.tar.gz
&& echo &$DATE backup fail!!!!&
三、做好计划任务
把增量备份脚本 和 完全备份脚本按照想要的时间写在计划任务中，做定时的增量备份。
当然还原的时候需要按照顺序依次还原。
&本文出自 “” 博客，请务必保留此出处
了这篇文章
类别：┆阅读(0)┆评论(0)
10:17:29 23:03:18 15:52:05 15:43:29 14:25:01 19:51:05 13:29:16mysql---整体备份和增量备份
对整张表或者整个甚至所有数据库进行备份。
增量备份：
对某一范围内的数据进行备份。
1、整体备份:
对表进行备份：
针对存储引擎为myisam的表，可以直接复制frm、myd、myi这三个文件起到备份的效果。需要还原的时候，再复制回来就可以达到还原效果。
如果存储引擎为innodb的表，就不这样简单了，因为所有表的数据和索引都存在一起（表空间）。一旦复制表空间，所有表的数据和索引就跟着一起复制过来。
如何进行备份？可以利用dump工具
先创建一个表，并插入一些数据
备份前需要退出mysql，利用mysqldump -u用户 -p 库名 表名 & 输出备份路径
输入密码后导出备份文件
在E盘的备份目录下出现了table1文件
打开table1文件，可以看到文件实际存储的是建表语句和插入数据的SQL语句
现在我们删除tab_one表，用table1来还原tab_one
再查看tab_one表，表已经被还原回来
如何备份多个表？
答：mysqldump -u用户 -p 数据库 表1 表2 && 表N & 备份文件路径
如何备份1个数据库？
答：mysqldump -u用户 -p -B 数据库 & 备份文件路径
如何备份多个数据库？
答：mysqldump -u用户 -p -B 库1 库2 && 库N &备份文件路径
如何备份所有数据？
答：mysqldump -u用户 -p -A &备份文件路径
2、增量备份
首先启动二进制日志功能，通过设置my.ini或者my.conf
在mysqld下面添加二进制备份路径（注意路径是左斜杠&/&而不是&\&，与windows不同）
重启mysql服务
会看到在E盘的beifen目录下多了2个文件
打开index文件，内容是日志文件的路径。日志文件可以有多个，命名规则是log-bin中设置的testBei后面加上6位数(从000001一直递增)
日志文件无法直接打开，可以利用mysqlbinlog工具打开
退出mysql客户端在windows控制台上（确保设置过环境变量，不然要进入mysql的bin目录）输入
mysqlbinlog 日志文件路径
二进制文件记录了除select操作以外的绝大多数操作（具体我也不太清楚，基本的增删改查是肯定要记录的）
因为每次操作的时间和&位置&都会被记录下来。所以要想还原数据有两种途径通过&时间&或&位置&。
通过时间还原：
--start-datetime=&还原数据的起始时间&
--stop-datetime=&还原数据的结束时间&
mysqlbinlog --start-datetime=&时间& 日志文件路径 | mysql -u用户 -p 从规定的起始时间还原到现在
mysqlbinlog --stop-datetime=&时间& 日志文件路径 | mysql -u用户 -p 从最开始还原到规定的结束时间
mysqlbinlog --start-datetime=&时间& --stop-datetime=&时间& 日志文件路径 | mysql -u用户 -p 从规定的起始时间还原到规定的结束时间
通过位置还原：
--start-position=&还原数据的起始位置&
--stop-position=&还原数据的结束位置&
mysqlbinlog --start-position=&位置& 日志文件路径 | mysql -u用户 -p 从规定的起始位置还原到现在
mysqlbinlog --stop-position=&位置& 日志文件路径 | mysql -u用户 -p 从最开始还原到规定的结束位置
mysqlbinlog --start-position=&位置& --stop-position=&位置& 日志文件路径 | mysql -u用户 -p 从规定的起始位置还原到规定的结束位置
现在删除tab_two表
查看日志可以发现删除tab_two的sql
现在想恢复tab_two，就应该设置好时间范围（建表时间--删除时间）。
再看一下结果
(window.slotbydup=window.slotbydup || []).push({
id: '2467140',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467141',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467142',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467143',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467148',
container: s,
size: '1000,90',
display: 'inlay-fix'MySQL数据库-备份恢复（6）
使用mysqldump加拷贝binlog的方式实现mysql增量备份
1. 主备模式下，一般使用备库来做数据库的备份；
2. 增量备份一定要基于完全备份之上。也就是说，在增量备份之前，一定要存在一个完全备份；
3. 完全备份首先要判断读取的master配置和重演master的配置是否相等，如果不相等表示存在读取了master的数据，但是还没有重演到slave的数据；
4. 在3中相等的情况下，停止复制线程，然后flush tables with read lock锁住备库；
5. 成功锁定之后，使用flush logs切换到新的binlog，并且记录下来（比如就是mysql-bin.000004，它就是我们增量备份的起点）；
6. 保持4中锁定，再使用mysqldump导出库；
7. 完全备份结束，此时我们拥有了一个导出文件和5中记录的binlog文件名；
8. 接下来可以释放4中持有的锁定，然后启动复制线程；
生产工作一段时间（比如24小时）后，此时我们需要增量备份了。
很明显，我们上次备份时备库切换到了新的binlog上，这样我们直接拷贝包括新的binlog文件开始的所有binlog文件即可。
但是正在被使用的那个binlog文件，我们直接拷贝的话，就必然有数据一致性的问题。
所以，我们在拷贝之前，必然要停止复制，然后锁住数据库，再切换到新的binlog（比如是mysql-bin.000015），然后拷贝到上一个mysql-bin.000014即可。
这样此次增量备份我们仅仅拷贝了004号到014号的11个文件。而下一次增量备份，我们就直接从mysql-bin.000015开始了。
ps，mysql的备份确实比较“弱”，上面的增量方案也只适用于一些小库，如果数据库超过百gb，那么这种方案在实施过程中还是有不少麻烦。
网上比较火的percona-xtrabackup备份工具，个人认为可控性不强，很容易报错，也不怎么推荐。
Oracle官方付费的backup工具也没有接触过。
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：7282次
积分：1245
积分：1245
排名：千里之外
原创：114篇
(3)(52)(2)(56)(14)