Press enter to see results or esc to cancel.


Copy Mysql database from one server to another server

This tutorial shows how to transfer Mysql Database from one server to another server without any software. In this tutorial we are transferring our Local host server database to a server located remotely hosted in freemysqlhosting.net. Click here to see how you can register free to get remote MySql database online.

  1. The image below shows my MySql command line, I have a database adv in this. I am going to transfer this database to remote database.

    The adv database has the tables,

  2. The Remote Mysql info is, You can update it with your server information.

    Host Server: sql12.freemysqlhosting.net
    Database Name: sql12179062
    Username: sql12179062
    Password: avGdiRx5Tm
    Port number: 3306
    
  3. To transfer the Database, The command is as shown. You can run the command from CMD prompt The below command will transfer database1 to database2.

    mysqldump -hhost1 -uusername1 -ppassword1 database1 | mysql -hhost2 -uusername2 -ppassword2 database2
    

    In our example the command will be,

    mysqldump -hlocalhost -uroot -proot adv | mysql -hsql12.freemysqlhosting.net -usql12179062 -pavGdiRx5Tm sql12179062
    
  4. Now I executed the command through CMD as shown below, I navigated to the location from CMD to the location where I installed MySql and executed the command.

    Microsoft Windows [Version 6.1.7601]
    Copyright (c) 2009 Microsoft Corporation.  All rights reserved.
    
    C:\Users\MIRITPC>cd "C:\Program Files (x86)\MySQL\MySQL Server 5.0\bin"
    
    C:\Program Files (x86)\MySQL\MySQL Server 5.0\bin>mysqldump -hlocalhost -uroot -proot adv | mysql -hsql12.freemysqlhosting.net -usql12179062 -pavGdiRx5Tm sql12179062
    
    C:\Program Files (x86)\MySQL\MySQL Server 5.0\bin>
    

    The database copy has completed successfully. It will not show any success message.

  5. Now Lets connect to Remote Mysql database, The command for connecting to remote database is.

    mysql -u username -ppassword -h host databasename
    

    So in our Example,

    mysql -u sql12179062 -pavGdiRx5Tm -h sql12.freemysqlhosting.net sql12179062
    

    I have connected to Remote database and checked database info. All the tables have been migrated. So the database copy is successful.

    Microsoft Windows [Version 6.1.7601]
    Copyright (c) 2009 Microsoft Corporation.  All rights reserved.
    
    C:\Users\MIRITPC>cd "C:\Program Files (x86)\MySQL\MySQL Server 5.0\bin"
    
    C:\Program Files (x86)\MySQL\MySQL Server 5.0\bin>mysql -u sql12179062 -pavGdiRx5Tm -h sql12.freemysqlhosting.net sql12179062
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 17056171 to server version: 5.5.54-0ubuntu0.14.04.1
    
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    
    mysql> show tables;
    +-----------------------+
    | Tables_in_sql12179062 |
    +-----------------------+
    | branch                |
    | countries_flag_code   |
    | news                  |
    | user_table            |
    | vid                   |
    | visiting_ip           |
    +-----------------------+
    6 rows in set (1.68 sec)
    
    mysql>
    

Tags