Preserve update_time and modify_time in mysqldump – How to

backupinformation-schemaMySQLmysqldump

Does mysqldump preserve the create_time and update_time attributes that are output by show table status from?

If not, is there an option that does this?

From the manual it looks like mysqldump preserves this data if you export to XML.
EDIT: Well the data is included in the export. Whether it's read in at the other end I'm not sure.

Is there a way to do this with a normal .sql dump?

Best Answer

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.

Related Question