Mysql(五)-数据恢复

########################################################################################
增量备份与增量恢复

一、启用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、