Any way to view the ALTER TABLE after its execution? -MySQL

Question

In the same way, SHOW CREATE TABLE tblname; returns previously executed, is there anyway viewing SQL query ALTER TABLE? Please, help?

Answer 1

One small clarification: show create table does not actually return what was previously executed. It just shows you the DDL, which will create the table from scratch. Perhaps the table was created and then modified many times, but show create table reflects the current state of the table.

As for finding any alter table statements that have been running in the table recently, the best option is a binary log.

First check if binary logging is enabled:

 show variable like 'log_bin'; 

If so, find the binary log for the appropriate time period, use mysqlbinlog to convert it to SQL, then grep for the corresponding table name to find the alter table statement you are looking for.

Answer 2

If your MySQL instance is enabled, you can view the logs. That would be the best option.

If your system is turned on, you can start the client from one system and try the up arrow. You could see the team there.

If you know the user who ran this command, and they ran it directly from the command line, the same story trick may work.

Answer 3

Tools:

• Maatkit.
• Red-Gate MySQL and Data Comparison
• toad
• SQLyog
• Mysql diff

Manual mode:

First check 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 view alter instructions.