Thursday, September 19

How to Migrate Mysql Database from one Server to another server


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

Server1 machine  Type this Command

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

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

        squser -> Mysql-user of server1

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

        newdatabase1 -> database name
        mahesh -> mysql-User of Remoteserver(server2) -> 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 ... 


No comments:

Post a Comment

:: Linux - Legends ::