Atp's external memory

Wordpress migration to a new system.

Restoring Wordpress backups to a new host.

I've helped out a friend with their website recently, and it made me realise how difficult technology can still be for people who don't do this on a daily basis. Even smart people - she has a background as a professional scientist, artist and now successful businesswoman - can still require help with what on the surface appears to be the simple task of moving a website created on her mac using MAMP to the hosting company. 

Although it's not hard it requires an understanding of how things fit together so here's a script I came up with for migrating her production site. I practised by restoring a wordpress backup to a clean CentOS/RHEL 6.4 server in EC2. The information was gathered from lots of blog posts, the Wordpress Codex, Mysql docs and some trial and error.

As always - the information presented here is for my own notes and use. If it helps you fine, if not - it's at your own risk. 

The starting point I'm assuming is a backup made by something like XCloner or Google Drive for Wordpress (GDfW). The important feature being that you have both the files and a dump of all of the wp_tables in the database. For XCloner you can include drop statements as well (presumably "DROP IF EXISTS") but the backup I had to work with didn't have those. 

This is a manual restore. The XCloner.php and TAR.php do some of this, and make a good start, but the backup I had to restore with was a google drive one, and it was just faster and easier for me to handle it by hand. 

So, starting with a clean Centos 6.4/Rhel 6.4 install

Prepping the machine

Install apache, php and mysql to give us a standard LAMP stack. 

As root;

yum install -y php php-mysql httpd mysql-server mysql 

run mysql_secure_installation, and turn them all on 



chkconfig httpd on
chkconfig mysqld on
service httpd restart
service mysqld restart

at this point for XCloner to work (if you choose to use the script) you can save yourself hassle by turning off secure linux.

setenforce 0

 Now we need to prepare the machine for wordpress by removing the welcome.conf and allowing fileinfo overrides so that permalinks will work. Also at this point check that mod_rewrite is present. (The italics show the output)

rm -f /etc/httpd/conf.d/welcome.conf
ls -1 /etc/httpd/modules/

  In /etc/httpd/conf/httpd.conf find the stanza that starts;

<Directory "/var/www/html">

and ensure the directives are set as following; 

<Directory "/var/www/html">
Options Indexes FollowSymLinks
AllowOverride All
Order allow,deny
Allow from all </Directory>

 and tell the server to double check its config.

apachectl graceful

Now, I'm being lax here with the AllowOverride ALL, but it was a disposable test server. See here for more details about this step. On Linux AllowOverride FileInfo suffices. 

Check IPTABLES allows your site to be visible. As root;

iptables -L

You're looking for a rule that looks like this in Chain INPUT

ACCEPT     tcp  --  anywhere             anywhere            state NEW tcp dpt:http

At this point the server should be ready to roll. 

Restoring the Wordpress Backup

The first step is to find out about the environment our backed up wordpress expects to be living in.

Unpack/zip/tar the backup into /var/www/html 

We'll need to look at the wp-config.php file which should be in the document root, and we will need the location of the sql backup. For XCloner this is under administrator/backups and for GDfW this is under wp-content/db. They're both .sql files. 

From wp-config.php we want to know the following;

/** The name of the database for WordPress */
define('DB_NAME', 'the_wordpress_db');

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

/** MySQL database password */
define('DB_PASSWORD', 'a_password');

/** MySQL hostname */
define('DB_HOST', 'the_db_host');

Take notes of those. Almost all the time the_db_host is localhost, so I'll use that below. 


Now we prepare the database for the data import;

Using the mysql root password you set during the run of mysql_secure_installation (you didn't skip that did you?)

mysql -u root -h localhost -p

We create the database and the user; 

create database the_wordpress_db;
create user 'a_username'@'localhost' identified by 'a_password';
grant all on the_wordpress_db.* to a_username;
flush privileges;

Obviously I hope that by now you've realised that you'll need to replace the tokens "the_wordpress_db", "a_username" and "a_password" with the values you found in your wp-config.php file.

 If your database and web server don't live on the same server, then you'll need to adjust your mysql command line and the user you create to reflect the names of the db server and the web server. 

We can check the privs and user are set up correctly by using something like this;

SELECT * FROM information_schema.USER_PRIVILEGES where grantee like '%a_username%';

 You should see something that looks like this, again with a_username replaced by your actual username in your wp-config.php file. 

| 'a_username'@'localhost' | NULL | USAGE | NO |
| 'a_username'@'%' | NULL | USAGE | NO |
2 rows in set (0.00 sec)

Now onto the import of the data. 

cd to the directory the .sql file is in. For XCloner, this will be called database-sql.sql in administrator/backups, and for GDfW it will be called something like loginname_1370940213.sql in wp-content/db/. 

using the mysql command line again;

mysql -u a_username -h localhost -p

and use these commands;

use the_wordpress_db;
source database-sql.sql;

The second one should create all your tables.  It may take some time to complete. Again replace the_wordpress_db with the name of the database you typed into the create database command above, and you found in your wp-config.php

You can check it worked by using

 show tables;

at the "mysql>" command prompt and you'll get the following output if it worked. 

| Tables_in_prrcfi_wp |
| wp_commentmeta |
| wp_comments |
| wp_gd_manager |
| wp_links |
| wp_options |
| wp_postmeta |
| wp_posts |
| wp_term_relationships |
| wp_term_taxonomy |
| wp_terms |
| wp_usermeta |
| wp_users |
12 rows in set (0.00 sec)

Depending on the plugins you have installed, you may see different tables. In this case, as the backup was done with GDfW there's a 'wp_gd_manager' table that won't be present in a vanilla install. 

If you don't see that, stop here and figure out what went wrong. You may have created the database incorrectly, the user incorrectly or the .sql file may be empty.  In which case your backup is hosed and you'll need to try backing up again.  One common mistake is to fluff the user privileges. If you "grant all on the_wordpress_db" rather than "grant all on the_wordpress_db.*" your import will fail. Don't miss off the ".*". 

Great - now we have a database - hopefully full of stuff. 


We're halfway through. 

If you're using permalinks, then check the .htaccess exists and apache can write to it (assuming you want to manipulate your permalinks from the wp-admin console). 

ls -l .htaccess 
-rwxr-xr-x. 1 apache root 236 Jun 12 17:31 .htaccess

again italics denoting the output. 

now, we need to adapt wp-config.php to its new environment

We've already ensured that the database matches the DB_NAME, DB_USER, DB_PASSWORD, DB_HOST directives as above, so we don't need to change them. However we may want to adapt our install to its new home. 

We need to add the following lines to wp-config.php;


 If they already exist, then change the values there, but its likely you'll need to add them. 

Obviously change the string to the name of your webserver - for example or or whatever. (Apologies to whomever may own 

The site at this point should be up to serve traffic, but all the posts will be broken and there's various other bits of clean up required. 

The next step is to fix up the links to the old site name in the wp_posts table. 

Fixup wp_posts

We can do this most simply using searchreplacedb from here

 This is a php script you can put in /var/www/html and then call with a web browser. It will pick up the database settings from wp-config and give you a list of tables. Once you have selected the tables you want to run it on it will allow you to replace one string with another. Its basically a database/wordpress aware "sed -e".

After loading let it populate the values from wp-config.php, then select wp_posts in the list of tables it brings up. Make sure its just wp_posts.

You'll then be presented with search and replace boxes. You'll need to type in the old hostname in the search box and the new hostname in the replace box. Take care, as no validation checking is done. If you get it wrong, then you'll need to start again with the db import (fastest route is to use mysql to DROP DATABASE a_wordpress_db, and then run CREATE DATABASE a_wordpress_db and redo the import of the sql file). 

This script will then run through the wp_posts table and replace all the hardwired entries to the old hostname with the name of the new hostname.

Check the output. The number of entries changed should be a non-zero number.

 At this point, delete searchreplacedb2.php from your root. DONT FORGET. This script gives direct access to your data from the internet. If you don't you may as well post the wp-admin password as your first post. 

Fixup Permalinks etc.

Assuming you're using permalinks, and the check on the .htaccess file passed earlier, then simply log into the wp-admin site (http://your.webserver-name/wp-admin/) and in the left hand menu go to Settings -> Permalinks. Don't change anything, but hit the "Save Changes" button. A message should pop up near the top of the page saying permalinks updated. If it doesn't check the permissions of the .htaccess file, and check your selinux status. 

The next step is to find and replace any occurrences of the old hostname and replace it with the new hostname in the various bits of the Wordpress wp-admin pages. I found I needed to fixup the following;

1) CSS references to background image.  (Appearance->Theme Options->Design Options->Custom CSS)

2) Theme Links. (Appearance->Theme Options->Advance Options->Home Slogan Options) 

In both cases I was relacing the host part of the URL with the name of the new hostname. 

 Wrap up

At this point its about cleaning up. Don't forget to;

  1. Complete the usual lock down of a brand new Centos server on the internet. (That's a post in itself)
  2. Change passwords for your wp-admin account. 
  3. Remove searchreplacedb2.php
  4. Remove any remote access to the mysql database you may have added
  5. Run mysql_secure_installation if you haven't already
  6. Remove extraneous files and tighten up the permissions in /var/www/html
  7. Consider turning selinux back on (Actually, let me rephrase that: the change we made above was temporary so if  you really don't want it back on, then you'll need to make it permanent: google is your friend here) 
  8. Take a backup of your migrated wordpress db with one of the tools and store on a different machine
  9. Reboot the system to test it all comes back (you may have missed a chkconfig command, or an iptables rule)

And lastly - get a beer and be pleased that you remembered to take backups in the first place (assuming its an "unscheduled" move).

Apologies for any transcription errors in the above.  After a practise run, it took me  about 10 minutes to complete.

Written by atp

Saturday 15 June 2013 at 09:54 am

Posted in Default, Linux

Leave a Reply