我遇到了一次断电，之后我的服务器就无法启动了。我最终运行了 fsck，之后使用 smartctl 检查了驱动器。整体健康测试通过了，除了 mysql 之外，一切似乎都正常。
我正在运行 Ubuntu 16.04。这里有一些日志，特别是 error.log 显示了一些“未找到表空间...”和“无法打开数据文件...”错误。/var/lib/mysql 中的数据库文件仍然存在。
我还尝试使用 /etc/mysql/my.cnf 中的所有 6 个值运行“innodb_force_recovery”，但没有结果。
systemctl 状态 mysql.service
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: activating (start-post) (Result: exit-code) since Di 2019-06-11 11:31:52 CEST; 15s ago
Process: 8110 ExecStart=/usr/sbin/mysqld (code=exited, status=2)
Process: 8101 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Main PID: 8110 (code=exited, status=2); : 8111 (mysql-systemd-s)
CGroup: /system.slice/mysql.service
└─control
├─8111 /bin/bash /usr/share/mysql/mysql-systemd-start post
└─8172 sleep 1
Jun 11 11:31:52 serber systemd[1]: Starting MySQL Community Server...
Jun 11 11:31:53 serber systemd[1]: mysql.service: Main process exited, code=exited, status=2/INVALIDARGUMENT
journalctl-xe
-- Unit mysql.service has begun starting up.
Jun 11 11:32:23 serber audit[8218]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/8218/status" pid=8218 comm="mys
Jun 11 11:32:23 serber audit[8218]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=8218 c
Jun 11 11:32:23 serber audit[8218]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/8218/status" pid=8218 comm="mys
Jun 11 11:32:23 serber kernel: audit: type=1400 audit(1560245543.341:7230): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/
Jun 11 11:32:23 serber kernel: audit: type=1400 audit(1560245543.341:7231): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/
Jun 11 11:32:23 serber kernel: audit: type=1400 audit(1560245543.341:7232): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/
Jun 11 11:32:23 serber audit[8243]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/8218/task/8243/mem" pid=8243 co
Jun 11 11:32:23 serber kernel: audit: type=1400 audit(1560245543.861:7233): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/
Jun 11 11:32:23 serber systemd[1]: mysql.service: Main process exited, code=exited, status=2/INVALIDARGUMENT
Jun 11 11:32:53 serber systemd[1]: Failed to start MySQL Community Server.
-- 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.
Jun 11 11:32:53 serber systemd[1]: mysql.service: Unit entered failed state.
Jun 11 11:32:53 serber systemd[1]: mysql.service: Failed with result 'exit-code'.
Jun 11 11:32:53 serber systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Jun 11 11:32:53 serber systemd[1]: Stopped MySQL Community Server.
-- 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.
Jun 11 11:32:53 serber systemd[1]: Starting MySQL Community Server...
-- Subject: Unit mysql.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
/var/log/mysql/error.log
2019-06-11T09:40:09.274147Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 55302581654
2019-06-11T09:40:09.274268Z 0 [Note] InnoDB: At LSN: 55302581435: unable to open file ./mysql/innodb_index_stats.ibd for tablespace 506
2019-06-11T09:40:09.274277Z 0 [Note] InnoDB: Database was not shutdown normally!
2019-06-11T09:40:09.274280Z 0 [Note] InnoDB: Starting crash recovery.
2019-06-11T09:40:09.274748Z 0 [Note] InnoDB: Tablespace 506 was not found at './mysql/innodb_index_stats.ibd', but there were no modificati ons either.
2019-06-11T09:40:09.441985Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2019-06-11T09:40:09.442009Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2019-06-11T09:40:09.442014Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2019-06-11T09:40:09.442019Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './mysql/help_category.ibd' OS error: 71
2019-06-11T09:40:09.442023Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2019-06-11T09:40:09.442026Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2019-06-11T09:40:09.442030Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2019-06-11T09:40:09.442042Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `mysql/help_category`. Please refer to http://dev. mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2019-06-11T09:40:09.442048Z 0 [Warning] InnoDB: Ignoring tablespace `mysql/help_category` because it could not be opened.
2019-06-11T09:40:09.442111Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2019-06-11T09:40:09.442116Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2019-06-11T09:40:09.442120Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2019-06-11T09:40:09.442123Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './mysql/innodb_index_stats.ibd' OS error: 71
2019-06-11T09:40:09.442126Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2019-06-11T09:40:09.442129Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2019-06-11T09:40:09.442132Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2019-06-11T09:40:09.442136Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `mysql/innodb_index_stats`. Please refer to http:/ /dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2019-06-11T09:40:09.442140Z 0 [Warning] InnoDB: Ignoring tablespace `mysql/innodb_index_stats` because it could not be opened.
2019-06-11T09:40:09.445987Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2019-06-11T09:40:09.445996Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-06-11T09:40:09.446026Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-06-11T09:40:09.634990Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2019-06-11T09:40:09.635646Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2019-06-11T09:40:09.635654Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2019-06-11T09:40:09.635970Z 0 [Note] InnoDB: Waiting for purge to start
2019-06-11T09:40:09.636594Z 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`innodb_index_stats` in the cache. Attempting to load the tablespace with space id 506
2019-06-11T09:40:09.636618Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2019-06-11T09:40:09.636625Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2019-06-11T09:40:09.636650Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './mysql/innodb_index_stats.ibd' OS error: 71
2019-06-11T09:40:09.636656Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2019-06-11T09:40:09.636659Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2019-06-11T09:40:09.636664Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `mysql/innodb_index_stats`. Please refer to http:/ /dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
09:40:09 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.
key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 76388 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x7f5aec000900
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f5af1ffaca0 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xe9510b]
/usr/sbin/mysqld(handle_fatal_signal+0x489)[0x78ad29]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x11390)[0x7f5b11cec390]
/usr/sbin/mysqld(_Z28fil_space_is_being_truncatedm+0xc0)[0x1122b80]
/usr/sbin/mysqld(_Z14row_purge_stepP9que_thr_t+0x286)[0xfd6026]
/usr/sbin/mysqld(_Z15que_run_threadsP9que_thr_t+0xa69)[0xf832f9]
/usr/sbin/mysqld(_Z9trx_purgemmb+0xab5)[0x1037b15]
/usr/sbin/mysqld(srv_purge_coordinator_thread+0x445)[0x100c9e5]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7f5b11ce26ba]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f5b1117741d]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): is an invalid pointer
Connection ID (thread ID): 0
Status: NOT_KILLED
答案1
我可以像这样修复该问题：（以下步骤描述了不可逆转地更改数据并可能导致数据丢失的活动。）
- 必要时停止 MySQL
- 备份 InnoDB 文件（/var/lib/mysql/ib*）
- 将这些行添加到 /etc/mysql/my.cnf
[mysqld]
innodb_force_recovery = 4 - 以安全模式启动 MySQL：
sudo /usr/bin/mysqld_safe --user=mysql --skip-grant-tables
- 备份所有数据库 (
mysqldump -A > dump.sql) 或单个数据库 (mysqldump database > database.sql) - 删除表 (
drop database mydatabase;) - 停止 MySQL
- 删除 InnoDB 文件 (/var/lib/mysql/ib*)
innodb_force_recovery=4从配置中删除- 像平常一样启动 MySQL (
sudo service mysql start)。现在生成了新的 ib* 文件。 恢复数据库
mysql < dump.sql
进行完整备份或
创建数据库 mydatabase；
mysql mydatabase < mydatabase.sql