Precautions for Exporting Large Tables Through mysqldump

If the –q or --quick parameter is added when you use mysqldump to export data, the results of SELECT statements are not buffered in memory but directly exported. If this parameter is disabled, the results of SELECT statements are buffered in memory and then sent to the client.

• If you use mysqldump to back up only a small amount of data which can be stored in the idle memory buffer, disabling -q increases the export speed.
• Buffering a large amount of data may consume a large amount of memory, causing a memory swapping. If you use mysqldump to back up a large amount of data which cannot be stored in the memory buffer, enable -q. If -q is not enabled, a large amount of memory will be consumed and may even cause the database to break down due to out of memory.

Therefore, you are advised to enable the -q parameter when using mysqldump to back up data.

Example command:

mysqldump -uroot -p -P3306 -h192.168.0.199 --set-gtid-purged=OFF --single-transaction --flush-logs -q test t1>t1.sql