Go Premium for a chance to win a PS4. Enter to Win

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
?
400 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
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

879 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