如何减慢 MySQL 转储的速度而不影响服务器上的当前负载？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5666784/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I slow down a MySQL dump as to not affect current load on the server?
提问by z33k3r
While doing a MySQL dump is easy enough, I have a live dedicated MySQL server that I am wanting to setup replication on. To do this, I need dumps of the databases to import to my replication slave.
虽然执行 MySQL 转储很容易，但我有一个实时专用 MySQL 服务器，我想在其上设置复制。为此，我需要转储数据库以导入到我的复制从站。
The issue comes when I do the dumps, MySQL goes full force at it and ties up resources to the sites that connecting to it. I am wondering if there is a way to limit the dump queries to a low priority state to which preference is given to live connections? The idea being that the load from external sites is not affected by the effort of MySQL to do a full dump...
当我进行转储时，问题就出现了，MySQL 全力以赴，并将资源绑定到连接到它的站点。我想知道是否有办法将转储查询限制为优先考虑实时连接的低优先级状态？这个想法是来自外部站点的负载不受 MySQL 进行完整转储的努力的影响......
回答by CA3LE
I have very large databases with tens of thousands of tables some of which have up to 5GB of data in 10's of millions of entries. (I run a popular service)... I've always had headaches when backing up these databases. Using default mysqldump it quickly spirals the server load out of control and locks up everything... affecting my users. Trying to stop the process can lead to crashed tables and lots of downtime during recovery of those tables.
我有一个非常大的数据库，其中包含数万个表，其中一些在数百万个条目中的 10 个中有高达 5GB 的数据。（我运行一个流行的服务）...备份这些数据库时我总是头疼。使用默认的 mysqldump 它会迅速使服务器负载失控并锁定所有内容......影响我的用户。尝试停止该过程可能会导致表崩溃并在恢复这些表期间出现大量停机时间。
I now use...
我现在用...
mysqldump -u USER -p --single-transaction --quick --lock-tables=false DATABASE | gzip > OUTPUT.gz
The mysqldump referenceat dev.mysql.com even says...
该mysqldump的参考在dev.mysql.com甚至说...
To dump large tables, you should combine the --single-transaction option with --quick.
要转储大表，您应该将 --single-transaction 选项与 --quick 结合使用。
Says nothing about that being dependent on the database being InnoDB, mine are myISAM and this worked beautifully for me. Server load was almost completely unaffected and my service ran like a Rolex during the entire process. If you have large databases and backing them up is affecting your end user... this IS the solution. ;)
没有说依赖于数据库是 InnoDB，我的是 myISAM，这对我来说效果很好。服务器负载几乎完全不受影响，我的服务在整个过程中像劳力士一样运行。如果您有大型数据库并且备份它们会影响您的最终用户……这就是解决方案。;)
回答by Drew Clayton
If using InnoDB tables, use the --single-transaction and --quick options for mysqldump
如果使用 InnoDB 表，请为 mysqldump 使用 --single-transaction 和 --quick 选项
回答by Roberto Novakosky
1) first you need to see about your MySQL version. use at least 5.7 so it supports mult thread. Old versions use only 1 thread and is not a good idea at the same time using DB and doing mysqldump if you have large database.
1) 首先你需要了解你的 MySQL 版本。至少使用 5.7，所以它支持多线程。旧版本仅使用 1 个线程，如果您有大型数据库，同时使用 DB 和执行 mysqldump 并不是一个好主意。
2) Prefer to build your backup not in the same DB disc, because performanace of read/write, or maybe you need RAID 10.
2) 最好不要在同一个 DB 盘中建立备份，因为读/写的性能，或者你可能需要 RAID 10。
3) mysqlbackup from MySQL Enterprise is better, but is paid, I dont know if it is an option to you.
3) MySQL Enterprise 的 mysqlbackup 更好，但是是付费的，我不知道您是否可以选择它。
4) Sometimes many tables dont need transaction, so use transaction only on tables you need
4）有时很多表不需要事务，所以只在你需要的表上使用事务
5) Transaction generally is necessary, use InnoDB format to better performanance and not use lock tables.
5）事务一般是必须的，使用InnoDB格式性能更好，不使用锁表。
6) Some cases is better to do one program, so you can create your transaction only to read your tables without lock anyone, and test with some sleeps, and not to freeze your service.
6) 有些情况最好做一个程序，这样你就可以创建你的事务，只读取你的表而不锁定任何人，并测试一些睡眠，而不是冻结你的服务。
回答by khushi muhammad
Use nice and gzip command to execute the command at lowest priority.
使用 nice 和 gzip 命令以最低优先级执行命令。
nice -n 10 ionice -c2 -n 7 mysqldump db-name | gzip > db-name.sql.gz
回答by Darryl at NetHosted
You can prefix the mysqldump command with the following:
您可以使用以下前缀 mysqldump 命令：
ionice -c3 nice -n19 mysqldump ...
Which will run it at low IO and CPU priority so should limit the impact of it.
它将以低 IO 和 CPU 优先级运行它，因此应该限制它的影响。
Note, this will only delay the time between MySQL executing. The scripts themselves will still be as intensive as they were before, just with a longer break between scripts.
请注意，这只会延迟 MySQL 执行之间的时间。脚本本身仍然会像以前一样密集，只是脚本之间的休息时间更长。
