Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

Move MySQL Databse from Command Line

I have full root command-line access to Server A and Server B.  Both servers are running Linux (CentOS 5).

I have a MySQL database on Server A that is more than 20 gig in size.

How can I move it from a command line to server B?
0
hankknight
Asked:
hankknight
3 Solutions
 
tahussleCommented:
its really straight forward  follow the guide below

http://www.webmasterworld.com/forum49/723.htm


to copy the database to the new server you can use scp
http://infohost.nmt.edu/tcc/help/xfer/scp.html

or ftp or whichever file transfer you want to use

could also use winscp if connecting to the servers from a windows machine

http://winscp.net/eng/index.php
0
 
jeff_01Commented:
Backup the current database by running
"mysqldump -u mydatabaseuser -p mydatabase > backup.sql"

Since your file will be quite big you should use gzip to compress it before copying it over to the new server.
"gzip backup.sql"

Unzip the compressed file on the new server
"gunzip bckup.sql.gz"

Then create the new database that you wish to import the backup into and then run
"mysql -u mydatabaseuser2 -p mydatabase2 < backup.sql"

Hope this helps





0
 
bman21Commented:
another way of doing this is to use rsync over ssh if you have those technologies in place already.  I currently use this same setup for backing up my databases.  You have to be careful as rsync will copy a table file even if it is currently being updated.  In other words, you could get partial or incomplete tables.  I would suggest shutting down the database if you decide to use this method of transfer.

Below is the command line that you will want to use:

cd /SERVER_A_MYSQL_DIR
rsync -avuzb -e ssh SERVER.B.IP_ADDRESS:/PATH/TO/SERVER_B_MYSQL_DIR .

Once the transfer is complete, restart Server B's mysql instance.  You should then see your tables with data in them after loggin into that Database.

For more information on the switches for rsync, run "man rsync"

Hope this helps.  I would suggest that you do it as mentioned above as it is mysql's recommended method of backing up/moving databases.  But if you are comfortable with shutting the DB down till the above command completes I would use this method.    
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now