0

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.
2
  • #innodb_force_recovery = 3 -- Did you have the # on it?? That says to ignore.
    – Rick James
    Oct 13, 2019 at 1:26
  • Copy-paste error. Fixed. Oct 13, 2019 at 3:06

1 Answer 1

0

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.

1
  • In case the link from 1) stops working one day: the important line seems to be something like mysqldump -u root -p --all-databases > /home/zzzzz/all_databases.sql
    – Scz
    Dec 5, 2020 at 17:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.