如何减慢 MySQL 转储速度而不影响服务器上的当前负载？
虽然进行 MySQL 转储非常简单，但我有一个实时专用 MySQL 服务器，我想在其上设置复制。为此，我需要将数据库转储导入到我的复制从站中。
当我进行转储时，问题就出现了，MySQL 全力以赴，并将资源绑定到连接到它的站点。我想知道是否有一种方法可以将转储查询限制为低优先级状态，并优先考虑实时连接？这个想法是，来自外部站点的负载不会受到 MySQL 执行完整转储的影响......
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.
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...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
我有非常大的数据库，其中有数以万计的表，其中一些表的数据高达 5GB，包含数十百万个条目。 （我运行一项流行的服务）...备份这些数据库时我总是很头疼。使用默认的 mysqldump 它会迅速使服务器负载失控并锁定所有内容......影响我的用户。尝试停止该进程可能会导致表崩溃以及恢复这些表期间的大量停机时间。
我现在使用...
mysqldump 参考在 dev.mysql.com 甚至说...
没有提到依赖于 InnoDB 数据库，我的是 myISAM，这对我来说效果很好。服务器负载几乎完全不受影响，我的服务在整个过程中像劳力士一样运行。如果您有大型数据库并且备份它们会影响您的最终用户......这就是解决方案。 ;)
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.
I now use...
The mysqldump reference at dev.mysql.com even says...
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 表，请使用 mysqldump 的 --single-transaction 和 --quick 选项
If using InnoDB tables, use the --single-transaction and --quick options for mysqldump
除了已经提到的使用
--single-transaction和--quick的解决方案之外，我不会直接将结果通过管道传输到 gzip 中，而是首先将其转储为 .sql 文件，然后对其进行 gzip 压缩。 （使用 && 而不是 | ）转储本身会更快，因此停机时间会更少。
（对于我的测试，它的速度双倍快）
所以我会选择“&& gzip”而不是“| gzip”
重要：首先使用df -h! 因为您将需要更多的管道 | gzip。
->这也会产生 1 个名为 dump_name.sql.gz 的文件
Besides the already mentioned solution of using
--single-transactionand--quick, I would not directly pipe the result in gzip but first dump it as a .sql file, and then gzip it. (Use && instead of | )The dump itself will be faster so lower downtime.
(for what I tested it was double as fast)
So I would go for "&& gzip" instead of "| gzip"
Important: check for free disk space first with
df -h! since you will need more then piping | gzip.-> which will also result in 1 file called dump_name.sql.gz
1）首先你需要查看你的MySQL版本。至少使用5.7，这样它才支持多线程。旧版本仅使用 1 个线程，如果您有大型数据库，那么同时使用 DB 和执行 mysqldump 并不是一个好主意。
2) 最好不要在同一个数据库磁盘上构建备份，因为读/写性能，或者可能您需要 RAID 10。
3) MySQL Enterprise 的 mysqlbackup 更好，但是是付费的，我不知道它是否是一个选项你。
4) 有时很多表不需要事务，所以只在需要的表上使用事务
5) 事务一般是必要的，使用InnoDB格式以获得更好的性能并且不要使用锁表。
6）某些情况下最好执行一个程序，因此您可以创建事务仅用于读取表而不锁定任何人，并进行一些睡眠测试，而不是冻结您的服务。
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.
2) Prefer to build your backup not in the same DB disc, because performanace of read/write, or maybe you need RAID 10.
3) mysqlbackup from MySQL Enterprise is better, but is paid, I dont know if it is an option to you.
4) Sometimes many tables dont need transaction, so use transaction only on tables you need
5) Transaction generally is necessary, use InnoDB format to better performanance and not use lock tables.
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.
使用nice和gzip命令以最低优先级执行命令。
Use nice and gzip command to execute the command at lowest priority.
您可以在 mysqldump 命令前面加上以下前缀：
这将以低 IO 和 CPU 优先级运行它，因此应该限制它的影响。
注意，这只会延迟 MySQL 执行之间的时间。剧本本身仍将像以前一样密集，只是剧本之间的休息时间更长。
You can prefix the mysqldump command with the following:
Which will run it at low IO and CPU priority so should limit the impact of it.
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.