How can I slow down a MySQL dump as to not affect current load on the server?

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

Solution 1:

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

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

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

To dump large tables, you should combine the --single-transaction option with --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. ;)

Solution 2:

If using InnoDB tables, use the --single-transaction and --quick options for mysqldump

Solution 3:

Besides the already mentioned solution of using --single-transaction and --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.

mysqldump --single-transaction --quick -u user -p my_db_name > dump_name.sql && gzip dump_name.sql

-> which will also result in 1 file called dump_name.sql.gz