Joomla! Discussion Forums

 since this was long ago...I repost becasue I do need an answer more close to it.
not exactly a site that manages all the rest but what I need is a MYSQL that manages/engulfes/ meltdown all the others. better say like that: I'm allowed to use 15 db from MySQL - since I do need to save my db numbers...I had the idea of making my 15 db (all from Joomla sites) into one.
Of course I do not wanted to loose anything- but will be smart to have one and big one than 15 tiny ones ( around 300 kb each one or a bit more some..).
anyone here can give a GOOD and Practical move about that?
be carefull when writing here; I'm a total dummie about MySQL and have not yest touch it directly in the MySQL programa itself! All I do is to work in the back end of Joomla as ADM..
thanks really appreciated since I'm looking for a solution since 10 months ago..so far nothing really practical.  :-*

On the question of having several sites sharing some joomla content and having some unique ... I don't have an answer for that one.

But, on the question of "can I have several different Joomla sites all running from a single mySQL database" the answer is absolutely yes.

Here's the secret.  When Joomla creates tables in a database, it prefixes all of it's table names with a prefix that you can set.

The default prefix is jos_ but you can change that.

So, for example, the "users" table gets named "jos_users" in your mySQL database.  And so on for the 30+ tables that make up a Joomla database.

Now, you can use a different prefix for each site.  It is specified when you are doing the Joomla install.  (It often is not accessable if you are doing a "fantastico" type of install).

So, you could use "site1_" as a prefix on one site, and the users table would end up being called "site1_users."  On site 2, you use the prefix "site2_" and the user's table gets named "site2_users."  And so on.

So, you can indeed do this.

But another question to ask is "should" I do this?  For example, you have a host that gives you only 15 mySQL databases.  OK.  Fine.

But hosts often also limit the number of simultaneous connections to your mySQL database.  My host, for example, allows only 25 connections at a time.  Now, a Joomla connection probably only lasts a half second or so, but if you have a bunch of sites and they get busy, you could end up maxing out (at certain moments) the simultaneous queries limit.

Now, another possible problem is with backups and restores.  You need to be sure that you have a good backup strategy in place.  Now, you host may do your backups for you (and if you are confident that this works OK, then that is OK.

But, the short answer to your question is "Yes.  You can do this."

yes it's!  I already have let's say 15 sites instaled using fantastico to do it. and yes the prefix is jos_joom1 and so on until 15.
Yes I d understand the problem with so many queries etc..actually I do not understand it but I have seen it..and I hate when it happens..because my entirely domain gets inacessible for several minutes.
So, J., how do I do that?
as I said up there, those 13 let's say are quite small..like under 300kb !! oops !! I was wrong the databse is about 50 kb in zip format and that's the big one! so the 300 kb are all together maybe less the total size -  all 15 sites...

Point is how do I do that? since I have not a single clue how to get in the  My Sql databse in the host's CPanel (ok I know how..but I don't know how to change things inside mostly because the CPanel in the host changes all the time to newer version and I had not yet try anything in this newer version)
Now, do think I newbie can do that?

Can a newbie do it?  Sure, but it is a bit tricky, and a bit dangerous.  So, be sure you have a good backup of your databases.

Now, let me see if I can give you a step by step of the process.

Let's say that you have site number one with a database name of myname_joom1.

And, site two has the database name of myname_joom2.

Now, each of these two databases has a bunch of tables with the prefix jos_.

So, here is how I would combine them into a single database.  Let's combine database 2 into database 1.

First of all, go into your phpMyAdmin in your cPanel of your hosting account.  (in cPanel, click on MySQL databases.  Then scroll down to the very bottom of the screen and you will see a very small link to phpMyAdmin).  This will allow you to manipulate your databases directly.

But, be very careful.  You can do a lot of damage if you do the wrong thing.

Find the database myname_joom2 (or whatever it is called).  It will be listed on the left side of the screen. 

Do an export of all of the tables to a text file.  Click on the tab and select all of the tables (they are probably already selected).

Then, under structure, check "Add if not exist."

Now, go to the very bottom left check the box and then click the button at the bottom left.

That will "dump" your data into a text file that you can save on your computer somewhere (remember the file name and location!).

Then, you will need to open that text file with a text editor (not a word processor) and do a search and replace.  Search for "jos_" and replace them with "site2_" (or whatever you want to use as the prefix for this set of joomla tables.

Save that file after doing the search and replace.

Now, go back to your phpMyAdmin and open the first database  myname_joom1.

Go to to the "import" tab and import the SQL dump file that you just did the search and replace on.

Now, you have the tables for 2 sites in one database.  One set begins with "jos_" and the second set begins with "site2_" (or whatever you set it to be).

Now, the final step is to go back to site 2 and have it point to the database that you have just combined the tables on, and to tell it that the table prefix it is to use is not "jos_" but "site2_".

Log into the administrator side of your second site.  Go to Site -> Global Configuration.  Now, go to the "Database" tab.

You'll need to change the settings here as follows:

mySQLUsername:   

mySQLDatabase:   

mySQL Database Prefix:  site2_

these changes.

Now, if you used the same password for the joom2 database as you did for the joom1 database, then you are done.  Site 2 will be pointing to the same mySQL database as site 1, but with different tables (due to the different prefix).

If you did not use the same password for database 2 as you did for database 1, you need to go into the configuration.php file for the first site and get the password from there (it is "hashed" or encoded) and then copy it to the password in the configuration.php file in your second site.

Remember ... the database password is not to be confused with your admin password for your joomla site.

Now, this is, of course, risky and if you mess up you will end up with a site that is down.

So, be sure to make a backup of your configuration.php file before you do any of this stuff so that if you mess up, you can restore that file and get your site back up while you figure out what went wrong.

Does this make sense?

John! thta's a real tutorial! I wouldn't expect less from a pro like you.
Now I'm going by tiny steps..
first as my CPanel does not looks like you mention here, I have to figure out more carefull..I'll be back and forth here to read and do my tiny stesps...
hopefully I can count on you.
That's a big thanks
c

Well, I'll be glad to help.

Let me just underline again the importance of having backups all along the way.  Things will go wrong and you need to be able to "revert" back to where you were 20 minutes ago with the site was working properly, or you will have a sick, sick feeling in your stomach at some point.

You'll be messing with the Joomla tables and tinkering with passwords, and you have to be very, very careful here.

In fact, this might be a good time for you to learn how to use some of the "install and run Joomla on your local PC" tools. 

Or, if you want to stay online, try this.  Create a totally new mySQL database that will be the "new" master Joomla db.

Then, migrate your site content to this new db one site at at time.  That way, each site will still have its own, original db.

Then, once you have migrated your site's data to this new db, modify the configuration.

Using an FTP tool, make a copy of your site's configuration.php file.  Title it something like configuration_good.php and resave it back into your site's root folder. 

Then, go in and edit the configuration.php modifying the database information to point to your newly created (and recently migrated data) database.  Adjust the table prefix from "jos_" to "site3_" (or whatever) as appropriate.

Then save the configuration file and test your site to see how it goes.  That way, if something goes really wrong, you can just rename the file from configuration.php to configuration_this_doesnt_work.php and rename your configuration_good.php back to configuration.php and you have your site back up and running again in 30 seconds.

So, be careful.  While you can indeed do this, it is risky and requires great care.

I'd practice with my smaller, less critical sites first before I tried with a more critical site.

And, I go back to a comment I made earlier.  You can do this, but SHOULD you do this is another question! 

There are two reasons that I have stopped doing this.

One, I switched hosts to a new host that allows me unlimited mySQL databases.

Second, I just LOVE the jombackup mambot.  It backs up your site each day, zips up the backup, and emails it to the address of your choice.  But, it only backs up tables beginning with jos_. 

Anyway, keep us posted on how the process goes and we'll be glad to help if we can.

But remember those three key concepts:  backup, backup, and backup.

ok, I'm good student. ALready did all back ups possible to be done in my entirely domain. Finished just now.
Here it goes: http://galvao.info/portal/images//untitled-2.jpg  how it looks the Cpanel
Going inside that CPanel is kind of trip because all looks so neat and pro that I'm always afraid to touch things in...he he he
about the host:
Yes in a more deeply thought that's what I'm going to do next year or so..because my domain is pay for next 421 days ahead!
For the moment I do want to do this for try a bit deeper using MySql and  it's DB.
Now you say that I should try to build a new 'joomla' in my pc instaed of using the fantastico for making that 'mother' joomla site and have all others grab to it as individual db but at same time one only. DB.
Clear as crystal!
what is not is that part of creating an instalation of Joomla right here in my pc. Got not a clue how to do that.
Wel  I can go to the tuturial  and look up.
Then we'll have to freeze that message for while...until I build up the new joomla here. It's going to be my Xtmas 'doings' ..ha ha ha...
ok, John I believe I have to freeze it now and go to action.

Well, have a very merry Christmas!

Your cPanel is very nice and gives very easy access to the phpMyAdmin.  It's easier than the one I use!

I'd say just keep working online in building that "mother" database.

Now, if you want to install Joomla on your own PC (so you can work on sites offline), take a look at JSAS.

http://extensions.joomla.org/component/ ... Itemid,35/

There are a few steps to move a site back and forth from one server (webhost -> your pc (called localhost)  or localhost -> webhost), and they are tricky the first few times you do it, but it isn't real difficult.

And a component like jom_pack can make it much easier.

But, I'd say, at this point, keep doing what you are doing.  Do it online with your hosting service.  You are comfortable there, so that is where I would work.

So, back to action ... !

right John.
So I'm going to do it online, yes, I believe is going to be easier or at least more practica that build all again here at my pc.
Now I'll be back there.
thanks a lot an have a great Xtmas you too. Here follow the link ..my thanks to you!
http://galvao.info/portal/images/stories/my/valand72.jpg