Bug #49633 mysqldump: problems dumping INFORMATION_SCHEMA
Submitted: 11 Dec 2009 21:02 Modified: 11 Dec 2009 21:46
Reporter: Paul DuBois Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: mysqldump Command-line Client Severity:S4 (Feature request)
Version:5.1.43+ OS:Any
Assigned to: CPU Architecture:Any

[11 Dec 2009 21:02] Paul DuBois
This is a followup to Bug#33762, which gives mysqldump the ability to dump INFORMATION_SCHEMA if it named explicitly on the command line. But that fix has some problems.

The test case uses this command:

--exec $MYSQL_DUMP --compact --opt -d information_schema tables

I did my own test and found that "mysqldump --no-data information_schema tables" works on
Mac OS X with the table name in any lettercase, but on Linux, I had to give the table name
as TABLES. (Even though that is normally not true for references to I_S tables on Linux.)

Also, attempting to dump the entire database by not naming a table produces an error:

mysqldump --no-data information_schema
mysqldump: Got error: 1044: Access denied for user 'paul'@'localhost' to database
'information_schema' when using LOCK TABLES

If you look in the general query log, you can see a LOCK TABLES statement that attempts to lock all the I_S tables. If you give the --skip-lock-tables (or --skip-opt) option, the command succeeds, but perhaps mysqldump could be smart enough not to attempt locking the I_S tables?

How to repeat:
See Description.
[11 Dec 2009 21:46] MySQL Verification Team
Thank you for the bug report. Verified as described.
[3 Jun 2014 13:08] Georgi Kodinov
Posted by developer:
http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html says:
mysqldump does not dump the INFORMATION_SCHEMA database by default. To dump INFORMATION_SCHEMA, name it explicitly on the command line and also use the --skip-lock-tables option.
But nevertheless knowing not to use LOCK TABLES on I_S is a good productivity shortcut. 
So moving to a feature request.