设置
- 服务器计算机
- 发行版：Raspbian GNU/Linux 8
- 内核：4.9.35-v7+armv7l（32位）
我的 MySQL 数据库未加载
admin@Xroklaus:~ $ sudo systemctl status mysql
● mysql.service - LSB: Start and stop the mysql database server daemon
Loaded: loaded (/etc/init.d/mysql)
Active: failed (Result: exit-code) since Thu 2018-01-11 01:09:32 UTC; 6h ago
Process: 914 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)
Jan 11 01:08:48 Xroklaus systemd[1]: Starting LSB: Start and stop the mysql database server daemon...
Jan 11 01:09:32 Xroklaus mysql[914]: Starting MariaDB database server: mysqld . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . failed!
Jan 11 01:09:32 Xroklaus systemd[1]: mysql.service: control process exited, code=exited status=1
Jan 11 01:09:32 Xroklaus systemd[1]: Failed to start LSB: Start and stop the mysql database server daemon.
Jan 11 01:09:32 Xroklaus systemd[1]: Unit mysql.service entered failed state.
错误日志
admin@Xroklaus:~ $ sudo cat /var/lib/mysql/Xroklaus.err
180111 1:04:39 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
180111 1:04:39 [Note] Server socket created on IP: '::'.
180111 1:04:39 [ERROR] Fatal error: Can't open and lock privilege tables: Incorrect file format 'host'
180111 01:04:39 mysqld_safe mysqld from pid file /var/lib/mysql/Xroklaus.pid ended
180111 01:08:53 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
180111 1:08:53 [Note] /usr/sbin/mysqld (mysqld 10.0.32-MariaDB-0+deb8u1) starting as process 1347 ...
180111 1:08:54 [Note] InnoDB: innodb_empty_free_list_algorithm has been changed to legacy because of small buffer pool size. In order to use backoff, increase buffer pool at least up to 20MB.
180111 1:08:54 [Note] InnoDB: Using mutexes to ref count buffer pool pages
180111 1:08:54 [Note] InnoDB: The InnoDB memory heap is disabled
180111 1:08:54 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
180111 1:08:54 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
180111 1:08:54 [Note] InnoDB: Compressed tables use zlib 1.2.8
180111 1:08:54 [Note] InnoDB: Using Linux native AIO
180111 1:08:54 [Note] InnoDB: Not using CPU crc32 instructions
180111 1:08:54 [Note] InnoDB: Initializing buffer pool, size = 128.0M
180111 1:08:54 [Note] InnoDB: Completed initialization of buffer pool
180111 1:08:54 [Note] InnoDB: Highest supported file format is Barracuda.
180111 1:08:54 [Note] InnoDB: The log sequence numbers 1616887 and 1616887 in ibdata files do not match the log sequence number 1616913 in the ib_logfiles!
180111 1:08:54 [Note] InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
180111 1:08:55 [Note] InnoDB: 128 rollback segment(s) are active.
180111 1:08:55 [Note] InnoDB: Waiting for purge to start
180111 1:08:55 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.36-82.1 started; log sequence number 1616913
180111 1:08:55 [Note] Plugin 'FEEDBACK' is disabled.
180111 1:08:55 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
180111 1:08:55 [Note] Server socket created on IP: '::'.
180111 1:08:55 [ERROR] Fatal error: Can't open and lock privilege tables: Incorrect file format 'host'
180111 01:08:55 mysqld_safe mysqld from pid file /var/lib/mysql/Xroklaus.pid ended
看起来好像缺少一个文件。
admin@Xroklaus:~ $ sudo mysql_upgrade
Version check failed. Got the following error when calling the 'mysql' command line client
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")
FATAL ERROR: Upgrade failed
[更新]
我可以使用 运行 daeman mysqld_safe --skip-grant-tables，但升级失败：
admin@Xroklaus:~ $ sudo mysql_upgrade
Phase 1/6: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats OK
mysql.columns_priv
Error : Incorrect file format 'columns_priv'
error : Corrupt
mysql.db
Error : Incorrect file format 'db'
error : Corrupt
mysql.event
Error : Incorrect file format 'event'
error : Corrupt
mysql.func
Error : Incorrect file format 'func'
error : Corrupt
mysql.gtid_slave_pos OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.index_stats OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.plugin
Error : Incorrect file format 'plugin'
error : Corrupt
mysql.proc OK
mysql.procs_priv
Error : Incorrect file format 'procs_priv'
error : Corrupt
mysql.proxies_priv OK
mysql.roles_mapping OK
mysql.servers OK
mysql.table_stats OK
mysql.tables_priv
Error : Incorrect file format 'tables_priv'
error : Corrupt
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user
Error : Incorrect file format 'user'
error : Corrupt
Repairing tables
mysql.columns_priv
Error : Incorrect file format 'columns_priv'
error : Corrupt
mysql.db
Error : Incorrect file format 'db'
error : Corrupt
mysql.event
Error : Incorrect file format 'event'
error : Corrupt
mysql.func
Error : Incorrect file format 'func'
error : Corrupt
mysql.plugin
Error : Incorrect file format 'plugin'
error : Corrupt
mysql.procs_priv
Error : Incorrect file format 'procs_priv'
error : Corrupt
mysql.tables_priv
Error : Incorrect file format 'tables_priv'
error : Corrupt
mysql.user
Error : Incorrect file format 'user'
error : Corrupt
Phase 2/6: Fixing views
Phase 3/6: Running 'mysql_fix_privilege_tables'
ERROR 130 (HY000) at line 141: Incorrect file format 'user'
ERROR 130 (HY000) at line 143: Incorrect file format 'user'
ERROR 130 (HY000) at line 144: Incorrect file format 'user'
ERROR 130 (HY000) at line 146: Incorrect file format 'db'
ERROR 130 (HY000) at line 147: Incorrect file format 'user'
ERROR 130 (HY000) at line 148: Incorrect file format 'db'
ERROR 130 (HY000) at line 150: Incorrect file format 'user'
ERROR 130 (HY000) at line 155: Incorrect file format 'user'
ERROR 130 (HY000) at line 156: Incorrect file format 'tables_priv'
ERROR 130 (HY000) at line 158: Incorrect file format 'tables_priv'
ERROR 130 (HY000) at line 166: Incorrect file format 'tables_priv'
ERROR 130 (HY000) at line 174: Incorrect file format 'columns_priv'
ERROR 130 (HY000) at line 177: Incorrect file format 'columns_priv'
ERROR 130 (HY000) at line 186: Incorrect file format 'columns_priv'
ERROR 130 (HY000) at line 189: Incorrect file format 'func'
ERROR 130 (HY000) at line 191: Incorrect file format 'user'
ERROR 130 (HY000) at line 192: Incorrect file format 'user'
ERROR 130 (HY000) at line 200: Incorrect file format 'user'
ERROR 130 (HY000) at line 201: Incorrect file format 'user'
ERROR 130 (HY000) at line 205: Incorrect file format 'db'
ERROR 130 (HY000) at line 211: Incorrect file format 'user'
ERROR 130 (HY000) at line 212: Incorrect file format 'db'
ERROR 130 (HY000) at line 214: Incorrect file format 'user'
ERROR 130 (HY000) at line 215: Incorrect file format 'func'
ERROR 130 (HY000) at line 216: Incorrect file format 'user'
ERROR 130 (HY000) at line 220: Incorrect file format 'user'
ERROR 130 (HY000) at line 244: Incorrect file format 'db'
ERROR 130 (HY000) at line 249: Incorrect file format 'db'
ERROR 130 (HY000) at line 279: Incorrect file format 'func'
ERROR 130 (HY000) at line 281: Incorrect file format 'func'
ERROR 130 (HY000) at line 312: Incorrect file format 'plugin'
ERROR 130 (HY000) at line 317: Incorrect file format 'user'
ERROR 130 (HY000) at line 318: Incorrect file format 'db'
ERROR 130 (HY000) at line 319: Incorrect file format 'db'
ERROR 130 (HY000) at line 322: Incorrect file format 'user'
ERROR 130 (HY000) at line 323: Incorrect file format 'user'
ERROR 130 (HY000) at line 324: Incorrect file format 'db'
ERROR 130 (HY000) at line 325: Incorrect file format 'db'
ERROR 130 (HY000) at line 328: Incorrect file format 'user'
ERROR 130 (HY000) at line 329: Incorrect file format 'user'
ERROR 130 (HY000) at line 330: Incorrect file format 'user'
ERROR 130 (HY000) at line 332: Incorrect file format 'user'
ERROR 130 (HY000) at line 333: Incorrect file format 'db'
ERROR 130 (HY000) at line 334: Incorrect file format 'db'
ERROR 130 (HY000) at line 337: Incorrect file format 'user'
ERROR 130 (HY000) at line 338: Incorrect file format 'user'
ERROR 130 (HY000) at line 339: Incorrect file format 'db'
ERROR 130 (HY000) at line 340: Incorrect file format 'db'
ERROR 130 (HY000) at line 343: Incorrect file format 'user'
ERROR 130 (HY000) at line 344: Incorrect file format 'user'
ERROR 130 (HY000) at line 345: Incorrect file format 'db'
ERROR 130 (HY000) at line 346: Incorrect file format 'db'
ERROR 130 (HY000) at line 349: Incorrect file format 'user'
ERROR 130 (HY000) at line 350: Incorrect file format 'db'
ERROR 130 (HY000) at line 352: Incorrect file format 'user'
ERROR 130 (HY000) at line 353: Incorrect file format 'user'
ERROR 130 (HY000) at line 355: Incorrect file format 'user'
ERROR 130 (HY000) at line 356: Incorrect file format 'user'
ERROR 130 (HY000) at line 357: Incorrect file format 'user'
ERROR 130 (HY000) at line 358: Incorrect file format 'user'
ERROR 130 (HY000) at line 361: Incorrect file format 'procs_priv'
ERROR 130 (HY000) at line 364: Incorrect file format 'procs_priv'
ERROR 130 (HY000) at line 367: Incorrect file format 'procs_priv'
ERROR 130 (HY000) at line 370: Incorrect file format 'procs_priv'
ERROR 130 (HY000) at line 373: Incorrect file format 'procs_priv'
ERROR 130 (HY000) at line 472: Incorrect file format 'user'
ERROR 130 (HY000) at line 473: Incorrect file format 'user'
ERROR 130 (HY000) at line 474: Incorrect file format 'user'
ERROR 130 (HY000) at line 475: Incorrect file format 'user'
ERROR 130 (HY000) at line 476: Incorrect file format 'db'
ERROR 130 (HY000) at line 477: Incorrect file format 'db'
ERROR 130 (HY000) at line 478: Incorrect file format 'event'
ERROR 130 (HY000) at line 479: Incorrect file format 'event'
ERROR 130 (HY000) at line 480: Incorrect file format 'event'
ERROR 130 (HY000) at line 481: Incorrect file format 'event'
ERROR 130 (HY000) at line 515: Incorrect file format 'event'
ERROR 130 (HY000) at line 516: Incorrect file format 'event'
ERROR 130 (HY000) at line 517: Incorrect file format 'event'
ERROR 130 (HY000) at line 518: Incorrect file format 'event'
ERROR 130 (HY000) at line 519: Incorrect file format 'event'
ERROR 130 (HY000) at line 521: Incorrect file format 'event'
ERROR 130 (HY000) at line 524: Incorrect file format 'event'
ERROR 130 (HY000) at line 526: Incorrect file format 'event'
ERROR 130 (HY000) at line 529: Incorrect file format 'event'
ERROR 130 (HY000) at line 531: Incorrect file format 'event'
ERROR 130 (HY000) at line 534: Incorrect file format 'event'
ERROR 130 (HY000) at line 536: Incorrect file format 'event'
ERROR 130 (HY000) at line 538: Incorrect file format 'event'
ERROR 130 (HY000) at line 539: Incorrect file format 'db'
ERROR 130 (HY000) at line 541: Incorrect file format 'user'
ERROR 130 (HY000) at line 542: Incorrect file format 'user'
ERROR 130 (HY000) at line 543: Incorrect file format 'user'
ERROR 130 (HY000) at line 546: Incorrect file format 'db'
ERROR 130 (HY000) at line 547: Incorrect file format 'db'
ERROR 130 (HY000) at line 548: Incorrect file format 'user'
ERROR 130 (HY000) at line 550: Incorrect file format 'user'
ERROR 130 (HY000) at line 551: Incorrect file format 'user'
ERROR 130 (HY000) at line 552: Incorrect file format 'user'
ERROR 130 (HY000) at line 553: Incorrect file format 'user'
ERROR 130 (HY000) at line 554: Incorrect file format 'user'
ERROR 130 (HY000) at line 555: Incorrect file format 'user'
ERROR 130 (HY000) at line 556: Incorrect file format 'user'
ERROR 130 (HY000) at line 557: Incorrect file format 'user'
ERROR 130 (HY000) at line 558: Incorrect file format 'user'
ERROR 130 (HY000) at line 559: Incorrect file format 'user'
ERROR 1436 (HY000) at line 574: Thread stack overrun: 4864 bytes used of a 131072 byte stack, and 0 bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack.
ERROR 130 (HY000) at line 577: Incorrect file format 'user'
ERROR 130 (HY000) at line 597: Incorrect file format 'user'
ERROR 130 (HY000) at line 598: Incorrect file format 'db'
ERROR 130 (HY000) at line 599: Incorrect file format 'tables_priv'
ERROR 130 (HY000) at line 600: Incorrect file format 'columns_priv'
ERROR 130 (HY000) at line 601: Incorrect file format 'procs_priv'
ERROR 130 (HY000) at line 603: Incorrect file format 'event'
ERROR 130 (HY000) at line 608: Incorrect file format 'procs_priv'
ERROR 130 (HY000) at line 609: Incorrect file format 'tables_priv'
ERROR 130 (HY000) at line 625: Incorrect file format 'event'
ERROR 1050 (42S01) at line 644: Table 'cond_instances' already exists
ERROR 1050 (42S01) at line 669: Table 'events_waits_current' already exists
ERROR 1050 (42S01) at line 694: Table 'events_waits_history' already exists
ERROR 1050 (42S01) at line 719: Table 'events_waits_history_long' already exists
ERROR 1050 (42S01) at line 732: Table 'events_waits_summary_by_instance' already exists
ERROR 1050 (42S01) at line 745: Table 'events_waits_summary_by_host_by_event_name' already exists
ERROR 1050 (42S01) at line 758: Table 'events_waits_summary_by_user_by_event_name' already exists
ERROR 1050 (42S01) at line 772: Table 'events_waits_summary_by_account_by_event_name' already exists
ERROR 1050 (42S01) at line 785: Table 'events_waits_summary_by_thread_by_event_name' already exists
ERROR 1050 (42S01) at line 797: Table 'events_waits_summary_global_by_event_name' already exists
ERROR 1050 (42S01) at line 806: Table 'file_instances' already exists
ERROR 1050 (42S01) at line 835: Table 'file_summary_by_event_name' already exists
ERROR 1050 (42S01) at line 866: Table 'file_summary_by_instance' already exists
ERROR 1050 (42S01) at line 879: Table 'socket_instances' already exists
ERROR 1050 (42S01) at line 909: Table 'socket_summary_by_instance' already exists
ERROR 1050 (42S01) at line 938: Table 'socket_summary_by_event_name' already exists
ERROR 1050 (42S01) at line 973: Table 'host_cache' already exists
ERROR 1050 (42S01) at line 982: Table 'mutex_instances' already exists
ERROR 1050 (42S01) at line 996: Table 'objects_summary_global_by_type' already exists
ERROR 1050 (42S01) at line 1006: Table 'performance_timers' already exists
ERROR 1050 (42S01) at line 1016: Table 'rwlock_instances' already exists
ERROR 1050 (42S01) at line 1025: Table 'setup_actors' already exists
ERROR 1050 (42S01) at line 1033: Table 'setup_consumers' already exists
ERROR 1050 (42S01) at line 1042: Table 'setup_instruments' already exists
ERROR 1050 (42S01) at line 1053: Table 'setup_objects' already exists
ERROR 1050 (42S01) at line 1061: Table 'setup_timers' already exists
ERROR 1050 (42S01) at line 1106: Table 'table_io_waits_summary_by_index_usage' already exists
ERROR 1050 (42S01) at line 1150: Table 'table_io_waits_summary_by_table' already exists
ERROR 1050 (42S01) at line 1229: Table 'table_lock_waits_summary_by_table' already exists
ERROR 1050 (42S01) at line 1249: Table 'threads' already exists
ERROR 1050 (42S01) at line 1265: Table 'events_stages_current' already exists
ERROR 1050 (42S01) at line 1281: Table 'events_stages_history' already exists
ERROR 1050 (42S01) at line 1297: Table 'events_stages_history_long' already exists
ERROR 1050 (42S01) at line 1310: Table 'events_stages_summary_by_thread_by_event_name' already exists
ERROR 1050 (42S01) at line 1323: Table 'events_stages_summary_by_host_by_event_name' already exists
ERROR 1050 (42S01) at line 1336: Table 'events_stages_summary_by_user_by_event_name' already exists
ERROR 1050 (42S01) at line 1350: Table 'events_stages_summary_by_account_by_event_name' already exists
ERROR 1050 (42S01) at line 1362: Table 'events_stages_summary_global_by_event_name' already exists
ERROR 1050 (42S01) at line 1408: Table 'events_statements_current' already exists
ERROR 1050 (42S01) at line 1454: Table 'events_statements_history' already exists
ERROR 1050 (42S01) at line 1500: Table 'events_statements_history_long' already exists
ERROR 1050 (42S01) at line 1532: Table 'events_statements_summary_by_thread_by_event_name' already exists
ERROR 1050 (42S01) at line 1564: Table 'events_statements_summary_by_host_by_event_name' already exists
ERROR 1050 (42S01) at line 1596: Table 'events_statements_summary_by_user_by_event_name' already exists
ERROR 1050 (42S01) at line 1629: Table 'events_statements_summary_by_account_by_event_name' already exists
ERROR 1050 (42S01) at line 1660: Table 'events_statements_summary_global_by_event_name' already exists
ERROR 1050 (42S01) at line 1669: Table 'hosts' already exists
ERROR 1050 (42S01) at line 1678: Table 'users' already exists
ERROR 1050 (42S01) at line 1688: Table 'accounts' already exists
ERROR 1050 (42S01) at line 1723: Table 'events_statements_summary_by_digest' already exists
ERROR 1050 (42S01) at line 1733: Table 'session_connect_attrs' already exists
ERROR 1050 (42S01) at line 1739: Table 'session_account_connect_attrs' already exists
FATAL ERROR: Upgrade failed
[更新]
通过删除所有表来修复。
答案1
您的 MySQL 数据库已损坏。
首先备份你的/var/lib/mysql。
然后做：
sudo mysqld --skip-grant-tables &
或者对于 Debian
mysqld_safe --skip-grant-tables &
运行时，执行以下操作：
$ mysql
mysql> use mysql
mysql> repair table host use_frm;
mysql> exit
立即停止mysqld并重新启动服务：
$ sudo service mysql restart
现在应该可以解决这个问题。请注意，从 SD 卡运行 MySQL 数据库时很容易出现这些小问题。众所周知，树莓派在关闭时会出现零星的 SD 损坏问题。
在 SD 卡中使用密集的数据库操作也会更快地缩短其使用寿命。
考虑将操作系统或至少 MySQL DB 移动到外部磁盘或 USB 笔。
如果您最近升级了 MySQL 版本，并且/或仍然遇到 MySQL 错误，请立即运行升级操作：
sudo mysql_upgrade
此操作修复了不同 MySQL 版本之间变化的表和内部数据表示。
附言。可能会有更多的表被损坏，数据库的完整性也会受到损害。如果数据不重要，请考虑恢复最近的 MySQL 数据库备份转储或从头开始创建它。