Mysql Replication using SSH Tunneling

 in Blog / Mysql  tagged mysql / replication / ssh tunneling by

Contents

  • Mysql Master – Slave Replication Using SSH Tunneling

    • Installation/Configuration of Mysql Master

    • Installation/Configuration of Mysql Slave

    • Configuring SSH Tunneling

SSH tunneling is one of the option to securely replicate the data between mysql master and slave. The documentation is based on Centos 5.5. However, this will work with other operating systems also. Also, as there are many docs available in the internet about mysql replication, so I'm not going to explain in detail other than mentioning the important points.

Lets consider the name as “Mercury” for our mysql master and “Saturn” for our mysql slave.

Installation/Configuration of Mercury

  • yum install -y mysql-server

  • Update the following values in /etc/my.cnf

    • server-id = 1

    • log_bin

    • expire_logs_days = 10

    • binlog-do-db=<database name> # input the database which should be replicated

    • binlog-ignore-db=mysql # input the database that should be ignored for replication

    • binlog-ignore-db=test

If binary logging is not enabled, replication will not take place. Enabling binary logging requires huge disk space. Inorder to remove the old logs, we have to enable expire_logs_days. Our current setting will flush the logs which are older than 10 days.

  • Now we have to create a user account for replication. Login to mysql terminal and execute:

    • mysql> grant replication slave on *.* to 'replication'@'localhost' identified by 'slave';

    • When using ssh tunneling, you have to use hostname as  “127.0.0.1”. Do not use hostname as "localhost" as it might not work properly with mysql.

  • Restart the mysql server

Now, take a db dump of the database which needs to be replicated. Lets say our db name is “universe” . Login to mysql terminal and execute

  • USE universe;

  • FLUSH TABLES WITH READ LOCK;

  • SHOW MASTER STATUS;

The output should look something like this:

+------------------+--------------------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+--------------------------+------------------+
| mysql-bin.000001 |       103 | universe     | mysql            |
+------------------+--------------------------+------------------+
1 row in set (0.00 sec)

Don't exit from the mysql terminal. Take another ssh terminal and take a db dump.

  • mysqldump –database universe > universe.sql

  • Exit from mysql terminal and copy the db dump to slave server.

CONTINUE TO PART-2