SOS! How to import my backup databse to an existing one in a migration process?

I am in the middle of a migration process from a shared hosting (bluehost) to a VPS server. Actually it is a follow-up question to a previous post for the same site.

So it is a WordPress multisite that I need to migrate to the VPS. So far I did the following:

  1. Backup content and database files from the LIVE site, let’s call them and backup_db.sql.

  2. Created the website using EasyEngine, thanks to EE technology, for multisite subdirectory WorPress installation:
    sudo ee site create --wpsubdir

  3. I am migrating to the same domain name as before

  4. I took note of all credentials in the wp-config-php from the LIVE website to replicate them in the ee-created site, ie. database username and password. This is documented here. Question: do I need to replicate the WordPress admin username and password too like the LIVE website, how to set these in the ee during site creation?

  5. Now I can open the blank website created by ee from my local machine AFTER editing editing the /etc/hosts file. So far so good.

  6. I transferred the and unzipped that correctly replacing the wp-content on the new server. The transfer worked well, since I could see my files in the Media from inside the newly created WordPress site. Question: the LIVE website has the following folder structure: public_html folder which contains misc files, e.g. .htacces, etc + wp-content + wp-includes + wp-admin + cgi-bin. While the ee site structure is the following: var/www/ sort of thing. I wonder what should be transferred specifically to the new server apart from the wp-content and whether I should adapt anything related to the changed path as public_html no longer exists on the new server (target website).

  7. I added the subdirectories of the newly created website network, nothing more.

  8. After having my backup.sql transferred to the new server, I imported the database using this line: mysql -h localhost -u db_name -p db_username < backup.sql

  9. I didn’t need to adapt the wp-config.php on the new server as it is referring to the same LIVE site MySQL database name and username, even password (is that really necessary, can one just rename everything to reflect the new names?!)

After doing all this, I can no longer open the website from my local machine it will direct me automatically to the LIVE website, so my guess the problem appeared after transferring misc files in the public_html path to the new server replacing everything similar there. It could be the .htaccess or others I don’t know. Any idea please?

I migrated using a import export plugin, there are many. but my site is not wpmu but I reckon you should try finding a import export plugin. Then you just create the wordpress blank install with easy-engine commands and then import your old site using a plugin.

this is what I used

PS(if you have a alot of data over 500 something MB just google for a solution to remove the limit, its really easy…)

Thanks for your advise about picking a plugin to do it for you. That wouldn’t be an issue had the WorpPress site been NOT a multisite network. Multisite installation would have you buy a premium plugin which I am reluctant to do. The website is about 380MB size, so not that big so far. Yes, I would consider buying the plugin otherwise.

Finally, I was able to migrate the WPMU multisite network from the shared host to the VPS.

What was missing I had to transfer all of the files from the live website, all what is inside publich_html, that did the trick. I excluded the cgi-bin and any previous backup folders. That’s it!

What was unexpected I couldn’t test the website on my local server (by editinog the etc/hosts file) as before. I don’t know why and as stated in the post before, something wrong had happened, my guess, after transferring ALL files to the destination server. So at that moment I was utterly clueless as whether the migration did happen or not. Therefore, I deleted the new server IP and domain name from the hosts file and pointed the DNS to the new server and added the domain name to the VPS server. The surprise was there: I could still surf the website, but this time sitting on the VPS cloud. A very pleasant experience, and all that without a premium plugin to buy.

Questions still waiting for answers

  1. Why is that at some point in the migration process, specifically after transferring files to the new server, I couldn’t test my website as before on my local machine, instead I was being directed to the live website on the shared host? Can anyone explain please?

  2. When I visit my databse on the new server and view the tables AFTER the database import step, I can see there are two sets tables: one set starts with the prefix wp_ and the other set starts with the prefix that I know from the live website wp_aphw_. I used this command on the new server to view my tables:

sudo mysqlshow <your_db_name>

my guess, is that those tables without the old prefix wp_aphw are the ones used to run the blank website after being created by ee, while those with the prefix belong to the original website. So my question: how to delete those tables without this prefix, i.e., is there a way to drop multiple tables using MySQL commands, those without the prefix wp_apwh? I would be excited to see an answer to these questions.

  1. Seems to be that you missed a step somewhere, i don’t use .com locally, some people use .local but I heard that is not good now, I use .dev currently but that may be going away, too – some people just navigate to the local ip/port. I have not done this set up, though I did read the steps you referred to, etc. As far as creating the user name – a proper backup of sql should include it and after import, it should do away with any default that auto ee set up unless you used those credentials which you implied you did for the database in wpconfig - (which is usually different than the wp user credentials coming from the sql import). If you have wpmu – did you try their site backup solution? When you say vps in the cloud worked – it seems that ee set up on a remote vps cloud server as well as locally on your machine or are you referring to a local install of a vps? I’m confused.

For question # 2 you might install the wordpress plugin adminer on your local install to look at your data tables and drop any you would like. It is basically phpmyadmin inside your wordpress install. that may help you troubleshoot your live data etc.


Custom Database Name

wp_aphw_db-name = True

Custom Database User

wp_aphw_db-user = True

output: Enter The MySQL Database Name [wp_aphw_db-name]: wp_aphw_db-name Enter The MySQL Database Username [wp_aphw_db-user]: wp_aphw_db-user Enter The MySQL Database Password [73kWK4MpRiXjuvo]: database password

thanks for your kind reply.

If you have wpmu – did you try their site backup solution? When you say vps in the cloud worked – it seems that ee set up on a remote vps cloud server as well as locally on your machine or are you referring to a local install of a vps? I’m confused.

I mean WordPress Mutlisite installation, the backup I used was through a plugin BackUpWordPress and it works a treat. The one natively provided by BlueHost was good and free but for some reason I couldn’t download the backup on my local machine. The VPS I meant the instance droplet on a remote server I have on Vultr. I was just testing the website locally on my machine before going live on the VPS droplet.

Thanks for the recommended plugin, I will give it a try, seems my pains using mysql command line are gone.

I see, now. Great!-- yeah, since ee loads multisite automatically i thought you really meant wpmu == lol. I’m glad you got over the sql barrier you experienced – (good for you!) and I hope that plugin helps you work out the duplicate tables.