Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Where the mysql tables are stored in windows if i want to get their backup?

Posted on 2009-04-07
16
Medium Priority
?
398 Views
Last Modified: 2012-05-06
i have two hard disks. now i want to get/export/ import all the tables of mysql of previous hard disk and use them in freshly installed mysql in new windows.
0
Comment
Question by:designersx
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 2
16 Comments
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24087679
Is the mysql server running for the old data? Or do you have the old database on a "slave" disk only, not connected to a running mysql server?
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24087697
If you have a running mysql server for the old data, you can use phpMyAdmin or mysqldump utility to create a dump file.

http://www.phpmyadmin.net/
http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24087756
To import the files into the new server, use phpMyAdmin or from the windows command line (Run -> cmd):

mysql -uroot -p database < database_dump.sql

You may need to cd to your mysql directory first, if not the mysql/bin directory is in your path.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:designersx
ID: 24087884
i have installed mysql server on my new windows.

my previous database tables are now in    d:\wamp\bin\mysql\mysql5.0.51b\data  so i which path i should go exaactly, please tell me

yogesh
0
 
LVL 9

Expert Comment

by:LinuxNubb
ID: 24088039
designersx, want cxr is saying, is that even if you point it to the correct directory you will not get a good backup.  If mysql is running, you cannot back the files up because they will be locked.  You need to create backups of those files through the dump utilities and back up those dump files.
0
 

Author Comment

by:designersx
ID: 24088138
LinuxNubb: i didn't get u?
0
 
LVL 9

Expert Comment

by:LinuxNubb
ID: 24088188
You cannot back up open files.  If you try to back up the open mysql database files, you will get errors.

If you create copies of the database files as cxr describes above, you will get good backups.  You can create those files anywhere you like and can back them up correctly and successfully.
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24088650
In the data folder you will find one folder for each database. Within the dabase folders are all myisam tables, but not innodb tables! If you have only used myisam tables, you can try to copy the database folder to the data folder of the new mysql server. The new server should recognize them immediately, no configuration needed.

The old version was 5.0.51b, which version has the new server?

If you have used innodb files, it becomes more difficult.
0
 

Author Comment

by:designersx
ID: 24103671
would u please explain this statement.

mysql -uroot -p database < database_dump.sql

what is database_dump.sql  ?
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24103737
>> what is database_dump.sql  ?

That would be the name of the dump file created by phpMyAdmin or mysqldump. To be able to create a dump file, the database must be running.
0
 

Author Comment

by:designersx
ID: 24103965
my all database tables are kept safe  in this path    D:\wamp\bin\mysql\mysql5.0.51b\data    (this is the d:

of my new windows but actually it was the c: drive of other hard disk.)  now i want all of them into

C:\wamp\bin\mysql\mysql5.0.51b\data   how i should do , i am unable to run the above command

mysql -uroot -p database < database_dump.sql

what i am doing is :- in windows command line,

c:\wamp\bin\mysql\mysql -uroot -p database < database_dump.sql  

it is giving error, the system cannot find the file specified.
0
 

Author Comment

by:designersx
ID: 24103998
how do i come to know that i am using innodb tables or myisam tables?
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24104002
Assuming the database server is not running for the old mysql, you can try to copy the old databases to the new mysql server. I don't know if you have many databases you wish to restore? Try it with one database first: below the old data folder, you will find a folder with the name of the database. Copy this folder to the data folder of the new mysql. This only works if all tables are myisam, because innodb tables are stored in a separate data storage file, and this is more complicated to copy to a new server. It might not work if the mysql versions are different. The old one was 5.0.51b, what version is the new install?
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 24104045
Same version, thats good. Lets hope it is not innodb. You can find that out by doing this from the command line:

dir D:\wamp\bin\mysql\mysql5.0.51b\data\OldDatabase

If there are three files for each table, *.frm, *.myd and *.myi files, then this is myisam tables. If you only have *.frm files, it is innodb, and the data is stored in a different file.

Reading data from the database_dump.sql dump file requires that you have such a dump file, and you don't.

To copy the database, do this from the windows command line:

copy D:\wamp\bin\mysql\mysql5.0.51b\data\OldDatabase C:\wamp\bin\mysql\mysql5.0.51b\data\NewDatabase

...where "OldDatabase" is the name of the old database and "NewDatabase" is the name you want to have for this database on the new server. If you use a name that allready exists on the new server, it will overwrite any existing tables in the new database server.
0
 

Author Comment

by:designersx
ID: 24104116
my versions are same.

it is showing only frm files and 1 opt file. i think it is innodb.
with this,

copy D:\wamp\bin\mysql\mysql5.0.51b\data\OldDatabase C:\wamp\bin\mysql\mysql5.0.51b\data\NewDatabase

1 file is copied but the complete folder is not shown that had 3 tables.
i think i have lost the tables.

this is really wrong. it should not be like this.


yogesh
0
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 200 total points
ID: 24105992
The tables are not lost. You can copy the innodb table-spaces from the old database to the new server. It's just a more complicated process, compared to myisam tables.

Have you started using the new mysql server, have you inserted any data into it? If yes, take a backup using the mysqldump utility. Verify that the backups are good: check file size, and check content by loading the file into a text editor. The dump file contains CREATE TABLE and INSERT statements for all your tables.

Shut down the new server, i.e. stop the mysql server service. From windows command line: net stop mysql

Check configuration to find where innodb table-space is stored. Check these files:

D:\wamp\bin\mysql\mysql5.0.51b\my.ini
C:\wamp\bin\mysql\mysql5.0.51b\my.ini

Look for innodb_data_home_dir and innodb_data_file_path, this shows where the innodb table-space is stored. There could be one or more innodb-files on both disks.

In the data folder for both servers, locate the innodb log files. They are named ib_logfile0, ib_logfile1, ib_logfile2 and so on.

The *.frm files must also be copied from the old disk, but I guess you have allready done this? If so, rename the innodb files on C: so that you have a backup of these. Then copy the innodb files from D: to the correct location on C:, both the table-space files (often named ibdata*) and the ib_logfile* files.

Then start the server with: net start mysql
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to create an extensible mechanism for linked drop downs.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

715 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