一, 恢复方案
1, 数据量不是特别大, 可以将 mysqldump 命令备份的数据使用 MySQL 客户端命令或者 source 命令完成数据的恢复;
2, 使用 Xtrabackup 完成数据库的物理备份恢复, 期间需要重启数据库服务;
3, 使用 LVM 快照卷完成数据库物理备份恢复, 期间需要重启数据库服务;
二, 使用 mysqlbinlog 进行时间点恢复
1, 介绍
mysqlbinlog 是一个从二进制日志中读取语句的工具, 在 MySQL 安装完成之后自带的.
2, 二进制日志恢复原理
当使用 mysqldump 对数据库进行备份时, 生成的备份文件中包含了数据库 DML 操作时的时间点以及备份时的二进制日志位置信息, 如果单库, 可以从某个时间点开始, 进行时间点恢复; 如果是主从架构, 可以根据备份时的 --master-data=2 和 --single-transaction, 完成根据时间点或者位置点的恢复.
3, 二进制日志恢复示例
(1)单库恢复示例
创建数据库, 并插入测试数据
- MySQL> SHOW CREATE DATABASE test_db;
- MySQL> CREATE TABLE `student` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `name` varchar(20) NOT NULL,
- `age` tinyint(4) DEFAULT NULL,
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
- MySQL> INSERT INTO student (name,age) VALUES('Jack',23),('Tomcat',24),('XiaoHong',22),('ZhangFei',29);
使用 mysqldump 进行全量备份, 备份时滚动日志, 同时记住二进制日志文件名称和日志的位置点
- [[email protected] ~]# mysqldump -uroot -proot -h127.0.0.1 -P3306 --databases test_db --single-transaction --triggers --routines --flush-logs --events> /tmp/test_db.sql
- [[email protected]-BLOG ~]# MySQL -e "show binary logs"> bin_pos_`date +%F`.out
此时查看二进制日志文件名称和日志点位置如下
- MySQL> SHOW BINARY LOGS;
- +------------------+-----------+
- | Log_name | File_size |
- +------------------+-----------+
- | MySQL-bin.000001 | 1497 |
- | MySQL-bin.000002 | 397 |
- +------------------+-----------+
- 2 rows in set (0.00 sec)
使用了一段时间, 不小心误操作, 执行了如下的语句, 将数据库中的数据全部修改了
MySQL> UPDATE STUDENT SET name = 'admin';
过了一段时间, 可能是几分钟, 也可能是几个小时, 有人反映网站登录有问题了, 查看发现好多数据被误修改, 而这段时间内, 还一直有写入操作, 如又新增了如下的记录
MySQL> INSERT INTO student(name,age) VALUES('Hbase',23),('BlackHole',30);
此时需要恢复数据, 首先为了防止数据继续写入, 可以先锁表, 暂停写入业务, 通知用户系统维护, 然后执行如下操作:
- # 登录数据库, 锁表, 此时表只能读, 不能写
- MySQL> USE test_db;
- MySQL> LOCK TABLE student READ;
- # 然后重新 (注意是重新打开) 打开一个 session 窗口, 否则会话处出之后, 锁就会释放. 然后压缩备份现有数据和二进制日志文件
- [[email protected]-BLOG mysql_logs]# tar -zcvf mysql_data.tar.gz /mysql_data/*
- [[email protected]-BLOG mysql_logs]# tar -zcvf mysql_bin.tar.gz /mysql_logs/*
- # 导入最近备份的一次全备数据
- [[email protected]-BLOG ~]# MySQL -uroot -proot -h127.0.0.1 -P3306 </tmp/test_db.sql
- # 查看全备时的二进制日志文件和日志点
- [[email protected]-BLOG ~]# cat bin_pos_2018-06-24.out
- Log_name File_size
- MySQL-bin.000001 1497
- MySQL-bin.000002 397
- # 将 861 这个点之后的二进制日志文件转换为一个 sql 文件
- [[email protected]-BLOG bin]# ./mysqlbinlog /mysql_logs/MySQL-bin.000002 --start-position=397> /tmp/tmp.sql
- # 使用 VIM 编辑器编辑这个 sql 文件, 找到其中的未加条件的 UPDATE 语句, 然后将其删掉, 然后将删掉 UPDATE 语句之后的 sql 脚本内容导入到数据库中
- [[email protected]-BLOG bin]# VIM /tmp/tmp.sql
- use `test_db`/*!*/;
- SET TIMESTAMP=1522088753/*!*/;
- update student set name = 'admin' #删掉这一句
- [[email protected]-BLOG bin]# MySQL -uroot -proot -h127.0.0.1 -P3306 </tmp/tmp.sql
- # 登录数据库查询数据是否恢复, 可以查看被误修改的数据是否还原, 然后对表执行解锁, 再次全备数据
- MySQL> UNLOCK TABLES;
(2)主从架构数据恢复示例
环境
主库: 192.168.199.10(node01)
从库: 192.168.199.11(node02)
首先停止从库的 SQL 线程, 然后在从库上全备数据, 并输入 "SHOW SLAVE STATUS" 信息到备份文件中,"SHOW SLAVE STATUS" 的输出信息中记录了当前应用到了主库的哪个位置点的信息
- # 登录从库, 然后关闭 SQL 线程
- MySQL> STOP SLAVE SQL_THREAD;
- # 然后记录从库中当前应用的主库的二进制日志文件信息
- [[email protected] mysql_data]# MySQL -e "SHOW SLAVE STATUS \G"> slave_`date +%F`.info
- [[email protected] 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 线程
MySQL> START SLAVE SQL_THREAD;
启动 SQL 线程之后, 备份这段时间内在主库上的 DML 操作会重新同步到从库上. 假如在主库上发生了一个误操作, 没加条件更新了 student 表中的所有数据, 导致了表中所有数据被修改, 此时由于同步操作, 从库也被修改了
- # 登录主库, 修改数据库的对外用户, 使其暂不提供服务, 然后滚动日志
- MySQL> UPDATE MySQL.user SET Host = '127.0.0.1' WHERE User='tomcat';
- Query OK, 1 rows affected (0.00 sec)
- # 刷新权限表
- MySQL> FLUSH PRIVILEGES;
- Query OK, 0 rows affected (0.00 sec)
- # 滚动日志
- MySQL> FLUSH LOGS;
- Query OK, 0 rows affected (0.01 sec)
- # 将从库备份的数据及备份时刻的从库 slave 信息传到主库上
- [[email protected] mysql_data]# scp /tmp/mysql_test_db_2018-06-24.sql 192.168.199.10:/root/
- [[email protected] mysql_data]# scp slave_2018-06-24.info node01:/root/
备份主库的数据目录和二进制日志文件目录
- [[email protected] mysql_logs]# tar -zcvf mysql_master_data.tar.gz /mysql_data/*
- [[email protected] mysql_logs]# tar -zcvf mysql_logs.tar.gz /mysql_logs/*
导入从库最近一次备份的数据
- [[email protected] mysql_logs]# MySQL -uroot -proot -h127.0.0.1 -P3306 </root/mysql_test_db_2018-03-26.sql
- # 注意: 上述的操作不能锁主库的表, 否则全备数据无法导入.
查看备份时刻的从库中应用到的主库二进制日志文件名称及位置点
- [[email protected] mysql_logs]# cat /root/slave_2018-03-26.info
- Master_Log_File: master-bin.000002 #备份时所应用的主库二进制日志文件名称
- Read_Master_Log_Pos: 395 #备份时所应用的主库二进制日志文件的位置
从该日志文件及日志点开始, 将 395 日志点之后的日志文件转换为 sql 脚本, 如果有多个二进制日志文件可以同时转换为 sql 脚本, 如下所示
- [[email protected] mysql_logs]# mysqlbinlog /mysql_logs/master-bin.000002 --start-position=395> /tmp/tmp.sql
- # 将 master-bin.000003,master-bin.000004,master-bin.000005 合并到 / tmp.sql 文件中
- [[email protected] mysql_logs]# mysqlbinlog /mysql_logs/master-bin.00000{
- 3,4,5
- } --start-position=395> /tmp/tmp.sql
找到误操作的 update 语句, 然后删除该语句, 并将增量的 sql 脚本导入数据库
- [[email protected] mysql_logs]# VIM /tmp/tmp.sql
- use `test_db`/*!*/;
- update student set name = 'admin' #删掉这一句
- [[email protected] mysql_logs]# MySQL -uroot -proot -h127.0.0.1 -P3306 </tmp/tmp.sql
登录数据库, 查看数据是否正常, 被误修改的数据是否已经恢复, 如果恢复, 则在主库上全备数据, 然后传到从库, 完成从库恢复
- [[email protected] 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
- [[email protected] mysql_data]# scp /tmp/master_test_db_2018-06-24.sql node01:/root/
- # 如果从库设置了只读, 需要先去掉只读限制
- MySQL> SET GLOBAL read_only = OFF;
- # 将数据导入从库
- [[email protected] mysql_logs]# MySQL -uroot -proot -h127.0.0.1 -P3306 </root/master_test_db_2018-06-24.sql
- # 开启从库的只读
- MySQL> SET GLOBAL read_only = ON;
由于在主库上备份时添加了 --master-date=1 参数, 所以从库导入之后, 不需要重新执行 change master 操作.
登录从库, 查看 SHOW SLAVE STATUS 信息是否正常, 如果正常, 登录主库, 重新修改授权表, 然后对外提供服务
- MySQL> UPDATE MySQL.user set Host = '192.168.0.%' WHERE User = 'tomcat';
- MySQL> FLUSH PRIVILEGES;
- Query OK, 0 rows affected (0.00 sec)
执行完成之后, 主从数据恢复完毕.
至此, 数据恢复介绍完毕, 上述介绍了使用全备加二进制日志实现单实例数据库和主从数据库的数据恢复过程
- Reference link:
- https://segmentfault.com/a/1190000015371440
- https://www.cnblogs.com/-mrl/p/9959365.html
全量备份参考: mysqldump -u root -p -B -F -R -x --master-data=2 test_db|gzip >/opt/backup/test_db_$(date +%F).sql.gz
参数说明:
-B: 指定数据库
-F: 刷新日志
-R: 备份存储过程等
-x: 锁表
--master-data=2 表示在 dump 过程中记录主库的 binlog 和 pos 点, 并在 dump 文件中注释掉这一行;
--master-data=1 表示在 dump 过程中记录主库的 binlog 和 pos 点, 并在 dump 文件中不注释掉这一行, 即恢复时会执行;
--dump-slave=2 表示在 dump 过程中, 在从库 dump,mysqldump 进程也要在从库执行, 记录当时主库的 binlog 和 pos 点, 并在 dump 文件中注释掉这一行;
--dump-slave=1 表示在 dump 过程中, 在从库 dump,mysqldump 进程也要在从库执行, 记录当时主库的 binlog 和 pos 点, 并在 dump 文件中不注释掉这一行;
注意: 在从库上执行备份时, 即 --dump-slave=2, 这时整个 dump 过程都是 stop io_thread 的状态.
mysqldump 导出数据时, 当这个参数 (master-data) 的值为 1 的时候, mysqldump 出来的文件就会包括 CHANGE MASTER TO 这个语句, CHANGE MASTER TO 后面紧接着就是 file 和 position 的记录, 在 slave 上导入数据时就会执行这个语句, salve 就会根据指定这个文件位置从 master 端复制 binlog. 默认情况下这个值是 1
当这个值是 2 的时候, chang master to 也是会写到 dump 文件里面去的, 但是这个语句是被注释的状态.
--single-transaction: 从 5.1.13 开始 mysqldump 指定 --single-transaction 备份时使用 START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ 来代替 begin 开启一个事物, 这样就能在备份的时候产生一个一致的快照
来源: http://www.bubuko.com/infodetail-3117184.html