Copying MySQL data files to another computer

Posted on 2008-11-08
Last Modified: 2012-05-05
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?
Question by:rpkhare
    LVL 4

    Assisted Solution

    LVL 26

    Expert Comment

    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)?

    LVL 8

    Author Comment

    (1) Win XP SP2
    (2) InnoDB
    (3) Default
    LVL 26

    Accepted Solution


     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..
    LVL 8

    Author Comment

    Where the "ibdata" and ".ibd" files located? And, where is the transaction log located?
    LVL 26

    Expert Comment

    >>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.

    LVL 8

    Author Comment

    Thanks I will try it.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (…
    I have been using r1soft Continuous Data Protection ( for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now