mysqldump: Got error: 1044: Access denied for user ‘root’@’localhost’ to database ‘information_schema’ when using LOCK TABLES

I‘m trying to make backups using the mysqldump command and getting the following error or warning:

ADVERTISEMENTS

mysqldump: Got error: 1044: Access denied for user ‘root’@’localhost’ to database ‘information_schema’ when using LOCK TABLES

How do I fix this problem?

You can pass the –single-transaction option to mysqldump command:
$ mysqldump --single-transaction -u user -p DBNAME > backup.sql
Another option is to grant LOCK TABLES to your user:
$ mysql -u root -p
And type:
mysql> GRANT SELECT,LOCK TABLES ON DBNAME.* TO 'username'@'localhost';

Sample Shell Script

#!/bin/bash
# Purpose: Backup mysql 
# Author: Vivek Gite; under GNU GPL v2.0+ 
NOW=$(date +"%d-%m-%Y")
DEST="/.backup/mysql"
# set mysql login info
MUSER="root"                # Username
MPASS='PASSWORD-HERE'   # Password
MHOST="127.0.0.1"  # Server Name
 
# guess binary names
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"
 
[ ! -d "${DEST}" ] && mkdir -p "${DEST}"
# get all db names
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
do
 FILE=${DEST}/mysql-${db}.${NOW}-$(date +"%T").gz
 # get around error 
 $MYSQLDUMP --single-transaction -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
done
🐧 Get the latest tutorials on SysAdmin, Linux/Unix, Open Source/DevOps topics:
CategoryList of Unix and Linux commands
File Managementcat
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilitiesdig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04

ADVERTISEMENTS
8 comments… add one
  • fox Nov 18, 2010 @ 2:53

    Gracias por la data!

  • webuffalo Feb 9, 2011 @ 9:56

    I added this to automysqlbackup, let’s see tomorrow if it worked
    thanks

  • Alex Oct 25, 2011 @ 13:32

    Niiiceee) thx! It worked.

  • Juarez Nov 22, 2011 @ 19:45

    The first tip worked just fine! Thanks a lot!

  • RudyD Aug 3, 2012 @ 18:19

    Greetings!

    Just getting this on newer Ubuntu and Debian machines.
    The “–single-transaction” switch ruins the next switch which would be the credentials from a custom ‘~my.cf’ file.
    Passing the information_schema into the ignored databases seems making the script skip it. ( Other script of Vivek’s a bit customized further).

    Anyhow I would like to find a way to dump that too if there is any reason and use.

    (The grant of the rights for the root user did not help. In my case root proceeds with the dump.)

    Anyhow I did not investigated further yet.
    — R

  • marco Sep 10, 2013 @ 3:50

    I get this error when the user is root. How is it possible??

  • Anil Kumar Mistery Apr 5, 2014 @ 8:03

    Thanks, I am a great fan of your web site, if I will do anything for you in my life it wd be a great pleaseure for me . I have 8 years of experience of linux admin, I am a Redhat Certified Server Admin,thanks Nixcraft.
    Anil Mistery

  • Rasta Jun 25, 2014 @ 14:15

    Thanks for this solution,
    like a lot of people I find your website workmanlike and comprehensive.
    Good Job !

Leave a Reply

Your email address will not be published.

Use HTML <pre>...</pre>, <code>...</code> and <kbd>...</kbd> for code samples.