Welcome to Server Circle. It's a friendly site and all levels of experience are welcome. Be aware that we use cookies for your login.
Server Circle - Ask questions about Servers and get answers from experts.
Beta (0.54 sec)
MYSQL dump stops database ?

I think when you run MYSQL dump it locks write access to the tables on the database briefly while it dumps the data as backup. Is there any way to speed it up and measure the time the database is unavailable for ?
Asked by:
JimmyC
945 points
 Report Abuse
 Share Page - Category: Linux Servers - Tags: MYSQL dump stops database ?
 Enter your response
Please use Pastie.org to paste lengthy code or to fix formatting issues with code
  • Responses in reverse (3)

This is correct. There is a --debug option which may well give you the information you seek; however, in some distributions the debug option is disabled. You can check this by running

$ mysqldump --help |grep debug

If it's disabled in your distribution, you will have to download and build from source.

By default, mysqldump uses the --lock-tables setting, in which each table is locked only whilst it is being dumped. Alternatively one can use the --lock-all-tables, which gives the same behaviour as the mysqlhotcopy utility: all tables are locked from the outset. If using this option, then of course the duration of the lock can be given by prefixing the command with 'time'.

Response by:
CPKS
225 points
If you're using mysqldump for backup purposes and you're finding that the table locking is causing problems then you need to find another method altogether.

Take a look at Percona XtraBackup, this is a free product that can do live non-blocking backups, allow you to do point-in-time recovery etc. All in all a much better solution than using mysqldump.

Alternatively use a file system which supports snapshots and backup a snapshot of your DB file system.

Response by:
lgretton
215 points
As mysqldump does it thing it will lock each table in turn "read only" and then unlock it.

One way to find out how long it takes to run is to use "time mysqldump ..." make sure you use the password on the command line otherwise the time to enter it will be taken into account.

There is the --quick/-q flag for mysqldump, I believe it will depend on the size of the tables if you'll see performance increase or not.

If you need to do a mysqldump without lock affects, you would really need to set replication up and mysqldump from the slave server, this would then queue inserts/updates on the master to be applied when the lock is removed.

Response by:
_SteveWilson ...
4290 points


  • Related Questions
About Us : Contact Us : Etiquette : Terms : CDN Failover : ShorterURL : CDN Fallback : © 2013 Server Circle