Move MySQL Databse from Command Line

Posted on 2009-12-21
Last Modified: 2012-05-08
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?
Question by:hankknight
    LVL 5

    Assisted Solution

    its really straight forward  follow the guide below

    to copy the database to the new server you can use scp

    or ftp or whichever file transfer you want to use

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

    Accepted Solution

    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

    LVL 2

    Assisted Solution

    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:


    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.    

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Little introduction about CP: CP is a command on linux that use to copy files and folder from one location to another location. Example usage of CP as follow: cp /myfoder /pathto/destination/folder/ cp abc.tar.gz /pathto/destination/folder/ab…
    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…
    Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now