How to keep mySQL content in sync on dev and live servers?

  • I’ve been building my first WP site on my live server which is fine for now because it’s not public yet, so if I break something it’s OK since nobody’s looking at the site (and I have it password-protected until launch). But once it’s launched and no longer protected, and people are visiting it, I’ll want to be able to make edits to my child theme and to content locally on my office computer and test changes there instead, before uploading them to my host server. So I expect I’ll install MAMP and use FTP-based sync for my child theme’s files, as I have done before on a traditional static site. But since WP uses a mySQL database for the content, I will also need to keep my local database and the hosted database in sync and FTP would not work well for that unless I know exactly which files contain mySQL’s data and I lock visitors out from posting comments while I’m making updates locally, right? Is there a simple utility for keeping the databases in sync? If not, how have other people addressed this issue?

Viewing 10 replies - 1 through 10 (of 10 total)

  • I was going to say some kind of “cron” job might do it, but when you said local machine, I don’t know. I often do testing on separate servers, but never locally.

    It’s possible cron would work in that scenario, too. Some server expert person will have better ideas.

    Read through this thread:
    https://old.nabble.com/Keep-local-DB-in-sync-with-remote-DB-td20747766.html

    For what its worth, I never sync my development and production stuff from local to live, only the other way around. The reason is that the development server is for testing and testing sometimes breaks stuff. If you break your database and then upload before you notice, well, you’ve broken your database. I consider what you are doing kind-of dangerous. Plus, if you are trying to transfer content you are going to have trouble with paths, some of which are stored as absolute addresses in the database and would have to be search and replaced to move from local to live.

    Thread Starter davidtruog

    (@davidtruog)

    @flamenco: I like being able to work on my site when offline (e.g., while traveling) — hence the need for a fully functional local copy.

    @michaelh: I had found that thread but those solutions all seem pretty complex compared to everything else about WP so I thought someone must have a simpler solution. If not, I’ll go back to that thread and try to figure out whose proposed solution might be least cumbersome.

    @apljdi: You’re talking about having development and production stuff on both your local and your live servers? So four versions of your site? I’m talking about a simpler set-up: production on your live (remote) server, and development on your local server. So I think you’d agree that it would be even more dangerous to be “testing” new content on the live production server, right? Hence the need for what I’m asking about: making chances locally; then uploading that changed content one I’ve verified that it’s solid. Make sense?

    If it’s too complex, use one of the WordPress Database Backup plugins that emails you the database then just use phpMyAdmin to restore to your local machine when you need it.

    Thread Starter davidtruog

    (@davidtruog)

    That sounds like a useful compromise. Since it sounds like there are several, could you point me to one you that recommend, or at least that you’ve heard good things about? I’d appreciate it!

    David

    You’re talking about having development and production stuff on both your local and your live servers?

    No, just one local development install (per site) and then the production site online. My point was that I never upload local database stuff to the live site. I will sometimes pull the live database and install it locally, though. So, php, js, css, etc. go one way, not counting backups, from local to production. The database never does.

    I guess in principle “testing” content locally is better than not but I’m not sure you gain enough to justify the effort. If all you are talking about is content and not plugin or theme edits that could wreck the database and/or bring down the site, I can’t see that effort is worth is. The worst you are going to get with ordinary content is wonky formatting. In this case the effort you’ll spend trying to prevent the problem is grossly out of proportion to the problem itself. Plus, you may well need to run some complicated regex on your database in order to move it from local to live and that in itself introduces a certain risk.

    If you are talking about theme and plugin edits then you run a risk of corrupting the (local) database and that might not be immediately noticeable. If you then upload that local database you’ve uploaded a broken database and recovering could be a mess, especially as time passes. That is why I never take my development database online. It stays isolated and I can do what I will with it. If something goes wrong, then no harm done. Copy the live one, or any of several backups, and start over.

    Thread Starter davidtruog

    (@davidtruog)

    @apljdi: I see what you mean. What tool and/or process do you recommend for making a local copy of the content of the database on your server, for such testing purposes?

    Well, in a lot of cases you have to make due what your host has installed. PhpMyAdmin seems to be quite common and its easy to use but there are other things out there.

    Since PhpMyAdmin is open source you can download and install it locally for all of your local database exploration and manipulation, including importing the live database.

    Thread Starter davidtruog

    (@davidtruog)

    Thanks, @apljdi. Actually phpMyAdmin is already available on my hosted server, so I can use that, if that’s what you recommend as the best option where available. I appreciate the suggestion.

Viewing 10 replies - 1 through 10 (of 10 total)
  • The topic ‘How to keep mySQL content in sync on dev and live servers?’ is closed to new replies.