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.
Best Answer
The database size itself is very big and that seems to be the reason of
lost connection
to mysql server.You can try the below command which will try to take the backup after increasing the
net_write_timeout
andmax_allowed_packet
This command will increase the timeout and max_allowed_packet size. After this, run the below command to take the dump
If you still get error, please provide exact error you are getting in the mysql error logs.