MySQL如何利用逻辑备份+binlog恢复数据?
Contents
零背景说明
本文档记录在CentOS 7上运行的MySQL 8.0.29版本的数据库，利用逻辑备份+binary log来恢复数据的操作步骤和流程。
一 准备环境
0 OS和数据库信息
#OS [root@node-2 ~]# uname -rm 3.10.0-862.el7.x86_64 x86_64 [root@node-2 ~]# cat /etc/redhat-release CentOS Linux release 7.5.1804 (Core) [root@node-2 ~]#
数据库：
[root@node-2 ~]# mysql -V mysql Ver 8.0.29 for Linux on x86_64 (MySQL Community Server - GPL) [root@node-2 ~]#
1 数据库开启binary log
如果没有开启，则需要手工通过修改配置文件/etc/my.cnf来开启MySQL的binary log。
我这里，通过rpm的方式来安装的MySQL 8.0.29版本的MySQL数据库，默认开启了binary log。
mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.29 | +-----------+ 1 row in set (0.00 sec) mysql> show variables like 'log_bin%'; +---------------------------------+-----------------------------+ | Variable_name | Value | +---------------------------------+-----------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/binlog | | log_bin_index | /var/lib/mysql/binlog.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | +---------------------------------+-----------------------------+ 5 rows in set (0.00 sec) mysql>
操作系统上，确认binary log 信息：
[root@node-2 ~]# ll /var/lib/mysql/binlog.* -rw-r----- 1 mysql mysql 1218575 10月 17 16:01 /var/lib/mysql/binlog.000001 -rw-r----- 1 mysql mysql 16 10月 17 15:14 /var/lib/mysql/binlog.index [root@node-2 ~]#
2 创建测试表和数据
mysql> create database testdb; Query OK, 1 row affected (0.04 sec) mysql> use testdb; Database changed mysql> create table t1(id int); Query OK, 0 rows affected (0.78 sec) mysql> insert into t1 values(1); Query OK, 1 row affected (0.08 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from t1; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql>
3 逻辑备份数据库
[root@node-2 ~]# mysqldump -uroot -pOnlyou_168 --all-databases --flush-logs --source-data >/data/backup/`date +%F`_mysql.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@node-2 ~]#
其中：
–all-databases，表示备份所有的数据库；
–flush-logs, 表示在逻辑备份前，对服务器执行一次切换binary log的操作。这有啥用呢？意味着将来恢复的时候，直接拿我这个逻辑备份的dump文件，就可以恢复到日志切换前的那个状态。
–source-date，表示在逻辑备份的dump文件中，会记录binary log的详细切换信息。配合上面的参数–flush-logs使用。旧版本的MySQL对应的该命令行选项是：–master-data “This option is deprecated and will be removed in a future version. Use source-data instead.”
查看逻辑备份dump文件内容：
-- MySQL dump 10.13 Distrib 8.0.29, for Linux (x86_64) -- -- Host: localhost Database: -- ------------------------------------------------------ -- Server version 8.0.29 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */; /*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Position to start replication or point-in-time recovery from -- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=157; -- -- Current Database: `mysql`
注意，上面的一行，CHANGE MASTER TO MASTER_LOG_FILE=’binlog.000002′, MASTER_LOG_POS=157;
意味着，将来，我这个dump文件可以把数据库恢复到截止binary log为binlog.000002的position=157的位置处。即：至少可以恢复出testdb库里的表t1中的记录id=1的数据。如果，还需要继续恢复数据的话，就需要从binlog.000002的position=157的位置处，继续向后应用binary log的内容来恢复数据。
4 创建新的测试数据
mysql> use testdb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from t1; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.01 sec) mysql> insert into t1 values(200); Query OK, 1 row affected (0.07 sec) mysql> select * from t1; +------+ | id | +------+ | 1 | | 200 | +------+ 2 rows in set (0.00 sec) mysql>
5 备份binary log
[root@node-2 ~]# ll /var/lib/mysql/binlog.* -rw-r----- 1 mysql mysql 1219279 10月 17 16:08 /var/lib/mysql/binlog.000001 -rw-r----- 1 mysql mysql 434 10月 17 16:30 /var/lib/mysql/binlog.000002 -rw-r----- 1 mysql mysql 32 10月 17 16:08 /var/lib/mysql/binlog.index [root@node-2 ~]# mkdir -p /data/backup/binlog [root@node-2 ~]# cp /var/lib/mysql/binlog.* /data/backup/binlog/
6 模拟故障
删除MySQL的所有数据文件和日志文件
[root@node-2 ~]# systemctl stop mysqld [root@node-2 ~]# rm -rf /var/lib/mysql/* [root@node-2 ~]#
7 执行恢复
首先，重新启动初始化MySQL数据库：
[root@node-2 ~]# systemctl start mysqld [root@node-2 ~]# grep "temporary" /var/log/mysqld.log 2022-10-17T08:33:21.380612Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: k4/Y!8G)JuTd [root@node-2 ~]#
修改MySQL root口令：
[root@node-2 ~]# mysql -uroot -p'k4/Y!8G)JuTd' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.29 Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> alter user root@'localhost' identified by 'Onlyou_168'; Query OK, 0 rows affected (0.08 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql>
重新初始化的MySQL数据库实例，压根儿不存在testdb数据库。
读取逻辑备份文件来恢复数据：
mysql> source /data/backup/2022-10-17_mysql.sql ... Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql>
验证恢复结果：
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | testdb | +--------------------+ 5 rows in set (0.00 sec) mysql> select * from testdb.t1; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql>
发现只有1条记录，这是符合预期的。我们当时执行逻辑备份时，testdb库中的t1表，有且仅有1条记录，id=1。
id=200是我们在备份之后，数据库的binary log为binlog.000002时，从position=157的位置处，开始记日志时，才写入数据库里的数据。
那么，我们想要恢复该条记录时，就的通过binary log为binlog.000002时，从position=157的位置处，向后继续恢复。
通过mysqlbinlog命令来读取binary log的内容，并且通过管道传递给mysql命令去执行。
[root@node-2 binlog]# pwd /data/backup/binlog [root@node-2 binlog]# ll 总用量 1200 -rw-r----- 1 root root 1219279 10月 17 16:31 binlog.000001 -rw-r----- 1 root root 434 10月 17 16:31 binlog.000002 -rw-r----- 1 root root 32 10月 17 16:31 binlog.index [root@node-2 binlog]# mysqlbinlog binlog.000002 --start-position=158|mysql -uroot -p'Onlyou_168' mysql: [Warning] Using a password on the command line interface can be insecure. [root@node-2 binlog]# mysql -uroot -p'Onlyou_168' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 8.0.29 MySQL Community Server - GPL Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select * from testdb.t1; +------+ | id | +------+ | 1 | | 200 | +------+ 2 rows in set (0.00 sec) mysql>
最后，确定数据，恢复成功。
二 binary log和redo log的区别
MySQL里的binary log用于记录数据库里的DDL、DML操作，主要用于replication和point in time recovery。默认路径在$DATADIR路径下，名为binlog.xxxxxx;
MySQL里和Oracle对应的redo log是transaction log，默认路径是$DATADIR下，名为ib_logfile0和ib_logfile1。主要用于innodb引擎。所以，命名类似于ib_xxx。
三 小结
通过逻辑备份+binary log来恢复数据的一个快速上手，了解MySQL和其它关系型数据库的不同之处：MySQL既有binary log，也有单独的类似于Oracle的redo log；
了解通过mysqldump备份数据库的选项：–databases –flush-logs –source-data
一条评论
Pingback: