二、Mysql bin-log日志恢复数据
需要开启binlog日志功能
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bK2bSynC-1584494478336)(https://i.loli.net/2020/03/17/Up7nQwj9ulg4IWC.png)]
加上配置之后重启服务,使其生效
[root@mysql-1 mysql]# systemctl restart mysqld
mysql> flush logs; #刷新binlog日志,使下面的语句存放到下一个binlog日志中
mysql> create table testdb.t2(id int);
根据位置恢复
找到要恢复的sql语句的起始位置、结束位置
[root@mysql-1 mysql]# mysqlbinlog mysql-bin.000002 #查看binlog日志内容
mysql>drop table testdb.t2(id int); //删除表格。注意:增删改都会记录到binlog日志中。
#恢复语句,按照上面文件中找到的位置,起始是219 结束是321,文件名要看清楚。
[root@mysql-1 mysql]# mysqlbinlog --start-position 219 --stop-position 321 mysql-bin.000002 |mysql -uroot -p123
扩展:
mysql> mysql> show binlog events in 'mysql-bin.000002'; 在数据库中找到binlog起止位置和结束位置。
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.29-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 219 | Query | 1 | 320 | use `testdb`; create table t2(id int) |
| mysql-bin.000002 | 320 | Rotate | 1 | 367 | mysql-bin.000003;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
作业:
根据binlog日志的时间点恢复
找到要恢复sql语句的起始时间、结束时间
mysqlbinlog --start-datetime='2019-8-21 23:55:54' --stop-datetime='2019-8-21 23:56:541' mylog.000001 | mysql -u root -p1
作业步骤提示
待定
三、物理备份
percona是一家老牌的mysql技术咨询公司。它不仅提供mysql的技术支持、培训、咨询,还发布了mysql的分支版本–percona Server。并围绕percona Server还发布了一系列的mysql工具。
物理备份: 直接复制数据库文件,适用于大型数据库环境,不受存储引擎的限制,但不能恢复到不同的MySQL版本。
1.完全备份-----完整备份:
每次都将所有数据(不管自第一次备份以来有没有修改过),进行一次完整的复制,备份后会清除文件的存档属性,方便日后增量备份或者差异备份进行版本比较。
特点:占用空间大,备份速度慢,但恢复时一次恢复到位,恢复速度快。
2.增量备份: 每次备份上一次备份到现在产生的新数据
在第一次完整备份之后,第二次开始每次都将添加了存档属性的文件进行备份,并且在备份之后再把这些存档属性清除。为什么要清除存档属性呢?这就是为了下一次备份的时候判断是否有文件变化,因为用户在每次备份以后修改这些被清除存档属性的文件,存档属性就会自动加上,相当于用户告诉系统,这些文件有变化,你下一次就备份这些文件,其他没有存档属性的就不需要备份,这就是增量备份的工作机制。
特点:因每次仅备份自上一次备份(注意是上一次,不是第一次)以来有变化的文件,所 以备份体积小,备份速度快,但是恢复的时候,需要按备份时间顺序,逐个备份版本进行恢 复,恢复时间长。
-
差异备份:只备份跟完整备份不一样的
在第一次完整备份之后,第二次开始每次都将所有文件与第一次完整备份的文件做比较,把自第一次完整备份以来所有修改过的文件进行备份,且以后每次备份都是和第一次完整备份进行比较(注意是第一次,不是上一次),备份自第一次完整备份以来所有的修改过的文件。因此,差异备份在备份完毕之后不需要清除文件的存档属性,因为这些文件和下一次备份没有什么关系,它仅仅和第一次完整备份的数据进行比较(第一次完整备份之后是清除存档属性的)。
(相当于第一次机器人把地板打扫干净了,你踩过,就会有脚印,机器人就把脚印记录下 来,但不打扫,下次你又有踩脏的,机器人就把你这几次所有踩脏的地方都记录下来,始终 不打扫,每次都这样。机器人每次记录的内容就相当于差异备份的内容)
特点:占用空间比增量备份大,比完整备份小,恢复时仅需要恢复第一个完整版本和最后一次的差异版本,恢复速度介于完整备份和增量备份之间。
简单的讲,完整备份就是不管三七二十一,每次都把指定的备份目录完整的复制一遍,不管目录下的文件有没有变化;增量备份就是每次将之前(第一次、第二次、直到前一次)做过备份之后有变化的文件进行备份;差异备份就是每次都将第一次完整备份以来有变化的文件进行备份。
热备份
数据库启动同时给客户端提供服务的情况下
冷备份
数据库要关掉或者不能给客户端提供服务
热代表当服务还在运行中,进行操作
冷代表操作时需要关闭服务。
王者荣耀,有的时候需要停服更新,有的时候在线就能更新。
官网:www.percona.com
1.安装xtrabackup
安装xtrabackup
# wget http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
# rpm -ivh percona-release-0.1-4.noarch.rpm
[root@mysql-server yum.repos.d]# vim percona-release.repo
修改如下内容:将原来的1改为0
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yDDuPAFs-1584494478339)(https://i.loli.net/2020/03/17/agptvZBNdPD26Ai.png)]
# 下载工具
[root@mysql-server yum.repos.d]# yum -y install percona-xtrabackup-24.x86_64
如果使用yum公网下载时出现如下错误
解决方法:
mv /etc/yum.repo.d/mysql-community.repo /etc/yum.repo.d/mysql-community.repo.bak
然后在重新下载
yum -y install percona-xtrabackup-24.x86_64
2.完全备份流程:
创建备份目录:
[root@mysql-server ~]# mkdir /xtrabackup/full -p
备份:
# 语法 innobackupex --user=mysql用户 --password=mysql密码 备份目录
[root@mysql-server ~]# innobackupex --user=root --password='123' /xtrabackup/full
可以查看一下:
[root@mysql-server ~]# cd /xtrabackup/full/
[root@mysql-server full]# ls
2019-08-20_11-47-49
=======================================================
完全备份恢复流程
1. 停止数据库
2. 清理环境
3. 重演回滚--> 恢复数据
4. 修改权限
5. 启动数据库
1.关闭数据库:
[root@mysql-server ~]# systemctl stop mysqld
[root@mysql-server ~]# rm -rf /var/lib/mysql/*
[root@mysql-server ~]# rm -rf /var/log/mysqld.log #可选操作
[root@mysql-server ~]# rm -rf /var/log/mysql-slow/slow.log #可选操作
2.恢复之前的验证恢复:
# 语法 innobackupex --apply-log 备份的目录
[root@mysql-server ~]# innobackupex --apply-log /xtrabackup/full/2019-08-20_11-47-49
3.确认数据库目录:
恢复之前需要确认配置文件内有数据库目录指定,不然xtrabackup不知道恢复到哪里
# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
4.恢复数据:
[root@mysql-server ~]# innobackupex --copy-back /xtrabackup/full/2019-08-20_11-47-49
5.修改权限:
[root@mysql-server ~]# chown mysql.mysql /var/lib/mysql -R
启动数据库:
[root@mysql-server ~]# systemctl start mysqld
3.增量备份流程
原理:每次备份上一次备份到现在产生的新数据
1.在数据库上面创建一个测试的库
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QXafP5Ki-1584494478341)(https://i.loli.net/2020/03/17/nmQN14YLBXjq8cW.png)]
1.完整备份:周一
[root@mysql-server ~]# rm -rf /xtrabackup/*
[root@mysql-server ~]# innobackupex --user=root --password='123' /xtrabackup
[root@mysql-server ~]# cd /xtrabackup/
[root@mysql-server xtrabackup]# ls
2019-08-20_14-51-35
[root@mysql-server xtrabackup]# cd 2019-08-20_14-51-35/
[root@mysql-server 2019-08-20_14-51-35]# ls
backup-my.cnf ib_buffer_pool mysql sys testdb xtrabackup_info
company ibdata1 performance_schema test xtrabackup_checkpoints xtrabackup_logfile
2、增量备份:周二 —— 周三
在数据库中插入周二的数据:
mysql> insert into testdb.t1 values(2); #模拟周二
#语法 innobackupex --user=用户 --password='密码' --incremental --incremental-basedir=(基于哪一次备份进行增量备份)
[root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2019-08-20_14-51-35/
[root@mysql-server ~]# cd /xtrabackup/
[root@mysql-server xtrabackup]# ls
2019-08-20_14-51-35 2019-08-20_15-04-29 ---相当于周二的增量备份
在数据库中插入周三的数据:
mysql> insert into testdb.t1 values(3); #模拟周三
[root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2019-08-20_15-04-29/ #基于前一天的备份为目录
[root@mysql-server ~]# cd /xtrabackup/
[root@mysql-server xtrabackup]# ls
2019-08-20_14-51-35 2019-08-20_15-04-29 2019-08-20_15-10-56 ---相当于周三的增量备份
查看一下备份目录:
[root@mysql-server ~]# ls /xtrabackup/
2019-08-20_14-51-35 2019-08-20_15-04-29 2019-08-20_15-10-56
全备周一 增量周二 增量周三
增量备份恢复流程
1. 停止数据库
2. 清理环境
3. 依次重演回滚redo log--> 恢复数据
4. 修改权限
5. 启动数据库
6. binlog恢复
[root@mysql-server ~]# systemctl stop mysqld
[root@mysql-server ~]# rm -rf /var/lib/mysql/*
依次重演回滚redo log:
# 语法 innobackupex --apply-log(重演) --redo-only(仅仅只是验证) 指定完整备份
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_14-51-35
周二 --- 周三
# 语法 innobackupex --apply-log --redo-only 指定完整备份目录 --incremental-dir=周二增量备份目录
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_14-51-35 --incremental-dir=/xtrabackup/2019-08-20_15-04-29
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_14-51-35 --incremental-dir=/xtrabackup/2019-08-20_15-10-56/
恢复数据:
[root@mysql-server ~]# innobackupex --copy-back /xtrabackup/2019-08-20_14-51-35/
修改权限
[root@mysql-server ~]# chown -R mysql.mysql /var/lib/mysql
[root@mysql-server ~]# systemctl start mysqld
登陆上去看一下:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Qlq29Jnp-1584494478342)(https://i.loli.net/2020/03/17/n35aSGUchMuNe2Q.png)]
4.差异备份流程
清理备份的环境:
[root@mysql-server ~]# rm -rf /xtrabackup/*
登陆数据库,准备环境
mysql> create database testdb;
mysql> delete from testdb.t1;
mysql> insert into testdb.t1 values(1); #插入数据1,模拟周一
mysql> select * from testdb.t1;
+------+
| id |
+------+
| 1 |
+------+
mysql> \q
查看时间:
[root@mysql-server ~]# date
Tue Aug 20 15:39:59 CST 2019
1、完整备份:周一
[root@mysql-server ~]# innobackupex --user=root --password='123' /xtrabackup
2、差异备份:周二 —— 周三
语法: # innobackupex --user=root --password=888 --incremental /xtrabackup --incremental-basedir=/xtrabackup/完全备份目录(周一)
3.修改时间:
[root@mysql-server ~]# date 08211543
Wed Aug 21 15:43:00 CST 2019
4.在登陆mysql:
mysql> insert into testdb.t1 values(2); #插入数据2,模拟周二
差异备份周二的
[root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2019-08-20_15-42-02/ #备份目录基于周一的备份
5.再次登陆mysql
mysql> insert into testdb.t1 values(3); #插入数据,模拟周三
6.在次修改时间
[root@mysql-server ~]# date 08221550
Thu Aug 22 15:50:00 CST 2019
7.再次差异备份
[root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2019-08-20_15-42-02/ #还是基于周一的备份
8.延伸到周四
mysql> insert into testdb.t1 values(4);
9.修改时间
[root@mysql-server ~]# date 08231553
Fri Aug 23 15:53:00 CST 2019
10.差异备份周四
[root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2019-08-20_15-42-02/ #还是基于周一的备份
11.查看一下备份目录
[root@mysql-server ~]# ls /xtrabackup/
2019-08-20_15-42-02 2019-08-21_15-46-53 2019-08-22_15-51-15 2019-08-23_15-53-28
周一 周二 周三 周四
差异备份恢复流程
1. 停止数据库
2. 清理环境
3. 重演回滚redo log(周一,某次差异)--> 恢复数据
4. 修改权限
5. 启动数据库
6. binlog恢复
停止数据库
[root@mysql-server ~]# systemctl stop mysqld
[root@mysql-server ~]# rm -rf /var/lib/mysql/*
1.恢复全量的redo log
语法: # innobackupex --apply-log --redo-only /xtrabackup/完全备份目录(周一)
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_15-42-02/
2.恢复差异的redo log
语法:# innobackupex --apply-log --redo-only /xtrabackup/完全备份目录(周一)--incremental-dir=/xtrabacku/某个差异备份
这里我们恢复周三的差异备份
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_15-42-02/ --incremental-dir=/xtrabackup/2019-08-22_15-51-15/ #我们恢复周三的差异备份
3.恢复数据
语法:# innobackupex --copy-back /xtrabackup/完全备份目录(周一)
[root@mysql-server ~]# innobackupex --copy-back /xtrabackup/2019-08-20_15-42-02/
修改权限:
[root@mysql-server ~]# chown -R mysql.mysql /var/lib/mysql
[root@mysql-server ~]# systemctl start mysqld
登陆mysql查看一下:
只有123.因为我们恢复的是周三的差异备份。