MySQL-binlog+dump备份还原

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: MySQL-binlog+dump备份还原

binlog日志恢复

       MySQL备份一般采取全库备份加日志备份的方式,例如每天执行一次全备份,每小时执行一次二进制日志备份。这样在MySQL故障后可以使用全备份和日志备份将数据恢复到最后一个二进制日志备份前的任意位置或时间。

binlog介绍

       mysql的二进制日志记录着该数据库的所有增删改的操作日志(前提是要在自己的服务器上开启binlog),还包括了这些操作的执行时间。为了显示这些二进制内容,我们可以使用mysqlbinlog命令来查看。

Binlog的用途

  1. 主从同步
  2. 恢复数据库

开启binary log功能

       通过编辑my.cnf中的log-bin选项可以开启二进制日志;形式如下: log-bin[=DIR/[filename]](配置文件中只写log_bin不写后面的文件名和路径时,默认存放在/usr/local/mysql/data目录下,文件名为主机名-bin.000001…命名) 其中,DIR参数指定二进制文件的存储路径;filename参数指定二级制文件的文件名,其形式为filename.number,number的形式为000001、000002等。

       每次重启mysql服务或运行mysql> flush logs;都会生成一个新的二进制日志文件,这些日志文件的number会不断地递增。除了生成上述的文件外还会生成一个名为filename.index的文件。这个文件中存储所有二进制日志文件的清单又称为二进制文件的索引

       配置保存以后重启mysql的服务器,用mysql> show variables like 'log_bin';查看bin-log是否开启如下所示。

1. [root@mysql ~]# vim /etc/my.cnf 
2. log_bin=mysql-bin
3. server_id=1
4. [root@mysql ~]# systemctl restart mysqld
5. [root@mysql ~]# mysql -uroot -p123 -e "show variables like 'log_bin'"
6. mysql: [Warning] Using a password on the command line interface can be insecure.
7. +---------------+-------+
8. | Variable_name | Value |
9. +---------------+-------+
10. | log_bin       | ON    |
11. +---------------+-------+

配置binlog

1. 查看产生的binary log

注:查看binlog内容是为了恢复数据 bin-log因为是二进制文件,不能通过文件内容查看命令直接打开查看,mysql提供两种方式查看方式,在介绍之前,我们先对数据库进行一下增删改的操作,否则log里边数据有点空。

1. [root@mysql ~]# mysql -uroot -p123
2. #省略部分内容
3. mysql> reset master;                    #清空所有二进制文件,从000001开始
4. Query OK, 0 rows affected (0.00 sec)
5. 
6. mysql> create database bbs character set utf8 collate utf8_bin;
7. Query OK, 1 row affected (0.01 sec)
8. 
9. mysql> use bbs;
10. Database changed
11. mysql> create table tb1(
12.     -> id int primary key auto_increment,
13.     -> name varchar(20));
14. Query OK, 0 rows affected (0.02 sec)
15. 
16. mysql> insert into tb1(name) values('z3'),('l4');
17. Query OK, 2 rows affected (0.02 sec)
18. Records: 2  Duplicates: 0  Warnings: 0
19. 
20. mysql> flush logs;          #刷新日志,下面操作将在000002
21. Query OK, 0 rows affected (0.01 sec)
22. 
23. mysql> delete from tb1 where id=2;
24. Query OK, 1 row affected (0.00 sec)
25. 
26. mysql> insert into tb1(name) values('w5');
27. Query OK, 1 row affected (0.00 sec)
28. 
29. mysql> select * from tb1;
30. +----+------+
31. | id | name |
32. +----+------+
33. |  1 | z3   |
34. |  3 | w5   |
35. +----+------+
36. 2 rows in set (0.00 sec)

2. 查看MySQL Server上的二进制日志

1. mysql> show binary logs;
2. +------------------+-----------+
3. | Log_name         | File_size |
4. +------------------+-----------+
5. | mysql-bin.000001 |       866 |
6. | mysql-bin.000002 |       670 |
7. +------------------+-----------+
8. 2 rows in set (0.00 sec)

3. 查看二进制日志信息的命令:

语法格式:SHOW BINLOG EVENTS[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

       查看二进制日志中的事件,默认显示可找到的第一个二进制日志文件中的事件,包含了日志文件名、事件的开始位置、事件类型、结束位置、信息等内容

1. mysql> show binlog events;
2. +------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------+
3. | Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                                              |
4. +------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------+
5. | mysql-bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.40-log, Binlog ver: 4    #此事件为格式描述事件                                          |
6. | mysql-bin.000001 | 123 | Previous_gtids |         1 |         154 |                                                                                   |
7. | mysql-bin.000001 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                              |
8. | mysql-bin.000001 | 219 | Query          |         1 |         346 | create database bbs character set utf8 collate utf8_bin   //为查询事件                         |
9. | mysql-bin.000001 | 346 | Anonymous_Gtid |         1 |         411 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                              |
10. | mysql-bin.000001 | 411 | Query          |         1 |         553 | use `bbs`; create table tb1(
11. id int primary key auto_increment,
12. name varchar(20)) |
13. | mysql-bin.000001 | 553 | Anonymous_Gtid |         1 |         618 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                              |
14. | mysql-bin.000001 | 618 | Query          |         1 |         689 | BEGIN                             #为查询事件,事务开始                                                 |
15. | mysql-bin.000001 | 689 | Table_map      |         1 |         737 | table_id: 109 (bbs.tb1)           #为表映射事件                                                       |
16. | mysql-bin.000001 | 737 | Write_rows     |         1 |         788 | table_id: 109 flags: STMT_END_F   #为我们执行的insert事件                                                |
17. | mysql-bin.000001 | 788 | Xid            |         1 |         819 | COMMIT /* xid=13 */               #Xid时间是自动提交事务的动作                                                |
18. | mysql-bin.000001 | 819 | Rotate         |         1 |         866 | mysql-bin.000002;pos=4            #为日志轮换事件,是我们执行flush logs开启新日志文件引起的                                                |
19. +------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------+
20. 12 rows in set (0.01 sec)

4. 查看指定的二进制日志中的事件

1. mysql> show binlog events in 'mysql-bin.000002';
2. +------------------+-----+----------------+-----------+-------------+---------------------------------------+
3. | Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
4. +------------------+-----+----------------+-----------+-------------+---------------------------------------+
5. | mysql-bin.000002 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.40-log, Binlog ver: 4 |
6. | mysql-bin.000002 | 123 | Previous_gtids |         1 |         154 |                                       |
7. | mysql-bin.000002 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
8. | mysql-bin.000002 | 219 | Query          |         1 |         290 | BEGIN                                 |
9. | mysql-bin.000002 | 290 | Table_map      |         1 |         338 | table_id: 109 (bbs.tb1)               |
10. | mysql-bin.000002 | 338 | Delete_rows    |         1 |         381 | table_id: 109 flags: STMT_END_F       |
11. | mysql-bin.000002 | 381 | Xid            |         1 |         412 | COMMIT /* xid=15 */                   |
12. | mysql-bin.000002 | 412 | Anonymous_Gtid |         1 |         477 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
13. | mysql-bin.000002 | 477 | Query          |         1 |         548 | BEGIN                                 |
14. | mysql-bin.000002 | 548 | Table_map      |         1 |         596 | table_id: 109 (bbs.tb1)               |
15. | mysql-bin.000002 | 596 | Write_rows     |         1 |         639 | table_id: 109 flags: STMT_END_F       |
16. | mysql-bin.000002 | 639 | Xid            |         1 |         670 | COMMIT /* xid=16 */                   |
17. +------------------+-----+----------------+-----------+-------------+---------------------------------------+
18. 12 rows in set (0.01 sec)

该命令还包含其他选项以便灵活查看,以pos219下面起始到第三个结束。

1. mysql> show binlog events in 'mysql-bin.000002' from 219 limit 1,3;
2. +------------------+-----+-------------+-----------+-------------+---------------------------------+
3. | Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                            |
4. +------------------+-----+-------------+-----------+-------------+---------------------------------+
5. | mysql-bin.000002 | 290 | Table_map   |         1 |         338 | table_id: 109 (bbs.tb1)         |
6. | mysql-bin.000002 | 338 | Delete_rows |         1 |         381 | table_id: 109 flags: STMT_END_F |
7. | mysql-bin.000002 | 381 | Xid         |         1 |         412 | COMMIT /* xid=15 */             |
8. +------------------+-----+-------------+-----------+-------------+---------------------------------+
9. 3 rows in set (0.00 sec)

SHOW BINARY LOGS 等价于 SHOW MASTER LOGS PURGE BINARY LOGS用于删除二进制日志。

如: PURGEBINARY LOGS TO 'mysql-bin.00010';         #把这个文件之前的其他文件都删除掉

       PURGE BINARY LOGS BEFORE '2016-08-28 22:46:26';         #把指定时间之前的二进制文件删除了

       RESET MASTER 与 RESET SLAVE 前者清空index文件中列出的所有二进制日志,重置index文件为空,并创建一个新的二进制日志文件,一般用于MASTER首次启动时。后者使SLAVE忘记其在MASTER二进制日志文件中的复制位置,它会删除master.info、relay-log.info 和所有中继日志文件并开始一个新的中继日志文件,以便于开始一个干净的复制。在使用RESET SLAVE前需先关闭SLAVE复制线程。 上述方式可以查看到服务器上存在的二进制日志文件及文件中的事件,但是想查看到文件中具体的内容并应于恢复场景还得借助mysqlbinlog这个工具。

       语法格式: mysqlbinlog [options] log_file ... 输出内容会因日志文件的格式以及mysqlbinlog工具使用的选项不同而略不同。 mysqlbinlog的可用选项可参考man手册。 二进制日志文件的格式包含行模式、语句模式和混合模式(也即有服务器决定在什么情况下记录什么类型的日志),基于语句的日志中事件信息包含执行的语句等,基于行的日志中事件信息包含的是行的变化信息等。混合模式的日志中两种类型的事件信息都会记录。 为了便于查看记录了行变化信息的事件在当时具体执行了什么样的SQL语句可以使用mysqlbinlog工具的-v(--verbose)选项,该选项会将行事件重构成被注释掉的伪SQL语句,如果想看到更详细的信息可以将该选项给两次如-vv,这样可以包含一些数据类型和元信息的注释内容,如 先切换到binlog所在的目录下

1. [root@mysql ~]# cd /usr/local/mysql/data
2. [root@mysql data]# mysqlbinlog mysql-bin.000001             #查看二进制文件
3. 
4. [root@mysql data]# mysqlbinlog -v mysql-bin.000001          #查看详细内容
5. 
6. [root@mysql data]# mysqlbinlog -vv mysql-bin.000001         #查看更详细内容

       另外mysqlbinlog和可以通过--read-from-remote-server选项从远程服务器读取二进制日志文件,这时需要一些而外的连接参数,如-h,-P,-p,-u等,这些参数仅在指定了--read-from-remote-server后有效。 无论是本地二进制日志文件还是远程服务器上的二进制日志文件,无论是行模式、语句模式还是混合模式的二进制日志文件,被mysqlbinlog工具解析后都可直接应用与MySQL Server进行基于时间点、位置或数据库的恢复。

       下面我们就来演示如何使用binlog恢复之前删除数据(id=2那条记录) 注意:在实际生产环境中,如果遇到需要恢复数据库的情况,不要让用户能访问到数据库,以避免新的数据插入进来,以及在主从的环境下,关闭主从。 查看binlog文件,从中找出delete from test.tb1 where id=2

1. [root@mysql ~]# cd /usr/local/mysql/data
2. [root@mysql data]# mysqlbinlog -v mysql-bin.000002 
3. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
4. /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
5. DELIMITER /*!*/;
6. # at 4
7. #230324  8:44:33 server id 1  end_log_pos 123 CRC32 0xcbae27e2  Start: binlog v 4, server v 5.7.40-log created 230324  8:44:33
8. # Warning: this binlog is either in use or was not closed properly.
9. BINLOG '
10. cfIcZA8BAAAAdwAAAHsAAAABAAQANS43LjQwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
11. AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
12. AeInrss=
13. '/*!*/;
14. # at 123
15. #230324  8:44:33 server id 1  end_log_pos 154 CRC32 0xc6b0dd29  Previous-GTIDs
16. # [empty]
17. # at 154
18. #230324  8:45:29 server id 1  end_log_pos 219 CRC32 0x59f973f8  Anonymous_GTID  last_committed=0    sequence_number=1   rbr_only=yes
19. /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
20. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
21. # at 219
22. #230324  8:45:29 server id 1  end_log_pos 290 CRC32 0xe9a3eaa9  Query   thread_id=3   exec_time=0   error_code=0
23. SET TIMESTAMP=1679618729/*!*/;
24. SET @@session.pseudo_thread_id=3/*!*/;
25. SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
26. SET @@session.sql_mode=1436549152/*!*/;
27. SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
28. /*!\C utf8 *//*!*/;
29. SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
30. SET @@session.lc_time_names=0/*!*/;
31. SET @@session.collation_database=DEFAULT/*!*/;
32. BEGIN
33. /*!*/;
34. # at 290
35. #230324  8:45:29 server id 1  end_log_pos 338 CRC32 0xe66de950  Table_map: `bbs`.`tb1` mapped to number 109
36. # at 338
37. #230324  8:45:29 server id 1  end_log_pos 381 CRC32 0x6c2d4b4b  Delete_rows: table id 109 flags: STMT_END_F
38. 
39. BINLOG '
40. qfIcZBMBAAAAMAAAAFIBAAAAAG0AAAAAAAEAA2JicwADdGIxAAIDDwI8AAJQ6W3m
41. qfIcZCABAAAAKwAAAH0BAAAAAG0AAAAAAAEAAgAC//wCAAAAAmw0S0stbA==
42. '/*!*/;
43. ### DELETE FROM `bbs`.`tb1`
44. ### WHERE
45. ###   @1=2
46. ###   @2='l4'
47. # at 381
48. #230324  8:45:29 server id 1  end_log_pos 412 CRC32 0x09d061ff  Xid = 15
49. COMMIT/*!*/;
50. # at 412
51. #230324  8:45:49 server id 1  end_log_pos 477 CRC32 0x00977c6e  Anonymous_GTID  last_committed=1    sequence_number=2   rbr_only=yes
52. /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
53. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
54. # at 477
55. #230324  8:45:49 server id 1  end_log_pos 548 CRC32 0x8ea03cb0  Query   thread_id=3   exec_time=0   error_code=0
56. SET TIMESTAMP=1679618749/*!*/;
57. BEGIN
58. /*!*/;
59. # at 548
60. #230324  8:45:49 server id 1  end_log_pos 596 CRC32 0xe32cd3c5  Table_map: `bbs`.`tb1` mapped to number 109
61. # at 596
62. #230324  8:45:49 server id 1  end_log_pos 639 CRC32 0x30b3d697  Write_rows: table id 109 flags: STMT_END_F
63. 
64. BINLOG '
65. vfIcZBMBAAAAMAAAAFQCAAAAAG0AAAAAAAEAA2JicwADdGIxAAIDDwI8AALF0yzj
66. vfIcZB4BAAAAKwAAAH8CAAAAAG0AAAAAAAEAAgAC//wDAAAAAnc1l9azMA==
67. '/*!*/;
68. ### INSERT INTO `bbs`.`tb1`
69. ### SET
70. ###   @1=3
71. ###   @2='w5'
72. # at 639
73. #230324  8:45:49 server id 1  end_log_pos 670 CRC32 0xcfda2a0b  Xid = 16
74. COMMIT/*!*/;
75. SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
76. DELIMITER ;
77. # End of log file
78. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
79. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

       从中可以看出delete事件发生position是290,事件结束position是412 恢复流程:直接用bin-log日志将数据库恢复到删除位置290前,然后跳过故障点,再进行恢复下面所有的操作,命令如下 由于之前没有做过全库备份,所以要使用所有binlog日志恢复,所以生产环境中需要很长时间恢复,导出相关binlog文件。

1. [root@mysql ~]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000001> /opt/mybin.000001.sql
2. [root@mysql ~]# mysqlbinlog --stop-position=290 /usr/local/mysql/data/mysql-bin.000002> /opt/mybin.290.sql
3. [root@mysql ~]# mysqlbinlog --start-position=412 /usr/local/mysql/data/mysql-bin.000002> /opt/mybin.412.sql
4.

删除bbs数据库

1. mysql> drop database bbs;
2. Query OK, 1 row affected (0.09 sec)

利用binlog恢复数据

逐步恢复,查看是否恢复全表。

1. [root@mysql ~]# mysql -uroot -p123 < /opt/mybin.000001.sql 
2. #验证
3. mysql> select * from bbs.tb1;
4. +----+------+
5. | id | name |
6. +----+------+
7. |  1 | z3   |
8. |  2 | l4   |
9. +----+------+
10. 2 rows in set (0.00 sec)
11. [root@mysql ~]# mysql -uroot -p123 < /opt/mybin.290.sql 
12. #验证
13. mysql> select * from bbs.tb1;
14. +----+------+
15. | id | name |
16. +----+------+
17. |  1 | z3   |
18. |  2 | l4   |
19. +----+------+
20. 2 rows in set (0.00 sec)
21. [root@mysql ~]# mysql -uroot -p123 < /opt/mybin.412.sql
22. #验证
23. mysql> select * from bbs.tb1;
24. +----+------+
25. | id | name |
26. +----+------+
27. |  1 | z3   |
28. |  2 | l4   |
29. |  3 | w5   |
30. +----+------+
31. 3 rows in set (0.00 sec)

       可以看到完整的都恢复过来了 mysqlbinlog 可以使用多个选项,常见的选项有以下几个:

--start-datetime 从二进制日志中读取指定时间戳或者本地计算机时间之后的日志事件。

--stop-datetime 从二进制日志中读取指定时间戳或者本地计算机时间之前的日志事件。

--start-position从二进制日志中读取指定position 事件位置作为开始。

--stop-position 从二进制日志中读取指定position 事件位置作为事件截至。

mysqldump

       mysqldump是mysql用于备份和数据转移的一个工具。它主要产生一系列的SQL语句,可以封装到文件,该文件包含有所有重建你的数据库所需要的 SQL命令如CREATE DATABASE,CREATE TABLE,INSERT等等。可以用来实现轻量级的快速迁移或恢复数据库。 mysqldump 是将数据表导成 SQL 脚本文件,在不同的 MySQL 版本之间升级时相对比较合适,这也是最常用的备份方法。 mysqldump一般在数据量很小的时候(几个G)可以用于备份。当数据量比较大的情况下,就不建议用mysqldump工具进行备份了。

数据库的导出

       导出对象说明:mysqldump可以针对单个表、多个表、单个数据库、多个数据库、所有数据库进行导出的操作

1. #导出单表
2. [root@mysql ~]# mysqldump -uroot -p123 库名  表名 > 备份路径
3. #导出多表
4. [root@mysql ~]# mysqldump -uroot -p123 库名 表名1 表名2 ...> 备份路径
5. #导出所有表
6. [root@mysql ~]# mysqldump -uroot -p123 库名 > 备份路径
7. #导出单库
8. [root@mysql ~]# mysqldump -uroot -p123 --databases[-B] 库名  > 备份路径
9. #导出多库
10. [root@mysql ~]# mysqldump -uroot -p123 --databases[-B] 库名1 库名2 ... > 备份路径
11. #导出所有库
12. [root@mysql ~]# mysqldump -uroot -p123 --all-databases[-A] > 备份路径
13. #--flush-logs这个选项就会完整备份的时候重新开启一个新binlog
14. [root@mysql ~]# mysqldump -uroot -p --flush-logs 库名 > 备份路径

数据库的导入

[root@mysql ~]# mysql -uroot -p123 库名 < 备份路径

       mysql安装自带的一些库丢失,靠备份导入却不能实现恢复,需要初始化库后在导入才能恢复。那核心库丢失如何恢复?下面跟着步骤备份库,删除库,并且恢复回来。

mysqldump+binlog

       在前面我们介绍了mysql的binlog和mysqldump工具,下面我们来学习如何实现mysqldump全库备份+binlog的数据恢复。

先开启二进制日志

1. [root@mysql ~]# vim /etc/my.cnf
2. log_bin=mysql-bin
3. server_id=1
4. [root@mysql ~]# systemctl restart mysqld

检查开启binlog 先创建一些原始数据

1. mysql> reset master;
2. Query OK, 0 rows affected (0.00 sec)
3. 
4. mysql> create database test_db;
5. Query OK, 1 row affected (0.00 sec)
6. 
7. mysql> use test_db;
8. Database changed
9. mysql> create table tb1(id int primary key auto_increment,name varchar(20));
10. Query OK, 0 rows affected (0.07 sec)
11. 
12. mysql> insert into tb1(name) values('tom1'),('tom2');
13. Query OK, 2 rows affected (0.02 sec)
14. Records: 2  Duplicates: 0  Warnings: 0
15. 
16. mysql> commit;
17. Query OK, 0 rows affected (0.00 sec)
18. 
19. mysql> select * from tb1;
20. +----+------+
21. | id | name |
22. +----+------+
23. |  1 | tom1 |
24. |  2 | tom2 |
25. +----+------+
26. 2 rows in set (0.00 sec)

方案:mysqldump全库备份+binlog还原 1、mysqldump备份方案: 每周一凌晨1点全库备份 2、备份步骤

(1) 创建备份目录

[root@mysql ~]# mkdir -p /opt/mysqlbackup/daily

(2)全库备份 这里我们模拟周一的完整备份数据库任务

1. [root@mysql ~]# mysqldump -uroot -p123 --flush-logs test_db > /opt/mysqlbackup/test_db_`date +%Y%m%d_%H%M%S`.sql            #备份库 时间戳命名
2. [root@mysql ~]# ll /opt/mysqlbackup/
3. 总用量 4
4. drwxr-xr-x. 2 root root    6 3月  29 13:45 daily
5. -rw-r--r--. 1 root root 1871 3月  29 13:46 test_db_20230329_134659.sql

备份mysqldump全库备份之前的binlog日志文(注:生产环境中可能不只一个binlog文件)

1. [root@mysql ~]# cp /usr/local/mysql/data/mysql-bin.000001 /opt/mysqlbackup/daily/
2. [root@mysql ~]# mysql -uroot -p123 -e "purge binary logs to 'mysql-bin.000002'"

登录mysql模拟下操作失误,将数据修改错误了。

1. mysql> use test_db;
2. Database changed
3. mysql> delete from tb1 where id=1;
4. Query OK, 1 row affected (0.01 sec)
5. 
6. mysql> commit;
7. Query OK, 0 rows affected (0.00 sec)
8. 
9. mysql> insert into tb1(name) values('tom3');
10. Query OK, 1 row affected (0.00 sec)
11. 
12. mysql> commit;
13. Query OK, 0 rows affected (0.00 sec)

备份自mysqldump之后的binlog日志文件

[root@mysql ~]# cp /usr/local/mysql/data/mysql-bin.000002 /opt/mysqlbackup/daily/

上面的模拟的误操作是删除了id=1的记录

(3)现在我们使用mysqldump的全库备份和binlog来恢复数据。 使用mysqldump的备份进行全库恢复

[root@mysql ~]# mysql -uroot -p123 test_db < /opt/mysqlbackup/test_db_20230329_135149.sql

查询数据

1. [root@mysql ~]# mysql -uroot -p123 -e "select * from test_db.tb1"
2. mysql: [Warning] Using a password on the command line interface can be insecure.
3. +----+------+
4. | id | name |
5. +----+------+
6. |  1 | tom1 |
7. |  2 | tom2 |
8. +----+------+

       从显示结果可以看到使用mysqldump备份将数据还原到了备份时的状态,刚才删除的数据(id=2)恢复回来了,但备份后产生的数据却丢失了所以还得利用binlog进一步还原 因为删除是在全库备份后发生的,而mysqldump全库备份时使用--flush-logs选项,所以只需要分析全库备份后的binlog即mysql-bin.000002。

1. mysql> show binary logs;
2. +------------------+-----------+
3. | Log_name         | File_size |
4. +------------------+-----------+
5. | mysql-bin.000002 |      1853 |
6. +------------------+-----------+
7. 1 row in set (0.01 sec)

查看mysql-bin.000002中的事件,可以看到有删除事件

1. mysql> show binlog events in 'mysql-bin.000002';
2. #省略部分内容
3. | mysql-bin.000002 |  219 | Query          |         1 |         294 | BEGIN                                                                                                                                                                                     |
4. | mysql-bin.000002 |  294 | Table_map      |         1 |         346 | table_id: 109 (test_db.tb1)                                                                                                                                                               |
5. | mysql-bin.000002 |  346 | Delete_rows    |         1 |         391 | table_id: 109 flags: STMT_END_F                                                                                                                                                           |
6. | mysql-bin.000002 |  391 | Xid            |         1 |         422 | COMMIT /* xid=43 */

       使用mysqlbinlog 命令可以查看备份的binlog文件的详细事件。 恢复流程:我们直接用bin-log日志将数据库恢复到删除位置前,然后跳过故障点,再进行恢复删除后的所有操作。

1. [root@mysql ~]# mysqlbinlog -v /opt/mysqlbackup/daily/mysql-bin.000002 
2. #省略查看内容

我们先用mysqlbinlog命令找到delete那条语句的位置

1. # at 219
2. #230329 13:53:58 server id 1  end_log_pos 294 CRC32 0x557ff3dc  Query   thread_id=2 exec_time=0 error_code=0
3. SET TIMESTAMP=1680069238/*!*/;
4. SET @@session.pseudo_thread_id=2/*!*/;
5. SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
6. SET @@session.sql_mode=1436549152/*!*/;
7. SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
8. /*!\C utf8 *//*!*/;
9. SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
10. SET @@session.lc_time_names=0/*!*/;
11. SET @@session.collation_database=DEFAULT/*!*/;
12. BEGIN
13. /*!*/;
14. # at 294
15. #230329 13:53:58 server id 1  end_log_pos 346 CRC32 0xa80266ea  Table_map: `test_db`.`tb1` mapped to number 109
16. # at 346
17. #230329 13:53:58 server id 1  end_log_pos 391 CRC32 0x69164e4d  Delete_rows: table id 109 flags: STMT_END_F
18. 
19. BINLOG '
20. dtIjZBMBAAAANAAAAFoBAAAAAG0AAAAAAAEAB3Rlc3RfZGIAA3RiMQACAw8CPAAC6mYCqA==
21. dtIjZCABAAAALQAAAIcBAAAAAG0AAAAAAAEAAgAC//wBAAAABHRvbTFNThZp
22. '/*!*/;
23. ### DELETE FROM `test_db`.`tb1`
24. ### WHERE
25. ###   @1=1
26. ###   @2='tom1'
27. # at 391
28. #230329 13:53:58 server id 1  end_log_pos 422 CRC32 0xfa0ce547  Xid = 43
29. COMMIT/*!*/;

       通过mysqlbinlog命令所显示的结果可以看到误操作delete的开始postion为219,结束position是422。 从二进制日志中读取指定position=219事件位置作为截至,即把数据恢复到delete删除前

[root@mysql ~]# mysqlbinlog --stop-position=219 /opt/mysqlbackup/daily/mysql-bin.000002 | mysql -uroot -p123

       从二进制日志中读取指定position=422事件位置作为开始,即跳过删除事件,恢复删除事件之后对数据的正常操作

[root@mysql ~]# mysqlbinlog --start-position=422 /opt/mysqlbackup/daily/mysql-bin.000002 | mysql -uroot -p123

查看恢复结果:

1. [root@mysql ~]# mysql -uroot -p123 -e "select * from test_db.tb1"
2. mysql: [Warning] Using a password on the command line interface can be insecure.
3. +----+------+
4. | id | name |
5. +----+------+
6. |  1 | tom1 |
7. |  2 | tom2 |
8. |  3 | tom3 |
9. +----+------+

       从上面显示可以看出数据恢复到正常状态 生产环境中Mysql数据库的备份是周期性重复的操作,所以通常是要编写脚本实现,通过crond计划任务周期性执行备份脚本 mysqldump

       备份方案: 周日凌晨1点全库备份 周一到周六凌晨每隔4个小时增量备份一次 设置crontab任务,每天执行备份脚本

1. [root@mysql ~]# crontab -e
2. #每个星期日凌晨1:00执行完全备份脚本
3. 0 1 * * 0 /root/mysqlfullbackup.sh >/dev/null 2>&1
4. #周一到周六每隔4个小时增量备份一次
5. 0 */4 * * 1-6 /root/mysqldailybackup.sh >/dev/null 2>&1

mysqlfullbackup.sh脚本内容:

1. [root@mysql ~]# vim mysqlfullbackup.sh
2. #!/bin/sh
3. # Name:mysqlFullBackup.sh
4. # 定义数据库目录
5. mysqlDir=/usr/local/mysql
6. # 定义用于备份数据库的用户名和密码
7. user=root
8. userpwd=123
9. dbname=test_db
10. # 定义备份目录
11. databackupdir=/opt/mysqlbackup
12. [ ! -d $databackupdir ] && mkdir $databackupdir
13. # 定义邮件正文文件
14. emailfile=$databackupdir/email.txt
15. # 定义邮件地址
16. email=root@localhost.localdomain
17. # 定义备份日志文件
18. logfile=$databackupdir/mysqlbackup.log
19. DATE=`date -I`
20. echo "" > $emailfile
21. echo $(date +"%y-%m-%d %H:%M:%S") >> $emailfile
22. cd $databackupdir
23. # 定义备份文件名
24. dumpfile=mysql_$DATE.sql
25. gzdumpfile=mysql_$DATE.sql.tar.gz
26. # 使用mysqldump备份数据库,请根据具体情况设置参数
27. $mysqlDir/bin/mysqldump -u$user -p$userpwd --flush-logs -x $dbname > $dumpfile
28. # 压缩备份文件
29. if [ $? -eq 0 ]; then
30.  tar czf $gzdumpfile $dumpfile >> $emailfile 2>&1
31. echo "BackupFileName:$gzdumpfile" >> $emailfile
32. echo "DataBase Backup Success!" >> $emailfile
33. rm -f $dumpfile
34. else
35. echo "DataBase Backup Fail!" >> $emailfile
36. fi
37. # 写日志文件
38. echo "--------------------------------------------------------" >> $logfile
39. cat $emailfile >> $logfile
40. # 发送邮件通知
41. cat $emailfile | mail -s "MySQL Backup" $email

mysqldailybackup.sh脚本内容:

1. [root@mysql ~]# vim mysqldailbackup.sh
2. #!/bin/sh
3. # Name:mysqlDailyBackup.sh
4. # 定义数据库目录和数据目录
5. mysqldir=/usr/local/mysql
6. datadir=$mysqldir/data
7. # 定义用于备份数据库的用户名和密码
8. user=root
9. userpwd=123456
10. # 定义备份目录,每日备份文件备份到$dataBackupDir/daily
11. databackupdir=/opt/mysqlbackup
12. dailybackupdir=$databackupdir/daily
13. [ ! -d $dailybackupdir ] && mkdir -p $databackupdir/daily
14. # 定义邮件正文文件
15. emailfile=$databackupdir/email.txt
16. # 定义邮件地址
17. email=root@localhost.localdomain
18. # 定义日志文件
19. logfile=$databackupdir/mysqlbackup.log
20. echo "" > $emailfile
21. echo $(date +"%y-%m-%d %H:%M:%S") >> $emailfile
22. #
23. # 刷新日志,使数据库使用新的二进制日志文件
24. $mysqldir/bin/mysqladmin -u$user -p$userpwd flush-logs
25. cd $datadir
26. # 得到二进制日志列表
27. filelist=`cat mysql-bin.index`
28. icounter=0
29. for file in $filelist
30. do
31.  icounter=`expr $icounter + 1` 
32. done
33. nextnum=0
34. ifile=0
35. for file in $filelist
36. do
37.  binlogname=`basename $file`
38.  nextnum=`expr $nextnum + 1`
39. # 跳过最后一个二进制日志(数据库当前使用的二进制日志文件)
40. if [ $nextnum -eq $icounter ]; then
41. echo "Skip lastest!" > /dev/null
42. else
43.  dest=$dailybackupdir/$binlogname
44. # 跳过已经备份的二进制日志文件
45. if [ -e $dest ]; then
46. echo "Skip exist $binlogname!" > /dev/null
47. else
48. # 备份日志文件到备份目录
49. cp $binlogname $dailybackupdir
50. if [ $? -eq 0 ]; then
51.  ifile=`expr $ifile + 1`
52. echo "$binlogname backup success!" >> $emailfile
53. fi
54. fi
55. fi
56. done
57. if [ $ifile -eq 0 ];then
58. echo "No Binlog Backup!" >> $emailfile
59. else
60. echo "Backup $ifile File(s)." >> $emailfile
61. echo "Backup MySQL Binlog OK!" >> $emailfile
62. fi
63. # 发送邮件通知
64. cat $emailfile | mail -s "MySQL Backup" $email
65. # 写日志文件
66. echo "--------------------------------------------------------" >> $logfile
67. cat $emailfile >> $logfile


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
关系型数据库 MySQL 数据库
rds备份与恢复
rds备份与恢复
28 3
|
2月前
|
关系型数据库 MySQL 数据库
Python tk dos命令备份mysql数据库
Python tk dos命令备份mysql数据库
16 0
|
2月前
|
存储 关系型数据库 MySQL
mysql数据库如何做到定期备份
mysql数据库如何做到定期备份
264 2
|
3月前
|
存储 关系型数据库 MySQL
MySQL库的操作『增删改查 ‖ 编码问题 ‖ 备份与恢复』
MySQL库的操作『增删改查 ‖ 编码问题 ‖ 备份与恢复』
44 0
|
15天前
|
SQL 关系型数据库 MySQL
mysql怎么备份
mysql怎么备份
72 7
|
2月前
|
存储 关系型数据库 MySQL
利用Xtrabackup进行mysql增量备份和全量备份
利用Xtrabackup进行mysql增量备份和全量备份
89 0
|
10天前
|
关系型数据库 MySQL Linux
Linux环境下定时备份mysql数据库
Linux环境下定时备份mysql数据库
|
15天前
|
关系型数据库 MySQL 数据安全/隐私保护
百度搜索:蓝易云【mysql在执行innobackupex增量备份时报错cannot open *****/xtrabackup_checkpoints】
如果以上方法都不能解决问题,建议检查其他错误日志或报错信息,以进一步确定问题的原因,并采取相应的措施进行修复。备份和恢复过程中的错误处理是保证数据安全的关键,务必谨慎处理。 买CN2云服务器,免备案服务器,高防服务器,就选蓝易云。百度搜索:蓝易云
17 2
|
17天前
|
存储 关系型数据库 MySQL
mysql怎么备份
mysql怎么备份
14 7
|
24天前
|
监控 容灾 安全
规划阿里云RDS跨区迁移并构建容灾与备份策略
规划阿里云RDS(Relational Database Service)跨区迁移并构建容灾与备份策略
20 2

热门文章

最新文章