In the same way SHOW CREATE TABLE tblname; brings back what was previously executed, is there anyway to view the SQL of an ALTER TABLE query? please help?
+1
A:
If your MySQL instance has logging turned on, you can view the logs. This would be the best option.
If your system has history turned on, you can start a client back up from the same system and try the up arrow. You might be able to see the command there.
If you know the user that ran the command and they happened to run it directly from a command line, the same history trick might work.
TheJacobTaylor
2010-02-24 18:49:38
http://dev.mysql.com/doc/refman/5.0/en/server-logs.html is a link to descriptions of MySQL logging for administration.
TheJacobTaylor
2010-02-24 18:52:00
thankyou for replying, I'm using phpmyadmin but I cant seem to find any history or log option.
Chloe McDermott
2010-02-24 19:05:32
If you need the data, can you ask the administrator of the system to get the query logs for you? Have them find commands from your user, with "alter", on the specific day. Hopefully they can help.
TheJacobTaylor
2010-02-24 23:02:41
+1
A:
One small clarification: show create table does not actually "bring back what was previously executed". It just shows you the DDL that would create the table from scratch. The table may have been created and then altered many times, but show create table reflects the current state of the table.
As for finding any alter table statements that ran on the table recently, the best bet is the binary log.
First check to see if binary logging is enabled:
show variable like 'log_bin';
If it is, find the binary log for the relevant time period, use mysqlbinlog to convert it to SQL, then grep for the relevant table name to find the alter table statement you are looking for.
Ike Walker
2010-02-24 19:16:31