Mysql – How to restore thesql database using files on data folder

MySQL

I cannot run my mysql since yesterday, and I cannot access it, so I decided to reinstall it. I've backed up the data folder, which I presume contains all I need to restore the database after I reinstall mysql.

I've copy pasted and overwrite the old data folder with the backup, but it throws error that will prevent the mysql from loading up.

I've copy pasted only the folder with the same name as my database (let's say mydatabase), and it runs just fine, but the database itself could not be loaded. When I try to access the database with SQLyog, every table throws "Can't open file tablename.ibd"

How can I restore my database properly?

I'm using MySQL 4.1 and Windows 7.

Best Answer

  • Restoring MySQL InnoDB Files on Windows

    The InnoDB type files were a more difficult task, and that is why I am writing this post. I had a hard time finding out how to do it, but I pieced together enough information to accomplish the task. Now, in an effort to give back, I’ll explain exactly what I did to get it restored.

    In our back-ups we had the following files:

    \MySQL\MySQL Server 4.1\data\ibdata1
    \MySQL\MySQL Server 4.1\data\ib_logfile0
    \MySQL\MySQL Server 4.1\data\ib_logfile1
    

    Plus, in the data folder there were was a folder with the name of the database I was restoring that contained *.frm files (table_name.frm).

    I did the restore on my development machine rather than the actual server because I didn’t want to screw up what was working on the server. I already had MySQL installed from an XAMPP install. (My development box is running Windows XP SP2). XAMPP installs MySQL a little differently than the regular MySQL install, so if it helps to follow what I did here, you may want to install it.

    I first stopped my MySQL service using XAMPP’s control panel.

    I moved the files listed above (ib* files and the folder containing the *.frm files) to the my local mysql data folder (C:\Program Files\xampp\mysql\data).

    I then edited my.cnf (located in C:\Program Files\xampp\mysql\bin) and made the following changes (starting at line 66 for me):

    OLD:

    skip-innodb
    #innodb_data_home_dir = C:/Program Files/xampp/mysql/data/
    #innodb_data_file_path = ibdata1:10M:autoextend
    #innodb_log_group_home_dir = C:/Program Files/xampp/mysql/data/
    #innodb_log_arch_dir = C:/Program Files/xampp/mysql/data/
    #set-variable = innodb_buffer_pool_size=16M
    #set-variable = innodb_additional_mem_pool_size=2M
    #set-variable = innodb_log_file_size=5M
    #set-variable = innodb_log_buffer_size=8M
    #innodb_flush_log_at_trx_commit=1
    #set-variable = innodb_lock_wait_timeout=5
    

    NEW:

    #skip-innodb
    innodb_data_home_dir = C:/Program Files/xampp/mysql/data/
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_log_group_home_dir = C:/Program Files/xampp/mysql/data/
    innodb_log_arch_dir = C:/Program Files/xampp/mysql/data/
    set-variable = innodb_buffer_pool_size=16M
    set-variable = innodb_additional_mem_pool_size=2M
    set-variable = innodb_log_file_size=170M
    set-variable = innodb_log_buffer_size=8M
    innodb_flush_log_at_trx_commit=1
    set-variable = innodb_lock_wait_timeout=50
    

    (I had to set innodb_log_file_size to the actual size of my log file)

    I then edited the XAMPP batch file that starts the mysql service (C:\Program Files\xampp\mysql_start.bat). I added –innodb_force_recovery=6 to the end of the call to mysqld. So line 8 of that file now read:

    mysql\bin\mysqld –defaults-file=mysql\bin\my.cnf –standalone –console –innodb_force_recovery=6
    

    This did the trick! My databases were recovered on my machine. I used SQLyog to do a sql dump of the database to restore it on our production server.

    Source


    Further Resources