How to Find Out When and By Who a MySQL Database Was Deleted on CentOS 7

Posted on by admin

If you are managing a MySQL server on CentOS 7, you may encounter a situation where you need to find out when and by who a database was deleted. This can be useful for auditing, troubleshooting, or recovering purposes. In this blog post, we will show you how to use the MySQL log files to track down the deletion events and identify the responsible users.

What are MySQL log files?

MySQL log files are files that record various information about the activity and status of your MySQL server. There are different types of log files that MySQL can generate, such as:

  • Error log: This log file contains information about errors that occur while the server is running or during startup or shutdown. It also records any messages from storage engines or plugins. The error log can help you diagnose problems and fix bugs in your MySQL server.
  • General query log: This log file contains information about every statement that clients execute on the server, such as connection and disconnection events, queries, and results. The general query log can help you monitor the activity and performance of your MySQL server and clients.
  • Binary log: This log file contains information about all changes to the data and schema of your databases. The binary log can also be used for replication and backup purposes. The binary log can help you recover data from accidental deletion or corruption, or restore a database to a previous state.
  • Slow query log: This log file contains information about queries that take longer than a specified amount of time to execute or do not use indexes efficiently. The slow query log can help you identify and optimize slow queries that affect the performance of your MySQL server.

Depending on how your MySQL server is configured, these log files may be located in different directories or have different names. You can check your /etc/my.cnf file to see where your log files are stored and what they are called.

How to enable binary logging?

The most relevant log file for finding out when and by who a database was deleted is the binary log. However, binary logging is not enabled by default in MySQL. To enable it, you need to add the following line to your /etc/my.cnf file under [mysqld] section:

log-bin=/var/log/mysql/mysql-bin.log

You can change the path and name of the binary log file as you wish. You also need to restart your MySQL server for the changes to take effect.

How to view the binary log file?

To view the contents of the binary log file, you can use the mysqlbinlog utility. For example:

mysqlbinlog /var/log/mysql/mysql-bin.log

This will display all the events recorded in the binary log file in a human-readable format. You can look for DROP DATABASE statements that indicate when a database was deleted and by which user. For example:

# at 4
#210101 12:34:56 server id 1 end_log_pos 123 CRC32 0x12345678 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1609504496/*!*/;
DROP DATABASE test
/*!*/;
DELIMITER ;
End of log file

This shows that on January 1st 2021 at 12:34:56 UTC, user root (thread_id=1) deleted a database named test.

You can also use various options with mysqlbinlog to filter or limit the output based on date, time, event type, database name, etc. For more information on how to use mysqlbinlog, please refer to https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog.html.

How to prevent binary logs from being deleted?

Binary logs may be rotated or purged periodically based on your configuration or manual commands. Therefore, you may not be able to find older events if they have been deleted from the logs. To prevent this from happening, you can adjust your expire_logs_days or max_binlog_size settings in /etc/my.cnf file or use PURGE BINARY LOGS command with caution.

expire_logs_days specifies how many days old binary logs should be kept before being automatically removed by mysqld_safe script (default is zero). For example:

expire_logs_days = 30

This will keep binary logs for up to 30 days before deleting them.

max_binlog_size specifies how large each individual binary log file should be before being rotated (default is 1GB). For example:

max_binlog_size = 500M

This will rotate binary logs after they reach 500MB in size.

PURGE BINARY LOGS TO 'mysql-bin.0003';

This will delete all binary logs before mysql-bin.0003.

PURGE BINARY LOGS BEFORE '2021-01-15 10:00:00';

This will delete all binary logs before January 15th 2021 at 10:00:00 UTC.

Be careful when using PURGE BINARY LOGS command, as it may affect replication or backup operations that rely on the binary logs. You should always make sure that you have a backup of your data and that your replicas have processed all the events in the binary logs before deleting them.

How to secure your log files from unauthorized access?

Log files may contain sensitive information such as user names, passwords, queries, results, etc. Therefore, it is important to secure them from unauthorized access or tampering. Here are some tips to secure your log files:

  • Use appropriate file permissions and ownership for your log files. By default, MySQL creates log files with read and write permissions for the mysql user and group only. You can use chmod and chown commands to change these permissions and ownership if needed.
  • Use encryption for your log files. You can use SSL/TLS encryption to encrypt the communication between your MySQL server and clients or replicas. You can also use encryption tools such as GPG or OpenSSL to encrypt your log files on disk.
  • Use audit logging plugins for MySQL. Audit logging plugins allow you to record additional information about who accessed what data and when on your MySQL server. You can use audit logging plugins such as MariaDB Audit Plugin or Percona Audit Log Plugin to enable audit logging on your MySQL server.
  • Use log analysis tools for MySQL. Log analysis tools allow you to monitor, analyze, and alert on your log files. You can use log analysis tools such as Logstash or Splunk to collect, parse, index, search, visualize, and alert on your log files.

Conclusion

In this post, we have shown you how to find out when and by who a MySQL database was deleted on CentOS 7 using the binary log file. We have also shown you how to enable binary logging, view the binary log file, prevent binary logs from being deleted, and secure your log files from unauthorized access. We hope this blog post was helpful for you and that you learned something new about MySQL logging.