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 …

Recently, I saw many interest in understanding and getting information about database locking.

MySQL InnoDB’s locking can be complex and having an overview not always simple.

For more information about how InnoDB locking is working, I can only recommend this excellent series of articles by Kuba:

• https://dev.mysql.com/blog-archive/innodb-data-locking-part-1-introduction/
• https://dev.mysql.com/blog-archive/innodb-data-locking-part-2-locks/
• https://dev.mysql.com/blog-archive/innodb-data-locking-part-2-5-locks-deeper-dive/
• …

This morning, a user asked in the MySQL Community Slack if somebody had an idea why the error log was filled up continuously with a warning messages like this one:

2020-07-24T06:54:00.877128Z 46 [Warning] [MY-013360] [Server] 
Plugin sha256_password reported: ''sha256_password' is deprecated and will be removed in 
a future release. Please use caching_sha2_password instead'

The context was that the user upgraded from MySQL 5.7 to MySQL 8.0 and changed the authentication plugin of all users to caching_sha2_password.

As the warning message was written to the error log almost twice per second, I suspected a monitoring tool. The user said, that no application, no monitoring was using this server yet.

Of course we verified that all users had the plugin changed.

This is important when helping people (friends, colleagues, …

In MySQL, it’s very common and recommended to you an auto_increment as Primary Key.

However that integer can have different types each having their size limitation, and they can also be signed or not.

Many people when creating a table use by default the following syntax do define their auto_increment primary key:

id int auto_increment primary key

This is a signed integer where the maximum value is 2,147,483,647.

It’s recommended as a DBA to keep an eye on how fast your tables are filling up and how much records can still be added to your tables.

And this can happen even to the bests ! (see this Github Availability Report on May 5th at 00:45)

MySQL Shell Plugin

Once again, MySQL Shell comes to the rescue with an easy plugin: …

Yesterday, Bhuvanesh published an article about how to verify the difference between allocated diskspace for a tablespace and the the data in it.

I commented with an old post explaining how to get some similar info only using SQL in case you don’t have filesystem access.

And finally, my friend Bill Karwin, commented how this info is not always accurate. Which, of course, I agree with.

This is why, I checked …

Do you know that it’s possible to get information from the CPUs of your MySQL Server from SQL ?

If you enable the status for the  INNODB_METRICS table in INFORMATION_SCHEMA, you will be able to query CPU information.

First, check if those status are enabled:

MySQL> SELECT name, subsystem, status 
FROM INFORMATION_SCHEMA.INNODB_METRICS where NAME like 'cpu%';
+---------------+-----------+----------+
| name          | subsystem | status   |
+---------------+-----------+----------+
| cpu_utime_abs | cpu       | disabled |
| cpu_stime_abs | cpu       | disabled |
| cpu_utime_pct | cpu       | disabled |
| cpu_stime_pct | cpu       | disabled |
| cpu_n         | cpu       | disabled |
+---------------+-----------+----------+
5 rows in set (0.00 sec)

It seems, that this is a question that regularly shows up in forums or stackoverflow.

To start, let’s highlight the fact that in MySQL 8.0 it’s not any more possible to create a user directly from the GRANT command (ERROR 1410 (42000): You are not allowed to create a user with GRANT).

This means that to grant some privileges, the user must be created first.

Let’s create a user ‘user1‘ with ‘ChangeMe‘ as password that the user will have to change:

mysql> create user 'user1' identified by 'ChangeMe' password expire;
Query OK, 0 rows affected (1.35 sec)

Let’s try to connect to MySQL using that new created user:

$ mysql -u …

As you know, MySQL uses some keywords and some of them are also reserved.

Let’s have a look how to deal with that:

mysql> create table WRITE (id int auto_increment primary key, varying varchar(10), than int);
 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near 'WRITE (id int auto_increment primary key, 
varying varchar(10), than int)' at line 1

OK, it seems WRITE is a keyword I cannot use as table name. I’ve then two choices:

• rename the table to something else like WRITE_TBL
• use back-ticks (`) around the table like `WRITE`

Let’s use the first option:

mysql> create table WRITE_TBL (id int auto_increment primary key, varying varchar(10), than …

For the third year in a row, we will take advantage of the mass presence of our MySQL Engineers during FOSDEM to organize the pre-FOSDEM MySQL Day.

The program of this 3rd edition is already on track, thank you to all the speakers who already confirmed their participation.

Many of you already know how to retrieve the size of your dataset, schemas and tables in MySQL.

To summarize, below are the different queries you can run:

Dataset Size

I the past I was using something like this :

But now with sys schema being installed by default, I encourage you to use some of the formatting functions provided with it. The query to calculate the dataset is now:

SELECT sys.format_bytes(sum(data_length)) DATA,
       sys.format_bytes(sum(index_length)) INDEXES,
       sys.format_bytes(sum(data_length + index_length)) 'TOTAL SIZE'
FROM information_schema.TABLES ORDER  BY data_length + index_length;

Let’s see an example: