Mysql 5.7 cannot start again

connectionserrorsMySQLmysql-5.7

For the first time in my life, I have Ubuntu, so I do not know much how to manage and what should I be looking for; I'm using version 18.04.
I installed MySQL 5.7, it worked great for a couple of days and suddenly it can't even restart, doesn't start again (with service mysql start or anything)…

Using systemctl status mysql.service I get:

● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor 
preset: enabled)
Active: failed (Result: exit-code) since Thu 2018-07-26 14:24:49 CEST; 
10min ago
Process: 3758 ExecStart=/usr/sbin/mysqld --daemonize --pid- 
file=/run/mysqld/mysqld.pid (code=exited
Process: 3749 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre 
(code=exited, status=0/SUCCESS)

јул 26 14:24:49 maax systemd[1]: mysql.service: Service hold-off 
time over, scheduling restart.
јул 26 14:24:49 maax systemd[1]: mysql.service: Scheduled 
restart 
job, restart counter is at 5.
јул 26 14:24:49 maax systemd[1]: Stopped MySQL Community Server.
јул 26 14:24:49 maax systemd[1]: mysql.service: Start request 
repeated too quickly.
јул 26 14:24:49 maax systemd[1]: mysql.service: Failed with 
result 'exit-code'.
јул 26 14:24:49 maax systemd[1]: Failed to start MySQL Community 
Server.

and using journalctl -xe I get:

јул 26 14:24:49 maax systemd[1]: mysql.service: Scheduled 
restart job, restart counter is at 5.
-- Subject: Automatic restarting of a unit has been scheduled
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
-- 
-- Automatic restarting of the unit mysql.service has been scheduled, 
as the result for
-- the configured Restart= setting for the unit.
јул 26 14:24:49 maax systemd[1]: Stopped MySQL Community Server.
-- Subject: Unit mysql.service has finished shutting down
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
-- 
-- Unit mysql.service has finished shutting down.
јул 26 14:24:49 maax systemd[1]: mysql.service: Start request 
repeated too quickly.
јул 26 14:24:49 maax systemd[1]: mysql.service: Failed with 
result 'exit-code'.
јул 26 14:24:49 maax systemd[1]: Failed to start MySQL Community 
Server.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
-- 
-- Unit mysql.service has failed.
-- 
-- The result is RESULT.

What should I do to fix this and continue to use Mysql every day normally?
Mysql works okay when I reinstall it, but after some time, it crushes again…

Best Answer

The error log of MySQL records the details why MySQL can not restart, check the error log and update it in Questions would be helpful.

If MySQL is installed by apt-get or some other system's ways and uses the default configurations, error log always in /var/log/mysql.

If my.cnf changes, check the modified my.cnf in /etc/mysql/my.cnf or other paths to find the log_error param.

Related Solutions

MySQL cannot start

InnoDB: We intentionally generate a memory trap.

It looks like InnoDB is deliberately crashing itself because it's encountering a problem with your on-disk data structures that it cannot safely recover from.

What right happened before this? Unexpected power loss, hard reboot, lock-up? Upgrade? Updates? Restoring files from a backup? Is there anything interesting at the OS level in /var/log/messages?

Knowing that information might change the way you proceed somewhat, but the net effect is that this portion of the error message is leading you in the right direction:

InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.

You will need to force recovery to have any chance of getting to your data. Be patient and use the lowest value possible to get the server started so you can make a backup of as much data as possible.

Of course, if there are any underlying hardware issues, those need to be addressed first.

Update: suggestion: put innodb_force_recovery = 6 into the [mysqld] section of your my.cnf file instead of passing it on the command line. I don't know how the initscripts will handle it. Or, you can leave it where it is but put a nonsense value like --innodb_force_recovery=kitten ... just to make sure the option is really being seen by mysqld.

At some point, you should get a message in the log about the option being set, but I'm not sure where it is, in time, related to what you're seeing... so I want to be sure it's really getting set.

Here's a normal 5.5.28 startup with innodb_force_recovery = 1. (Sorry, I don't have a 5.1 that I can take offline at the moment).

130131 12:47:16 InnoDB: Completed initialization of buffer pool
130131 12:47:16 InnoDB: highest supported file format is Barracuda.
130131 12:47:21  InnoDB: Waiting for the background threads to start
130131 12:47:22 InnoDB: 1.1.8 started; log sequence number 2872409775300
130131 12:47:22 InnoDB: !!! innodb_force_recovery is set to 1 !!!

It seems to me that this message is appearing a little too late in the show, which may be the simple explanation for why you aren't seeing it.

MySQL – Forcing InnoDB Recovery of a Corrupted Database

InnoDB complains about wrong LSN because ib_logfile-s were deleted. You shouldn't have deleted those. But MySQL can survive after that. Next time InnoDB updates a page it will overwrite its LSN in the header and the error message will go away. Just wait till it starts (the error log shows neither successful nor unsuccessful start).

To get 100% clean tablespace you need to start MySQL with innodb_force_recovery=4, take mysqldump and restore it on a fresh instance of InnoDB (by fresh I mean you have to delete ibdata1, and all databases directories).

UPDATE:

At this point MySQL is started with innodb_force_recovery=x (x != 0)

Take dump of all databases:

# mysqldump --skip-lock-tables -A > alldb.sql

Check where MySQL keeps its files(in my case it's /var/lib/mysql/):
```
# mysql -NBe "SELECT @@datadir"
/var/lib/mysql/
```
Stop MySQL
```
# mysqladmin shut
```
Move old MySQL files to safe place
```
# mv /var/lib/mysql /var/lib/mysql.old
```

Create new system database

# mkdir /var/lib/mysql
# mysql_install_db

Start MySQL
```
# /etc/init.d/mysql start
```
Restore the dump
```
# mysql < alldb.sql
```

Restore may take long time if the database is big. Another trick may work in that case. Run ALTER TABLE ... ENGINE INNODB on each InnoDB table. It will rebuild all InnoDB indexes and thus the errors will go away.

Best Answer

Related Solutions

MySQL cannot start

MySQL – Forcing InnoDB Recovery of a Corrupted Database

Related Question