How to Migrate Mysql Database from one Server to another server

Mysql:

MySQL is most commonly used for Web applications and for  embedded  applications  and  has become a popular alternative to proprietary database systems because of its speed  and reliability. MySQL can run on LINUX, Windows and Mac OS.

          By default remote access to the MySQL database server is disabled for security reasons.
However, some time you need to provide remote access to database server from home or a web server.

      To Take Backup and Restore MYSQL Database using mysqldump command in Local Machine
 
In this Concept we are use 2 servers ,  server1 and server2

Our task is Copy Database from server1 to server2



Step1 : setup a user account and access mysql server remotely on Server2


            See this link to  how to setup a user account and access mysql server remotely.


Step2 : checking Remote Access from Server2 to Server1

   In server1 Machine:
  
      First checking Remote Access of Server2

          $ mysql -u mahesh -h "remote-server-ipaddress" -p

              Ask Mysql user Password (enter password)


Step3 : Create Database name in Server2 Machine

   Create Same database name (which database migrate)

          mysql> create database "databasename";
   Check database 

         mysql> show databases;


Step4 : Migrate Database from Server1 to Server2

   Using Mysqldump Command Copy database from one server to another

  In Server1 machine  Type this Command

  #mysqldump -u user -p password databasename | mysql -uremoteuser  -h server2-IPaddress -premotepassword databasename
 
  Example:

   
[root@system22 ~]# mysqldump -u squser -p mysql123 newdatabase1 | mysql -u mahesh -h 192.168.0.93 -p mahesh123 newdatabase1

  Where
        squser -> Mysql-user of server1

        mysql123 -> Password of mysql-User(squser) in server1

        newdatabase1 -> database name
     
        mahesh -> mysql-User of Remoteserver(server2)

        192.168.00.93 -> server2 ipaddress

        mahesh123 -> Password of mysql-User(mahesh) in server2      

Step5 : Checking Database

    Now Check Your Copied Database in Server2 Machine

     $ mysql  -u username -p

    mysql> show databases;

     Your Copied Database is there ...