Copy one table from one database to another and replace values in one of the columns

This blog is also available in .onion domain. Both blogs have different databases, DB users and passwords, different WordPress configurations, and as a result, they have different data in DB column.

After publishing a new post here, I do not want to manually copy posts from here to there and I want images from b.agilob.net to be available in .onion to avoid any clearnet connections. To do so, the content of each post must be scanned for ‘/‘ and this string must be replaced with ‘http://agiloblulimjeqlq.onion/‘.

To copy all posts from bagilobnet.wp_posts to agiloblulimjeqlq.wp_posts I can querry:

INSERT INTO agiloblulimjeqlq.wp_posts SELECT * FROM bagilobnet.wp_posts;

To avoid having duplicates in the database I added ‘IGNORE’:

INSERT IGNORE INTO agiloblulimjeqlq.wp_posts SELECT * FROM bagilobnet.wp_posts;

To simply replace string / with http://agiloblulimjeqlq.onion/ it’s enough to:

SELECT *, REPLACE(post_content, '/', 'http://agiloblulimjeqlq.onion/') AS post_content FROM bagilobnet.wp_posts;

And now comes the tricky part:

INSERT IGNORE INTO agiloblulimjeqlq.wp_posts
(SELECT 
`ID`, `post_author`, `post_date`, `post_date_gmt`, `post_title`, `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`, `post_mime_type`, `comment_count`, 
REPLACE(post_content, '/', 'http://agiloblulimjeqlq.onion/') AS `post_content` FROM bagilobnet.wp_posts);

would result in tables order mismatch, because `post_content` is 6th in `wp_posts` and this query would place the table as the last one, so to keep the order correct, I had to list all columns from both tables, and then replace `post_content`, which was listed as the last column:

INSERT IGNORE INTO agiloblulimjeqlq.wp_posts
(`ID`, `post_author`, `post_date`, `post_date_gmt`, `post_title`, `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`, `post_mime_type`, `comment_count`, `post_content`)
(SELECT `ID`, `post_author`, `post_date`, `post_date_gmt`, `post_title`, `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`, `post_mime_type`, `comment_count`, 
REPLACE(post_content, '/', 'http://agiloblulimjeqlq.onion/') AS `post_content` FROM bagilobnet.wp_posts);
 
Query OK, 624 ROWS affected (0.03 sec)
Records: 624  Duplicates: 0  Warnings: 0
[Total: 0    Average: 0/5]