Knowledgebase: MySQL & Databases
How do I Use a MySQL Database for Multiple Applications?
Posted by Pacific Host on 05 December 2011 05:42 AM

When you install a script from Cpanel which requires a MySQL database, you must have 1 available unused database. This can be a waste for simple scripts like the Advanced Guestbook which doesn?t get a lot of interaction or entries compared to other scripts. It is also a bit of a pain if you only want to check out some of the scripts to see what they do. You have to install a script, do the setup and testing, and then delete the database to try out the next. It would be nice to be able to check out two or more scripts at once, especially if you are comparing 2 shopping carts or 2 CMS scripts. So, how can you share a database between 2 or more applications?

IMPORTANT NOTE: Installing more than one app in a database will increase the size of it and more importantly the number of accesses to it. Doing this for the reasons lined out above is fine but you do not want to combine two heavily used apps like a forum and a CMS (unless the forum is part of the CMS) or a chat program. Too much traffic will cause the database access to slow down. It is not a problem for testing but in actual use it can be. 

There are two ways to accomplish your mission. Please read the entire procedure before beginning.

Step 1: For those who are not afraid to manually install scripts, you can install additional scripts by hand by searching on the Internet for the script name or by asking someone in the forums if they know where to get it. Scripts come with readme files or install files that explain how to upload the script and set it up. Personally, I find it easier to use a proper script editor to read these files rather than Windows notepad. TsWebeditor is one and it is free. Be sure you have the name of your database, the db username, and the password on hand. The database name and username will be cpanelusername_dbname and cpanelusername_dbuser (change these to yours).

Many scripts have easy installers but some will require that you import a .sql file into the database with phpmyadmin. You can find it in cPanel under MySQL. When in phpMyAdmin, click on Databases, then the name of the database. Click on Query Window in the lower left of the screen. Click on Import > Click on Browse > Find the .sql file on your computer to upload it to the database then click on Go.

IMPORTANT NOTE: Backup the database in cPanel under Backups before you install the new tables. The new tables being added must have different names than the ones already installed for the other application.

When it is all setup, check out the app, adjust any settings in the app, and test, test, test! If your server is running under suphp, you will need to change permissions to 755 rather than 777 and 644 rather than 666. You will need to move any php_values that were installed in .htaccess to their own php.ini file which you create. Remove ?php_values? from in front of them when placing in php.ini. These two items will cause 500 Internal Server errors under suphp and some other strange things as well, depending on the script.

Step 2: For those that don?t want to manually install or want it down and dirty, you can actually have Cpanel do most of the work with it?s Backup function. Start by installing one of the apps you want in Cpanel. It will create a database, a database user, and a password. Run the app to be sure it is working and make any adjustments for suphp. Backup the database in Cpanel under Backups. It will save it to your computer, so put it in a folder just for this and be sure to write down the name of the app you installed as well as where you installed it if you were given a choice.

Now that it is backed up, delete the database in Cpanel under MySQL. Install the next script. Repeat the above from 2. down. Make sure you backup the new database each time before deleting.

When you have installed the last app you want to combine and backed up the database again, you may begin adding in the other database backups to it by using phpmyadmin to import the other backups into the current database. When in phpmyadmin, click on Databases, then the name of the database. Click on Query Window in the lower left of the screen. Click on Import. Click on Browse. Find the .gz file on your computer to upload it to the database then click on Go.

When you have combined all the backups (except the backup of the last database), you will need to edit each app?s config file to change the name of the database called, the username, and the password to match the current database. Usually this can be found in a file called config.php or configure.php. If you don?t see them there, then ask in our forums for the right page to edit. You are looking for something that looks like this:



$dbh=mysql_connect ("localhost", " cpanelusername_username ", "PASSWORD") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("cpanelusername_dbname ");

To get the right password for the current database, find the config file for the last app installed before correcting the others. Copy the password from it. I use CTRL-C to copy it and CTRL-V to paste or Edit, Copy and Edit, Paste, or right click, Copy and right click, Paste. You can always just write it down too with a number 2 pencil. ;-)

Test each app again to be sure they are working. 

(0 vote(s))
Not helpful

Comments (0)