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

  • du - VAS Billing Subscriptions Hack/Scam MLPremiumSub Invascom Astromart Issues Complaint
  • Docker Swarm vs Kubernetes Comparison Guide
  • When is it time to leave your VPS/VDS Cloud Hosting Provider?
  • 2024 Buyer's Guide: How to Choose and Buy the Best VPS/VDS for Your Needs - Tips and Strategies
  • Postfix / sendmail config for DKIM, SPF and DMARC Tutorial Guide E-mail Delivery for Hotmail.com Gmail.com and More HowTo
  • Install Grafana on Linux Debian Ubuntu Tutorial Guide
  • How To Completely Disable ufw in Linux Ubuntu Mint Debian
  • System has not been booted with systemd as init system (PID 1). Can't operate. Failed to talk to init daemon. Ubuntu Debian Linux Solution Cannot reboot
  • Mint Ubuntu Linux Gnome Showing Home Directory on Desktop instead of Desktop Directory
  • vi vim not doing code highlighting E319: Sorry, the command is not available in this version solution
  • Proxmox How To Rename Node Hostname Fix Solution
  • Linux how to get list of all timezones on system Ubuntu
  • Proxmox install issue cannot see the buttons or install wrong / bad resolution cannot see the entire screen problem solution
  • configure.ac:75: error: possibly undefined macro: AC_PROG_LIBTOOL If this token and others are legitimate, please use m4_pattern_allow. See the Autoconf documentation. solution
  • Can't exec "aclocal": No such file or directory at /usr/share/autoconf/Autom4te/FileUtils.pm line 326. autoreconf: failed to run aclocal: No such file or directory solution
  • /bin/sh: autoreconf: command not found solution
  • glib-2.0 required to compile QEMU solution
  • How To Upgrade Debian 8,9,10 to Debian 12 Bookworm
  • Linux dhcp dhclient Mint Redhat Ubuntu Debian How To Use Local Domain DNS Server Instead of ISPs
  • Docker dockerd swarm high CPU usage cause solution