英文:

Does MYSQLDump create temp tables?

问题

I'm on MySQL 5.7

我使用的是 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.

我有一个大约 150GB 大小的表，但计算机上只有 200GB 的存储空间。因此，我想删除这个表中早于 9 个月的数据。

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?

因此，我的计划是使用带有 WHERE 子句的方式对表进行转储。然后截断表，并重新插入该转储数据。
创建带有 WHERE 子句的转储是否会创建临时表，在导出所有数据之前，存储空间就会用完？

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.

当我尝试使用普通的 DELETE 语句时，遇到的问题是表锁定以及由于创建临时表而导致存储空间迅速填满。至少我认为这是当我尝试仅执行删除操作时发生的情况。

英文:

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

答案1

得分: 1

你可以在命令行中使用 the --opt 开关，使 mysqldump 在不使用任何临时空间的情况下运行。至少要使用 --quick 开关。

你可以使用一个简单的 WHERE 子句，它仍然可以工作。

并确保在有足够硬盘空间存储输出 .sql 文件的计算机上运行该命令。

英文:

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.