So today i’m gonna talk about one of the most frustrating things about the WP multisite installation: Exporting a site out of the realm.
full disclosure: I’m not a PHP programmer and i don’t find WP as ‘user-friendly’ as people say it is.
Great, so here is the story:
Lately i had to do this job and i found out that the normal export/import plugins just won’t cut.
3 very annoying things
- WP don’t do relative paths. I understand why but i still don’t like it.
- The plugin won’t export the users.
- Multisite shares the users tables so it’s not easy to export just the site’s users.
So what can one do to accomplish this task without knowing PHP? BASH!!!
the solution requires shell access to the server and a DB user that can access schema DB.
Here is my script, step by step.
#first, some vars:
|
1 2 3 4 |
wpdb=[name-of-wordpress-database] dbuser=[database-super-administrator] dbpass=[dbuser-password] wpdirectory=[wordrpress-root-directory] |
#now, we can initialize the script:
|
1 2 3 4 5 6 |
echo "Blog ID:" read blogid if [ ! -d $blogid ]; then mkdir $blogid fi cd $blogid |
the script asks for the website ID and creates a directory where the script lays to hold the files.
#DB export website tables
|
1 2 3 4 |
mysql -u $dbuser -p$dbpass -N information_schema -e "select table_name from tables where table_schema = '${wpdb}' and table_name like '%wp\_${blogid}\_%'" > tables.txt mysqldump -u $dbuser -p$dbpass $wpdb `cat tables.txt` > wordpress.sql rm tables.txt sed -i "s,wp_${blogid}_,wp_,g" wordpress.sql |
the script exports all the tables except for the users tables into a script file and replace the table name suffix to fit a standalone installation.
# users & usermeta tables
|
1 2 3 4 5 6 7 8 |
userids=($(mysql -u $dbuser -p$dbpass $wpdb -Bse "select distinct user_id from wp_usermeta where meta_key like 'wp\_${blogid}\_%'")) userids_cnt=${#userids[@]} where_users="ID=${userids[0]}" where_meta="user_id=${userids[0]}" for (( i=1; i <${userids_cnt}; i++)) do where_users="$where_users or ID=${userids[$i]}" where_meta="$where_meta or user_id=${userids[$i]}" done mysqldump -u $dbuser -p$dbpass $wpdb wp_users --where "${where_users}" > wp_users.sql mysqldump -u $dbuser -p$dbpass $wpdb wp_usermeta --where "${where_meta}" > wp_usermeta.sql sed -i "s,wp_${blogid}_,wp_,g" wp_usermeta.sql |
now this is the tricky part,
the script takes out the users ID’s that belongs to this website and saves them to an array.
then it iterates over the array and create a ‘WHERE’ clause for each of the tables using their own syntax.
the final part is to export those 2 tables to SQL scripts and replace the table name suffix that is stored inside of wp_usermeta.meta_key column.
#plugins
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
plugins=($(mysql -u $dbuser -p$dbpass $wpdb -Bse "select * from wp_${blogid}_options where option_name='active_plugins'" | grep -Po ':"\K[^/]*')) plugins_cnt=${#plugins[@]} if [ ! -d plugins ]; then mkdir plugins fi for (( p=0; p <${plugins_cnt}; p++)) do if [[ ${plugins[p]} = *hello.php* ]]; then hello=($(echo ${plugins[p]} | awk -F\" '{print $NF}')) cp -r $wpdirectory/wp-content/plugins/${hello} ./plugins/ else cp -r $wpdirectory/wp-content/plugins/${plugins[p]} ./plugins/ fi done |
the scripts creates an array of active plugins and makes a plugins directory inside the website directory it created before.
then it iterates over the array and copies the plugins into the new plugins dir.
one thing to note here: wordpress plugins are stored inside a directory*.
[*except for the hello dolly plugins that for some reason wordpress decided it needs to be in the root plugins directory, so i had to add a special treatment for this.]
#uploads
|
1 2 3 4 |
if [ ! -d uploads ]; then mkdir uploads fi cp -r $wpdirectory/wp-content/uploads/sites/${blogid}/ ./uploads/ |
next is the upload folder. nothing new here just coping the website’s uploads folder as is.
#final
|
1 2 3 4 5 6 7 8 9 10 |
read -r -p "do you want to zip it and remove the folder? [y/N]: " zipit if [[ $zipit =~ ^([yY][eE][sS]|[yY])$ ]] then echo "zipping the site...." cd .. tar -zcf ${blogid}.tar.gz ${blogid}/* rm -rf ${blogid}/ fi echo "Done!" |
the final part is just to wrap it all and get it ready to be shipped to the new server.
I made it open as a question since there is one important thing that got left behind in the script, the website’s Theme.
the Theme part is not in the script since sometimes people uses Child Themes,
and I found out that just copying the needed folders is much more faster then trying to analyze the style.css file and look for the parent theme.
You can add this if you want and also share it with me will be nice.
So that’s it. now you have a folder with all of your website data ready to be imported on the new server.
the steps i took on the new server are those:
download and unzip the new generated files.
move plugins to plugins dir.
create uploads dir and move uploads into it.
import all DB scripts
chmod + chown on the wordpress dir
change hard coded link in theme folder ( ( if any ) )
for the upload folders do: find . -type f -print0 | xargs -0 sed -i ‘s,uploads/sites/[id]/,uploads/,g’ # replace [id] with website ID from multisite.
for the old url with new url do: find . -type f -print0 | xargs -0 sed -i ‘s,[oldurl],[newurl],g’
the DB is now in place but it still holds the full path urls from the old server.
the url’s are stored in serialized arrays so a simple replace command will just break the WPDB.
So since i’m not a PHP Programmer I had to outsource the help of a free code by a company named interconnect/it.
The product is called DATABASE SEARCH AND REPLACE SCRIPT IN PHP,
And I must say, except for the fact the it does the job, I just fell in love with the UI/UX. those guys really know how to make a product.
so how do you use it?
download search and replace and extract it to %wp-root-dir%/sar/
go to http://[NEW_URL]/sar/ and replace the domain name and the old uploads path ( like the replace on the theme folder ).
delete the %wp-root-dir%/sar/ directory so no one else can play around with your DB.
That’s it! all that is left now is to go to the /wp-admin/ section,
follow the update DB procedure and you are DONE!
full script right here:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
#!/bin/bash #SETUP #static wpdb=wordpress dbuser=root dbpass=password wpdirectory=/var/www/html #read input echo "Blog ID:" read blogid #MAIN if [ ! -d $blogid ]; then mkdir $blogid fi cd $blogid #db - blog's related tables echo "creating sql scripts..." mysql -u $dbuser -p$dbpass -N information_schema -e "select table_name from tables where table_schema = '${wpdb}' and table_name like '%wp\_${blogid}\_%'" > tables.txt mysqldump -u $dbuser -p$dbpass $wpdb `cat tables.txt` > wordpress.sql rm tables.txt sed -i "s,wp_${blogid}_,wp_,g" wordpress.sql #db - users & usermeta tables userids=($(mysql -u $dbuser -p$dbpass $wpdb -Bse "select distinct user_id from wp_usermeta where meta_key like 'wp\_${blogid}\_%'")) userids_cnt=${#userids[@]} where_users="ID=${userids[0]}" where_meta="user_id=${userids[0]}" for (( i=1; i <${userids_cnt}; i++)) do where_users="$where_users or ID=${userids[$i]}" where_meta="$where_meta or user_id=${userids[$i]}" done mysqldump -u $dbuser -p$dbpass $wpdb wp_users --where "${where_users}" > wp_users.sql mysqldump -u $dbuser -p$dbpass $wpdb wp_usermeta --where "${where_meta}" > wp_usermeta.sql sed -i "s,wp_${blogid}_,wp_,g" wp_usermeta.sql #plugins echo "copying plugins...." plugins=($(mysql -u $dbuser -p$dbpass $wpdb -Bse "select * from wp_${blogid}_options where option_name='active_plugins'" | grep -Po ':"\K[^/]*')) plugins_cnt=${#plugins[@]} if [ ! -d plugins ]; then mkdir plugins fi for (( p=0; p <${plugins_cnt}; p++)) do if [[ ${plugins[p]} = *hello.php* ]]; then hello=($(echo ${plugins[p]} | awk -F\" '{print $NF}')) cp -r $wpdirectory/wp-content/plugins/${hello} ./plugins/ else cp -r $wpdirectory/wp-content/plugins/${plugins[p]} ./plugins/ fi done #uploads echo "copying upload folder...." if [ ! -d uploads ]; then mkdir uploads fi cp -r $wpdirectory/wp-content/uploads/sites/${blogid}/ ./uploads/ #zip it and remove! read -r -p "do you want to zip it and remove the folder? [y/N]: " zipit if [[ $zipit =~ ^([yY][eE][sS]|[yY])$ ]] then echo "zipping the site...." cd .. tar -zcf ${blogid}.tar.gz ${blogid}/* rm -rf ${blogid}/ fi echo "Done!" |