[Ghost] Migrate from sqlite3 to MySQL

31 December 2013 ghost-post, ubuntu, mysql, sqlite3

So you’re running a Ghost blog with the default sqlite3 database, but you changed your mind and now you want to switch to MySQL ? Don’t worry, it shouldn’t take more that 10 minutes.

This tutorial assumes that you are already running a MySQL server and a Ghost blog on the same server, at the address example.com.

This as been tested on Ubuntu 12.04 server and Ghost 0.3.3.

Why ?

There are a lot of reasons to switch from sqlite3 to MySQL. I was personnaly motivated by the followings:

1. I already run a few databases in MySQL
2. I could backup the database without shutting down Ghost
3. I could use phpMyAdmin

BTW, I don’t think performance is a reason to change. Although it’s true that MySQL is faster, it’s well known that sqlite3 is fast enough for a blog.

Step 1 : Export database

Within your web browser, open example.com/ghost/debug/. Then click “Export” and choose a location for your GhostData.json. Keep it until step 6.

Step 2 : Install MySQL module

In order to use MySQL in Ghost, we first need to install the MySQL module for node.js:

cd /var/www/ghost
npm install mysql

Step 3 : Create MySQL user and database

Ghost needs a MySQL user account to connect to the database, so we’ll create a ghost user and grant him with the required priviledges. Then we’ll create the ghost database, because Ghost won’t create it by itself.

mysql -u root -p

CREATE USER ghost@localhost IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON ghost.* TO ghost@localhost;    
CREATE DATABASE ghost CHARACTER SET utf8;
EXIT;

Step 4 : Configure Ghost

Edit /var/www/ghost/config.js and replace the database section by:

database: {
    client: 'mysql',
    connection: {
        host: '127.0.0.1',
        user: 'ghost',
	    password: 'password',
	    database: 'ghost',
	    charset: 'utf8'
    }
},

Step 5 : Start Ghost

Now that Ghost is ready, you can start it:

npm start --production

Then, in your web browser, open example.com and create a user like the first time you created your blog. It doesn’t matter what user name or password you choose since they will be replaced by the original ones.

Step 6 : Import database

With your web browser, open example.com/ghost/debug/. Then select the GhostData.json in your hard drive and click “Import”.

Step 7 : Clean up

First thing you’ll notice is that, in the admin interface, the posts appear in the wrong order. Don’t worry, they appear correctly in the blog. (This bug has been fixed in Ghost 0.4)

Then, you’ll see that the “Welcome to Ghost” post has been created again, like it did when you first created your blog. You need to delete this post, like you’d do for any post.

Finally, if you want to get rid of the sqlite3 database:

rm /var/www/ghost/content/data/ghost.db