Joomla!, Wordpress tips for building your site

How to convert to MySQL from SQLite with Wordpress

Published on| November 8th, 2009 | 3 Comments.

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


What do you feel?
it is cumbersome?


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


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


Leave a Reply


Recent Posts


Tag Cloud

execute remove フロントページ 投稿ページ トップページ install WIndows Note Convert META generator 日付 donwload file manage multibyte utf-8 unicode shiftjis euc console サイトマップ 問題 ParmaLink Redirect パーマリンク はみ出る pre テンプレート テーマ タグクラウド マルチランゲージ リダイレクト PHP(タグ) タグ table control HTML(タグ) コマンド 国際化(翻訳) SQLite(タグ) MySQL(タグ) qTranslate プラグイン(タグ) Wordpress(タグ)


Site Description

Joomla!, Wordpress as CMS using a site building, site management, software usage to describe the development tips.

  • はてなブックマークへ追加する
  • Facebookでシェアする
  • twitter でつぶやく
  • Google Plusでシェアする
  • Pocketでシェアする