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

by

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

More Related Contents:

  1. Mysql replication with UPDATE JOIN on an ignored table
  2. Restoring MySQL database from physical files
  3. Can I restore a single table from a full mysql mysqldump file?
  4. How can I backup a Docker-container with its data-volumes?
  5. How to take backup of a single table in a MySQL database?
  6. Scaling solutions for MySQL (Replication, Clustering)
  7. Export MySQL database using PHP [closed]
  8. Dump a mysql database to a plaintext (CSV) backup from the command line
  9. INSERT … ON DUPLICATE KEY UPDATE with WHERE?
  10. How can “set timestamp” be a slow query?
  11. What is the best way to do incremental backups in MySQL?
  12. How to import a SQL Server .bak file into MySQL?
  13. Visual Studio Code backup mechanics
  14. How can I schedule a daily backup with SQL Server Express? [duplicate]
  15. Speeding up mysql dumps and imports [closed]
  16. mysqli or die, does it have to die?
  17. MySQL – Rows to Columns
  18. ROW_NUMBER() in MySQL
  19. How can I temporarily disable a foreign key constraint in MySQL?
  20. PHP/MySQL insert row then get ‘id’
  21. Show values from a MySQL database table inside a HTML table on a webpage [duplicate]
  22. Can we connect remote MySQL database in Android using JDBC? [closed]
  23. JDBC MySql connection pooling practices to avoid exhausted connection pool
  24. MYSQL Select MAX Date inside a join statement
  25. TRIGGERs that cause INSERTs to fail? Possible?
  26. Mysql query to dynamically convert rows to columns on the basis of two columns
  27. When to use MyISAM and InnoDB? [duplicate]
  28. MySQL my.ini location
  29. connecting to a docker-compose mysql container denies access but docker running same image does not
  30. #1214 – The used table type doesn’t support FULLTEXT indexes

Leave a Comment