MySQL CPU usage super high since server crashed

I've had a big crash on my server 2 days ago, still don't know why, but after resurrect it the mysql server is consuming a huge amount of cpu and I don't know why:

The server is Mariadb installed by plesk, the my.cnf is completly empty, there is no config at all, everything default like before the crash.

There are no queries in the processlist, but the mysqld process is getting peaks of 200-300% cpu usage:

Here is what mysqltuner says:

```
>> MySQLTuner 1.7.10 - Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 5.5.60-MariaDB
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mariadb/mariadb.log(23K)
[OK] Log file /var/log/mariadb/mariadb.log exists
[OK] Log file /var/log/mariadb/mariadb.log is readable.
[OK] Log file /var/log/mariadb/mariadb.log is not empty
[OK] Log file /var/log/mariadb/mariadb.log is smaller than 32 Mb
[!!] /var/log/mariadb/mariadb.log contains 13 warning(s).
[!!] /var/log/mariadb/mariadb.log contains 17 error(s).
[--] 14 start(s) detected in /var/log/mariadb/mariadb.log
[--] 1) 180826 12:06:05 [Note] /usr/libexec/mysqld: ready for connections.
[--] 2) 180825 12:35:37 [Note] /usr/libexec/mysqld: ready for connections.
[--] 3) 180825 12:26:25 [Note] /usr/libexec/mysqld: ready for connections.
[--] 4) 180825 12:23:35 [Note] /usr/libexec/mysqld: ready for connections.
[--] 5) 180825 10:09:44 [Note] /usr/libexec/mysqld: ready for connections.
[--] 6) 180825 10:00:49 [Note] /usr/libexec/mysqld: ready for connections.
[--] 7) 180825 2:08:32 [Note] /usr/libexec/mysqld: ready for connections.
[--] 8) 180822 3:19:35 [Note] /usr/libexec/mysqld: ready for connections.
[--] 9) 170

Most likely your database files are corrupt. For the sake of performance, and because it is the ordinary working, mysqld doesn't check their integrity, but assumes they are okay. The side effect is that such events can cause anything in the database.

Probably some tree-based index was corrupted on a way, that the nodes are pointing to eachother, which results an inifinite loop walking them.

Considering that your database roughly works, dump all of it, delete the database and reinitalize. The required steps:

• Export the whole thing, the command: mysqldump -uroot -p --all-databases --skip-lock-tables > wholedump.sql

• Stop the mysqld

• Make a copy from the database directory (on most Linuxes, it is in /var/lib/mysql). If something wouldn't work, you will have least something to recover ( ;-) ).

• Restart the mysqld

• Import with the command `mysql -uroot

It is the most simple case, it is not impossible that you will find some new problem doing these steps, then make a new question.

Never can be said enough: you need to have a periodical, automatic backup of all your important databases. Next time a crash will happen and your db won't be recoverable, or only partially recoverable, what will you do?

StackExchange Database Administrators Q#215898, answer score: 2