transferring MySQL databases from one machine to another

Hi

I am trying to migrate a MySQL database from one machine to another but am
not sure how to go about this. I physically copied the .frm files but that
does not seem to work. Does anyone have any ideas how to do this? The
database is not very large (I could actually recreate the database manually)
but I want to know how this is done for future projects. Any help would be
highly appreciated. Thanks.

Navodit wrote:
> Hi
>
> I am trying to migrate a MySQL database from one machine to another but am
> not sure how to go about this. I physically copied the .frm files but that
> does not seem to work. Does anyone have any ideas how to do this? The
> database is not very large (I could actually recreate the database manually)
> but I want to know how this is done for future projects. Any help would be
> highly appreciated. Thanks.
>
>
>
My approach for MyISAM files between identical versions of MySQL is:

First 'flush tables' using MySQL and make sure the system is quiescent -
shutting down the server is a good idea.

I usually copy the entire database directory but copying the .MYI
(index), .MYD (data) and .frm (structure) files for each table has worked.

If you can't do the transfer as 'root' chown -R the owner/group and
restore to mysql:mysql at the other end.

That should be sufficient if you're not replacing a table. If you are
replacing a table, the server may have the original files open and will
carry on using them. Fortunately I can shut-down the server while doing
this.

Somewhat slower and safer would be a mysqldump from the remote computer
piped into mysql for the destination computer. I think this should work
with all 'real' engines and may be necessary when moving between MySQL
versions (I used it moving from v5.0 to v4.0 with appropriate switches).
I don't use it currently because I'm mostly transferring packed MyISAM
tables and I want them to stay packed!

Chad

--
Chad Hanna
Systems Developer FamilyHistoryOnline www.familyhistoryonline.net
FreeBSD Apache MySQL Perl mod_perl PHP

"Navodit" <kaushik2@uiuc.edu> wrote:

> I am trying to migrate a MySQL database from one machine to another but am
> not sure how to go about this. I physically copied the .frm files but that
> does not seem to work. Does anyone have any ideas how to do this? The
> database is not very large (I could actually recreate the database manually)
> but I want to know how this is done for future projects. Any help would be
> highly appreciated. Thanks.

Technically this "backup database here" and "restore the backup there".
So please consult the manual on howto backup and restore MySQL data.

http://dev.mysql.com/doc/refman/5.0/...revention.html


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/