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.