There is no way to reload the INFORMATION_SCHEMA manually. All the tables are dynamica entities. For instance, look at INFORMATION_SCHEMA.TABLES
:
mysql> show create table INFORMATION_SCHEMA.TABLES\G
*************************** 1. row ***************************
Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
`TABLE_CATALOG` varchar(512) DEFAULT NULL,
`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
`TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
`TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
`ENGINE` varchar(64) DEFAULT NULL,
`VERSION` bigint(21) unsigned DEFAULT NULL,
`ROW_FORMAT` varchar(10) DEFAULT NULL,
`TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
`AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
`DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
`MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
`INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
`DATA_FREE` bigint(21) unsigned DEFAULT NULL,
`AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
`CREATE_TIME` datetime DEFAULT NULL,
`UPDATE_TIME` datetime DEFAULT NULL,
`CHECK_TIME` datetime DEFAULT NULL,
`TABLE_COLLATION` varchar(32) DEFAULT NULL,
`CHECKSUM` bigint(21) unsigned DEFAULT NULL,
`CREATE_OPTIONS` varchar(255) DEFAULT NULL,
`TABLE_COMMENT` varchar(80) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql>
It is a temporary table. If you restart mysql, how does it get populated? Evidently, it is popoulated during mysql startup. What would be included? Retrieve OS metadata of the table.
Let's look at mysql.user from the OS
[root@*** ~]# ls -l /var/lib/mysql/mysql/user.*
-rw-rw---- 1 mysql mysql 10466 Jun 5 2012 /var/lib/mysql/mysql/user.frm
-rw-rw---- 1 mysql mysql 2144 Jul 15 23:29 /var/lib/mysql/mysql/user.MYD
-rw-rw---- 1 mysql mysql 2048 Dec 10 18:25 /var/lib/mysql/mysql/user.MYI
Let's look at mysql.user from the mysql client:
mysql> select * from information_schema.tables
-> where table_schema='mysql' and table_name='user'\G
*************************** 1. row ***************************
TABLE_CATALOG: NULL
TABLE_SCHEMA: mysql
TABLE_NAME: user
TABLE_TYPE: BASE TABLE
ENGINE: MyISAM
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 31
AVG_ROW_LENGTH: 69
DATA_LENGTH: 2144
MAX_DATA_LENGTH: 281474976710655
INDEX_LENGTH: 2048
DATA_FREE: 0
AUTO_INCREMENT: NULL
CREATE_TIME: 2012-06-05 23:45:23
UPDATE_TIME: 2012-07-15 23:29:05
CHECK_TIME: NULL
TABLE_COLLATION: utf8_bin
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT: Users and global privileges
1 row in set (0.00 sec)
mysql>
Please notice the following
- The Creation Time
- file datetime for
.frm
is Jun 5 2012
CREATE_TIME: 2012-06-05 23:45:23
- The Update Time
- file datetime for
.MYD
is Jul 15 23:29
UPDATE_TIME: 2012-07-15 23:29:05
The OS metadata for mysql.user
is loaded into INFORMATION_SCHEMA.TABLES
on mysql startup and is updated through INSERTs, UPDATEs and DELETEs.
SUMMARY
While you can mysqldump INFORMATION_SCHEMA.TABLES
, it is impossible to manually reload it. Theoretically, you could shutdown mysql, tweek the OS clock, tweek every file, then startup mysql. It's not worth the effort.
You apparently have a missing drupal_install_test
in your database schema. As the name says, it's a test table that is created at installation time to test the performance of the database and of the database user you provided for your site. At the end of the installation process, it gets dropped from the database.
The problem here is that the table has been dropped but mysql still thinks it exists. The reason why that table has remained in information_schema.tables
should definitely be investigated and may reflect some issues in the database or in your server's configuration.
That said, if drupal_install_test
is the only corrupted table in your database, you can try and drop it and proceed with your backup. I recommend that you make a full backup of the mysql data directory (in a typical linux installation /var/lib/mysql
) and issue a DROP TABLE drupal_install_test
command. Mysql should be able to restore the integrity of the schema tables and you should be able to proceed with your backup.
Best Answer
There is a bug report on this: mysqldump: Couldn't execute 'show table status': SELECT command denied to user. Surprisingly, it's not a bug at all. here is why:
customer_cohort_paid might
be a view. Whatever user created the view is not listed anymore inmysql.user
. What this does is make such a view incapable of being dumped.You could do one of two(2) things
SUGGESTION #1
Force the dump with
-f
If you can force the dump to make views appear in the dump file, you may want to edit it (See my old post Modify DEFINER on Many Views or this other one How do I change the DEFINER of a VIEW in Mysql?)
WARNING: If any one extended INSERT command has a syntax problem or improperly escaped or encapsulated data, the hundreds of rows in that one INSERT may not be loaded.
SUGGESTION #2
Find out the original user who made the view, and recreate that user
Run this query :
select * from mysql.tables_priv;
This will show all table-level and column-level grants. Look at the first three columns and figure out what GRANT command to make that user again.