How to start MySQL after failure?

Question

After I reboot my server, I can no longer start mysql.

I have followed several solutions posted here:

• This
• This
• Or this Did not help much... :(

I run the following commands to start the service:

mysql
>> ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

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

systemctl status mysql.service
>> mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: failed (Result: exit-code) since Wed 2022-01-26 15:18:33 UTC; 47s ago
    Process: 2612 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=1/FAILURE)

 journalctl -xe
>> Startup of the manager took 379405 microseconds.
Jan 26 14:33:24 sumentserver polkit-agent-helper-1[2223]: pam_unix(polkit-1:auth): authentication failure; logname= uid=1001 euid=0 tty= >
-- Reboot --
Jan 26 14:44:33 sumentserver systemd[1685]: Reached target Paths.
-- Subject: A start job for unit UNIT has finished successfully
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
-- 
-- A start job for unit UNIT has finished successfully.

The server has Ubuntu 20.4

Any tip about what can I do?

Answer 1

I faced the same problem today ,the source of the issue might be different but, read carefully /var/log/mysql/error.log it should point to the error.

I deleted one table that I didn't need .MYD and .MYI files. It was a mistake , I should drop the table , but my server was on 100% memory usage.

What I did was reading the error on:

sudo tail -f /var/log/mysql/error.log

Keep two terminal open at the same time, first run

sudo tail -f /var/log/mysql/error.log 

on first terminal , then run on the second terminal

sudo systemctl stop mysql.service   ---to shut it down
sudo systemctl start mysql.service  ---to try and start up and see the reason why it is not starting.

For me it was:

2022-01-26T16:30:36.213788Z 0 [Note] InnoDB: Database was not shutdown normally!
2022-01-26T16:30:36.213802Z 0 [Note] InnoDB: Starting crash recovery.
2022-01-26T16:30:36.213830Z 0 [ERROR] InnoDB: Tablespace 605 was not found at ./contratti/ip_log2_bak.ibd.
2022-01-26T16:30:36.213840Z 0 [ERROR] InnoDB: Set innodb_force_recovery=1 to ignore this and to permanently lose all changes to the tablespace.
2022-01-26T16:30:36.644359Z 0 [ERROR] InnoDB: Cannot continue operation.
2022-01-26T16:30:38.045091Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-01-26T16:30:38.062994Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.34-log) starting as process 2843 ...
2022-01-26T16:30:38.416074Z 0 [Note] InnoDB: PUNCH HOLE support available

I was able to fixing by adding on /etc/mysql/mysql.conf.d/mysqld.cnf

innodb_force_recovery = 2

From MySQL docs

If you are able to dump your tables with an innodb_force_recovery value of 3 or less, then you are relatively safe that only some data on corrupt individual pages is lost.

After this change I stopped and started again MySQL and it is working great.

Answer 2

Following debuging commens from @Ergest Basha, after checking the logs it looked like a configuration issue:

2022-01-26T15:10:01.855905Z 0 [ERROR] [MY-000077] [Server] /usr/sbin/mysqld: Error while setting value 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBS>

Roll back to default