Are there other ways this has been dealt with?
If you use MySQL Replication, you could have a Master/Slave that has the following:
- AUDIT_LOG as InnoDB on the Master
- AUDIT_LOG as ARCHIVE on the Slave
This would provide fast writing of the AUDIT_LOG on the Master and makes the data available for reads almost instantly. The Slave would have the backup as an ARCHIVE with a small disk footprint.
DO NOT SEND ANY ALTER TABLE SQL AGAINST AUDIT_LOG ONCE REPLICATION IS SET UP. Otherwise, you lose the small disk footprint feature of the ARCHIVE Storage Engine.
Any drawbacks or trade-offs that are apparent?
You must configure InnoDB
Linux's Viewpoint
You should run this Linux command
history | grep mysqladmin
This will let you see if anyone ran a shutdown from within the server. Note that this will not let you see remote mysqladmin shutdowns. Perhaps running tcpdump and locating the word mysqladmin or shutdown might help.
MySQL's Viewpoint
Shutdown commands do not exist from command line utilities. Therefore, binary logs will not have shutdowns recorded. You are on the right track when you said;
One possibility I'm thinking is someone (who has SUPER privilege) turned off the binary log then run SHUTDOWN and turned it on again.
Look over the users with that privilege. Run this query:
SELECT user,host FROM mysql.user WHERE shutdown_priv='Y';
You may see something like this:
mysql> select user,host from mysql.user where shutdown_priv='Y';
+-------+--------------+
| user | host |
+-------+--------------+
| root | localhost |
| root | 127.0.0.1 |
| root | ::1 |
| root | 10.240.163.% |
| root | 10.48.19.% |
| user1 | % |
+-------+--------------+
6 rows in set (0.00 sec)
mysql>
You will see all the users that have shutdown privilege. SUPER privilege does not have the shutdown privilege. There is a separate privilege called SHUTDOWN.
You can quickly revoke that privilege. For example, note the user user1@'%'. It has remote shutdown privileges. You can yank it with
REVOKE SHUTDOWN ON *.* FROM user1.'%';
or
UPDATE mysql.user SET shutdown_priv='N'
WHERE user='user1' AND host='%';
FLUSH PRIVILEGES;
That takes care of the remote shutdown privileges.
You should also address securing user connections by removing SHUTDOWN privilege from every user that does not need to have it. Perhaps you can do this:
UPDATE mysql.user SET shutdown_priv='N'
WHERE CONCAT(user,'@',host) NOT IN
('root@localhost','root@127.0.0.1');
FLUSH PRIVILEGES;
That way, only root@localhost and root@127.0.0.1 can do mysqladmin shutdown within the localhost. I would definitely have the root@127.0.0.1 on hand because there are times when mysqld loses connection to the mysql.sock file and prevents root@localhost from connecting to issue a shutdown. With the root@127.0.0.1, you can issue this:
mysqladmin -uroot -p -h127.0.0.1 --protocol=tcp
to issue a mysqladmin shutdown without needed the socket file.
Give it a Try !!!
Best Answer
The point of database-level audits, such as can be done with triggers is to capture "who" made the change... but from the database's perspective, there is only one "who" -- the MySQL user the application uses. (Also, your devops and DBA users, but primarily, it sounds like the application's changes you're interested in.)
I would suggest that the appropriate approach is for the application to log the app-user actions through deliberate insertions into "history" or "event" tables, referencing that Perl
$userid, which presumably would also be the primary key in "user" table in the database... you'd be generating these logs from application code.The role of trigger-based audits, then, is primarily one of sanity-checking the application: the audit shows this user's balance increased... did the application write a matching log entry in the appropriate "history" table? The history table should then have the app-user id, and everything cross-references, if the app is correctly logging its actions.
(My personal preference is for the application to modify the database only through calls to stored procedures... in this way, the application can't fail to write these app-user-level history logs, because the stored procedures encapsulate every DML action into a single atomic database call... but I digress.)
There's a
workhackaround available in MySQL, if you want to try to hybridize these two approaches... even if it's only to satisfy your curiosity and broaden your understanding of the nature of what you're dealing with.User Defined Variables in MySQL are untyped variables, much like Perl scalars: they are whatever you put in them, strings, datetimes, floats, or blobs, and they're scoped to the individual MySQL connection. Their value is independent of database transactions, but never visible from one connection to another.
Until you disconnect from this connection, the variable
@app_user_id(which I just made up, it's just a variable name, but the@designates it as a user defined variable) will contain the value you stored there unless you set it to something else. Because these variables have session scope, they also available to triggers, procedures, and stored functions (but not views or events)... so instead of usingCURRENT_USER()in a trigger, use@app_user_id.This, of course, requires that your application initialize this variable each time it begins running code that could update the database, and you have to hold on to that DBI handle, if you're working in a framework that can swap them out from under you.
So, not a perfect solution, but possibly the closest thing you'll find to accomplish what you want, using the setup you want to try.
http://dev.mysql.com/doc/refman/5.6/en/user-variables.html