What to do when Ghost goes wrong (ie you loose a password, upgrade fails etc). A few tips on some common Ghost Site Adminstration tasks.

Status: Updated Apr 2021 following Ghost 3.x to 4.x update


Ghost 3.x -> 4.x update failure - missing columns and tables

In April 2021 I decided to try to do update on one of my 3.x Ghost instances to 4.x. This did not go well... I followed the requested stops of:

  1. Updated Ghost CLI: "sudo npm install -g ghost-cli@latest" - this would not "stick" unless I added the -g flag  "sudo npm install -g ghost-cli@latest -g"
  2. Updated 3.x to latest verson: "ghost update v3"
  3. Then did further update step to 4.X result was failure to migrate the database.

Digging into this the issues I had were:

  1. Character set collation problems (as per section below "Preparation for MySQL 8 Collation Change")
  2. Missing column in table - "members_stripe_customers_subscriptions" which resulted in simillar problem to that described in section "Ghost Update: Missing Columns in members Table" below.
  3. Completely missing table: "members_products", which required looking at the github code to find the table definition and create this manually.

Here is the SQL comands log for fixing these problems 1, 2 & 3:

sudo mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 363
Server version: 8.0.23-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use ghost_story ;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA LIKE 'ghost_XXXX' ;
+---------------+--------------+----------------------------------------+----------------------------+--------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME                             | COLUMN_NAME                | COLLATION_NAME     |
+---------------+--------------+----------------------------------------+----------------------------+--------------------+
| def           | ghost_XXXXX  | actions                                | actor_id                   | utf8mb4_general_ci |
| def           | ghost_XXXXX  | actions                                | actor_type                 | utf8mb4_general_ci |
...
...
...
| def           | ghost_XXXXX  | webhooks                               | updated_at                 | NULL               |
| def           | ghost_XXXXX  | webhooks                               | updated_by                 | utf8mb4_general_ci |
+---------------+--------------+----------------------------------------+----------------------------+--------------------+
345 rows in set (0.01 sec)

---
--- Now find the tables which have wrong collation, as per section: "Preparation for MySQL 8 Collation Change"
--- And fix as detailed
---

--- Next is to add column and missing table:
---
mysql> alter table members_stripe_customers_subscriptions add column stripe_price_id varchar(255) not null default '' unique key ;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table members_stripe_customers_subscriptions add key (stripe_price_id) ;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

---
--- Add missing table..
---
mysql> create table members_products (id varchar(24) not null primary key, member_id varchar(24) not null references members(id) on delete cascade, product_id varchar(24) not null references products(id) on delete cascade, sort_order integer unsigned not null default 0) ;
Query OK, 0 rows affected (0.03 sec)

These updates resulted in working 4.X Ghost Blog now working again.

Ghost Update: Missing Columns in members Table

In mid December 2020 doing update to 3.40.1 I found that in addition to the utf8mb4 Collation bug, I also hit bug with the member table not having all colums defined. Error on starting Ghost was:

...
...
...
2020-12-16 03:33:17] INFO Populating email counts on members
[2020-12-16 03:33:17] WARN Removing members.email_opened_count column
[2020-12-16 03:33:17] INFO Removing members.email_count column
[2020-12-16 03:33:17] INFO Removing members.email_open_rate column
[2020-12-16 03:33:18] ERROR update `members` set `email_count` = (SELECT COUNT(id) FROM email_recipients WHERE email_recipients.member_id = members.id), `email_opened_count` = (SELECT COUNT(id) FROM email_recipients WHERE email_recipients.member_id = members.id AND email_recipients.opened_at IS NOT NULL) - ER_BAD_FIELD_ERROR: Unknown column 'email_opened_count' in 'field list'

update `members` set `email_count` = (SELECT COUNT(id) FROM email_recipients WHERE email_recipients.member_id = members.id), `email_opened_count` = (SELECT COUNT(id) FROM email_recipients WHERE email_recipients.member_id = members.id AND email_recipients.opened_at IS NOT NULL) - ER_BAD_FIELD_ERROR: Unknown column 'email_opened_count' in 'field list'

{"config":{"transaction":true},"name":"03-populate-members-email-counts.js"}
"Error occurred while executing the following migration: 03-populate-members-email-counts.js"

Error ID:
    300

Error Code: 
    ER_BAD_FIELD_ERROR

----------------------------------------

MigrationScriptError: update `members` set `email_count` = (SELECT COUNT(id) FROM email_recipients WHERE email_recipients.member_id = members.id), `email_opened_count` = (SELECT COUNT(id) FROM email_recipients WHERE email_recipients.member_id = members.id AND email_recipients.opened_at IS NOT NULL) - ER_BAD_FIELD_ERROR: Unknown column 'email_opened_count' in 'field list'
    at MigrationScriptError.KnexMigrateError (/var/www/ghost/versions/3.40.1/node_modules/knex-migrator/lib/errors.js:7:26)
    at new MigrationScriptError (/var/www/ghost/versions/3.40.1/node_modules/knex-migrator/lib/errors.js:25:26)
    at /var/www/ghost/versions/3.40.1/node_modules/knex-migrator/lib/index.js:1055:19
    at processTicksAndRejections (internal/process/task_queues.js:97:5)

Error: ER_BAD_FIELD_ERROR: Unknown column 'email_opened_count' in 'field list'
    at Query.Sequence._packetToError (/var/www/ghost/versions/3.40.1/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/var/www/ghost/versions/3.40.1/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (/var/www/ghost/versions/3.40.1/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/var/www/ghost/versions/3.40.1/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/var/www/ghost/versions/3.40.1/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/var/www/ghost/versions/3.40.1/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/var/www/ghost/versions/3.40.1/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/var/www/ghost/versions/3.40.1/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (events.js:314:20)
    at Socket.EventEmitter.emit (domain.js:483:12)
    at addChunk (_stream_readable.js:297:12)
    at readableAddChunk (_stream_readable.js:272:9)
    at Socket.Readable.push (_stream_readable.js:213:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
    --------------------
    at Protocol._enqueue (/var/www/ghost/versions/3.40.1/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Connection.query (/var/www/ghost/versions/3.40.1/node_modules/mysql/lib/Connection.js:198:25)
    at /var/www/ghost/versions/3.40.1/node_modules/knex-migrator/node_modules/knex/lib/dialects/mysql/index.js:135:18
    at new Promise (<anonymous>)
    at Client_MySQL._query (/var/www/ghost/versions/3.40.1/node_modules/knex-migrator/node_modules/knex/lib/dialects/mysql/index.js:129:12)
    at Client_MySQL.query (/var/www/ghost/versions/3.40.1/node_modules/knex-migrator/node_modules/knex/lib/client.js:169:17)
    at /var/www/ghost/versions/3.40.1/node_modules/knex-migrator/node_modules/knex/lib/transaction.js:321:24
    at new Promise (<anonymous>)
    at Client_MySQL.trxClient.query (/var/www/ghost/versions/3.40.1/node_modules/knex-migrator/node_modules/knex/lib/transaction.js:316:12)
    at Runner.query (/var/www/ghost/versions/3.40.1/node_modules/knex-migrator/node_modules/knex/lib/runner.js:151:36)
    at /var/www/ghost/versions/3.40.1/node_modules/knex-migrator/node_modules/knex/lib/runner.js:40:23
    at /var/www/ghost/versions/3.40.1/node_modules/knex-migrator/node_modules/knex/lib/runner.js:277:24
    at processTicksAndRejections (internal/process/task_queues.js:97:5)
    at async up (/var/www/ghost/versions/3.40.1/core/server/data/migrations/versions/3.40/03-populate-members-email-counts.js:7:9)
    at async Object.up (/var/www/ghost/versions/3.40.1/core/server/data/migrations/utils.js:236:13)

To looking the member table I found that for some reason not all the columns had been created:

mysql> show columns in members;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| id          | varchar(24)   | NO   | PRI | NULL    |       |
| email       | varchar(191)  | NO   | UNI | NULL    |       |
| created_at  | datetime      | NO   |     | NULL    |       |
| created_by  | varchar(24)   | NO   |     | NULL    |       |
| updated_at  | datetime      | YES  |     | NULL    |       |
| updated_by  | varchar(24)   | YES  |     | NULL    |       |
| name        | varchar(191)  | YES  |     | NULL    |       |
| note        | varchar(2000) | YES  |     | NULL    |       |
| subscribed  | tinyint(1)    | YES  |     | 1       |       |
| uuid        | varchar(36)   | YES  | UNI | NULL    |       |
| geolocation | varchar(2000) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
11 rows in set (0.00 sec)   <<==== TABLE has 11 Columns defined

mysql> show columns in members ;
+--------------------+---------------+------+-----+---------+-------+
| Field              | Type          | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| id                 | varchar(24)   | NO   | PRI | NULL    |       |
| email              | varchar(191)  | NO   | UNI | NULL    |       |
| created_at         | datetime      | NO   |     | NULL    |       |
| created_by         | varchar(24)   | NO   |     | NULL    |       |
| updated_at         | datetime      | YES  |     | NULL    |       |
| updated_by         | varchar(24)   | YES  |     | NULL    |       |
| name               | varchar(191)  | YES  |     | NULL    |       |
| note               | varchar(2000) | YES  |     | NULL    |       |
| subscribed         | tinyint(1)    | YES  |     | 1       |       |
| uuid               | varchar(36)   | YES  | UNI | NULL    |       |
| geolocation        | varchar(2000) | YES  |     | NULL    |       |
| email_open_rate    | int unsigned  | YES  | MUL | NULL    |       |
| email_count        | int unsigned  | NO   |     | 0       |       |
| email_opened_count | int unsigned  | NO   |     | 0       |       |
+--------------------+---------------+------+-----+---------+-------+
14 rows in set (0.01 sec)   <<==== Working Ghost sample has 14 Columns

Work around was to manually add the missing colums into the table and rerun ghost;

mysql> alter table members add column email_open_rate int unsigned;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table members add column email_count  int unsigned not null ;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table members add column email_opened_count  int unsigned not null
;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

NOTE: the working Ghost table definition also has index on the email_open_rate column, so this should also be added.


Preparation for MySQL 8 Collation Change

MySQL 8.0 changed to defalt 'utf8mb4' Character Set collation to 'utf8mb4_0900_ai_ci'. Currently Ghost uses 'utf8mb4' with 'utf8mb4_general_ci'.

The result is that when new tables get created they could be using incompatible collation sets, which result in SQL error (see below "ER_CANT_DROP_FIELD_OR_KEY" for example).

So to avoid surprises there are come things you can do to make sure your database does not get changed during either an OS , MySQL or Ghost Update.

  • Check the Collation for all table in ghost database - "SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS ;" . If you have tables with "utf8mb4_0900_ai_ci" then you are likely to run into problems, so do an update on the problems tables Collation settings (see next).
  • Look for cases where have "utf8mb4_0900_ai_ci" collation - "SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS where COLLATION_NAME like 'utf8mb4_0900_ai_ci';"
  • Update Collation for tables that have already have incorrect collation - "alter table <YOUR-TABLE> convert to character set utf8mb4 collate utf8mb4_general_ci ;"

To test I have done update on Ghost from 3.27.0 -> 3.40.1 and found that even if I do an update of the Ghost database to change the default collation, that the update still failed. In this case with a Migration lock failure:

[2020-12-16 00:26:25] ERROR Migration lock was never released or currently a migration is running.

Migration lock was never released or currently a migration is running.

"If you are sure no migration is running, check your data and if your database is in a broken state, you could run `knex-migrator rollback`."

Error ID:
    500

----------------------------------------

The offending table was again (see below): "email_recipients" and the fix was to:

  1. change the collation on this table
  2. manually update the migraton lock: "UPDATE migrations_lock set locked=0 where lock_key='km01';" and
  3. then restart ghost (I always run ghost in foreground when bugs appear): "cd /var/www/ghost" & "ghost run".

NOTE: I have logged bug on this as update fails consistently and for the same reason.


Ghost 3.40.1 Update Error: ER_CANT_DROP_FIELD_OR_KEY: Can't DROP 'email_recipients_email_id_foreign'; check that column/key exists

December 2020, just before Christmas, I have not done a Ghost update for some time so thought I should check my ghost servers and did a ghost update from 3.27 -> 3.40.1.

This did not go well with ghost crashing on restart with an SQL error: ER_CANT_DROP_FIELD_OR_KEY.

To help diagnose the problem I had to do some checking on the ghost database and reading up on the use of Foreign Keys in SQL database, Database Character Set and Collaction orders: The character set and collation (sorting) order can be set on a Database, Table and Column level. To see what character set is being use at each of these levels do:

  • Database - "select * from information_schema.schemata where schema_name like '<YOUR-DB>' ;"
  • Database (to alter this) - "alter database <YOUR-DB> character set utf8mb4 collate utf8mb4_general_ci ;"
  • Tables - "show full columns from <YOUR-TABLE> ;"
  • Tables (to alter this) - "alter table <YOUR-TABLE> convert to character set utf8mb4 collate utf8mb4_general_ci ;"

The reason for the need to look at the Character Sets and Collating is because as part of doing update I also did an update on Ubuntu (via apt update / upgrade) and this appears to have changed the default character set and collating. The result is that new tables where being created with collating order of: 'utf8mb4_0900_ai_ci' and the old ones had a collating order of: 'utf8mb4_general_ci'.

This in-turn results in a failure to create a foreign key as part of the database update, which in turn result in failure in the subsequent drop of the foriegn key, which results in the error reporting:

...
...
...
2020-12-15 09:25:06] ERROR alter table `email_recipients` drop foreign key `email_recipients_email_id_foreign` - ER_CANT_DROP_FIELD_OR_KEY: Can't DROP 'email_recipients_email_id_foreign'; check that column/key exists

alter table `email_recipients` drop foreign key `email_recipients_email_id_foreign` - ER_CANT_DROP_FIELD_OR_KEY: Can't DROP 'email_recipients_email_id_foreign'; check that column/key exists

"OuterError: update `members` set `email_count` = (SELECT COUNT(id) FROM email_recipients WHERE email_recipients.member_id = members.id), `email_opened_count` = (SELECT COUNT(id) FROM email_recipients WHERE email_recipients.member_id = members.id AND email_recipients.opened_at IS NOT NULL) - ER_CANT_AGGREGATE_2COLLATIONS: Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='"

Error ID:
    400

Error Code: 
    ER_CANT_DROP_FIELD_OR_KEY

----------------------------------------
...
...
...

So fix is to ensure you database and tables are all using the same Character Set amd Collation Order. For ghost this should be: utf8mb4 / utf8mb4_general_ci.


Ghost Error: Can't connect to the bootstrap socket (localhost 8000) ECONNREFUSED

Following upgrade of Ubuntu from 18.04 (LTS) -> 20.04 (LTS) which disables the nodejs repository, I did a new upate to nodejs 12.x (previous recommended version was 10.x:

$ curl -sL https://deb.nodesource.com/setup_12.x | sudo -E bash
sudo apt-get install -y nodejs
---
--- Followed by ghost-cli update and ghost update
$ sudo npm install ghost-cli@latest -g
$ cd /var/www/ghost
$ ghost update
$ ghost start

However ghost fails to start.. and I get the error:

Can't connect to the bootstrap socket (localhost 8000) ECONNREFUSED

The you can see this by running: ghost run

Problems is that node executable changes from: "nodejs" to "node" so systemd script fails:

[Unit]
Description=Ghost systemd service for blog: localhost
Documentation=https://docs.ghost.org

[Service]
Type=simple
WorkingDirectory=/var/www/ghost
User=1001
Environment="NODE_ENV=production"
ExecStart=/usr/bin/nodejs /usr/bin/ghost run
Restart=always

[Install]
WantedBy=multi-user.target

Simply:

  • change "ExecStart" line to use /usr/bin/node,
  • do "sudo systemctl daemon reload"
  • ghost should now start correctly

Ghost V2 to V3 Database Migration Error

The Ghost team has released Ghost V3, so like other I have upgraded some of my ghost servers to V3.

In doing this I came across this error on doing inplace "ghost update" comment:

 $ ghost update
+ sudo systemctl is-active ghost_ghost-BLAH-com
✔ Checking system Node.js version
✔ Ensuring user is not logged in as ghost user
✔ Checking if logged in user is directory owner
✔ Checking current folder permissions
✔ Checking folder permissions
✔ Checking file permissions
✔ Checking content folder ownership
✔ Checking memory availability
✔ Checking for available migrations
✔ Checking for latest Ghost version
✔ Downloading and updating Ghost to v3.0.0


Checking theme compatibility for Ghost 3.0.0

✔ Your theme is compatible.

You can also check theme compatibility at https://gscan.ghost.org

? Are you sure you want to proceed with migrating to Ghost 3.0.0? Yes
✔ Updating to a major version
+ sudo systemctl is-active ghost_ghost-BLAH-com
+ sudo systemctl stop ghost_ghost-BLAH-com
✔ Stopping Ghost
✔ Linking latest Ghost and recording versions
+ sudo systemctl is-active ghost_ghost-BLAH-com
☱ Stopping Ghost✔ Ensuring user is not logged in as ghost user
✔ Checking if logged in user is directory owner
☲ Restarting Ghost✔ Checking current folder permissions
+ sudo systemctl is-active ghost_ghost-BLAH-com
✔ Validating config
✔ Checking folder permissions
✔ Checking file permissions
✔ Checking content folder ownership
+ sudo systemctl start ghost_ghost-BLAH-com
☲ Restarting Ghost+ sudo systemctl stop ghost_ghost-BLAH-com
✖ Restarting Ghost
A GhostError occurred.

Message: drop table if exists `members_stripe_customers_subscriptions` - ER_TABLEACCESS_DENIED_ERROR: DROP command denied to user 'GHOST_USER'@'GHOST_SERVER.com' for table 'members_stripe_customers_subscriptions'
Suggestion: journalctl -u ghost_ghost-BLAH-com -n 50

Debug Information:
    OS: Ubuntu, v19.04
    Node Version: v10.17.0
    Ghost-CLI Version: 1.12.0
    Environment: production
    Command: 'ghost update'

Additional log info available in: /home/ME/.ghost/logs/ghost-cli-debug-2019-10-26T03_10_33_463Z.log

Try running ghost doctor to check your system for known issues.

You can always refer to https://ghost.org/docs/api/ghost-cli/ for troubleshooting.



? Unable to upgrade Ghost from v2.31.1 to v3.0.0. Would you like to revert back to v2.31.1? Yes
+ sudo systemctl is-active ghost_ghost-BLAH-com
+ sudo systemctl reset-failed ghost_ghost-BLAH-com
✔ Checking system Node.js version
✔ Ensuring user is not logged in as ghost user
✔ Checking if logged in user is directory owner
✔ Checking current folder permissions
✔ Checking folder permissions
✔ Checking file permissions
✔ Checking content folder ownership
✔ Checking for available migrations
✔ Checking for latest Ghost version
ℹ Downloading and updating Ghost [skipped]
+ sudo /var/www/ghost/current/node_modules/.bin/knex-migrator-rollback --force --v 2.31.1 --mgpath /var/www/ghost/current
✖ Rolling back database migrations
A GhostError occurred.

Message: The database migration in Ghost encountered an error.
Help: https://ghost.org/faq/upgrade-to-ghost-2-0/#what-to-do-when-an-upgrade-fails
Suggestion: ghost update --rollback

Debug Information:
    OS: Ubuntu, v19.04
    Node Version: v10.17.0
    Ghost-CLI Version: 1.12.0
    Environment: production
    Command: 'ghost update'

Additional log info available in: /home/ME/.ghost/logs/ghost-cli-debug-2019-10-26T03_12_26_245Z.log

Try running ghost doctor to check your system for known issues.

You can always refer to https://ghost.org/docs/api/ghost-cli/ for troubleshooting.

As per prior posting I converted by sqlite3 ghost to MYSQL and in doing this set permission on database as: GRANT create, delete, insert, select, update, alter,references ON <your-db>.* TO GHOST_DB_USER@&lt;ip/subnet&gt;

Error is pretty clear and consistent with fact that I did not grant "drop" permissions, so fix is to add drop permissions to ghost db acount use:

mysql> show grants for 'USER'@'XXX.XXX.XXX.X/255.255.255.X' ;
mysql> GRANT DROP ON `GHOST_DB`.* TO 'USER'@'XXX.XXX.XXX.0/255.255.255.X' ;
-- or if you have local mysql
mysql> show grants for 'USER'@'localhost';
mysql> GRANT DROP ON `GHOST_DB`.* TO 'USER'@'localhost' ;
--- and flush
mysql> FLUSH PRIVILEGES;

Password Reset

This is likely the most common problem. You have forgotten ghost admin password and password reset mail is not avaiable or working, so you cannot use ghost tools to fix things. The reset requires you to do three things:

StepTaskNotes
1Get Database DetailsGet the database connection information from ghost by looking at config.production.json (or development) in Ghost root directory (typically /var/www/ghost). Config item is: "database".
2Get DB User DetailsLog into your MySQL host (may or may not be the same machine that ghost is on depending on your configuration).
sudo mysql -u root -p
mysql> use <db-name> ; <== (DB from step 1)
mysql> select * from users ;
3Reset PasswordGhost uses BCrypt Hash for password protection, so simply use known BCryptencrytped password "password" as reset password.
UPDATE users SET password='$2a$10$BQToDNdBtBKCvnrTmMi5m.NK.7i6Qx7YASs.jTkE86I5zqxzE8klC' WHERE email = '<email-as-per-step-2>' ;
4Reset via GhostNow just login to Ghost with use id as per Step 2/3 with password "password" (no quotes).
No use Ghost tools to reset the password.

These instruction also work with sqlite3 database, but rather than going into sql interpreter, you should change directory to <ghost-root>/content/data and then do:

sudo sqlite3 ghost.db

The is no need for the "use <DB>" step with sqlite3.

NOTE: If you have installed an MySQL DB Admin tools then you can do all this quickly and simplely, or just write a script...


Ghost Database Content Migration

Migrating from sqlite (development) to MySQL (production configuration) is documented here.


Reference & Links:

Acknowledgement: Ghost Writer Image from: Sue Tooth - Writing & Editing Services.

MySQL & Unicode - briefing on Unicode Charater Sets & Collation

MySQL / Ghost Unicode Collation Bug - the change in MySQL default character set collation results in failure during migration at restart.