Copy mysql database

elainelaw · 03-08-2010, 01:47 AM

I want to migrate a mysql databases from a old server to a new server ( Redhat ) , can advise what I need to do ,is it just copy all .frm , .MYI & .MYD files to new server will do ? or I need to do export and inport data ? thx

Tinkster · 03-08-2010, 01:57 AM

Depends on a variety of things; in doubt export/import is the cleanest
choice.

evo2 · 03-08-2010, 02:14 AM

Unless you are running the exact same mysql version I'd imagine you could get incompatibility issues just by copying the raw files. For that reason I'd use mysqldump.

Evo2.

elainelaw · 03-08-2010, 03:47 AM

thx replies,

I only found some .frm , .MYI , .MYD in /var/lib/mysql/mysql directory , I would like to ask is that mean there is only a mysql db in the system ? if yes, the db name is "mysql" ? thx

evo2 · 03-08-2010, 04:04 AM

The mysql database in a mysql server is the database that is used to administer user accounts and other information that is internal to a mysql server. If that is the only directory in /var/lib/mysql, then I would say yes, it seems that there are no other databases.... not a very useful mysql server.

Since this is a database that is internal to mysql itself, I would be hesitant to blindly copy it to another installation.

Evo2.

elainelaw · 03-09-2010, 09:25 AM

thx reply ,

I want to migrate the mysql db from Debian to Redhat , the best medhod is export the data and then import to it ? can I use phpmyadmin to do all the migration steps ? thx

freelinuxtutorials · 03-09-2010, 10:15 AM

you can use phpmyadmin for those things. but I still recommend mysqldump.And to get a consistent backup, you need to lock the tables first,then fire mysqldump

jamescondron · 03-09-2010, 10:18 AM

you want mysqldump for that, you should have it installed. Easy peasy

Code:

mysqldump -u DBUSER -p DBNAME > DBNAME.sql

and

Code:

mysqlimport

The man pages for both will give you more in depth information

chrism01 · 03-09-2010, 06:53 PM

@freelinuxtutorials: mysqldump does the locks as reqd.
@OP: only dump/import app schems, not the metadata eg mysql schema, unless the DB versions are the same; even then it may have issues unless the 2 systems are identical.
What will be reqd is to use SHOW GRANTS to get a list of users and access perms.

elainelaw · 03-10-2010, 08:40 PM

Quote:

Originally Posted by jamescondron

you want mysqldump for that, you should have it installed. Easy peasy

Code:

mysqldump -u DBUSER -p DBNAME > DBNAME.sql

and

Code:

mysqlimport

The man pages for both will give you more in depth information

I found the below command to export all database by one time .

To dump all databases, use the --all-databases option:
shell> mysqldump --all-databases > all_databases.sql

I also try to use mysqlimport to import it to another server , but it pops the database is not available , I need to create all databases before run mysqlimport ? if no need , is there method that is easy import the databases ? thx

Tinkster · 03-10-2010, 09:00 PM

You didn't mention which version(s) of mysql and its command-line
tools you're using, so it's impossible to answer your question.

elainelaw · 03-10-2010, 09:13 PM

Quote:

Originally Posted by Tinkster

You didn't mention which version(s) of mysql and its command-line
tools you're using, so it's impossible to answer your question.

I use Redhat EL 4 , mysql 4.0

export command
===============
mysqldump testdb > testdb.sql

import command
==============
mysqlimort testdb testdb.sql

error :
Error: Table 'testdb.testdb' doesn't exist

I perfer use --all-databases to export all databases by one time and load into it .

Can advise what can i do ? thx

Tinkster · 03-10-2010, 09:19 PM

I'd look in the generated testdb.sql file if it has statements to
create a DB as well as tables. I don't have access to such ancient
versions of MySQL, so can't test this.