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.
Scroll to top