Find and Replace text in all posts with MySQL command

Developer help
1

My Ghost is installed on the Ubuntu host with MySQL. How to install & setup Ghost on Ubuntu 16.04, 18.04, 20.04 or 22.04

Ghost is in the latest 3 version. MySQL 8.0 version.

How to change url (FIND & REPLACE) in all posts text-content with MySQL in ssh?
Can you help me, provide the right command, please?

2

Hi, my solution is notepad++, find and replace menu with regex pattern.

  1. step is ghost labs page and export content on migration menu.
  2. step is open the exported json file on notepad++ and find-replace with regex pattern.
  3. step import changed json file on the ghost labs page.

image
image691×490 25 KB

I hope it will help you.

3

@Boryl the best approach is probably to use mysqldump to get a text file of your entire database contents, open it in a text editor to use find+replace, then re-import the sql file.

Note that Ghost prior to 4.0 stored URLs in the database as relative so there shouldn’t be any need to replace URLs if your domain has changed but there may be some cleanup needed if you’re changing subdirectories. In Ghost 4.0 onwards urls are stored as __GHOST_URL__/* in the database for faster transforms, those urls are always relative to your configured url so there should be less need (hopefully no need) for manual find+replace when changing subdirectory.

4

Thanks. I do not want to process entire database. Is there any command I can find a string text (URL) in posts content only and replace? I have found this, but do not know the Ghost’s tables and fields where post text content is.

UPDATE `table_name`
 SET `field_name` = replace(same_field_name, 'unwanted_text', 'wanted_text')
5

Anyone knows how to use below template to find and replace text in posts (MySQL)?

UPDATE `table_name` SET `field_name` = replace(same_field_name, 'unwanted_text', 'wanted_text')
6

Hello again Boryl, i’m sorry my old answer. Here you are posts table details.

Your table = posts
Your columns = (html, mobiledoc, plaintext) or somethings.

image
image219×633 6.91 KB

7

html, mobiledoc, plaintext indeed contain post text.

Done. :slight_smile: