In this tutorial, I am going to give the instructions on how to set up MariaDB server with TLS/SSL, and how to establish secure connections from the console and PHP/Python scripts. MariaDB is a database server that offers drop-in replacement functionality for MySQL server. MariaDB is built by some of the original authors of MySQL, with assistance from the broader community of Free and open-source software developers. In addition to the core functionality of MySQL, MariaDB offers a rich set of feature enhancements, including other storage engines, server optimizations, and patches.
How to set up MariaDB SSL/TLS
I tested these instructions on RHEL/CentOS 7/8, Debian 9/10, Ubuntu 16.04/18.04/18.04 LTS, Arch Linux, and FreeBSD. However, it should work on other Linux distros too.
Step 1 – Install MariaDB
Type the command as per your Linux or Unix variant.
Ubuntu/Debian Linux Install MariaDB server/client
Type the following apt-get command or apt command:
$ sudo apt-get install mariadb-server mariadb-client
CentOS/RHEL/Fedora Linux Install MariaDB server/client
Type the following yum command:
$ sudo yum install mariadb-server mariadb
Fedora Linux user type the following dnf command :
$ sudo dnf install mariadb-server mariadb
Install MariaDB server/client on Arch Linux
Type the following pacman command:
$ sudo pacman -S mariadb
FreeBSD Unix Install MariaDB server/client
To install the port, run:
# cd /usr/ports/databases/mariadb100-server/ && make install clean
# cd /usr/ports/databases/mariadb100-client/ && make install clean
To add the binary package, enter:
# pkg install mariadb100-server mariadb100-client
Alpine Linux install MariaDB
Use the apk command:
# apk add mysql mysql-client
# mysql_install_db --user=mysql --datadir=/var/lib/mysql
# rc-update add mariadb
# rc-service mariadb start
# mysqladmin -u root password 'my-password-here'
Step 2 – Secure MariaDB
Type the following command:
$ mysql_secure_installation
Sample outputs:
Fig.01: Secure the MariaDB installation
Step 3 – Create the CA certificate (TLS/SSL)
Make a directory named ssl in /etc/mysql/ directory using the mkdir command:
$ cd /etc/mysql
$ sudo mkdir ssl
$ cd ssl
Note: Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. To avoid any issues, I am setting them as follows. Otherwise, you will get certification verification failed error. Hence set it as follows:
CA common Name : MariaDB admin
Server common Name: MariaDB server
Client common Name: MariaDB client
Type the following command to create a new CA key:
$ sudo openssl genrsa 2048 > ca-key.pem
OR
$ sudo openssl genrsa 4096 > ca-key.pem
Sample outputs:
Fig.02: Generate the CA key
Type the following command to generate the certificate using that key:
$ sudo openssl req -new -x509 -nodes -days 365000 -key ca-key.pem -out ca-cert.pem
Sample outputs:
Fig.03: Using the CA key, generate the CA certificate for MariaDB
Now you must have two files as follows:
- /etc/mysql/ssl/ca-cert.pem – Certificate file for the Certificate Authority (CA).
- /etc/mysql/ssl/ca-key.pem – Key file for the Certificate Authority (CA).
I am going to use both files to generate the server and client certificates.
Step 4 – Create the server SSL certificate
To create the server key, run:
$ sudo openssl req -newkey rsa:2048 -days 365000 -nodes -keyout server-key.pem -out server-req.pem
Sample outputs:
Fig.04: Create the server key for MariaDB server
Next process the server RSA key, enter:
$ sudo openssl rsa -in server-key.pem -out server-key.pem
Sample outputs:
writing RSA key
Finally sign the server certificate, run:
$ sudo openssl x509 -req -in server-req.pem -days 365000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
Sample outputs:
Signature ok
subject=/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=MariaDB server
Getting CA Private Key
Now you must have additional files:
- /etc/mysql/ssl/server-cert.pem – MariaDB server certificate file.
- /etc/mysql/ssl/server-key.pem – MariaDB server key file.
You must use above two files on MariaDB server itself and any other nodes that you are going to use for cluster/replication traffic. These two files will secure server side communication.
Step 5 – Create the client TLS/SSL certificate
The mysql client, PHP/Python/Perl/Ruby app is going to use the client certificate to secure client-side connectivity. You must install the following files on all of your clients including the web server. To create the client key, run:
$ sudo openssl req -newkey rsa:2048 -days 365000 -nodes -keyout client-key.pem -out client-req.pem
Sample outputs:
Fig.05: Create the client key for MariaDB server
Next, process client RSA key, enter:
$ sudo openssl rsa -in client-key.pem -out client-key.pem
writing RSA key
Finally, sign the client certificate, run:
$ sudo openssl x509 -req -in client-req.pem -days 365000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
Sample outputs:
Signature ok
subject=/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=MariaDB client
Getting CA Private Key
Step 6 – How do I verify the certificates?
Type the following command to verify the certificates to make sure everything was created correctly:
$ openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
Sample outputs:
server-cert.pem: OK client-cert.pem: OK
There should not be any error and you must get OK answer for both server and client certificates.
Step 7 – Configure the MariaDB server to use SSL
Edit the vi /etc/mysql/mariadb.conf.d/50-server.cnf or /etc/mysql/mariadb.cnf as follows:
$ sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf
Append/edit in [mysqld] as follows:
### MySQL Server ### ## Securing the Database with ssl option and certificates ## ## There is no control over the protocol level used. ## ## mariadb will use TLSv1.0 or better. ## #ssl ssl-ca=/etc/mysql/ssl/ca-cert.pem ssl-cert=/etc/mysql/ssl/server-cert.pem ssl-key=/etc/mysql/ssl/server-key.pem ## Set up TLS version here. For example TLS version 1.2 and 1.3 ## tls_version = TLSv1.2,TLSv1.3
Save and close the file. Secure keys using the chmod command/chown command:
## Assuming that mariadb (mysqld) server started by the mysql user ##
$ sudo chown -Rv mysql:root /etc/mysql/ssl/
changed ownership of '/etc/mysql/ssl/server-key.pem' from root:root to mysql:root changed ownership of '/etc/mysql/ssl/client-req.pem' from root:root to mysql:root changed ownership of '/etc/mysql/ssl/server-req.pem' from root:root to mysql:root changed ownership of '/etc/mysql/ssl/ca-key.pem' from root:root to mysql:root changed ownership of '/etc/mysql/ssl/server-cert.pem' from root:root to mysql:root changed ownership of '/etc/mysql/ssl/client-cert.pem' from root:root to mysql:root changed ownership of '/etc/mysql/ssl/ca-cert.pem' from root:root to mysql:root changed ownership of '/etc/mysql/ssl/client-key.pem' from root:root to mysql:root changed ownership of '/etc/mysql/ssl/' from root:root to mysql:root
You can restart mariadb as follows:
$ sudo /etc/init.d/mysql restart
For systemd based Linux distro use the systemctl command:
$ sudo systemctl restart mysql
Make sure no ssl error reported. Here is simple way to verify that using the grep command
$ sudo grep ssl /var/log/syslog
$ sudo grep ssl /var/log/syslog | grep key
$ sudo grep mysqld /var/log/syslog | grep -i ssl
Step 8 – Configure the MariaDB client to use SSL
Configure the MariaDB client such as 192.168.1.200 to use SSL (add in the /etc/mysql/mariadb.conf.d/50-mysql-clients.cnf ):
$ sudo vi /etc/mysql/mariadb.conf.d/50-mysql-clients.cnf
Append/edit in [mysql] section:
## MySQL Client Configuration ## ssl-ca=/etc/mysql/ssl/ca-cert.pem ssl-cert=/etc/mysql/ssl/client-cert.pem ssl-key=/etc/mysql/ssl/client-key.pem ## Force TLS version for client too #tls_version = TLSv1.2,TLSv1.3 ### This option is disabled by default ### ### ssl-verify-server-cert ###
Save and close the file. You must copy /etc/mysql/ssl/ca-cert.pem, /etc/mysql/ssl/client-cert.pem, and /etc/mysql/ssl/client-key.pem to all of your clients. For example:
{vivek@server}: rsync /etc/mysql/ssl/ca-cert.pem /etc/mysql/ssl/client-cert.pem /etc/mysql/ssl/client-key.pem \
user@client:/etc/mysql/ssl
Step 9 – Verification
Type the mysql command command:
$ mysql -u {User-Name-Here} -h {Server-IP-here} -p {DB-Name-Here}
$ mysql -u root -h 192.168.1.100 -p mysql
$ mysql -u root -h 127.0.0.1 -p mysql
Type the following SHOW VARIABLES LIKE '%ssl%'; command at MariaDB [(none)]> prompt:
MariaDB [(none)]> SHOW VARIABLES LIKE '%ssl%';
OR issue the status command:
MariaDB [(none)]> status;
Sample outputs:
Fig.06: Establish secure connection from console and verifying it
Verify SSL vs TLS connections. The following command should fail as ssl 3 is not supported and configured to use:
$ openssl s_client -connect 192.168.1.100:3306 -ssl3
140510572795544:error:140A90C4:SSL routines:SSL_CTX_new:null ssl method passed:ssl_lib.c:1878:
Check for TLS v 1/1.1/1.2:
$ openssl s_client -connect 192.168.1.100:3306 -tls1
$ openssl s_client -connect 192.168.1.100:3306 -tls1_1
$ openssl s_client -connect 192.168.1.100:3306 -tls1_2
Sample outputs:
CONNECTED(00000003) --- no peer certificate available --- No client certificate CA names sent --- SSL handshake has read 5 bytes and written 7 bytes --- New, (NONE), Cipher is (NONE) Secure Renegotiation IS NOT supported Compression: NONE Expansion: NONE No ALPN negotiated SSL-Session: Protocol : TLSv1 Cipher : 0000 Session-ID: Session-ID-ctx: Master-Key: Key-Arg : None PSK identity: None PSK identity hint: None SRP username: None Start Time: 1485335036 Timeout : 7200 (sec) Verify return code: 0 (ok) ---
How to read tcpdump packet capture file to verify secure communication
Finally, you can use the tcpdump command packet analyzer that runs under the command line to look into port 3306:
$ sudo tcpdump -i eth0 -s 65535 port 3306 -w /tmp/mysql.pcap
Now connect from your PHP/Python/Perl/Ruby mysql app or console mysql app:
$ mysql -u bar -h 192.168.1.100 -p foo
Use the tcpdump to verify that no clear text information including passwords are exchanged between the server and client as follows:
$ tcpdump -r /tmp/mysql.pcap | less
You can read captured traffic with tcpdump or other tools such as Wireshark.
Step 10 – Add a user to MariaDB server
Type the following command:
$ mysql -u root -p
Create a database called foo:
CREATE DATABASE foo;
Add a user named bar for a database called foo:
GRANT ALL ON foo.* TO bar@localhost IDENTIFIED BY 'mypassword' REQUIRE SSL;
Grant access from the web server hosted at 192.168.1.200:
GRANT ALL ON foo.* TO bar@192.168.200 IDENTIFIED BY 'mypassword' REQUIRE SSL;
Make secure connection from bash shell
You can login from console as follows:
$ mysql -u bar -p -h 192.168.1.100 foo
Make secure connection from Python
First install Python interface to MySQL:
$ sudo apt-get install python-mysql.connector
OR for Python v3.x
$ sudo apt-get install python3-mysql.connector
Here is a sample Python code to connect securely using SSL:
#!/usr/bin/python import MySQLdb ssl = {'cert': '/etc/mysql/ssl/client-cert.pem', 'key': '/etc/mysql/ssl/client-key.pem'} conn = MySQLdb.connect(host='192.168.1.100', user='bar', passwd='mypassword', ssl=ssl) cursor = conn.cursor() cursor.execute("SHOW STATUS LIKE 'Ssl_cipher'") print cursor.fetchone()
OR
#!/usr/bin/python # Note (Example is valid for Python v2 and v3) from __future__ import print_function import sys import mysql.connector from mysql.connector.constants import ClientFlag config = { 'user': 'bar', 'password': 'mypassword', 'host': '192.168.1.100', 'client_flags': [ClientFlag.SSL], 'ssl_ca': '/etc/mysql/ssl/ca-cert.pem', 'ssl_cert': '/etc/mysql/ssl/client-cert.pem', 'ssl_key': '/etc/mysql/ssl/client-key.pem', } cnx = mysql.connector.connect(**config) cur = cnx.cursor(buffered=True) cur.execute("SHOW STATUS LIKE 'Ssl_cipher'") print(cur.fetchone()) cur.close() cnx.close()
Sample outputs:
('Ssl_cipher', 'DHE-RSA-AES256-SHA')
Conclusion
You learned how to set up and install SSL certificates between MariaDB server and client. For more information see the official MariaDB site here and recommended MariaDB books here.
- CentOS 8 install MariaDB
- OpenBSD install MariaDB database server
- RHEL 8 install MariaDB
- FreeBSD 11 install MariaDB
- Setup MariaDB SSL and secure connections from clients
🐧 Get the latest tutorials on Linux, Open Source & DevOps via:
- RSS feed or Weekly email newsletter
- Share on Twitter • Facebook • 8 comments... add one ↓
Category | List of Unix and Linux commands |
---|---|
File Management | cat |
Firewall | Alpine Awall • CentOS 8 • OpenSUSE • RHEL 8 • Ubuntu 16.04 • Ubuntu 18.04 • Ubuntu 20.04 |
Network Utilities | dig • host • ip • nmap |
OpenVPN | CentOS 7 • CentOS 8 • Debian 10 • Debian 8/9 • Ubuntu 18.04 • Ubuntu 20.04 |
Package Manager | apk • apt |
Processes Management | bg • chroot • cron • disown • fg • jobs • killall • kill • pidof • pstree • pwdx • time |
Searching | grep • whereis • which |
User Information | groups • id • lastcomm • last • lid/libuser-lid • logname • members • users • whoami • who • w |
WireGuard VPN | Alpine • CentOS 8 • Debian 10 • Firewall • Ubuntu 20.04 |
Nice guide but what if we’re not in the lan and we want to use TLS? Are we supposed to create a new certificate for each IP as a “road warrior”?
A “road warrior”? You need to set up VPN such as WireGuard or OpenVPN. Another option is to copy client certs files to your client:
[mysql@server:~ ]$ scp /etc/mysql/ssl/* user@client:/path/to/client/ssl/
Make sure you edit my.cnf on client for certificates.
Hi,
thank you for this guide. This looks easy. Keep up with this work.
I am a bit confused, that you mentioned php scripts in the beginning of the guide and ended up with python scripts at the end of the tutorial with no php scripts at all. Maybe you could correct one of the two parts of this guide. Just a thought.
can you update this to replace the ca with https://letsencrypt.org/certs/lets-encrypt-x3-cross-signed.pem and certbot commands? thanks a ton.
Hi Vivek,
Thanks for this tutorial! Unfortunately I am stuck in step 7. I added the tls_version to the 50-server.cnf file but then MariaDB won’t restart anymore. The error log says it does not recognise the tls_version variable. I also tries adding it to my.cnf and same thing happens. Any thoughts?
Check /etc/mysql/ssl/ permissions. They are different for different version of Linux and Unix systems. May I know your Linux distro?
Hi Vivek!
Thanks for a thorough step-by-step tutorial! It is much appreciated!
I am installing on debian 10 and have mariadb up and running but am having problems with the ssl generation.
I keep getting errors during the verification: error 19=8 at 0 depth lookup: self signed certificate
happens with both server-cert.pem and client-cert.pem
any ideas?
When OpenSSL asks you for the Common Name for each certificate, use different names.