I do not know whether this is a mysql(version 5.6.11) bug, the case is as following. today when i update mysql conf and kill -9 and restart mysqld, i find that it can't restart anymore. after having a look at the error log, i find the following:

2013-07-09 17:04:21 7872 [ERROR] InnoDB: Tried to read 1048576 bytes at offset 2097152. Was only able to read 917504.

then i make a strace to trace which file has the problem. the result is ibdata1.

-rw-rw----. 1 mysql mysql   79691776 7月   9 16:51 ibdata1

well, for performance i change log commit as following.

innodb_flush_log_at_trx_commit = 2

but i do not think this can cause data lost since OS does not reboot during mysql restart. so can anybody help me out on what's the reason(maybe the kill -9?) that makes data corrupt and how to restore the data, thank you very much.

  • Do you have backup ? Jul 9, 2013 at 10:35
  • yes, i make that operation on slave. so it does not affect much. but what confuses me is why that would happen. and what to do to make progress on that so that it would not happen again:) Jul 9, 2013 at 11:15

1 Answer 1


If you don't have backup.

I think the recommended way to recover that databases is start MySQL with innodb_force_recovery = 4 (or higher values) and dump the databases to a SQL.Then drop it and recover from backup. Instead of dropping the original database I prefer to create the new databases with another name or in another server and check the content first.


  1. In mysqld section of my.cnf add a line innodb_force_recovery = 4 and then restart MySQL server using /etc/init.d/mysql restart.

  2. Take backup.

  3. Restore it on other server and verify the contents.

Please also have a look at Forcing InnoDB Recovery.

UPDATE : For your comment how to progress on that so that it would not happen again

You should not kill MySQL it is not a good practice that may cause MySQL server to crash,whenever you make any configuration changes you should proceed as follows.

  1. Safely stop MySQL Server using /etc/init.d/mysql stop
  2. Make changes
  3. Restart MySQL Server
  • This saved my production database - thank you!!! I'd only suggest that everyone start with innodb_force_recovery=1, then go up from there, per the linked documentation. Though, I must admit 4 was the magic number for me.
    – Sam
    Mar 29, 2016 at 12:37
  • I keep having 'mysqldump: Got error: 2013: Lost connection to MySQL server during query when using LOCK TABLES' without or with innodb_force_recovery values (1-5) any idea? same 'show tables'
    – stackdave
    Apr 12, 2017 at 4:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.