MySQL/MariaDB setup
In this tutorial we will look at how to setup MariaDB/MySQL including SSL/TLS. This will enable the clients connecting with the MySQL server. We will add repository, install mariadb-server, generate certs, place them in the right folder, edit the configurations and test it.
Link to fetch the respective repository
Link to the MariaDB Downloads page
On the above page:
- Choose a distribution
- Choose a MariaDB Server version
Once you do this, you will be able to view the commands to install MariaDB Server on your system of any operating system ( OS ). In this tutorial I have chosen Ubuntu Focal ( 20.04 ) and a MariaDB Server version of 10.5
Add Apt Repositories
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
Install MariaDB server
sudo apt install mariadb-server-10.5
Setup root user
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!
Login into MySQL
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)
TLS Setup of MariaDB
We have setup MySQL/MariaDB above without TLS. For most of the setups the above would be sufficient. To take it to the next level, we will add TLS ability here such that the data transferred between the client and MySQL is encrypted and secure.
Generation of certificates
There are several ways to generate certificates. If you are at an organization, it probably has a standard way to generate a CA. You are free to go ahead and get the CA Cert, Server Cert and Server Key using that way. If not, follow the below steps. No Pre-requisites required.
Generate CA ( Certificate Authority ) Key and Certificates
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
How to make the operating system trust this self signed cert?
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
Generation of Server key and Server cert
We will generate server key and use it to generate a CSR ( Certificate Signing Request ). We will take this to the Certificate Authority and get it signed by the CA. Thus we will get the server cert. In this process we will also use the server extensions.
# 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
Place the certs in the MySQL folder
# 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
Configure MySQL Server
# 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
Restart MySQL
service mysql restart
We will be able to login without certificate too, as a root user. When we do this, the connection will not use SSL connection, the data will not be encrypted. Let's confirm that
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
--------------
We can see above that SSL is not in use. Let us login using SSL abilities
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)
How to Force users to use SSL?
We saw that the root user had a choice to choose between SSL and non-SSL connection. But what if we wanted to force a user and require SSL connection else drop the connection. This is possible by creating the user with REQUIRE SSL.
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)
The user has been created. Now exit out of MySQL prompt and login using the tharun user
root@ubuntu-focal:~/certs# mysql -utharun -p
Enter password:
ERROR 1045 (28000): Access denied for user 'tharun'@'localhost' (using password: YES)
We see that the user is not able to login without the ca.pem file. Here onwards the client will need the ca.pem file in order to connect to MySQL Server.
More trending articles on Hashicorp Vault:
What is Vault? Why do we need it?
Set up a Vault Dev and Production server in 5 minutes:
Thank you for reading, This is Tharun Shiv a.k.a Developer Tharun
You can find more articles here: https://dev.to/developertharun
Roadrunners is a series that is aimed at delivering concepts as precisely as possible. Here, a roadrunner is referred to as a person who does things super fast & efficiently. Are you a roadrunner?
Thank you
Top comments (2)
how to creat the client key
can u pls explain how to create the client key ??