Recover ghost blog using content folder/mysql DB

Developer help Self-hosting
1

My ghost blog recently died after the SSD died. Fortunately, the actual content (MySQL and content folders) were stored on a separate HDD RAID (and backup), so were fine. I’ve been running Ghost in Docker (SSD), and unfortunately lost the docker compose file (DB passwords, version info, general config etc.).

I managed to rebuild the config, and compose file, and have manually reset the DB password (and updated the DB to match the “close-enough” version of Ghost) in trying to get the blog back up again. The docker compose points to the content folder and the DB of the “recovered” blog. However, while Ghost starts and I can access the “new blog” it just comes up as a fresh install, rather than the “old blog” (i.e. not really using the existing DB and content).

What can I do to restore the blog to use the existing DB and content?

2

Based on some other posts, I’ve tried an SQL dump of the database (seemed successful) and subsequent “re-import” from the dumped export file - no effect (from the console of the DB container - I’m using dockge to manage my docker compose and containers), still runs as a “new” blog.

3

Based on what you’re describing I am suspecting that you’re not in the correct database.

Ghost is very particular upon startup to check whether the database is in the correct state:

If it isn’t, it will initialise the empty state − which you see as a new Ghost installation.

Any chance there are multiple databases in your MySQL server? Or, if you have them containerised, multiple database containers?

4

Thanks @jannis, I am running ghost containerised, so not sure how there could be multiple databases, unless it was an artifact of how I copied the files from the failed server to the new instance. For reference, this is my (redacted docker compose file):

services:
  ghost:
    image: ghost:5.88.3-alpine # Use the Ghost 5 Alpine image for a smaller footprint
    ports:
      - 2368:2368
    environment:
      url: https://xxx.xxx.xxx.xx
      database__client: mysql
      database__connection__host: db
      database__connection__user: root
      database__connection__password: xxxx-xxxx
      database__connection__database: ghost_db
    volumes:
      - /mnt/apps/ghost-migratory/content:/var/lib/ghost/content
    depends_on:
      - db
    restart: unless-stopped
  db:
    image: mysql:8.4
    environment:
      MYSQL_ROOT_PASSWORD: xxxx-xxxx
      MYSQL_DATABASE: ghost_db
      MYSQL_USER: root
      MYSQL_PASSWORD: xxxx-xxxx
    volumes:
      - /mnt/apps/ghost-migratory/mysql:/var/lib/mysql
    restart: unless-stopped
    command: --mysql-native-password=ON #--skip-grant-tables
networks: {}

How I go about (a) checking if this is the problem (is it something I should be able to see in the boot logs?), and (b) if it is, how best to correct it?

Thank you!

5

I’d start by investigating what databases exist and what’s in them (check with docker ps what the container is actually called):

docker exec -it ghost_db mysql -u root -p

Once you’re in there, run:

SHOW DATABASES;

This will list all databases.

Then have a look at each of them to see what’s in there:

USE <database_name>;
SHOW TABLES;

The old database should have all your posts in posts, so you can list a few like this to check:

SELECT * FROM posts LIMIT 5;
1 Like
6

Okay… that lists six databases:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| ghost              |
| ghost_db           |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

Both the ghost and ghost_db database have a “posts” table, but it looks like the "ghost” database has the actual (original) posts. The “ghost_db” seem to have the “new blog” default content.

This is progress - thank you! Any tips on getting rid or the incorrect “ghost_db” database and making “ghost” into a correct “ghost_db”?

7

The first two are what matters, the others are system databases.

Having a posts table itself is also normal, since these are created on startup by Ghost itself. Having posts in the table is what makes the difference here.

So, you were just in the wrong database :smiley:

Getting rid of the database will work with this:

And to make Ghost use the correct database, just change the credentials in your compose file.

2 Likes
8

Fantastic… thank you so much - I have it back up!

For others reference for how I executed the above in dockge (or those new to mysql commands like me), see below:

To get into mysql from dockge, go into the db terminal (click “>_ Bash”) and type:

mysql -p database_name

The mysql command can be entered as a single line (and you need the first few to use SELECT), so for me to list me posts it was:

USE ghost_db; SELECT * FROM posts LIMIT 5;

To remove the old (wrong) database and “rename” the correct one, I did the following:

Back up (mysql dump) the correct database:

mysqldump -p ghost > ghost_dump_real.sql

Drop both databases from in mysql (be REALLY sure you backed up the correct one above):

mysql -p ghost_db
> DROP DATABASE IF EXISTS ghost
> DROP DATABASE IF EXISTS ghost_db
> exit

Then create the “renamed” database and import from the dump file:

mysqladmin -p create ghost_db
mysql -p ghost_db < ghost_dump_real.sql

After that completed, I restarted the container (may not be needed).

Thanks again @jannis !

1 Like