ERRORS REPORTED: mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user ‘root’@’localhost’ for table ‘cond_instances’ when using LOCK TABL

  • Add –skip-add-locks(–skip-lock-tables) to your mysqldump command
  • For automysqlbackup on Debian:
    Edit the automysqlbackup executable.
    Modify the function dbdump.
    Add the –skip-lock-tables.

    dbdump () {
            touch $2
            chmod 600 $2
            if [ $1 = "information_schema" ] ; then
                    NEWOPT="--skip-opt ${OPT}"
            elif [ $1 = "performance_schema" ]; then
                    NEWOPT="--skip-lock-tables --skip-opt ${OPT}"
            else
                    NEWOPT="--opt $OPT"
            fi
    
            echo "${1} - ${NEWOPT}"
    
            if [ -z "${USERNAME}" -o -z "${PASSWORD}" ] ; then
                    mysqldump --defaults-file=/etc/mysql/debian.cnf $NEWOPT $1 > $2
            else
                    mysqldump --user=$USERNAME --password=$PASSWORD --host=$DBHOST $NEWOPT $1 > $2
            fi
            return 0
    }
    
  • This is not a problem of locks, and the offered solutions merely bypass the real problem:
    A 5.5 mysqldump application should not export the performance_schema database in the first place.
    Also, a 5.1 client is unaware of the “futuristic” existence of performance_schema and therefore attempts to dump it. Try and find the 5.5 version, and use it for dumping, without adding the suggested locks, and this should work well.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Scroll to top