IMPORTANT: WordPress serializes certain data, therefore some database values may appear not to transfer (widgets, nav menus, Settings API data, etc.). There are various solutions if you want to manually post process your .sql dump files for migration to a new domain name, but the easiest way I’ve found around this is to create both your staging server and localhost domain names of equal length to your final deployment endpoint. If you’re developing a project for example.com then using ex.dev is ill advised for your local dev; best to keep it the same length and go with example.dev.
In your Git repo’s root dir navigate to the hidden dir at .git/hooks/
There you will find all of the hooks available (disabled by the .sample file extension); removing the .sample will enable them.
The two I use are pre-commit in my dev environment and post-merge in my staging/live environment.
In pre-commit, I want to:
- Dump the dev database
- Quickly search/replace any occurences of my dev URL with the staging or live URL (and keep a handy copy of a timestamped original before the replacements)
- Add the dumped database file to the list of staged files for commit along with my other changes
I’ve opted to store my database dump files in a hidden dir I made in my project root, .sql/
So, given all that, here is my pre-commit hook:
#!/bin/sh
mysqldump -u [dev_db_user] -p --skip-extended-insert [dev_db_name] > ./.sql/db.sql
sed -i.$(date +%Y-%m-%d-%H.%M.%S).dev s/mysite.dev/mysite.com/g ./.sql/db.sql
git add ./.sql/db.sql
Each line of that script corresponds to the numbered list up above.
Now we have a nice fresh database dump that’s had our dev URLs replaced and it’s committed. Time to push!
Now on our staging server we’re getting ready to pull from our Github/Bitbucket repo. After we merge those changes we want to:
- Dump the current staging/live database into a timestamped backup
- Import our recently committed file (which, if you’ve done it my way, will be at
/.sql/db.sql
So to do that, I head to my staging/live server and cd into /.git/hooks/ and remove .sample from post-merge.sample–if you don’t have that sample file then just touch post-merge to create it.
This is my post-merge hook:
#!/bin/sh
mysqldump -u [live_db_user] -p [live_db_name] > ./.sql/db.sql.$(date +%Y-%m-%d-%H.%M.%S).live
mysql -u [live_db_user] -p [live_db_name] < ./.sql/db.sql
Again, that script performs the actions in the numbered list that precedes it line by line.
Now that I’m not shuffling my database back and forth as much I’ve added the .sample extension back to the hook scripts.
If you wanted to bypass the pre-commit hook I’ve read that you can by using the --no-verify argument when committing, alá git commit --no-verify -m 'Commit message' (I actually haven’t tested that). The downside is that there is no command line option to bypass the post-merge hook, so your staging/live server won’t get a new db.sql file, but it will create a backup and re-import the last db.sql file you committed… hence I’ve commented out both hooks.
Let me know what you all think and if anyone out there has a more efficient way of doing this!
Sources: githooks - Skip Git commit hooks - Stack Overflow, http://ben.kulbertis.org/2011/10/synchronizing-a-mysql-database-with-git-and-git-hooks/
