MySQL特殊恢复实战--第一讲：参数文件丢失，如何救库

##

MySQL特殊恢复实战–第一讲：参数文件丢失，如何救库

参数文件对于数据库而言是非常重要的，这句话适用于Oracle/PostgreSQL/MySQL，参数文件的丢失，轻则导致无法启动数据库，重则导致数据丢失。而MySQL常规的备份方式是不会主动去备份参数文件的，这点RMAN确实有优势。回到MySQL，在没有备份参数文件的情况下，如何拯救MySQL呢？

前提：MySQL版本8.0.35，采用二进制安装，参数文件位置自定义，参数内容自定义项较多。加大难度。

1、场景复现

1.1、模拟参数文件my.cnf丢失

[root@mydb01 3306]# systemctl stop mysql
[root@mydb01 3306]# ll
total 12
drwxrwxr-x 9 mysql mysql 4096 Mar  1 20:14 data
-rwxr-xr-x 1 root  root  4451 Mar  1 20:14 my.cnf
[root@mydb01 3306]# rm -fr my.cnf
[root@mydb01 3306]# ll
total 4
drwxrwxr-x 9 mysql mysql 4096 Mar  1 20:14 data

直接删除参数文件

2、启动mysq数据库

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

3、查看可能的信息

3.1、status信息

[root@mydb01 3306]# systemctl status mysql.service
● mysql.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysql; bad; vendor preset: disabled)
   Active: failed (Result: exit-code) since Fri 2024-03-01 20:14:44 CST; 8s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 7931 ExecStop=/etc/rc.d/init.d/mysql stop (code=exited, status=0/SUCCESS)
  Process: 7972 ExecStart=/etc/rc.d/init.d/mysql start (code=exited, status=1/FAILURE)

Mar 01 20:14:43 mydb01 systemd[1]: Starting LSB: start and stop MySQL...
Mar 01 20:14:43 mydb01 mysql[7972]: Starting MySQL.Logging to '/mysql/data/3306/data/mydb01.err'.
Mar 01 20:14:44 mydb01 mysql[7972]: The server quit without updating PID file (/mysql/data/3306/mysql.pid).[FAILED]
Mar 01 20:14:44 mydb01 systemd[1]: mysql.service: control process exited, code=exited status=1
Mar 01 20:14:44 mydb01 systemd[1]: Failed to start LSB: start and stop MySQL.
Mar 01 20:14:44 mydb01 systemd[1]: Unit mysql.service entered failed state.
Mar 01 20:14:44 mydb01 systemd[1]: mysql.service failed.

乍一看是没有什么重要信息的，但是你可以找到错误日志的位置/mysql/data/3306/data/mydb01.err，以及pid的位置/mysql/data/3306/mysql.pid。

这些参数可重要，因为数据库启动的重要标识是有pid文件，所以pid文件重要。

也可以不重要，pid文件的位置和错误日志文件的位置在哪里，并没有那么重要。

当然我们把这两个信息可以记录下来。

3.2、journalctl -xe信息

status和journalctl -xe能拿到的信息基本一致

[root@mydb01 3306]# journalctl -xe
--

-- Unit mysql.service has finished starting up.
--

-- The start-up result is done.
Mar 01 20:14:21 mydb01 polkitd[594]: Unregistered Authentication Agent for unix-process:6465:36431 (system bus name :1.41, object path /org/freedesktop/PolicyKit1/AuthenticationAgent, lo
Mar 01 20:14:26 mydb01 polkitd[594]: Registered Authentication Agent for unix-process:7925:37196 (system bus name :1.42 [/usr/bin/pkttyagent --notify-fd 5 --fallback], object path /org/f
Mar 01 20:14:26 mydb01 systemd[1]: Stopping LSB: start and stop MySQL...
-- Subject: Unit mysql.service has begun shutting down
-- Defined-By: systemd

-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--

-- Unit mysql.service has begun shutting down.
Mar 01 20:14:28 mydb01 mysql[7931]: Shutting down MySQL..[  OK  ]
Mar 01 20:14:28 mydb01 systemd[1]: Stopped LSB: start and stop MySQL.
-- Subject: Unit mysql.service has finished shutting down
-- Defined-By: systemd

-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--

-- Unit mysql.service has finished shutting down.
Mar 01 20:14:28 mydb01 polkitd[594]: Unregistered Authentication Agent for unix-process:7925:37196 (system bus name :1.42, object path /org/freedesktop/PolicyKit1/AuthenticationAgent, lo
Mar 01 20:14:43 mydb01 polkitd[594]: Registered Authentication Agent for unix-process:7966:38879 (system bus name :1.43 [/usr/bin/pkttyagent --notify-fd 5 --fallback], object path /org/f
Mar 01 20:14:43 mydb01 systemd[1]: Starting LSB: start and stop MySQL...
-- Subject: Unit mysql.service has begun start-up
-- Defined-By: systemd

-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--

-- Unit mysql.service has begun starting up.
Mar 01 20:14:43 mydb01 mysql[7972]: Starting MySQL.Logging to '/mysql/data/3306/data/mydb01.err'.
Mar 01 20:14:44 mydb01 mysql[7972]: The server quit without updating PID file (/mysql/data/3306/mysql.pid).[FAILED]
Mar 01 20:14:44 mydb01 systemd[1]: mysql.service: control process exited, code=exited status=1
Mar 01 20:14:44 mydb01 systemd[1]: Failed to start LSB: start and stop MySQL.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd

-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--

-- Unit mysql.service has failed.
--

-- The result is failed.
Mar 01 20:14:44 mydb01 systemd[1]: Unit mysql.service entered failed state.
Mar 01 20:14:44 mydb01 systemd[1]: mysql.service failed.
Mar 01 20:14:44 mydb01 polkitd[594]: Unregistered Authentication Agent for unix-process:7966:38879 (system bus name :1.43, object path /org/freedesktop/PolicyKit1/AuthenticationAgent, lo

4、数据库日志分析

我们从错误日志中去分析，发现执行systemctl时并不会产生新的内容。WHY？理论上是有日志产生的，接着分析。

2024-03-01T12:14:20.132589Z 0 [System] [MY-010116] [Server] /mysql/app/mysql/bin/mysqld (mysqld 8.0.35) starting as process 7856
2024-03-01T12:14:20.143856Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-03-01T12:14:20.898130Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-03-01T12:14:21.121404Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-03-01T12:14:21.121500Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-03-01T12:14:21.151984Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2024-03-01T12:14:21.152317Z 0 [System] [MY-010931] [Server] /mysql/app/mysql/bin/mysqld: ready for connections. Version: '8.0.35'  socket: '/mysql/data/3306/mysql.sock'  port: 3306  MySQL Community Server - GPL.
2024-03-01T12:14:26.476049Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.35).
2024-03-01T12:14:27.693166Z 0 [System] [MY-010910] [Server] /mysql/app/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.35)  MySQL Community Server - GPL.

5、参数文件知识学习

这里需要了解mysql读取参数文件位置，直接从mysqld --verbose --help中就可以看到

[root@mydb01 data]# mysqld --verbose --help
mysqld  Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL)
BuildID[sha1]=6d2f8b8d1160e6ff611567f97b9c5eba916143aa
Copyright (c) 2000, 2023, 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.

Starts the MySQL database server.

Usage: mysqld [OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
The following groups are read: mysql_cluster mysqld server mysqld-8.0
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file,
                        except for login file.
--defaults-file=#       Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-group-suffix=#
                        Also read groups with concat(group, suffix)
--login-path=#          Read this path from the login file.

6、数据文件位置查询

需要知道mysql有哪些文件，在什么位置，比如basedir/datadir/binlog/relaylog等等。

这里举例如何查找mysql的datadir查找方法有很多，比如找auto.cnf文件

[root@mydb01 data]# find / -name auto.cnf
/mysql/data/3306/data/auto.cnf

其他文件可以按照这个思路去找。

7、特殊文件大小

在数据库中有些参数是会影响文件的大小，比如redo log 的大小，当然还有其他文件，这部分文件如何去处理呢？

这里先埋个伏笔，第二期再解析。

8、重组my.cnf文件内容

在上面的信息拿到之后，就可以重建参数文件了

比如你需要在参数文件中添加的内容

[mysqld]
basedir=/mysql/app/mysql
datadir=/mysql/data/3306/data
log_bin=/mysql/log/3306/binlog/mydb-binlog
log_bin_index=/mysql/log/3306/binlog/mydb-binlog.index

...

...

socket=/mysql/data/3306/mysql.sock

9、救库

启动数据库，

mysqld_safe --defaults-file=/mysql/data/3306/my.cnf

然后导出数据,导出方法有很多，不赘述了。

10、总结

参数文件丢失是不是很简单就可以救库，没有备份也可以完成，生产中也许有千分之一的概率丢失参数文件。希望你也能成功救库。

最后的废话，备份参数文件同样重要，也有留意mysqld-auto.cnf也同样重要，使用mysql 8，那么你一定知道这个文件。

Bye。