Search code examples
mysqletlmysql-5.7

Does MYSQLDump create temp tables?

I'm on MySQL 5.7

I have a table that is about 150GB, the storage on the computer is only 200GB. So I wanted to get rid of data older than 9 months on this table.

So my plan was to take a dump of the table with the where clause. Then truncate the table, and reinsert the dump. Does creating a dump with a where clause create a temp table, where I would run out of storage before being able to export all that data?

What I ran into where I tried regular delete statement was table locking and storage filling up quickly from temp table being created to delete. At least I think this is what happened when I tried to just delete

Solution

  • You can make mysqldump run without using any temp space. Use the --opt switch on the command line. At a minimum use the --quick switch.

    You can use a simple WHERE clause and it will still work.

    And be sure to run the command on a machine with enough hard drive space to store the output .sql file.