目录
前言
增量备份可以在完全备份的基础上,减少备份文件的大小,从而加快备份和恢复的速度。
一、MySQL数据库增量恢复类型
1、一般恢复
将所有备份的二进制日志内容全部恢复
2、基于位置恢复
数据库在某一时间点可能既有错误的操作也有正确的操作,可以基于精准的位置跳过错误的操作,发生错误节点之前的一个节点,上一次正确操作的位置点停止。
3、基于时间点恢复
跳过某个发生错误的时间点实现数据恢复,在错误时间点停止,在下一个正确时间点开始。
二、MySQL 增量备份
1、增备部署
1.开启二进制日志功能
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED #可选,指定二进制日志(binlog)的记录格式为MIXED(混合输入)
server-id = 1 #可加可不加该命令
#二进制日志(binlog)有3种不同的记录格式: STATEMENT (基于SQL语句)、ROW(基于行)、MIXED(混合模式),默认格式是STATEMENT
① STATEMENT(基于SQL语句):
每一条涉及到被修改的sql 都会记录在binlog中
缺点:日志量过大,如sleep()函数,last_insert_id()>,以及user-defined fuctions(udf)、主从复制等架构记录日志时会出现问题
总结:增删改查通过sql语句来实现记录,如果用高并发可能会出错,可能时间差异或者延迟,可能不是我们想想的恢复可能你先删除或者在修改,可能会倒过来。准确率底
② ROW(基于行)
只记录变动的记录,不记录sql的上下文环境
缺点:如果遇到update......set....where true 那么binlog的数据量会越来越大
总结:update、delete以多行数据起作用,来用行记录下来,
只记录变动的记录,不记录sql的上下文环境,
比如sql语句记录一行,但是ROW就可能记录10行,但是准确性高,高并发的时候由于操作量,性能变低 比较大所以记录都记下来,
③ MIXED 推荐使用
一般的语句使用statement,函数使用ROW方式存储。
systemctl restart mysqld
查看二进制日志文件的内容
cp /usr/local/mysql/data/mysql-bin.000002 /opt/
① mysqlbinlog --no-defaults /opt/mysql-bin.000002
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
#--base64-output=decode-rows:使用64位编码机制去解码(decode)并按行读取(rows)
#-v: 显示详细内容
#--no-defaults : 默认字符集(不加会报UTF-8的错误)
PS: 可以将解码后的文件导出为txt格式,方便查阅
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002 > /opt/mysql-bin.000002
二进制日志中需要关注的部分
1、at :开始的位置点
2、end_log_pos:结束的位置
3、时间戳: 210712 11:50:30
4、SQL语句
2.进行完全备份(增量备份时基于完全备份的,所以我们直接完全备份数据库)
[root@mysql data]# mysqldump -uroot -p school info > /opt/kgc_info1_$(date +%F).sql
[root@mysql data]# mysqldump -uroot -p123123 school > /opt/school_all_$(date +%F).sql
3.可每天进行增量备份操作,生成新的二进制日志文件(例如:mysql-bin.000002)
mysqladmin -u root -p flush-logs
4.插入新数据,以模拟数据的增加或变更
PS:在第一次完全备份之后刷新二进制文件,在第二个二进制文件中记载着"增量备份的数据"
mysql> create database kk11;
Query OK, 1 row affected (0.00 sec)
mysql> use kk11;
Database changed
mysql> create table test1 (id int(4),name varchar(4));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test1 values(1,'one');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1 values(2,'two');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test1;
+------+------+
| id | name |
+------+------+
| 1 | one |
| 2 | two |
+------+------+
2 rows in set (0.00 sec)
5.再次生成新的二进制日志文件(例如:mysql-bin.000003)
mysqladmin -u root -p flush-logs
#之前的步骤4的数据库操作会保存到mysql-bin.000002文件中,之后我们测试删除kk11库的操作会保存在mysql-bin.000003文件中 (以免当我们基于mysql-bin.000002日志进行恢复时,依然会删除库)
2、MySQL增量恢复
2-1、一般恢复
1.一般恢复
(1)、模拟丢失更改的数据的恢复步骤(直接使用恢复即可)
① 备份kk11库中test1表
mysqldump -uroot -p123123 kk11 test1 > /opt/kk11_test11.sql
② 删除kk11库中test1表
drop table kk11.test1;
③ 恢复test1表
mysql -uroot -p kk11 < info-2020-08-31.sql
#查看日志文件
[root@mysql data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002
(2)、模拟丢失所有数据的恢复步骤
① 模拟丢失所有数据
[root@mysql data]# mysql -uroot -p123123
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kk11 |
| mysql |
| performance_schema |
| school |
| sys |
| test |
+--------------------+
7 rows in set (0.00 sec)
mysql> drop database kk11;
Query OK, 1 row affected (0.00 sec)
mysql> exit
② 基于mysql-bin.000002恢复
mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p
2-2、断点恢复
2.断点恢复
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
例:
# at 302
#201122 16:41:16
插入了"user3"的用户数据
# at 623
#201122 16:41:24
插入了"user4"的用户数据
(1)、基于位置恢复
① 插入三条数据
mysql> use kk11;
mysql> select * from test1;
+------+------+
| id | name |
+------+------+
| 1 | one |
| 2 | two |
+------+------+
2 rows in set (0.00 sec)
mysql> insert into test1 values(3,'true');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1 values(4,'f');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test1 values(5,'t');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test1;
+------+------+
| id | name |
+------+------+
| 1 | one |
| 2 | two |
| 3 | true |
| 4 | f |
| 5 | t |
+------+------+
5 rows in set (0.00 sec)
#需求:以上id =4的数据操作失误,需要跳过
② 确认位置点,刷新二进制日志并删除test1表
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000003
960 停止
1066 开始
#刷新日志
mysqladmin -uroot -p123123 flush-logs
mysql> use kk11;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_kk11 |
+----------------+
| test1 |
+----------------+
1 row in set (0.00 sec)
mysql> drop table kk11.test1;
Query OK, 0 rows affected (0.00 sec)
③ 基于位置点恢复
#仅恢复到操作 ID 为“623"之前的数据,即不恢复"user4"的数据
mysqlbinlog --no-defaults --stop-position='1793' /opt/mysql-bin.000002 | mysql -uroot -p
960 停止
1066 开始
#仅恢复"user4"的数据,跳过"user3"的数据恢复
mysqlbinlog --no-defaults --start-position='623' /opt/mysql-bin.000002 | mysql -uroot -p
mysqlbinlog --no-defaults --start-position='400' --stop-position='623' /opt/mysql-bin.000002 | mysql -uroot -p #恢复从位置为400开始到位置为623为止
(2)、基于时间点恢复
mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime='年-月-日小时:分钟:秒' 二进制日志 | mysql -u 用户名 -p 密码
#仅恢复到16:41:24 之前的数据,即不恢复"user4"的数据
mysqlbinlog --no-defaults --stop-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 | mysql -uroot -p
#仅恢复"user4"的数据,跳过"user3"的数据恢复
mysqlbinlog --no-defaults --start-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 | mysql -uroot -p
如果恢复某条SQL语之前的所有数据,就stop在这个语句的位置节点或者时间点
如果恢复某条SQL语句以及之后的所有数据,就从这个语句的位置节点或者时间点start
总结
增量备份
1、开启二进制日志,设置二进制日志格式MIXED
2、进行一次完全备份,可每周备份一次,通过crontab -e
3、使用mysqladm -uroot -p flush-logs刷新分割出二进制日志,由于刷新之前的数据操作都记录在老的二进制日志里,所以需要把老的二进制日志复制到一个安全的目录中保管
4、可以通过mysqlbinlog --no-defaults --base64-output=decode-rows -v二进制日志文件名查看日志内容
5、可以通过mysqlbinlog --no-defaults二进制日志文件名l mysql -uroot -p恢复丢失的数据