Joomla Database Transfer


#1

Hi. I recently converted my site from HTML/CSS to Joomla! 1.5 (which is really great, by the way). However, I hit a glitch in transferring my site from my local PC to DreamHost and I wanted to understand whether there is a better way to do it.

The recommended procedure for trying out a CMS system like Joomla! is to install it locally, get it working, then move it to your host (DH). The basic process for moving it is (1) Install Joomla! on your DH site; (2) copy Joomla files from local to DH. (3) Export the Joomla MySql db on your local system and import it into your DH MySql db.

On step (3), I ran into a problem. The db name on DH must be unique across the entire site, so it will typically be different than your db name on your local system (which can be anything). When you install Joomla! on DH (even a blank install without sample website), it creates your MySql db with data in a number of tables. You cannot import the data from your local db if you have data already in the tables (because of duplicate keys).

One easy solution would be to drop the DH db and then re-create it (thus creating an empty db). But DH does not allow you to drop (or rename) db’s. So the only solution I could come up with was to truncate each table that had rows in it and then do the import.

Is there an easier or better way to accomplish this? Thanks. Mark


#2

I have installed Joomla! many many times in different environments, and I routinely “stage” Joomla! sites under development before launching them (going "live), but I don’t think that “recommendation” is necessarily a good one. There are circumstances where it might be desirable, such as when you have poor connectivity, expensive connections, limited storage/bandwidth on your host, lack of available domain(s) to host development sites under,etc., but if those are not problems for you, I think it is much preferable to stage a site for development in the same server environment as the live site wil be running under.

This eliminates any “last minute gotchas” that can present themselves when you move from one environment to another (particularly with applications that configure themselves “automagically” via environment variables, or do things differently depending upon what functions or features are available in a given environment). Examples of what I am talking about here include the use of register_globals, allow_url_fopen, permissions, etc.

That said, with DreamHost, the availability and ease of using unlimited subdomains, the ridiculously generous storage and bandwidth quotas, and the availability of the shell makes “developing on the server” a great way to approach the situation for Joomla! sites , and makes “going live” extremely efficient and easy once you understand how to do it. :wink:

Actually, I think you have misstated that process, as steps one and two are redundant, if you have already installed Joomla! on your DH site, there should be no need to copy “Joomla! files from local to DH”.

Well, if you are limited in what you can name a db on DreamHost but are not on you local implementation, you can always name the db on your local implementation the same as your preconfigured DH db, but it is important to note that the names of the db (or the hostname, the db user/pass, etc) don’t really need to be identical between the sites at all - they are easy to change with an edit to the configuration.php file.

The tables that already exist are the result of the redundant steps one and two I mentioned above. If you had not first installed Joomla! on DreamHost, but had instead simply created an empty db to hold the Joomla! data you plan to import from your staging site, you would not have these tables at all. If you have already installed Joomla! (though you didn’t need to!), you can “simulate” thid condition by using phpMyAdmin, or other method to manipulate the database, and just drop all the existing tables - they will be recreated by the import.

I think there is, Mark! My “staging” routime goes like this:

  1. Create a subdomain on DreamHost to hold the development site. This can be a “free” dreamhosters.com subdomain (anythingnotyettaken.dreamhosters.com) or a subdomain of the site being deployed (dev1.yournewdomain.tld), or a subdomain or one of your existing domains (newsitedev.yourexistingdomain.tld), etc.

  2. Create a database, and a host, to hold the data fro the Joomla! site being developed. You can also use an preciously existing db, as Joomla! allows you to define a prefix for it’s tables to avoid conflicts with other tables in a pre-existing database), but since you can have an unlimited number of databases at no additional cost on DreamHost, unless you have a reason to want to put “many sites in one database” for ease of backup or some other reason, I see no reason not to keep it simple and just create a new one.

  3. Install Joomla! in that subdomain. Either manually or via the “one-click” installer - either way is fine. I prefer to install Joomla! via the shell using wget and tar because it is really quick and easy. Installing the Joomla! code that will (later) become the “live” site directly opn the server avoid the common problem reliably uploading the ginormous number of files that comprise Joomla! via FTP without error, and assures all your permissions are accurately set for Joomla! operation on DreamHost! (you might want to check out a related thread for more information on that issue). :wink:

  4. Optionally, protect the “dev” Joomla! installation from public viewing before you are ready via apache authentication and .htaccess

  5. Build the site on the server!

Now, when the site is ready to launch, it’s a five-minute process (or less!) to rename the dev subdomain directory to the name of the “live” site’s web directory, edit a couple of variables in the configuration file, and go “live”. No incompatible environment issues, no FTP issues, and you can see the site actually operating as the visitors to the live site will see it before you even launch.

I think that process is just a better way to go at it, but, like with most things, YMMV. Hopefully, if you prefer to develop Joomla! sites on your own machine, some of this response has still been helpful. Good luck with your project(s)! :slight_smile:

–rlparker


#3

Thanks very much for the explanation. I agree with you that, in some ways, it would have been easier just to start with Joomla in DreamHost. However, it seems to be almost always recommended to work on localhost first, which of course makes perfect sense if you are just trying out a CMS and don’t know whether you will use it (which was true in my case). A lot of times, you don’t really know whether you’re going to use something until you’ve already put a lot of work into it.

The phpMyAdmin “trick” that I didn’t know about was the ability to Select All tables and then either drop or truncate them. That makes it very easy to move everything from a db on localhost to the db on DH, even if it has a different name.

Just to make sure on one other point. Is this statement true? The one-click install for Joomla at DH is exactly the same as copying all Joomla files from an existing Joomla installation and then editing the configuration.php file manually?

Thanks again. Mark


#4

I’ve heard that too, but to me is just easy to experiment on a throwaway domain/sudomain …that way I know it’s gonna work in the host’s environment, and I can wipe it out with a single shell command and a web panel action or two if I don’t like it. You should do whatever works best for you! :wink:

[quote]Just to make sure on one other point. Is this statement true? The one-click install for Joomla at DH is exactly the same as copying all Joomla files from an existing Joomla installation and then editing the configuration.php file manually?
[/quote]
As far as I can tell, though I don’t really know how DH keeps track of the one-click status for the purpose of upgrading… you can manually upgrade a one-click, but that breaks the “one-click” upgrader going forward, so there must nbe some difference somewhere - but I can’t find it so it must not be in the actual Joomla! codebase. :wink:

–rlparker