Importing mysql file to domain not yet on Dreamhost DNS

wordpress

#1

Scenario: Migration of Wordpress site from my old ISP to Dreamhost. DNS is not yet moved to Dreamhost. Need to know how to dump and load MySQL data (see question at the end)

From test site on Dreamhost:
mysql.example.dreamhosters.com

To my main domain*:
mysql.example.com

  • NOTE:
  • As many of you know, since DNS has not been moved (from old ISP to Dreamhost), the “domain” is really just a folder on Dreamhost server (something like “/home/example.com”).

Since DNS is not moved, I can’t get to:
https://mysql.example.com (again, since this will redirect it to my old ISP).

I have already dumped the mysql file from https://mysql.example.dreamhosters.com

Question:
How do I upload it to https://mysql.example.com? Can I do this from ssh session on Unix CLI by logging to MySql interface?

thanks for the responses!


#2

Both hostnames (mysql.example.dreamhosters.com and mysql.example.com) refer to the same MySQL server, so there’s nothing to move. If you were to switch your DNS over and connect to phpMyAdmin on mysql.example.com, you would see all of the exact same databases already there.


#3

hi Andrew,

Maybe I did not explain it clearly or don’t understand the “moving a new and active domain to Dreamhost” clearly. I would reason that most customers, with an active website, when moving to Dreamhost has gone through this same process to some degree. i.e.

  1. You want to create a test site on Dreamhost
    If your domain is example.com, than you would create it as: example.dreamhosters.com and relevant MySQL database which can be accessed via mysql.dreamhosters.com (right?)

For a CMS-oriented website, such as Wordpress, this will allow you to test it, make sure the page loads are okay, and that everything looks correct before you move your real domain.

At this point, your domain is still hosted by old ISP. But a duplicate test copy is up on Dreamhosters.

  1. You want to test the domain (while dns is still not moved) on Dreamhost
    From the SSH session, you can see that now you have two folders sitting parallel to each other, which is something like:
    /home/example.dreamhosters.com
    /home/example.com (this is your real domain once dns is moved)

For this also, you have created a database (Goodies -> mysql databases) and you have created a hostname such as: mysql.example.com

Dreamhot, however, allows you to reach to “example.com” (on /home/example.com) even when it is hosted at old ISP. The way you do it is …

  • add example.com in hosts file on your PC/Linux host at home and point the domain to DNS IP of Dreamhost
    (recall, Unix checks /etc/nsswitch.conf where usually it is listed as “hosts dns”. If it is like that, hosts translates to /etc/hosts and dns translates to /etc/resolv.conf. In other words, it will look into hosts file before it gets to DNS stub file)

On my Linux host at home, I can do this with no problem. i.e. in the browser I can pull “example.com” from Dreamhost and not from my real domain from old ISP.

Anyways, in case someone else was stuck at this point, here is how I dumped and loaded MySQL from my database on example.dreamhosters.com to my database on example.com (again, the one that is hosted on Dreamhost. The actual domain on old ISP is untouched).

Source: http://wiki.dreamhost.com/Migrate_MySQL
Command:
mysqldump -h oldhost -u oldusername -poldpassword olddbname | mysql -h newhost -u newusername -pnewpassword newdbname

In this case, the “oldhost” and “newhost” are the same host - the name of Dreamhost server your domain is being hosted on.


#4

Any database you create for any domain within the account is available to any other domain. Your browser does not need to know where mysql.blah.tld is - that’s just a name that the WP script to uses to connect internally (like localhost).

  1. You don’t need 2 mysql hostnames or 2 databases.

  2. When using the HOSTS hack you don’t even need a dreamhosters sub-domain.


#5

hi sXi,

Thanks for clarifying point 1!

Point 2 was clear to me; I am trying to keep both the
example.dreamhosters.com (for testing - such as downloading new plugins etc to ensure it does not mess up the format of the main website. So data here will be a subset of the example.com) and example.com.

Question:
Let’s assume that I keep both the databases (accessible either from mysql.example.dreamhosters.com, mysql.example.com, or Unix CLI that I shared in this same thread), what are some of the next steps:

a. Do I need to download and run “search and replace” tool or some plugin that does similar thing, by replacing “example.dreamhosters.com” with “example.com” (which will run on the db tables and on static php files across the entire /home/example.com folder structure?

b. manually and carefully change wp-config.php - adding the right db name/password etc.?

c. Any other points/caveats/gotchas I need to be aware of?

Please share a link if it is already explained somewhere.

Thanks in advance to everyone responding!


#6

What you have outlined appears to be a correct method in your case. There are more than one ways to skin a cat, but I’d do exactly what you’ve listed :wink:


#7

Thanks sXi !!

For the benefit of anyone reading the thread, I was able to make the site work last evening (till what is discussed in the thread; there might be some more challenges ahead). Here are some details if anyone is following this thread. Also, there is a possibility that a few things maybe out of sync since I have done these in bits and pieces and trying to remember the steps.

Few things to keep in mind …

  • From word press point of view, settings are exactly same on example.dreamhosters.com and example.com

  • Original site, say example.com, is still hosted at old ISP and nothing is changed there.

  • On Dreamhost, I have setup two parallel sites/folder structure
    /home/example.dreamhosters.com - test site so I can see load time before hosting on Dreamhost, test plugins to ensure it does not mess up site looks etc.
    /home/example.com - set by Dreamhost and future home of my main website once I move dns to Dreamhost

  • FTP client in use is FileZila though being a nix person, I use SSH whenever I can

  • Set up two database hosts (mysql.example.dreamhosters.com and mysql.example.com) and datbases
    This is setup using: Goodies -> mysql databases

  • Created WP site structure, added posts and pages under "example.dreamhosters.com"
    Note: I did a dump and load from old site but the site format looked a bit messed up. Given that the original site is not in WP and WP footprint is very small, I decided to recreate it.

With above in mind, following are the steps:

STEP 1. Copy Wordpress files (themes, plugins, etc.)
Copy everything from /home/example.dreamhosters.com to /home/example.com
a. Login to Dreamhost via SSH and do:

This will copy everything from “example.dreamhosters.com” to “example.com

$ cp -rp /home/example.dreamhosters.com/* /home/example.com

confirm it worked; both of these should look same.

$ cd /home/example.dreamhosters.com/; ls -l
$ cd /home/example.com/; ls -l

STEP 2. Move MySQL data from “example.dreamhosters.com” to “example.com

Option 1: SSH to Dreamhost and Dump and Load from nix cli (one step)
Source: http://wiki.dreamhost.com/Migrate_MySQL
Command:
$ mysqldump -h oldhost -u oldusername -poldpassword olddbname | mysql -h newhost -u newusername -pnewpassword newdbname

  • In this case, the “oldhost” and “newhost” are the same host - the name of Dreamhost server your domain is being hosted on.
    If this worked, it will copy your entire data to the new db.

You can login to MySQL, pick a table and display something like
show databases;
show tables;
select * from

;

Option 2: Dump and load using PhpMyAdmin
a. Dump MySQL data from “example.dreamhosters.com” to your PC
Many ways to do this. One way is to directly login to phpmyadmin:
http://www.example.dreamhosters.com/dh_phpmyadmin/mysql.example.dreamhosters.com/

You want to export your db content (from all tables) into a .sql file.

Note:

  • Userid/pass can be found in wp-config.php
  • Screen shot and steps of Database export are same as: http://robcubbon.com/moving-wordpress-site-to-new-domain/
  • You can edit this file in Notepad or Wordpad and really do all the search & replace of “example.dreamhosters.com” with “example.com” and then import this file to “mysql.example.com”. However, if your db is big, this can become a tiring process and you may miss something. I don’t recall now if Notepad and Wordpad has a feature that does global search and replace (i.e. once instead of prompting you to change every instance)

b. Upload .sql file to the root of your WORDPRESS site
Use FileZila or any ftp client to move .sql file from PC to Dreamhost to folder “/home/example.com”

You can probably also try this:
http://www.example.dreamhosters.com/dh_phpmyadmin/mysql.example.com/

and import the .sql file. It might even pick the file from your PC. I didn’t try this but I am fairly sure it works.

STEP 3: Intial Testing of site (while dns is still not moved)
On my nix (linux/unix) host, I added Dreamhost DNS in /etc/resolv.conf, and in the browser pulled it as: http://example.com. It will pull the main page but none of the links will work for now ( you can howver on the categories and it will show still as “example.dreamhosters.com”)

See: http://wiki.dreamhost.com/DNS_-_Viewing_site_before_DNS_change

STEP 4. Search and replace all instances of “example.dreamhosters.com” with "example.com"
For this, I used this tool:
http://interconnectit.com/products/search-and-replace-for-wordpress-databases/

Note: I am sure there are good WP plugins to do just that in a simpler way.

STEP 5. Login to your WP BACKEND and then visit your site from there

Note: I haven’t quite figured out this ritual. Maybe I need to empty cache used by WP Super Cache or any other cache plugins.
However, this does seem to “refresh” links. If I just pulled my site (example.com) on the nix host with temp dns, the links still showed from old “example.dreamhosters.com”.

NOTE: At this point, both “example.dreamhosters.com” and “example.com” should look exactly the same.
i.e. example.dreamhosters.com shows in any browser since it is on the internet. And “example.com” only shows on the PC on which you have set up the temp DNS.

NEXT STEP…

  • Move DNS from old host to new host

Some links I found/gleaned through (helpful if you are moving from godaddy to Dreamhost):
http://jphenow.com/blog/2011/03/28/moving-a-wordpress-install-from-godaddy-to-dreamhost/