Any way to view ALTER TABLE after its been executed? -MySQL

Question

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?

Answer 1

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.

Answer 2

Tools:

• Maatkit.
• Red-Gate's MySQL Schema & Data Compare
• Toad
• SQLYog
• MySQL Diff

Manual:

First check to see if binary logging is enabled:

show variable like 'log_bin';

Then,

mysqlbinlog /var/log/mysql/mysql-bin.000001 | grep 'alter table *tablename*' > test.file

Go to test.file to see the alter statements.