Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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. http://stackoverflow.com/questions/650238/how-to-show-the-last-queries-executed-on-mysql

share|improve this question

4 Answers 4

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:

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
    
So recording queries is a task that can only be handled by the application and not by the server itself? –  Devin Dixon Dec 31 '12 at 15:58
    
Huh? Please re-read your question. I think you might need to rephrase it. –  longneck Dec 31 '12 at 16:02
    
I think it make sense. Each database/schema is tied to a site, lets use wordpress sites as an example. I'm trying to figure out a way to record the queries that are executed but have this operation controlled by the server and not wordpress logging its own queries. Does thta make sense? –  Devin Dixon Dec 31 '12 at 16:09
    
I still don't get your first comment/question. I pretty clearly answered that MySQL can record queries at the server-level. You then asked a question implying that MySQL can't. Yes, it can. Just not at the database level. But I will update my answer to be more specific. –  longneck Dec 31 '12 at 16:12

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.