MySQL won't start

Question

I'm using a debian system. I could not start my mysql after the partition went full - I received the message "ERROR: The partition with /var/lib/mysql is too full!"

I found a recommendation to delete some of the data folders I don't need in /var/lib/mysql so I deleted the folder of an old DB i didn't need.

Now when I tried to start the mysqld service again, I receive the following error:

150218 10:09:03 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
150218 10:09:03 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full n$
150218 10:09:03 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use$
150218 10:09:03 [Note] Plugin 'FEDERATED' is disabled.
150218 10:09:03 InnoDB: The InnoDB memory heap is disabled
150218 10:09:03 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150218 10:09:03 InnoDB: Compressed tables use zlib 1.2.7
150218 10:09:03 InnoDB: Using Linux native AIO
150218 10:09:03 InnoDB: Initializing buffer pool, size = 128.0M
150218 10:09:03 InnoDB: Completed initialization of buffer pool
150218 10:09:03 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 20417554120
150218 10:09:03  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 20419913007
150218 10:09:03  InnoDB: Error: page 24667 log sequence number 20425339296
InnoDB: is in the future! Current system log sequence number 20419913007.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
150218 10:09:03  InnoDB: Error: page 530914 log sequence number 20426394124
InnoDB: is in the future! Current system log sequence number 20419913007.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
InnoDB: 1 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 6135 row operations to undo
InnoDB: Trx id counter is 574E00
150218 10:09:04  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 150218 10:09:04  InnoDB: Error: page 248000 log sequence number 20425331738
InnoDB: is in the future! Current system log sequence number 20419913007.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html

I saw that I should start mysql while adding to the my.cnf the following command innodb_force_recovery = 1/2/3/4/5/6 Starting from one, going up.

Tried all of those but nothing.

Does someone has any idea?

Answer 1

I had a very similar problem when /var/lib/mysql filled /root. Have a look at https://dba.stackexchange.com/questions/91575/moved-mysql-datadir-now-server-will-not-run Use a symlink as there are so many paths buried in various configuration files it is hard to track them down.

Answer 2

By deleting a whole database directory, you have made your system inconsistent: most probably, you still have references to that database in "ibdata*" and "ib_logfile*" (assuming there are InnoDB tables in that database).

The error message "log sequence number is in the future" is another sign of similar inconsistencies.

You might try to start your MySQL server using "innodb_force_recovery" (which makes it read-only) and then use "mysqldump" to get at your data, store them somewhere else. Then get rid of your database files (probably everything below "/var/lib/mysql/data") and install a new, empty MySQL instance ("mysql_install_db") into which you load that dump. This may include data loss, because of those inconsistencies.

Don't forget to set up monitoring of your disk space!