FIX: "Access denied...when using LOCK TABLES"

Help
boodle
2006-11-03
2017-01-20
  • boodle

    boodle - 2006-11-03

    Error:
    "mysqldump: Got error: 1044: Access denied for user 'XXX'@'localhost' to database 'XXX' when using LOCK TABLES"

    find line in script:

    # Database dump function
    dbdump () {
    mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST $OPT $1 > $2
    return 0
    }

    change to:

    # Database dump function
    dbdump () {
    mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST $OPT --skip-opt $1 > $2
    return 0
    }

    Here is the explaination in brief from (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html) (worth the read).
    p.s. you can selectively turn options on or off.

    To get a list of the options your version of mysqldump supports, execute mysqldump --help.

    Some mysqldump options are shorthand for groups of other options. --opt and --compact fall into this category. For example, use of --opt is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. Note that all of the options that --opt stands for also are on by default because --opt is on by default.

    To reverse the effect of a group option, uses its --skip-xxx form (--skip-opt or --skip-compact). It is also possible to select only part of the effect of a group option by following it with options that enable or disable specific features. Here are some examples:

        *

          To select the effect of --opt except for some features, use the --skip option for each feature. For example, to disable extended inserts and memory buffering, use --opt --skip-extended-insert --skip-quick. (As of MySQL 5.0, --skip-extended-insert --skip-quick is sufficient because --opt is on by default.)
        *

          To reverse --opt for all features except index disabling and table locking, use --skip-opt --disable-keys --lock-tables.

    When you selectively enable or disable the effect of a group option, order is important because options are processed first to last. For example, --disable-keys --lock-tables --skip-opt would not have the intended effect; it is the same as --skip-opt by itself.

    mysqldump can retrieve and dump table contents row by row, or it can retrieve the entire content from a table and buffer it in memory before dumping it. Buffering in memory can be a problem if you are dumping large tables. To dump tables row by row, use the --quick option (or --opt, which enables --quick). --opt (and hence --quick) is enabled by default as of MySQL 5.0 to enable memory buffering, use --skip-quick.

    If you are using a recent version of mysqldump to generate a dump to be reloaded into a very old MySQL server, you should not use the --opt or --extended-insert option. Use --skip-opt instead.

    boodle

     
    • boodle

      boodle - 2006-11-03

      Can also do it like this: (using --skip-lock-tables)

      find line in script: 

      # Database dump function 
      dbdump () { 
      mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST $OPT $1 > $2 
      return 0 

      change to: 

      # Database dump function 
      dbdump () { 
      mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST $OPT --skip-lock-tables $1 > $2

      return 0 

       
  • solaceten

    solaceten - 2012-05-25

    Hi

    I also get the lock tables error

    I tried both fixes above and then I get this error

    mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ /*!50084 SQL_NO_FCACHE */ * FROM `CLIENT_STATISTICS`': Access denied; you need the PROCESS,SUPER privilege for this operation (1227)

    Any advice?

    Thanks

     
  • fullmoonguru

    fullmoonguru - 2013-01-10

    Sorry if this seems like a dumb question but which script is this in?  I assume it's mysqldump but I can't find the location in Ubuntu 12.04.

     
  • avibodha

    avibodha - 2013-03-29

    or you can do it this way:

    (around line .438)
    parse_configuration () {
        # OPT string for use with mysqldump ( see man mysqldump )
        opt=( '-quote-names'  '-opt'  '-skip-lock-tables' )

        # OPT string for use with mysql (see man mysql )
        mysql_opt=(  )

        # OPT string for use with mysqldump fullschema
        opt_fullschema=( '-all-databases'   '-routines'   '-no-data'   '-skip-lock-tables'  )

     
  • ramonchis

    ramonchis - 2017-01-20

    I found the same issue and solved it by adding the following privileges to the user

    GRANT SELECT ON . TO 'dbbackup'@'localhost';
    GRANT FILE ON . TO 'dbbackup'@'localhost';
    GRANT SHOW VIEW ON . TO 'dbbackup'@'localhost';
    GRANT LOCK TABLES ON . TO 'dbbackup'@'localhost';

    And then

    FLUSH PRIVILEGES

     

Log in to post a comment.