Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Do you have backup ? –  Abdul Manaf Jul 9 '13 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:) –  user2431104 Jul 9 '13 at 11:15
add comment

1 Answer 1

up vote 1 down vote accepted

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.

STEPS TO BE FOLLOWED

  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
share|improve this answer
    
thank you very much for your great help:) –  user2431104 Jul 9 '13 at 11:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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