Suddenly mysql not working

Question

When I execute command service mysql restart it returns error in terminal :

stop: Unknown instance: start: Job failed to start

This is my error.log file.

200205 18:57:35 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
200205 18:57:35 [Note] Plugin 'FEDERATED' is disabled.
200205 18:57:35 InnoDB: The InnoDB memory heap is disabled
200205 18:57:35 InnoDB: Mutexes and rw_locks use GCC atomic builtins
200205 18:57:35 InnoDB: Compressed tables use zlib 1.2.8
200205 18:57:35 InnoDB: Using Linux native AIO
200205 18:57:35 InnoDB: Initializing buffer pool, size = 128.0M
200205 18:57:35 InnoDB: Completed initialization of buffer pool
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 5.
InnoDB: You may have to recover from a backup.
200205 18:57:35  InnoDB: Page dump in ascii and hex (16384 bytes):

In server, there are already 58% empty space available.

What should I need to do to again MySQL running properly?

Please help me. I need urgent help.

Thanks.

Answer 1

Almost a year has passed since this question was asked, but it's something that can create a lot of stress for people who are not prepared.

When you see a message like InnoDB: Database page corruption on disk or a failed ... file read, then you're looking at one of a handful of problems:

1. the hard drive has physically gone bad
2. the system was in the middle of a long write when power was cut to the machine, now the InnoDB tables are in an incomplete state
3. the hard drive is full
4. the hard drive has run out of inodes

To resolve this, there are really only two options that I have found work consistently. The first is to switch over to the hot-swap server and carry on, which is not very realistic for most people running MySQL. The second is to set the mysql_force_recovery value in mysqld.cnf and hope that the system can rebuild enough of itself to allow a mysqldump, followed by a database restoration.

This is how you do it:

0. Open a Terminal or SSH into the server

1. Open the mysqld.cnf file as root:

   sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
   

   Note: You can use any text editor that you prefer. vi here is more about muscle memory than an implicit recommendation.

2. Add the following to the end of the file:

   mysql_force_recovery=4
   

3. Restart MySQL:

   sudo service mysql start
   

The rebuild process may take a long time depending on the size of the database. I have seen 5GB databases need as much as 15 minutes to come back online. Once the database is accessible again, export as much of the data as possible:

mysqldump -h {host} -u {user} -p --events --routines --triggers --all-databases > full-export.sql

Restore this to a test/development machine, see what's missing or what needs to be fixed, then begin building a new production server. Do not trust the old one after it has failed you, regardless the reason.