0

We want to keep track of changes being done to MySQL procedures only, actually recently our procedures seem to have overwritten to some previous versions and we were unable to track it that who did this. We were able to track thread-id from mysqlbinlog but were not able to determine whose userid was using that thread-id. Is there any way by which we can do following

• Track user-id from mysqlbinlog thread-id
• Keep track of every create procedure/drop procedure

We are using MySQL 5.7.20 community edition on windows 10 & CentOS7 and we are using row level bin-logging

Any help is greatly appreciated.

  • What about using a source control versioning system like SVN along with your migration scripts? – πάντα ῥεῖ Mar 6 '18 at 17:34
  • Yes we are using similar sort of softwre but that doesn't give us any idea at DB level, we would like to have this details at DB level – Nawaz Sohail Mar 6 '18 at 17:35
0

You can use an audit plugin, such as MariaDB's audit plugin which also works for MySQL, see: https://www.percona.com/blog/2016/02/15/mysql-mariadb-with-mariadb-auditing-plugin/

For MariaDB's plugin, you can configure it to log DCL queries only, which should cover CREATE/DROP PROCEDURE.

| improve this answer | |
  • isn't there any native method available? we are looking for something within MySQL processes – Nawaz Sohail Mar 7 '18 at 13:17
  • I don't think so. As that linked page explains, the MariaDB plugin is using the MySQL audit plugin API, so it's partly native in that sense. If you want something entirely MySQL, then you need a MySQL Enterprise license. You can of course also turn on the general query log (SET GLOBAL general_log = 'ON';), but this logs everything, so isn't a good idea on a production system. – dbdemon Mar 7 '18 at 14:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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