Mysql – How to find MYSQL Logs for Drop Delete Record

MySQL

I am using SQlyog Ultimate for Mysql GUI. I want to know who is deleting, Dropping table and record. I want to track the User Ip and Name.

If there is any method to track please share with me.

I found some Link but not able to understand
How to show the last queries executed on MySQL?

Thanks
Sitansu

Best Answer

You must enable query logging on server as described on the official page:

https://dev.mysql.com/doc/refman/5.7/en/server-logs.html

Here general logging is the easiest approach that can be used to resolve your objective. Link shared by you shows two different methods of achieving the same. You can use following queries as root user to get what you want

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';    

select event_time,user_host,argument
from mysql.general_log
where command_type='Query' and 
upper(argument) rlike '(DROP|DELETE|TRUNCATE)+';

Beware that it will fill server disk space very faster.

Related Solutions

MySQL Authentication – How to Audit Logins on MySQL Database

You would probably want to use the general query log.

The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients.

One important thing with logging for security is that an attacker cannot access the log to erase traces of their presence, so consider append-only files.

FWIW in Oracle we can send logs automatically to a remote syslog, but I don't believe MySQL has this feature yet. Perhaps you could fake it with SNMP but I have not tried it.

Mysql – How should I design a specific needs thesql table

I suggest you create a table with the following fields:

Page URL
Date (Not datetime)
Count

And add a unique key on (page_url, date).

Then, use INSERT INTO .. ON DUPLICATE KEY UPDATE.

So,

CREATE TABLE `page_view` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `page_url` varchar(512) NOT NULL,
  `date` date NOT NULL,
  `count` int(10) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `page_url` (`page_url`,`date`)
)

Then, use this insert:

INSERT INTO page_view (page_url, date) 
VALUES ('www.example.com/page1.html', date(now())) 
ON DUPLICATE KEY UPDATE count=count+1;

Best Answer

Related Solutions

MySQL Authentication – How to Audit Logins on MySQL Database

Mysql – How should I design a specific needs thesql table

Related Question