MySQL实现备份案例（2）

案例1：MySQL8.0实现数据库冷备份和还原

10.0.0.10 -- MySQL8.0

1. #停止数据库
2. [root@CentOS8 my.cnf.d]# systemctl stop mysqld.service
4. #备份数据
5. [root@CentOS8 ~]# scp -pr  /var/lib/mysql 10.0.0.13:/mysql-bak
7. #备份配置文件
8. [root@CentOS8 ~]# scp -rp /etc/my.cnf  /etc/my.cnf.d/

复制代码

10.0.0.13 -- MySQL8.0

1. #安装MySQL8.0
2. [root@CentOS8 config]# yum insatll mysql-server -y
4. #替换拷贝过来的数据
5. [root@CentOS8 mysql]# cp -a mysql /var/lib/mysql
7. #替换配置文件
8. [root@CentOS8 config]# cp my.cnf /etc/
9. [root@CentOS8 config]# cp -a  my.cnf.d  /etc/
11. #更改文件权限
12. [root@centos8 ~]#chown -R mysql.mysql /var/lib/mysql/
14. #启动数据库测试
15. [root@CentOS8 config]# systemctl start mysqld.service

复制代码

注意：scp -p：保留文件的访问和修改时间，文件的所有者和所有组会发生变化（建议使用rsync -a进行远程拷贝）
案例2：使用mysqldump进行完全备份并模拟数据库损坏：

1. 1.使用musqldump的格式三备份数据库所有数据
2. [root@CentOS8 ~]# mysqldump -A >hello.sql
4. #查看备份的数据库
5. [root@CentOS8 ~]# grep -i '^create database' hello.sql;
6. CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;
7. CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET utf8 */;
9. 2.模拟数据库损坏，清空数据库的所有文件，会自动初始化MySQL为重装的状态
10. [root@CentOS8 ~]# rm -rf /var/lib/mysql/*   #yum安装默认的数据存放路径为/var/lib/mysql/
12. 3.启动数据库
13. [root@CentOS8 ~]# systemctl start mysqld.service
15. 4.导入数据
16. [root@CentOS8 ~]# mysql < hello.sql
18. 5.测试是否恢复正常
19. mysql> show databases ;
20. +--------------------+
21. | Database           |
22. +--------------------+
23. | information_schema |
24. | hellodb            |
25. | mysql              |
26. | performance_schema |
27. | sys                |
28. +--------------------+
29. 5 rows in set (0.00 sec)

复制代码

案例3: 利用二进制日志，还原数据库最新状态

1. 利用--master-data选项在备份的文本文件中从二进制日志中标记当前位置。
3. 前提：需要开启二进制日志
5. 步骤：
6. 1.使用mysqldump进行完全备份
7. [root@CentOS8 ~]# mysqldump -A --master-data=2  > hellodb.sql
9. 2.更改数据库中的数据信息
10. mysql> insert into teachers values(null,'xiao ming',20,'M');
11. Query OK, 1 row affected (0.00 sec)
13. mysql> insert into teachers values(null,'xiao hong',18,'F');
14. Query OK, 1 row affected (0.00 sec)
16. mysql> insert into teachers values(null,'xiao he',19,'M');
17. Query OK, 1 row affected (0.00 sec)
19. 3.损坏数据库
20. [root@CentOS8 ~]# rm -rf /var/lib/mysql/*
22. 4.执行还原操作
23. [root@CentOS8 ~]# ll /data/logbin/*
24. -rw-r----- 1 mysql mysql 1024 Sep 19 18:26 /data/logbin/mysql-bin.000001
25. -rw-r----- 1 mysql mysql  154 Sep 19 18:26 /data/logbin/mysql-bin.000002
26. -rw-r----- 1 mysql mysql   60 Sep 19 18:26 /data/logbin/mysql-bin.index
27. [root@CentOS8 ~]# mysqlbinlog --start-position=154 /data/logbin/mysql-bin.000001 > binlog.sql
28. [root@CentOS8 ~]# mysqlbinlog  /data/logbin/mysql-bin.000002 >> binlog.sql
30. 5.关闭二进制日志 -- 数据恢复期间不记录二进制日志
31. mysql> set sql_log_bin=0;
32. Query OK, 0 rows affected (0.00 sec)
34. mysql> select @@sql_log_bin;
35. +---------------+
36. | @@sql_log_bin |
37. +---------------+
38. |             0 |
39. +---------------+
40. 1 row in set (0.00 sec)
43. 6.恢复数据库
44. root@CentOS8 ~]# mysql < hellodb.sql
45. [root@CentOS8 ~]# mysql < binlog.sql
47. 7.检查

复制代码

案例4：恢复误删除的表

案例说明：每天凌晨3点定时做完全备份，早上10：00误删除了表students，10：10才发现故障，现需要将数
据库还原到10：10的状态，且恢复被删除的students表

1. 完全备份（3）点--------->删除了表students(10：00)----->发现故障（10：10）
3. 思路：
4. 1.将数据库恢复到3点时候的状态
5. 2.通过二进制日志将三点到十一点的数据进行还原（需要删除二进制日志中删除students这条语句）

复制代码

实现：

1. 前提：mysql需要开启二进制日志
3. 1.凌晨三点进行完全备份：
4. [root@CentOS8 ~]# mysqldump -uroot -p -A -F --single-transaction --master-data=2 > /data/back/all_back_`date +"%F:%T"`.sql
6. 2.模拟三点到十点之间有数据更新
7. mysql> insert students (name,age,gender) values('rose',20,'f');
8. Query OK, 1 row affected (0.01 sec)
10. mysql> insert students (name,age,gender) values('jack',22,'M');
11. Query OK, 1 row affected (0.00 sec)
13. 3.模拟误删除了students
14. mysql> drop table students;
15. Query OK, 0 rows affected (0.01 sec)
17. 4.其他表仍然更新数据
18. mysql> insert teachers (name,age,gender)values('xiao wang ',30,'M');
19. Query OK, 1 row affected (0.00 sec)
21. mysql> insert teachers (name,age,gender)values('xiao li',28,'M');
22. Query OK, 1 row affected (0.00 sec)
25. 5.发现表删除，进行还原（此时需要停止数据库的访问）
26. （1）#从完全备份中，找到二进制位置
27. [root@CentOS8 back]# cat all_back_2022-09-20\:00\:32\:05.sql  | grep -i "^-- change master"
28. -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=156;
30. （2）查看对应的二进制文件并生成sql语句
31. [root@CentOS8 back]# mysqlbinlog /data/logbin/mysql-bin.000002 > binlog.sql
32. #mysqlbinlog默认将信息输出到屏幕
34. （3）删除binlog.sql中关于删除表的语句
35. [root@CentOS8 back]# grep -i '^DROP TABLE' binlog.sql
36. DROP TABLE `students` /* generated by server */
38. [root@CentOS8 back]# sed -i.bak '/^DROP TABLE/d' binlog.sql
39. [root@CentOS8 back]# grep -i '^DROP TABLE' binlog.sql
41. （4）执行数据恢复脚本（需要进入到mysql里面使用source命令执行）
42. [root@centos8 ~]#mysql -uroot -p
44. #不将数据恢复的信息写入到二进制日志里面
45. mysql> set sql_log_bin=0;
46. Query OK, 0 rows affected (0.00 sec)
48. mysql> source  /data/back/all_back_2022-09-20:00:32:05.sql；
50. #执行这个脚本的时候有一个报错信息，不用管他，数据依然能正常恢复
51. mysql> source  /data/back/binlog.sql;
53. mysql> set sql_log_bin=1;
54. Query OK, 0 rows affected (0.00 sec)

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！