Migrating Ghost Installation From SQLite3 To MySQL


If you have a Ghost blog with a SQLite3 backend and want to migrate to MySQL, you can use this simple process:

  1. To get started you will need to have MySQL installed on your server:
  2. Ubuntu

    apt-get install mysql-server

    CentOS

    yum install mysql-server
    <li>Go through the MySQL setup</li>
    
    mysql_secure_installation
    <li>Next log into MySQL database as the root user (note that there is no space between the -p and your password):</li>
    
    mysql -u root -p{your password}
    <li>Create the database for Ghost:</li>
    
    create database ghost;
    <li>Create a user for your Ghost blog:</li>
    
    CREATE USER 'ghost'@'localhost' IDENTIFIED BY 'password';
    <li>Grant Ghost user access to the Ghost database</li>
    
    grant create,delete,insert,select,update,alter ON ghost.* TO 'ghost'@'localhost';
    <li>And now flush the newly granted privileges</li>
    
    flush privileges;
    <li>Now that MySQL is all setup you need to do a backup of your current Ghost content.  Go to <code>yoursite.com/ghost/settings/labs</code> and do an export of your Ghost blog.</li>
    
    
    <li>Now you need to stop Ghost and edit your <code>config.js</code> file.</li>
    

    In your config.js file switch the database stanza, in the production section, to the following:

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

    Replace the database username database user password database name with the specifics you used while setting up MySQL.

    <li>Now start Ghost and go to <code>yoursite.com/ghost</code>.  You will not be logged in anymore and your previous user account will not work.  Create a temporary user to get logged in.  Now go to <code>yoursite.com/ghost/settings/labs</code> and import your previously exported Ghost backup. </li>
    

Now all of your Ghost content has been imported into the MySQL database!