如何减慢MySQL转储的速度，以免影响服务器上的当前负载? [英] How can I slow down a MySQL dump as to not affect current load on the server?

问题描述

虽然进行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.

问题出在我进行转储时，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...

推荐答案

我有非常庞大的数据库，其中包含成千上万个表，其中一些表在多达千万个条目的10个数据中拥有多达5GB的数据. (我运行一项受欢迎的服务)...备份这些数据库时，我总是头疼.使用默认的mysqldump，它会迅速使服务器负载失控，并锁定所有内容，从而影响我的用户.尝试停止该过程可能会导致表崩溃，并在恢复这些表期间造成大量停机.

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.

我现在使用...

mysqldump -u USER -p --single-transaction --quick --lock-tables=false DATABASE | gzip > OUTPUT.gz

开发人员在 mysqldump参考处. mysql.com甚至说...

The mysqldump reference at dev.mysql.com even says...

要转储大表，应结合使用--single-transaction --quick选项.

To dump large tables, you should combine the --single-transaction option with --quick.

没有说依赖数据库为InnoDB，我的是myISAM，这对我来说效果很好.服务器负载几乎完全不受影响，并且我的服务在整个过程中像劳力士一样运行.如果您有大型数据库，并且对其进行备份会影响您的最终用户，这就是解决方案. ;)

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. ;)

这篇关于如何减慢MySQL转储的速度，以免影响服务器上的当前负载?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！