We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Move MySQL data?

lee88
lee88 asked
on
Medium Priority
334 Views
Last Modified: 2012-05-06
I have been asked to repair a MySQLServer instance. My understanding is that the associated executables are on the Windows XP C: drive, but the data was on the E: drive. The E: drive failed. However, I have what I believe to be a backup copy of the data (a single file named "ibdata1"), which someone copied to a folder on the C: drive just before the E: drive failure. My questions are:
1.  Is it likely that the "ibdata1" file IS the SQL data? Does "ibdata1" sound like a default-name, and is a small SQL dataset stored in a single file?
2. Is there a way to see (edit) the configuration? I am hoping that I can just change a pointer to point to this data file. I found MySQLInstanceConfig.exe, but it prompts me saying it will over-write the current config. I would just like to view the current cofig (location of data).

Obviously, I have never worked with MySQL. Any help / suggestions sincerely appreciated.

Comment
Watch Question

UmeshSenior Principal Technical Support Engineer
Top Expert 2009

Commented:
1.  Is it likely that the "ibdata1" file IS the SQL data? Does "ibdata1" sound like a default-name, and is a small SQL dataset stored in a single file?

>>  that's Innodb data file but you need innodb log file (ib_logfile0 and ib_logfile1) & all the /databasename/*.frm files in order to restore.. only datafile is not enough..  may be you can try something which is suggested here

http://forums.mysql.com/read.php?156,163884,165472#msg-165472

and go thru reply from Marko Mäkelä (http://forums.mysql.com/read.php?156,163884,165472#msg-165472)

2. Is there a way to see (edit) the configuration? I am hoping that I can just change a pointer to point to this data file. I found MySQLInstanceConfig.exe, but it prompts me saying it will over-write the current config. I would just like to view the current cofig (location of data).

Pls don't try this with innodb data file.. you would end-up corrupting the file

Author

Commented:
Thanks for the response.

I found the ib_logfile0 and ib_logfile1 files located on the good C: drive in this folder:
 - C:\Program Files\MySQL\MySQL Server 5.0\data
The ib_logfile0 file is dated 02-06-2009 which is 4 days after the last new data was added.
The ib_logfile1 file is dated 12-01-2006 which is probably when MySQL was installed?

I also found /databasename/*.frm (and other files) located in this folder:
 - C:\Program Files\MySQL\MySQL Server 5.0\KioskActivity
which I do think is the active application folder. (Not sure why the ibdata1 file was on the failed E: drive).
The newest file in this folder is dated 02-02-2009 which is when the last new data was added.

With the above info, can I somehow tell MySQL how to find my newly-relocated ibdata1 file?
Senior Principal Technical Support Engineer
Top Expert 2009
Commented:
This should help you in restoring ( it's shown in linux but all steps should work for windows too.. only need to change path in windows for mysqld)

http://www.buildcms.com/cms_knowledgebase/how_to_recover_mysql_data_from_innodb

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Excellent. Thank you.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.