How to convert to MySQL from SQLite with 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

1. 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.

2. 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

3. 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.

4. 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.

5. 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`

6. Delete ‘/WordPress/wp-content/db.php’ , or rename.

Finish!!

What do you feel?
it is cumbersome?

You might also like:

• Note WordPress with SQLite on Windows Server
• How to work WordPress with SQLite
• SQLite Database Browser to download the 100 mark!!
• One single database for multiple WordPress, Joomla! To operate
• How to use your original mysql table in WordPress post.

3 Responses to “How to convert to MySQL from SQLite with WordPress”

1. wordpress后端数据?从sqlite改?mysql?? – 阿泰的菜园
   March 4th, 2013 @ 23:11:49

   […] 解决方法参考 How to convert to MySQL from SQLite with WordPress […]

2. かんべえ
   October 10th, 2014 @ 22:38:35

   ロリポップのコロリポプランでWordPressを運用しています。他のサーバーに移したいのですが、上記の方法で実施すればいいのでしょうか。

3. taro
   October 11th, 2014 @ 01:30:57

   かんべえさん
   コメントありがとうございます。
   SQLiteを使ってらっしゃるのであれば、データベースの移行は可能かと思います。コロリポプランでは、SSH接続などはできないでしょうから、シェルが使えないため、直接コマンドは、難しいかもしれません。その際は、自PCで試してみられることをおすすめします。それから、移行するとスムーズにいけるかと思います。

Leave a Reply