Reference Information › CA AppLogic Support Knowledge Base › Overview of Support Knowledge Base › Database Replication with MySQLR Appliances using VPN Tunnel

Database Replication with MySQLR Appliances using VPN Tunnel

Database replication can be setup in Applogic using 2 applications (call them Master and Slave). The idea is to replicate any changes in Master dababase to the database in Slave application and keep all data in sync in both databases.

1. Master application can be built as follows:
   • Create application called Master and drag in VPN, MySQLR and mon appliances to it. Connect "srv" terminal of VPN with "rin" terminal of MySQLR appliance.
   • Then connect "mon" terminals of VPN and MySQLR appliances with "mon" terminal of mon appliance.
   • In Property Values of MySQLR specify "rpl_mode" = master, server_id = 1, web_pwd = <ui_passwd>
   • In Property Values of VPN provide IP, netmask, gateway; mode = server, tunnel = ipsec shared secret (this is the option to use VPN tunnel), auth_pat = secret.key, tcp_ports = 0, udp_ports = 0 aux_protocols = all

     log_level = debug
     

   Note:: You must create a file called secret.key on VPN appliance in /mnt/data/server directory. I created this file with following contents:

   "b1e1a632d4661bfbe5f8cff77f1a1c85. This is my secret key! This is my secret key! This is my secret key!" (this is what it was on customer's appliance).

   If tunnel = "ipsec shared secret" then without this "secret.key" file application will not start.

2. Now create application Slave as follows: In application called Slave drag MySQLR, VPN and mon appliances into the canvas. Connect "rout" of MySQLR to "clt" of VPN. Then connect "mon" of MySQLR and VPN with "mon" of mon appliance.

   In Property Values of MySQLR specify "rpl_mode" = slave, server_id = 2, web_pwd = <ui_passwd>

   In Property Values of VPN provide IP, netmask, gateway; mode = client, tunnel = ipsec shared secret (this is the option to use VPN tunnel), auth_pat = secret.key, tcp_ports = 0, udp_ports = 0 log_level = debug, remote_host = <ip_addr of VPN appliance from Master application>

   Note: Here as well you must create a file called secret.key on VPN appliance in /mnt/data/server and in /mnt/data/cleint directories. I created this file with following contents:

   "b1e1a632d4661bfbe5f8cff77f1a1c85. This is my secret key! This is my secret key! This is my secret key!" (this is what it was on customer's appliance).

   If tunnel = "ipsec shared secret" then without this file application will not start.

Start both applications (Master first, then Slave). You should be able to open SQL UI in both applications - click on MySQLR appliance and click on "login (web)" icon. To login leave user blank and use password that you defined before in Property Values (<ui_password>). In MySQLR in Slave application "MYSQL Status" and "MYSQL Replication" status should be green, OK. In MySQLR on Master "MYSQL Status" will be green and "MYSQLR Replication" will be red, because it is not connected to a master.

Database replication should be now working.

To verify this you can create a table in MySQLR appliance in Master application:

1. Right-click MySQLR appliance in Master and select "Login". In ssh window type "mysql;" - this will open sql prompt.
2. Type "show databases;" - this will list all databases; database "test" should be one of them.
3. Type "use test;" - this will select database "test" to use
4. Type "CREATE TABLE XXX (ID INT(5),name VARCHAR(30));" - this will create new table with name XXX
5. Type "show tables" - table XXX should be listed.
6. Now right click MySQLR appliance in Slave and select "Login". In ssh window type "mysql;"
   1. Type "show databases;" - all tables will be listed (test should be one of them)
   2. Type "use test;"
   3. Type "show databases;" - table XXX should be listed (it will be replicated from Master).
7. Now go back to Master MySQLR appliance and type "drop table XXX;" and then type "show tables;" - table XXX should not be listed
8. Now in Slave MySQLR appliance type "show tables;" - table XXX should not be listed (since it was removed on Master, as a result of replication it should also disappear on Slave appliance).

Databases may go out of sync. This happened in my case when I created new table (for example "TABLE1") in Master appliance and it was replicated on Slave. Then I restarted both applications.

So in MySQLR on Master type: "CREATE TABLE TABLE1 (ID INT(5),name VARCHAR(30));" then restart both applications

After both apps were running in SQL UI on Slave appliance I saw error that table TABLE1 already exists and MYSQLR Replication status was red; replication stopped working. I created new tables on Master, but they were no longer replicated to Slave.

To correct this and re-sync databases I ran the following steps:

• On Master MySQLR:
  • Run "RESET MASTER;"
  • Run "FLUSH TABLES WITH READ LOCK;"
  • Run "SHOW MASTER STATUS;" and write down values of "File" and "Position" from the output
  • Without closing connection to a client run "drop table TABLE1;" (alternatively copy entire database to a file by running: "mysqldump -uroot -all -databases > /<full path>/mysqldump.sql" - I didn't run
  • this step because in my case only one table (TABLE1) was causing the problem, so I just deleted it from Master and from Slave).
  • Run "UNLOCK TABLES;" after that scp file mysqldump.sql (if you created it) to Slave MySQLR appliance.
• On Slave MySQLR:
  • Run "STOP SLAVE;"
  • Run "drop table TABLE1;" (alternatively, if mysqldump.sql file was sent in step 5 above, run this: "mysql -uroot -p < mysqldump.sql") - I only ran "drop table TABLE1;"
  • Run "RESET SLAVE;"
  • Run "CHANGE MASTER TO MASTER_LOG_FILE='<File>', MASTER_LOG_POS=<Position>;" where File and Position are output values from step 3 run earlier on Master
  • Run "START SLAVE;"
  • Run "SHOW SLAVE STATUS;"
  • Output should show "Slave_IO_Running: Yes" and "Slave_SQL_Running: Yes"
  • In SQL UI on Slave MYSQLR Replication should show status green.

Now database replication should work again - it can be tested as described above.