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.

Is it possible to get the last restore date of a MySQL database?

I am going to restore the database from a .sql dump file, but I don't know how to get from the database, the last time when a restore happened.

I tried to query the information_schema.tables table in order to check the creation_time of each table, but they seem to be not updated after the restore.

share|improve this question
add comment

3 Answers

up vote 2 down vote accepted

I've just tested your solution about the "CREATE_TIME" field in information_schema and I think it's the good one, look :

[root@localhost] [MFO] mysql> select * from information_schema.tables where table_name = "myisammfo"\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: MFO
     TABLE_NAME: myisammfo
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Fixed
     TABLE_ROWS: 2
 AVG_ROW_LENGTH: 7
    DATA_LENGTH: 14
MAX_DATA_LENGTH: 1970324836974591
   INDEX_LENGTH: 1024
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2013-03-05 15:15:22
    UPDATE_TIME: 2013-03-05 15:17:58
     CHECK_TIME: 2013-03-05 15:18:28
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.01 sec)

I did a mysqldump of MFO DB:

predator:~# mysqldump -uroot -p --databases MFO >/tmp/MFO.SQL

Then loaded it:

predator:~# mysql -uroot -p </tmp/MFO.SQL

Now the CREATE_TIME is today:

predator:~# mysql -uroot -p
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 86261
Server version: 5.0.51a-24+lenny5 (Debian)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

[root@localhost] [(none)] mysql> select * from information_schema.tables where table_name = "myisammfo"\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: MFO
     TABLE_NAME: myisammfo
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Fixed
     TABLE_ROWS: 2
 AVG_ROW_LENGTH: 7
    DATA_LENGTH: 14
MAX_DATA_LENGTH: 1970324836974591
   INDEX_LENGTH: 1024
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2013-10-02 16:45:56
    UPDATE_TIME: 2013-10-02 16:45:56
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.00 sec)

Best regards.

Max.

share|improve this answer
    
Hi mfouilleul, thank you for your reply! Actually I am using an InnoDB engine and the above mentioned approach did not work. –  user28941 Oct 2 '13 at 15:06
    
Hi, what version of MySQL do you have. I have no difference between MyISAM or InnoDB with this approach. –  mfouilleul Oct 2 '13 at 15:20
    
Hi, I'm using mysql community server 5.5.25. I tried the solution you pasted above, but when I restore the data the create_time does not change. –  user28941 Oct 2 '13 at 15:35
    
Hi mfouilleul, the problem has been solved using the solution you provided here. The create_time is actually changed after the restore, I was checking a table belonging to a different schema. Thank you! –  user28941 Oct 3 '13 at 6:53
add comment

If you are on linux you can also make a ls -l on your datadir to show the creation date of your schemas folder :

[root@db-prod-03 ~]# ls -l /database/data/mysql | grep test
drwxrwx--- 2 mysql mysql       4096 Jul 30 12:59 test

Best Regards.

Max.

share|improve this answer
    
thank you! I'll give it a try. –  user28941 Oct 2 '13 at 15:11
add comment

In your mysqldump, did you put the mysql schema in it?

The mysql schema contains the information_schema datas so when the mysql is restored, the past infos are loaded... understand?

Max.

share|improve this answer
    
Edit : I did three answers because it's three differents approaches. –  mfouilleul Oct 2 '13 at 15:42
    
I used the same syntax you provide in the previous answer (e.g.: mysqldump -uroot -p --databases MFO > /tmp/MFO.SQL), but the date does not change. Anyhow, each time I query the information_schema tables their creation_time change (also without performing a dump). Instead, the creation_time of my own schema does not. –  user28941 Oct 2 '13 at 15:45
    
Also, the .sql produced by my dump does not contain any operation on the information_schema –  user28941 Oct 2 '13 at 15:50
    
information_schema does not contains tables, it contains views. The datas are stored in mysql schema. –  mfouilleul Oct 3 '13 at 8:09
add comment

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.