xtrabackup 介绍.ppt

1、percona extrabackup 介绍,2012.06.09 王洪权,内容概要,一 常见备份介绍 二 percona extrabackup介绍 三 extrabackup 日常备份 （全备和增备） 四 快速搭建slave，克隆slave 五 制定适合自己的备份策略,2019/7/7,逻辑备份-mysqldump,2019/7/7,逻辑备份-mysqldump,库很小的时候例如10G以下还可以接受-备份控制在1小时之内。 但是如果库很大的时候-100G ？ mysqldump可能耗时非常长,无法忍受。,2019/7/7,mysqldump 优缺点,优点：容易使用，可以轻松备份单个表，单个

2、库，恢复也很方便 缺点：单线程，备份很慢，消耗IO，尤其是在slave上备份，会造成slave滞后master,2019/7/7,Slave 备份,2019/7/7,slave 冷备-cp拷贝,2019/7/7,slave作为备份机,但是，仍然无法满足误删除等操作。,2019/7/7,Percona XtraBackup,开源热备工具，支持 mysql在线热备 http:/ Binary tar.gz Centos based, may not work on SUSE RPM RedHat based source,2019/7/7,支持的 MySQL 版本,MySQL 5.0 MySQL

3、5.1 + built-in InnoDB MySQL 5.1 + InnoDB-plugin MySQL 5.5 Percona Server 5.0 Percona Server 5.1 Percona Server 5.5 Percona XtraDB Cluster 5.5,2019/7/7,Binaries structure,innobackupex Perl script xtrabackup Percona Server 5.1; MySQL 5.1 + InnoDB-plugin xtrabackup_51 MySQL 5.0; Percona Server 5.0; MyS

4、QL 5.1+builtin InnoDB xtrabackup_55 MySQL 5.5; Percona Server 5.5 tar4ibd Only in XtraBackup-1.6; built-in in 2.0 xbstream Only in 2.0,2019/7/7,备份目录,MySQL files + xtrabackup_binlog_info xtrabackup_binlog_pos_innodb Only after apply-log xtrabackup_slave_info When slave-info is used xtrabackup_checkpo

5、ints xtrabackup_logfile xtrabackup_binary backup-f,2019/7/7,InnoDB 内部,2019/7/7,InnoDB 恢复,2019/7/7,备份思想,2019/7/7,备份思想,2019/7/7,恢复,2019/7/7,2019/7/7,full backup,2019/7/7,用Inobackupex 进行全备并且进行日常的恢复innobackupex /data/backupsinnobackupex -use-memory=1G -apply-log /data/backups/2010-06-08_10-42-44/ innoba

6、ckupex -copy-back /data/backups/2010-06-08_10-42-44/ chown -R mysql:mysql /var/lib/mysql 启动mysql,2019/7/7,Incremental backup,2019/7/7,Incremental backup-全备,2019/7/7,Incremental backup-增备1,2019/7/7,Incremental backup-增备2,2019/7/7,Incremental backup-基于全备的增备,2019/7/7,2019/7/7,2019/7/7,Log Sequential Nu

7、mber,SHOW ENGINE INNODB STATUSG- LOG - Log sequence number 180661841734,2019/7/7,2019/7/7,2019/7/7,2019/7/7,2019/7/7,2019/7/7,Xtrabackup_checkpoints,cat xtrabackup_checkpoints backup_type = full-backuped from_lsn = 0 to_lsn = 172573554953 last_lsn = 172573554953,2019/7/7,2019/7/7,2019/7/7,Incrementa

8、l apply,2019/7/7,Innobackupex incremental,记录了所有 InnoDB的增量改变 并不能控制myisam 和其他引擎，每次增备的时候也是一次全拷贝,2019/7/7,Innobackupex commands,innobackupex -incremental -incremental-basedir= /previous/full/或者/incremental /data/backup/inc,2019/7/7,Innobackupex from LSN,innobackupex -incremental -incremental-lsn=1725735

9、54953 /data/backup/inc,2019/7/7,Incremental apply complications,2019/7/7,理解恢复,2019/7/7,理解恢复,2019/7/7,增备必须在最后apply,否则增备会失效,Innobackupex -redo-only恢复全备 redo-only 注意必须加这个参数 innobackupex -apply-log -redo-only -use-memory=10G /data/backup/mysql-data恢复增备 innobackupex -apply-log -use-memory=1G /data/full/b

10、ackup -incremental-dir=/data/incremental/backup,2019/7/7,Point-In-Time Recovery,2019/7/7,Binary logs,2019/7/7,binlog 内容,2019/7/7,xtrabackup_binlog_info,Master binary log position Result of SHOW MASTER STATUS,binlog.000001 68212201,2019/7/7,mysql SHOW BINARY LOGS;+-+-+| Log_name | File_size |+-+-+| m

11、ysql-bin.000001 | 126 | mysql-bin.000002 | 1306 | mysql-bin.000003 | 126 | mysql-bin.000004 | 497 |+-+-+mysql SHOW MASTER STATUS;+-+-+-+-+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-+-+-+-+| mysql-bin.000004 | 497 | | |+-+-+-+-+,2019/7/7,cat /path/to/backup/xtrabackup_binlog_infomysql-bin

12、.000003 57 mysqlbinlog /path/to/datadir/mysql-bin.000003 /path/to/datadir/mysql-bin.000004 -start-position=57 -stop-datetime=“12-06-08 10:00:00“ | mysql -u root -p,2019/7/7,单表恢复,innobackupex -apply-log -export /path/to/backup 要在需要需要导入表的数据库上建立一个和备份时候的表相同数据结构的表，所以这个就建议在备份前对数据库的表结构用mysqldump 进行逻辑导出 CRE

13、ATE TABLE mytable (.) ENGINE=InnoDB; ALTER TABLE mydatabase.mytable DISCARD TABLESPACE; 拷贝恢复文件中的.ibd .exp文件到相关的数据库目录下，设置innodb_expand_import 为1 ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;,2019/7/7,快速搭建主从-克隆从库,1 用innobackupex快速搭建备库 2 用innobackupex 快速搭添加从库,从库克隆,2019/7/7,搭建slave,2019/7/7,搭建备库过程,1

14、 在主库上进行数据备份TheMaster$ innobackupex -user=yourDBuser -password=MaGiCdB1 /path/to/backupdir 2 在主库上进行数据恢复innobackupex -user=yourDBuser -password=MaGiCdB1 / -apply-log /path/to/backupdir/$TIMESTAMP/ 3 这部主要是把主库的备份拷贝到备库rsync -avprP -e ssh /path/to/backupdir/$TIMESTAMP TheSlave:/path/to/mysql/ 4 停掉备库，把先前安装

15、的备库的数据文件备份mv /path/to/mysql/$TIMESTAMP /path/to/mysql/datadir 5 赋予权限chown mysql:mysql /path/to/mysql/datadir 6 在主库上添加备库用于复制的用户GRANT REPLICATION SLAVE ON *.* TO repl$slaveip IDENTIFIED BY $slavepass; 7 拷贝主库的f文件到备库scp userTheMaster:/etc/mysql/f /etc/mysql/f 修改server-id log-bin skip-slave-start 8 查看备库中

16、xtrabackup_binlog_info 文件中的binlog文件 和具体位置cat /var/lib/mysql/xtrabackup_binlog_info 9 在备库上进行 change CHANGE MASTER TO MASTER_HOST=$masterip,MASTER_USER=repl,MASTER_PASSWORD=$slavepass,MASTER_LOG_FILE=TheMaster-bin.000001,MASTER_LOG_POS=481; 10 启动slave检查复制是否正常START SLAVE;SHOW SLAVE STATUS G,2019/7/7,克隆

17、slave,2019/7/7,相比于以前的搭建从库只是在备份的时候多了2个参数 -slave-info -safe-slave-backup 查找备份中xtrabackup_slave_info, 文件中的master_log_file 和master_log_pos 文件，然后再新的 slave上进行change,2019/7/7,每天全备+ binary logs,/data/backup/2012-04-01 /data/backup/2012-04-02 /data/backup/2012-04-03 /data/backup/2012-04-04 /data/backup/2012-

18、04-05 As many copies as you need + backup of binary logs,2019/7/7,全备增备混合,改变大小控制在 10% 以内 Full /data/backup/2012-04-01 Incremental /data/backup/2012-04-02 Incremental /data/backup/2012-04-03 Incremental /data/backup/2012-04-04 Full /data/backup/2012-04-05,2019/7/7,extrabackup 2.0,亮点： 1 支持压缩 2 支持并行 3 支持限制IO大小 4 支持流,2019/7/7,Thank you!,