MySQL数据库备份恢复介绍

1、技术创新，变革未来MySQL数据库备份恢复介绍01 MySQL简介与主流分支版本01MySQL 之父Michael “Monty” Widenius1、开源MySQL数据库的创始成员2、MySQL AB公司的首席技术官3、MySQL数据库第一行代码的作者4、MySQL数据库命名人5、MariaDB创始人兼首席技术官；6、独自完成撰写MySQL数据库服务器端95%的代码。MySQLMaxDBMariaDB01MySQL 介绍1999成立MySQL AB公司2000公布源码，采 用GPL协议， 正式进入开源 世界2008.1.16Sun收购 MySQL2019MySQL5.6 MySQL5.7 M

2、ySQL8.0（2016.8.25DMR、2018.4.8 GA）2005.10里程碑，发布 MySQL5.0，奠 定了迈向高性 能数据库基础2009.4.20Oracle收购Sun（MySQL5.5）1979 TcX UNIREG1995 Sun Solaris01MySQL 主流分支MySQLEnterprisePercona ServerMariaDBDrizzleMySQL官方MySQL号称最接近MySQL Enterprise发行版的 产品XtraDBMonty团队迭代更干净、快速的MySQL不兼容MySQL01MySQL 行业前景01全球最大网站Top2001国内MySQL行业应用

3、 互联网行业数据库MySQL市场第一 甲骨文公司的两款数据库（Oracle+MySQL）共占据着全世界的数据库市场份额的60%以 上，在中国（Oracle+MySQL）的使用更占到80%左右 中国前100个大企业/国有企业有99个以上使用Oracle为主MySQL为辅，中国前100个互 联网行业公司有95%以上使用MySQL为主Oracle/NoSQL为辅 MySQL数据库在互联网行业90%以上的使用比例，最典型的就是BAT了，近2年开始MySQL扩展到金融、通信、生产制造、快速消费品零售、物流运输、医疗、政府等行业01MySQL 介绍Oracle VS MySQL ： 企业服务软件的开源与闭

4、源之争01MySQL 介绍Oracle VS MySQL ： 企业服务软件的开源与闭源之争Oracle： 功能强大保障体系充分，MOS成熟度高BUG更新较快并发机制粒度细，并发高软件成本高运维成本依赖数据规模，小规模数据库 运维成本远高于MySQL，大规模数据库 运维成高低于MySQL。传统行业的霸主技术掌控度低MySQL：功能略有不足保障体系成熟度不如Oracle BUG更新不如Oracle并发机制较粗，比Mongo类NoSQL要强， 并发性总体不如Oracle软件成本低运维成本依赖数据规模，小规模数据库 运维成本低于Oracle，大规模数据库运维 成高低于Oracle。互联网行业技术掌控度

5、高06 MySQL备份恢复简介06MySQL备份恢复DBA四条守则备份重于一切三思而后行rm是危险的你来制定规范做任何重大操作的时候都要经过测试！06为什么要备份故障类型描述恢复方法用户错误用户意外删除数据或将数据更新 为错误的值从备份中恢复删除的数据断电一个或多个系统断电使用连续供电系统（UPS）硬件故障一个或多个系统组件发生故障使用冗余系统或复制数据软件故障数据在使用过程中被修改或丢失该类故障可能很难侦查，需要从备份中 恢复数据基础设施问题放置设备的基础设施无法使用可能需要一套新的基础设施，用于建立 一个可以正常运行的系统网络故障生产数据不能被访问重新连接或使用可连接的复制数据蓄意破坏数据

6、被有意泄露或破坏排查、关闭安全漏洞，并审查数据常见的数据丢失原因：06勒索病毒06备份类别06逻辑与物理备份逻辑备份（logical backup）仅仅是一些普通SQL SELECT查询的合集。通常， 通过表扫描，即遍历每条记录创建逻辑备份。物理备份（physical backup）是指原始二进制数据（文件）的副本，这 些副本通常是操作系统级别的文件。任何复制数据、索引和缓冲内存（文件）且不是逐条记录访问的备份方法都为物理备份。存储成本？ 速度？灾难恢复？06备份恢复介绍MySQL企 业级备份MySQL实 用工具MySQLdump物理复制LVM、ZFS快照XtraBackup热备份是（仅Inn

7、oDB）是（仅InnoDB）是（仅 InnoDB，需 添加single- transaction选否是（需要全局 表刷新锁）是（仅InnoDB 和XtraDB）项）开销收费免费免费免费免费免费备份速度中等慢慢快快中等恢复速度快慢慢快快快类型物理逻辑逻辑物理物理物理OS所有所有所有所有支持LVM的操 作系统所有引擎不包括内 存表所有所有不包括内存表不包括内存表不包括内存表哪一种方案适合你？06物理拷贝(停数据库)roottest # /usr/local/mysql/bin/mysqladmin -uroot -p shutdownroottest mysql# tar -cvf data.ta

8、r data/roottest mysql# scp r data root远程备份机IP:/新目录停数据库备份datadir恢复数据库roottest # /usr/local/mysql/bin/mysqladmin -uroot -p shutdown roottest mysql# mv data data.bakroottest mysql# tar f搭建主从06物理拷贝（加全局锁）将MySQL设置为只读状态的命令：mysql set global read_only=1; Query OK, 0 rows affected (0.00 sec)mysql flush tables

9、 with read lock; Query OK, 0 rows affected (0.00 sec)roottest mysql# tar -cvf data.tar data/roottest mysql# scp r data root远程备份机IP:/新目录注：read_only可以限定普通用户进行数据修改的操作， 但不会限定具有super权限的用户的数据修改操作mysql unlock tables;mysql set global read_only=0;将MySQL设置为读写状态的命令：06XtraBackup工具使用干什么用？是什么？1、Percona公司 开源软件2、支持

10、Innodb、Mysiam、Xtradb等存储引擎数据的在线热备 3、Myisam型温备06XtraBackup使用原理The backup and restore involves conceptually 3 steps:backup operation.Apply log operation to prepare backup for restore.copy-back operation to restore backup to server repository.06XtraBackup使用Percona XtraBackup软件下载地址https:/downloads/XtraBa

11、ckup/LATEST/rpm -aq | grep libgcryptrpm包、 二 进 制 包 以 及 源 码 编 译 三 种 安 装 方 式06XtraBackup使用Percona XtraBackup软件安装在Linux操作系统创建/Xtrabackup目录，上传介质包percona-xtrabackup-2.3.10-Linux-x86_64.tar.gz # mkdir p /Xtrabackupsftp cd /Xtrabackupsftp put percona-xtrabackup-2.3.10-Linux-x86_64.tar.gz解压介质包，并设置环境变量# cd /X

12、traBackup/# tar zxvf percona-xtrabackup-2.3.10-Linux-x86_64.tar.gz # vi /etc/profileexport PATH=/mysql/app/bin:/XtraBackup/percona-xtrabackup-2.3.10-Linux-x86_64/bin:$PATH# source /etc/profile # su -至此，Percona Xtrabackup软件部署完成06XtraBackup使用06XtraBackup使用MySQL服务器全局备份rootmaster XtraBackup# mkdir xglob

13、al rootmaster XtraBackup# mkdir xincr1 rootmaster XtraBackup# mkdir xincr2 rootmaster XtraBackup# which innobackupex/XtraBackup/percona-xtrabackup-2.3.10-Linux-x86_64/bin/innobackupexrootmaster XtraBackup# innobackupex -defaults-file=/etc/f -socket=/tmp/mysql.sock - port=3306 -no-timestamp -uroot -p

14、mysql /XtraBackup/xglobalrootmaster xglobal# pwd/XtraBackup/xglobal rootmaster xglobal# ls -lh-rw-r- 1root root387May 18 16:34 f-rw-r- 1root root76M May 18 16:33 ibdata1drwx- 2 root root4.0KMay 18 16:34 performance_schema-rw-r- 1root root28May 18 16:34 xtrabackup_binlog_info-rw-r- 1root root117May 1

15、8 16:34 xtrabackup_checkpoints-rw-r- 1root root555May 18 16:34 xtrabackup_info-rw-r- 1root root2.5KMay 18 16:34 xtrabackup_logfile06XtraBackup使用ster XtraBackup# which innobackupexp/percona-xtrabackup-2.3.10-Linux-x86_64/bin/innobackupexster XtraBackup# innobackupex -defaults-file=/etc/f -socket=/tmp

16、/mysql.sock -no-timestamp -uroot -pmysql /XtraBackup/xglobal ster xglobal# pwdp/xglobalster xglobal# ls -lhroot root387May 18 16:34 f root root76M May 18 16:33 ibdata1MySQL服务器全局备份rootmaster XtraBackup# mkdir xglobal rootmaster XtraBackup# mkdir xincr1 rootmaster XtraBackup# mkdir xincr2 rootma/XtraB

17、ackurootma port=3306 rootma/XtraBacku rootma-rw-r 1-rw-r- 1drwx- 2 root root4.0KMay 18 16:34 performance_schema-rw-r- 1root root28May 18 16:34 xtrabackup_binlog_info-rw-r- 1root root117May 18 16:34 xtrabackup_checkpoints-rw-r- 1root root555May 18 16:34 xtrabackup_info-rw-r- 1root root2.5KMay 18 16:3

18、4 xtrabackup_logfile06XtraBackup使用MySQL服务器基于全局第一次增量备份rootmaster xincr1innobackupex -defaults-file=/etc/f -socket=/tmp/mysql.sock -port=3306 -no- timestamp -uroot -pmysql -incremental-basedir=/XtraBackup/xglobal -incremental /XtraBackup/xincr1rootmaster xincr1# pwd/XtraBackup/xincr1rootmaster xincr1#

19、 ls -lh-rw-r- 1root root 387May 18 16:44 f-rw-r- 1root root 16KMay 18 16:43 ibdata1.delta-rw-r- 1root root44May 18 16:43 ibdata1.metadrwx- 2 root root4.0KMay 18 16:44 performance_schema-rw-r- 1root root28May 18 16:44 xtrabackup_binlog_info-rw-r- 1root root123May 18 16:44 xtrabackup_checkpoints-rw-r-

20、 1root root632May 18 16:44 xtrabackup_info-rw-r- 1root root2.5K May 18 16:44 xtrabackup_logfile06XtraBackup使用MySQL服务器基于第一次增量的第二次增量备份rootmaster xincr2# innobackupex -defaults-file=/etc/f -socket=/tmp/mysql.sock -port=3306 -no- timestamp -uroot -pmysql -incremental-basedir=/XtraBackup/xincr1 -incremen

21、tal /XtraBackup/xincr2rootmaster xincr2# pwd/XtraBackup/xincr2rootmaster xincr2# ls -lh-rw-r- 1 root root387May 18 16:53 f-rw-r- 1 root root16KMay 18 16:53 ibdata1.delta-rw-r- 1 root root44May 18 16:53 ibdata1.metadrwx- 2 root root4.0K May 18 16:53 performance_schema-rw-r- 1 root root28May 18 16:53

22、xtrabackup_binlog_info-rw-r- 1 root root123May 18 16:53 xtrabackup_checkpoints-rw-r- 1 root root631May 18 16:53 xtrabackup_info-rw-r- 1 root root2.5KMay 18 16:53 xtrabackup_logfile06XtraBackup使用MySQL目标服务器环境准备rootmaster XtraBackup# tar -cvf xglobal.tar xglobal/ rootmaster XtraBackup# tar -cvf xincr1.

23、tar xincr1/ rootmaster XtraBackup# tar -cvf xincr2.tar xincr2/rootmaster XtraBackup# scp *.tar root26:/XtraBackup/rootslave XtraBackup# tar -xvf xglobal.tar rootslave XtraBackup# tar -xvf xincr1.tar rootslave XtraBackup# tar -xvf xincr2.tar06XtraBackup使用MySQL目标服务器全局备份Prepare全量备份应用redo，前滚已提交的事务rootsl

24、ave XtraBackup# innobackupex -apply-log -redo-only /XtraBackup/xglobal基于全量备份，第一次增量前滚，并应用redo，前滚已提交的事务rootslave XtraBackup# innobackupex -apply-log -redo-only -incremental-dir=/XtraBackup/xincr1/XtraBackup/xglobal基于全量备份，第二次增量前滚，并应用redo，前滚已提交的事务，回滚未提交的事务rootslave XtraBackup# innobackupex -apply-log -i

25、ncremental-dir=/XtraBackup/xincr2 /XtraBackup/xglobal只前滚不回滚06XtraBackup使用MySQL目标服务器全局恢复rootslave # service mysqld stopShutting down MySQL. OK rootslave # mv /mysql/app/data/* /tmp/rootslave # innobackupex -defaults-file=/etc/f -copy-back /XtraBackup/xglobal rootslave # chown -R mysql:mysql /mysql/ap

26、p/datarootslave # service mysqld startStarting MySQL. OK -mv-back06XtraBackup使用MySQL服务器单表备份rootmaster XtraBackup# mkdir xtable rootmaster xtrabackup# mkdir xtable_incr1单表备份1：rootmaster xtrabackup# innobackupex -defaults-file=/etc/f -socket=/tmp/mysql.sock -port=3306 -no-timestamp -databases=xtraback

27、up.xtrabackup_20180521 -uroot -pmysql /XtraBackup/xtable单表备份2：rootmaster xtrabackup# innobackupex -defaults-file=/etc/f -socket=/tmp/mysql.sock -port=3306 -no- timestamp -include=xtrabackup.xtrabackup_20180521 -uroot -pmysql /XtraBackup/xtable基于单表全量的第一次增量备份：rootmaster xtrabackup# innobackupex -defau

28、lts-file=/etc/f -socket=/tmp/mysql.sock -port=3306 -no-timestamp -slave-info -databases=xtrabackup.xtrabackup_20180521 -uroot -pmysql -incremental- basedir=/XtraBackup/xtable -incremental /XtraBackup/xtable_incr106XtraBackup使用MySQL目标服务器环境准备rootmaster XtraBackup# tar -cvf xtable.tar xtable/ rootmaste

29、r XtraBackup# tar -cvf xtable_incr1.tar xtable_incr1/rootmaster XtraBackup# scp *.tar root26:/XtraBackup/ root26s password:xtable_incr1.tar100% 570KB 570.0KB/s00:00 xtable.tar100%76MB 76.1MB/s00:01rootslave XtraBackup# tar -xvf xtable.tar rootslave XtraBackup# tar -xvf xtable_incr1.tar06XtraBackup使用

30、MySQL目标服务器单表备份Prepare单表全量备份应用redo，前滚已提交的事务rootslave XtraBackup# innobackupex -apply-log -redo-only /XtraBackup/xtable基于单表全量备份，增量前滚，并应用redo，前滚已提交的事务，回滚未提交的事务rootslave XtraBackup# innobackupex -apply-log -incremental-dir=/XtraBackup/xtable_incr1 /XtraBackup/xtable生成单表.exp表空间扩展文件rootslave XtraBackup# i

31、nnobackupex -apply-log -export /XtraBackup/xtablextrabackup: This target seems to be already prepared.xtrabackup: export option is specified.xtrabackup: export metadata of table xtrabackup/xtrabackup_20180521 to file ./xtrabackup/xtrabackup_20180521.exp (1 indexes)06XtraBackup使用MySQL目标服务器单表恢复rootsla

32、ve # service mysqld stopShutting down MySQL. OK rootslave # mv /mysql/app/data/ib* /tmp/rootslave XtraBackup# innobackupex -defaults-file=/etc/f -copy-back -force-non-empty- directories /XtraBackup/xschemarootslave # chown -R mysql:mysql /mysql/app/data rootslave # service mysqld startStarting MySQL

33、. OK 06XtraBackup使用MySQL目标服务器单表恢复rootslave xtrabackup# pwd/XtraBackup/xtable/xtrabackup rootslave xtrabackup# ls -rtlh-rw-r- 1 root root 96K May 21 15:18 xtrabackup_20180521.ibd-rw-r- 1 root root 8.4K May 21 15:18 xtrabackup_20180521.frm-rw-r-r- 1 root root 16K May 21 15:18 xtrabackup_20180521.exp-r

34、w-r-r- 1 root root 444 May 21 15:18 xtrabackup_20180521.cfg rootslave xtrabackup# mysqlfrm -diagnostic xtrabackup_20180521.frmCREATE TABLE xtrabackup_20180521 (id int(11) NOT NULL,time datetime NOT NULL) ENGINE=InnoDB;05XtraBackup使用MySQL目标服务器单表恢复（表空间传输） 生产端获取建表语句并在目标端创建rootlocalhost xtrabackup CREAT

35、E TABLE xtrabackup_20180521 (id int(11) NOT NULL, time datetime NOTNULL ) ENGINE=InnoDB;目标端卸载ibd文件rootlocalhost xtrabackup ALTER TABLE xtrabackup.xtrabackup_20180521 DISCARD TABLESPACE;目标端拷贝备份文件至库目录rootslave xtrabackup# cp xtrabackup_20180521.cfg xtrabackup_20180521.exp xtrabackup_20180521.ibd/mysql

36、/app/data/xtrabackup目标端修改拷贝文件权限rootslave xtrabackup# chown mysql:mysql /mysql/app/data/xtrabackup/xtrabackup_20180521.cfgrootslave xtrabackup# chown mysql:mysql /mysql/app/data/xtrabackup/xtrabackup_20180521.exp rootslave xtrabackup# chown mysql:mysql /mysql/app/data/xtrabackup/xtrabackup_20180521.i

37、bd目标端加载表空间.ibd 文件rootlocalhost xtrabackup ALTER TABLE xtrabackup.xtrabackup_20180521 IMPORT TABLESPACE;06XtraBackup使用mysqlfrm等工具安装：# rpm -ivh mysql-connector-python-2.1.6-1.el6.x86_64.rpm # rpm -ivh mysql-utilities-1.6.4-1.el6.noarch.rpm下载地址/archives/c-python/ /archives/utilities/06XtraBackup使用全库备份i

38、nnobackupex -defaults-file=/etc/f -socket=/tmp/mysql.sock -uroot -pmysql -slave-info /XtraBackup/backup单库/多库备份innobackupex -defaults-file=/etc/f -socket=/tmp/mysql.sock -uroot -pmysql -databases=yzw test/xtrabackup/backup单表/多表备份innobackupex -defaults-file=/etc/f -socket=/tmp/mysql.sock -uroot -pmysq

39、l -databases=yzw.a hzmc.a /xtrabackup/backup应用innobackupex -apply-log /XtraBackup/2019-04-07_21-38-38恢复innobackupex -defaults-file=/etc/f -copy-back(-move-back) /XtraBackup/2019-04-07_21-38-3806MySQLdump使用了解下MySQLdump的优缺点：优点：简单灵活，备份空间小，数据格式清晰，方便编辑；可以进行不 同级别备份，全局、单库、单表；对InnoDB表可以进行一致性备份，适 用于轻量级规模的场景。

40、缺点：比文件级（物理）备份的二进制文件复制（如MEB、LVM或 XtraBackup等）花费的时间要长，并且在生产环境出现故障后无法试下 快速恢复。06MySQLdump使用Q :MySQLdump如何实现对Innodb表的一致性备份？A :MySQLdump在热备份Innodb表的时候，可以指定single-transaction选项，该选项会 指示Innodb存储引擎以一致性读的方式读取表。因此，你做的任何更改都会应用到表上，但 是数据在备份时被冻结。此时，不能使用DDL语句，如ALTER TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLE。这是因为一

41、致性读无法隔离DDL更改。This backup acquires a global read lock on all tables (using FLUSH TABLES WITH READ LOCK) at the beginning of the dump. As soon as this lock has been acquired, the binary log coordinates are read and the lock is released.06MySQLdump使用06MySQLdump使用06MySQLdump使用了解下MySQLdump转储的备份：SQL Forma

42、tMysqldump创建的备份可以是一组SQL语句集，运行这些SQL语句便可以重建数据库。Delimited-Text FormatMysqldump创建的备份也可以是以特定分隔符分隔的一个或者多个文本文件（TXT），可以 直接对数据进行查找、替换和修改等操作。06MySQLdump使用Command-Line Format-secure-file-priv=dir_nameSystem VariableNamesecure_file_privVariable ScopeGlobalDynamic VariableNoPermitted Values (= 5.6.34)TypestringD

43、efaultplatform specificValid ValuesemptydirnameNULL06MySQLdump使用常用参数-A全库导出的只有表、视图、触发器默认不导出存储函数、存储过程、事件-E事件-R存储过程及函数等定义好的MySQL程序-d只导出结构-t只导出数据-f忽略错误-F-flush-logs 导出之前刷新日志文件-dump-slave备库端使用,获取备份数据的Binlog位置和Binlog文件名-master-data=2|1获取备份数据的Binlog位置和Binlog文件名-single-transaction锁定但是可以进行写入操作，无法ddl操作,保证inno

44、db引擎表一致性06MySQLdump使用06MySQLdump使用MySQL服务器全局备份rootmaster /# mysqldump -uroot -pmysql -single-transaction -master-data=2 -E -R -all-databases full.sql06MySQLdump使用MySQL服务器全局对象统计（主、从端）Mysql select sum(a+b+c+d+e+f+g+h+i+j+k) as OBJECTS from (select count(*) as a from information_schema.SCHEMATA where S

45、CHEMA_NAME in(hzmc)t1,(select count(*) as b from information_schema.EVENTS where EVENT_SCHEMA in(hzmc)t2, (select count(*) as c from information_schema.PARTITIONS where TABLE_SCHEMA in(hzmc)t3,(select count(*) as d from information_schema.TABLE_CONSTRAINTS where CONSTRAINT_SCHEMA in(hzmc)t4,(select

46、count(*) as e from information_schema.ROUTINES where ROUTINE_SCHEMA in(hzmc) and ROUTINE_TYPE=PROCEDURE)t5,(select count(*) as f from information_schema.ROUTINES where ROUTINE_SCHEMA in(hzmc) and ROUTINE_TYPE=FUNCTION)t6,(select count(*) as g from information_schema.TABLES where TABLE_SCHEMA in(hzmc

47、)t7,(select count(*) as h from information_schema.TRIGGERS where TRIGGER_SCHEMA in(hzmc)t8, (select count(*) as i from information_schema.VIEWS where TABLE_SCHEMA in(hzmc)t9, (select count(*) as j from information_schema.STATISTICS where INDEX_SCHEMA in(hzmc)t10, (select count(*) as k from mysql.ser

48、vers)t11);06MySQLdump使用MySQL服务器单库/多库备份rootmaster logicbak# mysqlshow -uroot -p|grep test1|awk print $2 Test1Eg:mysqldump -uroot -pmysql -single-transaction -master-data=2 -E -R -databases yzw employees hzmc yzw.sqlrootmaster logicbak# mysqldump -uroot -p -single-transaction -master-data=2 -routines

49、-events - flush-logs -databases test1 test1.sql06MySQLdump使用MySQL服务器备份扩展备份sql文件中含trigger创建语句rootmaster logicbak# cat test1.sql |grep -A4 -B1 -i trigger DELIMITER ;/*!50003 CREATE*/ /*!50017 DEFINER=rootlocalhost*/ /*!50003 trigger cons1_trigger AFTER insert on cons1 for each rowbeginINSERT INTO cons

50、2 VALUES (New.id, new.c1, new.id2, new.id3); end */;DELIMITER ;06MySQLdump使用备份sql文件中含function创建语句rootmaster logicbak# cat test1.sql |grep -A4 -B1 -i function“ DELIMITER ;CREATE DEFINER=rootlocalhost FUNCTION func1(m_id INT) RETURNS int(11) BEGINRETURN(SELECT count(*) from cons1 where id=m_id);END ;

51、DELIMITER ;06MySQLdump使用备份sql文件中含procedure创建语句rootmaster logicbak# cat test1.sql |grep -A11 -B1 -i procedure“ DELIMITER ;CREATE DEFINER=rootlocalhost PROCEDURE myproc() begindeclare sqltext varchar(4000);declare i bigint; set i=1;while i double.sql# mysqldump -uroot -pmysql -single-transaction -master-data=2 -E -R yzw test test.sql06MySQLdump使用按条件导出# mysqldump -uroot -pmysql -single-transaction -master-data=2 yzw test -where=id test.sql只导出结构#用于测试升级#