Combining Databases

slloyd · Post by **slloyd** » Tue Mar 20, 2007 4:13 am

I have about 45 databases for each cms site I own (they are subdomains). Can I combine them into one database? If so, how?

cyberman · Post by **cyberman** » Tue Mar 20, 2007 5:27 am

You can put all 45 databases in one database. You should only make a look every database must have a specific prefix.

slloyd · Post by **slloyd** » Tue Mar 20, 2007 3:24 pm

How do I do that? Can you explain the steps?

Pierre M. · Post by **Pierre M.** » Wed Mar 21, 2007 11:25 am

Choose an appropriate "table prefix" when you install.
Like cms_1_ then cms_2_ then cms_3_ instead of "cms_" only.
You only need one database (so have multiple table sets)
Pierre M.

slloyd · Post by **slloyd** » Thu Mar 22, 2007 2:09 am

I feel like such an idiot when I ask questions on this forum because every answer I get, I don't understand. I know nothing about databases, so your answer makes no sense to me. Can you clarify? Sorry.

cyberman · Post by **cyberman** » Thu Mar 22, 2007 4:54 am

Are you a little bit familiar with phpmyadmin?

Pierre M. · Post by **Pierre M.** » Thu Mar 22, 2007 12:22 pm

slloyd wrote: (...)Can you clarify? Sorry.

Don't worry, you already know it. If you have installed 45 times, just install a 46th and give special attention the database configuration step : one of the parameters is called "table prefix" and the default value is "cms_". If you want to make multiple installs with only one database, just change only this table prefix to store all your install in the same database. Just set the prefix to "cms_1_" for the first install, then "cms_2_" for the 2nd, then "cms_3_"... you got it.

Pierre M.

cyberman · Post by **cyberman** » Thu Mar 22, 2007 1:28 pm

If you have used every time the same prefix now you have to manipulate all table prefixes far all 45 installs by hand via phpmyadmin.

Vin · Post by **Vin** » Thu Mar 22, 2007 9:28 pm

Yes, and I think you would need to change the prefix in the config.php -

Code: Select all

#If app needs to coexist with other tables in the same db,
#put a prefix here.  e.g. "cms_"
$config['db_prefix'] = 'cms_';

for each of the cms site.

slloyd · Post by **slloyd** » Sat Mar 24, 2007 8:34 pm

Okay, I think I get it. So how do I get the info that's already in the database, into the new combined database? What are the steps? Do I have to dump each one and then...ugh. Or is it just the matter of re-directing the config.php file?

Does everything have to be so complicated?

Vin · Post by **Vin** » Sun Mar 25, 2007 9:41 am

Uh, yes, RENAMING each of the table would be VEERY frustrating (I'm afraid phpmyadmin doesn't support batch conversion - as for other db tools, don't know). However, there is an easier way (supposing you have mysql database) (yeah, unfortunately, you'll have to do it for each of the 45 installs; still, it's better):

Download mysql_dump from the forge here: http://dev.cmsmadesimple.org/projects/mysqldump/ and install it on each of the cmsms sites
Dump the databases just in case something went wrong when doing the following steps
Now, create rename.php file and insert the following code:

Code: Select all

<!DOCTYPE HTML PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN">
<__html>
  <head>
  <meta http-equiv="content-type" content="text/html; charset=windows-1250">
  <meta name="generator" content="PSPad editor, www.pspad.com">
  <title></title>
  </head>
  </__body>
  <form action="rename.php" method="post">
    <table>
      <tr><td><label for="user">Enter the computer address (localhost in most cases): </label></td>
      <td><input type="text" name="computer" /><br /></td></tr>
      <tr><td><label for="user">Enter the username of the cmsms database: </label></td>
      <td><input type="text" name="user" /><br /></td></tr>
      <tr><td><label for="password">Enter the password: </label></td>
      <td><input type="password" name="password" /><br /></td></tr>
      <tr><td><label for="dbname">Enter the name of the cmsms database: </label></td>
      <td><input type="text" name="dbname" /><br /></td></tr>
      <tr><td><label for="oldprefix">Enter the old database prefix(if any)(example: cms_): </label></td>
      <td><input type="text" name="oldprefix" /><br /></td></tr>
      <tr><td><label for="newprefix">Enter the new database prefix(if any)(example: cms_1_): </label></td>
      <td><input type="text" name="newprefix" /><br /></td></tr>
    </table>
    <input type="submit" value="Confirm" />
  </form>
    <?php 
      if ($_SERVER['REQUEST_METHOD']=='POST') {
        mysql_connect($_POST['computer'] ,$_POST['user'], $_POST['password']);
        if (mysql_connect($_POST['computer'] ,$_POST['user'], $_POST['password'])==false) return 'Could not connect to the database<br />';
        mysql_select_db($_POST['dbname']);
        if (mysql_select_db($_POST['dbname'])==false) return 'Could not select the '.$_POST['dbname'].' database<br />';
        echo mysql_error().'<br />';
        $sql=mysql_query("SHOW TABLES");
        echo mysql_error().'<br />';
        while ($oldtbl=mysql_fetch_row($sql)) {
          echo $oldtbl[0];
          $newtbl=substr_replace($oldtbl[0], $_POST['newprefix'], 0, strlen($_POST['oldprefix']));
        	mysql_query("ALTER TABLE `".$oldtbl[0]."` RENAME `".$newtbl."` ");
        	echo mysql_error().'<br />';
        	if (mysql_error()==false) {
         	echo ' -> '.$newtbl;
         }
        }
      }
    ?>
  <__body>
</__html>

and put it onto the server (or the test computer) so that you can access it via the browser(if you put it onto the server, DELETE it after you've succeeded in renaming the tables - there's a security risk).
Fill in the form and submit it via your browser.
Check the database via phpmyadmin (or another db tool) whether the tables were changed successfully.
Repeat the renaming for all of the 45 sites.
Now delete the rename.php if you've placed it onto the server.
Rename the db_name and db_prefix in the config.php for all of the sites respectively:

Code: Select all

#This is your database connection information.  Name of the server,
#username, password and a database with proper permissions should
#all be setup before CMS Made Simple is installed.
$config['dbms'] = 'mysql';
$config['db_hostname'] = 'localhost';
$config['db_username'] = 'root';
$config['db_password'] = '';
$config['db_name'] = '[b]cmsms[/b]';

#If app needs to coexist with other tables in the same db,
#put a prefix here.  e.g. "cms_"
$config['db_prefix'] = '[b]cms_[/b]';

That is, rename the db_name to the name of the site you want to have as the 'main' one for all of the sites (if you don't want to have an existing name of the database, you'll have to rename one of the databases in the phpmyadmin and change the $config['db_prefix'] respectively)
AND the corresponding $config['db_prefix'].[/li]
Backup the data on all of the sites via the module.(NOTE - maybe you should delete the previous backups FIRST to avoid the mistakes when fulfilling next steps)
Move the backups from the admin\backups\ folders of all of the sites and place it in the admin\backups\ of the site you want to have as the 'main' for backups(NOTE - again, you may delete the current backups FIRST for this site so that you can restore the backups without checking whether they are the backups with renamed tables or old backups)[.
Restore the backups
Now, you should have 45 times n tables in the database, where n was the count of the tables for each of the site database (supposing the count was the same for all of the databases)

I know, this is not an optimal solution, but for what it's worth...
[EDIT] If you're running on PHP5, you may run into problems when submitting the form. Try changing mysql_* functions to mysqli_* then.

And yes, that (x)html file is invalid. But that shouldn't affect the functionality.

slloyd · Post by **slloyd** » Mon Mar 26, 2007 3:36 am

Oh brother. Nothing is ever easy is it?

Thanks for your help!

kermit · Post by **kermit** » Mon Mar 26, 2007 4:09 am

you can also do it "manually" by taking the sql dumps out of something like phpmyadmin, which are nothing but text files containing CREATE TABLE and INSERT INTO statements that look like:

--
-- Table structure for table `cms_version`
--

CREATE TABLE `cms_version` (
`version` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `cms_version`
--

INSERT INTO `cms_version` (`version`) VALUES
(27);

in a decent text editor, do search & replaces on these:

CREATE TABLE `cms_
INSERT INTO `cms_

replacing cms_ with the new prefix, e.g. cms01_
note you can't just do a search & replace on 'cms_' as that'd also match tags like cms_selflink in content.

then import the changed sql files into a new db and change the db settings in each cmsms install's config.php

to make the import a bit easier, you could combine the 45 changed sql text files into one and then zip it up before using the import function of phpmyadmin.

Vin · Post by **Vin** » Mon Mar 26, 2007 6:31 pm

Ah, and I thought that these files were not readable...
However, it's a struggle anyway...dumping all of the 45 sites $:-\$
But combining the sql file means one restore

.
But... isn't it too late?

Pierre M. · Post by **Pierre M.** » Tue Mar 27, 2007 10:51 am

kermit wrote: you can also do it "manually" by taking the sql dumps
in a decent text editor, do search & replaces on these

I support this nice way of doing things.
Pierre M.

CMS Made Simple Forums

Combining Databases

Combining Databases

Re: Combining Databases

Re: Combining Databases

Re: Combining Databases

Re: Combining Databases

Re: Combining Databases

Re: Combining Databases

Re: Combining Databases

Re: Combining Databases

Re: Combining Databases

Re: Combining Databases

Re: Combining Databases

Re: Combining Databases

Re: Combining Databases

Re: Combining Databases