Error 1044 in MySQL: Access denied when using LOCK TABLES

I got an error while using mysqldump

mysqldump: Got error: 1044: Access denied for user x@y to database z when using LOCK TABLES

To solve this problem, either ask you administrator to grant you the lock privileges, or use the following command instead.

mysqldump -u username -p database --single-transaction >dump.sql

This is the help entry for the keyword

--single-transaction
          Creates a consistent snapshot by dumping all tables in a
          single transaction. Works ONLY for tables stored in
          storage engines which support multiversioning (currently
          only InnoDB does); the dump is NOT guaranteed to be
          consistent for other storage engines. While a
          --single-transaction dump is in process, to ensure a
          valid dump file (correct table contents and binary log
          position), no other connection should use the following
          statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
          TRUNCATE TABLE, as consistent snapshot is not isolated
          from them. Option automatically turns off --lock-tables.
This entry was written by Stefano Borini, posted on November 8, 2008 at 3:54 pm, filed under MySQL. Bookmark the permalink. Follow any comments here with the RSS feed for this post. Both comments and trackbacks are currently closed.

12 Comments

  1. Hemant Gandhi
    Posted December 7, 2008 at 3:47 pm | Permalink

    Nice and simple.
    Most of the other posts I found wanted me to give the db user the right to look tables.

  2. Asif
    Posted February 16, 2009 at 5:34 pm | Permalink

    Yes very simple and easy way to get it done.

    its –single-transaction

  3. Ganga
    Posted February 4, 2010 at 5:51 pm | Permalink

    That was simple and working! Thanks

  4. Alex
    Posted May 12, 2010 at 9:40 am | Permalink

    Works perfectly.

    How ?

  5. sageperilous
    Posted June 25, 2010 at 8:27 am | Permalink

    Thanks a lot. It solved my problem

  6. Pritesh
    Posted July 13, 2010 at 4:14 pm | Permalink

    Hi, Stefano,

    you solution is excellant and resolve my issue

    Thanks for such a simple soluction

    -Pritesh

  7. tudor
    Posted September 17, 2010 at 3:29 pm | Permalink

    thank you ! for the science! :)

  8. viper_d2
    Posted October 31, 2010 at 12:45 pm | Permalink

    Does it work for restoring the backup???

  9. Christian bajada
    Posted January 3, 2011 at 12:44 pm | Permalink

    Works fine, thanks.

  10. Brendan Coffey
    Posted February 18, 2011 at 10:12 pm | Permalink

    –single-transaction ! Problem solved! Thank you!

  11. Sanjay M
    Posted June 16, 2011 at 12:10 pm | Permalink

    thanks had got stucked you got me going! :)

  12. Marcelo Mendoza
    Posted September 29, 2011 at 3:31 pm | Permalink

    Excellent!!! It works for me. Thanks Stefano