MySQL Audit Log

mysql-audit-log.sql

-- http://mysqlhints.blogspot.ae/2011/01/how-to-log-user-connections-in-mysql.html
-- http://stackoverflow.com/questions/2627058/get-current-session-process-id-from-inside-a-mysql-query
-- http://www.xaprb.com/blog/2006/07/23/how-to-track-what-owns-a-mysql-connection/
-- http://www.softtreetech.com/idbaudit.htm


DROP TABLE IF EXISTS mysql.audit_connections;
CREATE TABLE mysql.audit_connections (
  `audit_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Audit ID',
  `connections_counter` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Total number of connections',
  `connection_id` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Internal Connection ID',
  `user_host` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'user@host Parameter',
  `connected_on` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Last connection time',
  PRIMARY KEY (`audit_id`),
  UNIQUE KEY `connection_id_user_host_key` (`connection_id`,`user_host`)
) ENGINE=MYISAM COMMENT='Auditing MySQL Connections';


-- CURRENT_USER | SYSTEM_USER
SET GLOBAL init_connect = "
SET collation_connection = utf8_general_ci;
SET NAMES utf8;
SET character_set_server = utf8;
SET collation_server = utf8_unicode_ci;
INSERT INTO mysql.audit_connections (
	`connection_id`, user_host, connected_on
) VALUES (
	CONNECTION_ID(), SYSTEM_USER(), NOW()
) ON DUPLICATE KEY UPDATE
	connections_counter = connections_counter+1,
	connected_on = NOW()
;";


-- GRANT INSERT ON TABLE mysql.audit_connections TO '%USER%'@'%HOST%';
-- SELECT CONCAT('GRANT INSERT ON TABLE mysql.audit_connections TO \'', USER,'\'@\'', HOST, '\';') permit_audits_sql FROM mysql.user;

-- SELECT connections_counter counter, `connection_id`, user_host, connected_on FROM mysql.audit_connections;
-- SELECT * FROM mysql.audit_connections;


-- -------------------------

INSERT INTO mysql.audit_connections (
	`connection_id`, user_host, connected_on
) VALUES (
	CONNECTION_ID(), CURRENT_USER(), NOW()
) ON DUPLICATE KEY UPDATE
	connections_counter = connections_counter+1,
	connected_on = NOW()
;

-- SELECT @@init_connect;
-- SHOW VARIABLES LIKE 'init_connect';


SET GLOBAL general_log = 'on';

SELECT CURRENT_USER(), SYSTEM_USER();

INSERT INTO mysql.audit_connections (
	user_host, `connection_id`, lastconnect
) VALUES (
	CURRENT_USER(), 1, NOW()
) ON DUPLICATE KEY UPDATE
	connections=connections+1,
	lastconnect=NOW()
;

SET GLOBAL init_connect = "INSERT INTO admin.connections (`user_host`, `connections`, `lastconnect`) VALUES (CURRENT_USER(), 1, NOW()) ON DUPLICATE KEY UPDATE `connections`=`connections`+1, lastconnect=NOW();"



-- net stop mysql
-- net start mysql
SELECT * FROM mysql.audit_connections;

SELECT CURRENT_USER(), CONNECTION_ID();



REPLACE INTO connection_info
   (CONNECTION_ID, unix_proc_id, prog_name, ts)
   SELECT CONNECTION_ID(), '$PID', '$PROGRAM_NAME', CURRENT_TIMESTAMP
   
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;