mysql备份原理与在TDSQL中的实践-雷海林

所需积分/C币:18 2016-07-27 18:01:02 2.95MB PDF

腾讯高级软件工程师雷海林带来了《MySQL备份原理与在TDSQL中的实践》的主题分享。
目录 1.备份的意义和基本原理 2. mysqldump分析 3. FTWRL锁的分析 4.逻辑复制的性能优化 5. xtrabackup原理分析 6.方案对比和选型建议 7. TDSQLI目前采用的备份方案 备份的意义 备份主要用来做数据恢复 A)错误的SQL B)业务或者数据库本身的Bug C)黑客攻击 D)审计或者测试,回档到指定时间点 E)DBA睡觉踏实 备份的基本原理-多引擎的结构 t1 t2 t tn MySQL-Server层 innodb 其他引擎 binlog(t5, t Buffer pool(t1, t2, t9) 如 MyISAM 表结构 4,t6.Ing redolog(t5, undolog 等 frm文件 t7,t5*,t8) (t1, t8, t10) datafile(t3, t5) 备份的基本原理-如何获取数据 核心是什么 致性全量数据+ BINLOG位置 离线备份 停机 锁表, FLUSH TABLES WITH READ LOCK 在线热备份 逻辑备份, select获取—致性数据+ BINLOG位置 物理备份,拷贝一致性数据文件+ BINLOG位置 mysqldump备份原理解析-针对 InnODB 510 Connect kp@10.231. 136. 34 as anonymous on 510 Init Db innodb test 510 Query *!40100 SET QOSQL MODE=*/ 510 Query SHOW CREATE DATABASE IF NOT EXISTS 'innodb_tester- 510 Query show tables 510 Query /*140103 SET TIME ZONE=+00: 00*510 Query show table status like't1' 510 Query SHOW STATUS LIKE 'binlog snapshot 510 Query SET SQL QUOTE SHOW CREATE=1 )-Plport] 510 Query SET SESSION TRANSACTION ISOLATION 510 query SET SESStON Character- set results s= binary 510 Query START TRANSACTION /140100 WITHC 510 Query SET SESSION Character set results = utf8 510 QHennngleHiwi amBlaciKo r'bin1og-snapshot 510 Query show fields from't1 510 Query 1 UNI OCK TARLE 510 Query SE!ECT /*! SOL NO CACHE */*FROM ES i ten swit eatables in a LE GROUD NAIEI 510 Init DB myisam test 510 Query SHOW CREATE DATABASE IF NOT EXISTS myisam test AND LOGFILE GROUP NAME IN(SELECT DISTINCT LO s10 Query show tables pM INFORMATION_ SCHEMA PARTITIONS WHERE TABLE_ SCH510 Query show table status like'tl 510 Query SELLCe Drs: INCT 1A3LESPA: L RAE, 510 Query SET SQLQUOTE_SHOW_CREATE-1 SPACE NAME IN(SFLECT DISTINCT TABL ESPACE NAME 510 Query SET SESSTON character set results ='"binary 510 Query show create table-1 510 Query SET SESSION character set results= 'utf8 510 Query optlm17 r switcn-deTaurt le show fields from t1 518 na aisnadb teststent snapshot 510 Query SELECT /*(49001 SQL NO CACHE*/* FROMD t1 备份的基本原理-—致性快照采取的操作 致性的数据,需要支持MVCC的引擎 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 致性的数据和 binlog快照 START TRANSACTION WITH CONSISTENT SNAPSHOT:(lock prepare_commit_mutex保存bnog位置) 获取 binlog位置 SHOW STATUS LIKE binlog_snapshot_% 备份的基本原理-如何获取—致性的位置 binlog与 redology的顺序一致性 事务提交的简易流程 Session Server Binlog Innodb Session Server Binlog commit START TRANSACTION WITH CONSISTENT Prepare SNAPSHOT (lock write&fsync redolog ock保存 binlog位 置,nock ok ok write&tsync binlog ok ok commit(unlock) ok k mysqldump备份原理解析-nDB与MAM混合 510 Connect kp@10. 231. 136.34 as anonymous on 510 Query 140100 SET @OSQL MODE=&/ 510 Query /*!40103 SET TIME ZONE-+00: 00 510 Query SHOW STATUS LIKE binlog snapshot 510 Query START TRANSACTION /<140100 WITH CONSISTENT SNAPSHOT dd-locks --skin-lock-tables -V 510 Query SHOW STATUS LIKE 'bin 1og_ snapshot_% 517 Init db innodb test 510 Quer UNLOCK TABLES 17 Query SHOW CREATE DATABASE IF NOT EXISTS innodb test 510 Quer set optimizer switch="semijoin=off 517 Query show tables AND LOGFILE GROUP NAME IN(SELECT DISTINCT LOGFILE- GROUP NAME FROM IN n9517 Query SET SQL_QUOTE_SHON_CREATE-1 517 Query show table status like't1 oth 510 Query SELECT DISTINCT TABLESPACE NAME, FILE NAME, LOGFILE GROU SPACE NAME IN (SELECT DISTINCT TABLESPACE NAME FROM INFORMATION SCHEI 517 Query SET SESSION character set results=binary 5170 Query show create table t1 510 Query set optimizer switch=default 517 Query SET SESSION character set results =utf8 510 Init DB innodb test 517 Query show fields from t1 517 Connect kp@10. 231. 136.34 as anonymous on 517 Query SELECT /140001 SQL NO CACHE *)TROMt1 517 Query x!40100 SET @@SQL MODE-3 517 Query *40103 SET TIME ZONE=+00: 00 517 Query FLUSH/!40101 LOCAL TABLES (yisan te 517 Query FLUSHTABLES WITH READ LOCK 517 Query SHOW CREATE DATABASE IF NOT EXISTS myisam test 517 Query SHOW MAS TERSTATUS Carry? 517 Query set optimizer switch= semijoin=off show caple status iike t 517 Query SELECT LOGFILE GROUP NAME, FILE NAME, To 517 Query SET SQL QUOTE SHOW CREATE=1 AND LOGFILE__GROUP_NAME IN(SELECT DISTINCT LOGFILE_GROU517 Query SET SESSION character__ set results='binary M INFORMATION SCHEMA PARTITIONS WHERE TABLE SCHEMA IN 517 Query show create table t1 517 Query SELECT DISTINCT TABLESPACE NAME, FILE NAN 517 Query SET SESSION character set results = utf8 SPACE NAME IN (SELECT DISTINCT TABLESPACE NAME FROM INI 517 Query show fields from t1 517 Query set optimizer switch=default 517 Query SELECT /140001-S0L NO_CACHE*/* FRo't1'

...展开详情

评论 下载该资源后可以进行评论 2

donglanyu_123 还可以,谢谢分享
2018-05-11
回复
liusg123 资源还算不错。
2016-11-28
回复
img
仲培艺

关注 私信 TA的资源

上传资源赚积分,得勋章
最新资源