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

  • Cisco Router Setup Guide and Tutorial Howto With Commands and Examples
  • Linux Bash Script To List All Connected IPs and their network name
  • Cisco Switches How To Get Of Port Line Status Console Messages
  • Cisco DHCP Snooping Relay Setup Information
  • Cisco Switch Setup Guide Command List
  • Cisco 2960 Switch Reset To Factory Defaults
  • How To Boot Cisco CUCM UCSInstall 8.6, 10, 11 and 12 on KVM/Proxmox
  • VBOX VirtualBox How To Import Raw .img Disk File
  • Windows Server 2012, 2016, 2019 How To Install and Missing Disabled Telnet Client
  • proxmox vm networking breaks when you restart your network on the hostnode
  • Linux ln symlink how to update existing symbolic link
  • Ubuntu 18.04 / Linux Mint 19.1 Cannot Type or Login - solution
  • LUKS Hard Drive Encryption on Linux Mint Ubuntu Debian etc how to mount encrypted hard drive
  • How to use nmap locate other machines/computers/servers on your network using nmap
  • Linux Mint 18.2 Create Config File To Start Application Upon Login
  • Dell Wyse Thin Client BIOS Access Key
  • sudoers file in /etc warning about comments/includes!
  • Centos 7 Reallocate logical volume space to another
  • lvm how to reduce volume size
  • letsencrypt certbot error "Unable to find a virtual host listening on port 80 which is currently needed for Certbot to prove to the CA that you control your domain. Please add a virtual host for port 80."