Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I just recovered my InnoDB tables from my backup (copied ibdata1, ib_logfile1, ib_logfile0)

However, when I start mysql now, and try to access my site with InnoDB tables, *I get these errors

Got error -1 from storage engine
MySQL server has gone away

and in my /var/log/syslog I have a lot of messages like

Oct 19 00:21:31 vm645 mysqld: 111019  0:21:31  InnoDB: Error: page 113644 log sequence number 12 1381339343
Oct 19 00:21:31 vm645 mysqld: InnoDB: is in the future! Current system log sequence number 12 1375387790.
Oct 19 00:21:31 vm645 mysqld: InnoDB: Your database may be corrupt or you may have copied the InnoDB
Oct 19 00:21:31 vm645 mysqld: InnoDB: tablespace but not the InnoDB log files. See
Oct 19 00:21:31 vm645 mysqld: InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
Oct 19 00:21:31 vm645 mysqld: InnoDB: for more information.

and at the end, I see

Oct 19 00:21:31 vm645 mysqld: InnoDB: Apply batch completed
Oct 19 00:21:31 vm645 mysqld: 111019  0:21:31  InnoDB: Started; log sequence number 12 1375387790
Oct 19 00:21:31 vm645 mysqld: InnoDB: !!! innodb_force_recovery is set to 4 !!!
Oct 19 00:21:31 vm645 mysqld: 111019  0:21:31 [Note] Event Scheduler: Loaded 0 events
Oct 19 00:21:31 vm645 mysqld: 111019  0:21:31 [Note] /usr/sbin/mysqld: ready for connections.
Oct 19 00:21:31 vm645 mysqld: Version: '5.1.49-3-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Debian)

Okay, I'd say it's ready to be connected. However, I get the same errors as stated at the start of this question. It looks like it's up but everytime I try to run query on InnoDB table it just fell down for a second.

Thoughts?

EDIT:

Sometimes, when I'm restarting mysql it shows me in console

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)                                                            - ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
......

and I have to start it again

EDIT2:

For some reason, now it is throwing only

Unknown table engine 'InnoDB'
share|improve this question
2  
Uhmmm. You don't restore a database by copying the ibdata files. You recover it by mysqldump and the like: dev.mysql.com/doc/refman/5.6/en/mysqldump.html –  mailq Oct 18 '11 at 22:38
    
How did you back this up? Have you tried performing a restore to a clean install of mysql? –  Zoredache Oct 18 '11 at 22:38
    
Try without the log files. –  David Schwartz Oct 18 '11 at 22:39
    
@Zoredache: yeah, reinstalled mysql package & backed up - classic rsync, no mysqldump –  genesis Oct 18 '11 at 22:47
2  
Now you know one of the reasons some of us are so strongly against attempting to backup MySQL at the file level. –  John Gardeniers Oct 19 '11 at 0:13
show 2 more comments

2 Answers

up vote 0 down vote accepted

I have backuped all databases (mysqldump -p db_name > db_name.sql), reinstalled mysql completely and imported my databases back (mysql -p db_name < db_name.sql). It works now.

Side note: Never, ever remove /var/lib/mysql/ibdata1.

share|improve this answer
add comment

How did you made the backup? Copying over the mysql directory is a very bad idea, and wont work well. Use mysqldump, or innobackupex.

For this problem propably it will solve your problem is you move your ib_logfile[0|1] somewhere and starting the mysql server. After this I would start checking if my data is corrupted in the db.

share|improve this answer
    
That will not solve the log sequence number is in the future! error message. Moving his old ib_logfiles back might work but he likely doesn't have access to those any more. Running enough queries to increment the sequence number beyond what it's reporting also fixes that error but using mysqldump and restoring from scratch is a better method if you can manage it (which he seems to have done). –  Ladadadada Oct 29 '13 at 12:54
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.