0

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'
7
  • 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, 2011 at 22:38
  • How did you back this up? Have you tried performing a restore to a clean install of mysql?
    – Zoredache
    Oct 18, 2011 at 22:38
  • Try without the log files. Oct 18, 2011 at 22:39
  • @Zoredache: yeah, reinstalled mysql package & backed up - classic rsync, no mysqldump
    – genesis
    Oct 18, 2011 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. Oct 19, 2011 at 0:13

2 Answers 2

0

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.

0

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.

1
  • 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, 2013 at 12:54

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .