Import .frm and .opt files to MySQL

Question

I am using MySQL 5.5.1.1 downloaded and installed from : http://dev.mysql.com/downloads/

I want to import .frm and .opt database files from my local machine to my mysql database.

I created a new Databbase connection to help me import the files from my machine. But I am unable to import .frm files as I keep getting error message: Format not supported.

Can someone please help me on how I could import files to MySQL client??

Thank you, J

Answer 1

As far importing any MySQL data, three(3) things need to considered.

MyISAM

Importing a MyISAM table is a simple as moving three files with the extensions .frm, .MYD, and .MYI files for the given table into a MySQL folder. For example, if the table name is mydata, then the three(3) files

• mydata.frm
• mydata.MYD
• mydata.MYI

The following could be the nightmare of nightmares. Importing InnoDB depends entirely on many factors that fall into one of two categories:

InnoDB (innodb_file_per_table disabled [default])

All InnoDB data and index pages are located in /var/lib/mysql/ibdata1. This file must be moved from your source machine (Server-S) to the target machine (Server-T) and placed in the same absolute path. In fact, here is the shocker: Server-S and Server-T must be the same. In other words, you cannot import and export InnoDB .ibd files to other machines. They can only be imported and exported on the same machine the .ibd was created on.

You would also have to move /var/ib/mysql/ib_logfile0 and /var/ib/mysql/ib_logfile1 from Server-S and place them in the same absolute path on Server-T.

You also must make sure that every InnoDB variable set in /etc/my.cnf from Server-S must be set in /etc/my.cnf on Server-T.

InnoDB (innodb_file_per_table enabled)

For every InnoDB table, there will be two files. For example, if the InnoDB table in the mydata database is called mytable, you will have /var/lib/mysql/mydata/mytable.frm and /var/lib/mysql/mydata/mytable.ibd. The .ibd file contains data and index pages for the table. To import the individual table you must

1. Place the mytable.frm in /var/lib/mysql/mydata folder on Server-T
2. Place the mytable.ibd in /var/lib/mysql/mydata folder on Server-T
3. Run ALTER TABLE mydata.mytable IMPORT TABLESPACE;

Make sure you have the /var/lib/mysql/ibdata1 in the same place it was imported from.

Moral of the story

Please do not use IMPORT TABLESPACE techniques across different servers. Just do a mysqldump of everything and import the mysqldump. Logical dumps are always the safest way to go !!!

Answer 2

I did a mysql import using the files on xampp. I have very many databases so I did not want to go through the mysqldump route. The process was:

1. Install the new xampp, but do not enable the services
2. Update my.ini to include any custom settings I had on the previous install.
3. Confirm that mysql is starting successfully
4. Copy over all the database folders (inside the data folder) from the old install with the exception of mysql, phpmyadmin and webauth.
5. Confirm that mysql is starting successfully
6. Copy over ib_logfile0, ib_logfile1 and ibdata1. Rename the files first, do not overwrite just in case something goes wrong.
7. Confirm that mysql is starting successfully
8. Copy over the following files from the data/mysql folder: db.frm, db.MYD, db.MYI, user.frm, user.MYD, user.MYI. Do not overwrite the new ones just in case it doesn't work.
9. Confirm that mysql is starting successfully

At the end of the process I had mysql working perfectly with all my databases and users intact.