How to convert to MySQL from SQLite with Wordpress

Published on| November 8th, 2009 | No Comment.

Categories:WordPress, Preferences | Tags: Convert, MySQL, SQLite, WordPress

The post “How to work WordPress with SQLite“, “Note WordPress with SQLite on Windows Server” was written before.
In the previous posts, I described how to install WordPress with SQLite.

If your site has not many access , you will think nothing even if your site works by WordPress with SQLite.
However, when your site has many access, you may think to convert from SQLite DB to MySQL DB. Because you feel limit of SQLite DB by a lot of accesses.
When you convert DB, you will feel troublesome it.

If you have environment to work the MySQL and you think that want to work WordPress with SQLite, I will advice you should use MySQL.

In this post, as the author, but for now, WordPress is the SQLite try to describe who responded to install.

This post will help you that installed WordPress with SQLite like me when you want to convert from SQLite to MySQL.

How to convert from SQLite to MySQL

First, you should backup of your SQLite DB file.
If you have installed with default settings, you will find its file on a following directory.

/WordPress/wp-content/database/MyBlog.sqlite

‘/WordPress’ is directory that WordPress is installed.
Next, you should dump to SQL Text file from SQLite DB file.
In Windows, you can do it very easy by getting ‘sqlite3.exe’ from web site.
For example, you will get SQL Text file by entering following commands on ‘sqlite3.exe’ DOS window.
```
 > sqlite3.exe MyBlog.sqlite
   sqlite> .output wordpress.sql
   sqlite> .dump
```
Next, you should edit a SQL Text file.
Clear all lines include ‘CREATE INDEX …’.
Clear all lines include ‘CREATE TABLE modTimes …’.
Replace all lines include ‘CREATE TABLE wp_xxxx …’ to ‘CREATE TABLE wp_xxxx …’ in ‘/WordPress/wp-admin/includes/schema.php’.

Add the following code to top line in SQL Text file.
set character_set_client = utf8;

Save edited SQL Text file.
Next, you will run edited SQL Text file on MySQL.
```
   % mysql -hvvvv -uxxxx -pyyyyy zzzz
   mysql > set character_set_client = utf8;
   mysql > source wordpress.sql
```
vvvv : host name (localhost is optional), xxxx : user name, yyyyy : password, zzzz : DB name.

Next, you will edit ‘/WordPress/wp-config.php’.
You should set ‘xxxxx’, ‘yyyyy’, ‘zzzzz’, ‘aaaaa’ according to your environment in following code.

    /** The name of the database for WordPress */
    define('DB_NAME', 'xxxxx');
 
    /** MySQL database username */
    define('DB_USER', 'yyyyy');
 
    /** MySQL database password */
    define('DB_PASSWORD', 'zzzzzzzzz);
 
    /** MySQL hostname */
    define('DB_HOST', 'aaaaa');
    //define('DB_HOST', 'localhost');
 
    /** Database Charset to use in creating database tables. */
    define('DB_CHARSET', 'utf8');
 
    /** The Database Collate type. Don't change this if in doubt. */
    define('DB_COLLATE', '');
    //define('DB_TYPE', 'sqlite');	//mysql or sqlite`
    define('DB_TYPE', 'mysql');	//mysql or sqlite`