• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1010
  • Last Modified:

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?
0
rpkhare
Asked:
rpkhare
  • 3
  • 3
2 Solutions
 
UmeshCommented:
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
 
rpkhareAuthor Commented:
(1) Win XP SP2
(2) InnoDB
(3) Default
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
UmeshCommented:
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
 
rpkhareAuthor Commented:
Where the "ibdata" and ".ibd" files located? And, where is the transaction log located?
0
 
UmeshCommented:
>>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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now