mysql backup and restore from ib_logfile failure

Question

Here's the case:

After computer being hacked, we are in a rush to backup all data out to other computer.

As a result, the mysql databases are not backup out as sql statement.

What we have done is backup out all the physical files/folders in the C drive to new computer.

Eg:

C:\Program Files\MySQL\MySQL Server 4.1\data

In this case, all data for mysql are inside unreadable file. Inside data folder consist of files like ib_logfile0, ib_logfile1, but not ib_data1 All database's table structure format are inside each respective folder. (Some folder have .frm, .opt) (some other folder have .frm, .myd, .myi)

How can I retrieve back the data from the database in a new computer?

I tried to install the same mysql version(4.1) at new computer, then replace all backup files inside data folder into this mysql in new computer. Then restart mysql service.

When I restart, it fail: Could not start mysql service on local computer. error 1067: process terminated unexpectedly.

Error log showing:

InnoDB: The first specified data file .\ibdata1 did not exist:
InnoDB: a new database to be created!
090930 10:24:49  InnoDB: Setting file .\ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Error: log file .\ib_logfile0 is of different size 0 87031808 bytes
InnoDB: than specified in the .cnf file 0 25165824 bytes!
090930 10:24:49 [ERROR] Can't init databases
090930 10:24:49 [ERROR] Aborting

090930 10:24:49 [Note] C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt: Shutdown complete

Answer 1

you need to look at the mysql.err log in the data folder to see what's wrong.

did the mysql service start properly when you first installed it?

looks like you're on windows, so i'm going to guess that you moved the files instead of copying them. when you move files in windows, they retain their permissions. when you copy, they inherit the permissions from the folder. so check to make sure they have the right permissions.

Answer 2

your error log indicates that your innodb configuration does not match your innodb files. you need to look at the configuration file on your old server to figure out where mysql was configured to store your innodb files. then go find those files and copy them to the new server. you should also copy the innodb configuration settings exactly from the old server and apply them to the new server.

Answer 3

Sounds like your innodb_log_file_size in my.ini was not the same as before.

Have a look a the ib_logfile0 filesize in the data folder and if it's 102,400KB it should be: innodb_log_file_size=100M

Yours looks like it should be: innodb_log_file_size=83M (set to 24M now)

I've had this issue when someone used the installer to "repair" a mysql installation.

Have Fun.