MySQL/Maria Database not loading: /var/run/mysqld/mysqld.sock (2 "No such file or directory")
Your MySQL DB is corrupted.
Backup your /var/lib/mysql first.
Then do:
sudo mysqld --skip-grant-tables &
or for Debian
mysqld_safe --skip-grant-tables &
While running it, do:
$ mysql
mysql> use mysql
mysql> repair table host use_frm;
mysql> exit
Stop mysqld
now and restart the service:
$ sudo service mysql restart
That should fix it for now. Be aware you are prone to these little problems running a MySQL DB from an SD card. The raspberry is also known for having sporadic SD corruption problems when turning it off.
Using intensive database operations in an SD card with also shorten it´s useful life faster.
Consider moving the OS or at least the MySQL DB to an external disk or an USB pen.
If you have upgraded MySQL versions recently, and/or still got MySQL errors, run now the upgrade operation:
sudo mysql_upgrade
This operation fixes tables and internal data representation that change between different MySQL versions.
PS. It is possible that more tables are corrupted and the DB integrity is compromised for good. Consider restoring a recent MySQL DB backup dump or creating it over from scratch if the data is not important.
Related videos on Youtube
shomit
Updated on September 18, 2022Comments
-
shomit over 1 year
Setup
- Server computer
- Distro: Raspbian GNU/Linux 8
- Kernel: 4.9.35-v7+ armv7l (32 bit)
My MySQL database is not loading
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.
Error log
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
Looks like it's missing a file.
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
[update]
I'm able to run the daeman with
mysqld_safe --skip-grant-tables
, but the upgrade fails: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
[update]
Fixed by dropping all tables.
- Server computer
-
shomit over 6 yearsAnd I don't mind if I have to destroy the DB. The data is not that important. The application using it just needs to run.