questions

How to Update db Table Prefix Via Terminal Command

Rate this post

Per these excellent instructions, I can export the tables of a wordpress subsite from a multisite installation. However, I cannot manage to update the table_prefix of the db according to the instructions. Can anyone see what I am doing wrong? or provide another “neat” terminal “one-liner” to update the db table_prefix (see ” #6 ” below)?

synapsis:

Where:
mainsite = domain.localhost.mainsite.com
subsite = domain.com

#0  sudo su


#1  Create space for db export & conversion
mkdir /tmp/domain.localhost.mainsite.com/ 
mkdir /tmp/domain.localhost.mainsite.com/domain.com

#2   change to wp root directory
$ cd /var/www/html/domain.localhost.mainsite.com/


#3  Locate blog_id of relevant site (NEVER # 1, this is main site)
wp site list


#4   Export all the subsite db tables to new  " .sql " file
wp db export --tables=$(wp db tables --url=domain.localhost.mainsite.com --format=csv --skip-plugins --skip-themes --allow-root) /tmp/domain.sql --allow-root

#5 Identify current db prefix

grep "table_prefix" wp-config.php

output: $table_prefix = 'wpb_';

#6 Change the table prefix to match new location db prefix (" 2nodeh_ ")

sed "s#wpb_<blog_id#>_:#2nodeh_#g" /tmp/domain.sql > /tmp/domain.localhost.mainsite.com/domain.com/domain.sql  #This does not seem to work

#7  make " .sql " even smaller, using gzip
gzip /tmp/domain.localhost.mainsite.com/domain.com/domain.sql -c > /tmp/domain.localhost.mainsite.com/domain.com/domain.sql.gz


#8 (move db to safer storage space) upload db to new location and import 
(php my admin fastest way) 

#9 rsync plugins, themes, & uploads to new location
/wprootdirectory/wp-content/plugins/

/wprootdirectory/wp-content/themes/

/wprootdirectory/wp-content/uploads/sites/<blog_id#>/

Whenever I import the db into the new database the original table_prefix is still present. I know how to use mysql to change the table prefix. I am specifically looking for a single terminal command similar to #6 above… Any tips?

 

✔️Solution:

Found the answer, apparently the syntax in item #6 of my question was incorrect, to change a wordpress table prefix using sed, use the following format:

sed --in-place --expression 's#`wp_<blog_id#>_#`<new_prefix>_#g' exported-db.sql

Notes:
1.) The above is specifically for exporting a subsite from a multisite as described on the question, but can be used in other scenarios as well

2.) While temptingly easy, DO NOT, use this method to replace urls if you are moving from “domain.localhost.mainsite.com” to “domain.com” because wordpress serializes urls in the db in some instances, instead use the handy sql query below via the phpmyadmin sql quesy interface, making appropriate url changes:

UPDATE wp_options SET option_value = replace(option_value, 'oldurl.com', 'newurl.com') WHERE option_name = 'home' OR option_name = 'siteurl';UPDATE wp_posts SET guid = replace(guid, 'oldurl.com','newurl.com');UPDATE wp_posts SET post_content = replace(post_content, 'oldurl.com', 'newurl.com');

UPDATE wp_postmeta SET meta_value = replace(meta_value,'oldurl.com','newurl.com');

3.) As always.. BACKUP YOUR DB, BEFORE YOU DO ANY OF THIS

Leave a Reply

Your email address will not be published.

Back to top button