Jonathon Delacour the heart of things

MT+MySQL·PHP: 03 phpMyAdmin

This installment is aimed at anyone who needs to transfer their posts and comments from an existing weblog (MT, Blogger, Radio, Greymatter) to a Movable Type weblog with a MySQL backend.

If you are simply upgrading Movable Type and switching from the Berkeley DB database to MySQL, this post will be of academic interest since all you need to do is run the conversion script (mt-db2mysql.cgi) that comes with the upgrade distribution.

But in my case, I’m moving servers so I need to get the content out of my current weblog and into the new one. A similar situation applies to anyone migrating from a different blogging tool to MT.

I’m also changing from .html pages to .php pages so I’ll have to implement a redirect mechanism if I’m to preserve my permalinks. For that to work as smoothly as possible, I need to ensure that the entry IDs in the new weblog exactly match those in the old one. For me anyway, it didn’t happen with a single export/import. I had to tweak my export file and run the import a number times before the posts were imported properly, which meant I needed to start with a fresh MySQL database each time. phpMyAdmin makes that a snap.

At this point, I’m assuming that your hosting plan includes MySQL and PHP and that you’ve installed Movable Type (specifying that you want to use MySQL as the database rather than Berkeley DB).

Installing phpMyAdmin

You can download phpMyAdmin from www.phpmyadmin.net—at the time I’m writing, the current version is 2.3.1.

Following the Quick Install Instructions, here’s what I did:

1. Choose and download a distribution kit with the files having the extension (.php3 or .php) depending on the way your web/PHP server interprets those extensions.

I downloaded the file named phpMyAdmin-2.3.1-php.zip since I’ll be using .php extensions and I’m familiar with Zip files.

2. Untar or unzip the distribution (be sure to unzip the subdirectories): tar xzvf phpMyAdmin_x.x.x.tar.gz in your webserver’s document root. If you don’t have direct access to your document root, put the files in a directory on your local machine, and, after step 3, transfer the directory on your web server using, for example, ftp.

I unzipped the file to a directory named phpMyAdmin on my local machine.

3. Open the file config.inc.php3 in your favourite editor and change the values for host, user, password and authentication mode to fit your environment. Also insert the correct value for $cfg[‘PmaAbsoluteUri’]. Have a look at Configuration section for an explanation of all values.

I opened config.inc.php (not config.inc.php3) and jumped down to the Configuration section.

The first thing to note is a warning for Mac users:

php seems not to like Mac end of lines character (“\r”). So ensure you choose the option that allows to use the *nix end of line character (“\n”) in your text editor before registering a script you have modified.

I set a value for $cfgPmaAbsoluteUri that mapped to the directory into which I planned to upload the contents of my phpMyAdmin directory via FTP:

$cfgPmaAbsoluteUri = 'http://path/to/folder/phpmyadmin/';

I entered the values for the host, user, password that had been provided by my hosting service and config as the authentication mode:

Values to be inserted into config.inc.php file

$cfgServers[$i]['host'] = 'your.mysql.server';	// MySQL hostname
$cfgServers[$i]['port'] = '';	// MySQL port - leave blank for default port
$cfgServers[$i]['socket'] = '';	// Path to the socket - leave blank for default socket
$cfgServers[$i]['connect_type'] = 'tcp';	// How to connect to MySQL server ('tcp' or 'socket')
$cfgServers[$i]['controluser'] = '';	// MySQL control user settings (this user must have read-only access to the "mysql/user" and "mysql/db" tables)
$cfgServers[$i]['controlpass'] = '';
$cfgServers[$i]['auth_type'] = 'config';	// Authentication method (config, http or cookie based)?
$cfgServers[$i]['user'] = 'your_username';	// MySQL user
$cfgServers[$i]['password'] = 'your_password';	// MySQL password (only needed with 'config' auth_type)

4. It is recommended that you protect the directory in which you installed phpMyAdmin (unless it’s on a closed intranet, or you wish to use http or cookie authentication), for example with HTTP-AUTH (in a .htaccess file). See the FAQ section for additional information.

I decided to use a .htaccess file, which turned out to be relatively straightforward, as I’ll explain below in the section titled Setting up .htaccess security on the phpmyadmin folder.

I used my FTP client to create a directory named phpmyadmin on the server and copied the files—including .htaccess, .htpasswd and the modified config.inc.php file—from the phpMyAdmin folder on my local machine to the phpmyadmin folder on the server.

5. Open the file <www.your-host.com>/<your-install-dir>/index.php3 in your browser. phpMyAdmin should now display a welcome screen and your databases, or a login dialog if using http or cookie authentication mode.

I entered the URL <www.your-host.com>/phpmyadmin/index.php in the browser and phpMyAdmin displayed the welcome screen, as promised.

Securing the the phpmyadmin folder with .htaccess

Before we go any further, read Dorothea Salo’s Adventures in .htaccess. If you’d like to know even more, you might want to check out this .htaccess Tutorial.

Now that you know the basics, here’s how to password-protect your phpMyAdmin directory. You need to create two files: .htaccess and .htpasswd.

The .htpasswd file contains your username and password in a special username:encryptedpassword format (if you want to have multiple usernames and passwords, these need to occupy one pair per line. Of course, you’ll need an encryption application to create the username/password pair. If your Web hosting provider doesn’t offer such an application, you can use this one from 4WebHelp.

Once you have the username:encryptedpassword combination, use a text editor to save it as .htpasswd.

Then create another document containing the following information:

####
AuthType Basic
AuthName "private"

AuthUserFile /path/to/folder/phpmyadmin/.htpasswd

AuthGroupFile /dev/null

<Limit GET POST PUT>
require valid-user
</Limit>
####

Save this document as .htaccess and include it and .htpasswd in the phpMyAdmin folder for upload to the phpmyadmin directory on your server. (Note that you may need to make a change to the settings in your FTP client in order to see these two files on the server since they are normally hidden under *NIX.)

Now, to access the <www.your-host.com>/phpmyadmin/index.php page, you will have to enter the specified username and password.

Next: fun with exporting and importing.

Permalink