mysqldump: Couldn't execute 'show create table `general_log`': SHOW command denied to user 'user'@'localhost' for table 'general_log' (1142) - Solution

mysqldump: Couldn't execute 'show create table `general_log`': SHOW command denied to user 'user'@'localhost' for table 'general_log' (1142)

One of my clients almost found out the hard way, here is an unlikely situation that happened.

1.) Years ago the client had another VPS to which they backed up a BLOG nightly to an .sql file, what they forgot is that the file also contained all databases (they used the --all-databases option but forgot).  So imagine the surprise when they ran this .sql file on a production server, thinking they're just importing their old BLOG.  In fact the "mysql" table was part of that backup since it was --all-databases, and it flushed the mysql table with entries from their old VPS.  As you can imagine this would effect every database driven application including websites and even email etc...

2.) They were lucky because I was able to see what had happened and they haven't run "flush privileges" so the original mysql database was in memory.  I thought I would just restore backups they had taken so no problem right ?  Their dumps weren't working right but fortunately I was able to take a raw /var/lib/mysql backup from their filesystem and get up on another VPS and dump the original mysql db from there and restore it. 

3.) No, every database except "mysql' was backed up successfully, the user they had to dump all the databases needed one specific privilege to be able to dump mysql. 

The solution for the above error:

ADD the privileges "Show_db" to the user and then it will work.
 


Tags:

mysqldump, couldn, execute, general_log, user, localhost, solutionmysqldump, unlikely, vps, blog, nightly, sql, contained, databases, server, importing, quot, mysql, flushed, entries, database, websites, email, etc, haven, flush, privileges, restore, backups, dumps, weren, fortunately, var, lib, filesystem, db, successfully, show_db,

Latest Articles

  • ImageMagick Convert PDF Not Authorized
  • ImageMagick Converted PDF to JPEG some files have a black background solution
  • Linux Mint Mate Customize the Lock screen messages and hide username and real name
  • Ubuntu/Gnome/Mint/Centos How To Take a partial screenshot
  • ssh how to verify your host key / avoid MIM attacks
  • Cisco IP Phone CP-8845 8800/8900 Series How To Reset To Factory Settings Instructions
  • ls how to list ONLY directories
  • How to encrypt your SSH private key file id_rsa
  • Linux Mint 18 Disable User Name List from showing on Login Screen
  • Firefox Cannot Hit Enter Key In Address Bar and Location History Not Working
  • Cisco Unified Communications Manager / CUCM IP 8.6,10,12 Install Error Solution
  • Ubuntu Debian Mint Linux SSHD OpenSSH Server Not Starting After Reboot Solution
  • nmap how to scan for all ports and not just the 1000 most common ports
  • Windows 7,8,10 and Server 2008, 2012, 2016, 2019 Read Only Attribute Won't Go Away
  • bind / named how to make a wildcard record and retain defined A records
  • Cisco Unified Communications Manager 12 Install Errors on Proxmox/KVM
  • Local Vs Universally Administered MAC Address NIC Refuses to come up
  • Cisco Unified Communications Manager 12 CUCM 12 - How To Enable Video Calling
  • Windows 7, 8, 10, Windows Server 2008, 2012, 2016, 2019 How To AC97 Audio Drivers and Other Unsigned Drivers
  • Cisco Unified Communications Manager / CUCM IP Telephony Definitions