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

Related Solutions

Mysql – Import or restore MySQL databases from var directory

Disclaimer: I can't guarantee that this works for you too, it did for me.

I've done similar restorations (under Windows, though) by simply copying the files (the whole /usr/local/mysql directory...shouldn't that be under /var/mysql anyway?). Of course you'll need to stop the MySQL daemon and restart if after you're done.

Also, check the file permissions, just in case that something has changed.

Thesql how to fix Access denied for user ‘root’@’localhost’

Follow the steps below.

Start the MySQL server instance or daemon with the --skip-grant-tables option (security setting).
```
$ mysqld --skip-grant-tables
```

Execute these statements.

$ mysql -u root mysql
$mysql> UPDATE user SET Password=PASSWORD('my_password') where USER='root';
$mysql> FLUSH PRIVILEGES;

If you face the unknown field Password error above use:

update user set authentication_string=password('my_password') where user='root';

Finally, restart the instance/daemon without the --skip-grant-tables option.
```
$ /etc/init.d/mysql restart
```

You should now be able to connect with your new password.

$ mysql -u root -p

Enter password: my_password

Fix for MySQL “Unable to lock ibdata1” error

sudo mv /usr/local/mysql/data/ibdata1 /usr/local/mysql/data/ibdata1.bak
sudo mv /usr/local/mysql/data/ib_logfile0 /usr/local/mysql/data/ib_logfile0.bak
sudo mv /usr/local/mysql/data/ib_logfile1 /usr/local/mysql/data/ib_logfile1.bak
sudo cp -a /usr/local/mysql/data/ibdata1.bak /usr/local/mysql/data/ibdata1
sudo cp -a /usr/local/mysql/data/ib_logfile0.bak /usr/local/mysql/data/ib_logfile0
sudo cp -a /usr/local/mysql/data/ib_logfile1.bak /usr/local/mysql/data/ib_logfile1
sudo /etc/init.d/mysql restart