How can you see a history of queries ran on a MySQL database?

 To view the history of queries run on a MySQL database, there are a few methods you can use. Below are some options:

1. General Query Log (if enabled)

MySQL can log all queries that are executed. To view the history of queries, you must first ensure that the General Query Log is enabled.

• Enable General Query Log: You can enable the General Query Log temporarily (or permanently by modifying the MySQL configuration file).

  Temporarily enable via SQL:

  SET global general_log = 1;
  SET global general_log_file = '/path/to/your/query.log';
  

  Permanently enable (edit my.cnf or my.ini):

  • Open the MySQL configuration file.
  • Add or uncomment the following lines:

    [mysqld]
    general_log = 1
    general_log_file = /path/to/your/query.log
    

  • Restart MySQL to apply the changes.

  View the Query Log:

  • After enabling the General Query Log, MySQL will log all SQL queries to the specified log file. You can read it using a text editor or cat/less command:

    cat /path/to/your/query.log
    

  Disable General Query Log: If you don't want to keep logging queries for performance reasons, disable it:

  SET global general_log = 0;
  

2. MySQL Binary Log (for replication or point-in-time recovery)

If the binary log is enabled for replication or for point-in-time recovery, you can use it to inspect the history of changes to the database (it will log changes such as INSERT, UPDATE, DELETE statements).

• Enable Binary Log (if not enabled): Add this to your my.cnf or my.ini file:

  [mysqld]
  log-bin = /path/to/mysql-bin
  

• Check the Binary Log: You can use the mysqlbinlog utility to read the binary log:

  mysqlbinlog /path/to/mysql-bin.000001
  

  You can filter the log based on dates or specific events.

3. MySQL Audit Plugin (e.g., MySQL Enterprise Audit Plugin)

MySQL provides audit functionality through plugins that can track user activity.

• Install and Configure Audit Plugin: MySQL's Enterprise Edition includes an audit plugin (audit_log), but you can also use third-party audit plugins in the Community Edition.

  To enable the plugin:

  INSTALL PLUGIN audit_log SONAME 'audit_log.so';
  

• View Audit Logs: Audit logs will usually be stored in a file or a database table depending on how the plugin is configured. You can view the logs directly:

  cat /path/to/audit.log
  

4. Using SHOW BINARY LOGS and SHOW BINLOG EVENTS (for binary logs)

If you want to check binary logs directly for events:

SHOW BINARY LOGS;
SHOW BINLOG EVENTS IN 'mysql-bin.000001';

This can help you inspect the sequence of events stored in binary logs.

5. Using SHOW PROCESSLIST

If you want to see currently running queries (not past queries), you can use:

SHOW PROCESSLIST;

This will show you the queries that are currently being executed by active MySQL sessions.

6. Manual Query History via Application Logs

If your application logs queries or the MySQL client you're using has history tracking, you might find queries in your application's log files.

• For MySQL Client: The MySQL client (mysql command-line tool) has its own query history file:
  • On Linux, it’s typically stored in ~/.mysql_history.
  • On Windows, it might be stored in a file like my_history.

Considerations:

• General Query Log: Logs all queries, including SELECT statements. Can have performance overhead, so it should be used carefully, especially on production systems.
• Binary Log: Captures changes to the data (i.e., DML operations like INSERT, UPDATE, DELETE) but not SELECT queries.
• Audit Plugins: Provides detailed logging and filtering options but may require additional setup.

If you are working in a production environment, be cautious when enabling query logging as it can create large log files and impact performance.