Solved

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

Posted on 2009-04-07
16
391 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:designersx
Comment Utility
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
Comment Utility
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
Comment Utility
LinuxNubb: i didn't get u?
0
 
LVL 9

Expert Comment

by:LinuxNubb
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:designersx
Comment Utility
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
Comment Utility
>> 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
Comment Utility
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
Comment Utility
how do i come to know that i am using innodb tables or myisam tables?
0
 
LVL 39

Expert Comment

by:Roger Baklund
Comment Utility
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
Comment Utility
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
Comment Utility
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 50 total points
Comment Utility
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 Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
This article discusses four methods for overlaying images in a container on a web page
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now