绑定完请刷新页面
取消
刷新

分享好友

×
取消 复制
简单总结mysql逻辑备份
2019-05-02 19:36:27


一、不同引擎保证备份一致性内部实现不一样

1、对支持事务的引擎如InnoDB,加上--single-transaction保证数据一致性

--single-transaction参数使备份过程中,该session读到的数据都是启动备份时间点的数据。

可以理解为备份过程中的提交的事务时是看不到的,也不会备份进去。


2、对不支持事务的引擎如MyISAM,只能通过--lock-all-tables/--lock-tables锁表来保证数据一致性

(1)在刚开始的时候还会加一个数据库的读锁(FLUSH TABLES WITH READ LOCK),等开启事务后,

(2)再记录下数据库此时 binlog 的位置(showmaster status),马上解锁,再读取表的数据。

(3)等所有的数据都已经导完,就可以结束事务


二、mysqldump的--lock-tables一次性锁定当前库的所有表

使用show open tables where in_use >0命令,你会看到MyDB里面的所有表的In_use的值都为1,意味着是一次性锁定当前库的所有表。而不是锁定当前导出表。

准备一个稍微大一点的库,如果数据库太小,那么可能mysqldum命令一下子就导出了所有库,很难清晰的看到实验结果。

执行下面命令做逻辑备份

# mysqldump -u root -p --default-character-set=utf8  --opt --extended-insert=false --lock-tables MyDB > db_backup_MyDB.sql

Enter password: 

 

同时立即执行下面命令 

 

mysql> show open tables where in_use >0;

+----------+--------------------------------+--------+-------------+

| Database | Table                          | In_use | Name_locked |

+----------+--------------------------------+--------+-------------+

| MyDB     | AO_60DB71_VERSION              |      1 |           0 |


....................................................................


三、mysqldump实现导出的sql文件一行一个insert....value()

# mysqldump -uroot -p zcs0237 --skip-extended-insert

......

INSERT INTO `zcs0237` VALUES (1,32,37,38,'2016-10-18 06:19:24','susususususususususususu');

INSERT INTO `zcs0237` VALUES (2,37,46,21,'2016-10-18 06:19:24','susususususu');

INSERT INTO `zcs0237` VALUES (3,21,5,14,'2016-10-18 06:19:24','susu');


四、恢复全备后可通过之后的binlog增量恢复

1、--master-data获取在dump时候的master 的binlog文件名和position的位置(1=显示;2=注释)

当等于1时dump出来的数据,恢复在slave上是非常方便的。

# MySQLdump -u root -p --single-transaction --master-data --flush-log --database test > test.sql 

 --> --flush-log 表示备份开始之后的更行都切到下一个二进制日志


2、可以在备份的test.sql文件中前几行看到记录着备份当时的二进制日志信息

# vim test.sql 

--CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=436263492; 

---- Current Database: `test` 

..... 

3、通过之后的二进制日志进行恢复

# mysqlbinlog --start-position=436263492 mysql-bin.000004 > 00004.sql  


五、案例:mysqldump脚本

1、vi /tmp/backup.sh

#!/bin/bash

backupdir=/backup

#备份文件后缀时间

time=_` date +%Y_%m_%d_%H_%M_%S `

/usr/local/mysql/bin/mysqldump -uroot --socket=/data/mysql/mysql.sock --port=3306 -proot --single-transaction  --master-data=1 -A | gzip > $backupdir/backup$time.sql.gz

#删除7天之前的备份文件

find $backupdir -name backup*.sql.gz" -type f -mtime +7 -exec rm -rf {} \; > /dev/null 2>&1


2、crontab -e 

0 0 * * * /bin/sh /tmp/backup.sh >> "/backup/backup.log" 2>&1


六、mysqlpump支持并行

mysqlpump是MySQL5.7的官方工具,用于取代mysqldump,其参数与mysqldump基本一样。有以下提升:

1、并行备份数据库和数据库中的对象的,加快备份过程。

2、更好的控制数据库和数据库对象(表,存储过程,用户帐户)的备份。

3、备份用户账号作为帐户管理语句(CREATE USER,GRANT),而不是直接插入到MySQL的系统数据库。

4、备份出来直接生成压缩后的备份文件。

5、备份进度指示(估计值)。

6、重新加载(还原)备份文件,先建表后插入数据最后建立索引,减少了索引维护开销,加快了还原速度。

7、备份可以排除或则指定数据库。



七、mydumper-myloader支持多线程

1、mydumper是针对mysql数据库备份的一个轻量级第三方的开源工具,备份速度远高于原生态的mysqldump以及众多优异特性。

2、多线程逻辑备份,导出的文件形式是每个表一个文件,对于开发测试环境的误操作恢复十分有效

3、与mysqldump相同,备份时对 MyISAM 表施加FTWRL (FLUSH TABLES WITH READ LOCK), 会阻塞DML 语句

4、保证备份数据的一致性

5、支持文件压缩,支持导出binlog,支持多线程恢复,支持将备份文件切块

6、支持以守护进程模式工作,定时快照和连续二进制日志

7、案例

(1)开启16个备份进程,备份出所有的库以及二进制日志文件

mydumper -u root -p root123 --threads=16 -o /docker/mydumper/all_backup

(2)从全备的文件夹中恢复sampson库到samp库中去:

myloader -u root -p root123 --database=samp --source-db=sampson -d /docker/mydumper/all_backup -v 3




动力小刚于2019年4月  个人邮箱:zcs0237#163.com


分享好友

分享这个小栈给你的朋友们,一起进步吧。

MySQL干货资料
创建时间:2020-05-06 14:18:32
每天都有干货输出哦
展开
订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅:虚拟交易,一经交易不退款;若特殊情况,可3日内客服咨询

• 专区发布评论属默认订阅所评论专区(除付费小栈外)

技术专家

查看更多
  • 飘絮絮絮丶
    专家
戳我,来吐槽~