I had to recover InnoDB tables from my backup, but mysql is crashing

Question

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'

Uhmmm. You don't restore a database by copying the ibdata files. You recover it by mysqldump and the like: http://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
@Zoredache: yeah, reinstalled mysql package & backed up - classic rsync, no mysqldump — genesis, Oct 18 '11 at 22:47
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 00:13

score 0 · Accepted Answer · answered Oct 19 '11 at 16:19

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.

answered Oct 19 '11 at 16:19

genesis

343
4
15

score 0 · Answer 2 · answered Oct 28 '13 at 22:44

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.

answered Oct 28 '13 at 22:44

banyek

399
1
10

That will not solve the `log sequence number is in the future!` error message. Moving his old `ib_logfile`s *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

I had to recover InnoDB tables from my backup, but mysql is crashing

EDIT:

EDIT2:

2 Answers2