MySQL-MariaDB Registry files


One feature from MySQL (also from MariaDB) very useful for DBA is registry files, that stores everything of what is happening on the server. You have several alternatives. By default, all registry files are located on the data dir, although you can force to save it to a different path (you must do this using the config file of the server my.ini).

Error registry

It saves all information related to when the server starts up and stops using mysqld and also, if any critical error was raised while running. This information can ve really useful if you have any problem with the server, to give you a clue of what’s not running well.

Query registry

It stored all connections and queries sent to the server on a file called, normally, SERVER_NAME.log. This log file is also very useful whenever you suspect there is an error on a client and you need to know what was the query sent to mysqld. You may notice that the order or the queries . You can rename the file of this error log, but you must do it with the server stopped.

Binary Registry

It stores all queries that updated data or could update data, for example, a DELETE query with no registers to remove, in a very efficient format and on a safe way due to transactions.

Other useful information of this registry is the time spent on each queri, although the execution couldn’t finished.

The purpose of this registry is to update the database during a recovery operation as complete as it could be, because this binary registry contains all queries stored after a backup was made.

If you need to clean this registry, use the command PURGE MASTER LOGS. Of course, avoid deleting the binary log files directly from the server if you don’t want extra troubles!

Slow querys registry

It’s a file that stores all SQL queries not executed after a time, assined on the long_query_time variable.  This way, you can know what SQL queries you need to imrpove and optimize.

Have a nice day!!