LinuxQuestions.org
Register a domain and help support LQ
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 03-08-2010, 02:47 AM   #1
elainelaw
Member
 
Registered: Jan 2008
Posts: 258

Rep: Reputation: 30
Copy mysql database


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
 
Old 03-08-2010, 02:57 AM   #2
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 23,066
Blog Entries: 11

Rep: Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910
Depends on a variety of things; in doubt export/import is the cleanest
choice.
 
Old 03-08-2010, 03:14 AM   #3
evo2
LQ Guru
 
Registered: Jan 2009
Location: Japan
Distribution: Mostly Debian and Scientific Linux
Posts: 5,753

Rep: Reputation: 1288Reputation: 1288Reputation: 1288Reputation: 1288Reputation: 1288Reputation: 1288Reputation: 1288Reputation: 1288Reputation: 1288
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.
 
Old 03-08-2010, 04:47 AM   #4
elainelaw
Member
 
Registered: Jan 2008
Posts: 258

Original Poster
Rep: Reputation: 30
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

Last edited by elainelaw; 03-08-2010 at 04:49 AM.
 
Old 03-08-2010, 05:04 AM   #5
evo2
LQ Guru
 
Registered: Jan 2009
Location: Japan
Distribution: Mostly Debian and Scientific Linux
Posts: 5,753

Rep: Reputation: 1288Reputation: 1288Reputation: 1288Reputation: 1288Reputation: 1288Reputation: 1288Reputation: 1288Reputation: 1288Reputation: 1288
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.
 
Old 03-09-2010, 10:25 AM   #6
elainelaw
Member
 
Registered: Jan 2008
Posts: 258

Original Poster
Rep: Reputation: 30
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
 
Old 03-09-2010, 11:15 AM   #7
freelinuxtutorials
Member
 
Registered: Oct 2009
Posts: 70

Rep: Reputation: 21
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
 
Old 03-09-2010, 11:18 AM   #8
jamescondron
Member
 
Registered: Jul 2007
Location: Scunthorpe, UK
Distribution: Ubuntu 8.10; Gentoo; Debian Lenny
Posts: 961

Rep: Reputation: 69
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

Last edited by jamescondron; 03-09-2010 at 11:19 AM. Reason: Fix [/code] tag
 
Old 03-09-2010, 07:53 PM   #9
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.8, Centos 5.10
Posts: 17,240

Rep: Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324Reputation: 2324
@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.
 
Old 03-10-2010, 09:40 PM   #10
elainelaw
Member
 
Registered: Jan 2008
Posts: 258

Original Poster
Rep: Reputation: 30
Quote:
Originally Posted by jamescondron View Post
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
 
Old 03-10-2010, 10:00 PM   #11
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 23,066
Blog Entries: 11

Rep: Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910
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.
 
Old 03-10-2010, 10:13 PM   #12
elainelaw
Member
 
Registered: Jan 2008
Posts: 258

Original Poster
Rep: Reputation: 30
Quote:
Originally Posted by Tinkster View Post
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
 
Old 03-10-2010, 10:19 PM   #13
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 23,066
Blog Entries: 11

Rep: Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910
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.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy MySQL database with PHPMyAdmin....how?!?!? vous Linux - Software 1 09-08-2009 12:18 PM
How can i copy the MySQL database? halvorls Linux - Server 3 08-14-2008 01:59 PM
How can i copt the MYSQL Database to a other server (Database) halvorls Linux - Server 3 07-27-2008 10:19 PM
copy one mysql database into another Peter Shepard Ubuntu 1 02-11-2008 07:29 PM
How do I make a copy of mysql database intale(located) on linux server jenny1668 Linux - Newbie 1 10-02-2006 08:02 AM


All times are GMT -5. The time now is 09:28 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration