MySQL 5.1 / phpMyAdmin - logging CREATE/ALTER statements

Question

Is it possible to log CREATE / ALTER statements issued on a MySQL server through phpMyAdmin? I heard that it could be done with a trigger, but I can't seem to find suitable code anywhere. I would like to log these statements to a table, preferably with the timestamp of when they were issued. Can someone provide me with a sample trigger that would enable me to accomplish this?

I would like to log these statements so I can easily synchronize the changes with another MySQL server.

Answer 1

There is a patch for phpMyAdmin which provides configurable logging with only some simple code modifications.

We did this at my work and then i tweaked it further to log into folders by day, log IP addresses and a couple other things and it works great.

Thanks @Unreason for the link, i couldn't recall where i found it.

Answer 2

Here is a script that would do what you want for mysql-proxy (check the link on official docs how to install the proxy).

To actually log the queries you can use something as simple as

function string.starts(String,Start)
    return string.sub(String,1,string.len(Start))==Start
end

function read_query( packet )
    if string.byte(packet) == proxy.COM_QUERY then
        local query = string.lower(string.sub(packet, 2))
        if string.starts(query, "alter") or string.starts(query, "create") then
            -- give your logfile a name, absolute path worked for me
            local log_file = '/var/log/mysql-proxy-ddl.log'
            local fh = io.open(log_file, "a+")
            fh:write( string.format("%s %6d -- %s \n",
                os.date('%Y-%m-%d %H:%M:%S'),
                proxy.connection.server["thread_id"],
                query))
            fh:flush()
        end
    end
end

The script was adopted from here, search for 'simple logging'.

This does not care about results - even if the query returned an error it would be logged (there is 'more customized logging' example, which is a better candidate for production logging).

Also, you might take another approach if it is applicable for you - define different users in your database and give DDL rights only to a certain user, then you could log everything for that user and you don't have to worry about details (for example - proxy recognizes the following server commands, out of which it inspects only Query)

Installing the proxy is straight forward, when you test it you can run it with

mysql-proxy --proxy-lua-script=/path/to/script.lua

It runs on port 4040 by default so test it with

mysql -u user -p -h 127.0.0.1 -P 4040

(make sure you don't bypass the proxy; for example on my distro mysql -u user -p -h localhost -P 4040 completely ignored the port and connected over socket, which left me puzzled for a few minutes)

Answer 3

The answer to your question will fall into one of the listed in MySQL Server logs

If you just want to get the CREATE/ALTER statements, I would go with the general query log. But you will have to parse the file manually. Be aware of the security issues this approach raises.

In your scenario, replication seems to be an overkill.

Triggers are not a valid option since they are only supported at SELECT, UPDATE and INSERT level and not ALTER/CREATE.

Edit 1:

The query log would be the best choice but as you mentioned on busy servers the logs would cause a considerable efficiency penalty. The only additional alternative I know of is MySQL Proxy.