3 {{ upvoteCount | shortNum }}
3 {{ upvoteCount | shortNum }}

Neat WordPress Restore Technique

by faverr
2 replies
I came up with a technique that I thought others might be interested in.

I have a WordPress blog. I've had a need to do a backup of my blog content from the mySQL database and then do a restore back into the database after making some changes to the backup copy of the content (my reasons for doing these changes outside of the WP admin screens are a long story that I won't go into).

Ideally what I wanted to do was restore into a differently named database and then after the restore was done, rename my original database to something else and rename the new one to the original name. This way I could keep the original database around just in case the restored content ended up having a problem. If a problem occurred, I could just rename the old one back to the original name.

Unfortunately I found that there is no easy way to rename a database.

But I came up with an alternative. I write SQL commands to rename all of the tables in my WordPress database by prepending the string "old_" to the table names. Then, I restore my backup (which uses the original table names). If a problem occurs, I can just use SQL commands to delete the newly restored table names and then rename the "old_" ones back to the original names.

It's quick and it works great.
#neat #rename database #restore #technique #wordpress #wordpress backup #wordpress restore
  • Profile picture of the author floatingatoll
    Originally Posted by faverr View Post

    Unfortunately I found that there is no easy way to rename a database.

    But I came up with an alternative. I write SQL commands to rename all of the tables in my WordPress database by prepending the string "old_" to the table names. Then, I restore my backup (which uses the original table names). If a problem occurs, I can just use SQL commands to delete the newly restored table names and then rename the "old_" ones back to the original names.

    It's quick and it works great.
    I used this heavily at a prior job for scripting incident recovery tasks. I also used the prefix "OLD_" for the tables being backed up. For some operations I would create "NEW_" tables from the existing data set and then execute a pair of renames to swap out the current->OLD and the NEW->current tables in a fraction of a second. It was excellent for minimizing downtime, as long as you could temporarily suspend writes to the database.

    If you're using WordPress with MySQL (which appears to be the popular choice these days), in many cases you can move tables into another database:

    ALTER TABLE old_database.table RENAME new_database.table

    Both databases must be on the same filesystem, which is true for probably 99% of the installations out there. I've never used this, preferring the OLD_ and NEW_ method instead, so be sure to try it out on some test data before trying it in production.
    {{ DiscussionBoard.errors[281799].message }}
    • Profile picture of the author faverr
      Originally Posted by floatingatoll View Post

      If you're using WordPress with MySQL (which appears to be the popular choice these days), in many cases you can move tables into another database:

      ALTER TABLE old_database.table RENAME new_database.table

      Both databases must be on the same filesystem, which is true for probably 99% of the installations out there. I've never used this, preferring the OLD_ and NEW_ method instead, so be sure to try it out on some test data before trying it in production.
      Good suggestion. I considered doing as you mentioned, knowing that it was likely my databases would be on the same filesystem. However, I also knew if I were wrong, it would not work and I'd have to come up with another approach. Also if it did work, I figured there was the possibility at any time that my service provider could, as a part of system maintenance, decide to move a database to another filesystem. According to Murphy's Law, that would happen precisely when I was in a critical crunch time to get databases "moved around."

      I do think your approach is preferable if you have control over which filesystems your databases are on, or if you don't mind taking the small risk of having a database unexpectedly moved to another filesystem.
      {{ DiscussionBoard.errors[282039].message }}

Trending Topics