MySQL完全备份与恢复

InnoDB存储引擎的数据库在磁盘上存储成三个文件：

db.opt(表属性文件)、表名.frm（表结构文件）、表名.ibd（表数据文件）。

MySQLdump 备份与恢复

1）、完全备份一个或多个完整的库（包括其中所有的表）

[root@localhost bak]# mysqldump -uroot -pabc123 --databases school test > /opt/bak/mysql_school_test.sql 备份mysql与school和test的两个库
[root@localhost bak]# ls

2）、完全备份MySQL服务器中所有的库

[root@localhost bak]# mysqldump -uroot -pabc123 --all-databases > /opt/bak/mysql_all.sql;
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost bak]# ls

3）、完全备份指定库中的部分表

[root@localhost bak]# mysqldump -uroot -pabc123 test test1 test2 > /opt/bak/test_1_2.sql;
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost bak]# ls

[root@localhost bak]# mysqldump -uroot -pabc123 -d test test1 test2 > /opt/bak/test_1_2_jiegou.sql;
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost bak]# ls

流程解析

先备份数据库中的数据打包，模拟数据丢失，损坏，利用备份打包恢复数据

物理冷备份

先关闭数据库，之后打包备份

恢复数据库

恢复数据库，采用将备份数据mv成线上库文件夹的方式

配置流程

首先创建数据库数据

mysql> create database awk; 创建数据库awk
Query OK, 1 row affected (0.00 sec)
mysql> use awk; 使用数据库
Database changed
mysql> create table a(id int(20));创建数据库并设置数据
Query OK, 0 rows affected (0.01 sec)
mysql> desc a;描述表a的结构

[root@localhost mysql]# systemctl stop mysqld 关闭数据库
[root@localhost mysql]# tar zcvf /opt/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/  源文件来源路径备份打包所有数据库，当天日期
[root@localhost mysql]# cd /opt/
[root@localhost opt]# ls -lh 查看文件

模拟数据库丢失，损坏

[root@localhost opt]# systemctl start mysqld 开启数据库
[root@localhost opt]# mysql -uroot -pabc123 登录数据库
mysql> show databases; 查看数据库信息

mysql> drop database awk; 删除数据库信息
Query OK, 1 row affected (0.00 sec)
mysql> show databases; 查看数据库

恢复数据

[root@localhost opt]# systemctl stop mysqld 关闭数据库
[root@localhost opt]# cd /opt/
[root@localhost opt]# tar zxvf mysql_all-2021-07-13.tar.gz 解压缩
[root@localhost opt]# ls -lh 查看文件

当备份时加 --databases ,表示针对于test库

[root@localhost opt]# mysqldump -uroot -pabc123 --databases test > /opt/mysql_test.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysql> drop database test;
Query OK, 2 rows affected (0.01 sec)
mysql> show databases;

[root@localhost opt]# mysql -uroot -pabc123 < mysql_test.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> show databases;

mysql数据库增量恢复

1）、一般恢复

将所有备份的二进制日志内容全部恢复

2）、基于位置恢复

数据库在某一时间点可能既有错误的操作也有正确的操作
可以基于精准的位置跳过错误的操作
发生错误节点之前的一个节点，上一次正确操作的位置点停止

3）、基于时间点恢复

跳过某个发生错误的时间点实现数据恢复
在错误时间点停止，在下一个正确时间点开始

增备实验

1）、开启二进制日志功能

[root@localhost ~]# vim /etc/my.cnf
server-id = 1  可加可不加该命令
log-bin=mysql-bin 
binlog_format = MIXED
[root@localhost ~]# systemctl restart mysqld

查看二进制日志文件的内容

[root@localhost ~]# cd /usr/local/mysql/data/
[root@localhost data]# ls

[root@localhost data]# cp mysql-bin.000001 /opt/
[root@localhost data]# cd /opt/
[root@localhost opt]# ls

[root@localhost opt]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000001 

二进制日志中需要关注的部分

1、at ：开始的位置点
2、end_log_pos：结束的位置
3、时间戳： 210712 11:50:30
4、SQL语句

进行完全备份(增量备份时基于完全备份的，所以我们直接完全备份数据库)

[root@localhost data]# mysqladmin -uroot -pabc123 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost data]# ls

插入新数据，以模拟数据的增加或变更

mysql> create database ky11;
Query OK, 1 row affected (0.00 sec)
mysql> use ky11;
Database changed
mysql> create table txt1 (id int(5),name varchar(3));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into txt1 values(1,'one');
Query OK, 1 row affected (0.01 sec)
mysql> insert into txt1 values(2,'two');
Query OK, 1 row affected (0.00 sec)
mysql> select * from txt1;

[root@localhost opt]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002 > mysql_02.sql
[root@localhost opt]# ls

[root@localhost opt]# mysqladmin -uroot -pabc123 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost opt]# ls /usr/local/mysql/data/

模拟丢失所有数据的恢复步骤

模拟丢失所有数据

mysql> drop database ky11;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;

基于mysql-bin.000002恢复

[root@localhost opt]# mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -uroot -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost opt]# mysql -uroot -pabc123
mysql> show databases;