Move MySQL database to new server
We had a power outage and when the power was restored the MySQL service would not start. After numerous attempts to uninstall/reinstall MySQL without success we installed MySQL on a new server. We have a file back up of all the .frm files but I am not sure how to attach them on the new MySQL server. I put the DBs in the data directory on the new server and they show up in WorkBench but when you click to open the tables it says it does't exist.
Attached are two screen shots:
1. DB Overview
2. Output from clicking on one of the tables.
My question is: How do I move the MySQL databases to a new server? Please note I am very new with MySQL so detailed directions are much appreciated.
Windows 2003 R2
MySQL 5.1.47
Workbench 5.2.35.1
Install Dir: C:\Program Files\MySQL
Data Dir: C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data
Thank You!
Attached are two screen shots:
1. DB Overview
2. Output from clicking on one of the tables.
My question is: How do I move the MySQL databases to a new server? Please note I am very new with MySQL so detailed directions are much appreciated.
Windows 2003 R2
MySQL 5.1.47
Workbench 5.2.35.1
Install Dir: C:\Program Files\MySQL
Data Dir: C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data
Thank You!
Did you put the .myd/.myi/.frm files into the database folders exactly as they were on the old server?
ASKER
Thank you, xterm, for the quick response. The files were not in the exact directory structure as they were on the old server but they are now.
I also edited the my.ini file to point to the directory structure that I re-created.
Unfortunately, it did not fix my issue.
At this point the my.ini file on both the old and new server are now exact except for on the new server the innodb_data_home_dir is specified as C:\Data and the old server does not specify anything.
I can see the tables in workbench but it won't read the data. Is there a script to point that database to its data? Thanks!
I also edited the my.ini file to point to the directory structure that I re-created.
Unfortunately, it did not fix my issue.
At this point the my.ini file on both the old and new server are now exact except for on the new server the innodb_data_home_dir is specified as C:\Data and the old server does not specify anything.
I can see the tables in workbench but it won't read the data. Is there a script to point that database to its data? Thanks!
Just to be clear here, for each of these tables, you didn't just back up the .frm files correct? Those are just the table definitions.
AKA, for each database you have a table.frm, a table.MYI, and a table.MYD file for each table in the directory called databasename. Yes?
Finally, the old server that crashed - was it also a Windows OS, or did it come from a Unix machine?
AKA, for each database you have a table.frm, a table.MYI, and a table.MYD file for each table in the directory called databasename. Yes?
Finally, the old server that crashed - was it also a Windows OS, or did it come from a Unix machine?
ASKER
xterm, that makes perfect sense, I didn't realize there were other files. Unfortunately, there were only .frm files in those database directories.
It was a windows 2008 server.
I have all the program files, program data directories still intact, I just added an '_old' at the end of the folder name. Let me check the back up and see if it has those files and I will let you know what I find out.
Thanks!
It was a windows 2008 server.
I have all the program files, program data directories still intact, I just added an '_old' at the end of the folder name. Let me check the back up and see if it has those files and I will let you know what I find out.
Thanks!
ASKER
Hey xterm, looks like the MySQL program data directory was not being backed up. We have pretty much accepted the fact that the data is probably gone. Not a big deal, there wasn't that much data in those databases. I do have a couple of questions, if you don't mind answering them.
1. Have you ever heard of the myd files disappearing after data corruption/server crash/etc...? We lost power and when the server came back online those myd files were gone. The OS is still intact.
2. MySQL will not re-install. I have un-installed, deleted the MySQL directories, deleted the registry settitngs and the install locks up when attempting to start the service. Do you know of any tricks to fix this?
Thanks!
1. Have you ever heard of the myd files disappearing after data corruption/server crash/etc...? We lost power and when the server came back online those myd files were gone. The OS is still intact.
2. MySQL will not re-install. I have un-installed, deleted the MySQL directories, deleted the registry settitngs and the install locks up when attempting to start the service. Do you know of any tricks to fix this?
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Solution would have to restore the database but it was not being back up.