How to migrate Ghost blog from SQLite3 To MySQL

Migrating Ghost blog from SQLite3 to MySQL is fairly simple process. You need to export your posts, configure MySQL for Ghost and lastly, you need to import previously exported posts and you are done. Please follow below mentioned steps.

Step#1: Exporting the Ghost Blog posts.
Login to ghost blog as admin using following link
http://yourblog.com/ghost/ OR http://yourblog.com/admin

Go to labs option and clock on export.

It will create a json file. Keep this file handy as we will import same file after we have configured MySQL.

Step#2: Install MySQL
Please ensure MySQL is installed on your machine. You can follow how to install MySQL on ubuntu.

Step#3: create user for ghost blog
Login to MySQL as root using following command. It will ask for root password.

$ mysql -u root -p

Create database as well as user by using following commands

mysql> create database ghost;  
mysql> CREATE USER 'ghost'@'localhost' IDENTIFIED BY 'password';  

Now add grant user to database

mysql> grant create, delete, insert, select, update, alter,references ON ghost.* TO 'ghost'@'localhost';  

and now flush the newly added access.

mysql> flush privileges;  

Step#4: edit config file to use MySQL
Configure to use production database as mentioned in this post.
Your final config file snippet is as below:

        database: {
            client: 'mysql',
            connection: {
                host: 'localhost',
                user: 'username',
                password: 'password',
                database: 'ghost',
                charset: 'utf8'
            },
            debug: true
        },

Step#4: Now run your blog in production mode as mentioned in post using ghost in production and development mode.

$ npm start --production

Step#5 Importing previously exported blog dump.
Again Login to ghost blog as admin using following link
http://yourblog.com/ghost/ OR http://yourblog.com/admin

Go to labs option and clock on import and use the previously exported file.

This worked well for me, please let me know if you run into any issues…

Recommendations.
Having seen that, I would recommend using SQLite3 as a database someone has said, don’t fix it until its broken and considering SQLite can handle 100k hits per days (which cover most of the websites) there is no need to create extra work for yourself by moving database to MySQL. However you must note that, GhostPro uses MySQL database and ghost officially has dropped support for PostgreSQL.

Please refer to SQLite documentation for further clarification.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.