mysql - MySQL突然崩溃

Question

MySQL突然崩溃了。PHP 显示错误： Warning: mysqli_connect(): (HY000/2002): No such file or directory

[root@support mysql]# mysql -v
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

• 检查存储细节都很好（df -h & df -i）
• 套接字文件似乎没问题（我停止了服务并删除了锁定文件）
• 错误日志： 2020-03-03T04:11:10.160439Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './zabbix_bk/widget_field.ibd' OS error: 71 2020-03-03T04:11:10.160451Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation. 2020-03-03T04:11:10.160463Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2020-03-03T04:11:10.160474Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2020-03-03T04:11:10.160487Z 0 [ERROR] InnoDB: Could not find a valid tablespace file forzabbix_bk/widget_field . Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue. 2020-03-03T04:11:10.160500Z 0 [Warning] InnoDB: Ignoring tablespacezabbix_bk/widget_fieldbecause it could not be opened. 2020-03-03T04:11:10.162306Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1" 2020-03-03T04:11:10.162331Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2020-03-03T04:11:10.162573Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2020-03-03T04:11:10.175636Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2020-03-03T04:11:10.176919Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active. 2020-03-03T04:11:10.176949Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active. 2020-03-03T04:11:10.178519Z 0 [Note] InnoDB: 5.7.23 started; log sequence number 9654599972 2020-03-03T04:11:10.179611Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool 2020-03-03T04:11:10.179951Z 0 [Note] Plugin 'FEDERATED' is disabled. 2020-03-03T04:11:10.181546Z 0 [Warning] InnoDB: Cannot open table mysql/plugin from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. mysqld: Table 'mysql.plugin' doesn't exist 2020-03-03T04:11:10.181662Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 2020-03-03T04:11:10.182789Z 0 [Warning] InnoDB: Cannot open table mysql/gtid_executed from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. mysqld: Table 'mysql.gtid_executed' doesn't exist 2020-03-03T04:11:10.182854Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2020-03-03T04:11:10.182909Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them. 2020-03-03T04:11:10.183401Z 0 [Warning] CA certificate ca.pem is self signed. 2020-03-03T04:11:10.187590Z 0 [Note] Server hostname (bind-address): '*'; port: 3306 2020-03-03T04:11:10.187676Z 0 [Note] IPv6 is available. 2020-03-03T04:11:10.187712Z 0 [Note] - '::' resolves to '::'; 2020-03-03T04:11:10.187778Z 0 [Note] Server socket created on IP: '::'. 2020-03-03T04:11:10.189608Z 0 [Note] InnoDB: Buffer pool(s) load completed at 200303 12:11:10 2020-03-03T04:11:10.189888Z 0 [Warning] InnoDB: Cannot open table mysql/server_cost from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. 2020-03-03T04:11:10.189931Z 0 [Warning] Failed to open optimizer cost constant tables 2020-03-03T04:11:10.191998Z 0 [Warning] InnoDB: Cannot open table mysql/time_zone_leap_second from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. 2020-03-03T04:11:10.192053Z 0 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them 2020-03-03T04:11:10.192946Z 0 [Warning] InnoDB: Cannot open table mysql/servers from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue. 2020-03-03T04:11:10.192980Z 0 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist 2020-03-03T04:11:10.203770Z 0 [Note] Event Scheduler: Loaded 1 event 2020-03-03T04:11:10.204582Z 0 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.7.23-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 2020-03-03 12:11:10 0x7efd135fe700 InnoDB: Assertion failure in thread 139625416877824 in file btr0cur.cc line 325 InnoDB: Failing assertion: btr_page_get_next( latch_leaves.blocks[0]->frame, mtr) == page_get_page_no(page) 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. 04:11:10 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 = 68195 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x7efd040008c0 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 = 7efd135fddd8 thread_stack 0x40000 /usr/sbin/mysqld(my_print_stacktrace+0x35)[0xf52f35] /usr/sbin/mysqld(handle_fatal_signal+0x4a4)[0x7d3754] /lib64/libpthread.so.0(+0xf7e0)[0x7efd759167e0] /lib64/libc.so.6(gsignal+0x35)[0x7efd743b64f5] /lib64/libc.so.6(abort+0x175)[0x7efd743b7cd5] /usr/sbin/mysqld(_Z18ut_print_timestampP8_IO_FILE+0x0)[0x7c28a8] /usr/sbin/mysqld(_Z20btr_cur_latch_leavesP11buf_block_tRK9page_id_tRK11page_size_tmP9btr_cur_tP5mtr_t+0x7ac)[0x11ba89c] /usr/sbin/mysqld(_Z27btr_cur_search_to_nth_levelP12dict_index_tmPK8dtuple_t15page_cur_mode_tmP9btr_cur_tmPKcmP5mtr_t+0x233b)[0x11c7adb] /usr/sbin/mysqld(_Z22row_search_index_entryP12dict_index_tPK8dtuple_tmP10btr_pcur_tP5mtr_t+0x13b)[0x1120aeb] /usr/sbin/mysqld[0x111dbd9] /usr/sbin/mysqld(_Z14row_purge_stepP9que_thr_t+0xa1d)[0x111fe9d] /usr/sbin/mysqld(_Z15que_run_threadsP9que_thr_t+0x9bf)[0x10d5a2f] /usr/sbin/mysqld(srv_worker_thread+0x100)[0x114faf0] /lib64/libpthread.so.0(+0x7aa1)[0x7efd7590eaa1] /lib64/libc.so.6(clone+0x6d)[0x7efd7446cc4d] 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 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. 2020-03-03T04:11:10.637453Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.23-log) starting as process 32491 ... 2020-03-03T04:11:10.644182Z 0 [Note] InnoDB: PUNCH HOLE support available 2020-03-03T04:11:10.644226Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2020-03-03T04:11:10.644239Z 0 [Note] InnoDB: Uses event mutexes 2020-03-03T04:11:10.644251Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier 2020-03-03T04:11:10.644263Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3 2020-03-03T04:11:10.644274Z 0 [Note] InnoDB: Using Linux native AIO 2020-03-03T04:11:10.644682Z 0 [Note] InnoDB: Number of pools: 1 2020-03-03T04:11:10.644842Z 0 [Note] InnoDB: Using CPU crc32 instructions 2020-03-03T04:11:10.647913Z 0 [Note] InnoDB: Initializing buffer pool, total size = 1G, instances = 8, chunk size = 128M 2020-03-03T04:11:10.748191Z 0 [Note] InnoDB: Completed initialization of buffer pool 2020-03-03T04:11:10.760371Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2020-03-03T04:11:10.765485Z 0 [Note] InnoDB: Highest supported file format is Barracuda. 2020-03-03T04:11:10.773068Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 9653793292 2020-03-03T04:11:10.903199Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 9654600030 2020-03-03T04:11:10.903438Z 0 [Note] InnoDB: Database was not shutdown normally! 2020-03-03T04:11:10.903453Z 0 [Note] InnoDB: Starting crash recovery. 2020-03-03T04:11:10.962458Z 0 [Note] InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percent: 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 </code>

其实“zabbix_bk”已经下架快半年了？？

即使我停止了服务，错误仍然记录到错误文件（mysqld.log）中？

操作系统：Centos 6 mySQL：5.7.23

谢谢。

标签： mysql