MYSQL development workflow with git versioned scripts
I’m sure that similar workflows are common, but in my searching I have not found any documented examples.
- Is there a convenient way to include a mysqldump in my git commits?
- How to include MySQL database schema on GitHub?
- Git Squash commits from mysqldump
- how to repack logfiles with git efficiently
- mysqldump schema only, schema update without drop
- git pre-commit + mysqldump: cannot find path, not existing command
- I want to manage all of a mysql (v5.1+) database definition in
git, so that I have all of the benefits of source control.
- I want to define changes to the database by editing the scripts, and
running the scripts, so that I can edit the database definition in
my editor of choice (vim), rather than the mysql shell, or a gui
Aside: I currently use the mysql shell, but would like to use vim to maintain routines and events which becomes unwieldy in the shell.
I will do an initial dump with the ‘–no-data’ option to get my initial scripts, but from then on I want my workflow to be: edit scripts -> commit -> run scripts rather than: perform change to database -> dump ddl -> commit.
If I dump the database with the following:
mysqldump -u [user] -p [password] --no-data --routines > ddl.sql
DROP TABLE IF EXISTS `[table]`; CREATE TABLE `[table]` ([table definition])
Obviously that will destroy, and recreate the table if I run the script.
What I would like to do is update the table with definition changes if the table exists, otherwise create the table with the definition. I would prefer to define the table in one [table]([table dfinition]) block, that is re-used, than to have to maintain multiple duplicate blocks.
How would I change the initial dump syntax, or the scripts, so that I can use the same scripts to update the database, and re-create the database definition (without data) in another environment?
One Solution collect form web for “MYSQL development workflow with git versioned scripts”
As you point out, just
mysqldumping does not work for this.
Generally, you need to store the changes to the database structure. So instead of storing:
// commit 1 // foo.sql CREATE TABLE foo ( foo_id INT ); // commit 2 // foo.sql CREATE TABLE foo ( foo_id INT, foo_val INT );
You should store something more like:
// commit 1 // foo_1.sql CREATE TABLE foo ( foo_id INT ); // commit 2 // foo_2.sql ALTER TABLE foo ADD COLUMN ( foo_val INT );
Then, you should have a script that can run
in the right order.
You would be even better off to write scripts that have up() and down() options, so your migrations can be run forwards (build table, add column) and backwards (drop column, drop table).
You just have to remember to run the backwards scripts before you check out a branch that relies on the db change.