Ben's notes

Linux, Unix, network, radio...

User Tools

Site Tools


powerdns_master-slave_replication_with_mysql

Encrypt MariaDB replication traffic

  • CentOS 7
  • MariaDB

Check for SSL support

  • Check SSL variable on both master and slave. This value should be 'DISABLED' or 'OK'. If value is 'NO' then reinstall or recompile with SSL support:
    mysql> SHOW VARIABLES LIKE 'have_ssl';

Master configuration

Replication user

  • Create replication user on master server:
    mysql>MariaDB [powerdns]> CREATE USER 'replicator'@'%' IDENTIFIED BY 'chooseyourownpassword!';
    Query OK, 0 rows affected (0.00 sec)
    
    MariaDB [powerdns]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'%';
    Query OK, 0 rows affected (0.00 sec)
    
    MariaDB [powerdns]> GRANT SELECT ON powerdns.* TO 'replicator'@'%';
    Query OK, 0 rows affected (0.00 sec)
    
    MariaDB [powerdns]> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.01 sec)
  • Test connection with the above username and password, from the slave to the master.

SSL keys

  • Create directory for key and certificates:
    mkdir -p /etc/pki/tls/certs/mariadb
    cd /etc/pki/tls/certs/mariadb
  • Create private key and public certificate:
    openssl req -x509 -sha256 -newkey rsa:4096 \
    -keyout master-private.pem -out master-public.pem \
    -subj '/CN=ns1.polaire.nl' -nodes -days 3650
    
    chmod 400 master-private.pem
    chown mysql master-private.pem
  • Copy public certificate to (as) 'CA certificate':
    cp master-public.pem ca.pem

MariaDB configuration

  • Edit /etc/my.cnf, add:
    [mysqld]
    log_bin = mysql-bin
    server_id = 10
    ssl-ca=/etc/pki/tls/certs/mariadb/ca.pem
    ssl-cert=/etc/pki/tls/certs/mariadb/master-public.pem
    ssl-key=/etc/pki/tls/certs/mariadb/master-private.pem
  • Restart MariaDB:
    systemctl restart mariadb

Require SSL

  • Modify replicator user to require SSL:
    MariaDB [(none)]> GRANT USAGE ON *.* TO 'replicator'@'%' REQUIRE SSL;
    Query OK, 0 rows affected (0.00 sec)
    
    MariaDB [(none)]> SHOW GRANTS FOR "replicator";
  • Test connection again from slave, this should no fail!

Slave configuration

  • Create directory and copy master CA certificate to slave:
    mkdir -p /etc/pki/tls/certs/mariadb
    cd /etc/pki/tls/certs/mariadb
  • Test connection from slave to master using SSL:
    mysql -u replicator -p'yourpassword' -hns1.polaire.nl --ssl-ca /etc/pki/tls/certs/mariadb/ca.pem --ssl-verify-server
  • Show cipher in use:
    <MariaDB [(none)]> SHOW STATUS LIKE 'Ssl_cipher';
    +---------------+---------------------------+
    | Variable_name | Value                     |
    +---------------+---------------------------+
    | Ssl_cipher    | DHE-RSA-AES256-GCM-SHA384 |
    +---------------+---------------------------+
    1 row in set (0.05 sec)

Client certificate verification

  • On the master server enable client key verification:
    MariaDB [(none)]> GRANT USAGE ON *.* TO 'replicator'@'%' REQUIRE SUBJECT '/CN=ns2.polaire.nl';
    
    MariaDB [(none)]> SHOW GRANTS FOR "replicator";
  • Client connection test should fail again!
  • Now create a key and certificate for the slave:
    cd /etc/pki/tls/certs/mariadb
    openssl req -x509 -sha256 -newkey rsa:4096 \
    -keyout slave-private.pem -out slave-public.pem \
    -subj '/CN=ns2.polaire.nl' -nodes -days 3650
    
    chmod 400 slave-private.pem
    chown mysql slave-private.pem
  • Append public key to CA on both master and slave:
    cat slave-public.pem >> ca.pem
  • Restart master server:
    systemctl restart mariadb
  • Test connection, this should work!
    mysql -u replicator -p'yourpass' -hns1.polaire.nl \
        --ssl-ca /etc/pki/tls/certs/mariadb/ca.pem \
        --ssl-cert /etc/pki/tls/certs/mariadb/slave-public.pem \
        --ssl-key /etc/pki/tls/certs/mariadb/slave-private.pem

Backup master and import to slave

  • Create backup on master:
    mysqldump -u root -p --single-transaction --all-databases --master-data=1 > /tmp/master_backup.sql
  • Copy the dump to the slave.

Slave configuration

  • Edit /etc/my.cnf on the slave:
    [mysqld]
    log_bin = mysql-bin
    server_id = 20
    log_slave_updates = 1
    relay_log = mysql-relay-bin
    read_only = 1

Enable replication

  • Import the backup on the slave:
    MariaDB [(none)]> source /tmp/master_backup.sql
  • Configure replication:
    CHANGE MASTER TO
    Master_Host='ns1.polaire.nl',
    Master_User='replicator',
    Master_Password='yourpassword',
    Master_SSL=1,
    Master_SSL_CA = '/etc/pki/tls/certs/mariadb/ca.pem',
    Master_SSL_CERT = '/etc/pki/tls/certs/mariadb/slave-public.pem',
    Master_SSL_KEY = '/etc/pki/tls/certs/mariadb/slave-private.pem',
    Master_SSL_Verify_Server_Cert = 1;
    Query OK, 0 rows affected (0.08 sec)
  • Start slave:
    MariaDB [(none)]> slave start;
    Query OK, 0 rows affected (0.00 sec)
    
    MariaDB [(none)]> show slave status \G
powerdns_master-slave_replication_with_mysql.txt · Last modified: 2021/10/09 15:14 by 127.0.0.1