MySQL

Currently we are doing replication between two servers i.e. VPN3 and VPN4. Our project needed persistent backup of the business critical database on another server as we don’t want to loose any data while server goes down. So we have implemented below mentioned steps. We are using MySQL 5.1 version of mysql. Any higher version is also suitable, as performance of replication has been increased in MySQL 5.6 and above. So below mentioned steps are tested successfully on MySQL5.1, however the same steps might work for other version, that you need to sort out.

  • Step 01: Add my.cnf files as mentioned in the /etc/my.cnf (this holds several mysql environment variables and values for smooth performance of the mysql).Run below queries on both the server to create a user which will handle replications between these two servers.
    mysql > create user ‘replicator’ identified by ‘replicator’;
  • Step 02: Once added these files according to server replication architecture you are following.Reboot the services of mysqld to take effect of changes.
    mysql> Service mysqld restart
    on all server you want to do replication settings.(Currently we are doing between two servers, but cyclic replication can be done between multiple servers as per project need).
  • Step 03: Create GRANT access for replication user on each server i.e. VPN3 and VPN4 On VPN3 run following command on mysql CLI
    MYSQL>GRANT REPLICATION SLAVE ON *.* TO ‘replicator’@’10.3.140.70’ IDENTIFIED BY ‘replicator’;
    MYSQL> FLUSH PRIVILEGES;
    On VPN4 run following command on mysql CLI
    MYSQL> GRANT REPLICATION SLAVE ON *.* TO ‘replicator’@’10.34.188.148’ IDENTIFIED BY ‘replicator’;
    MYSQL> FLUSH PRIVILEGES;
    You can verify the replication user added on mysql by running following commands.
    mysql> select user,host from mysql.user;
    Also verify the permissions status by running below command
    mysql> select * from mysql.user where user=’replicator’;
  • Step 04: Run mysql query on VPN4 server
    MYSQL> SHOW MASTER STATUS;
    +——————+———-+————–+——————+
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +——————+———-+————–+——————+
    | mysql-bin.000002 | 35271224 | | |
    +——————+———-+————–+——————+
    1 row in set (0.00 sec) and then run below mentioned mysql command on slave i.e. on VPN3 server with above mentioned details of File and Position in it.
    MYSQL> CHANGE MASTER TO
    MASTER_HOST=’192.168.1.21′,
    MASTER_PORT=3306,
    MASTER_USER=’replicator’,
    MASTER_PASSWORD=’replicator’,
    MASTER_LOG_FILE=’mysql-bin.000002′,
    MASTER_LOG_POS=35271224;
  • Step 05: Run mysql query on VPN3 server
    MYSQL> SHOW MASTER STATUS;
    +——————+———-+————–+——————+
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +——————+———-+————–+——————+
    | mysql-bin.000003 | 37416567 | | |
    +——————+———-+————–+——————+
    1 row in set (0.00 sec) and run this mysql command on slave i.e. on VPN4 server with above mentioned details of File and Position in it.
    MYSQL> CHANGE MASTER TO
    MASTER_HOST=’192.168.1.21′,
    MASTER_PORT=3306,
    MASTER_USER=’replicator’,
    MASTER_PASSWORD=’replicator’,
    MASTER_LOG_FILE=’mysql-bin.000003′,
    MASTER_LOG_POS=37416567;
  • Step 06: Run mysql query on VPN4 server
    MYSQL> START SLAVE;
    Wait 5 seconds , then on Server VPN4
    MYSQL> SHOW SLAVE STATUS\G
    If Slave_IO_Running=Yes and Slave_SQL_Running=Yes, replication is working on VPN4
  • Step 07: Run mysql query on VPN3 server
    MYSQL> START SLAVE;
    Wait 5 seconds,then on Server VPN3
    MYSQL> SHOW SLAVE STATUS\G
    If Slave_IO_Running=Yes and Slave_SQL_Running=Yes, replication is working.

Now replication is successfully set on both the servers. Changes from one server will automatically copied to another mysql server. Changes always flow from Master Server to Slave Server. As this is asynchronous process so there is slight delay in the copy of data from one server to another server. Generally this process is used for persistent backup of the DB to another server. Since we have created both servers as Master and Slave as well. So any changes on server VPN4 will be reflected back to VPN3 and vice versa.