I am trying to follow the instructions regarding "How to recover MySQL data from Innodb...etc" at: http://www.buildcms.com/cms_knowledgebase/how_to_recover_mysql_data_from_innodb
which are written for linux and I am using Windows XP. The first step says:
Restore data into the normal MySQL data directory. I have this folder:
- C:\Program Files\MySQL\MySQL Server 5.1
which contains a bin folder and a share folder.
So, where do I put:
1. My ibdata1 file (which I was told is the Innodb data file)
2. The ib_logfile0 and ib_logfile1 files
3. The folder "KioskActivity" which I think is the production folder aka /databasename/*.frm (and other files) that was located on the good (old PC) at:
- C:\Program Files\MySQL\MySQL Server 5.0\KioskActivity
Note: the article referenced above has several steps that I plan to ask for help on with separate Expert Exch questions and pay the max 500 points each step. These are probably easy points for some of you experts out there :)
Here's the article:
How to recover MySQL data from Innodb ?
Unfortunately some times you need to recover MySQL data from ibdata1. It's many reasons why your getting corrupted Innodb files that cannot automatically be restored of the mysqld daemon.
We assume that your scenario are like following:
You got backup of your ibdata1, ib_logfile0 and ib_logfile1
You also got backup of your database folder with .frm files
You would like to restore this backup into an MySQL server that's already in production.
Before we start, just one advice: Stop crying, your data isn't lost.
Restore the data backup you got
First of all restore you data on another MySQL server, to not interrupt the services running in your normal production environment. Restore data into the normal MySQL data directory. For our scenario we use /var/lib/mysql.
Be careful to get the right permissions and owners on all your data files. Your MySQL users should own the files and the group should also be assigned to MySQL.
Before you continue you need to find the size of your Innodb logfiles. Simply run the command ls -l to find this. This will output something like this:
-rw-rw---- 1 mysql mysql 5242880 Jun 25 11:30 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Jun 25 11:30 ib_logfile1Start up MySQL in rescue mode
Some simple steps will start up the MySQL daemon in rescue mode for you:
From your Unix shell su into the mysql user: su mysql
Start up your mysqld process with the logfile size and innodb_force_recovery as parameters.
/usr/sbin/mysqld --innodb_log_file_size=5242880 --innodb_force_recovery=6
If everything goes fine you should get a output like this:
InnoDB: The user has set SRV_FORCE_NO_LOG_REDO on
InnoDB: Skipping log redo
070625 11:59:36 InnoDB: Started; log sequence number 0 0
InnoDB: !!! innodb_force_recovery is set to 6 !!!
070625 11:59:36 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.18' socket: '/var/lib/mysql/mysql.sock' port: 3306 SUSE MySQLGet your data
The last simple but most important step is now to get your data.
Open a new shell to the server where your MySQL daemon are running in recovery mode.
Simply run a normal mysqldump of your database:
mysqldump -u root -p database > database.sql
If you get a message looking like this, you got corrupted Innodb log files:
Got error: 1146: Table 'database.table' doesn't exist when using LOCK TABLESWhat you can do to resolve this problem with keeping the ib_logfile0 file from you most current backup while you restore all the other files from a older backup. This isn't a fail-proof solution, but worth a try.
Restore you data
Now you can copy your SQL dump to your production server and simply restore the data from your MySQL dump file like this:
mysql -u root -p database < database.sql