Ghost content migration SQLITE to MYSQL

John Hartley -

The Ghost team has been doing a great job with blog features and integration, however the documentation for self hosted sites appears to be getting a low priority. Perfectly understandable in "Cloud" or is it now "Mist" time.

I have just migrated my blog content from sqlite3 to mysql. I started with sqlite, in the hope that Ghost might introduce support for PostgreSQL DB, my preferred RDBMS. For now it appears that their is no likelihood of getting PostgreSQL so I have had to move my content into MySQL DB.

Steps required for this include:

StepTaskNotes
1Setup MySQL DBLog into MySQL interpreter and create required role for ghost access and database
In MySQL the user is specific to a given IP address, so depending on whether you are connecting to MySQL via loccally (localhost) or remotely (x.x.x.x/y.y.y.y) where x == IP address, y == subnet mask, or both you might have multiple users.
mysql -u root -p
CREATE USER ghost@<ip/subnet> IDENTIFIED BY '<your-password>' ;
CREATE DATABASE <your-db> CHARACTER SET utf8 ;
GRANT create, delete, drop, insert, select, update, alter,references ON <your-db>.* TO ghost@<ip/subnet> ;    
FLUSH PRIVILEGES ;
EXIT;
2Export PostingNow go to the ghost admin (typically <your-site>/ghost ) select "Labs" functions (its at the bottome left corner). Then "Export Contents" and save the JSON content file locally.
3Reconfigure Ghost DBLog into ghost machine and stop ghost.
Now change the ghost configuration file ("configuration.production.json" or possibly development, typically at /var/www/ghost )to point to MySQL DB.
Here is sample of database configuration for mysql 
...
...
  "database": {
    "client": "mysql",
    "connection": {
      "host": "mysql-server.at.my.domain",
      "user": "your-ghost-db-user",
      "password": "secret-biz",
      "database": "ghost_db_a",
      "charset": "utf8"  <<=== beware this should be changed with new mysql versions } ... ...< pre>

Restart ghost

Note: you can have multiple ghost db's on the same mysql instance, you just need to define each of these seperately as part of step 1.
4Import PostingsFinally back to browser and Labs tab to this time use "Content Import" to load previously exported content
NOTE: Be aware that import adds post to existing set, so in this case you will likely want to delete ghost example postings, after import.

NOTES:

NOTE 1: You can use the same process to move form MySQL to SQLITE, but just ensure you have sqlite install and in step 3, change configuration to sqlite.

NOTE 2: Be aware that this describe how to migrate from one database to another, if you are moving content from one machine to another then you need to make sure you also move the content directory as well (typically /var/www/ghost/content ) as the contents are not stored in database.

NOTE 3: Newer version of MySQL changed the defaults character set and collolation. So setting "charset" to "utf8" will cause problems. See my "Ghost Blog Admin" for details on how to ensure Ghost works with new MySQL version