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

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.
designersxAsked:
Who is Participating?
 
Roger BaklundConnect With a Mentor Commented:
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
 
Roger BaklundCommented:
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
 
Roger BaklundCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Roger BaklundCommented:
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
 
designersxAuthor Commented:
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
 
LinuxNubbCommented:
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
 
designersxAuthor Commented:
LinuxNubb: i didn't get u?
0
 
LinuxNubbCommented:
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
 
Roger BaklundCommented:
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
 
designersxAuthor Commented:
would u please explain this statement.

mysql -uroot -p database < database_dump.sql

what is database_dump.sql  ?
0
 
Roger BaklundCommented:
>> 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
 
designersxAuthor Commented:
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
 
designersxAuthor Commented:
how do i come to know that i am using innodb tables or myisam tables?
0
 
Roger BaklundCommented:
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
 
Roger BaklundCommented:
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
 
designersxAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.