Migrating to MySQL

03/22/2005

After having played with Movable Type and saving all data in the filesystem for a couple of days, it was time to migrate to an SQL database. As a developer for MS SQL Server or Oracle I do know SQL. However, I have never had any experience with MySQL. Although I am not the best man for this job, the installation succeeded.

Installing MySQL

MySQL is a true SQL Server. It is under an Open Source Development and has won rising acceptance in the previous years. It is offered in the so-called "Dual Licensing Model". Suppose you develop an application but you do not want to publish the source code. If you then want to distribute MySQL as part of your own application, you have to pay for MySQL. Otherwise, you may download and use MySQL for free.

I downloaded the current version 4.1. The following components seemed to be interesting

All components will be installed with an MSI package. Therefore, this part of the installation is very easy. After having installed those three components, you should be able to...

  • create a connection to MySQL Server
  • create a new schema
  • create a new user account and give access rights

Prepare MySQL for Movable Type

Movable Type needs a schema of its own in MySQL. Movable Type will create the database objects (tables, views ...) in one of the later steps. However, we have to create the schema manually. Use MySQL Administrator for this. Then create a new user account and give this account access right to the new schema.

Unfortunately, MySQL v4.1 does not fit perfectly with the Perl base libaries that are currently distributed. With version v4.1, MySQL changed the way users are authenticated. Obviously, passwords are stored differently in this version than in previous versions. However, the responsible DBD::mysql module has not yet been updated for handling the altered situation.

If you did not regard my hints, Movable Type will show the following error when accessing the database.

Bad ObjectDriver config: Connection error: Client does not support authentication protocol requested by server; consider upgrading MySQL client

If you should see this message, an error has occurred when communication between Movable Type and MySQL took place. An article on the MySQL homepage describes this problem fully. You can simple avoid the problem by forcing MySQL to save the password in the old format. You can do so by executing some SQL in MySQL Query Browser.

UPDATE mysql.user
SET Password = OLD_PASSWORD('newpwd')
WHERE Host = 'some_host' AND User = 'some_user';

FLUSH PRIVILEGES;

Of course the variables »newpwd«, »some_host« and »some_user« have to be replaced by your specific values.

Afterwards MySQL is properly prepared for Movable Type. So we can continue with the migration to MySQL.

Installing new Perl Components

Some days ago, I described in entry Installation under Windows 2003 that I use ActivePerl from ActiveState. For using MySQL, we have to install additional modules - in exactly the same way that we installed the file-based database.

Unfortunately, Six Apart does not give detailed information about which modules to install. Therefore, this step is difficult for somebody knowing nothing about Perl. By performing the following steps, I achieved a positive result.

  • Open a command prompt
  • Change the current directory to »C:\Programme\ActiveState\Perl\bin«
  • Execute program »ppm.exe«
  • Execute the command »install dbd-mysql«
  • Execute the command »install dbd-mysqlpp«
  • Leave the program with »quit«

Afterwards, Perl and also Movable Type will be able to access a MySQL database.

Configuration of Movable Type

We now have to tell Movable Type that the data is to be stored in a MySQL database. For this, we have to do some changes.

The file »mt.cfg« contains configuration settings. For storing data in the filesystem we had to create a »Datasource« setting. Now we must reset this line to a comment. Put a '#' at the beginning of the line.

# DataSource ./db

The settings needed for accessing the MySQL database are already part of the file »mt.cfg«. You have to uncomment these lines and change them according to your scenario by entering the names of the database and the user.

ObjectDriver DBI::mysql
Database <database_name>
DBUser <database_user>

Finally, the password for the database user has to be entered in the file »mt-db-pass.cgi«.

Preparing the Database

As described in entry Installation under Windows 2003, the program »mt-check.cgi« can be used for testing whether anything is prepared for Movable Type.

When executing »mt-load.cgi«, Movable Type will create the needed objects in the MySQL database. From then on all data (authors, categories, entries ...) will be saved in the MySQL database

Migrating my Data

On Six Apart's homepage you can find a description showing how existing data can be moved from a file-based storage into a MySQL database.

I did not use that description. For a short period, I used two installations of Movable Type. At the same time, there were two virtual directories in MS IIS: one for saving the data in the filesystem and the other for saving the data in MySQL Server. As I had only very few results, these could be moved manually by Copy+Paste.

mgs | 03/22/2005

Feedback is welcome!

What do you think about this entry? Was it interesting or boring? I would like to hear your comments. If the text was helpful, please consider setting a link to http://www.movable-type-weblog.com/.

No spam please!

For protecting this weblog I have installed the MT-Approval Plugin. You have to view a new comment in preview mode, before it is saved on the server. Moreover, I will view your comment manually, before it is published. You can find more information on the subject in the entry Weblog Spamming Basics.

With an active TypeKey session, your comment will be published immediately.

Post a new comment

TypeKey has temporarily been disabled at this location. Please create your comment without using TypeKey or log in from the preview dialog.




Remember Me?