MySQL – Unable to Start on Ubuntu

MySQLUbuntu

I have an issue with re-starting my MySQL 8 service on Ubuntu 19 after a force shutdown in normal mode.

Originally I was able to re-start the service in the save mode (had to do 

innodb_force_recovery = 3  #in my cnf

However, switching this setting back to 0, in order to start up the db in normal mode, gives me the following error –

Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.

I tried doing 

mysqlcheck -uroot -p on_every_database

on every database, and all tables came back as OK .

I'm in the process of doing auto-repair on all db's, but thats 300gb total. Is there anything else I can try to fix the DB , without blowing everything away and restoring from mysql dumps?

p.s. stats on pc – 32gb ram total with 8gb for buffer pool size + 2tb ssd. so more than enough ram/space.

output for systemctl status mysql.service

Mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: en
   Active: failed (Result: exit-code) since Thu 2019-10-03 19:57:34 PDT; 3min 54
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 8810 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=
  Process: 8849 ExecStart=/usr/sbin/mysqld (code=exited, status=2)
 Main PID: 8849 (code=exited, status=2)
   Status: "Server startup in progress"

Oct 03 19:57:34 bob123 systemd[1]: mysql.service: Service RestartSec=100ms expir
Oct 03 19:57:34 bob123 systemd[1]: mysql.service: Scheduled restart job, restart
Oct 03 19:57:34 bob123 systemd[1]: Stopped MySQL Community Server.
Oct 03 19:57:34 bob123 systemd[1]: mysql.service: Start request repeated too qui
Oct 03 19:57:34 bob123 systemd[1]: mysql.service: Failed with result 'exit-code'
Oct 03 19:57:34 bob123 systemd[1]: Failed to start MySQL Community Server.

Best Answer

Trying mysql's built in table check/fix/ etc functions did not help my in restarting mysql server in regular mode.

I ended up: 1) doing mysqldump of all the databases, into one file. This article was immensely helpful

2) dropping 1 db at a time, and attempting to restart mysql server in regular mode, after each drop (nothing helped).

3) I ended up purging mysql from ubuntu all together, and re-installing it.

4) I did the sql dump restore doing the following command (took 3 hours for 200gb dump, on a fairly fast machine): 

mysql -u root -p  <  /home/zzzzz/all_databases.sql

Best of luck, and hope this doesn't happen to you - it was definitely a waste of 2 days of trial and error, with the nuclear option as my last restort.

Related Question