MySQL 5.7 社区版调整磁盘分区大小后崩溃

Question

我的基于 MySQL Community 版本的数据库存在严重问题，该数据库崩溃并且在调整数据库分区大小后无法启动备份。该数据库位于 RHEL7.2 Linux lvm 分区上，由于一些存储问题，该分区必须减少。

在调整大小期间，出现了 inode 问题，导致数据库文件和文件夹被移动到丢失+找到的位置，我们恢复了文件并将它们移动到正确的位置。重新启动 mysql 服务会导致以下错误:

    2017-06-12T09:38:12.510405Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-06-12T09:38:12.510568Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2017-06-12T09:38:12.510628Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.13) starting as process 40601 ...
2017-06-12T09:38:12.515354Z 0 [Note] InnoDB: PUNCH HOLE support available
2017-06-12T09:38:12.515396Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-06-12T09:38:12.515404Z 0 [Note] InnoDB: Uses event mutexes
2017-06-12T09:38:12.515413Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2017-06-12T09:38:12.515421Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-06-12T09:38:12.515428Z 0 [Note] InnoDB: Using Linux native AIO
2017-06-12T09:38:12.515779Z 0 [Note] InnoDB: Number of pools: 1
2017-06-12T09:38:12.515943Z 0 [Note] InnoDB: Using CPU crc32 instructions
2017-06-12T09:38:12.518004Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-06-12T09:38:12.527531Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-06-12T09:38:12.529516Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2017-06-12T09:38:12.541493Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-06-12T09:38:12.542896Z 0 [Note] InnoDB: The log sequence number 8204 in the system tablespace does not match the log sequence number 435564720566 in the ib_logfiles!
2017-06-12T09:38:12.542919Z 0 [Note] InnoDB: Database was not shutdown normally!
2017-06-12T09:38:12.542926Z 0 [Note] InnoDB: Starting crash recovery.
2017-06-12 10:38:12 0x7f4e11608740  InnoDB: Assertion failure in thread 139973275715392 in file fut0lst.ic line 85
InnoDB: Failing assertion: addr.page == FIL_NULL || addr.boffset >= FIL_PAGE_DATA
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
09:38:12 UTC - mysqld got signal 6 ;
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=8388608
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 = 68189 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
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 = 0 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xef0cfb]
/usr/sbin/mysqld(handle_fatal_signal+0x461)[0x7af361]
/lib64/libpthread.so.0(+0xf100)[0x7f4e111ef100]
/lib64/libc.so.6(gsignal+0x37)[0x7f4e0fbe25f7]
/lib64/libc.so.6(abort+0x148)[0x7f4e0fbe3ce8]
/usr/sbin/mysqld[0x77f71c]
/usr/sbin/mysqld(_Z22trx_undo_free_preparedP5trx_t+0x0)[0x77f4d4]
/usr/sbin/mysqld(_Z19trx_undo_lists_initP10trx_rseg_t+0xdcc)[0x10bcacc]
/usr/sbin/mysqld[0x10a11ec]
/usr/sbin/mysqld[0x10a39bc]
/usr/sbin/mysqld(_Z24trx_sys_init_at_db_startv+0x1883)[0x10aaa43]
/usr/sbin/mysqld(_Z34innobase_start_or_create_for_mysqlv+0x4830)[0x10721a0]
/usr/sbin/mysqld[0xf39451]
/usr/sbin/mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x51)[0x7fa2b1]
/usr/sbin/mysqld[0xce3925]
/usr/sbin/mysqld(_Z11plugin_initPiPPci+0x610)[0xcea830]
/usr/sbin/mysqld[0x7a82bd]
/usr/sbin/mysqld(_Z11mysqld_mainiPPc+0x842)[0x7a97e2]
/lib64/libc.so.6(__libc_start_main+0xf5)[0x7f4e0fbceb15]
/usr/sbin/mysqld[0x79f5e5]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

还有:

2017-06-12T09:38:12.542919Z 0 [Note] InnoDB: Database was not shutdown normally!
2017-06-12T09:38:12.542926Z 0 [Note] InnoDB: Starting crash recovery.
2017-06-12 10:38:12 0x7f4e11608740  InnoDB: Assertion failure in thread 139973275715392 in file fut0lst.ic line 85
InnoDB: Failing assertion: addr.page == FIL_NULL || addr.boffset >= FIL_PAGE_DATA

我尝试了多种innodb_force_recovery模式，直到我使用innodb_force_recovery = 6，mysqld才启动。当mysqld启动时，由于多个错误，我无法备份数据库，如下所示:

2017-06-12T11:10:52.217568Z 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`plugin` in the cache. Attempting to load the tablespace with space id 2
2017-06-12T11:10:52.277914Z 0 [ERROR] Function 'validate_password' already exists
2017-06-12T11:10:52.277954Z 0 [Warning] Couldn't load plugin named 'validate_password' with soname 'validate_password.so'.
2017-06-12T11:10:52.284445Z 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`gtid_executed` in the cache. Attempting to load the tablespace with space id 18
2017-06-12T11:10:52.293339Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2017-06-12T11:10:52.310829Z 0 [Warning] CA certificate ca.pem is self signed.
2017-06-12T11:10:52.313911Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2017-06-12T11:10:52.314120Z 0 [Note] IPv6 is available.
2017-06-12T11:10:52.314139Z 0 [Note]   - '::' resolves to '::';
2017-06-12T11:10:52.314179Z 0 [Note] Server socket created on IP: '::'.
2017-06-12T11:10:52.433825Z 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`server_cost` in the cache. Attempting to load the tablespace with space id 19
2017-06-12T11:10:52.449122Z 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`engine_cost` in the cache. Attempting to load the tablespace with space id 20
2017-06-12T11:10:52.476635Z 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`time_zone_leap_second` in the cache. Attempting to load the tablespace with space id 12
2017-06-12T11:10:52.477431Z 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`time_zone_name` in the cache. Attempting to load the tablespace with space id 8
2017-06-12T11:10:52.490830Z 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`time_zone` in the cache. Attempting to load the tablespace with space id 9
2017-06-12T11:10:52.507804Z 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`time_zone_transition_type` in the cache. Attempting to load the tablespace with space id 11
2017-06-12T11:10:52.508725Z 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`time_zone_transition` in the cache. Attempting to load the tablespace with space id 10
2017-06-12T11:10:52.513876Z 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`servers` in the cache. Attempting to load the tablespace with space id 3
2017-06-12T11:10:52.563565Z 0 [Note] Event Scheduler: Loaded 0 events
2017-06-12T11:10:52.563876Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.13'  socket: '/dataspace/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

当我尝试转储数据库文件时，我看到了这个:

mysqldump -uroot -pXXXXX --skip-lock-tables --skip-extended-insert --hex-blob office > office_dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Error 2013: Lost connection to `MySQL` server during query when dumping table `audit_log` at row: 334202

请问我该怎么做才能恢复数据并使数据库重新联机？

如有任何帮助，我们将不胜感激。

Answer 1

我做了什么让 MySQL 再次运行:

1. 备份了损坏的 $MYSQL_HOME 目录。
2. 已添加skip-grant-tables允许我以 root 身份登录 mysql，无需密码。
3. 尝试过不同的 innodb_force_recovery/etc/my.cnf 中的模式，直到允许 mysqld 启动。 (就我而言，它是 innodb_force_recovery = 5 )。

4. 尝试转储所有数据库但失败:

   mysqldump -uroot -pXXXXX --skip-lock-tables --skip-extended-insert --hex-blob databasename > databasename_dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table审核日志 at row: 334202

5. 我得到了一些 Percona 支持，他们建议我循环遍历 $MYSQL_HOME 中每个数据库目录中的 *.frm 文件，并为每个文件创建单独的 mysqldump 命令。 (在此之前不知道 frm 文件代表数据库内的各个数据库表)。他们还建议我 sleep在每个命令之后避免 Lost connection问题。我写了一个这样的脚本: for table in $(ls -1 *.frm); do table_name=$(echo $table | cut -d '.' -f 1); echo mysqldump --host=127.0.0.1 --user=root --skip-lock-tables databasename ${table_name} \> database.${table_name}.sql; echo sleep 5 >> commands.txt; done它生成了我可以用来转储每个数据库表的所有 mysqldump 命令。

6. 我循环访问了创建的commands.txt 文件以转储所有仍然可以工作的表。( sh -x commands.txt )

7. 我启动了一个新的 mysql 实例，创建了丢失的数据库，并将转储的所有 .mysql 文件导入到新数据库中。 for each in $(ls -1 $MYSQL_HOME/recoveries/*); do mysql -uroot -pXXXX databasename < $each; sleep 5; done

8. 确保数据库正在运行并修复了与缺失表相关的其他问题(mysqlfrm --diagnostic 在类似服务器上有所帮助)

9. 一切完成后，服务器重新上线，尽管几个月的关键数据丢失了。