version control of database

Question

Hello,

I trying to set up version control (with git/github) of my q2a-site in a usable way. What I want to be able to do, is to version control all php/css and "some" of the database.

The reason for wanting a version control on the database is that I want to persist all the settings (site name, URL of logo, categories, page settings, etc, etc, etc).

I do not want to version control all the questions and answers and users though. And in fact, I want to be able to have different versions of the users and questions (e.g. for dev and production) but use the same settings in both versions.

Is there a best practise for this? What I would like to do, is to have all configurations in files rather than in the database, and version control those files...

Thanks!

Answer 1

You can use mysqldump to dump SQL like pupi1985 said, but in my experience that's not great for version control because everything is on one line, so a change to one row basically counts as a change to the entire file in git.

However mysqldump has a "tab" option which basically outputs a CSV file. Here's what I use for my site:

mysqldump -user root -p databasename --tab="directory/" --fields-terminated-by="," --skip-comments --skip-tz-utc "tablename";

Set databasename/directory/tablename to appropriate vvalues. Repeat for every table you wish to dump. You get a .sql file with the table structure and a .txt file with the data. To import into the other database, do this:

mysql -user root -p databasename < "directory/tablename.sql";
mysqlimport -user root -p --local --fields-terminated-by="," databasename "directory/tablename.txt";

Comments

"everything is on one line" => Not really. You need to use the --skip-extended-insert flag and there will be an insert per line allowing to backup the database in plain SQL code and keep track of any single change:

mysqldump --user=root -p --skip-extended-insert databasename > file.sql

---

True. I remember seeing that and I think there was some other reason I went with the tab option though, maybe it's faster.

Incidentally, I don't use either for the Q2A part of my site, I use the "one line" mysqldump for backups since I don't need versioning.

Answer 2

Any repository would take care of all PHP/CSS files. When it comes to the DB we have to consider data and structure. Usually structure can be handled with migrations. Q2A does not support migrations. However, when upgrading it actually run some kind of migrations (there is no way to rollback them, though).

When it comes to data, things get more complicated because you would be expecting not to version control a binary file but rather a text file. The only way I can think of is extracting the data from the DB and turn it into SQL statements, I mean, a backup to SQL.

Now you seem to be expecting to backup settings (the ^options table) and some other stuff too, like categories (^categories, ^categorymetas). The thing to bear in mind here is autonumeric fields. Make sure you set the appropriate value to them when restoring the backup. You'll have to go through each table and see if backing it up makes sense for you or not. You can use the mysqldump utility to do so or use MySQL workbench, if you prefer more graphical utilities.