一、冷备与热备
热备 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)
验证成功!