How to handle Database from Dev to Server?

So I’ve been curious how others do this.

Using Git and pushing to Github, Bitbucket, or your own server is great, makes transferring your theme or project pretty simple. We also can use Composer to deal with all dependencies, which is great.

How do you handle moving the database though? I’m considering this not only for moving from your local development area to a server, but also for backup reasons.

Normally what I do right now is PHPMyAdmin locally and on the dev server, get a DB dump locally, save it, and load that on the server. If I’ve done this previously, I’ll drop all tables in the DB on the server, upload again, and change the two WP options for site and home URL. Then it’s mirrored.

I’m wondering if the .sql file should be included in the theme’s root dir, and could then be included in the Git repo. This would not only help keep a backup of the original, but also, when I SSH into the server, when I pull the Git repo, I can also handle the DB in the MySQL CLI.

How do you do this?

3 Likes

Really interested in this question despite me being a few steps behind you (haven’t been able to understand how to use BitBucket, have a local server environment, use composer or push to my own server yet!)

Pushing changes in the database seems to be something it is never discussed and puts me off developing locally. Getting DB dumps from PHPMyAdmin would be the only way I could see how to do it, but there must be better ways. You’ve also got issues with changes in absolute paths, domain names, SSL etc to deal with.

Is this something that you’d use the mystical “Capistrano‎” for?

If there’s one suggestion I could make, it’d be to never use absolute paths… perhaps in very specific instances, but other than testing, I can’t really think of a good time to use absolute paths.

I would try to use Wordpress as much as possible, that way you only have to change the site_url in the database and ALL your links are updated.

I can’t really comment on SSL, since I haven’t worked on sites that make extensive use of them.

In regards to Bitbucket, just start using Git. I found the ebook Pro Git to be a very helpful intro: http://git-scm.com/book

http://www.knewton.com/tech/blog/2012/07/announcing-wp-stack/

This seems relevant to the discussion. I’ve looked at MJ’s github repo, but don’t know anything about Capistrano so am having trouble seeing the do-something instruction for the configuration.

Also the following, which points to Capistrano WP deploy tutorials by Konstantin Kovshenin and identifies a couple of sticky bits.

I don’t know much about the more advanced ways of doing this, but I have found WP Migrate DB plugin to be wonderful. The free version is great and will let you migrate the DB in about 5 mins. In order to skip PhpMyAdmin entirely, I recently updated to the Pro version which lets you push/pull databases straight from the WP admin. I have to say that I am in love :smile:

The great thing is that both free and paid versions will will automatically replace the URLs before creating the sql dump or migrating the db in the Pro case. It searches the entire DB and replaces the URLs so you do not have to worry about using absolute paths for links or images within posts.

4 Likes

Good point, adding an image or link in the WP editor does cause problems.

For copying WP database MyPhpAdmin is best (ssh can do it with simple command) with addition of searchreplacebd2.php script which is able to replace all absolute links in whatever you want. For copying whole data from server I find usefull plugin Wordpress Importer which is able to import almost all data and images, almost because this plugin skips links and asociated categories. But good thing is that if you made mistake on image dimensions (large, medium, …) you can easily corect that by setting new project with corect dimensions and simplly import creating same project with different dimensions. I think that you can this way copy data to server if you have local environment with dynamic dns. In my opinion we need tool that update server, something like git for wp-mysql, that send changes to sever. Crete new post localy, ok update, and thats it.

This what I’m using and I LOVE it. It’s worked very well for me, and the developer has been super responsive to support requests.

@kalenjohnson I recently got into using Git and experienced the disappointment of having to manually transfer my .sql files. Then I discovered Git hooks. I’m sure there is a far more efficient long term solution, but these worked wonders for me. Basically just before commit you can add a hook that will dump your db and stage the new db file so it gets committed with your changes. Then just after merging your new changes I set it up to create a dump of the existing db (as a backup) and import the newly committed db. If anyone wants more details then let me know and I’ll throw together a more detailed post on how I did it!

3 Likes

I’ve wondered about this too,
Ive been dumping the database from localhost.
clearing any tables in the remote one,
re-uploading to the server
changing the links in the wp-options table.
thats probably a really cack-handed way of working :grin:

although on re-reading I see thats what OP said too, Id like an easier method though!

That’s a cool idea, can you use this hook only when requested? I wouldn’t want to be constantly committing my database, but I would definitely do it after setting up some new custom fields or adding a bunch of posts/pages.

1 Like

That’s part of the double edged sword I’m dealing with. Currently there is a way to bypass the pre-commit hook but I’m unaware of a method to bypass the post-merge hook (from shell).

For my latest project I noticed there was a period where this came in handy, then I no longer needed to shuffle my db back and forth so I simply removed the hooks.

Part of why I would post it is to get some feedback from more experienced Git users on a more effective way to do things.

I’ll get working on putting something together shortly.

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:

  1. Dump the dev database
  2. 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)
  3. 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:

  1. Dump the current staging/live database into a timestamped backup
  2. 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: http://stackoverflow.com/questions/7230820/skip-git-commit-hooks, http://ben.kulbertis.org/2011/10/synchronizing-a-mysql-database-with-git-and-git-hooks/

1 Like

I fell deep into the configuration management rabbit hole and haven’t been active here for a while.

I think the best answer would be to script it as part of your move from development to staging (or stating to production). So when you run your Capistrano or whatever script to deploy dev to stage, the script would dump the DB, move it, restore it. (I’m looking at Ansible because setting up Ruby/Capistrano on my Debian vagrant was a nightmare I don’t want to repeat–nowhere near as simple as they lead you to believe in all those Capistrano tutorials.)

Mark Jaquith seems to have a solution in WP-Stack with a db sync task. See this thread on github hXXps://github.com/markjaquith/WP-Stack/issues/5 and the .gitignore for db-sync and database-dump files in WordPress-Skeleton. But if I’m reading the Ruby right, it doesn’t change any embedded links (say, in your content). There are scripts to do that, though.

EDIT: And I should note that the one thing this still doesn’t address is database updates. If you build a site and launch into production, then come back days, weeks, or months later and need to add new content that WordPress keeps in the database. In that case, you don’t want to be overwriting the whole live database if your users are creating content. And I don’t think you really want to be doing a database diff between production and dev/stage either.

1 Like

Also you might want to take a look at Search Replace DB which comes with a command line option for replacing serialized values in the wordpress database. It’s free as well. There’s a version with a GUI and an optional CLI version as well.

1 Like