MySQL备份与恢复之保证数据一致性(5)
作者:lijiao 时间:2024-01-16 19:16:20
在上一篇文章中我们提到热拷贝(MySQL备份与恢复之热拷贝),热拷贝也就是在MySQL或者其他数据库服务在运行的情况下使用mysqlhotcopy命令进行备份。这篇文章我们讲解怎样保证数据一致性。现在假设有这样一种情况,我们总是在凌晨对数据库进行备份,假设在凌晨之后发生数据库异常,并且导致数据丢失。这样凌晨之前的数据我们已经做了备份,但是凌晨到发生异常这段时间的数据就会丢失(没有binlog的情况下)。好在InnoDB存储引擎支持事务,也支持Binlog,凌晨到发生异常这段时间的数据就可以通过日志文件进行备份。所以,日志文件是非常重要,非常关键的。我们备份不仅要对数据进行备份,如果条件允许还需要对二进制文件进行备份。当然备份好数据之后,可以清空二进制文件,但如果为了长远考虑,比如恢复出来的数据并不是我们想要的,我们就需要备份二进制文件了。还有一点切记,恢复数据需要转到测试数据库中做,不要在生产环境中做。待测试库中测试没有问题,再在生产环境中做。
示意图
保证数据一致性模拟
第一步,验证数据
[root@serv01 databackup]# rm -rf *
[root@serv01 databackup]# ls
mysql> use larrydb;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_larrydb |
+-------------------+
| class |
| stu |
+-------------------+
2 rows in set (0.00 sec)
mysql> select * from class;
+------+--------+
| cid | cname |
+------+--------+
| 1 | linux |
| 2 | oracle |
+------+--------+
2 rows in set (0.00 sec)
mysql> select * from stu;
+------+---------+------+
| sid | sname | cid |
+------+---------+------+
| 1 | larry01 | 1 |
| 2 | larry02 | 2 |
+------+---------+------+
2 rows in set (0.00 sec)
第二步,备份数据
[root@serv01 databackup]# mysqldump -uroot -p123456 --database larrydb > larrydb.sql
[root@serv01 databackup]# ll larrydb.sql
-rw-r--r--. 1 root root 2613 Sep 10 19:34 larrydb.sql
第三步,清空日志,因为已经做了备份,所以不需要以前的日志
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 27320 |
| mysql-bin.000002 | 1035309 |
| mysql-bin.000003 | 1010 |
| mysql-bin.000004 | 22809 |
| mysql-bin.000005 | 9860 |
| mysql-bin.000006 | 5659 |
| mysql-bin.000007 | 126 |
| mysql-bin.000008 | 10087 |
| mysql-bin.000009 | 8293 |
| mysql-bin.000010 | 476 |
| mysql-bin.000011 | 218 |
| mysql-bin.000012 | 126 |
| mysql-bin.000013 | 1113 |
| mysql-bin.000014 | 1171 |
| mysql-bin.000015 | 126 |
| mysql-bin.000016 | 107 |
| mysql-bin.000017 | 107 |
| mysql-bin.000018 | 13085 |
+------------------+-----------+
18 rows in set (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 107 |
+------------------+-----------+
1 row in set (0.00 sec)
第四步,更新数据
mysql> insert into class values(3,'Devel');
Query OK, 1 row affected (0.01 sec)
mysql> update class set cname="dab" where cid=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from class;
+------+-------+
| cid | cname |
+------+-------+
| 1 | linux |
| 2 | dab |
| 3 | Devel |
+------+-------+
3 rows in set (0.00 sec)
mysql> select * from stu;
+------+---------+------+
| sid | sname | cid |
+------+---------+------+
| 1 | larry01 | 1 |
| 2 | larry02 | 2 |
+------+---------+------+
2 rows in set (0.00 sec)
mysql> delete from stu where cid=2;
Query OK, 1 row affected (0.00 sec)
mysql> update stu set sname="larry007" where sid=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from stu;
+------+----------+------+
| sid | sname | cid |
+------+----------+------+
| 1 | larry007 | 1 |
+------+----------+------+
1 row in set (0.00 sec)
[root@serv01 data]# date
Tue Sep 10 19:38:24 CST 2013
第五步,模拟数据丢失,删除库
[root@serv01 data]# rm -rf /usr/local/mysql/data/larrydb/
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| game |
| hello |
| mnt |
| mysql |
| performance_schema |
| test |
+--------------------+
7 rows in set (0.00 sec)
[root@serv01 data]# cd /usr/local/mysql/data/
[root@serv01 data]# ll
total 28736
drwx------. 2 mysql mysql 4096 Sep 10 19:14 game
drwx------. 2 mysql mysql 4096 Sep 7 00:43 hello
-rw-rw----. 1 mysql mysql 18874368 Sep 10 19:36 ibdata1
-rw-rw----. 1 mysql mysql 5242880 Sep 10 19:36 ib_logfile0
-rw-rw----. 1 mysql mysql 5242880 Sep 4 23:39 ib_logfile1
drwxr-xr-x. 2 mysql mysql 4096 Sep 10 18:35 mnt
drwxr-xr-x. 2 mysql mysql 4096 Sep 4 23:39 mysql
-rw-rw----. 1 mysql mysql 998 Sep 10 19:37 mysql-bin.000001
-rw-rw----. 1 mysql mysql 19 Sep 10 19:34 mysql-bin.index
drwx------. 2 mysql mysql 4096 Sep 4 23:39 performance_schema
-rw-r-----. 1 mysql root 26371 Sep 10 19:30 serv01.host.com.err
-rw-rw----. 1 mysql mysql 5 Sep 10 18:36 serv01.host.com.pid
drwx------. 2 mysql mysql 4096 Sep 7 00:13 test
#可以使用mysqlbinlog命令查看日志文件
[root@serv01 data]# mysqlbinlog mysql-bin.000001
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| game |
| hello |
| mnt |
| mysql |
| performance_schema |
| test |
+--------------------+
7 rows in set (0.00 sec)
mysql> drop database larrydb;
Query OK, 0 rows affected (0.01 sec)
第六步,导入更新之前的数据
[root@serv01 databackup]# mysql -uroot -p123456 < larrydb.sql
ERROR 1050 (42S01) at line 33: Table '`larrydb`.`class`' already exists
[root@serv01 databackup]# mysql -uroot -p123456 < larrydb.sql
mysql> use larrydb;
Database changed
mysql> select * from stu;
+------+---------+------+
| sid | sname | cid |
+------+---------+------+
| 1 | larry01 | 1 |
| 2 | larry02 | 2 |
+------+---------+------+
2 rows in set (0.00 sec)
mysql> select * from class;
+------+--------+
| cid | cname |
+------+--------+
| 1 | linux |
| 2 | oracle |
+------+--------+
2 rows in set (0.00 sec)
第七步,根据日志恢复数据
[root@serv01 data]# mysqlbinlog --stop-datetime "2013-09-10 19:37:45" mysql-bin.000001 | mysql -uroot -p123456
mysql> select * from stu;
+------+---------+------+
| sid | sname | cid |
+------+---------+------+
| 1 | larry01 | 1 |
+------+---------+------+
1 row in set (0.00 sec)
mysql> select * from class;
+------+-------+
| cid | cname |
+------+-------+
| 1 | linux |
| 2 | dab |
| 3 | Devel |
+------+-------+
3 rows in set (0.00 sec)
#规律:恢复的时间点(或者是Commit之后的那个时间点)是发生事故的那个点再加上一秒。
[root@serv01 data]# mysqlbinlog --stop-datetime "2013-09-10 19:37:46" mysql-bin.000001 | mysql -uroot -p123456
mysql> select * from stu;
+------+----------+------+
| sid | sname | cid |
+------+----------+------+
| 1 | larry007 | 1 |
+------+----------+------+
1 row in set (0.00 sec)
mysql> select * from class;
+------+-------+
| cid | cname |
+------+-------+
| 1 | linux |
| 2 | dab |
| 3 | Devel |
| 3 | Devel |
+------+-------+
4 rows in set (0.00 sec)
[root@serv01 data]# mysqlbinlog mysql-bin.000001
# at 7131
#130910 19:37:45 server id 1 end_log_pos 7240 Query thread_id=20 exec_time=996 error_code=0
SET TIMESTAMP=1378813065/*!*/;
update stu set sname="larry007" where sid=1
/*!*/;
# at 7240
#130910 19:37:45 server id 1 end_log_pos 7312 Query thread_id=20 exec_time=996 error_code=0
SET TIMESTAMP=1378813065/*!*/;
COMMIT
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
标签:MySQL,备份与恢复,保证数据一致性
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
如何使用Python自动生成报表并以邮件发送
2021-07-20 08:51:06
使用python实现kmean算法
2022-09-17 13:07:22
![](https://img.aspxhome.com/file/2023/6/131236_0s.png)
pandas 如何将字符串映射为数字
2021-04-15 02:47:33
python中with用法讲解
2022-11-29 23:59:37
python pickle存储、读取大数据量列表、字典数据的方法
2021-10-01 11:22:51
asp+jsp+JavaScript动态实现添加数据行
2023-07-03 05:37:15
![](https://img.aspxhome.com/file/2023/9/114069_0s.png)
MySQL 常见错误分析与解决方法
2024-01-16 12:36:50
vue3简单封装input组件和统一表单数据详解
2024-04-26 17:41:18
![](https://img.aspxhome.com/file/2023/6/133126_0s.jpg)
JS实现为排序好的字符串找出重复行的方法
2024-04-10 10:49:53
SQLServer 使用rand获取随机数的操作
2024-01-24 00:22:11
![](https://img.aspxhome.com/file/2023/6/96166_0s.jpg)
jupyter notebook运行命令显示[*](解决办法)
2022-02-19 01:23:10
![](https://img.aspxhome.com/file/2023/8/67368_0s.png)
一个统计表每天的新增行数及新增存储空间的功能
2024-01-13 22:27:40
PHP实现的DES加密解密类定义与用法示例
2023-07-17 08:55:54
交互设计杂七杂八
2010-09-25 18:41:00
![](https://img.aspxhome.com/file/UploadPic/20109/25/02-24s.jpg)
Java连接Mysql 8.0.18版本的方法详解
2024-01-24 10:08:29
![](https://img.aspxhome.com/file/2023/1/66141_0s.png)
APAP ALV进阶写法及优化详解
2023-11-21 19:41:07
![](https://img.aspxhome.com/file/2023/5/109545_0s.png)
XmlUtils JS操作XML工具类
2024-02-24 14:10:45
SQL Server数据库的高性能优化经验总结
2024-01-18 17:04:55
MySQL无法启动几种常见问题小结
2024-01-18 06:05:32
Django1.11配合uni-app发起微信支付的实现
2023-12-18 13:22:22
![](https://img.aspxhome.com/file/2023/9/121129_0s.jpg)