Easiest way to setup MySQL/MariaDB with TLS/SSL in 10 minutes- v10.5 – Any OS – Ubuntu Focal | Developer Tharun

sudo apt-get install software-properties-common dirmngr apt-transport-https
sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el,s390x] https://mirrors.aliyun.com/mariadb/repo/10.5/ubuntu focal main'

sudo apt update

sudo apt install mariadb-server-10.5

service mysql status

mysql_secure_installation # walk through the process with default options along with the new password when prompted

root@ubuntu-focal:/etc/mysql# mysql_secure_installation 

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

Switch to unix_socket authentication [Y/n] Y
Enabled successfully!
Reloading privilege tables..
 ... Success!

Change the root password? [Y/n] Y
New password: <enter-new-password-here>
Re-enter new password: <enter-new-password-here>
Password updated successfully!
Reloading privilege tables..
 ... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] Y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] Y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] Y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] Y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

mysql -uroot -p
Enter password: <enter-new-password-here>

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 47
Server version: 10.5.13-MariaDB-1:10.5.13+maria~focal mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select user,host from mysql.user;

+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| root        | 127.0.0.1 |
| root        | ::1       |
| mariadb.sys | localhost |
| root        | localhost |
+-------------+-----------+
4 rows in set (0.004 sec)

MariaDB [(none)]> CREATE USER 'vault'@'192.%' IDENTIFIED BY '2%r3o0u8jf@e8owh*hfeu^8f0';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON vault.* TO 'vault'@'%';
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> select user,host from mysql.user;
----------+-----------+
| User        | Host      |
+-------------+-----------+
| vault       | %         |
| root        | 127.0.0.1 |
| root        | ::1       |
| mariadb.sys | localhost |
| root        | localhost |
+-------------+-----------+
5 rows in set (0.004 sec)

mkdir ~/certs
cd ~/certs

# generate CA Key with 4096 bits
openssl genrsa -des3 -out ca.key 4096

# generate CA pem from the ca.key
openssl req -x509 -new -nodes -key ca.key -sha256 -days 900 -out ca.pem

sudo apt-get install -y ca-certificates

#Convert the ca.pem certificate to a ca.crt certificate file.
openssl x509 -outform der -in ./ca.pem -out ./ca.crt

#Copy the ca.crt file to the /usr/local/share/ca-certificates directory.
sudo cp ./ca.crt /usr/local/share/ca-certificates

#Update the certificate store.
sudo update-ca-certificates

# Generate the server.key
openssl genrsa -out server.key 2048

# Generate the CSR and answer the questions
openssl req -new -key server.key -out server.csr
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:IN
State or Province Name (full name) [Some-State]:KA
Locality Name (eg, city) []:Blr
Organization Name (eg, company) [Internet Widgits Pty Ltd]:Developer Tharun
Organizational Unit Name (eg, section) []:Blog  
Common Name (e.g. server FQDN or YOUR name) []:server-fqdn.server.environment
Email Address []:sre@org.com

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []: <not mandatory, can be skipped, hit enter>
An optional company name []: <not mandatory, can be skipped, hit enter>

# Add the below to a file
# server.ext
authorityKeyIdentifier=keyid,issuer
basicConstraints=CA:FALSE
keyUsage = digitalSignature, nonRepudiation, keyEncipherment, dataEncipherment
subjectAltName = @alt_names
[alt_names]
DNS.1 = <mention the server FQDN>

# Create the cert using the Certificate Authority cert and key
openssl x509 -req -in server.csr -CA ca.pem -CAkey ca.key \
-CAcreateserial -out server.crt -days 365 -sha256 -extfile server.ext

# Verify the cert generated using the ca
# This way you will be able to verify that the server cert was created using this CA
openssl verify -CAfile ca.pem server.crt
server.crt: OK

# Create mysql certs folder
mkdir /etc/mysql/certs

cp ~/certs/ca.pem ~/certs/server.crt ~/certs/server.key /etc/mysql/certs

chown -R mysql: /etc/mysql/certs

# Place the config in the below file
# to override the configs in my.cnf
vim /etc/mysql/mariadb.conf.d/50-server.cnf

[mysqld]
bind-address    = 0.0.0.0
ssl_ca=/etc/mysql/certs/ca.pem
ssl_cert=/etc/mysql/certs/server.crt
ssl_key=/etc/mysql/certs/server.key

service mysql restart

mysql -uroot -p  # without any SSL/TLS encryption
<Enter password>

MariaDB [(none)]> \s
--------------
mysql  Ver 15.1 Distrib 10.5.13-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Connection id:          48
Current database:
Current user:           root@localhost
SSL:                    Not in use  #<-----------------------
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.5.13-MariaDB-1:10.5.13+maria~focal mariadb.org binary distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /run/mysqld/mysqld.sock
Uptime:                 3 hours 43 min 54 sec

Threads: 3  Questions: 110  Slow queries: 0  Opens: 34  Open tables: 27  Queries per second avg: 0.008
--------------

mysql -p --ssl-ca=/etc/mysql/certs/ca.pem

MariaDB [(none)]> \s
--------------
mysql  Ver 15.1 Distrib 10.5.13-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Connection id:          33
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.5.13-MariaDB-1:10.5.13+maria~focal mariadb.org binary distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /run/mysqld/mysqld.sock
Uptime:                 2 min 6 sec

Threads: 1  Questions: 68  Slow queries: 0  Opens: 32  Open tables: 25  Queries per second avg: 0.539
--------------

MariaDB [(none)]> SHOW VARIABLES LIKE '%ssl%';
+---------------------+-----------------------------+
| Variable_name       | Value                       |
+---------------------+-----------------------------+
| have_openssl        | YES                         |
| have_ssl            | YES                         |
| ssl_ca              | /etc/mysql/certs/ca.pem     |
| ssl_capath          |                             |
| ssl_cert            | /etc/mysql/certs/server.crt |
| ssl_cipher          |                             |
| ssl_crl             |                             |
| ssl_crlpath         |                             |
| ssl_key             | /etc/mysql/certs/server.key |
| version_ssl_library | OpenSSL 1.1.1f  31 Mar 2020 |
+---------------------+-----------------------------+
10 rows in set (0.001 sec)

MariaDB [(none)]> create user 'tharun'@'%' identified by 'xr7y(#$&*ox8r7#Y$xo87n' REQUIRE SSL; 
Query OK, 0 rows affected (0.004 sec)

MariaDB [(none)]> show grants for 'tharun'@'%';
+-------------------------------------------------------------------------------------------------------------------+
| Grants for tharun@%
  |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `tharun`@`%` IDENTIFIED BY PASSWORD '*A5F8D7B95653CF24C6DC9628BC84B0B2FF89D9DF' REQUIRE SSL |
+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

root@ubuntu-focal:~/certs# mysql -utharun -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'tharun'@'localhost' (using password: YES)