Moving a Live Textpattern Site to a Local Server

by Destry Wion  ::  published 14 October 05    

Overview

These instructions describe how to move (more accurately, duplicate) a remotely-hosted Web site powered by Textpattern to a local computer with an Apache Web server. One might consider doing this in the event a live Web site was created without first establishing a local development site; hence, moving a copy of a live site to a local server will provide a convenient development platform (as well as another source of backup) for a live site.

Audience and Assumptions

Although anyone can follow this article as a guide for achieving the end result, it will probably sing best to those who are familiar with phpMyAdmin, and who have an interest/reason in setting up a local development site.

Also, the article makes a number of assumptions about your Web host, operating system, and so forth; if you don’t fit the assumptions, then expect some changes you will have to account for on your own:

1. Web host is TextDrive. (If you have access to your remote site’s phpMyadmin interface, that’s all that really matters.)
2. Your running at least Textpattern 4.0.1 stable (or a problem-free later revision).
3. MySQL databases are version 4.1 or newer. (This is pretty important; if your Web host is using older gear, get them to upgrade your database first. Thereafter, make sure your site is in working order before following these instructions.)
4. Local computer is Windows XP. (Just be aware that protocols and file paths will certainly be different with other operating systems or versions).
5. The latest XAMPP for Windows package is installed on the local machine and already setup, including the establishment of virtual host containers and the modification of the local hosts file. (The entire process, specifically with Textpattern sites in mind, is described in the TextBook doc, Using XAMPP (Apache-MySQL-PHP-Perl) for Windows.)
6. A Secure File Transfer Protocol (SFTP) client is available, such as WinSCP3 for Windows, or something with similar capabilities. (Necessary for TextDrive accounts).

The Nutshell Process

This is basically what you will do (just read for understanding)...

1. Move a copy of the remote Textpattern files of the target site to a directory (serving as a virtual development domain) on your local computer.
2. Use phpMyAdmin to export a dump of the live MySQL database as a .sql file.
3. Create a new MySQL database on the local server, which will serve as the new development database.
4. Edit the config.php file for the duplicated Textpattern site with the new database variables so it will communicate properly with the new database.
5. Import the exported .sql database file into the new database.
6. Login to the admin pages of the new, development site and troubleshoot any site diagnostics.

At the end of the article I’ll touch upon potential issues with passwords not working and some diagnostics handling with regard to charset handling.

The Instructions

Step 1: Moving the Textpattern Files of Target Site

The first thing to do is use a SFTP client to move a copy of the remote Textpattern files to the waiting directory on the local Web server (WinSCP3 is a good one for Windows).

Step 2: Exporting the Remote MySQL Database

The MySQL database that supports the live site now needs to be exported, and these instructions describe the process by using phpMyAdmin.

1. Login to the remote Web server’s MySQL database via phpMyAdmin. (TextDrive accounts should be able to login to their phpMyAdmin interface using mysql.servername.textdrive.com, replacing “servername” with the name of your actual TextDrive server.)
2. In the phpMyAdmin interface, find and click the name of your target database from the drop-down menu in the left column (the database’s properties will be displayed in the main panel).
3. Click ‘Export’ at the top of the main panel (the export options will be displayed).
4. Select the appropriate Export options using the following guidelines…
   • Click the Save as file box so that phpMyAdmin can save the resulting dump as an .sql file to your computer’s desktop.
   • The Compression options can help solve problems with large database files and timeouts.
   • The Structure and Data sections control the database’s table structure, and the information stored within the tables, respectively. Leave the default settings in the Structure and Data sections if you are going to be importing the .sql file into a clean database.

Step 3: Creating a New Database on Local Server

Now you you need to create a new, local database using the phpMyAdmin interface that came with your XAMPP installation.

1. Open your local phpMyAdmin. (If you followed XAMPP installation instructions exactly, you should be able to access your local phpMyAdmin at http://localhost/phpmyadmin/.)
2. When there, create a new database (a simple one-word name in lowercase is best), and leave everything at the default settings.

Between your XAMPP installation, the new database you just created, and the variables already in the transferred config.php file, you now have all the information you need to modify the config.php file for the local site setup.

Step 4: Modifying the Duplicated (Local) config.php File

Modifying the duplicated config.php file is necessary in order for the local Textpattern files to communicate properly with the new local database.

In the Audience and Assumptions section at the beginning of this article, I mentioned it is important to be using MySQL databases that were 4.1 or newer, and in this section we are going to touch upon that a bit.

First, let’s look at the current default config.php file, which came about with revision 636). The following code is the same as the source code for revision 636 (which is still the default code as of this writing), but with documentation and blank lines removed to look more like your actual files:

<?php
$txpcfg['db'] = 'databasename';
$txpcfg['user'] = 'root';
$txpcfg['pass'] = '';
$txpcfg['host'] = 'localhost';
$txpcfg['table_prefix'] = '';
$txpcfg['txpath'] = '/home/path/to/textpattern/dir';
$txpcfg['dbcharset'] = 'utf8';
?>

Revision 636 was significant because it introduced the ['dbcharset'] variable (with a default value of “utf8”), which we will come back to shortly.

Now let’s consider the variables themselves for clarity. For the most part, things should look fairly straight forward; there’s opening and closing PHP syntax, with seven variables in between.

The first variable, ['db'], is the name of the database, and in your new config.php file you will use the name you created earlier in step 3.

Then next two variables, ['user'] and ['pass'], are your login information for the database. For your local file, the user login and password will be the same as what you used when setting up the local phpMyAdmin via XAMPP; if you didn’t change the username during the setup, then the user will be “root”, but you should have created a new password either way—use whatever it was.

The ['host'] variable is essentially what informs Textpattern as to where the database is located relative to the server (or something to that effect). Since your local database will always be hosted by the server it is on, your local file will always use “localhost” for this variable.

The ['table_prefix'] variable tells Textpattern if your database tables have a prefix on the names or not. You may or may not have prefixes on your tables depending on what you chose to do when originally installing Textpattern on your remote site. Regardless, you will use the same value as used in the remote version of the file because those are the names of the tables you exported.

The ['txpath'] variable is the path to the config.php file relative to the server root. Your txpath for the remote file was created automatically when you initially installed Textpattern on your remote server (all you had to do was copy/paste it in); however, your local path will be relative to the root of whatever drive you set up your local domain directories on. If you installed XAMPP according to the instructions mentioned before, and subsequently setup your virtual host containers in the root of your computer, then the path is relative to the C:\ drive. Be mindful also that your slashes will change from foreslashes (/) to backslashes (\), and that you do not use a terminating backslash. As an example, here’s what my path looks like relative to my C:\ drive…

C:\txp\site1\textpattern

Basically, I structured all of my Textpattern development sites under one folder, “txp” which was an easy way for me to keep sites run by different publishing systems separate.

The last variable, ['dbcharset'], is a special case because of Textpattern revision 636; it is a special case because you may or may not even see this line of code in your remote config.php file, which is a reflection of how long ago you initially installed Textpattern. If you see the line of code, your original installation of Textpattern likely happened after revision 636 and your MySQL is version 4.1 or newer. Super. If you don’t see the line of code (like I do not in mine) it means your original installation was further back in time and you have been updating the site over several versions/revisions of Textpattern. That’s super too. Whatever it is for you, it’s correct, leave it alone. Your local config.php file should reflect exactly the same thing as your remote file; do not add the line if it’s missing, and if it does have the line, do not change the variable (i.e., whether it’s utf8 or utf8, don’t touch it).

Now that you know what you need to change, open the local config.php file with a proper text editing application like Crimsom Editor, set all the variables accordingly, then save and close the file.

Step 5: Importing the .sql Database Dump File into the Local Database

Your getting close. Now you will load the original database tables from your remote site into the new database.

1. Open up phpMyAdmin again for your local database and select the new database you created in step 3 from the database options in the left column.
2. In the resulting main view, click the SQL button at the top.
3. In the resulting view, look to the bottom where it reads Location of the text file. Use the Browse button to navigate to your exported .sql database file you saved to your computer’s desktop.
4. Once you grab the file, click the Go button in the phpMyAdmin interface.

A couple of moments will pass as the tables load into your empty database, and then you should see a message at the top of the view that all was loaded “successfully.”

Step 6: Checking for Success (the “If” Section)

If all has gone well up to this point, you have effectively duplicated your remote (live) site on your local server, and the front end of your local site should appear exactly as your live site. Your site path should be something like http://local.sitename.tld, depending on how you set up your virtual host containers when installing XAMPP.

If you don’t see your site as you expect, then it likely means you have a problem with the config.php file; open that file again and make sure the variables are correct against your local database. At the same time, check for any little syntax errors you might have accidently made (e.g., missing single-quotes around values, etc.).

If you can see your site’s front end, then theoretically you should be able to see your backend too, at least the login page (e.g., http://local.sitename.tld/textpattern/index.php). Your login username and password should be the same as it was on the live site, because this information was transferred in one of the database tables (the ‘users’ table)—try to login.

If you can login, then go to the admin > diagnostics subtab panel and deal with any diagnostics issues that might be immediately indicated in red text. Once you have done that, have a look at the section near the end of this article titled Advanced Diagnostics Troubleshooting; this will cover a worthwhile check of your charset arrangement.

If you cannot login to the admin pages, then you will be interested in the next section for troubleshooting the login problem.

Handling Local Admin Login Problems

Sometimes it is the case that you cannot login to the local admin pages of the newly duplicated site. I’m going to assume that you actually remember your password and that you typed it correctly without the ALL-CAPS key on, or whatever. If you are sure you are using the right password and it still does not work, there are a couple things you should look into.

Changing Your Admin Password with an SQL Script

First, you should try changing your admin password for the local account by running a little SQL script in phpMyAdmin. The script will overwrite the current password hash with a new one. Do this by following the TextBook instructions, Resetting Admin Password.

If that still does not work (as it did not for me) you need to roll up the sleeves and do the following.

Changing Your Admin Password Using a New, Local Installation of Textpattern

Allow me a second to lay some background: When I first transferred a live site to a local domain, I could not login to my admin pages; the database versions were the same and I had not forgotten my password (it just did not work) so the previous technique of changing the password was no help. Before transferring the live site, I had successfully installed a fresh copy of Textpattern into another local domain (for personal use).

Knowing that a fesh install worked without problems, I came up with a convoluted plan to basically get the password from the working (fresh) site to the users table of the one I still needed to login to. Thankfully I had sense enough to first run my methodology by the Textpattern community, where the very knowledgeable, and all-around good guy, Sencer pointed out a more direct (and obvious) approach. The method is still a bit more laborious than simply changing the password with a script, but it works, and I don’t know of any other method that currently does in this case.

Here’s what you do:

1. Create a new virtual host container on your local site and do a fresh TxP install of a brand new site with a brand new database; i.e., you’re not transferring a site this time, your actually installing a brand new copy of Textpattern locally. (If you do it right, you should be able to login to this new site’s admin areas no problem. Note also that when adding your login details you can use the same password as used in your other site; this new one will be good, while the other one is corrupt and will be replaced. The advantage here is you retain the same password as it orginally was.)
2. Now you need to copy the login password for this new install, which we know works, and use it to overwrite the password for the transferred site. Do this by first opening the users table of the empty site in the local phpMyAdmin, get into ‘edit’ mode for the table, and copy the password hash for the only user in the table (the admin account).
3. Next, browse to the same table for the transferred site using the same local phpMyAdmin interface. When there, get into edit mode and paste the new password hash you copied above over the old, funky password. (The old password will likely begin with an asterisk (*), but in any case, overwrite everything.) Save changes and close out of the database.

You should now be able to login to the transferred site’s admin pages using the new (uncorrupted) password you just pasted into the users table. Remember, if you did not use the original password in step 1 just above, then your local site will now have a different password from the live site unless you take steps to make them the same.

Advanced Diagnostics Troubleshooting

Once again, let’s return to the notion of the ['dbcharset'] variable we talked about in step 4. If you have not done so yet, I advise you to read the Unicode Support, UTF-8 documentation in TextBook, because I’m only going to touch upon what is presented there more thoroughly.

Open up your diagnostics panel in the admin interface of Textpattern and switch into full-view mode. Scroll down a ways until you see diagnostics that look similar to the first lines of the following…

Charset (default/config): utf8/
character_set_client: utf8
character_set_connection: utf8
character_set_database: utf8
character_set_results: utf8
character_set_server: utf8
character_set_system: utf8
character_sets_dir: c:\apachefriends\xampp\mysql\share\charsets/
19 Tables: OK

What you see above is a snippet from my diagnostics, and it is a very good example of the mix of utf8 and utf8 charsets being used as a result of revision 636. Just like with your remote config.php file, the lines above may look different from yours, but that’s expected because our site histories are different; you only need to be concerned with a few things.

The first thing to note is the first line that reads…

Charset (default/config): utf8/

This line tells us to important things: “default” and “config”, the values of which are shown at the end of the line. The first one (default) tells us which character-encoding the mysql-connection would use as a default, while the latter one (config) reflects the configured charset in your remote config.php file.

In the diagnostic line above, what you see is a default value of “utf8” and nothing for config. Remember back a minute to step 4 when talking about the remote config.php file and whether it contained the ['charset'] line of code or not; if you did not have the line (like in my case) then you will not have a config value in your diagnostic, just like above.

On the other hand, if you did have a charset variable in your remote config.php file, then you should see something like this…

Charset (default/config): utf8/utf8

Or this…

Charset (default/config): utf8/utf8

The next thing you should look at are the three lines for client, connection, and results; all three of these lines should have the same charset variable, and as you can see in my diagnostics, they are all utf8.

Finally, you should look at the last line where it gives the number of tables for your particular install of Textpattern, and whether they are “OK” or not. In my diagnostics example, I have 19 Tables that ar OK (i.e., 19 Tables: OK). That confirms to me that my tables and charset settings are just fine.

If your tables are not indicated as “OK”, and you see something like what follows, where the diagnostics are saying the tables are “utf8”...

19 Tables: 
txp_textpattern is utf8,
txp_txp_category is utf8,
txp_txp_css is utf8, 
txp_txp_discuss is utf8, 
txp_txp_discuss_ipban is utf8, 
txp_txp_discuss_nonce is utf8, 
etc ...

...it likely means you tried to change the value for the ['charset'] variable in your config.php file when you were not supposed to. Remember: you never have to (nor should you) change that value. Open up your local config.php file and change it to reflect the charset variable exactly as it is for the remote site (if you don’t have the line in the remote file, leave it out in the local file too).

However, here is a different situation; if you see this kind of indication, where is reads “clients are using or haven’t closed the table properly”...

19 Tables: 
txp_textpattern: 1 client is using or hasn't
closed the table properly, 
txp_txp_category: 2 clients
are using or haven't closed the table properly, 
txp_txp_css: 42 clients are using or haven't closed
the table properly, 
txp_txp_discuss_nonce: 117 clients
are using or haven't closed the table properly,
etc ...

...then you might have corrupted tables, but not definitely. Read the official documentation on it, Problems from Tables Not Being Closed Properly, for more information. Basically, what you should try and do is clear up the diagnostic by doing a REPAIR on all the tables indicated to have closure problems. There are at least three methodologies you can choose from to repair your tables, which are addressed in the next sections, but regardless of which approach you take, your tables diagnostic should ultimately read as “OK”.

How to Repair Your MySQL Tables

As mentioned above, there are three ways you can go about doing a REPAIR on your MySQL tables, depending on whether you are working on your remote database or your local database. Two of the methods use phpMyAdmin, and work for either remote or local locations. The third method is to use a Secure Shell (SSH) tunnel, and makes more sense for your remote database only (though I’m still looking into that).

Repairing Tables Via phpMyAdmin Check-Boxes

This is the first of the phpMyAdmin approaches and it is essentially the graphical user interface method; you do the repairs by using the controls in the phpMyAdmin interface. As the title suggusts, you will be checking a lot of boxes. Follow the instructions in TextDrive’s knowledge base article, Repairing a MySQL Table, MyISAM.

Repairing Tables Via a MySQL Script

This method also uses the phpMyAdmin interface, but it is a lot more straightforward; you repair your tables by simply running a little MySQL script.

1. Open up the phpMyAdmin.
2. Select the database from the drop-down list in the left column.
3. In the resulting view, click the SQL button at the top of the main panel.
4. In the script entry box at top, type the following (replacing table1, table2, table3, etc for each table you have a closure problem with)...

   REPAIR TABLE table1 , table2 , table3 , etc

   Use your diagnostics as reference to which tables you need to type. Don’t forget their name prefixes if they have any.
5. Click the Go button.

Repairing Tables Via a Shell Command

Going the Other Direction

If you want to go the other way, from local server to remote, it’s just going to be the opposite process. In fact, if you have a local installation of Textpattern already then it’s even easier because you don’t have to mess with the XAMPP installation and Win OS files and such. Here’s the brief:

1. Create your remote database (on the hosted Web account).
2. Move a copy of your local TxP file tree to the remote location.
3. Do an SQL dump of the local database tables.
4. Upload the dump to the remote database.
5. Edit the moved config.php file (now sitting at the remote location) to reflect the correct information for the remote database (name, password, dbname, etc.)

It’s pretty straight forward. If you have hangups it will most likely be with getting your config.php file right (names, paths and such). If you have problems, bring them up in the Textpattern Support Forum where a lot more people can help you quicker, and with greater insight. (Be sure to include your diagnostics information when asking for help or you’ll get community tomatos.)

That wraps it up. If you find something unclear or perhaps not exactly correct, don’t hesitate to point it out, and if you can elucidate the process of using the shell script as noted above, that would be great.

Latest Ten Articles

• Finally Pro-MacBooked 6 April 07

  Six months later than expected, and on the eve of the new Apple Leopards, I am nevertheless a happy owner of the MacBook Pro, and it feels good.

• What Makes a Good Web Accessibility Guide for the Business? 2 April 07

  With pressure mounting on web developers and companies alike to provide quality eAccessibility products and services, it makes good business sense for companies to have their own eAccessibility guidelines to help ensure development objectives are being met in efficient and cost-effective ways. However, just knowing guidelines are needed is one thing, producing and integrating them into a development workflow is something else. What breadth and depth of information should they cover? How should they be written and structured for maximum understanding? What format provides the best utility? Seemingly, the preparation of eAccessibility guidelines is not a fundamental task, the considerations are many.

• Main Points Delivered at the First European eAccessibility Forum14 March 07

  I’m not exactly punctual on this one, but making a long story short, here are the main points as I took them from the eAccessibility Forum held in Paris nearly 6 weeks ago…already.

• In Paris for the First European eAccessibility Forum27 January 07

  It’s going to be a whirlwind trip on the train, but should be interesting nonetheless.

• Lose Readers by Moving Themes?14 January 07

  We hear about free Weblog themes all the time, and see the same ones all over the Web, but it’s not often you read about someone moving a personal theme from one self-owned domain to another, or the implications of doing so.

• Book Reviews Coming to Wion18 November 06

  Reading and writing is my kind of chocolate, and when it comes to book reviews, everybody wins. I hope you’ll find them helpful. Stay tuned.

• A Core Textpattern Technique Addressing Internationalization Interests14 November 06

  This article presents a core Txp technique for managing internationalization efforts, and three methods of use are described: 1) multilingual publishing within site, 2) collaborative international publishing between individuals, and 3) an alternate approach to #2 that essentially takes a community slant.

• Textpattern Building Block Mechanics30 May 06

  Here is the second article in a two-article series about Textpattern building blocks. If you missed the first article, Understanding Textpattern Building Blocks, you might check it out too.

• Heatmap Presentation of Eye-tracking Data17 May 06

  Isotopic heatmaps of eye-tracking sessions are easier too see and understand; hence, great slide material for stakeholder presentations (management, clients, and so forth).

• IE Conditional Comments: Where Have They Been?19 April 06

  Conditional comments have been with us for years and largely unknown, but with the coming of IE 7 they may be the swan song that’s about to go platinum.