MySQL : Separate Log File for Each Database

Question

I have a server with multiple databases on it. Its actually an instance on AmazonRDS so I do not have access to the my.cnf file. What I am trying to do is a create a log for each database separately that records the queries that were previously ran and possible errors generated by those queries. Is this possible with mysql to have a separate log file for each db/schema?

Update

Im looking for a solution like this, but not for the WHOLE server. https://stackoverflow.com/questions/650238/how-to-show-the-last-queries-executed-on-mysql

Answer 1

I cannot speak in terms of AmazonRDS but here is something generic to try

Did you know that the slow log is a CSV file it you activate it?

If you look at the mysql.slow_log, you see this:

mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.00 sec)

mysql>

This is no good to you as a CSV file. You can convert it to MyISAM and use it.

Here is what you can do:

• Make a copy the original CSV slow log
• Convert the CSV to MyISAM
• Add two indexes
  • by start_time
  • by db,start_time
• Activate the slow log

Here are the basic steps:

CREATE TABLE mysql.slow_log_original LIKE mysql.slow_log;
ALTER TABLE mysql.slow_log ENGINE=MyISAM;
ALTER TABLE mysql.slow_log ADD INDEX starttime (start_time);
ALTER TABLE mysql.slow_log ADD INDEX db_starttime (db,start_time);

Next, enable logging to mysql.slow_log by adding this to /etc/my.cnf

[mysqld]
slow-query-log
log-format=TABLE

If you still want a text file for slow queries in addition to a table, then use

[mysqld]
slow-query-log
slow-query-logfile=/var/log/mysql_slow.log
log-format=TABLE

Now you are probably wondering why I am showing the mysql.slow_log table and not mysql.general_log. Well, here is mysql.general_log:

mysql> desc mysql.general_log;
+--------------+------------------+------+-----+-------------------+-----------------------------+
| Field        | Type             | Null | Key | Default           | Extra                       |
+--------------+------------------+------+-----+-------------------+-----------------------------+
| event_time   | timestamp        | NO   | MUL | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| user_host    | mediumtext       | NO   |     | NULL              |                             |
| thread_id    | int(11)          | NO   |     | NULL              |                             |
| server_id    | int(10) unsigned | NO   |     | NULL              |                             |
| command_type | varchar(64)      | NO   |     | NULL              |                             |
| argument     | mediumtext       | NO   |     | NULL              |                             |
+--------------+------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.01 sec)

mysql>

There is nothing that points a query to the database in mysql.general_log.

Oh, by the way, you have to set other things to get the mysql.slow_log catch everything

[mysqld]
slow-query-log
log-format=TABLE
log-query-time=0
log-queries-not-using-indexes

That way, every query and its grandmother gets recorded in mysql.slow_log.

EPILOGUE

All you need do is run queries against mysql.slow_log. For example, to see all queries from the mydb database for today, you would run:

SELECT * FROM mysql.slow_log
WHERE db = 'mydb'
AND start_time >= DATE(NOW()) + INTERVAL 0 SECOND;

Since you are using Amazon RDS, you will have to check the Documentation to see if you can manipulate mysql.slow_log to your liking.

Here are my past posts in the StackExchange about using mysql.slow_log:

• Nov 18, 2011 : Create a custom log table in MySQL
• Dec 27, 2011 : How to do MySQL User Accounting
• Mar 30, 2012 : MySQL slow log configuration
• Nov 06, 2012 : AWS RDS MySQL / Slow Query Log

Answer 2

This isn't possible. The query log is a server-level setting.

If you want a query log for each database, you will need to use a different product. Possible candidates are MySQL Proxy or getting your application or application framework to log queries.

Answer 3

take a look at pt-query-digest - it can take as an input output of the tcpdump and it seems it's possible to create custom filters to extract only queries of a specific type [probably also queries to a specific database].

note that this can be good troubleshooting tool but it's not very handy solution for permanent logging.

Answer 4

RDS supports enabling the General Log via a DB Parameter Group setting. By default it goes to TABLE, but you can modify the log_output to FILE and reboot the DB instance, then you can download and parse the file via the Log Access API. (http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.Concepts.MySQL.html)

With MySQL 5.6 in RDS, you can use the binlog access functionality to stream binlogs to another environment and manipulate them as you see fit.