Install MariaDB Replicated with TLS on Ubuntu 20.04 LTS
Aug 26, 2020Edits:
- 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
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 :)