{{tag>mysql mariadb stunnel replication}}

====== 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

<code>
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
</code>

==== slave stunnel ====

cat /etc/stunnel/server_slave.conf
<code>
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
</code>

<WRAP center round help 60%>
If the server acts as a slave and master at the same time, change the PID name in configuration file
</WRAP>

===== 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:

<code mysql>
    ...
    
    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

    ...    
</code>

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;

====== Tested on ======
  * 

====== See also ======

====== References ======