mysqldump: Got error: 1556: You can’t use locks with log tables

I got the mysql error 1556 when I was backing up all the database from a remote mysql server (Amazon RDS )using a script.

Solution :

You may need to disable “–lock-tables” option on your dump statement ie –lock-tables=0 . What my assumption is when we use “–lock-tables” option, the current table which is being backed up will be operating on “read only” mode to avoid further write operations during the time. The cached querry which containing “write’ operation will be executed later once after the table dump has been done. Pls note that skipping this option is not recommended in production mode.

I made this changes because of since the Amazon RDS does allow only remote access even though I set daily backup and retention period in RDS web interface properly. This custom backup is taken for my surety even though AWS RDS handle it better.

$mysqldump –all-database –lock-tables=0 -uusername -h hostname -p’password’ -B | bzip2 > /backup/db/domain-00-00.2012.bz2

🙂 -njoy