As a MySQL DBA, you like to know who is connected on the system you manage. You also like to know who is trying to connect.
In this article, we will discover how we can retrieve the information and control who is using the MySQL DB instance we launched in OCI.
Secure Connections
The first thing we can check is that all our clients encrypt their connection to the MySQL server.
We use again Performance_Schema to retrieve the relevant information:
select connection_type, substring_index(substring_index(name,"/",2),"/",-1) name,
sbt.variable_value AS tls_version, t2.variable_value AS cipher,
processlist_user AS user, processlist_host AS host
from performance_schema.status_by_thread AS sbt
join performance_schema.threads AS t
on t.thread_id = sbt.thread_id
join performance_schema.status_by_thread AS t2
on t2.thread_id = t.thread_id
where sbt.variable_name = 'Ssl_version' and t2.variable_name = 'Ssl_cipher'
order by connection_type, tls_version;
+-----------------+-------------+-------------+-----------------------------+----------+------------+
| connection_type | name | tls_version | cipher | user | host |
+-----------------+-------------+-------------+-----------------------------+----------+------------+
| SSL/TLS | mysqlx | | | admin | 10.0.0.184 |
| SSL/TLS | thread_pool | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | ociadmin | localhost |
| SSL/TLS | thread_pool | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | ociadmin | localhost |
| SSL/TLS | thread_pool | TLSv1.2 | ECDHE-RSA-AES128-GCM-SHA256 | admin | 10.0.0.159 |
| SSL/TLS | thread_pool | TLSv1.2 | ECDHE-RSA-AES128-GCM-SHA256 | admin | 10.0.1.237 |
| SSL/TLS | thread_pool | TLSv1.2 | ECDHE-RSA-AES128-GCM-SHA256 | admin | 10.0.1.237 |
| SSL/TLS | thread_pool | TLSv1.2 | ECDHE-RSA-AES128-SHA256 | admin | 10.0.0.159 |
| TCP/IP | thread_pool | | | fred | 10.0.0.184 |
+-----------------+-------------+-------------+-----------------------------+----------+------------+
8 rows in set (0.0011 sec)We can see that one connection is not using SSL/TLS (the last line). But we can also notice that the first connection is not exporting the TLS version and cipher. This is because the X protocol doesn’t export these informations as status variable.
We can also notice that all encrypted connections are using the same TLS version (1.2) but different ciphers.
In fact, MDS, for the moment, only allows TLSv1.2:
show global variables like 'tls_version';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| tls_version | TLSv1.2 |
+---------------+---------+
1 row in set (0.0019 sec)This variable cannot be changed by the user or from DB Instance configuration as is the case when using MySQL on site.
We also saw that there is one connection not using SSL (the one by the user fred).
If we want to force the user to use encrypted connections to our MySQL DB system, we modify the user like this:
alter user fred require ssl;
Query OK, 0 rows affected (0.0023 sec)
An on the MySQL DB Instance, we can also verify this:
+-----------------+-------------+-------------+-----------------------------+------+------------+
| connection_type | name | tls_version | cipher | user | host |
+-----------------+-------------+-------------+-----------------------------+------+------------+
| SSL/TLS | thread_pool | TLSv1.2 | ECDHE-RSA-AES128-GCM-SHA256 | fred | 10.0.0.184 |
+-----------------+-------------+-------------+-----------------------------+------+------------+Failed Connections
As a DBA, you also need to verify who is trying to connect unsuccessfully to your database server. It could be a user without the right credentials, using a non supported TLS or cipher version or eventually a malicious person/program.
MySQL Database Service is provided with the connection control plugin enabled.
We can verify the failed attempts from the information_schema.connection_control_failed_loin_attempts table:
select * from information_schema.connection_control_failed_login_attempts;
+---------------------+-----------------+
| USERHOST | FAILED_ATTEMPTS |
+---------------------+-----------------+
| 'repl'@'10.0.0.159' | 3 |
| ''@'10.0.0.184' | 1 |
| ''@'10.0.0.159' | 2 |
| 'fred'@'%' | 2 |
+---------------------+-----------------+
4 rows in set (0.0005 sec)And in the error log we have more details about these failed connections:
select logged, data from performance_schema.error_log where error_code in ('MY-010926', 'MY-010914') order by logged desc limit 10;
+----------------------------+---------------------------------------------------------------------------------------------------------------------------+
| logged | data |
+----------------------------+---------------------------------------------------------------------------------------------------------------------------+
| 2023-03-21 10:37:33.384074 | Access denied for user 'fred'@'10.0.0.159' (using password: YES) |
| 2023-03-21 10:37:30.890519 | Access denied for user 'fred'@'10.0.0.159' (using password: YES) |
| 2023-03-21 09:11:33.918165 | Access denied for user 'fred'@'10.0.0.184' (using password: YES) |
| 2023-03-21 09:09:45.942667 | Access denied for user 'fred'@'10.0.0.184' (using password: YES) |
| 2023-03-21 09:09:38.643923 | Access denied for user 'fred'@'10.0.0.184' (using password: YES) |
| 2023-03-21 09:09:35.486164 | Access denied for user 'fred'@'10.0.0.184' (using password: YES) |
| 2023-03-21 09:06:27.652380 | Aborted connection 622 to db: 'unconnected' user: 'fred' host: '10.0.0.184' (Got an error reading communication packets). |
| 2023-03-21 09:06:27.652297 | Got an error reading communication packets |
| 2023-03-21 09:04:05.260232 | Aborted connection 620 to db: 'unconnected' user: 'fred' host: '10.0.0.184' (Got an error reading communication packets). |
| 2023-03-21 09:04:05.260165 | Got an error reading communication packets |
+----------------------------+---------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.0007 sec)We can see in connection_control_failed_login_attempts only 2 failed attempts for the user fred but in the error log we see much more, what does that mean ?
In fact it seems that before the last 2 attempts, that user was able to connect. This successful connection, reset the failed attempts but the previous related entries are of course kept in the error_log.
In MDS, the connection_control variables are pre-defined and cannot be changed. These are the values:
select * from performance_schema.global_variables
where variable_name like 'connection_control%';
+-------------------------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-------------------------------------------------+----------------+
| connection_control_failed_connections_threshold | 3 |
| connection_control_max_connection_delay | 10000 |
| connection_control_min_connection_delay | 1000 |
+-------------------------------------------------+----------------+In Performance_Schema, there is a table providing us some useful summary: host_cache:
select ip, count_ssl_errors, count_handshake_errors, count_authentication_errors
from host_cache;
+------------+------------------+------------------------+-----------------------------+
| ip | count_ssl_errors | count_handshake_errors | count_authentication_errors |
+------------+------------------+------------------------+-----------------------------+
| 10.0.0.159 | 0 | 2 | 3 |
| 10.0.0.184 | 4 | 4 | 0 |
| 10.0.1.237 | 0 | 0 | 0 |
+------------+------------------+------------------------+-----------------------------+
3 rows in set (0.0006 sec)Limits
In MySQL Database Service on OCI, you have multiple ways to limit the connections:
- using configuration settings (from OCI console)
- defining a limitation for a user
Limitations by Configuration
In MDS, you have the possibility to modify the following settings using a DB Instance Configuration:
Some of these variables are usually well known like max_connections and connect_timeout but some others might look new for some users.
max_connect_errors
In MDS, the default for this variable is the maximum possible. We don’t want to block a host (that is usually using a secure network). But you have the possibility to modify this setting as shown above.
On my system, I’ve changed the value of max_connect_errors to 3:
select @@max_connect_errors;
+----------------------+
| @@max_connect_errors |
+----------------------+
| 3 |
+----------------------+
1 row in set (0.0006 sec)Now if I try to access the MySQL TCP/IP port (3306) with telnet for example, after 3 attempts, the host will be blocked:
$ telnet 10.0.1.33 3306
Trying 10.0.1.33...
Connected to 10.0.1.33.
Escape character is '^]'.
Host '10.0.0.184' is blocked because of many connection errors;
unblock with 'mysqladmin flush-hosts'Connection closed by foreign host.Please note that invalid credentials are not taken into account for max_connect_errors.
But now, even if I try to connect using MySQL Shell, it will fail as the host is blocked:
select ip, sum_connect_errors, count_host_blocked_errors
from host_cache where SUM_CONNECT_ERRORS >= @@max_connect_errors;
+------------+--------------------+---------------------------+
| ip | sum_connect_errors | count_host_blocked_errors |
+------------+--------------------+---------------------------+
| 10.0.0.184 | 3 | 4 |
+------------+--------------------+---------------------------+Pay attention that you need to use mysqladmin to flush the hosts as in SQL it won’t work:
flush hosts;
ERROR: 1227 (42000): Access denied; you need (at least one of) the RELOAD privilege(s) for this operationBut this will work (from a compute instance for example):
# mysqladmin flush-hosts -h 10.0.1.33 -u admin -p
Enter password:memory tracking
With MySQL 8.0 and MySQL Database Service, it is possible to track and limit the memory consumption of the connections. The limitation doesn’t apply to users with CONNECTION_ADMIN privilege.
Once global_connection_memory_tracking is enabled, it’s possible to limit the memory consumption using global_connection_memory_limit and connection_memory_limit.
The current global memory consumption (including background threads and admin) can be returned by the following query:
SELECT format_bytes(variable_value) global_connection_memory
FROM performance_schema.global_status
WHERE variable_name='Global_connection_memory';
+--------------------------+
| global_connection_memory |
+--------------------------+
| 16.22 MiB |
+--------------------------+User Limitations
We also have the possibility to add some limitations for each users. These settings permit the DBA to limit individual accounts to use too many resources.
The MySQL DBA can then limit:
- the number of queries an account can issue per hour (
max_queries_per_hour) - the number of updates an account can issue per hour (
max_updates_per_hour) - the number of times an account can connect to the MySQL DB instance per hour (
max_connections_per_hour) - the number of simultaneous connections to the DB instance by an account (
max_user_connections)
Let’s use again the previous account fred and let limit the amount of queries it can achieve per hour:
alter user fred with max_queries_per_hour 5;If the user fred tries to run more than 5 queries in one hour, it will get the following error:
ERROR: MySQL Error 1226 (42000): User 'fred' has exceeded the 'max_questions'
resource (current value: 5)The current per hour counter is not exposed. And in MDS, to reset any of these limitations, you cannot use FLUSH PRIVILEGES or mysqladmin reload, but you have to use FLUSH USER_RESOURCES.
Conclusion
In this article we learned how to control the connections to the MySQL DB instance in OCI.
We also learned how to add some limitations globally or per user to not consume all the resources on the system.
