db is bring dropped, binlog help
Hi folks, We're experiencing a really bizarre thing. One of our mysql 5.0 databases is mysteriously and constantly being dropped. This is a cap from our binlog: #090409 15:09:13 server id 1 end_log_pos 326997 Query thread_id=9923 exec_time=1 error_code=0 SET TIMESTAMP=1239304153/*!*/; SET @@session.foreign_key_checks=0/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; DROP DATABASE `prod_db3` /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET completion_ty...@old_completion_type*/; This has occured once a day for the past 3 days. Is there any way to track this down further to which mysql account is being used? Should I suspect foulplay or network intrusion? Appreciate any insight and help. Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: db is bring dropped, binlog help
Hi John, I would almost certainly suspect some form of foul play, whether that be internal (i.e. an employee/colleague) or network intrusion. As you've figured the first thing to do is check which MySQL account is dropping the database. You already have the timestamp in your binlog so what you need to is dig around in MySQL's general log file. According to the manual, this log file records not only connections and disconnections, but also the SQL queries sent by the client, so you should be able to see quite clearly the DROP DATABASE statement being issued. See this manual page: http://dev.mysql.com/doc/refman/5.0/en/query-log.html If you already have the query log turned on, then just search for the entries around 15:09:13 09/04/2009 for the DROP statement. If you don't, restart your MySQL server with the logging option turned on and wait for it to happen again! I've just had a quick glance in my server's log and it appears that a connection is given an ID (which is where you see which user it was) then that ID is used throughout the log to indicate which queries have been executed from that connection. Hope you get this sorted out! Andy John Sun wrote: Hi folks, We're experiencing a really bizarre thing. One of our mysql 5.0 databases is mysteriously and constantly being dropped. This is a cap from our binlog: #090409 15:09:13 server id 1 end_log_pos 326997 Query thread_id=9923 exec_time=1 error_code=0 SET TIMESTAMP=1239304153/*!*/; SET @@session.foreign_key_checks=0/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; DROP DATABASE `prod_db3` /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET completion_ty...@old_completion_type*/; This has occured once a day for the past 3 days. Is there any way to track this down further to which mysql account is being used? Should I suspect foulplay or network intrusion? Appreciate any insight and help. Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: db is bring dropped, binlog help
Andy, Thanks a ton for the quick feedback. I will turn on query logging and give it a go! Thanks, John On Thu, Apr 9, 2009 at 6:16 PM, Andy Shellam andy-li...@networkmail.eu wrote: Hi John, I would almost certainly suspect some form of foul play, whether that be internal (i.e. an employee/colleague) or network intrusion. As you've figured the first thing to do is check which MySQL account is dropping the database. You already have the timestamp in your binlog so what you need to is dig around in MySQL's general log file. According to the manual, this log file records not only connections and disconnections, but also the SQL queries sent by the client, so you should be able to see quite clearly the DROP DATABASE statement being issued. See this manual page: http://dev.mysql.com/doc/refman/5.0/en/query-log.html If you already have the query log turned on, then just search for the entries around 15:09:13 09/04/2009 for the DROP statement. If you don't, restart your MySQL server with the logging option turned on and wait for it to happen again! I've just had a quick glance in my server's log and it appears that a connection is given an ID (which is where you see which user it was) then that ID is used throughout the log to indicate which queries have been executed from that connection. Hope you get this sorted out! Andy John Sun wrote: Hi folks, We're experiencing a really bizarre thing. One of our mysql 5.0 databases is mysteriously and constantly being dropped. This is a cap from our binlog: #090409 15:09:13 server id 1 end_log_pos 326997 Query thread_id=9923 exec_time=1 error_code=0 SET TIMESTAMP=1239304153/*!*/; SET @@session.foreign_key_checks=0/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; DROP DATABASE `prod_db3` /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET completion_ty...@old_completion_type*/; This has occured once a day for the past 3 days. Is there any way to track this down further to which mysql account is being used? Should I suspect foulplay or network intrusion? Appreciate any insight and help. Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org