MySQL实现备份案例(2)[通俗易懂]

MySQL实现备份案例(2)[通俗易懂]案例1:MySQL8.0实现数据库冷备份和还原 10.0.0.10 — MySQL8.0 #停止数据库 [root@CentOS8 my.cnf.d]# systemctl stop mysqld.

MySQL实现备份案例(2)

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

10.0.0.10 — MySQL8.0

#停止数据库
[root@CentOS8 my.cnf.d]# systemctl stop mysqld.service

#备份数据
[root@CentOS8 ~]# scp -pr  /var/lib/mysql 10.0.0.13:/mysql-bak

#备份配置文件
[root@CentOS8 ~]# scp -rp /etc/my.cnf  /etc/my.cnf.d/

10.0.0.13 — MySQL8.0

#安装MySQL8.0
[root@CentOS8 config]# yum insatll mysql-server -y

#替换拷贝过来的数据
[root@CentOS8 mysql]# cp -a mysql /var/lib/mysql

#替换配置文件
[root@CentOS8 config]# cp my.cnf /etc/
[root@CentOS8 config]# cp -a  my.cnf.d  /etc/

#更改文件权限
[root@centos8 ~]#chown -R mysql.mysql /var/lib/mysql/

#启动数据库测试
[root@CentOS8 config]# systemctl start mysqld.service

注意:scp -p:保留文件的访问和修改时间,文件的所有者和所有组会发生变化(建议使用rsync -a进行远程拷贝)

案例2:使用mysqldump进行完全备份并模拟数据库损坏:

1.使用musqldump的格式三备份数据库所有数据
[root@CentOS8 ~]# mysqldump -A >hello.sql

#查看备份的数据库
[root@CentOS8 ~]# grep -i "^create database" hello.sql;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET utf8 */;

2.模拟数据库损坏,清空数据库的所有文件,会自动初始化MySQL为重装的状态
[root@CentOS8 ~]# rm -rf /var/lib/mysql/*   #yum安装默认的数据存放路径为/var/lib/mysql/

3.启动数据库
[root@CentOS8 ~]# systemctl start mysqld.service 

4.导入数据
[root@CentOS8 ~]# mysql < hello.sql

5.测试是否恢复正常
mysql> show databases ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

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

利用--master-data选项在备份的文本文件中从二进制日志中标记当前位置。

前提:需要开启二进制日志

步骤:
1.使用mysqldump进行完全备份
[root@CentOS8 ~]# mysqldump -A --master-data=2  > hellodb.sql

2.更改数据库中的数据信息
mysql> insert into teachers values(null,"xiao ming",20,"M");
Query OK, 1 row affected (0.00 sec)

mysql> insert into teachers values(null,"xiao hong",18,"F");
Query OK, 1 row affected (0.00 sec)

mysql> insert into teachers values(null,"xiao he",19,"M");
Query OK, 1 row affected (0.00 sec)

3.损坏数据库
[root@CentOS8 ~]# rm -rf /var/lib/mysql/*

4.执行还原操作
[root@CentOS8 ~]# ll /data/logbin/*
-rw-r----- 1 mysql mysql 1024 Sep 19 18:26 /data/logbin/mysql-bin.000001
-rw-r----- 1 mysql mysql  154 Sep 19 18:26 /data/logbin/mysql-bin.000002
-rw-r----- 1 mysql mysql   60 Sep 19 18:26 /data/logbin/mysql-bin.index
[root@CentOS8 ~]# mysqlbinlog --start-position=154 /data/logbin/mysql-bin.000001 > binlog.sql
[root@CentOS8 ~]# mysqlbinlog  /data/logbin/mysql-bin.000002 >> binlog.sql

5.关闭二进制日志 -- 数据恢复期间不记录二进制日志
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_log_bin;
+---------------+
| @@sql_log_bin |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)


6.恢复数据库
root@CentOS8 ~]# mysql < hellodb.sql 
[root@CentOS8 ~]# mysql < binlog.sql

7.检查

案例4:恢复误删除的表

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


完全备份(3)点--------->删除了表students(10:00)----->发现故障(10:10)

思路:
1.将数据库恢复到3点时候的状态
2.通过二进制日志将三点到十一点的数据进行还原(需要删除二进制日志中删除students这条语句)

实现:

前提:mysql需要开启二进制日志

1.凌晨三点进行完全备份:
[root@CentOS8 ~]# mysqldump -uroot -p -A -F --single-transaction --master-data=2 > /data/back/all_back_`date +"%F:%T"`.sql

2.模拟三点到十点之间有数据更新
mysql> insert students (name,age,gender) values("rose",20,"f");
Query OK, 1 row affected (0.01 sec)

mysql> insert students (name,age,gender) values("jack",22,"M");
Query OK, 1 row affected (0.00 sec)

3.模拟误删除了students
mysql> drop table students;
Query OK, 0 rows affected (0.01 sec)

4.其他表仍然更新数据
mysql> insert teachers (name,age,gender)values("xiao wang ",30,"M");
Query OK, 1 row affected (0.00 sec)

mysql> insert teachers (name,age,gender)values("xiao li",28,"M");
Query OK, 1 row affected (0.00 sec)


5.发现表删除,进行还原(此时需要停止数据库的访问)
(1)#从完全备份中,找到二进制位置
[root@CentOS8 back]# cat all_back_2022-09-20:00:32:05.sql  | grep -i "^-- change master"
-- CHANGE MASTER TO MASTER_LOG_FILE="mysql-bin.000002", MASTER_LOG_POS=156;

(2)查看对应的二进制文件并生成sql语句
[root@CentOS8 back]# mysqlbinlog /data/logbin/mysql-bin.000002 > binlog.sql
#mysqlbinlog默认将信息输出到屏幕

(3)删除binlog.sql中关于删除表的语句
[root@CentOS8 back]# grep -i "^DROP TABLE" binlog.sql 
DROP TABLE `students` /* generated by server */

[root@CentOS8 back]# sed -i.bak "/^DROP TABLE/d" binlog.sql 
[root@CentOS8 back]# grep -i "^DROP TABLE" binlog.sql

(4)执行数据恢复脚本(需要进入到mysql里面使用source命令执行)
[root@centos8 ~]#mysql -uroot -p

#不将数据恢复的信息写入到二进制日志里面
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> source  /data/back/all_back_2022-09-20:00:32:05.sql;

#执行这个脚本的时候有一个报错信息,不用管他,数据依然能正常恢复
mysql> source  /data/back/binlog.sql;

mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

原文地址:https://www.cnblogs.com/heyongshen/archive/2022/09/20/16709687.html

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
转载请注明出处: https://daima100.com/4748.html

(0)
上一篇 2023-06-06
下一篇 2023-06-06

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注