MynaG's Bits n Bytes

Possible Scenario

• MYSQL program is corrupted and fail to start. Fresh install of MYSQL did not restore the data.
• Computer crashed but hard disk is still working
• Windows corrupted and fail to start up, but data is safe.

For your information, in MYSQL, files with the extension MYD is the table data, FRM files is the table definition and MYI file is the table indices. There is also the ibdata file specified in the my.ini which is the InnoDB tablesapce files.

You have to be an administrator, so that you can access Document and Settings folder or the Program Filesfolder.

1. Open Window Explorer (Windows Key + E) or Open My Computer and go to the data folder where your database files reside.
2. I would recommend you to copy all the folders in the data folder than just picking a few to copy. Copy the database folders and ibdata file (DO NOT COPY THE ib_logfile(x) files!)
   Note that the ibdata file might not be in the same location as the data files.
   To find out where it could be, read How to find MYSQL database files in Windows.
3. Go over to the target computer, Open System Services. (Type services.msc at the Windows menu “Search Program and files” entry box)
4. On the right panel of the Services window, scroll along the list and look for the name “MYSQL“. Select MYSQL and right mouse click and click <STOP>. Once it has stopped, you will it’s status become blank instead of <Started>.
5. Similarly look for the location of the data folder. Once you located it, I recommend you back up the folder or just rename it. Paste over the folders and ibdata file you have copied from the source computer to the data folder.
6. Go back to the Services window and select back MYSQL. Right mouse click on it and click <Start> to resume the MYSQL service.
7. Open MYSQL administrator and log on to your database server and you should be able to see your transferred database together with their data.

How to find the location of the MYSQL database files.

The most straightforward method is to look for the my.ini file in the folder where MYSQL is installed.

If you leave the installation path as default, it should be at the following path:
C:\Program Files\MySQL\MySQL Server XX.XX (where XX.XX is the MYSQL version no. )

Open the my.ini using notepad and search for the text datadir.
It should look something like this.

datadir=”C:/ProgramData/MySQL/MySQL Server 5.5/Data/”

That indicates where your MYSQL database files is stored on your computer.

Do note that InnoDB tablesapce files or the ibdata file by default configuration should be stored together with the data files in the data folder. However, it could be located in another location depending on the specified folder you have set during the installation.

Similary, check the location of the ibdata file in the my.ini file

innodb_data_home_dir=”C:/MySQL Datafiles/”

What if you have uninstalled your MYSQL?

If you cannot find my.ini or their backup files (my*****.ini.bak) in the installed folder, be glad that MYSQL will not removed the database files after uninstall, so it should still be there somewhere.

Usually by default it is in the C:\Program Files\MySQL\MySQL Server X.XX\data (X.XX is the MYSQL version no) folder. In some cases, for Windows XP, it can also be in the C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server XX.X\data folder or C:\Users\All Users\MySQL\MySQL Server 5.5\data folder for Windows 7 or Windows Vista . There is also a possibility that you have both folder, just make sure you got the correct one which is the one with your database name as folders in it.

In the data folder, you should be able to see some folders that were named after your database and a few files such as ib_logfile0, ib_logfile1 or  ibdata1.If you do not have any of the folders in those location I have mentioned, I suggest you do a file search and look for this ib_logfile* or ibdata* for InnoDB location.

The standard way to copy one database from one computer to another will be to backup using MSQL administrator from the source computer and then restore them onto the destination computer using the similar tool. However, if you have 10 or more databases to transfer, that will be pretty troublesome, isn’t it?

So here, I am going to show you how to move, transfer or copy MYSQL databases from one computer to another in Windows in the quickest way. The only requirement is that you have to be an administrator, so that you can access Document and Settings folder or the Program Files folder.

1. Open Window Explorer (Windows Key + E) or Open My Computer at the source computer
2. Depending on where you have set your data folder during the MYSQL installation, you will have to go that same location. If you do not know where it is, read How to find MYSQL database files in Windows.
3. Once you located the data folder, copy all the folders and the ibdata1 file. DO NOT COPY THE ib_logfile(x) files!
4. Go over to the target computer, Open System Services. (Type services.msc at the Windows menu “Search Program and files” entry box)
5. On the right panel of the Services window, scroll along the list and look for the name “MYSQL“. Select MYSQL and right mouse click and click <STOP>. Once it has stopped, you will it’s status become blank instead of <Started>.
6. Similarly look for the location of the data folder. Once you located it, I recommend you back up the folder or just rename it. Paste over the folders and ibdata file you have copied from the source computer to the data folder.
7. Go back to the Services window and select back MYSQL. Right mouse click on it and click <Start> to resume the MYSQL service.
8. Open MYSQL administrator and log on to your database server and you should be able to see your transferred database together with their data.