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

  • Grandstream Phone Vulnerability Security Issue Remote Backdoor Connection to 207.246.119.209:3478
  • Linux How to Check Which NIC is Onboard eth0 or eth1 Ubuntu Centos Debian Mint
  • VboxManage VirtualBox NAT Network Issues Managment Troubleshooting
  • Dell PowerEdge Server iDRAC Remote KVM/IP Default Username, Password Reset and Login Information Solution
  • Nvidia Tesla GPUs K40/K80/M40/P40/P100/V100 at home/desktop hacking, cooling, powering, cable solutions Tutorial AIO Solutions
  • Stop ls in Linux Debian Mint CentOS Ubuntu from applying quotes around filenames and directory names
  • Thunderbird Attachment Download Error Corrupt Wrong filesize of 29 or 27 bytes Solution
  • Generic IP Camera LAN Default IP Settings DVR
  • Ubuntu Debian Mint Linux How To Update Initramfs Manually update-initramfs
  • Enable Turbo Mode for CPU Ubuntu Linux Mint Debian Redhat
  • docker / kubernetes breaks Proxmox QEMU KVM Bridge VMs
  • How To Change Storage Location in Docker.io
  • RTL8812BU and RTL8822BU Linux Driver Ubuntu Setup Archer T3U Plus
  • Kazam video blank/high size and not working when recording solution
  • Cisco UC CME How To Enable Licensed Features
  • from pip._internal.cli.main import main File "/usr/local/lib/python3.5/dist-packages/pip/_internal/cli/main.py", line 60 sys.stderr.write(f"ERROR: {exc}") from pip._internal.cli.main import main File "/usr/local/lib/python3.5/dist-packag
  • ModuleNotFoundError: No module named 'pip._internal' solution python
  • grub blank screen how to manually boot kernel and initrd Linux Ubuntu Debian Centos won't boot solution
  • Cisco Switch / Router How To Restore Factory Default Settings
  • Cisco 2900 3900 Router Password Reset How To Reset Enable Password