1

Implementing a GIT repository for a project we are including the DB structure by generating a dump on the post-commit hook on deployment.

What I would like to have is a simple versioning system for the file based on the timestamp of the last change to the tables structure.

After finding this post with the suggestion to check for the dates of the the *.frm files in the MySQL data dir I thought the solution would be to implement it based on that last date as part of the generated file. This is:

  1. Find out the latest date-time of the files of the DB (i.e. /var/lib/mysql/databaseX/) via an ls command (of type ls -la *.frm)

  2. compare that value (last changed file) with the one of a certain file (ie /project/dump_2012102620001.sql) where the numbers correspond to the last generated dump.

  3. If files timestamp is after that date generate the mysqldump command, otherwise ignore so the dump does not get generated and committed as a change to GIT

Unfortunately my Linux console/bash concepts are too far from being capable and have not found any similar script to use.

Chris Seymour
  • 83,387
  • 30
  • 160
  • 202
luison
  • 1,850
  • 1
  • 19
  • 33

2 Answers2

3

You can use [[ file1 -ot file2 ]] to test whether file1 is older than file2.

last=$(ls -tr /path/to/db/files/*.frm | tail -n1)
if [[ dump -ot $last ]] ; then
    create_new_dump
fi
choroba
  • 231,213
  • 25
  • 204
  • 289
  • 1
    Code works fine, thanks for the tip but unfortunately due to the nature of GIT file timestamps are not retained and apply to the time the checkout was done (http://stackoverflow.com/questions/1964470/whats-the-equivalent-of-use-commit-times-for-git). This would lead to issues as the dumped file in the repository would not maintain the datetime it was created so the comparison would not be trustable :( – luison Nov 01 '12 at 12:16
1

You can save yourself a lot of grief by just dumping the table structure every time with the appropriate mysqldump command as this is relatively lightweight since it won't include table contents. Strip out the variable timestamp information at the top and compare with the previous file. Store if different.

Jeff Ferland
  • 17,832
  • 7
  • 46
  • 76
  • truly that sounds like a reasonable option. the dump takes a couple of seconds. I'll have to gather how to strip that line or ignore de diff... "Dump completed on 2012-10-31 9:13:56" – luison Oct 31 '12 at 08:18
  • need a bit of bash coding but a good option for this root is using the diff parameter "–ignore-matching-lines" i.e. diff mysqldump2.sql mysqldumped.sql –ignore-matching-lines="Dump completed on" – luison Nov 01 '12 at 12:18