From wiki.kungfootek.net
Jump to: navigation, search

WordPress sites are fairly portable. You can export it's database and filesystem components and move them to another location and it will be happy most of the time.

What it doesn't like is having it's domain name changed. The domain name can be changed in the menu system if both domains already point to the same location. Obviously this isn't very common.

If you're testing on a separate host or waiting for DNS you can use a hosts entry on the PC's looking to connect.

Mysql Database

Dump and import your database with the following commands:
mysqldump -u<user> -p<pass> <databasename> --add-drop-table --complete-insert  --disable-keys --insert-ignore > <database_output_file.sql>
mysql -u<user> -p<pass> <databasename> <database_output_file.sql>

If it's a permanent domain change there are 2 tables you will need to change to access any page on the site.

SQL Commands to change site Domain:
update wp_options set option_value='http://$Domain' where option_name='home'");
update wp_options set option_value='$Domain' where option_name='blogname'");

Optional, but included just in case:

SQL Commands to change Admin email address, and User Login, and User email address
update wp_options set option_value='$EmailAddress' where option_name='admin_email'");
update wp_users set user_login='$UserLoginName', user_email='$EmailAddress' where id='1'");

Config File

Explanations and Details of what else can live in the Wordpress configuration file can be found at the WordPress Codex

Database Connectivity
define('DB_NAME', '$DB_NAME');
define('DB_USER', '$DB_USER');
define('DB_HOST', '$DB_HOST');

Multi-User or Network sites will require the following to be correct or you will get 404's and other oddities on your admin page. Even though the main pages will load, secondary links on the site will fail to resolve, or will resolve to the prior domain.

Network Site

Auto-Updates and transparent module / theme installation

This portion of the config is not found in the default configuration file. Adding these lines and enabling SSL for FTP will allow users to upload or install Modules and Themes directly from the WordPress Admin interface.

define('FS_METHOD', 'ftpext');
define('FTP_BASE', '<$Path_to_ReadWrite_FileSystem_Base_OF_WordPress');
define('FTP_USER', '<$RealFTPUser');
define('FTP_PASS', '$RealFTPUserPass');
define('FTP_HOST', '<Domain_Name_Or_IP_Of_FTP_Server');
define('FTP_SSL', true); # <--- MUST be true for FTP to use FTPS ( Not SFTP )

If using SSH2 as a connection method you must generate keys. Do not use a passphrase. Make sure they are not in the home of the web folder and copy the public key into authorized_keys(2). Test the connection with the address entered for FTP_HOST As the $RealFTPUser.

su - <$RealFTPUser>
cat .ssh/id_rsa.pub >> .ssh/authorized_keys2
ssh Domain_Name_Or_IP_Of_FTP_Server (Yes)

Type Exit Twice to return to the initial prompt.

WordPress Database Internals

There are 4 ways you can host a WordPress database.

  • One Database per WP Site ( preferred. )
  • Single Database for many WP Sites ( Beware the Prefix. )
  • One Database per WP Network ( Not optimal. )
  • Single Database for Many WP Networks ( Do this and I will cut you. )

with one Database per WP Site you will not need a prefix at all. This is the simplest configuration.

With a single Database for many WP Sites you will have to be careful not to overlap any prefixes. You can have ONE WP site with no prefixes mixed in this database.

If you are hosting a WP Network I feel sorry for you if you are ever asked to move a singular site off the network into it's own server instance. There are no publicly available tools that I am aware of and it is very difficult. The best option is to NEVER network your WP site.

The Fourth Option can only be a result of horrific planning and can be separated into at least one database per network with instructions following below.

Importance of the table prefix

A Common gotcha to moving a WordPress site is the site prefix, and will require some database kungFoo. First, a little more about the prefix.

$table_prefix  = 'wp_';

This Prefix is how multiple WordPress tables can live in one database. A fresh WP install will have 11 tables. One Database per WP Site With the prefix active the 11 tables will look as such:

| Tables_in_homeschoolcom |
| wp_commentmeta          |
| wp_comments             |
| wp_links                |
| wp_options              |
| wp_postmeta             |
| wp_posts                |
| wp_term_relationships   |
| wp_term_taxonomy        |
| wp_terms                |
| wp_usermeta             |
| wp_users                |

A single Database for many WP Sites will look like the following. Notice how the tables are distinguished from another based on the prefix. The average user will never see this, and finding a problem in a specific site is painful if the entity performing the install did not use obvious names for prefixes.

| Tables_in_homeschoolcom |
| Happy_commentmeta       |
| Happy_comments          |
| Happy_links             |
| Happy_options           |
| Happy_postmeta          |
| Happy_posts             |
| Happy_term_relationships|
| Happy_term_taxonomy     |
| Happy_terms             |
| Happy_usermeta          |
| Happy_users             |
| Days_commentmeta        |
| Days_comments           |
| Days_links              |
| Days_options            |
| Days_postmeta           |
| Days_posts              |
| Days_term_relationships |
| Days_term_taxonomy      |
| Days_terms              |
| Days_usermeta           |
| Days_users              |

A network site will increment the prefix and looks like this:

| wp_1_commentmeta             |
| wp_1_comments                |
| wp_1_links                   |
| wp_1_options                 |
| wp_1_postmeta                |
| wp_1_posts                   |
| wp_1_term_relationships      |
| wp_1_term_taxonomy           |
| wp_1_terms                   |
| wp_blog_versions             |
| wp_blogs                     |
| wp_registration_log          |
| wp_signups                   |
| wp_site                      |
| wp_sitecategories            |
| wp_sitemeta                  |
| wp_usermeta                  |
| wp_users                     |

A network site will increment the prefix for each new site that is added, and which domain is related to which prefix is tracked within the WP system. In this configuration WP uses 9 of the 11 tables for each site, and combines users, and usermeta. When it's time to separate, each user must be referenced against their usermeta data to understand which WP site that user belongs to, and the appropriate new tables generated for each WP Site. If you are diagnosing a troubled site as mentioned above you might start by analyzing these tables first.

a glimpse of wp_usermeta. 3 relevant rows out of 16. This is unaltered from a live DB.

  umeta_id: 6
   user_id: 1
  meta_key: primary_blog
meta_value: 1
*************************** 6. row ***************************
  umeta_id: 7
   user_id: 1
  meta_key: source_domain
meta_value: blog.kw.com
*************************** 7. row ***************************
  umeta_id: 8
   user_id: 1
  meta_key: wp_1_capabilities
meta_value: a:1:{s:13:"administrator";b:1;}
*************************** 8. row ***************************

The moral of this section: Don't ever network your wordpress site. There is no good reason to. It adds complexity, and removes your ability to scale.

KungFoo Database

Altering the prefix to move WP table sets to other databases.

If you haven't read the WordPress_Database_Internals then you should go back and read that before continuing.

As mentioned above sometimes you need to rename the tables in a database to move WP sites around. There isn't an easier way that I have found to accomplish this than the method below. I will reference items I have already covered above.

Create a perl script with the following, omitting the line numbers:

  1 #!/usr/bin/perl
  3                 open (EXPORT, "+>", "destination.sql") || die "Cannot open destination file";
  4                 open (IMPORT, "source.sql") || die "Cannot open original file";
  5                         while ($Process=<IMPORT>) {
  6                                         $Process =~ s/match/changeto/g;
  7                                         print EXPORT $Process;
  8                         }
  9                 close IMPORT;
 10                 close EXPORT;

In line 3 change 'destination' to something descriptive about your database name and status. 'happy_renamed-tables.sql' as an example.

In line 4 change 'source' to the name of the exported database. Instructions on how to export are above.

In line 6 change 'match' to the prefix you want to move FROM, and 'changeto' the prefix you want to move TO. Eg: $Process =~ s/days_/wp_/g;

Assign it execute permissions and assume we called it alter-prefix.pl Once that's ready and you have confirmed it's right ( Double Check ) you can start with the rest of the process. the alter script should be in the same folders as your exported database.

- For one Database per WP Site:

# Export your database
# run alter-prefix.pl 
# enter mysql and drop the blog database ( You have the original export as a backup.)
# Still In Mysql re-create the database
# exit mysql.
# Import the new database.

Test the site checking front page, admin page and all links.

- for a Single Database for many WP Sites

# Use a tool such as PhpMyadmin to export only the tables you want. 
# If moving the site, drop those tables. If making a copy for testing leave them alone. 
# run alter-prefix.pl 
# Import the renamed tables at your new, or test site.

Test the site checking front page, admin page and all links.

- One Database per WP Network

Removing the individual sites from a network is more complex than will be covered here, at this time. - Sorry.

- Single Database for Many WP Networks

Same as directly above. Removing the individual sites from a network is more complex than will be covered here. However, you can separate the disparate networks using the same script above with an appropriate change to line 6. The script will match the prefix ending with an underscore. The number indicating the site iteration will remain.