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

  • Huion and Wacom Tablets How To Install in Linux Mint / Ubuntu and make the stylus work properly
  • ffmpeg how to cut certain parts of video out
  • ffmpeg how to concat and join two video clips
  • mencoder instead of ffmpeg to join or concatenate video files with different audio streams
  • Linux How To Stop Missing Drive from Halting Boot Process in fstab
  • How To Replace Audio Track of Video using ffmpeg
  • qemu-img convert formats vdi vmdk raw qcow2
  • Linux and Windows Dual Boot Crazy Time Issues
  • dynagen / dynamips 100% high CPU usage solution - how to set the idlepc value
  • How To Setup a Cisco CME (Cisco Manager Express) Virtual Router under Linux using dynamips and dynagen
  • Linux Mint Ubuntu Debian CentOS Dual Boot Install Issues
  • Linux Mint Ubuntu Debian Centos RHEL no sound solution
  • Linux Mint/Debian/Ubuntu/Centos Installer black grub screen and blank screen after trying to boot installer or main OS
  • Linux Mint Dual Boot Install Avoid Wiping our your Main C: drive /dev/sda MBR and EFI
  • QEMU-KVM soundhw deprecated how to enable sound in QEMU 4.x series
  • Virtualbox Error Cannot register the hard disk because a hard disk with UUID already exists solution
  • kernel: [549267.368859] mate-terminal[7871]: segfault at 2000000101 ip 00007f5d0a9548f0 sp 00007fff7012c610 error 4 in libgobject-2.0.so.0.4800.2[7f5d0a920000+52000]
  • apcupsd how to setup and monitor APC UPS units
  • How To Password Reset, Recover, Bypass, Remove and Unlock on Windows 10,8,7,Vista,XP,NT,2000,2003,2008,2012,2016,2019 Administrative Login Programs
  • Nvidia Ubuntu Linux Screentearing Video with solution driver