Geek Answers Handbook

View MySQL temporary table - not in session

Currently, I have a script running and did not think it would take a long time to start, the script modifies the temporary table.

I know that temporary tables exist only for the current session, but is there any data that they store outside the session?

The reason is because I want to know how long my script will work, if I could see the temporary data, then I could find out.

+7
source share
3 answers

There is no easy way to do this, I'm afraid.

Temporary tables will be stored in your specified mysqld temporary directory (usually / tmp), and you will see a set of tables like this:

-rw-rw---- 1 mysql mysql 8724 Nov 29 18:09 #sqldba_5fa70c_12f1.frm -rw-rw---- 1 mysql mysql 188408 Nov 29 18:09 #sqldba_5fa70c_12f1.MYD -rw-rw---- 1 mysql mysql 1024 Nov 29 18:09 #sqldba_5fa70c_12f1.MYI

This is a normal set of MyISAM tables that define (in the order above) structure, data, and index.

This is horrible hacks, but I suspect that you can copy these tables to tell your test schema, start a repair on the table, and then view its contents.

If you can measure the process by the size of the temp table, then this may be an easier way to analyze what is happening.

+8
source

Impossible, you can (however)
log milestone (message,% completion, numerical strings processed)
to a temporary file and use tail -f log_file to monitor

+1
source

In MySQL 5.7, a new INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO has been added: http://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-temp-table-info.html .

Otherwise, Percona XtraDB has this feature for a while: http://venublog.com/2010/02/03/show-temporary-tables/

0
source

More articles:


All Articles