6

I used phpMyAdmin->export to create a backup of my database but I have now discovered it did not export the whole database, some tables was missed/refused.

However at the time of doing the export I also saved the whole data directory located at mysql/data/ which has many .ibd and .frm filetypes, which seem to reflect the tables I used to have.

I have tried to copy the entire data directory and paste it into my new setup, but I am not sure if the versions of MySQL / phpMyAdmin are the same, how can I find the MySQL version from my old data directory? Possibly in one of those .frm / .ibd files?

When I do paste the entire data folder, some of the tables in the database are working like normal but some display the following error.

#1033 - Incorrect information in file: '.\db\a_table.frm'

Also it seems every table which shows this same error message is marked in use under Collation in the Structure view in phpMyAdmin

What can I do to fix this? What does it mean?

I am wondering if I re-create my old OS setup, it could work? Is there a better way?

0

2 Answers 2

1

I managed to find a workaround for this but I am not very happy about it. I installed my previuos setup which was EasyPHP and copied my old MySQL *data* dir into the EasyPHP *MySQL* data directory.

This time around the database was able to read the .ibd / .frm files correctly.(even though these files did not change at all)

I then used phpMyAdmin to export the database(this time around all tables/data was stored in the sql file)

I then disabled EasyPHP enviroment and started up my new enviroment and used phpMyAdmin->import with the new sql file which loaded the data correctly.

Overall I think its pretty poor that MySQL cant simply output some type of notice stating that the .ini enviroment variables are not set corresponding to the .ibd / .frm files rather than just output incorrect information and leave the user in the dark with all sorts of angles to think about. After googling around it seems many people seem to believe that their data is corrupt when that's really not the case.

0

How can I find the MySQL version from my old data directory?

$mysql> select version();

Before taking the backup, you should know few things: 1. Default storage engine: select * from information_schema.engines where support='Default'; If it is InnoDB. You should run

show global variables like 'innodb_file_per_table'; and check the value. If it is Off - there is a single file for data and index. So, You can't copy by database by just copying the directory. Also, copying database by copying the directory is not recommended way. There are two ways to export the backup.

1st way: The way through which you are taking the backup - using phpmyadmin or other GUI tools. Its easy if your Database size is less.

2nd way: Use commands to export and import the data. These commands (mysqldump for export and source for import) are inbuilt in MySQL. This post would be very useful for using commands either on terminal (Linux) or command prompt (windows). You would have a lot of flexibilities also when use these commands.

Before importing, please check the storage engines on the destination server. Use the first query I have written in this answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.