It is important that you use 192.168.1.100 as common name while creating SSL certificates.

Step 1 – Install MariaDB

Type the command as per your Linux or Unix variant.

Install MariaDB server/client on Ubuntu/Debian Linux

Type the following apt-get command or apt command:
$ sudo apt-get install mariadb-server mariadb-client

Install MariaDB server/client on CentOS/RHEL/Fedora Linux

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

Install MariaDB server/client on FreeBSD unix

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

Step 2 – Secure MariaDB

Type the following command:
$ mysql_secure_installation
Sample outputs:

Step 3 – Create the CA certificate

Make a directory named ssl in /etc/mysql/ directory:
$ 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 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
Sample outputs:

1. /etc/mysql/ssl/ca-cert.pem – Certificate file for the Certificate Authority (CA).
2. /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 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:

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:

1. /etc/mysql/ssl/server-cert.pem – MariaDB server certificate file.
2. /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 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:

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

Save and close the file. You can restart mariadb as follows:
$ sudo /etc/init.d/mysql restart
OR
$ sudo systemctl restart mysql

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
### 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:
[email protected]}: rsync /etc/mysql/ssl/ca-cert.pem /etc/mysql/ssl/client-cert.pem /etc/mysql/ssl/client-key.pem \
[email protected]:/etc/mysql/ssl


Step 9 – Verification

Type the following 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:

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 [email protected] IDENTIFIED BY 'mypassword' REQUIRE SSL;
Grant access from the web server hosted at 192.168.1.200:
GRANT ALL ON foo.* TO [email protected] 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')