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.

Leave a Reply