Server Setup / Troubleshooting

Mysqldump hangs website? [Solved]

by , , revisited on

We have by far the largest RPM repository with NGINX module packages and VMODs for Varnish. If you want to install NGINX, Varnish, and lots of useful performance/security software with smooth yum upgrades for production use, this is the repository for you.
Active subscription is required.

MySQLDump utility is quite useful in backing up website data. However, you may find that mysqldump hangs website when exporting large amounts of data.

The issue is caused with locking up tables while exporting. All you need to do is supply command line parameters to your mysqldump call. This will ensure that no locks are enabled while exporting data.

Faster MySQLDump parameters on the command line

mysqldump --max_allowed_packet=1G --single-transaction --quick --lock-tables=false ...

Faster MySQLDump parameters in global MySQL configuration

A better solution would be putting the same configuration straight into the MySQL server configuration. The common location for it is /etc/my.cnf. You will add the configuration values into [mysqldump] section:

max_allowed_packet = 1G

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.