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.

Configure Ghost to Run in Either Development or Production

Ghost blog can be run in multiple modes, two of the most useful modes are development or production (unless you are core ghost developer, you would not be using testing mode).

Each of these modes can be configured differently. In fact, you can use different databases in two different modes. Most often, SQLite3 is used in development mode but MySQL is used in production.

To start Ghost blog in development mode, you can start ghost application similar to normal Node.js webapp by using following command.

$ npm start

To start Ghost blog in production mode, use following command. Please notice the additional parameter passed.

$ npm start --production

If you are using PM2 in your production server, you can start Ghost blog in production mode by using following command.

$ NODE_ENV=production pm2 start index.js --name "Ghost"

Configure Ghost Blog using MySQL

By default Ghost uses sqlite3 database. If you want to use MySQL, this can be done by changing configuration config.js. Please follow changes suggested in below post to use MySQL in Ghost blog.

Ghost blog configuration is as below:

        database: {
            client: 'sqlite3',
            connection: {
                filename: path.join(__dirname, '/content/data/ghost.db')
            },
            debug: false
        },

This needs to be changed to following

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

You can make this change in production section of configuration and keep development section as is. You need to ensure that you start Ghost server in production by following command

npm start --production  

Adding Syntax Highlighter to Ghost Blog

Using newer things has its own set of disadvantages and ghost blogging platform is no exception. Ghost does not have efficient coding markup tools. They do have coding style which looks like below:

code Inline Code Ctrl/⌘ + Shift + K

Disadvantage of this way to syntax highlighter is that

  • It does not differentiate between syntax of different lanaguges.

  • formatting is very basis. When I was searching for better ways of doing syntax highlighting, I came across a faq on ghost blog which suggested to use prism.js for syntax highlighting.

This is one of the simplest tool that I have came across and you can set it up in few minutes.

Step#1
Download prism.js and prism.css from downaload page..
Before downloading these files, you need to select theme and list of languages to which you need this markup.

I have selected Okaidia theme and all languages (you never know which language you might need to refer, specially for a tech blog)

Step#2
Copy prism.js and prism.css files in following folder respectively

content/themes/theme-name/assets/js  
content/themes/theme-name/assets/css  

Step#3
Now we need to include prism.js and prism.css in default.hbs file of whichever theme you are using.
Include prims.css file in head section, just below other CSS files

<link rel="stylesheet" type="text/css" href="{{asset "css/prism.css"}}" />  

Include prism.js file in footer section just before end of body section.

<script type="text/javascript" src="{{asset "js/prism.js"}}"></script>  

This tool has some limitations, as you can see, above line is not rendered properly as html. This is because of double double-quotes. This works well if we remove double-quotes.

<script type="text/javascript" src="{{asset 'js/prism.js'}}"></script>  

Step#4
Deploy these updated files on your ghost server and you are all set. Language markup can be done as below:

  • language-javascript
  • language-sql
  • language-css
  • language-bash
  • language-html

How to add Google Analytics to Ghost Blog

Google Analytics is one of the most popular and powerful tool, following article explains how to use add Google Analytics to Ghost Blog. There are two methods to do so.

Adding Google Analytics using Code injection

Follow these steps and you are all set.
1. Add your website to https://analytics.google.com and get the unique code.
2. Login to Ghost Admin section and go to Settings –> Code Injection

3.Insert your code in this section and click “Save” and you are done.

Adding Google Analytics to Ghost theme

  1. Add your website to https://analytics.google.com and get the unique code.
  2. Go to ..contentthemes
  3. Find default.hbs file and open it in your favorite a text editor. I use brackets.
  4. Add it just below the {{ghost_head}} and above .

    5.Restart the ghost and you are all set.

How to install Ghost blog for free on Heroku

Ghost is a fully open source, hackable platform for building and running a modern online publication. It is a very good alternative to WordPress and blogger and Heroku is a platform as a service (PaaS) that enables developers to build, run, and operate applications entirely in the cloud. Heroku offers a free plan for experiencing the cloud. You can install Ghost on Heroku for free. Following is the step by step guide for installing Ghost Blog on Heroku.

  1. Download Ghost blog from here: https://ghost.org/developers/
  2. Unzip it into required folder.
    Unzipped folder name is “ghost-0.11.3”. You can keep the name same or change it to any other name. I have renamed folder to “mytravels” as this is my blog name.
  3. Initial NPM and Install required NPM modules
F:mytravels>npm init  

Please follow the instructions as it appears on the screen.

F:mytravels>npm install  

This will take some time as it will install all dependencies.
4 . Initiate git repository

F:mytravels>git init  
Initialized empty Git repository in F:/mytravels/.git/  
  1. Login to Heroku by entering your Heroku credentials
F:mytravels>heroku login  
Email: youremail@gmail.com  
Password (typing will be hidden):Logged in as youremail@gmail.com  
  1. Create Heroku application
F:mytravels>heroku create  
Creating app... done, desolate-journey-89792 https://desolate-journey-89792.herokuapp.com/ | https://git.heroku.com/desolate-journey-89792.git  

cross check git configuration using following comments

F:mytravels>git remote -v  
heroku  https://git.heroku.com/desolate-journey-89792.git (fetch)  
heroku  https://git.heroku.com/desolate-journey-89792.git (push)  
  1. Add PostgreSQL database
F:mytravels>heroku addons:add heroku-postgresql:hobby-dev  
Creating heroku-postgresql:hobby-dev on desolate-journey-89792... free  
Database has been created and is available  
 ! This database is empty. If upgrading, you can transfer
 ! data from another database with pg:copy
Created postgresql-slippery-42334 as DATABASE_URL  
Use heroku addons:docs heroku-postgresql to view documentation  
  1. Get database configuration as below:
F:mytravels>heroku config  
=== desolate-journey-89792 Config Vars
DATABASE_URL: postgres://lnfyqtfomdenyr:HwyaPziUdy0bQ1_lE40OKgbZl6@ec2-54-247-95-102.eu-west-1.compute.amazonaws.com:5432/d3t3m6qujeadp4  

These details appear in following order:

postgres://{POSTGRES_USER}:{POSTGRES_PASS}@{POSTGRES_HOST}:{POSTGRES_PORT}/{POSTGRES_DB}  
  1. Rename config.example.js to config.js and add following code
        production: {
        url: 'http://www.example.com',
        mail: {},
        database: {
            client: 'postgres',
            connection: {
                host: 'POSTGRES_HOST',
                user: 'POSTGRES_USER',
                password: 'POSTGRES_PASS',
                database: 'POSTGRES_DB',
                port: '5432'
            },
            debug: false
        },
        server: {
            host: '0.0.0.0',
            port: process.env.PORT
        }
    },
  1. Add your domain name using following command
{gfm-js-extract-pre-1}  
  1. Deploy these local changes to Heroku.
git add .  
git commit -am "first commit"  
git push heroku master