Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using version control (Git) on a MySQL database

I am a WordPress Designer/Developer, who is getting more and more heavily involved with using version control, notably Git, though I do use SVN for some projects. I am currently using Beanstalk for my remote repo.

Adding all of the WordPress files to my repo is no problem, if I wanted to I know I could .gitignore the wp-config file, but since I'm the only developer, currently, and these projects are closed source, it really makes little sense.

WordPress relies heavily on the database, as any CMS does, to keep textual content, and many settings depending on the specific plugin/theme configuration I'm using. I'm wondering what the best way of using version control on the database would be, if it's even possible. I guess I could do a SQL dump, though my MySQL server is running on Windows (read as: I don't know how to do it), and then add the SQL dump to my repository. But when I push something live, that poses huge security threats.

Is there an accepted practice of doing this?

like image 588
Zach Russell Avatar asked Nov 28 '12 15:11

Zach Russell


People also ask

Can you use Git for databases?

Git has a database "Committed means that the data is safely stored in your local database." "The Git directory is where Git stores the metadata and object database for your project." GitHub also provides access to a git database stored on GitHub via an API, aptly named "Git database".

Can Git be used for version control?

Git is an open source distributed version control system that helps software teams create projects of all sizes with efficiency, speed, and asynchronicity.

Can I use MySQL in GitHub?

GitHub uses MySQL as its main datastore for all things non- git , and its availability is critical to GitHub's operation. The site itself, GitHub's API, authentication and more, all require database access. We run multiple MySQL clusters serving our different services and tasks.


2 Answers

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.

like image 174
Kevin A. Naudé Avatar answered Oct 09 '22 23:10

Kevin A. Naudé


There are not many software available to version control databases like MySQL and MongoDB.

But one is under development and the beta version is about to be launched soon. Check out Klonio - Version Control for databases

like image 27
Kevin Avatar answered Oct 10 '22 00:10

Kevin