Skip to content

Instantly share code, notes, and snippets.

@rohityadavcloud

rohityadavcloud/mysql.notes.txt

Last active July 31, 2024 06:45
Show Gist options
  • Save rohityadavcloud/60aedc050270a007a3e31d624f5a725b to your computer and use it in GitHub Desktop.
Save rohityadavcloud/60aedc050270a007a3e31d624f5a725b to your computer and use it in GitHub Desktop.
Download ZIP
MySQL TLS + CloudStack
Raw
mysql.notes.txt
# CloudStack + MySQL TLS setup, tested on Ubuntu 22.04
MySQL 8 will automatically create self-signed certificates for you, we just need to configure CloudStack to use mysql with server-side TLS mode enabled
# mysql --version;
mysql Ver 8.0.37-0ubuntu0.22.04.3 for Linux on x86_64 ((Ubuntu))
# run mysql & mysql> SHOW VARIABLES LIKE '%ssl%'; to see if SSL is available
# mysql -u root -p --ssl-mode=required
# Next, check if TLS is available using \s;
mysql> \s;
--------------
mysql Ver 8.0.37-0ubuntu0.22.04.3 for Linux on x86_64 ((Ubuntu))
Connection id: 19172
Current database:
Current user: root@localhost
SSL: Cipher in use is TLS_AES_256_GCM_SHA384
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.37-0ubuntu0.22.04.3 (Ubuntu)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/run/mysqld/mysqld.sock
Binary data as: Hexadecimal
Uptime: 26 days 1 hour 27 min 17 sec
# We can change/add the user to require SSL for example with:
> CREATE USER 'developer'@'192.168.0.100/255.255.255.0' IDENTIFIED BY 'yourpassword' REQUIRE SSL;
# or, like alter user 'my_user'@'%' REQUIRE SSL;
# Enforce security in mysqld
# cat /etc/mysql/mysql.conf.d/mysqld.cnf | grep -i secure
require_secure_transport = ON
# Configure MySQL connector configuration in CloudStack's db.properties:
# cat /etc/cloudstack/management/db.properties | grep -i ssl
db.cloud.url.params=prepStmtCacheSize=517&cachePrepStmts=true&sessionVariables=sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'&serverTimezone=UTC&verifyServerCertificate=false&useSSL=true&sslMode=REQUIRED
db.cloud.useSSL=true
# Once verified, check if the 'cloud' user is accessing DB over SSL/TLS:
mysql> select * from performance_schema.threads where PROCESSLIST_USER="cloud"\G; # look for CONNECTION_TYPE
or,
mysql> select t.THREAD_ID,
-> t.PROCESSLIST_USER,
-> t.PROCESSLIST_HOST,
-> t.CONNECTION_TYPE,
-> sbt.VARIABLE_VALUE AS cipher
-> FROM performance_schema.threads t
-> LEFT JOIN performance_schema.status_by_thread sbt
-> ON (t.THREAD_ID = sbt.THREAD_ID AND sbt.VARIABLE_NAME = 'Ssl_cipher')
-> WHERE t.PROCESSLIST_USER IS NOT NULL;
+-----------+------------------+------------------+-----------------+------------------------+
| THREAD_ID | PROCESSLIST_USER | PROCESSLIST_HOST | CONNECTION_TYPE | cipher |
+-----------+------------------+------------------+-----------------+------------------------+
| 42 | event_scheduler | localhost | NULL | NULL |
| 52 | cloud | localhost | SSL/TLS | TLS_AES_256_GCM_SHA384 |
| 53 | cloud | localhost | SSL/TLS | TLS_AES_256_GCM_SHA384 |
| 57 | cloud | localhost | SSL/TLS | TLS_AES_256_GCM_SHA384 |
| 62 | cloud | localhost | SSL/TLS | TLS_AES_256_GCM_SHA384 |
| 63 | cloud | localhost | SSL/TLS | TLS_AES_256_GCM_SHA384 |
| 64 | cloud | localhost | SSL/TLS | TLS_AES_256_GCM_SHA384 |
| 68 | cloud | localhost | SSL/TLS | TLS_AES_256_GCM_SHA384 |
| 88 | root | localhost | Socket | |
+-----------+------------------+------------------+-----------------+------------------------+
9 rows in set (0.00 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment