Refreshing database from production to development

  • Resolved lomes

    (@lomes)


    16 years, 8 months ago

    I am in the process of re-working my installation of WordPress to better track changes I make. Previously I was just tracking them in a txt file that I would put the filename, what I changed, and the reason for it. This was ok at first but gets unwieldy very fast.

    I decided to implement Subversion to help me out with this and so far it’s been great. The flow will be pretty simple:

    – Check out from working base/repository to my development workstation.
    – Make changes
    – Upload to development site via SFTP for testing purposes.
    – Commit changes to repository
    – Update development site with latest revision to get development site all in-sync.
    – Test changes
    – Update production site from repository.

    Everything is going fine with one exception. Database consistency! I’d really like to refresh the dev database with production before doing major changes like new/updated plugins, upgrading WordPress, etc…

    I’ve examined the database and can find three instances where they are significantly (meaning important, not complicated) different. All three simply differ in what is basically the site url. They are:

    PROD – https://mysite.com/
    DEV – https://dev.mysite.com/

    – wp_options table has an option of siteurl with these values
    – wp_posts has a guid column on each post that contains the url
    – wp_users has a user_url column for each user and of course “my” user_url is my blog site, which would differ (this one isn’t much of an issue, but I figure any solution will/should take care of it anyway).

    So here’s what I view are my two options:

    – Create a semi-sophisticated refresh script that exports prod database, backup/drop dev database, import prod data into dev base, and then go through and change all values to their dev equivilent.

    – Possibly implement some kind of .htaccess rewrite to rewrite mysite.com to dev.mysite.com in development.

    I am leaning towards the .htaccess rewrite option (if it will work) because it will be less to maintain and any performance slowdown will only affect dev which isn’t a big deal. The refresh script is a good backup I think though because it will ensure the configuration of each site is as identical as possible, but it’s more to maintain and prone to getting out of date.

    I’m curious if anyone has attempted something like this already. Or if there’s an official “best practice” from WordPress on this process…

    Thanks in advance for any and all comments. I’m going to get started on the rewrite method but I hope to get some good and varied points of view!

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

  • – wp_options table has an option of siteurl with these values

    And there’s the “home” option as well.

    You can overcome this problem by specifying them like so in the wp-config.php file, which override whatever is in the database:

    define('WP_HOME', 'https://www.site-url.test' );
define('WP_SITEURL', 'https://www.site-url.test/wordpress-directory');

    – wp_posts has a guid column on each post that contains the url

    The guid is just meant to be unique for each post; it’s not a URL, so it doesn’t matter if the “URL” it seems to be is just local or another site. All that is important is that it’s unique to that post. It could have just been a hashed string.

    Thread Starter lomes

    (@lomes)

    Perfect. After looking at it, I don’t think the mod_rewrite method would’ve actually worked anyway.

    I had no idea you could define those vars to override the database. I can easily live with the user_url not pointing to the ‘correct’ instance, and if the guid doesn’t really matter other than being unique, that’s great too.

    Now I’m just going to define those two vars in wp-config and have SVN ignore them when updating. Awesome!

    Thanks.

    hey filosofo and iomes:

    i’m trying to get to iomes’ point in the development stage and struggling w/ it. i’ve got a local build in MAMP of my site.

    i’ve got a live build too (you know, out there on a live server at an http address). But i don’t know how to build locally, and then upload my changes to the server as Iomes is doing.

    do i have to use SVN?

    do i have to name my databases the same in both environments and have identical u/p?

    for example, live i have this config:

    /** MySQL database username */
define('DB_USER', 'whoruns031009');

    but in MAMP, this:

    /** MySQL database username */
define('DB_USER', 'root');

    do i have to use the same AUTH_KEY, NONCE_KEY, etc for both environments?

    what’s a side-by-side comparison of the wp_config file – on the local server vs the live server. is the wp_config exactly the same?

    it sounds like filosofo is saying that you add 1 or 2 lines of code to say: “most recent changes trump older changes”. yeah?

    lastly:
    how does the database content “move around” and get modified from one server to the next? that is, what path does the database content sitting in phpMyAdmin take to get from its location on one server to its location on another? does it pass via ftp?

    i see that the db sits outside – or above – htdocs. so how do i incorporate it into the site transfer?

    any screen grabs would help.

    thanks, guys.

    sergi

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Refreshing database from production to development’ is closed to new replies.