Geek Answers Handbook

Restore mysql database from ibdata1

I have a client that seems to have lost all its mysql databases from their local machine. They are on a Mac that I'm a little unfamiliar with, and I'm on Ubuntu. There were no .MYD or .MYI files in the database folder, only .frm. I made them mask the mysql and sight folders (with an eye on the database we needed) and the ibdata1, ib_logfile0 and ib_logfile1 files. I created a second folder for mysql, / var / lib / mysql2 and moved the files and folders there. I transferred the new folder and files to mysql: mysql, edited /etc/mysql/my.cnf to point to the new folder, edited /etc/apparmor.d/usr.sbin.mysqld, and then restarted apparmor and mysql. However, the following errors occur in the mysql error log:

130308 17:38:16 [Note] Plugin 'FEDERATED' is disabled. 130308 17:38:16 InnoDB: Initializing buffer pool, size = 8.0M 130308 17:38:16 InnoDB: Completed initialization of buffer pool InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 130308 17:38:16 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 130308 17:38:16 InnoDB: Error: space id and page n:o stored in the page InnoDB: read in are 0:589824, should be 0:7! 130308 17:38:16 InnoDB: Error: page 589824 log sequence number 786432 0 InnoDB: is in the future! Current system log sequence number 0 63932940. InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files. See InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html InnoDB: for more information. InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 7. InnoDB: You may have to recover from a backup. 130308 17:38:16 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex 0008000000090000000a0000000b0000000c00000000000000000000000202720000 (snipped because this goes on for a while) Tg 9 <oq E i F / D ;InnoDB: End of page dump 130308 17:38:16 InnoDB: Page checksum 4146777650, prior-to-4.0.14-form checksum 1800374066 InnoDB: stored checksum 524288, prior-to-4.0.14-form stored checksum 0 InnoDB: Page lsn 786432 0, low 4 bytes of lsn at page end 0 InnoDB: Page number (if stored to page already) 589824, InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0 InnoDB: Page may be a freshly allocated page InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 7. InnoDB: You may have to recover from a backup. InnoDB: It is also possible that your operating InnoDB: system has corrupted its own file cache InnoDB: and rebooting your computer removes the InnoDB: error. InnoDB: If the corrupt page is an index page InnoDB: you can also try to fix the corruption InnoDB: by dumping, dropping, and reimporting InnoDB: the corrupt table. You can use CHECK InnoDB: TABLE to scan your table for corruption. InnoDB: See also http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. InnoDB: Ending processing because of a corrupt database page.

I tried adding innodb_force_recovery = 4 to the my.cnf file, which generates a whole bunch of different errors:

 130308 17:48:30 [Note] Plugin 'FEDERATED' is disabled. 130308 17:48:30 InnoDB: Initializing buffer pool, size = 8.0M 130308 17:48:30 InnoDB: Completed initialization of buffer pool InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 130308 17:48:30 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 130308 17:48:30 InnoDB: Error: space id and page n:o stored in the page InnoDB: read in are 0:589824, should be 0:7! 130308 17:48:30 InnoDB: Error: page 589824 log sequence number 786432 0 InnoDB: is in the future! Current system log sequence number 0 63932940. InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files. See InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html InnoDB: for more information. InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 7. InnoDB: You may have to recover from a backup.

and a whole bunch more that I can provide if that helps. Any suggestions on what to try from here would be greatly appreciated, thanks.

-Michael

edit: I tried the following steps here, but had problems starting mysql using the command line used:

http://blog.shiraj.com/2012/10/extract-data-from-mysql-ibdata1-data-file/

+4
source share
1 answer

The following worked for mine:

  • Set innodb_force_recovery = 1 in my.cnf

  • Try restarting mysqld. If not, repeat step # 1 and increment innodb_force_recovery from each until successful completion. Use the guide to help you understand what happens every time you increase it: http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html

  • Once mysqld is running, try resetting all your databases

 mysqldump -u root -p --all-databases > /tmp/mysqldump-all.sql
  • If this fails, you first need to try it at the database level.
 mysqldump -u root -p --databases db_name > mysqldump-db_name.sql
  • If this fails, you will have to try it at the table level.

SELECT * FROM table_name INTO OUTFILE '/tmp/table_name.sql'

  • As soon as one of them is successful, and either all your db or all your tables are exported, stop mysqld

  • Move your ib_logfile *> ib_logfile * .bak. They are usually located in the mysql data directory.

  • If you increased your innodb_force_recovery => 4 in the first step, you need to set it below 4. Starting from 5.6.15, the innodb_force_recovery parameter of 4 or more indicates InnoDB in read-only mode.

  • Start mysqld server

  • Import exported databases or tables

mysql -u root -p </tmp/mysqldump-all.sql

  • Increased your innodb_force_recovery => 1

  • Reboot mysqld server

+3
source

More articles:


All Articles