MySQL---备份与还原

一、冷备与热备

热备 online：在mysql开启时候的备份

mysqldump是mysql用于转存储数据库的实用程序。它主要产生一个SQL脚本。

[root@nginx-kafka01 ~]# mysqldump  --all -databases -uroot -p'Sanchuang123' >all_db.sql  备份所有的数据库
在数据库执行删掉huyuxuan数据库
[root@nginx-kafka01 ~]# mysql --all -databases -uroot -p'Sanchuang123' <all_db.sql    还原

冷备 offline ： mysql是关闭时的备份
-------------------cp ， tar ， rsync ，scp 等都是冷备

二、备份方式

1、完全备份

完全备份即对系统进行完全备份，包括系统和数据。完全备份即对系统进行完全备份，包括系统和数据。

恢复的时候恢复最后一天的备份

2、差异备份

差异数据库备份，是记录自上次完全数据库备份之后对数据库的更改的数据库备份。降低增量备份存储空间不足的可能性，数据恢复时，只需要两份数据，一份是上一次完全备份，另一份是最新的差异备份，所需时间短，并节省空间，灾难恢复方便。

恢复的时候恢复第一次全备份的和最后一天备份的即可

3、增量备份

是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加或者被修改的文件。

备份的好处是每次备份需要备份的数据较少，耗时较短，占用的空间较小；坏处是数据恢复比较麻烦，每天的数据都要恢复才能完全恢复

总结：

备份方案：
	1.全天全备
	2.星期1全备，后面每天都是增量备份
	3.星期1全备，后面每天都是差异备份

三、备份和还原操作

1、产生全新的二进制文件

lr@huyuxuan 15:31  mysql>show master status;  查看当前二进制文件
+--------------------------+----------+--------------+------------------+-------------------+
| File                     | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------------+----------+--------------+------------------+-------------------+
| nginx-kafka01-bin.000002 |      904 |              |                  |                   |
+--------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

lr@huyuxuan 17:15  mysql>reset master;  删除二进制文件
Query OK, 0 rows affected (0.15 sec)

lr@huyuxuan 17:17  mysql>show master status;  重新生成的如下
+--------------------------+----------+--------------+------------------+-------------------+
| File                     | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------------+----------+--------------+------------------+-------------------+
| nginx-kafka01-bin.000001 |      154 |              |                  |                   |
+--------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

lr@huyuxuan 17:17  mysql>

2、给数据库做全备

[root@nginx-kafka01 mysql]# mkdir ./backup
[root@nginx-kafka01 mysql]# cd backup/
[root@nginx-kafka01 backup]# mysqldump -ulr -p'lr123456' --databases sanchuang >sanchuang.sql      给sanchuang库做全备
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@nginx-kafka01 backup]# ls
sanchuang.sql
[root@nginx-kafka01 backup]#

备份完后查看数据库，位置未发生变化。因为拿数据并未造成数据变化

lr@(none) 20:09  mysql>show master status;
+--------------------------+----------+--------------+------------------+-------------------+
| File                     | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------------+----------+--------------+------------------+-------------------+
| nginx-kafka01-bin.000001 |      154 |              |                  |                   |
+--------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
lr@(none) 20:15  mysql>

3.让数据发生变化，进行insert 和删除操作等

lr@sanchuang 20:19  mysql>insert into student(id,name,sex) values(12,'lili','m');
Query OK, 1 row affected (0.21 sec)

lr@sanchuang 20:19  mysql>
lr@sanchuang 20:19  mysql>insert into student(id,name,sex) values(13,'liu','w');
Query OK, 1 row affected (0.00 sec)

lr@sanchuang 20:20  mysql>
lr@sanchuang 20:20  mysql>show master status;  查看数据库 发生变化
+--------------------------+----------+--------------+------------------+-------------------+
| File                     | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------------+----------+--------------+------------------+-------------------+
| nginx-kafka01-bin.000001 |      715 |              |产生了二进制日志                  |                   |
+--------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

4.模拟出现故障，删除数据库

lr@sanchuang 20:20  mysql>drop database sanchuang;
Query OK, 6 rows affected (0.57 sec)

lr@(none) 20:22  mysql>

5、开始取恢复数据
第1步恢复全备

[root@nginx-kafka01 backup]# mysql -ulr -p'lr123456' <sanchuang.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@nginx-kafka01 backup]# 
lr@sanchuang 21:14  mysql>select * from student;  恢复全备之后，11，12 号还没恢复
+------+------+------+
| id   | name | sex  |
+------+------+------+
|    1 | lr   | m    |
|    2 | lili | m    |
|    3 | liu  | w    |
+------+------+------+

第2步：

方法一、根据位置号来恢复

查看二进制日志找到删除数据库之前的position 位置号

[root@nginx-kafka01 mysql]# mysqlbinlog -v nginx-kafka01-bin.000001 |grep -C 50 "drop database sanchuang"    -C看前后50行，只需要看删库前面的

执行如下语句 即恢复全备份之后到删库前之间的数据

[root@nginx-kafka01 mysql]# mysqlbinlog --start-position=154 --stop-position=780 ./nginx-kafka01-bin.000001 |mysql -ulr -p'lr123456'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@nginx-kafka01 mysql]# 

方法二、根据时间来恢复的方法

查看时间的 220813 21:12:47 是开始的时间，结束为删库之前那个

[root@nginx-kafka01 mysql]# mysqlbinlog -v nginx-kafka01-bin.000001 |more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220813 21:12:47 server id 1  end_log_pos 123 CRC32 0xddab2b94 	Start: binlog v 4, server v 5.7.38-
log created 220813 21:12:47 at startup

找出开始和结束的时间执行如下格式

 mysqlbinlog  --start-datetime="2020-08-13 11:50:07" --stop-datetime="2020-08-13 11:50:24" /data/mysql/zabbix-4-centos7-bin.000002 |mysql -uyangst -p'yang123#'

6、查看数据是否恢复

lr@sanchuang 11:14  mysql>select * from student;
+------+------+------+
| id   | name | sex  |
+------+------+------+
|    1 | lr   | m    |
|    2 | lili | m    |
|    3 | liu  | w    |
|   12 | lili | m    |
|   13 | liu  | w    |
+------+------+------+
5 rows in set (0.00 sec)

验证成功！