DB replication over stunnel

Install stunnel on master and slave

apt-get install stunnel

Enable stunnel autostart

sed -i '/ENABLED/s/0/1/' /etc/default/stunnel4

Create server certificate (client certs are not necessary). Just copy the server.certs, ca, and keys to slave server

See Creating CA and signing server and client certs with openssl

master stunnel

cat /etc/stunnel/server_master.conf

cert = /etc/stunnel/certs/certs/server.crt
key = /etc/stunnel/certs/private/server.key
sslVersion = TLSv1
chroot = /var/lib/stunnel4/
setuid = stunnel4
setgid = stunnel4
pid = /stunnel4.pid
socket = l:TCP_NODELAY=1
;socket = r:TCP_NODELAY=1
verify = 2
CAPath = /etc/stunnel/certs/certs
CAfile = /etc/stunnel/certs/certs/rootCA.crt
[mysql]
accept = 3307
connect = 127.0.0.1:3306

slave stunnel

cat /etc/stunnel/server_slave.conf

cert = /etc/stunnel/certs/server.crt
key = /etc/stunnel/certs/server.key
sslVersion = TLSv1
chroot = /var/lib/stunnel4/
setuid = stunnel4
setgid = stunnel4
pid = /stunnel4.pid
;socket = l:TCP_NODELAY=1
socket = r:TCP_NODELAY=1
verify = 2
CAPath = /etc/stunnel/certs
CAfile = /etc/stunnel/certs/rootCA.crt

[mysql]
client = yes
accept  = 127.0.0.1:3307
connect = 176.28.21.65:3307

If the server acts as a slave and master at the same time, change the PID name in configuration file

Check if connection over stunnel works

mysql -u root -p -h 127.0.0.1 -P 3307

This should connect you to the other server. Check with:

SHOW variables LIKE 'slow_query_log_file%';
+---------------------+------------------------------------------------+
| Variable_name       | Value                                          |
+---------------------+------------------------------------------------+
| slow_query_log_file | /var/lib/mysql/bet90besrv01-betserver-slow.log |
+---------------------+------------------------------------------------+

Value should be other server's hostname.

Make db dump on master

This exports 3 tables tbl* from exampledb DB. Master data 2 automatically appends the CHANGE MASTER TO statement required on the slave to start the replication process as a comment.

  mysqldump --master-data=2 -uadmin -p`cat /etc/psa/.psa.shadow` exampledb tbl_1 tbl_2 tbl_3 > 4import_dev.sql

On replication server, directive to replicate certain tables needs to be inserted each time for each table e.g. if only above 3 tables will be replicated:

    ...
 
    max_binlog_size   = 100M
    #binlog_do_db           = include_database_name
    #binlog_ignore_db       = exclude_database_name
    replicate-do-table=exampledb.tbl_1
    replicate-do-table=exampledb.tbl_2
    replicate-do-table=exampledb.tbl_3
 
 
    #
    # * InnoDB
 
    ...

Check server_id in mysql.conf too

Create replication user on master server if it doesn't exist

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication'@'127.0.0.1' IDENTIFIED BY 'somepassword';

Import on slave

Stop slave if it is running:

STOP SLAVE;
SHOW SLAVE STATUS;

Then import into fresh DB:

mysql -uroot -p database1 < 4import_dev.sql

On slave select master to connect to. MASTER_LOG_FILE, MASTER_LOG_POS, can be obtained from sql dump file.

CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=3307, MASTER_USER='replication', MASTER_PASSWORD='somepassword', MASTER_LOG_FILE='mysql-bin.001955', MASTER_LOG_POS=12660241;

START SLAVE;
SHOW SLAVE STATUS;

antisaWiki

Table of Contents