MySQL特殊恢复实战第二讲:各种文件丢失，如何救库

##

MySQL特殊恢复实战-第二讲:各种文件丢失，如何救库

这个文章来源于我1x年的时候，面试某备份外企，当时原问题是讲解Oracle的启动过程，众所周知，O的启动过程比较复杂，且需要一些文件(已经不玩O好多年了，记不住了，欢迎吐槽并补充)。

现在主要处理MySQL和PostgreSQL的事，所以我们来看MySQL丢了哪些文件会对启动有何影响以及如何拯救。

1、准备测试库和环境

这里不需要很复杂的测试环境，sakila db就可以了，然后正常关闭数据。安装方式也是repo安装。

[root@centos ~]# unzip sakila-db.zip
Archive: sakila-db.zip
creating: sakila-db/
inflating: sakila-db/sakila-data.sql
inflating: sakila-db/sakila-schema.sql
inflating: sakila-db/sakila.mwb
[root@centos ~]# mysql < sakila-db/sakila-schema.sql
[root@centos ~]# mysql < sakila-db/sakila-data.sql
[root@centos ~]# systemctl stop mysqld


检查状态

[root@centos ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: inactive (dead) since Wed 2024-03-06 21:06:19 CST; 2min 7s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 1927 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 1898 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 1927 (code=exited, status=0/SUCCESS)
Status: "Server shutdown complete"

Mar 06 21:05:51 centos systemd[1]: Starting MySQL Server...
Mar 06 21:05:53 centos systemd[1]: Started MySQL Server.
Mar 06 21:06:18 centos systemd[1]: Stopping MySQL Server...
Mar 06 21:06:19 centos systemd[1]: Stopped MySQL Server.


2、查看MySQL的文件

2.1、实例的文件

[root@centos ~]# ls -lsa /var/lib/mysql
total 81668
4 drwxr-x--x 8 mysql mysql 4096 Mar 6 21:06 .
4 drwxr-xr-x. 64 root root 4096 Mar 6 20:59 ..
4 -rw-r----- 1 mysql mysql 56 Mar 6 20:59 auto.cnf
4 -rw-r----- 1 mysql mysql 667 Mar 6 21:01 binlog.000001
4 -rw-r----- 1 mysql mysql 180 Mar 6 21:04 binlog.000002
1332 -rw-r----- 1 mysql mysql 1360592 Mar 6 21:06 binlog.000003
4 -rw-r----- 1 mysql mysql 48 Mar 6 21:05 binlog.index
4 -rw------- 1 mysql mysql 1680 Mar 6 20:59 ca-key.pem
4 -rw-r--r-- 1 mysql mysql 1112 Mar 6 20:59 ca.pem
4 -rw-r--r-- 1 mysql mysql 1112 Mar 6 20:59 client-cert.pem
4 -rw------- 1 mysql mysql 1680 Mar 6 20:59 client-key.pem
192 -rw-r----- 1 mysql mysql 196608 Mar 6 21:06 #ib_16384_0.dblwr
8384 -rw-r----- 1 mysql mysql 8585216 Mar 6 20:59 #ib_16384_1.dblwr
4 -rw-r----- 1 mysql mysql 2455 Mar 6 21:06 ib_buffer_pool
12288 -rw-r----- 1 mysql mysql 12582912 Mar 6 21:06 ibdata1
4 drwxr-x--- 2 mysql mysql 4096 Mar 6 21:06 #innodb_redo
0 drwxr-x--- 2 mysql mysql 6 Mar 6 21:06 #innodb_temp
0 drwxr-x--- 2 mysql mysql 143 Mar 6 20:59 mysql
26624 -rw-r----- 1 mysql mysql 27262976 Mar 6 21:06 mysql.ibd
12 drwxr-x--- 2 mysql mysql 8192 Mar 6 20:59 performance_schema
4 -rw------- 1 mysql mysql 1680 Mar 6 20:59 private_key.pem
4 -rw-r--r-- 1 mysql mysql 452 Mar 6 20:59 public_key.pem
4 drwxr-x--- 2 mysql mysql 4096 Mar 6 21:06 sakila
4 -rw-r--r-- 1 mysql mysql 1112 Mar 6 20:59 server-cert.pem
4 -rw------- 1 mysql mysql 1680 Mar 6 20:59 server-key.pem
0 drwxr-x--- 2 mysql mysql 28 Mar 6 20:59 sys
16384 -rw-r----- 1 mysql mysql 16777216 Mar 6 21:06 undo_001
16384 -rw-r----- 1 mysql mysql 16777216 Mar 6 21:06 undo_002


2.2、数据库的文件

[root@centos ~]# ls -lsa /var/lib/mysql/sakila/
total 24792
4 drwxr-x--- 2 mysql mysql 4096 Mar 6 21:06 .
4 drwxr-x--x 8 mysql mysql 4096 Mar 6 21:06 ..
128 -rw-r----- 1 mysql mysql 131072 Mar 6 21:06 actor.ibd
256 -rw-r----- 1 mysql mysql 262144 Mar 6 21:06 address.ibd
112 -rw-r----- 1 mysql mysql 114688 Mar 6 21:06 category.ibd
144 -rw-r----- 1 mysql mysql 147456 Mar 6 21:06 city.ibd
112 -rw-r----- 1 mysql mysql 114688 Mar 6 21:06 country.ibd
208 -rw-r----- 1 mysql mysql 212992 Mar 6 21:06 customer.ibd
352 -rw-r----- 1 mysql mysql 360448 Mar 6 21:06 film_actor.ibd
160 -rw-r----- 1 mysql mysql 163840 Mar 6 21:06 film_category.ibd
352 -rw-r----- 1 mysql mysql 360448 Mar 6 21:06 film.ibd
272 -rw-r----- 1 mysql mysql 278528 Mar 6 21:06 film_text.ibd
208 -rw-r----- 1 mysql mysql 212992 Mar 6 21:06 fts_0000000000000431_00000000000000b6_index_1.ibd
112 -rw-r----- 1 mysql mysql 114688 Mar 6 21:06 fts_0000000000000431_00000000000000b6_index_2.ibd
112 -rw-r----- 1 mysql mysql 114688 Mar 6 21:06 fts_0000000000000431_00000000000000b6_index_3.ibd
112 -rw-r----- 1 mysql mysql 114688 Mar 6 21:06 fts_0000000000000431_00000000000000b6_index_4.ibd
112 -rw-r----- 1 mysql mysql 114688 Mar 6 21:06 fts_0000000000000431_00000000000000b6_index_5.ibd
112 -rw-r----- 1 mysql mysql 114688 Mar 6 21:06 fts_0000000000000431_00000000000000b6_index_6.ibd
112 -rw-r----- 1 mysql mysql 114688 Mar 6 21:06 fts_0000000000000431_being_deleted_cache.ibd
112 -rw-r----- 1 mysql mysql 114688 Mar 6 21:06 fts_0000000000000431_being_deleted.ibd
112 -rw-r----- 1 mysql mysql 114688 Mar 6 21:06 fts_0000000000000431_config.ibd
112 -rw-r----- 1 mysql mysql 114688 Mar 6 21:06 fts_0000000000000431_deleted_cache.ibd
112 -rw-r----- 1 mysql mysql 114688 Mar 6 21:06 fts_0000000000000431_deleted.ibd
448 -rw-r----- 1 mysql mysql 458752 Mar 6 21:06 inventory.ibd
112 -rw-r----- 1 mysql mysql 114688 Mar 6 21:06 language.ibd
10240 -rw-r----- 1 mysql mysql 10485760 Mar 6 21:06 payment.ibd
10240 -rw-r----- 1 mysql mysql 10485760 Mar 6 21:06 rental.ibd
176 -rw-r----- 1 mysql mysql 180224 Mar 6 21:06 staff.ibd
144 -rw-r----- 1 mysql mysql 147456 Mar 6 21:06 store.ibd


数据库文件的分析，我们下一期再讲，超出篇幅了。

3、测试开始

如何对数据文件分类，可以查看官网。我们直接看演示

3.1、auto.cnf

[root@centos ~]# mkdir -p /mysql/backup
[root@centos ~]# mv /var/lib/mysql/auto.cnf /mysql/backup/
[root@centos ~]# systemctl start mysqld

[root@centos ~]# mysql sakila -e 'select * from actor limit 1'
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+


测试结果，数据库实例再次生成了auto.cnf，能成功启动数据库，并且不影响数据访问

3.2、binlog日志文件

这里是日志文件，不删binlog索引文件

[root@centos ~]# mv /var/lib/mysql/binlog.0* /mysql/backup/

[root@centos ~]# tail -100f /var/log/mysqld.log


错误日志信息

mysqld: File './binlog.000004' not found (OS errno 2 - No such file or directory)
2024-03-06T13:17:44.525110Z 0 [ERROR] [MY-010958] [Server] Could not open log file.
2024-03-06T13:17:44.525131Z 0 [ERROR] [MY-010041] [Server] Can't init tc log
2024-03-06T13:17:44.525153Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-03-06T13:17:46.028491Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.36) MySQL Community Server - GPL.
^C


如何解决呢？我这里是mv操作，你可以cp回去，然后改权限，就可以成功启动数据库了。

[root@centos ~]# cp /mysql/backup/binlog.00000* /var/lib/mysql/

[root@centos ~]# mysql sakila -e 'select * from actor limit 1'
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+


如果是删了binlog日志文件呢？接着看

3.3、binlog索引文件

[root@centos ~]# mv /var/lib/mysql/binlog.index /mysql/backup/

[root@centos ~]# systemctl start mysqld
[root@centos ~]# tail -100f /var/log/mysqld.log


日志无异常，数据库正常启动。但是binlog索引文件已经有了新的内容。

[root@centos ~]# cat /var/lib/mysql/binlog.index
./binlog.000006


思考题：可以把旧的日志文件信息写到这个文件吗？结果会怎么样？

3.4、*.pem文件

这里所有的*.pem文件一起移除

[root@centos ~]# mv /var/lib/mysql/*.pem /mysql/backup/

[root@centos ~]# ls -lsa /var/lib/mysql/*.pem
4 -rw------- 1 mysql mysql 1680 Mar 6 21:29 /var/lib/mysql/ca-key.pem
4 -rw-r--r-- 1 mysql mysql 1112 Mar 6 21:29 /var/lib/mysql/ca.pem
4 -rw-r--r-- 1 mysql mysql 1112 Mar 6 21:29 /var/lib/mysql/client-cert.pem
4 -rw------- 1 mysql mysql 1676 Mar 6 21:29 /var/lib/mysql/client-key.pem
4 -rw------- 1 mysql mysql 1676 Mar 6 21:29 /var/lib/mysql/private_key.pem
4 -rw-r--r-- 1 mysql mysql 452 Mar 6 21:29 /var/lib/mysql/public_key.pem
4 -rw-r--r-- 1 mysql mysql 1112 Mar 6 21:29 /var/lib/mysql/server-cert.pem
4 -rw------- 1 mysql mysql 1676 Mar 6 21:29 /var/lib/mysql/server-key.pem


数据库正常启动，再次生成新的文件。

思考题：如何手动生成这些pem文件呢？

3.5、Doublewrite文件

[root@centos ~]# mv /var/lib/mysql/*.dblwr /mysql/backup/

[root@centos ~]# ls -lsa /var/lib/mysql/*.dblwr
192 -rw-r----- 1 mysql mysql 196608 Mar 6 22:56 /var/lib/mysql/#ib_16384_0.dblwr
16320 -rw-r----- 1 mysql mysql 8585216 Mar 6 22:56 /var/lib/mysql/#ib_16384_1.dblwr


数据库正常启动，再次生成新的文件。

3.6、redo文件

文件目录#innodb_redo，

[root@centos ~]# mv /var/lib/mysql/#innodb_redo /mysql/backup/


启动失败

[root@centos ~]# systemctl start mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.


检查日志

[root@centos ~]# tail -100f /var/log/mysqld.log
2024-03-06T14:59:05.084947Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-03-06T14:59:05.126613Z 1 [ERROR] [MY-013862] [InnoDB] Neither found #innodb_redo subdirectory, nor ib_logfile* files in ./
2024-03-06T14:59:05.126667Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2024-03-06T14:59:05.612305Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2024-03-06T14:59:05.612892Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2024-03-06T14:59:05.612956Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-03-06T14:59:05.615089Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.36) MySQL Community Server - GPL.


怎么解决呢？请看日志，我想你已经知道答案了。

4、未完待续

内容太多，下一篇继续。