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

In the same way SHOW CREATE TABLE tblname; brings back what was previously executed, is there anyway to view the SQL of a开发者_如何学Pythonn ALTER TABLE query? Please help?

---

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.

---

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.

---

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.