Merging Sql Databases In Cpanel With Phpmyadmin Using SQL after Fantastico installs

[vujsa 0]

While attempting to install a bridge between Simple Machines Forum (SMF) and Mambo Open Source (Mambo), I had to install SMF manually because the two applications had to use the same database. When an application is installed using Fantastico, Fantastico automatically creates a new SQL database for the application. As a result, if Fantastico is used for both Mambo and SMF, then there will be two databases.

 

This guide will explain how to merge two or more SQL databases in cPanel using phpMyAdmin.

First, you'll need to decide what database will be the target. I usually create a new database and then copy the two original databases into it. There are a few things to consider before deciding which database to use as the target.

Will your application allow you to change which database it will use.

(Check your global configurations or server / database settings in your admin control panel.)

Does one or more of the applications need to be installed manually.

(If an application is to be installed manually, then insert the appropriate database information for your existing database.)

Do you already have a database that is shared by two or more applications.

(If so, then this might be your best choice because you won't need to change your configurations for as many applications.)

If you want create a new MASTER database to insert the two source databases into, then you'll need to do so with the MySQL Databases tool in cPanel.

Your username will automatically be added to the database name like so: username_data1 or username_smf2.

So when creating your new database, just use the extention like data1 or smf2.

Give your new database a name that is descriptive but short like masterDB1 which will become username_masterDB1.

You'll need to create a new user for this database in the same MySQL Databases tool in cPanel.

I suggest using the same system for database usernames as Fantastico uses which is the same name for the database name and user name.

So in our example, create a user named masterDB1 which will become username_masterDB1.

Create a password that is random but not one that you would use for any of your personal accounts.

Something like abc123def456ghi789 would be an ok password for an SQL database.

You'll need to remember your password or write it down because you won't be able to retieve it ever and changing the password is much more difficult than any other procedure in this guide.

Grant ALL Privileges to the new user.

Now, once you have a target database, you'll need to create a backup of the target database unless it was newly created.

Using the Backup tool in cPanel, choose the SQL database that needs to be backed up and downloaded.

Just save it to your hard drive and leave it alone.

OK, so far we haven't done anything that will disturb the operation of any database or application. Many of the steps that follow can leave your website unusable. Please make a full backup of your website now.

You can do this with the same Backup tool in cPanel by clicking on the link that reads "Generate/Download a Full Backup"

Be sure to wait until it finishes the backup and sends an email to you before downloading.

(This file can be left on the server but may be rather large and use valuable hosting space)

In the following screen, where it reads"Backup Destination:" choose Home Directory.

Enter your email address in the correct field and leave everything else blank then click the Generate Backup button.

After You receive your backup complete email, you can return to "Generate/Download a Full Backup" and download your newly backed up website file.

Okay, we are done with all of the preperation work, lets merge those databases.

Using phpMyAdmin, select the first source database.

Click on the Export tab at the top of the page.

Check Save as File and check "gzipped" as the Compression option.

Click the Go button.

If asked, choose Save to Disk and remember where the file is. C:\downloads\backups\ for example.

Repeat for all of the source databases.

 

Now you have all of your source files ready to be merged.

Using phpMyAdmin, select the target database.

Select the SQL tab at the top of the page.

Where it says "Location of the textfile:", Browse to the location of the databes source files and check Auto Detect for the Compression option.

Click the Go button.

Repeat for all of the source databases.

 

Your newly filled / merged database is now done.

 

All you have to do now is to change your settings in your applications to point to the correct database with the correct username and password.

 

Hope This Helps,

 

vujsa