Edits:

  • 2020-08-26 - Updated some text, no technical stuffs

Caveats:

  • If you test it out and you find something, please leave a message in the comments or slack

Like the last guides:

I take no responsibility if this guide bork you server, burn your house down to ashes, make your christmas tree self combust or makes your cat to leave you..
It’s under the “it worked for me[tm]” clause.

This is as always a work in progress.

Install 3 Ubuntu 20.04 LTS servers

Upgrade and secure ssh login with ufw or such iptables wrapper.

I always use digitalocean for public tests, and kvm for tests onprem..

This are the conditions for this writeup:

db01 - Master - 192.168.1.180
db02 - Slave01 - 192.168.1.200
db03 - Slave02 - 192.168.1.179

The database that is going to be replicated is named classicmodels and the exampledata is here https://github.com/falkowich/mysql-sample-db
_This is forked from https://github.com/hhorak/mysql-sample-db

Install and secure MariaDB on all servers

image

Moar hints here: https://www.digitalocean.com/community/tutorials/how-to-install-mariadb-on-ubuntu-20-04

sudo apt install mariadb-server
sudo mysql_secure_installation

Enter current password for root (enter for none): [enter]
Set root password? [Y/n] [N]
Remove anonymous users? [Y/n] [Y]
Disallow root login remotely? [Y/n] [Y]
Remove test database and access to it? [Y/n] [Y]
Reload privilege tables now? [Y/n] [Y]

Prepare the master node,(db01)

Configure so that the db is reachable remote.

Find and edit these settings.

sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf

bind-address            = 192.168.1.180
server-id              = 101
log_bin                = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size        = 100M
binlog_do_db           = classicmodels

Restart mariadb

sudo systemctl restart mariadb.service

Download the exampledata

On db01, download the exampledata

wget https://raw.githubusercontent.com/falkowich/mysql-sample-db/master/mysqlsampledatabase.sql

Create db, user and permission for our database

sudo mariadb

Create database and user

create database classicmodels;
grant ALL on classicmodels.* to 'app_user'@'localhost' identified by 'usr_supersecretpassword';
grant REPLICATION SLAVE on *.* to 'repl_user'@'%' identified by 'repl_supersecretpassword';
FLUSH PRIVILEGES;
exit

Load sql into db

mysql -u app_user -p classicmodels < mysqlsampledatabase.sql

Start the export to the first slave node

Check database log status

sudo mariadb

This locks the classicmodels tables, until we are finished with the dbdump

USE classicmodels;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
exit

This should show something like this, write down values of File and Position for later use

SHOW MASTER STATUS;
+------------------+----------+---------------+------------------+
| File             | Position | Binlog_Do_DB  | Binlog_Ignore_DB |
+------------------+----------+---------------+------------------+
| mysql-bin.000001 |   208712 | classicmodels |                  |
+------------------+----------+---------------+------------------+
1 row in set (0.000 sec)

Dump the data for export.

mysqldump -u app_user -p classicmodels > classicmodels.sql

Unlock the tables again

sudo mariadb
UNLOCK TABLES;
exit;

Copy the data to the first slave

scp classicmodels.sql 192.168.1.200:/tmp/

Prepare the first slave node,(db02)

Configure mariadb.

Find and edit these settings.

sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf

bind-address            = 192.168.1.200
server-id              = 102
log_bin                = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
binlog_do_db           = classicmodels
read_only               = 0 # this will we change later
report-host             = db02.example.com

Create db, user and permission for our database

sudo mariadb

Create database and user

create database classicmodels;
grant ALL on classicmodels.* to 'app_user'@'localhost' identified by 'usr_supersecretpassword';
FLUSH PRIVILEGES;
exit

Restart mariadb

sudo systemctl restart mariadb.service

Import the data.

mysql -u app_user -p classicmodels < /tmp/classicmodels.sql

If import worked, don’t forget to remove the data in /tmp

sudo rm /tmp/classicmodels.sql

After this we must configure where the master node is

sudo mariadb

Enter the data that you saved from the SHOW MASTER STATUS; on the master node.

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST="192.168.1.180", MASTER_USER="repl_user", MASTER_PASSWORD="repl_supersecretpassword", MASTER_LOG_FILE="mysql-bin.000001", MASTER_LOG_POS=208712;
START SLAVE;

Verify that thing is synced

Check slave status

show slave status\G

*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.1.190
                   Master_User: repl_user
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000004
           Read_Master_Log_Pos: 342
                Relay_Log_File: mysqld-relay-bin.000004
                 Relay_Log_Pos: 641
         Relay_Master_Log_File: mysql-bin.000004
              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: 342
               Relay_Log_Space: 1250
               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: 101
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.001 sec)

Verify that the replication is working

On the master

sudo mariadb
use classicmodels;

insert into offices(officeCode,city,phone,addressLine1,addressLine2,state,country,postalCode,territory) values(8,'foo','bar','baz','foo2','bar2','baz2','foo3','bar3');

Now should the replication write the same to the slave;

use classicmodels;

select * from offices;


+------------+---------------+------------------+--------------------------+--------------+------------+-----------+------------+-----------+
| officeCode | city          | phone            | addressLine1             | addressLine2 | state      | country   | postalCode | territory |
+------------+---------------+------------------+--------------------------+--------------+------------+-----------+------------+-----------+
| 1          | San Francisco | +1 650 219 4782  | 100 Market Street        | Suite 300    | CA         | USA       | 94080      | NA        |
| 2          | Boston        | +1 215 837 0825  | 1550 Court Place         | Suite 102    | MA         | USA       | 02107      | NA        |
| 3          | NYC           | +1 212 555 3000  | 523 East 53rd Street     | apt. 5A      | NY         | USA       | 10022      | NA        |
| 4          | Paris         | +33 14 723 4404  | 43 Rue Jouffroy Dabbans  | NULL         | NULL       | France    | 75017      | EMEA      |
| 5          | Tokyo         | +81 33 224 5000  | 4-1 Kioicho              | NULL         | Chiyoda-Ku | Japan     | 102-8578   | Japan     |
| 6          | Sydney        | +61 2 9264 2451  | 5-11 Wentworth Avenue    | Floor #2     | NULL       | Australia | NSW 2010   | APAC      |
| 7          | London        | +44 20 7877 2041 | 25 Old Broad Street      | Level 7      | NULL       | UK        | EC2N 1HN   | EMEA      |
| 8          | foo           | bar              | baz                      | foo2         | bar2       | baz2      | foo3       | bar3      |
+------------+---------------+------------------+--------------------------+--------------+------------+-----------+------------+-----------+
8 rows in set (0.002 sec)

Done

Prepare the next slave node,(db03)

Now it’s time to get the last slave (db03) into the cluster

Configure mariadb.

Find and edit these settings.

sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf

bind-address            = 192.168.1.179
server-id              = 103
log_bin                = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
binlog_do_db           = classicmodels
read_only               = 0 # this will we change later
report-host             = db03.example.com

Create db, user and permission for our database

sudo mariadb

Create database and user

create database classicmodels;
grant ALL on classicmodels.* to 'app_user'@'localhost' identified by 'usr_supersecretpassword';
FLUSH PRIVILEGES;
exit

Restart mariadb

sudo systemctl restart mariadb.service

Copy the data from master to the second slave

scp classicmodels.sql 192.168.1.179:/tmp/

Import the data.

mysql -u app_user -p classicmodels < /tmp/classicmodels.sql

If import worked, don’t forget to remove the data in /tmp

sudo rm /tmp/classicmodels.sql

After this we must configure where the master node is

sudo mariadb

Enter the data that you saved from the SHOW MASTER STATUS; on the master node.

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST="192.168.1.180", MASTER_USER="repl_user", MASTER_PASSWORD="repl_supersecretpassword", MASTER_LOG_FILE="mysql-bin.000001", MASTER_LOG_POS=208712;
START SLAVE;

Verify that things are synced

Check slave status

show slave status\G

*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.1.190
                   Master_User: repl_user
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000004
           Read_Master_Log_Pos: 999
                Relay_Log_File: mysqld-relay-bin.000003
                 Relay_Log_Pos: 1298
         Relay_Master_Log_File: mysql-bin.000004
              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: 999
               Relay_Log_Space: 1907
               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: 101
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 2
1 row in set (0.001 sec)

Verify that the replication is working

On the master

sudo mariadb
use classicmodels;

insert into offices(officeCode,city,phone,addressLine1,addressLine2,state,country,postalCode,territory) values(99,'foo-x','bar-x','baz-x','foo2-x','bar2-x','baz2-x','foo3-x','bar3-x');

Now should the replication write the same to the slave;

use classicmodels;

select * from offices;


+------------+---------------+------------------+--------------------------+--------------+------------+-----------+------------+-----------+
| officeCode | city          | phone            | addressLine1             | addressLine2 | state      | country   | postalCode | territory |
+------------+---------------+------------------+--------------------------+--------------+------------+-----------+------------+-----------+
| 1          | San Francisco | +1 650 219 4782  | 100 Market Street        | Suite 300    | CA         | USA       | 94080      | NA        |
| 10         | foo-x         | bar-x            | baz-x                    | foo2-x       | bar2-x     | baz2-x    | foo3-x     | bar3-x    |
| 2          | Boston        | +1 215 837 0825  | 1550 Court Place         | Suite 102    | MA         | USA       | 02107      | NA        |
| 3          | NYC           | +1 212 555 3000  | 523 East 53rd Street     | apt. 5A      | NY         | USA       | 10022      | NA        |
| 4          | Paris         | +33 14 723 4404  | 43 Rue Jouffroy Dabbans  | NULL         | NULL       | France    | 75017      | EMEA      |
| 5          | Tokyo         | +81 33 224 5000  | 4-1 Kioicho              | NULL         | Chiyoda-Ku | Japan     | 102-8578   | Japan     |
| 6          | Sydney        | +61 2 9264 2451  | 5-11 Wentworth Avenue    | Floor #2     | NULL       | Australia | NSW 2010   | APAC      |
| 7          | London        | +44 20 7877 2041 | 25 Old Broad Street      | Level 7      | NULL       | UK        | EC2N 1HN   | EMEA      |
| 8          | foo           | bar              | baz                      | foo2         | bar2       | baz2      | foo3       | bar3      |
+------------+---------------+------------------+--------------------------+--------------+------------+-----------+------------+-----------+
9 rows in set (0.001 sec)

Done

SSL/TLS Encryption

Master

sudo su
mkdir /etc/mysql/ssl

CA - Certificate:

cd /etc/mysql/ssl
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 365000 -subj "/C=SE/L=Koping/CN=CA Server"  -key ca-key.pem -out ca-cert.pem

Server - Certificate:

openssl req -newkey rsa:2048 -days 365000 -nodes -keyout server-key.pem -out server-req.pem -subj "/C=SE/L=Koping/CN=DB Server"
openssl  rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 365000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

Client - Certificate:

openssl req -newkey rsa:2048 -days 365000 -nodes -keyout client-key.pem -out client-req.pem  -subj "/C=SE/L=Koping/CN=Client Server"
openssl rsa -in client-key.pem -out client-key.pem
openssl  x509 -req -in client-req.pem -days 365000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
chown mysql /etc/mysql/ssl/ -R

Edit options

vim /etc/mysql/mariadb.conf.d/50-server.cnf

#
ssl-ca = /etc/mysql/ssl/ca-cert.pem
ssl-cert = /etc/mysql/ssl/server-cert.pem
ssl-key = /etc/mysql/ssl/server-key.pem
#
# Accept only connections using the latest and most secure TLS protocol version.
# ..when MariaDB is compiled with OpenSSL:
#ssl-cipher = TLSv1.2
# ..when MariaDB is compiled with YaSSL (default in Debian):
ssl = on

restart service

systemctl restart mariadb

tarball the client certs

cd /etc/mysql/ssl
tar zcvf /etc/mysql/ssl/slave-certs.tar.gz ca-cert.pem client-cert.pem client-key.pem

copy ca cert to slave servers

scp /etc/mysql/ssl/slave-certs.tar.gz   192.168.1.200:/tmp/
scp /etc/mysql/ssl/slave-certs.tar.gz  192.168.1.179:/tmp/

Slave

Enabling TLS with CHANGE MASTER
https://mariadb.com/kb/en/replication-with-secure-connections/

sudo su

mkdir /etc/mysql/ssl
cd /etc/mysql/ssl
tar zxvf /tmp/slave-certs.tar.gz 
chown mysql /etc/mysql/ssl/ -R
rm /tmp/slave-certs.tar.gz

sudo mariadb
stop slave;
CHANGE MASTER TO
   MASTER_SSL_CERT = '/etc/mysql/ssl/client-cert.pem',
   MASTER_SSL_KEY = '/etc/mysql/ssl/client-key.pem',
   MASTER_SSL_CA = '/etc/mysql/ssl/ca-cert.pem',
   MASTER_SSL=1;
start slave;
show slave status\G

*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.1.180
                   Master_User: repl_user
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000002
           Read_Master_Log_Pos: 342
                Relay_Log_File: mysqld-relay-bin.000002
                 Relay_Log_Pos: 555
         Relay_Master_Log_File: mysql-bin.000002
              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: 342
               Relay_Log_Space: 865
               Until_Condition: None
                Until_Log_File: 
                 Until_Log_Pos: 0
            Master_SSL_Allowed: Yes
            Master_SSL_CA_File: /etc/mysql/ssl/ca-cert.pem
            Master_SSL_CA_Path: 
               Master_SSL_Cert: /etc/mysql/ssl/client-cert.pem
             Master_SSL_Cipher: 
                Master_SSL_Key: /etc/mysql/ssl/client-key.pem
         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: 101
                Master_SSL_Crl: /etc/mysql/ssl/ca-cert.pem
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 2
1 row in set (0.001 sec)

Done, if you have any ideas or see any errors in this writeup.
Write a comment in the comments (doh?), or join the sadsloth slackchannel and lets hang out there :)