VPS Database Replication – Protect your Mysql & MongoDB replication

Posted on Posted in linux, MongoDB, Mysql, seguridad

Long time no write a new post, I’ve been busy with new projects. In one of them a new callenge appeared….

The infraestructure consisted in two VPS hosted in different datacenters without private comunication, and with the necesity to configure a secure replication for Mysql and MongoDB. The best solution that came to my head was build two SSH tunnels, one for Mysql and the other for MongoDB.

SSH-tunnel

Installation

For this post I assume that the Mysql and MongoDB software is already installed in both servers with default configuration.

Trust Relationship

Create a new user in both machines without login, this user will be responsible for keeping the tunnel up.

useradd -s /bin/false tunnel

Now creates a new ssh key pair

ssh-keygen 
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): /home/tunnel/.ssh/id_rsa 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /home/tunnel/.ssh/id_rsa.
Your public key has been saved in /home/tunnel/.ssh/id_rsa.pub.
The key fingerprint is:
83:11:c6:cb:g3:dd:67:a0:29:89:ag:3d:89:1b:5e:o4 root@nodeX.albertolarripa.com
The key's randomart image is:
+--[ RSA 2048]----+
|                 |
|       .         |
|        +        |
|       o o       |
|        S     .  |
|    .  . * o + . |
|   o.  ...+ + . o|
|  E.....o. .   o |
|    oo..o.       |
+-----------------+

Finally exchanged the id_rsa.pub public keys on both machines, add his content in the /home/tunnel/.ssh/authorized_keys of the twin account

#cat /tmp/id_rsa.pub 
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDhM+tC+yQZP3f79nMTCEgzI9emRzgs0UuKq9jlkWddLg3oABxJGNhq6O8NkE+Az0vP0BXKUh3gnn7T4pmlgxKduVYFUdWnH2AaxOAHSDOAhsdsñlajsazIo+ASMUyF9ddMrofs3yCMLnTsbcf26JZ1d+uN2fqfc9Nd0fpt+X4hMt2uld026ASda+saGT7eAS+Da+sd+aAANwUNSpwk7P8M6RUc/+Je0DMnncqlwMfMoXxMUEekeaa+m2PnG8Z/IbkA9bm49W4JWES/EzSBvhuUTvH5sAX3QRgObcpFtpNy5UAqEDeHmgS2jRb root@node1.albertolarripa.com

Build SSH tunnel

Download from this Webpage the SSH Reverse Tunnel script and install on both servers:

tar xvfz ssh-reverse-tunnel_0.5.3.tar.gz
cd ssh-reverse-tunnel_0.5.3
make install

Now copy the examples configuration files for Mysql and MongoDB tunnels

cd ssh-reverse-tunnel_0.5.3/examples
 cp -pr ssh-reverse-tunnel.conf /etc/mysql-reverse.conf
 cp -pr ssh-reverse-tunnel.conf /etc/mongo-reverse.conf

Configure both of them with the correct parameters:

NODE1

Mysql tunnel

VERBOSE=false
REMOTE_COMMANDS=false
REVERSE_PORTS_LIST=2206:localhost:3306
SERVER_USER=tunnel
SERVER_HOST=node2.albertolarripa.com
SERVER_HOST_CLIENT_ALIAS=client-local
SERVER_SSH_PORT=22
CLIENT_USER=tunnel
CLIENT_HOST=node1.albertolarripa.com
SSH_EXTRA_SERVER_OPTIONS="-x"
SSH_EXTRA_CLIENT_OPTIONS="-x -o TCPKeepAlive=yes"
CLIENT_MODE_TIMEOUT=20
SERVER_MODE_TIMEOUT=20

MongoDB tunnel

VERBOSE=false
REMOTE_COMMANDS=false
REVERSE_PORTS_LIST=22017:localhost:27017
SERVER_USER=tunnel
SERVER_HOST=node2.albertolarripa.com
SERVER_HOST_CLIENT_ALIAS=client-local
SERVER_SSH_PORT=22
CLIENT_USER=tunnel
CLIENT_HOST=node1.albertolarripa.com
SSH_EXTRA_SERVER_OPTIONS="-x"
SSH_EXTRA_CLIENT_OPTIONS="-x -o TCPKeepAlive=yes"
CLIENT_MODE_TIMEOUT=20
SERVER_MODE_TIMEOUT=20

NODE2

Mysql tunnel

VERBOSE=false
REMOTE_COMMANDS=false
REVERSE_PORTS_LIST=2206:localhost:3306
SERVER_USER=tunnel
SERVER_HOST=node1.albertolarripa.com
SERVER_HOST_CLIENT_ALIAS=client-local
SERVER_SSH_PORT=22
CLIENT_USER=tunnel
CLIENT_HOST=node2.albertolarripa.com
SSH_EXTRA_SERVER_OPTIONS="-x"
SSH_EXTRA_CLIENT_OPTIONS="-x -o TCPKeepAlive=yes"
CLIENT_MODE_TIMEOUT=20
SERVER_MODE_TIMEOUT=20

MongoDB tunnel

VERBOSE=false
REMOTE_COMMANDS=false
REVERSE_PORTS_LIST=22017:localhost:27017
SERVER_USER=tunnel
SERVER_HOST=node1.albertolarripa.com
SERVER_HOST_CLIENT_ALIAS=client-local
SERVER_SSH_PORT=22
CLIENT_USER=tunnel
CLIENT_HOST=node2.albertolarripa.com
SSH_EXTRA_SERVER_OPTIONS="-x"
SSH_EXTRA_CLIENT_OPTIONS="-x -o TCPKeepAlive=yes"
CLIENT_MODE_TIMEOUT=20
SERVER_MODE_TIMEOUT=20

Crontab

Set the execution of the ssh-reverse-tunnel script every 2 minutes

 */2 * * * * /usr/local/bin/ssh-reverse-tunnel client --configuration-file /etc/mysql-reverse.conf
 */2 * * * * /usr/local/bin/ssh-reverse-tunnel client --configuration-file /etc/mongo-reverse.conf

Wait 2 minutes and test if the tunnels startup properly

# ps aux | grep ssh
vpn 9822 0.0 0.0 41532 3124 ? S jun20 1:36 ssh -x -o TCPKeepAlive=yes -p 22 -n -N -R2206:localhost:3306 -l vpn node1.albertolarripa.com
vpn 17737 0.0 0.0 41532 3112 ? S jun23 0:57 ssh -x -o TCPKeepAlive=yes -p 22 -n -N -R22017:localhost:27017 -l vpn node1.albertolarripa.com

# netstat -nltp
tcp 0 0 127.0.0.1:2206 0.0.0.0:* LISTEN 18618/sshd: vpn 
tcp 0 0 127.0.0.1:22017 0.0.0.0:* LISTEN 18617/sshd: vpn

Restart Tunneling

As the tunnel may fail, I’ll create a process that in case of failure it will automatically start. Basically detect if the communication between Mysql and MongoDb still alive, if not the daemon create a file “.ssh-reverse-tunnel.restart.$CLIENT_HOST” to restart it

NODE1

In the node1 create the following script:

#!/bin/sh

PORT="2206"
SSH="/usr/bin/ssh"
MYSQL="/usr/bin/mysql"
MONGOSTAT="/usr/bin/mongostat"
TOUCH="/usr/bin/touch"
USER_TUNEL="tunnel"
BASE="/home/$USER_TUNEL"
CLIENT_HOST="node2.albertolarripa.com"
RESTART=".ssh-reverse-tunnel.restart.$CLIENT_HOST"
PROCESO=`ps aux | grep -c "restart_tunnel.sh"`
LOG="/tmp/tunnel"

if [ $PROCESO -gt '4' ]; then
{
exit
}
fi

while true
do
{
DATE=`/bin/date`
$MYSQL --no-defaults --protocol=tcp -h 127.0.0.1 -P 2206 -u vpn -p -e exit
MARIADB_TEST="$?"
$MONGOSTAT -h localhost --port 22017 -n1
MONGODB_TEST="$?"

if [ $MONGODB_TEST -eq 0 ] && [ $MARIADB_TEST -eq 0 ]; then
echo "$DATE ---- IT'S ALIVE" >> $LOG
sleep 15
else
echo "************************************************" >> $LOG
echo "WARNING TUNEL DOWN" >> $LOG
echo "$DATE" >> $LOG
echo "************************************************" >> $LOG
$TOUCH $BASE/$RESTART
echo "RESTART FILE CREATED" >> $LOG
echo "WAITING 15sec" >> $LOG
echo "+++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG
sleep 15
fi
}
done

Finally add it in the crontab

*/2 * * * * /home/tunnel/restart_tunnel.sh

NODE2

In the node2 create the following script:

#!/bin/sh

PORT="2206"
SSH="/usr/bin/ssh"
MYSQL="/usr/bin/mysql"
MONGOSTAT="/usr/bin/mongostat"
TOUCH="/usr/bin/touch"
USER_TUNEL="tunnel"
BASE="/home/$USER_TUNEL"
CLIENT_HOST="node1.albertolarripa.com"
RESTART=".ssh-reverse-tunnel.restart.$CLIENT_HOST"
PROCESO=`ps aux | grep -c "restart_tunnel.sh"`
LOG="/tmp/tunnel"

if [ $PROCESO -gt '4' ]; then
{
exit
}
fi

while true
do
{
DATE=`/bin/date`
$MYSQL --no-defaults --protocol=tcp -h 127.0.0.1 -P 2206 -u vpn -p -e exit
MARIADB_TEST="$?"
$MONGOSTAT -h localhost --port 22017 -n1
MONGODB_TEST="$?"

if [ $MONGODB_TEST -eq 0 ] && [ $MARIADB_TEST -eq 0 ]; then
echo "$DATE ---- IT'S ALIVE" >> $LOG
sleep 15
else
echo "************************************************" >> $LOG
echo "WARNING TUNEL DOWN" >> $LOG
echo "$DATE" >> $LOG
echo "************************************************" >> $LOG
$TOUCH $BASE/$RESTART
echo "RESTART FILE CREATED" >> $LOG
echo "WAITING 15sec" >> $LOG
echo "+++++++++++++++++++++++++++++++++++++++++++++++" >> $LOG
sleep 15
fi
}
done

Finally add it in the crontab

*/2 * * * * /home/tunnel/restart_tunnel.sh

Replication

Mysql Replicatiom

I will start setting the MultiMaster Replication, wich is based on both nodes have the role of Master and Slave at the same time, the production machine with read/write access, and the read-only CDR server.

Configuration

Active –> Passive replication

Server1

This is the Mysql active server with read/write access, the changes that commit in this server will be replicated to the passive Server2.

my.cnf

Edit your Mysql configuration file, normally /etc/my.cnf

  • log-bin: The label of your binary log
  • server-id: 1 because is the first Mysql Master server
  • max_binarylog_size: The maximum size of the binary logs
  • expire_logs_days: The binary logs policy retention
...
[mysqld]
log-bin=mysql-bin
server-id = 1
max_binlog_size=50M
expire_logs_days=15
  • binlog-ignore-db: If  you need not replicate one database, simply specify it like
binlog-ignore-db = db_cache
Grants

Now create the replication user with the following permissions

mysql> grant REPLICATION SLAVE on *.* to 'replicator'@'%' identified by 'password';
Dump Database

We perform a full export of the database that contains the binary log position

[root@server1]# time /usr/local/etc/mysql/bin/mysqldump --defaults-file=/etc/my.cnf -uroot -p<password> --all-databases --master-data > /backup/mysql.all.backup-20121021
Master status

Take the Binary name and the Master position

mysql> show master status;
+------------------------+----------+--------------+------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
| mysql-bin.001568       |      107 |              | db_cache         |
+------------------------+----------+--------------+------------------+
Server2

This is passive server with read-only access, normally this server received the changes from the active server, but in case of disaster, this server wake up with write access.

my.cnf

Like the server1 configuration but changing the server-id

...
[mysqld]
log-bin=mysql-bin
server-id = 2
max_binlog_size=50M
expire_logs_days=15
binlog-ignore-db = db_cache

Activate the read-only access adding this option in your configuration file

read_only
Import Database

Before setup the replication import the database with the previous export file

mysql> source /backup/mysql.all.backup-20121021
Master configuration

Logon in your mysql passive server and configure the master information

  • master_host: The loopback IP
  • master_port: SSH Mysql port (2206)
  • master_log_file: This is the name of the binary log that obtain previously
  • master_log_pos: And the active binary log position
mysql> change master to
 -> master_host='127.0.0.1',
 -> master_port=2206,
 -> master_user='replicator',
 -> master_password='password',
 -> master_log_file='mysql-bin.001568',
 -> master_log_pos=107;

Before start the slave, check if the information introduced are correct

mysql> show slave status \G

If all is OK, start the slave

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: replicator
                  Master_Port: 2206
                Connect_Retry: 60
              Master_Log_File: mysql-bin.001568
          Read_Master_Log_Pos: 107
               Relay_Log_File: mysql-relay-bin.000028
                Relay_Log_Pos: 259
        Relay_Master_Log_File: mysql-bin.001568
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 107
              Relay_Log_Space: 424
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1

 Passive <– Active replication

Now is the moment to configure the opposite replication, this replication don’t affect the Mysql active server because the passive server is in read mode, and no change is replicated.

Server2
Grants

In Mysql the REPLICATION permissions won’t replicate to others servers, is necessary create new user with this grant.

mysql> grant REPLICATION SLAVE on *.* to 'replicator'@'%' identified by 'password';
Master status

Take the Binary name and the Master position of you passive Master server.

mysql> show master status;
+------------------------+----------+--------------+------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
| mysql-bin.000003       |      107 |              | db_cache         |
+------------------------+----------+--------------+------------------+
Server1
Master configuration

Logon in your mysql active server and configure the master information

  • master_host: The loopback IP
  • master_port: SSH Mysql port (2206)
  • master_log_file: This is the name of the binary log that obtain previously
  • master_log_pos: And the active binary log position
mysql> change master to
 -> master_host='127.0.0.1',
 -> master_port=2206,
 -> master_user='replicator',
 -> master_password='password',
 -> master_log_file='mysql-bin.000003',
 -> master_log_pos=107;

Before start the slave, check if the information introduced are correct

mysql> show slave status \G

If all is OK, start the slave

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: replicator
                  Master_Port: 2206
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 107
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 259
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 107
              Relay_Log_Space: 424
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2

Ok we’re done with the configuration, both replications are running.

Manage

To change between active<–>passive mode you only need to change the write<–>read role’s.

For example if you need startup your passive server like active, with write access, you’ll follow this operation:

  1. Stop Mysql passive server (server2)
  2. Stop Mysql active server (server1)
  3. Comment in your server2 /etc/my.cnf file, the read_only option
  4. Start the new Mysql active server (server2)
  5. Add the read_only option in you new Mysql passive server (server1) /etc/my.cnf file
  6. Start both Mysql slave’s and check

MongoDB Replication

The MongoDB replication is very simple, basically consist in this procedures:

  1. Set the production Database as Master
  2. Backup Full of the production DB
  3. Import the backup in Slave DB
  4. Set the second MongoDB as Slave server
  5. Start MongoDB Slave service

Configuration

Server1

  • Set the production Database as Master

Edit your /etc/mongodb.conf configuration file and set the following options:

master = true
source = localhost

The source is localhost because the port of the slave machine are listening in the loopback interface.

To apply the changes restart mongodb service.

  • Backup Full of the production DB
mongodump --host localhost --port 27017 --out <PATH_BCK_DIR>

Server2

  • Import the backup in Slave DB
mongorestore -v -h localhost --port 27017 <PATH_BCK_DIR>
  • Set the second MongoDB as Slave server

Edit your /etc/mongodb.conf configuration file and set the following options:

slave = true
source = 127.0.0.1:22017

Now restart the slave to apply the changes, and monitorice the log file

# /etc/init.d/mongodb start && tail -f /var/log/mongodb/mongodb.log
Wed Jul  3 22:59:13 [initandlisten] MongoDB starting : pid=12803 port=27017 dbpath=/usr/local/data/mongo_data slave=1 64-bit host=server1.albertolarripa.com
Wed Jul  3 22:59:13 [initandlisten] db version v2.0.6, pdfile version 4.5
Wed Jul  3 22:59:13 [initandlisten] git version: nogitversion
Wed Jul  3 22:59:13 [initandlisten] build info: Linux batsu 3.2.0-23-generic #36-Ubuntu SMP Tue Apr 10 20:39:51 UTC 2012 x86_64 BOOST_LIB_VERSION=1_49
Wed Jul  3 22:59:13 [initandlisten] options: { bind_ip: "127.0.0.1", config: "/etc/mongodb.conf", dbpath: "/usr/local/data/mongo_data", logappend: "true", logpath: "/var/log/mongodb/mongodb.log", nohttpinterface: "true", port: 27017, profile: 1, slave: "true", source: "127.0.0.1:22017" }
Wed Jul  3 22:59:13 [initandlisten] journal dir=/usr/local/data/mongo_data/journal
Wed Jul  3 22:59:13 [initandlisten] recover : no journal files present, no recovery needed
Wed Jul  3 22:59:16 [initandlisten] preallocateIsFaster=true 3.48
Wed Jul  3 22:59:19 [initandlisten] preallocateIsFaster=true 4.6
Wed Jul  3 22:59:24 [initandlisten] preallocateIsFaster=true 3.12
Wed Jul  3 22:59:24 [initandlisten] preallocateIsFaster check took 10.156 secs
Wed Jul  3 22:59:24 [initandlisten] preallocating a journal file /usr/local/data/mongo_data/journal/prealloc.0
Wed Jul  3 22:59:30 [initandlisten] preallocating a journal file /usr/local/data/mongo_data/journal/prealloc.1
Wed Jul  3 22:59:36 [initandlisten] preallocating a journal file /usr/local/data/mongo_data/journal/prealloc.2
Wed Jul  3 22:59:43 [initandlisten] waiting for connections on port 27017
Wed Jul  3 22:59:44 [replslave] repl: from host:127.0.0.1:22017
Wed Jul  3 22:59:46 [initandlisten] connection accepted from 127.0.0.1:51794 #1
Wed Jul  3 22:59:46 [initandlisten] connection accepted from 127.0.0.1:51795 #2
Wed Jul  3 22:59:47 [conn1] end connection 127.0.0.1:51794
Wed Jul  3 22:59:47 [conn2] end connection 127.0.0.1:51795

And this is all, with this configuration you ensure your replication databases 😉

Regards of a Sysadmin

Leave a Reply

Your email address will not be published. Required fields are marked *