Mysql(五)-数据恢复

tonwei139

　　########################################################################################
　　增量备份与增量恢复
　　一、启用binlog日志实现增量备份
　　1、binlog日志介绍：
　　又被称为二进制日志，是mysql数据库服务日志文件的一种。
　　好处：
　　记录 除查询以外的所有SQL命令
　　可用于数据恢复
　　配置mysql主从同步的必要条件
　　############################################################
　　2、启用binlog日志
　　#vim /etc/my.cnf
　　[mysql]
　　server_id=id号               //数值范围1-255，不能重复
　　log-bin[=dir/name]
　　//日志存放默认位置为/var/lib/mysql/，如果需要自己指定日志文件的名称以及存放位置，需要写成中括号内的格式。
　　注意：自己指定存放的位置的目录需要修改文件属主及属组。chown -R mysql:mysql   存放路径。
　　binlog_format="mixed"           //日志格式
　　#systemctl restart mysqld
　　#ls /var/lib/mysql/
　　主机名-bin.000001              //默认的binlog日志文件名
　　主机名-bin.index               //记录当前已有的binlog日志文件名
　　日志格式：
　　statement：每一条修改数据的命令都会记录在binlog日志中。
　　row：不记录sql命令的上下文信息，仅保存哪条记录被修改。
　　mixed：前两种的综合。
　　#############################################################
　　3、查看binlog日志：
　　#mysqlbinlog    binlog日志文件名
　　#############################################################
　　4、binlog日志文件记录sql命令的方式：
　　时间点                 //根据修改的时间记录
　　pos点                    //根据数据偏移量记录
　　#############################################################
　　5、恢复数据
　　格式：
　　mysqlbinlog 选项  binlog日志文件名  | mysql -uroot -p密码
　　选项：
　　--》根据时间点恢复
　　--start-datatime="yyyy-mm-dd  hh:mm:ss"  --stop-datatime="yyyy-mm-dd  hh:mm:ss"
　　--》根据偏移量恢复 --start-position=数字   --stop-position=数字
　　##############################################################
　　6、生成日志与删除日志
　　--》生成日志
　　mysql> flush logs;
　　#mysql -uroot -p123456 -e "flush logs"
　　#systemctl restart mysqld
　　--》删除日志
　　mysql>reset master;                 //删除所有
　　mysql>purge master logsto "日志文件名"           //删除指定日志之前的所有日志
　　mysql>purge master logs to "mysql11-bin.000003" //1和2 已经删除，但是3以后的保留
　　##################################################################
　　7、举例
　　启用binlog日志
　　重启服务
　　写入数据
　　#mysql -uroot -p123456
　　show master status；             //查看当前使用的binlog日志
　　create database db1；
　　create table db1.t1（id int）；
　　insert into table db1 values（100）；
　　insert into table db1 values（200）；
　　select * from db1.t1;               //查看数据是否插入成功
　　删除数据
　　drop databases db1；
　　查看日志
　　mysqlbinlog /var/lib/mysql/mysql11-bin.000001
　　//通过查询日志，create database db1；      建表的偏移量at 296
　　//insert into table db1 values（200）；  插入这条记录的偏移量 at 1097
　　恢复数据（通过偏移量）
　　mysql>show databases;               //确认库已经删除
　　mysql>quit
　　#mysqlbinlog  --start-position=296  --stop-position=1097 \
　　/var/lib/mysql/mysql11-bin.000001 | mysql -uroot -p123456
　　查看结果
　　mysql>show databases;
　　mysql>select * from db1.t1;
　　###################################################################
　　二、使用第三方软件percona提供的命令innobackupex做增量备份
　　物理备份缺点：
　　备份时间长，冗余备份，浪费村数空间
　　跨平台性差
　　mysqldump备份缺点：
　　效率低，备份和还原时间慢
　　备份过程中，数据插入与更新会被挂起
　　percona优点：
　　备份过程不锁表，适合生产环境
　　支持InnoDB/XtraDB
　　以Perl脚本封装Xtrabackup还支持MyISAM
　　##########################################################
　　1、安装软件包（网上下载）
　　libev-4.15-1.el6.rf.x86_64.rpm
　　percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
　　###########################################################
　　2、innobackupex基本选项
　　--host                  //主机名
　　--user                  //用户名
　　--password                  //登陆密码
　　--databases                 //数据库名
　　--no-timestamp              //备份文件不用日期命名
　　--apply-log                 //回滚日志（准备还原）
　　--redo-only                 //日志回滚合并（最后一次的回滚合并不需要添加此选项）
　　--incremental   目录名         //增量备份
　　--incremental-basedir=目录名       //增量备份时指定上一次备份数据村存储的文件名
　　--export                    //导出表信息
　　import                  //导入表空间
　　##############################################################
　　3、完全备份
　　#innobackupex --user root --password 123456 --databases="系统库 存储库" 备份目录名 --no-timestamp
　　#rm -rf /var/lib/mysql
　　#mkdir /var/lib/mysql
　　完全恢复数据
　　#innobackup --user root --password 123456 --databases="系统库 存储库"  --apply-log  备份目录名
　　//回滚日志
　　#innobackupex  --user  root   --password  123456  --databases="系统库列表   存储数据库"  --copy-back  备份目录名
　　//恢复数据
　　#ls /var/lib/mysql/
　　#chown  -R mysql:mysql  /var/lib/mysql
　　#systemctl   stop mysqld
　　#systemctl   start mysqld
　　#mysql  -uroot  -pabc123
　　mysql>show  databases;
　　###############################################################
　　4、增量备份
　　增量备份之前，通常先作一次完全备份
　　增量备份第一次：
　　#innobackupex  --user  root   --password  123456  --databases="系统库列表   存储数据库"  --incremental   目录名1   --incremental-basedir=完全备份名  --no-timestamp
　　增量备份第二次：
　　#innobackupex  --user  root   --password  123456  --databases="系统库列表   存储数据库"  --incremental   目录名2   --incremental-basedir=目录名1  --no-timestamp
　　//相当于你的下一次备份，参照的时上一次的备份文件。
　　5、删除信息
　　rm -rf /var/lib/mysql
　　mkdir  /var/lib/mysql
　　6、增量恢复日志文件
　　#innobackupex --user root --password 123456 --databases=“系统库 存储库” --apply-log --redo-only 完全备份目录名
　　#innobackupex --user root --password 123456 --databases=“系统库 存储库” --apply-log --redo-only 完全备份目录名
　　--incremental-dir=目录名1
　　#innobackupex --user root --password 123456 --databases=“系统库 存储库” --apply-log --redo-only 完全备份目录名
　　--incremental-dir=目录名2
　　#cat 备份目录/xtraback_checkpositions           //文件可以查看日志序列号
　　7、恢复数据
　　#innobackupex  --user  root   --password  abc123  --databases="系统库列表   存储数据库"  --copy-back  完全备份目录名
　　#chown   -R  mysql:mysql  /var/lib/mysql
　　8、重启服务查看
　　#systemctl  stop mysqld
　　#systemctl  start mysqld
　　###############################################################################
　　9、增量举例
　　/allbak                         //自定义完全备份的目录名
　　/new1                               //自定义第一次增量备份目录名
　　/new2                               //自定义第二次增量备份目录名
　　//无需自己创建
　　完全备份
　　#innobackupex  --user  root   --password  123456  --databases="系统库列表   gamedb"  /allbak
　　--no-timestamp
　　增量第一次：
　　mysql>create table gamedb.t2(id int);
　　mysql>insert into gamedb.t2 values(200);
　　#innobackupex  --user  root   --password  123456  --databases="系统库列表   gamedb"  --incremental   /new1   --incremental-basedir=/allbak  --no-timestamp
　　增量第二次：
　　mysql>create table gamedb.t3(id int);
　　mysql>insert into gamedb.t3 values(300);
　　#innobackupex --user root --password 123456 --databases="系统库 gamedb"  --incremental /new2
　　--incremental-basedir=/new1 --no-timestamp
　　删除数据
　　rm -rf /var/lib/mysql
　　mkdir /var/lib/mysql
　　恢复日志文件
　　#innobackupex  --user  root   --password  abc123  --databases="系统库列表   gamedb"  --apply-log  --redo-only
　　/allbak
　　//回滚日志
　　#cat /allbak/xtraback_checkpositions            //文件可以查看日志序列号
　　#innobackupex  --user  root   --password  abc123  --databases="系统库列表   gamedb"  --apply-log  --redo-only
　　/allbak --incremental-dir=/new1
　　//增量回滚/new1
　　#cat /new1/xtraback_checkpositions          //文件可以查看日志序列号
　　#cat /allbak/xtraback_checkpositions            //文件可以查看日志序列号
　　//对比可以发现，回滚成功，它们的序列号是首尾相接的
　　#innobackupex  --user  root   --password  abc123  --databases="系统库列表   gamedb" --apply-log --redo-only
　　/allbak --increment-dir=/new2
　　//增量回滚/new2
　　#cat /new2/xtraback_checkpositions          //文件可以查看日志序列号
　　#cat /allbak/xtraback_checkpositions            //文件可以查看日志序列号
　　//对比可以发现，回滚成功，它们的序列号是首尾相接的
　　恢复数据
　　#innobackupex  --user  root   --password  abc123  --databases="系统库列表   gamedb"  --copy-back  /allbak
　　查看
　　#systemctl  stop mysqld
　　#systemctl  start mysqld
　　##############################################################################################
　　10、恢复单个表：
　　10.1 备份库
　　#innobackup  --user  root  --password 123456  --databases="gamedb"   /gamedbbak
　　10.2 删除单个表
　　mysql> drop table gamedb.t1;
　　10.3 按照原表结构创建表
　　mysql> create table gamedb.t1(id int);
　　10.4 导出表信息
　　#innobackup  --user  root  --password abc123  --databases=“gamedb.t1” --apply-log --export /gamedbbak
　　10.5 删除表空间
　　mysql>alter table gamedb.t1 discart tablespace;
　　10.6 将导出的表信息，拷贝到对应的数据库目录下
　　#cd  /gamedbbak/gamedb
　　#cp  t1.{exp,ibd,cfg}  /var/lib/mysql/gamedb/
　　#ls /var/lib/mysql/gamedb/t1.
　　#chown  mysql:myql   /var/lib/mysql/gamedb/t1.
　　10.6 导入表空间
　　alter  table  t1  import  tablespace;
　　10.7 验证
　　select  * from  gamedb.t1;
　　###########################################################################################################
　　4、