By batfastad


2013-01-07 13:21:18 8 Comments

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?

1 comments

@RolandoMySQLDBA 2013-01-07 17:57:27

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

[[email protected]*** ~]# 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.

@batfastad 2013-01-08 09:17:17

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.

Related Questions

Sponsored Content

1 Answered Questions

[SOLVED] Using mysqldump to initialize slave database

2 Answered Questions

[SOLVED] How to log verbose output from mysqldump?

1 Answered Questions

2 Answered Questions

[SOLVED] mysql - import only schema from full dump?

  • 2019-04-11 21:09:26
  • psad
  • 141 View
  • 0 Score
  • 2 Answer
  • Tags:   mysql mysqldump

1 Answered Questions

[SOLVED] mysqldump takes forever to dump tables from a remote server

1 Answered Questions

[SOLVED] Issue on mysqldump in MySQL5.6

4 Answered Questions

[SOLVED] Exporting/Importing a large MySQL database without mysqldump

Sponsored Content