• 售前

  • 售后

热门帖子
入门百科

MySQL数据库运维之数据恢复的方法

[复制链接]
伊索谗言 显示全部楼层 发表于 2021-10-26 13:11:07 |阅读模式 打印 上一主题 下一主题
之前三篇文章分别介绍了MySQL数据库常见的备份方法,其中包罗逻辑备份和物理备份,本篇将总结一下MySQL数据库的数据规复相干内容。这些数据规复方案在之前备份内容介绍时,此处总结一下规复方案,并联合数据库的二进制日志做下数据规复的示范!

一、规复方案

1、数据量不是特别大,可以将mysqldump命令备份的数据使用mysql客户端命令大概source命令完成数据的规复;
2、使用Xtrabackup完成数据库的物理备份规复,期间需要重启数据库服务;
3、使用LVM快照卷完成数据库物理备份规复,期间需要重启数据库服务;
二、使用mysqlbinlog进行时间点规复

1、介绍

mysqlbinlog是一个从二进制日志中读取语句的工具,在mysql安装完成之后自带的。
2、二进制日志规复原理

当使用mysqldump对数据库进行备份时,生成的备份文件中包含了数据库DML利用时的时间点以及备份时的二进制日志位置信息,如果单库,可以从某个时间点开始,进行时间点规复;如果是主从架构,可以根据备份时的--master-data=2和--single-transaction,完成根据时间点大概位置点的规复。
3、二进制日志规复示例

(1)单库规复示例

创建数据库,并插入测试数据
  1. mysql> SHOW CREATE DATABASE test_db;
  2. mysql> CREATE TABLE `student` (
  3. `id` int(11) NOT NULL AUTO_INCREMENT,
  4. `name` varchar(20) NOT NULL,
  5. `age` tinyint(4) DEFAULT NULL,
  6. PRIMARY KEY (`id`)
  7. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
  8. mysql> INSERT INTO student (name,age) VALUES('Jack',23),('Tomcat',24),('XiaoHong',22),('ZhangFei',29);
复制代码
使用mysqldump进行全量备份,备份时滚动日志,同时记着二进制日志文件名称和日志的位置点
  1. [root@WB-BLOG ~]# mysqldump -uroot -proot -h127.0.0.1 -P3306 --databases test_db --single-transaction --triggers --routines --flush-logs --events > /tmp/test_db.sql
  2. [root@WB-BLOG ~]# mysql -e "show binary logs" > bin_pos_`date +%F`.out
复制代码
此时检察二进制日志文件名称和日志点位置如下
  1. mysql> SHOW BINARY LOGS;
  2. +------------------+-----------+
  3. | Log_name     | File_size |
  4. +------------------+-----------+
  5. | mysql-bin.000001 |   1497 |
  6. | mysql-bin.000002 |    397 |
  7. +------------------+-----------+
  8. 2 rows in set (0.00 sec)
复制代码
使用了一段时间,不鉴戒误利用,执行了如下的语句,将数据库中的数据全部修改了
  1. mysql> UPDATE STUDENT SET name = 'admin';
复制代码
过了一段时间,可能是几分钟,也可能是几个小时,有人反映网站登录有标题了,检察发现很多多少数据被误修改,而这段时间内,还一直有写入利用,如又新增了如下的记载
  1. mysql> INSERT INTO student(name,age) VALUES('Hbase',23),('BlackHole',30);
复制代码
此时需要规复数据,起首为了防止数据继承写入,可以先锁表,暂停写入业务,通知用户体系维护,然后执行如下利用:
  1. #登录数据库,锁表,此时表只能读,不能写
  2. mysql> USE test_db;
  3. mysql> LOCK TABLE student READ;
  4. #然后重新(注意是重新打开)打开一个session窗口,否则会话处出之后,锁就会释放。然后压缩备份现有数据和二进制日志文件
  5. [root@WB-BLOG mysql_logs]# tar zcvf mysql_data.tar.gz /mysql_data/*
  6. [root@WB-BLOG mysql_logs]# tar zcvf mysql_bin.tar.gz /mysql_logs/*
  7. #导入最近备份的一次全备数据
  8. [root@WB-BLOG ~]# mysql -uroot -proot -h127.0.0.1 -P3306 < /tmp/test_db.sql
  9. #查看全备时的二进制日志文件和日志点
  10. [root@WB-BLOG ~]# cat bin_pos_2018-06-24.out
  11.   Log_name    File_size
  12.   mysql-bin.000001   1497
  13.   mysql-bin.000002    397
  14. #将861这个点之后的二进制日志文件转换为一个sql文件
  15. [root@WB-BLOG bin]# ./mysqlbinlog /mysql_logs/mysql-bin.000002 --start-position=397 > /tmp/tmp.sql
  16. #使用vim编辑器编辑这个sql文件,找到其中的未加条件的UPDATE语句,然后将其删掉,然后将删掉UPDATE语句之后的sql脚本内容导入到数据库中
  17. [root@WB-BLOG bin]# vim /tmp/tmp.sql
  18.   use `test_db`/*!*/;
  19.   SET TIMESTAMP=1522088753/*!*/;
  20.   update student set name = 'admin' #删掉这一句
  21. [root@WB-BLOG bin]# mysql -uroot -proot -h127.0.0.1 -P3306 < /tmp/tmp.sql
  22. #登录数据库查询数据是否恢复,可以查看被误修改的数据是否还原,然后对表执行解锁,再次全备数据
  23. mysql> UNLOCK TABLES;
复制代码
(2)主从架构数据规复示例

情况

主库:192.168.199.10(node01)
从库:192.168.199.11(node02)
起首制止从库的SQL线程,然后在从库上全备数据,并输入"SHOW SLAVE STATUS"信息到备份文件中,"SHOW SLAVE STATUS"的输出信息中记载了当前应用到了主库的哪个位置点的信息
  1. #登录从库,然后关闭SQL线程
  2. mysql> STOP SLAVE SQL_THREAD;
  3. Query OK, 0 rows affected (0.01 sec)
  4. #然后记录从库中当前应用的主库的二进制日志文件信息
  5. [root@node02 mysql_data]# mysql -e "SHOW SLAVE STATUS \G" > slave_`date +%F`.info
  6. [root@node02 mysql_data]# mysqldump -uroot -proot -h127.0.0.1 -P3306 --databases test_db --routines --triggers --single-transaction > /tmp/mysql_test_db_`date +%F`.sql
复制代码
在从库上备份完成之后,重新启动从库的SQL线程
  1. mysql> START SLAVE SQL_THREAD;
  2. Query OK, 0 rows affected (0.01 sec)
复制代码
启动SQL线程之后,备份这段时间内在主库上的DML利用会重新同步到从库上。如果在主库上发生了一个误利用,没加条件更新了student表中的全部数据,导致了表中全部数据被修改,此时由于同步利用,从库也被修改了
  1. #登录主库,修改数据库的对外用户,使其暂不提供服务,然后滚动日志
  2. mysql> UPDATE mysql.user SET Host = '127.0.0.1' WHERE User='tomcat';
  3. Query OK, 1 rows affected (0.00 sec)
  4. #刷新权限表
  5. mysql> FLUSH PRIVILEGES;
  6. Query OK, 0 rows affected (0.00 sec)
  7. #滚动日志
  8. mysql> FLUSH LOGS;
  9. Query OK, 0 rows affected (0.01 sec)
  10. #将从库备份的数据及备份时刻的从库slave信息传到主库上
  11. [root@node02 mysql_data]# scp /tmp/mysql_test_db_2018-06-24.sql 192.168.199.10:/root/
  12. [root@node02 mysql_data]# scp slave_2018-06-24.info node01:/root/
复制代码
备份主库的数据目次和二进制日志文件目次
  1. [root@node01 mysql_logs]# tar zcvf mysql_master_data.tar.gz /mysql_data/*
  2. [root@node01 mysql_logs]# tar zcvf mysql_logs.tar.gz /mysql_logs/*  
复制代码
导入从库最近一次备份的数据
  1. [root@node01 mysql_logs]# mysql -uroot -proot -h127.0.0.1 -P3306 < /root/mysql_test_db_2018-03-26.sql
  2. #注意:上述的操作不能锁主库的表,否则全备数据无法导入。
复制代码
检察备份时候的从库中应用到的主库二进制日志文件名称及位置点
  1. [root@node01 mysql_logs]# cat /root/slave_2018-03-26.info
  2.   Master_Log_File: master-bin.000002 #备份时所应用的主库二进制日志文件名称
  3.   Read_Master_Log_Pos: 395  #备份时所应用的主库二进制日志文件的位置
复制代码
从该日志文件及日志点开始,将395日志点之后的日志文件转换为sql脚本,如果有多个二进制日志文件可以同时转换为sql脚本,如下所示
  1. [root@node01 mysql_logs]# mysqlbinlog /mysql_logs/master-bin.000002 --start-position=395 > /tmp/tmp.sql
  2. #将master-bin.000003,master-bin.000004,master-bin.000005合并到/tmp.sql文件中
  3. [root@node01 mysql_logs]# mysqlbinlog /mysql_logs/master-bin.00000{3,4,5} --start-position=395 > /tmp/tmp.sql
复制代码
找到误利用的update语句,然后删除该语句,并将增量的sql脚本导入数据库
  1. [root@node01 mysql_logs]# vim /tmp/tmp.sql
  2.   use `test_db`/*!*/;
  3.   update student set name = 'admin' #删掉这一句
  4. [root@node01 mysql_logs]# mysql -uroot -proot -h127.0.0.1 -P3306 < /tmp/tmp.sql
复制代码
登录数据库,检察数据是否正常,被误修改的数据是否已经规复,如果规复,则在主库上全备数据,然后传到从库,完成从库规复
  1. [root@node01 mysql_data]# mysqldump -uroot -proot -h127.0.0.1 -P3306 --databases test_db --routines --triggers --single-transaction --master-date=1 > /tmp/master_test_db_`date +%F`.sql
  2. [root@node01 mysql_data]# scp /tmp/master_test_db_2018-06-24.sql node01:/root/
  3. #如果从库设置了只读,需要先去掉只读限制
  4. mysql> SET GLOBAL read_only = OFF;
  5. Query OK, 0 rows affected (0.00 sec)
  6. #将数据导入从库
  7. [root@node02 mysql_logs]# mysql -uroot -proot -h127.0.0.1 -P3306 < /root/master_test_db_2018-06-24.sql
  8. #开启从库的只读
  9. mysql> SET GLOBAL read_only = ON;
  10. Query OK, 0 rows affected (0.00 sec)
复制代码
由于在主库上备份时添加了--master-date=1参数,所以从库导入之后,不需要重新执行change master利用。
登录从库,检察SHOW SLAVE STATUS信息是否正常,如果正常,登录主库,重新修改授权表,然后对外提供服务
  1. mysql> UPDATE mysql.user set Host = '192.168.0.%' WHERE User = 'tomcat';
  2. mysql> FLUSH PRIVILEGES;
  3. Query OK, 0 rows affected (0.00 sec)
复制代码
执行完成之后,主从数据规复完毕。
至此,数据规复介绍完毕,上述介绍了使用全备加二进制日志实现单实例数据库和主从数据库的数据规复过程,如有标题,欢迎评论指出。也希望各人多多支持草根技术分享。

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作