• 售前

  • 售后

热门帖子
入门百科

MySQL系列之十二 备份与规复

[复制链接]
刘冠华 显示全部楼层 发表于 2021-8-14 14:22:46 |阅读模式 打印 上一主题 下一主题
目次


  • 系列教程
  • 一、备份计谋赘述

    • 1、备份的范例
    • 2、备份需要思量的因素
    • 3、备份的目标
    • 4、备份工具

  • 二、备份方案

    • 1、cp + tar == 物理冷备
    • 2、lvm快照 + binlog == 险些物理热备 + 增量备份
    • 3、mysqldump + InnoDB + binlog= 完全逻辑热备 + 增量备份
    • 4、Xtrabackup + InnoDB == 完全热备 + 增量备份
    • 5、利用Xtrabackup实现单表备份

  • 总结

系列教程

MySQL系列之开篇 MySQL关系型数据库基础概念
MySQL系列之一 MariaDB-server安装
MySQL系列之二 多实例配置
MySQL系列之三 基础篇
MySQL系列之四 SQL语法
MySQL系列之五 视图、存储函数、存储过程、触发器
MySQL系列之六 用户与授权
MySQL系列之七 MySQL存储引擎
MySQL系列之八 MySQL服务器变量
MySQL系列之九 mysql查询缓存及索引
MySQL系列之十 MySQL事件隔离实现并发控制
MySQL系列之十一 日志记载
MySQL系列之十二 备份与恢复
MySQL系列之十三 MySQL的复制
MySQL系列之十四 MySQL的高可用实现
MySQL系列之十五 MySQL常用配置和性能压力测试

一、备份计谋赘述


1、备份的范例

范例1:
       
  • 热备份:读写不受影响(MyISAM不支持热备,InnoDB支持热备)   
  • 温备份:仅可以实行读利用   
  • 冷备份:离线备份,读写利用均中止
范例2:
       
  • 物理备份:复制数据文件举行备份,占用较多的空间,速度快   
  • 逻辑备份:将数据导出至文本文件中,占用空间少,速度慢,大概丢失精度
范例3:
       
  • 完全备份:备份全部数据   
  • 增量备份:仅备份上次完全备份或增量备份以后变革的数据,备份较快,还原复杂   
  • 差别备份:仅备份上次完全备份以来变革的数据,备份较慢,还原简朴

2、备份需要思量的因素

       
  • 温备的持锁多久,在锁状态的环境下无法写入数据   
  • 备份产生的负载,要调空闲的时间备份   
  • 备份过程的时长,数据量大的时间时间会很长,要选择合适的方案   
  • 恢复过程的时长,备份数据需要即时测试

3、备份的目标

       
  • 数据库数据,每个表空间单独存放   
  • 二进制日志,需要和数据分开存储   
  • InnoDB的事件日志   
  • 存储过程、存储函数、触发器或事故调理器等   
  • 服务器的配置文件:/etc/my.cnf

4、备份工具

       
    1. mysqldump工具
    复制代码
    :逻辑备份工具,实用所有存储引擎温备;支持完全或部门备份;对InnoDB存储引擎支持热备;Schema(数据库的定义)和数据存储在一起。
  1. 用法:
  2.            shell> mysqldump [options] db_name [tbl_name ...]
  3.            shell> mysqldump [options] --databases db_name ...
  4.            shell> mysqldump [options] --all-databases
复制代码
  1. 选项:
  2.         -A:备份所有库
  3.         -B db_name1,[db_name2,...]:备份指定库
  4.         -E:备份相关的所有event scheduler
  5.         -R:备份所有存储过程和存储函数
  6.         --triggers:备份表相关触发器,默认启用,用--skip-triggers,不备份触发器
  7.         --master-data={1|2}:
  8.                  1:所备份的数据之前加一条记录为CHANGE MASTER TO语句,非注释,不指定默认为1
  9.                  2:记录为注释的CHANGE MASTER TO语句,注意:此选项会自动关闭--lock-tables功能,自动打开--lock-all-tables功能(除非开启--single-transaction)
  10.         -F:备份前滚动日志,锁定表完成后,执行flush logs命令,生成新的二进制日志文件,配合-A时,会导致刷新多次数据库,在同一时刻执行转储和日志刷新,则应同时使用--flush-logs和-x,--master-data或-single-transaction,此时只刷新一次;建议:和-x,--master-data或 --single-transaction一起使用
  11.         --compact 去掉注释,适合调试,生产不使用
  12.         -d:只备份表结构
  13.         -t:只备份数据,不备份create table
  14.         -n:不备份create database,可被-A或-B覆盖
  15.         --flush-privileges:备份前刷新授权表,备份mysql库或相关时需要使用
  16.         -f:忽略SQL错误,继续执行
  17.         --hex-blob:使用十六进制符号转储二进制列(例如,“abc”变为0x616263),受影响的数据类型包括BINARY, VARBINARY,BLOB,BIT
  18.         -q:不缓存查询,直接输出,加快备份速度
复制代码
  1. MyISAM备份选项:支持温备;不支持热备,所以必须先锁定要备份的库,而后启动备份操作
  2. -x,--lock-all-tables:加全局读锁,锁定所有库的所有表,同时加--single-transaction或--lock-tables选项会关闭此选项功能,注意:数据量大时,可能会导致长时间无法并发访问数据库
  3. -l,--lock-tables:对于需要备份的每个数据库,在启动备份之前分别锁定其所有表,默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
复制代码
InnoDB备份选项:支持热备,可用温备但不发起用

--single-transaction:此选项Innodb中推荐利用,不实用MyISAM,此选项会开始备份前,先实行START TRANSACTION指令开启事件此选项通过在单个事件中转储所有表来创建一致的快照。仅实用于存储在支持多版本控制的存储引擎中的表(目前只有InnoDB可以); 转储不包管与其他存储引擎保持一致。
​在举行单事件转储时,要确保有用的转储文件(精确的表内容和二进制日志位置),需要包管没有其他连接利用以下语句:ALTER TABLE,DROP TABLE,RENAME TABLE,TRUNCATE TABLE
​此选项和 --lock-tables(此选项隐含提交挂起的事件)选项是相互排挤备份大型表时,发起将--single-transaction选项和--quick联合一起利用
  1. InnoDB建议备份策略:
  2.         mysqldump –uroot –A –F –E –R  --single-transaction --master-data=1 --flush-privileges  --triggers --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql
  3. MyISAM建议备份策略:
  4.         mysqldump –uroot –A –F –E –R –x --master-data=1 --flush-privileges  --triggers --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql
复制代码
       
  • xtrabackup工具:由Percona提供支持对InnoDB做热备(物理备份)的工具,支持完全备份、增量备份
由Percona公司提供的mysql数据库备份工具,开源的能够对innodb和xtradb数据库举行热备的工具;
xtrabackup 是用来备份 InnoDB 表的,不能备份非 InnoDB 表;
innobackupex 脚本用来备份非 InnoDB 表,同时会调用 xtrabackup 命令来备份 InnoDB 表,还会和 MySQL Server 发送死令举行交互,如加全局读锁(FTWRL)、获取位点(SHOW SLAVE STATUS)等。即innobackupex是在xtrabackup 之上做了一层封装实现的;
虽然目前一样平常不消 MyISAM 表,只是 MySQL 库下的体系表是 MyISAM 的,因此备份基本都通过 innobackupex 命令举行;
xtrabackup版本升级到2.4后,相比之前的2.1有了比力大的变革:innobackupex 功能全部集成到 xtrabackup 内里,只有一个 binary步伐,别的为了兼容思量,innobackupex作为 xtrabackup 的软链接,即xtrabackup如今支持非Innodb表备份,并且Innobackupex在下一版本中移除,发起通过xtrabackup更换innobackupex。
利用innobakupex备份时,其会调用xtrabackup备份所有的InnoDB表,复制所有关于表布局定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件,同时还会备份触发器和数据库配置信息相关的文件。这些文件会被生存至一个以时间定名的目次中,在备份时,innobackupex还会在备份目次中创建如下文件:
       
  • 1)xtrabackup_checkpoints:备份范例(如完全或增量)、备份状态(如是否已经为prepared状态)和LSN(日志序列号)范围信息,每个InnoDB页(通常为16k巨细)都会包罗一个日志序列号,即LSN。LSN是整个数据库体系的体系版本号,每个页面相关的LSN能够表明此页面近来是怎样发生改变的;   
  • 2)xtrabackup_binlog_info:MySQL服务器当前正在利用的二进制日志文件及至备份这一刻为止二进制日志事故的位置;   
  • 3)xtrabackup_info:innobackupex工具实行时的相关信息;   
  • 4)backup-my.cnf:备份命令用到的配置选项信息;   
  • 5)xtrabackup_logfile:备份天生的日志文件。
  1. 用法:
  2.         innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/
复制代码
  1. 选项:
  2.     --user:该选项表示备份账号
  3.     --password:该选项表示备份的密码
  4.     --host:该选项表示备份数据库的地址
  5.     --databases:该选项接受的参数为数据名,如果要指定多个数据库,彼此间需要以空格隔开;如:"xtra_test dba_test",同时,在指定某数据库时,也可以只指定其中的某张表。如:"mydatabase.mytable"。该选项对innodb引擎表无效,还是会备份所有innodb表
  6.     --defaults-file:该选项指定从哪个文件读取MySQL配置,必须放在命令行第一个选项位置
  7.     --incremental:该选项表示创建一个增量备份,需要指定--incremental-basedir
  8.     --incremental-basedir:该选项指定为前一次全备份或增量备份的目录,与--incremental同时使用
  9.     --incremental-dir:该选项表示还原时增量备份的目录
  10.     --include=name:指定表名,格式:databasename.tablename
  11.     --apply-log:一般情况下,在备份完成后,数据尚且不能用于恢复操作,因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此,此时数据文件仍处理不一致状态。此选项作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态
  12.         --use-memory:该选项表示和--apply-log选项一起使用,prepare 备份的时候,xtrabackup做crash recovery分配的内存大小,单位字节。也可(1MB,1M,1G,1GB),推荐1G
  13.         --export:表示开启可导出单独的表之后再导入其他Mysql中
  14.         --redo-only:此选项在prepare base full backup,往其中merge增量备份时候使用
  15.         --copy-back:做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir
  16.         --move-back:这个选项与--copy-back相似,唯一的区别是它不拷贝文件,而是移动文件到目的地。这个选项移除backup文件,用时候必须小心。使用场景:没有足够的磁盘空间同事保留数据文件和Backup副本
复制代码
注意:
  1. 1)datadir目录必须为空。除非指定innobackupex --force-non-empty-directorires选项指定,否则--copy-backup选项不会覆盖;
  2. 2)在restore之前,必须shutdown MySQL实例,不能将一个运行中的实例restore到datadir目录中;
  3. 3)由于文件属性会被保留,大部分情况下需要在启动实例之前将文件的属主改为mysql,chown -R mysql:mysql /data/mysqldb
复制代码
       
  • mysqlbackup工具:热备份,MySQL Enterprise Edition组件   
  • mysqlhotcopy工具:险些冷备,仅实用于MyISAM存储引擎   
  • 基于lvm快照备份:险些热备,需要在拍快照前锁表   
  • tar + cp 等归档复制工具备份:完全冷备

二、备份方案


1、cp + tar == 物理冷备
  1. 将数据目录打包压缩备份,需要停服务,不推荐
复制代码
​1)备份:
  1. ~]# mkdir /backup
  2. ~]# systemctl stop mariadb #停止服务
  3. ~]# tar Jcf /backup/mariadb_all.tar.xz /var/lib/mysql/ #打包压缩
  4. backup]# systemctl start mariadb
复制代码
​2)还原:
  1. ~]# systemctl stop mariadb
  2. ~]# rm /var/lib/mysql/ -rf  #将损坏的库删除
  3. ~]# cd /backup/
  4. backup]# tar xf mariadb_all.tar.xz  #解压打包的数据库文件
  5. backup]# cp -av var/lib/mysql/ /var/lib/ #还原
  6. backup]# systemctl start mariadb #启动服务,恢复成功
复制代码

2、lvm快照 + binlog == 险些物理热备 + 增量备份

​1)备份:需要将数据库目次存放到lvm逻辑卷上
  1. ~]# systemctl stop mariadb
  2. ~]# rm /var/lib/mysql/ -rf  #将损坏的库删除
  3. ~]# cd /backup/
  4. backup]# tar xf mariadb_all.tar.xz  #解压打包的数据库文件
  5. backup]# cp -av var/lib/mysql/ /var/lib/ #还原
  6. backup]# systemctl start mariadb #启动服务,恢复成功
复制代码
  1. 准备lvm环境:
  2. ~]# pvcreate /dev/sda5
  3. ~]# vgcreate vg0 /dev/sda5
  4. ~]# lvcreate -n lv_data -L 10G vg0
  5. ~]# lvcreate -n lv_binlog -L 10G vg0
  6. ~]# mkfs.xfs /dev/vg0/lv_data
  7. ~]# mkfs.xfs /dev/vg0/lv_binlog
  8. ~]# mkdir -pv /data/{mysqldb,binlog}  #创建数据目录和二进制日志存放目录
  9. ~]# chown -R mysql:mysql /data/
  10. ~]# vim /etc/fstab
  11.         UUID=4e3d726a-d420-4c1e-812b-da315012ba86 /data/mysqldb xfs defaults 0 0
  12.         UUID=6dd98866-769f-4369-8738-291fbcc94ca1 /data/binlog xfs defaults 0 0
复制代码
  1. 配置数据库,模拟生成大量数据:
  2. ~]# yum install mariadb-server -y
  3. ~]# vim /etc/my.cnf
  4.     [mysqld]
  5.     datadir = /data/mysqldb  #指定数据库存放路径
  6.     log_bin = /data/binlog/mariadb-bin  #开启二进制日志记录,并且存放到指定路径
  7.     innodb_file_per_table = ON  #开启每个表单独的表空间
  8. ~]# systemctl start mariadb
  9. ~]# mysql  #连接数据库,这里省略了用户名和密码,以下都是如此
  10. MariaDB [(none)]> CREATE DATABASE school;  #创建一个测试的库
  11. MariaDB [(none)]> use school
  12. MariaDB [school]> CREATE TABLE testtb (id int auto_increment primary key,name char(30),age int default 20);  #创建一张数据表
  13. MariaDB [school]> DELIMITER //  #修改语句结束符为“//”
  14. MariaDB [school]> CREATE PROCEDURE pro_testtb()  #写一个存储过程,目的是生成十万条记录测试用
  15.     -> BEGIN
  16.     -> declare i int;
  17.     -> set i = 1;
  18.     -> while i < 100000
  19.     -> do INSERT INTO testtb(name,age) VALUES (CONCAT('testuser',i),i);
  20.     -> SET i = i + 1;
  21.     -> END while;
  22.     -> END//
  23. MariaDB [school]> DELIMITER ;  #记得将语句结束符再改回来
  24. MariaDB [school]> CALL pro_testtb;  #调用存储过程来
  25. MariaDB [school]> SELECT COUNT(*) FROM testtb;  #查看一下表中有十万条记录
  26. +----------+
  27. | COUNT(*) |
  28. +----------+
  29. |    99999 |
  30. +----------+
复制代码
  1. 开始备份:
  2. MariaDB [school]> FLUSH TABLES WITH READ LOCK;  #备份前切记锁表,防止用户继续写入
  3. MariaDB [school]> FLUSH LOGS;  #滚动一下二进制日志
  4. MariaDB [school]> SHOW MASTER LOGS;  #查看二进制日志的位置
  5. +--------------------+-----------+
  6. | Log_name           | File_size |
  7. +--------------------+-----------+
  8. | mariadb-bin.000001 |     30334 |
  9. | mariadb-bin.000002 |   1038814 |
  10. | mariadb-bin.000003 |  29178309 |
  11. | mariadb-bin.000004 |       528 |
  12. | mariadb-bin.000005 |       245 |  #将此出记录下来,我们后边需要用到
  13. +--------------------+-----------+
  14. ~]# lvcreate -L 5G -n lv_mysql_snap -s -p r /dev/vg0/lv_data  #需要再开一个终端创建快照,不要退出mysql终端
  15. MariaDB [school]> UNLOCK TABLES;  #创建快照后第一时间解锁,小心用户投诉
  16. ~]# mount -o nouuid,norecovery /dev/vg0/lv_mysql_snap /mnt/  #将快照挂载到/mnt
  17. ~]# cp -av /mnt/ /backup  #拷贝数据到备份目录
  18. ~]# umount /mnt/
  19. ~]# lvremove /dev/vg0/lv_mysql_snap  #拷贝完成后即时删除快照,影响服务器性能,到此完全备份完成~
复制代码
  1. 再加点数据:
  2. MariaDB [school]> CALL pro_testtb;  #让我们模拟再来插入十万条数据
  3. MariaDB [school]> SELECT COUNT(*) FROM testtb;
  4. +----------+
  5. | COUNT(*) |
  6. +----------+
  7. |   199998 |  #现在是二十万条记录数据了
  8. +----------+
复制代码
​2)还原:
  1. 模拟数据库损坏:
  2. ~]# rm -rf /data/mysqldb/*  #服务器崩溃,不多BB,直接清空库
  3. ~]# systemctl stop mariadb  #停服务
复制代码
  1. 开始还原:
  2. ~]# cp -av /backup/* /data/mysqldb/  #将备份的文件cp到对应的库目录下
  3. 在/etc/my.cnf的[mysqld]下加上skip_networking,禁止用户使用数据库,防止恢复过程中的数据写入
  4. ~]# systemctl start mariadb  #启动服务
  5. ~]# ls -1 /data/binlog/  #查看二进制日记的文件个数
  6.     mariadb-bin.000001
  7.     mariadb-bin.000002
  8.     mariadb-bin.000003
  9.     mariadb-bin.000004
  10.     mariadb-bin.000005
  11.     mariadb-bin.000006
  12.     mariadb-bin.index
  13. ~]# mysqlbinlog --start-position=245 /data/binlog/mariadb-bin.000005 > binlog.sql  #到出完全备份时间点以后的数据
  14. ~]# mysqlbinlog /data/binlog/mariadb-bin.000006 >> binlog.sql  #将之后的所有数据都追加到同一sql文件中
  15. ~]# mysql < binlog.sql  #利用二进制日志从我们之前完全备份的点开始增量还原
  16. ~]# mysql -e 'SELECT COUNT(*) FROM school.testtb'  #查看一下,二十万条记录都在,nice
  17. +----------+
  18. | COUNT(*) |
  19. +----------+
  20. |   199998 |
  21. +----------+
  22. 到/etc/my.cnf的[mysqld]下删除skip_networking,重启服务,到此还原完成~
复制代码
3、mysqldump + InnoDB + binlog= 完全逻辑热备 + 增量备份

​1)备份:这里我就不再天生数据了,就接着上边的环境做了
  1. ~]# mysqldump -A -F -E -R --single-transaction --master-data=2 --flush-privileges  > /backup/full-`date +%F-%T`.sql  #全库完全备份
复制代码
​2)模仿故障:
  1. MariaDB [(none)]> CREATE DATABASE db1;  #创建一个库
  2. MariaDB [(none)]> CREATE DATABASE db2;  #再创建一个库
  3. MariaDB [school]> use school;
  4. MariaDB [school]> DROP TABLE testtb;  #误操作,将我们二十万条记录的表删掉了
  5. MariaDB [school]> CREATE TABLE students (id INT(4) AUTO_INCREMENT PRIMARY KEY,name CHAR(30),age TINYINT);  #后续又有用户创建了其他的表
  6. MariaDB [school]> INSERT INTO students(name,age) VALUES ('user1',20);  #并且还加入了数据
复制代码
​3)还原:
  1. 此时,我们发现了有一个表不见了,需要紧急恢复,开始吧
  2. MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;  #锁表
  3. MariaDB [(none)]> FLUSH LOGS;  #刷新滚动一次二进制日志文件
  4. MariaDB [(none)]> SHOW MASTER LOGS;  #查看当前的日志状态
  5. +--------------------+-----------+
  6. | Log_name           | File_size |
  7. +--------------------+-----------+
  8. | mariadb-bin.000001 |     30334 |
  9. | mariadb-bin.000002 |   1038814 |
  10. | mariadb-bin.000003 |  29178309 |
  11. | mariadb-bin.000004 |       528 |
  12. | mariadb-bin.000005 |  29177760 |
  13. | mariadb-bin.000006 |  29177786 |
  14. | mariadb-bin.000007 |       953 |
  15. | mariadb-bin.000008 |       245 |
  16. +--------------------+-----------+
  17. ~]# systemctl stop mariadb  #停止服务,准备修复
  18. ~]# head -30 /backup/full-2018-06-14-05\:33\:47.sql |grep "CHANGE MASTER"
  19. -- CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000007', MASTER_LOG_POS=245;  #找到完全备份的日志点,在mariadb-bin.000007的245
  20. ~]# ls -1 /data/binlog/
  21. mariadb-bin.000001
  22. mariadb-bin.000002
  23. mariadb-bin.000003
  24. mariadb-bin.000004
  25. mariadb-bin.000005
  26. mariadb-bin.000006
  27. mariadb-bin.000007
  28. mariadb-bin.000008
  29. mariadb-bin.index
  30. ~]# mysqlbinlog --start-position=245 /data/binlog/mariadb-bin.000007 > /backup/binlog.sql #将完全备份之后的二进制日志导出来
  31. ~]# mysqlbinlog /data/binlog/mariadb-bin.000008 >> /backup/binlog.sql
  32. ~]# vim /backup/binlog.sql  #修改导出的sql文件,把误操作的SQL语句删除
  33. 删除"DROP TABLE `testtb` /* generated by server */"这行
复制代码
  1. 导入备份:
  2. ~]# rm -rf /data/mysqldb/*  #先清空故障库
  3. ~]# vim /etc/my.cnf  #编辑配置文件
  4.         在[mysqld]加入skip_networking,防止用户写入数据
  5. ~]# systemctl start mariadb  #启动服务
  6. ~]# mysql < /backup/full-2018-06-14-05\:33\:47.sql  #导入完全备份
  7. ~]# mysql < /backup/binlog.sql  #导入增量备份
  8. MariaDB [(none)]> show databases;  #查看一下我们的数据是否成功恢复
  9. +--------------------+
  10. | Database           |
  11. +--------------------+
  12. | information_schema |
  13. | db1                |  #已恢复
  14. | db2                |  #已恢复
  15. | mysql              |
  16. | performance_schema |
  17. | school             |
  18. | test               |
  19. +--------------------+
  20. MariaDB [(none)]> SELECT COUNT(*) FROM school.testtb;
  21. +----------+
  22. | COUNT(*) |
  23. +----------+
  24. |   199999 | #已恢复
  25. +----------+
  26. MariaDB [(none)]> SELECT * FROM school.students;
  27. +----+-------+------+
  28. | id | name  | age  |
  29. +----+-------+------+
  30. |  1 | user1 |   20 | #已恢复
  31. +----+-------+------+
  32. 到现在为止,已经完成恢复,把配置文件中的skip_networking删除,重启服务,大功告成~
复制代码
4、Xtrabackup + InnoDB == 完全热备 + 增量备份

​1)完全备份
  1. ~]# innobackupex --user=root /backup/  #这里省略了密码
复制代码
​2)增删数据
  1. MariaDB [school]> CALL pro_testtb;  #增加一些数据
  2. MariaDB [school]> SELECT COUNT(*) FROM testtb;  #现在有三十万条记录了
  3. +----------+
  4. | COUNT(*) |
  5. +----------+
  6. |   299998 |
  7. +----------+
  8. MariaDB [school]> INSERT INTO students VALUES (2,'user2',21);
  9. MariaDB [school]> UPDATE students SET age=19 WHERE id=1;
  10. MariaDB [school]> SELECT * FROM students;
  11. +----+-------+------+
  12. | id | name  | age  |
  13. +----+-------+------+
  14. |  1 | user1 |   19 |
  15. |  2 | user2 |   21 |
  16. +----+-------+------+
复制代码
​3)增量备份
  1. ~]# mkdir /backup/inc{1,2}  #创建增量备份的目录
  2. ~]# innobackupex --incremental /backup/inc1/ --incremental-basedir=/backup/2018-06-14_10-44-57/  #指定在完全备份的基础上增量备份
复制代码
​4)增删数据
  1. MariaDB [(none)]> CREATE DATABASE db3;
  2. MariaDB [(none)]> DROP TABLE school.students;  #误操作删除了表
  3. MariaDB [(none)]> use school
  4. MariaDB [school]> CALL pro_testtb;  #后续又有数据产生
  5. MariaDB [school]> SELECT COUNT(*) FROM testtb;
  6. +----------+
  7. | COUNT(*) |
  8. +----------+
  9. |   399997 |
  10. +----------+
  11. MariaDB [school]> SELECT * FROM students;  #到此出发现students表不见了,怎么办?
  12. ERROR 1146 (42S02): Table 'school.students' doesn't exist
复制代码
​5)故障出现
  1. ~]# rm -rf /data/mysqldb/*  #还原前清空数据目录
  2. MariaDB [(none)]> show databases;  #此时数据库已经没了
  3. +--------------------+
  4. | Database           |
  5. +--------------------+
  6. | information_schema |
  7. +--------------------+
复制代码
​6)紧急还原
  1. 恢复完全备份和增量备份:
  2. ~]# systemctl stop mariadb  #停止服务
  3. ~]# innobackupex --apply-log --redo-only /backup/2018-06-14_10-44-57/  #整理完全备份的数据,因为需要保留没有做完的事务日志所以一定要记得加"--redo-only"选项
  4. ~]# innobackupex --apply-log --redo-only /backup/2018-06-14_10-44-57/ --incremental-dir=/backup/inc1/2018-06-14_10-52-05/  #在完全备份的基础上将增量备份导入到一块,这里是最新的增量备份,"--redo-only"选项可以不加,加上也可以,为了误操作我就都加了
  5. ~]# ls /data/mysqldb/  #确认一下数据库目录是否为空
  6. ~]# innobackupex --copy-back /backup/2018-06-14_10-44-57/  #导入备份数据
  7. ~]# chown -R mysql:mysql /data/mysqldb/  #记得修改数据的所属组和所属者
  8. ~]# vim my.cnf 加入skip_networking,防止此时用户操作数据
  9. ~]# systemctl start mariadb  #启动服务,此时已经恢复到了最新的备份时的状态了
复制代码
  1. 依靠二进制日志,恢复最新增量备份到now的数据:
  2. ~]# cat /backup/2018-06-14_10-44-57/xtrabackup_binlog_info  #查看一下备份时的二进制日志记录点
  3.         mariadb-bin.000011      35740416
  4. ~]# ls -1 /data/binlog/  #看看我们的二进制日志文件记录到哪里了
  5. mariadb-bin.000001
  6. mariadb-bin.000002
  7. mariadb-bin.000003
  8. mariadb-bin.000004
  9. mariadb-bin.000005
  10. mariadb-bin.000006
  11. mariadb-bin.000007
  12. mariadb-bin.000008
  13. mariadb-bin.000009
  14. mariadb-bin.000010
  15. mariadb-bin.000011
  16. mariadb-bin.000012
  17. mariadb-bin.000013
  18. mariadb-bin.index
  19. ~]# mysqlbinlog --start-position=35740416 /data/binlog/mariadb-bin.000011 > /backup/binlog.sql  #将最新增量备份之后的二进制日志记录的数据导出来
  20. ~]# mysqlbinlog /data/binlog/mariadb-bin.000012 >> /backup/binlog.sql
  21. ~]# mysqlbinlog /data/binlog/mariadb-bin.000013 >> /backup/binlog.sql
  22. 编辑 /backup/binlog.sql 文件,将 "DROP TABLE `school`.`students` /* generated by server */" 删除,撤销误删除操作
  23. MariaDB [(none)]> SET sql_log_bin=0;  #先临时关闭二进制日记记录功能
  24. MariaDB [(none)]> source /backup/binlog.sql  #导入增量备份之后的最新数据
  25. 查看确认一下数据有没有恢复完整,把my.cnf中的skip_networking删除,重启服务
  26. 到此已经恢复到了最新的状态~
复制代码
5、利用Xtrabackup实现单表备份

​1)备份单表
  1. ~]# innobackupex --include="testdb.testlog" /backup  #备份表数据
  2. ~]# mysql -e 'SHOW CREATE TABLE testdb.testlog' > /backup/desc_testdb_testlog.sql  #备份表空间
  3. ~]# mysql -e 'DROP TABLE testdb.testlog'  #模拟故障,删除testlog表
复制代码
​2)还原单表
  1. ~]# innobackupex --apply-log --export /backup/2018-06-14_17-47-02/  #整理表数据
  2. ~]# vim /backup/desc_testdb_testlog.sql  #编辑创建表空间的语句,删除以下字段
  3.     Table   Create Table
  4.     testlog
  5. ~]# mysql testdb < /backup/desc_testdb_testlog.sql  #导入表空间
  6. ~]# mysql testdb -e 'DESC testlog'  #查看是否导入成功
  7. +-------+----------+------+-----+---------+----------------+
  8. | Field | Type     | Null | Key | Default | Extra          |
  9. +-------+----------+------+-----+---------+----------------+
  10. | id    | int(11)  | NO   | PRI | NULL    | auto_increment |
  11. | name  | char(30) | YES  |     | NULL    |                |
  12. | age   | int(11)  | YES  |     | 20      |                |
  13. +-------+----------+------+-----+---------+----------------+
  14. ~]# mysql -e 'ALTER TABLE testdb.testlog DISCARD TABLESPACE'  #清除表空间
  15. ~]# cd /backup/2018-06-14_17-47-02/testdb/
  16. testdb]# cp testlog.cfg testlog.exp testlog.ibd /var/lib/mysql/testdb/  #将表数据复制到库目录
  17. ~]# chown -R mysql:mysql /var/lib/mysql/testdb/  #修改所属者和所属组
  18. ~]# mysql -e 'ALTER TABLE testdb.testlog IMPORT TABLESPACE'  #导入表空间
复制代码
总结

本篇文章就到这里了,渴望可以给你带来一些资助,也渴望您能够多多关注草根技术分享的更多内容!

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作