Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
Awesome response and that makes perfect sense. So I suppose if I was really bothered about the update_time and create_time is reload the data, shut down the server, modify the file date times, start the server. I was curious as to whether there was an option to include this in one of the mysqldump comment blocks. It's not worth the effort. –  benbradley Jan 8 '13 at 9:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.