Using version control (Git) on a MySQL database

by

You can backup your database within a git repository. Of course, if you place the data into git in a binary form, you will lose all of git’s ability to efficiently store the data using diffs (changes). So the number one best practice is this: store the data in a text serialised format.

mysqldump is a suitable program to help you do this. It isn’t perfect though. If anything disturbs the serialisation order of items (eg. as a result of creating new tables, etc.) then artificial breaks will enter into the diff. That will decrease the efficiency of storage. You could write a custom serialiser to serialise changes only — but then you are doing the hard work that git is already good at. Just use the sql dump.

That being said, what you are wanting to do isn’t what devs normally mean when they talk about putting the database in git. For instance, if you read the link posted by @eggyal (link to codinghorror) you will see that what is actually placed in git are the scripts needed to generate the initial database. There may be additional scripts, like those to populate the database data with a clean state, or to populate it with testing data. All such sql scripts are text files, and pretty much the same format as the sql dump you would get from mysqldump. So there’s no reason you can’t do it that way with your day-to-day data as well.

More Related Contents:

  1. Put MySQL database under version control?
  2. How do I force "git pull" to overwrite local files?
  3. How can I see the changes in a Git commit?
  4. How do you set a default value for a MySQL Datetime column?
  5. Compare two MySQL databases [closed]
  6. MySQL – Trigger for updating same table after insert
  7. Updates were rejected because the remote contains work that you do not have locally
  8. Storing image in database directly or as base64 data?
  9. Implementation of Levenshtein distance for mysql/fuzzy search?
  10. Is MySQL breaking the standard by allowing selecting columns that are not part of the group by clause?
  11. unknown option `allow-unrelated-histories’
  12. MySQL – Operand should contain 1 column(s)
  13. Restore table structure from frm and ibd files
  14. Mysql: Setup the format of DATETIME to ‘DD-MM-YYYY HH:MM:SS’ when creating a table
  15. mysql pivot/crosstab query
  16. UNION query with codeigniter’s active record pattern
  17. When to use MongoDB or other document oriented database systems? [closed]
  18. MySQL grant all privileges to database except one table
  19. mysql stored procedure that calls itself recursively
  20. MySql views performance [closed]
  21. When to use NULL in MySQL tables
  22. How to get number of rows affected, while executing MySQL query from bash?
  23. What is the difference between tinyint, smallint, mediumint, bigint and int in MySQL?
  24. Detecting locked tables (locked by LOCK TABLE)
  25. MySQL Error: Incorrect usage of UPDATE and LIMIT
  26. Mysql insert into 2 tables
  27. How do you stop MySQL on a Mac OS install?
  28. Is there a way to compare two diffs or patches?
  29. Update multiple rows with multiple ‘where’ clauses for each individual row
  30. WHERE datetime older than some time (eg. 15 minutes)

Leave a Comment