Copying MySQL data files to another computer

In case I don't have a backup of database and I have just the data files in the Data folder left. How to attach those Data files to a fresh installation of MySQL?
LVL 8
rpkhareAsked:
Who is Participating?
 
UmeshConnect With a Mentor MySQL Principle Technical Support EngineerCommented:
Cool...

 If you are able to shut down your MySQL server, you can make a binary backup that consists of all files used by InnoDB to manage its tables. Use the following procedure:

Binary copying from source server

   1. Shut down your MySQL server and make sure that it shuts down without errors.
   2. Copy all your data files (ibdata files and .ibd files) into a safe place.
   3. Copy all your ib_logfile files to a safe place.
   4. Copy your my.cnf configuration file or files to a safe place.
   5. Copy all the .frm files for your InnoDB tables to a safe place.

On target server(Fresh installation)

Youll have to shut down MySQL, copy the files into place, and then restart.

You also need to ensure that InnoDBs transaction log files match its tablespace files. If the files dont matchfor example, if you replace the tablespace files but not the transaction log filesInnoDB may
refuse to start. This is one reason its crucial to back up the transaction log along
with the data files.

Also, going forward make sure youre using the newer InnoDB file-per-table feature (innodb_file_per_table). that makes life bit easier when restoring fever tables..
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Hi Khare,

Could you please tell me these so that I can tell you the best possible way

1. OS
2. InnoDB tables or MyISAM tables used?
3. If InnoDB then
  Files per table or single datafile (tablespace)?

Thanks,
Umesh
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
rpkhareAuthor Commented:
(1) Win XP SP2
(2) InnoDB
(3) Default
0
 
rpkhareAuthor Commented:
Where the "ibdata" and ".ibd" files located? And, where is the transaction log located?
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
>>Where the "ibdata" and ".ibd" files located? And, where is the transaction log located?

ibdata & ib_logfile  - should be in mysql/data/*
.ibd - avail only if  InnoDB file-per-table is used.




0
 
rpkhareAuthor Commented:
Thanks I will try it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.