Bug #60495 mysqldump is locking myisam tables

• Submitted: 16 Mar 2011 17:41
• Modified: 18 Mar 2011 15:48
• Reporter: Jamie Koc
• Status: Not a Bug
• Category: MySQL Server: mysqldump Command-line Client
• Severity: S2 (Serious)
• Version: 5.1.44
• OS: Linux
• CPU Architecture: Any
• Tags: lock tables, mysqldump

[16 Mar 2011 17:41] Jamie Koc

Description:
I am performing a simple mysqldump of a myisam database and I am noticing that tables are being blocked by the backup process. Note: mysqldump is not dumping the table that is being blocked.

Command:
/usr/bin/mysqldump -uusername -ppw --opt dbname > file.sql

As I understand it, I thought you either had to pass in --lock-all-tables or perform a flush tables with read_lock to see this sort of behavior.

Thanks,
Jamie

How to repeat:
Perform a mysqldump and update a myisam table that is not being backed up.

[16 Mar 2011 19:16] Valeriy Kravchuk

I think our manual, http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html, clearly explains the situation you see:

"Use of --opt is the same as specifying --add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, and --set-charset. All of the options that --opt stands for also are on by default because --opt is on by default."

Then check http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_lock-tables ...

[18 Mar 2011 15:28] Jamie Koc

Sorry I copied the wrong backup script that was executed.
The --opt was not passed in.

>mysqldump -u root -p dbname | gzip -c > YYYYMMDD-backup.sql.gz

This was the backup that was run command that was run and tables were locking.

[18 Mar 2011 15:48] Valeriy Kravchuk

Still my quote from the manual applies:

"All of the options that --opt stands for also are on by default because --opt is on by default."

This is intended and documented behavior.