Audit your MySQL or MariaDB database

Just as you take care to monitor and keep your applications up to date, it is also important to take care of the engines that serve your data because it is the most critical brick of your application: if your data is corrupted or cannot be updated in the time allowed, your application will be directly impacted.

It is therefore important to audit your database, even if you do not yet encounter any particular problem but in order to warn you to be victim of your success afterwards.

In this article, I deliberately chose to rely on MySQL / MariaDB databases, having had experience auditing this database engine.

First of all, know your database

The most important thing to manage a database is to know the data and to be aware of the use of each table.

The following query will therefore help you to get a quick overview of a database:

SELECT table_name, engine, row_format, table_rows, ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "size (mb)"
FROM information_schema.tables
WHERE table_schema = "<database>"
ORDER BY (data_length + index_length) DESC;

This request will return something looking to the following: