mysql 全量，增量备份与恢复

mysql 全量，增量备份与恢复

一，简要了解备份图示

详情（https://www.jianshu.com/p/25cca24a54cf）

二，完全备份

MYSQl数据库的备份可以采用多种方式

（1）直接打包数据库文件夹。

tar -czf  data.tar.gz /usr/local/mysql/data

(2) 使用专用备份工具mysqlldump

MySQL自带的备份工具，相当方便对MySQL进行备份，通过该命令工具可以将指定的库、表或全部的库导出为SQL脚本，在需要恢复时可进行数据恢复

使用mysqlldump命令进行备份

语法

mysqldump -u 用户名 -p [密码] [选项] [数据库名]  > /备份路径/备份文件名

实例

mysqldump命令对单个库进行完全备份

[root@localhost ~]# mysqldump -uroot -p test > /opt/test.spl
Enter password: 
[root@localhost ~]# cd /opt/
[root@localhost opt]# ls
test.spl
# 进入mysql删除该库
cd /opt/
drop database test;
create database test;
source test.spl;
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| PAN            |
| SHOOCL         |
| commodity      |
| grade          |
| grade1         |
| result         |
| student        |
| student1       |
| subject        |
+----------------+

mysqldump命令对多个库进行完全备份
mysqldump -u 用户名 -p [密码] [选项]  --databases  库名 1  [库名2] …  >  /备份路径/备份文件名
列子
mysqldump -u root –p  --databases  autth mysql > /backup/databases-auth-mysql.sql

所有库备份
mysqldump -u 用户名 -p [密码] [选项]  --all-databases  >  /备份路径/备份文件名
例子
mysqldump -u root -p --opt --all-databases > /backup/all-data.sql

在实际生产环境中，存在对某个特定表的维护操作，此时mysqldump同样发挥重大作用
使用mysqldump备份表的操作
mysqldump -u 用户名 -p [密码] [选项] 数据库名  表名 > /备份路径/备份文件名
列子
mysqldump -u root -p mysql user > /backup/mysql-user.sql

三，差异备份

开启mysql服务的二进制日志功能

默认是关闭的
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

vim /etc/my.cnf  #添加
server-id = 1                   #服务器的标志符 
log-bin = mysql-bin             #开启二进制日志功能 

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)
ok
对数据进行完全备份
mysql> select * from PAN;
+----+--------+
| id | numder |
+----+--------+
|  1 | pc     |
|  2 | niu    |
+----+--------+
 mysqldump -uroot -p test > /opt/PAN.spl
添加数据

mysql> insert into PAN values(3,'boy'),(4,'zhang');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from PAN;
+----+--------+
| id | numder |
+----+--------+
|  1 | pc     |
|  2 | niu    |
|  3 | boy    |
|  4 | zhang  |
+----+--------+
修改数据
mysql> update PAN set numder = 'kiki' where id = 4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from PAN;
+----+--------+
| id | numder |
+----+--------+
|  1 | pc     |
|  2 | niu    |
|  3 | boy    |
|  4 | kiki   |
+----+--------+
4 rows in set (0.00 sec)
模拟删除数据库
mysql> drop database test;
Query OK, 9 rows affected (2.19 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

生成二进制文件
mysqladmin -uroot -p flush-logs
[root@localhost opt]# cd /usr/local/mysql/data/
[root@localhost data]# ll
总用量 176168
-rw-rw----. 1 mysql mysql       56 4月   7 14:28 auto.cnf
-rw-rw----. 1 mysql mysql 79691776 4月  13 14:17 ibdata1
-rw-rw----. 1 mysql mysql 50331648 4月  13 14:17 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 4月   7 14:25 ib_logfile1
-rw-rw----. 1 mysql mysql     8960 4月  13 12:00 localhost.localdomain.err
-rw-rw----. 1 mysql mysql        5 4月  13 12:00 localhost.localdomain.pid
drwx------. 2 mysql mysql     4096 4月   7 14:25 mysql
-rw-rw----. 1 mysql mysql      937 4月  13 14:20 mysql_bin.000001
-rw-rw----. 1 mysql mysql      120 4月  13 14:20 mysql_bin.000002
-rw-rw----. 1 mysql mysql       38 4月  13 14:20 mysql_bin.index
drwx------. 2 mysql mysql     4096 4月   7 14:25 performance_schema

恢复完全备份
mysql> source test-PAN.sql;

mysql> select * from PAN;
+----+--------+
| id | numder |
+----+--------+
|  1 | pc     |
|  2 | niu    |
+----+--------+
只恢复到修改前

//只恢复到了没有修改数据前的数据表，查询正在使用的二进制文件
mysql> mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000002 |     8760 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

//查看日志，找到误删记录在哪
show binlog events in 'mysql_bin.000002';
//恢复到没有误删之前的数据库

mysqlbinlog --stop-position=505 mysql_bin.000001 |mysql -uroot -p
Enter password: 
mysql> use test
select * from PAN;
+----+--------+
| id | numder |
+----+--------+
|  1 | pc     |
|  2 | niu    |
|  3 | boy    |
|  4 | kiki   |
+----+--------+

四，增量备份

增量备份有三种恢复方式

增量备份也要在vim /etc/my.cnf  #添加
server-id = 1                   #服务器的标志符 
log-bin = mysql-bin             #开启二进制日志功能 

一般恢复

create database wj;      # 创建库

mysql> create table zhengdn(id int not null , name varchar(32) not null);   # 创建表

mysql> insert into zhengdn (id,name) values (1,'wangjiao'),(2,'niuqi');  #插入数据
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> 
mysql> select * from zhengdn;
+----+----------+
| id | name     |
+----+----------+
|  1 | wangjiao |
|  2 | niuqi    |
+----+----------+
2 rows in set (0.01 sec)

.重新生成一个日志文件，这样刚才的操作步骤都会保存在第一个二进制文件中
mysqladmin -u root -p flush-logs#重新生成二进制文件

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| wj                 |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database wj;
Query OK, 1 row affected (0.05 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> 

在使用mysqlbinlog --no-defaults mysql-bin.000003 | mysql -u root -p #恢复数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| wj                 |
+--------------------+
mysql> 
mysql> select * from zhengdn;
+----+----------+
| id | name     |
+----+----------+
|  1 | wangjiao |
|  2 | niuqi    |
+----+----------+
2 rows in set (0.01 sec)

基于时间恢复

mysql> select * from zhengdn;
+----+----------+
| id | name     |
+----+----------+
|  1 | wangjiao |
|  2 | niuqi    |
+----+----------+
2 rows in set (0.00 sec)

mysql> delete from zhengdn where name='wangjiao'; #误删除
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> insert into zhengdn (id,name) values (1,'wangj'); #继续插入
Query OK, 1 row affected (0.23 sec)

mysql> select * from zhengdn;
+----+-------+
| id | name  |
+----+-------+
|  2 | niuqi |
|  1 | wangj |
+----+-------+
2 rows in set (0.00 sec)

重新生成一个二进制日志
mysqladmin -u root -p flush-logs#创建新日志

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002#64位解码查看日志文件，防乱码
在其中需要找到两个时间点

复制时间

[root@localhost data]# mysqlbinlog --no-defaults --start-datetime='2021-4-13 16:00:12' /usr/local/
mysql/data/mysql-bin.000002 | mysql -u root -p

Enter password: 

[root@localhost data]# mysqlbinlog --no-defaults --stop-datetime='2021-4-13 15:59:30' /usr/local/m
ysql/data/mysql-bin.000002 | mysql -u root -p

Enter password: 

这样被误删除的数据就恢复好了

基于位置恢复

[root@localhost data]# mysqladmin -u root -p flush-logs
Enter password:

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002

mysqlbinlog --no-defaults --stop-position='649' /usr/local/mysql/data/mysql
-bin.000002 | mysql -u root -p

mysqlbinlog --no-defaults --stert-position='862' /usr/local/mysql/data/mysql
-bin.000002 | mysql -u root -p