Memorise

Extract data from MySQL ibdata1 data file

I recently had my server out of order and I could only access to files. I manage to backup my MySQL files (ibdata, ib_logfile1, ib_logfile2, and the tables *.frm files). No sql dump to be imported into a new MySQL installation.

I setup new Server with same version of MySql installed simply replacing the “data” directory inside the new installation setup gave me errors and MySql service won’t start, I think its complaining about InnoDB “sequence numbers”.

My new recovery Server setup
Windows 2003 SP1 and install SQLYog then I installed Xampp in C:\xampp, with server MySql 5.1. My ‘data’ directory was in C:\xampp\mysql\data.

my.ini file location was in C:\Program Files\MySQL\MySQL Server 5.1

Going further these are the steps that I followed to get my data back, I have taken the instruction from various sites mixed of linux setup, hence kept the linux instructions but I done this on Windows server commands are the same:

1. Stop your mysql daemon/service (# services stop mysql) on mine services.msc and made sure MySql service is not running.
2. Replace the new “data” directory with the backed-up one (don’t forget to work on a copy of your backup, and not with your one and only backup!!)
3. Start your mysqld with the innodb revocery parameters, and keep that windows open:
   # mysqld --standalone --console --innodb-force-recovery=4
   the console will stay open and you’ll see the log messages directly on your screen (CTRL-C will close the server), I came across number of problem please see after step 7 I have copied & pasted my output from my recovery.
4. Use your favorite db backup tool (in my case SQLYog) to create a new SQL dump of your database (myPhpAdmin, …) remember that starting your MySQL server with –innodb-force-revovery param you cann’t run any INSERT, DELETE or UPDATE sql command.
   You’ll wont backup the “mysql” and “performance_schema” tables..
5. Shut down your “temporary” MySQL server hitting CTRL-C on the shell opened at (3)
6. Restart MySQL with the defaults settings (or, if you like the shell output run: mysqld --standalone --console)
7. Remove all the databases and re-create them using your newly created SQL Dump

C:\Program Files\MySQL\MySQL Server 5.1
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\Program Files\MySQL\MySQL Server 5.1>cd bin

First try
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqld –standalone –console –innodb-force-recover=4

Error Came up as below
121013 19:43:47 [Note] Plugin ‘FEDERATED’ is disabled.
121013 19:43:47  InnoDB: Initializing buffer pool, size = 47.0M
121013 19:43:47  InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file .\ib_logfile0 is of different size 0 10485760 bytes
InnoDB: than specified in the .cnf file 0 25165824 bytes!
121013 19:43:47 [ERROR] Plugin ‘InnoDB’ init function returned error.
121013 19:43:47 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
121013 19:43:47 [ERROR] Unknown/unsupported table type: INNODB
121013 19:43:47 [ERROR] Aborting
121013 19:43:47 [Note] mysqld: Shutdown complete

Tried to fix it with recovery option 6

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqld –standalone –console –innodb-force-recover=6

121013 19:44:29 [Note] Plugin ‘FEDERATED’ is disabled.
121013 19:44:29  InnoDB: Initializing buffer pool, size = 47.0M
121013 19:44:30  InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file .\ib_logfile0 is of different size 0 10485760 bytes
InnoDB: than specified in the .cnf file 0 25165824 bytes!
121013 19:44:30 [ERROR] Plugin ‘InnoDB’ init function returned error.
121013 19:44:30 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
121013 19:44:30 [ERROR] Unknown/unsupported table type: INNODB
121013 19:44:30 [ERROR] Aborting
121013 19:44:30 [Note] mysqld: Shutdown complete

Still received error ‘FEDERATED is disabled’ when tried to use -u to authenticate

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqld –standalone –console –innodb-force-recover=6 -u root
121013 19:53:17 [Note] Plugin ‘FEDERATED’ is disabled.
121013 19:53:17  InnoDB: Initializing buffer pool, size = 47.0M
121013 19:53:17  InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file .\ib_logfile0 is of different size 0 10485760 bytes
InnoDB: than specified in the .cnf file 0 25165824 bytes!
121013 19:53:17 [ERROR] Plugin ‘InnoDB’ init function returned error.
121013 19:53:17 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
121013 19:53:17 [ERROR] Unknown/unsupported table type: INNODB
121013 19:53:17 [ERROR] Aborting
121013 19:53:17 [Note] mysqld: Shutdown complete

Fix the error ‘FEDERATED is disabled’ when I used –federated but still getting error about the log file size

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqld –standalone –console –innodb-force-recover=6 -u root –federated

121013 20:00:38  InnoDB: Initializing buffer pool, size = 47.0M
121013 20:00:39  InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file .\ib_logfile0 is of different size 0 10485760 bytes
InnoDB: than specified in the .cnf file 0 25165824 bytes!
121013 20:00:39 [ERROR] Plugin ‘InnoDB’ init function returned error.
121013 20:00:39 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
121013 20:00:39 [ERROR] Unknown/unsupported table type: INNODB
121013 20:00:39 [ERROR] Aborting
121013 20:00:39 [Note] mysqld: Shutdown complete

Fix the logfile error by editing the my.ini file  innodb_log_file_size=10M and changing the value same as my log file in ‘data’ directory. while keeping the command prompt open I launched SQLyog and saved all database one by one to .sql dump, see attached image.

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysqld –standalone –console –innodb-force-recover=6 -u root –federated

121013 20:05:20  InnoDB: Initializing buffer pool, size = 47.0M
121013 20:05:20  InnoDB: Completed initialization of buffer pool
InnoDB: The user has set SRV_FORCE_NO_LOG_REDO on
InnoDB: Skipping log redo
121013 20:05:22  InnoDB: Started; log sequence number 0 0
InnoDB: !!! innodb_force_recovery is set to 6 !!!
121013 20:05:22 [Note] Event Scheduler: Loaded 0 events
121013 20:05:22 [Note] mysqld: ready for connections.
Version: ‘5.1.66-community’  socket: ”  port: 3306  MySQL Community Server (GPL)
121013 20:12:34 [ERROR] Invalid (old?) table or database name ‘data ibdata file backup’
121013 20:12:34 [ERROR] Invalid (old?) table or database name ‘data ibdata filebackup’
121013 20:12:34 [ERROR] Invalid (old?) table or database name ‘data working’
121013 20:12:34 [ERROR] Invalid (old?) table or database name ‘data working’
121013 22:23:28 [ERROR] Invalid (old?) table or database name ‘data ibdata filebackup’
121013 22:23:28 [ERROR] Invalid (old?) table or database name ‘data working’
121013 22:23:28 [ERROR] Invalid (old?) table or database name ‘data ibdata filebackup’
121013 22:23:28 [ERROR] Invalid (old?) table or database name ‘data working’

Tagged with:

Categorised as: Linux/OSX, Microsoft, Networking