HOW TO INSTALL MULTIPLE INDEPENDENT MySQL DATABASES

This tutorial will explain how to install multiple MySQL databases on your server.  If you want 2 or more independent databases on the same server, perhaps one for your own websites and then another for someone else so they can have their own without risking the security of yours, this tutorial is the answer.

I spent several hours trying to figure this out myself and after extensive searching on the internet, I was left confused and without an answer.  It's hard to understand it when nothing really explains how it works or what you need to do to configure it correctly.  So I will explain what i learned and hopefully this tutorial will help someone that is in the same mess I was in when I decided to add a second database.

To begin with, each SQL database server must have a seperate installation for it.  Unlike the way multiple websites with apache work, SQL is actually a program and in order to have a new database, a new installation is required.  I set my first MySQL database up using XAMPP to install Apache, PHP, phpmyadmin, and MySQL, so I will be using this setup to write this tutorial.  If you have reached this website and have a different configuration and need help, simply make a post in the forum and I will do my best to help you with your configuration.

 

WHAT YOU NEED:

You first need to already have XAMPP installed or an installed web server with a working MySQL database server running on it. 
Next, go to http://dev.mysql.com/downloads/ and download the appropriate installation file.

I personally use the MySQL Community Server and find the Windows Essentials installation version being good enough for my needs.  If you need a more elaborate installation, you might want to try the larger download.

If you have already installed MySQL, go HERE to read how to remove it completely before continuing.

 

INSTALL MySQL

1) Launch the installation file to begin installing MySQL.

Choose "Custom" as your setup type.

 

2.) Choose your installation directories.

You can set the directories each part installs to by selecting it individually and then clicking change to set it to the directory of your choice.  Unless you're using this MySQL database for software development, then you won't need the C Include Files/ Lib Files.

 

3.) After installation finishes, you will come to this screen.  Go ahead and leave that box checked and click "Finish".

 

4.) Choose Detailed Configuration, then click next.

 

5.) Now select your Server type.  If you're running this on your every-day PC, select the first one.

If your computer is dedicated to being a web server and not used for personal use, then select the Server Machine as I have done.

 

6.) This option is pretty self explanitory.  Select the one that suits you.

 

7.) Here you can set your datafile directory.  Choose whichever option makes you happiest.

 

8.) This setting depends on your particular needs.

I personally use the first option, but you may need a higher value depending on your server.

 

9.) Select the port number for your MySQL server to run on.

Just choose whichever port you like.  Just make sure it's not already taken or else you will encounter an error when trying to run both databases at the same time because only one server can run on a port.  I also recommend checking the "Add firewall exception for this port" if you're running a public server.  Otherwise you could have problems in the future if the port is not properly enabled.

 

10.) Make your choice here depending on your needs.

 

11.) Select these accordingly.

Make SURE you change the Service Name to something different so you can identify the database.  I'm not sure, but it may also conflict with previous instances of MySQL that are running if the name is left the same, but I haven't tested this.  You can set the Service Name to whatever you like or just choose one of the options from the drop-down menu.

Choose Launch the MySQL Server automatically if you are running a dedicated server or you do not wish to manually start the MySQL server each time.

 

12.) This is another straight forward screen.

Set the root password for this server and choose whether or not it can be accessed from remote computers.

 

13.) If everything goes well, you should see this screen with the pretty blue check marks.

If you get an error here, there's two reasons I know of that cause it.  One being you already have a SQL server running on that same port and the second being you didn't remove the previous installation attempt.  I know you will get an Error 0 if you didn't remove the previous install.  Now I'm not talking about the install that XAMPP creates, but the one that would be there if you attempted to install MySQL Server using the download above and now you're following this tutorial.

To remove the previous install, click HERE to read the removal instructions.

 

INSTALLATION IS COMPLETE!!!

Now you must configure phpMyAdmin to recognize the new MySQL Server.  It is NOT neccessary to install phpMyAdmin again if you already have it installed.  I spent hours trying to figure this out before I finally found out all you have to do is just make a copy of the existing one and change a few values in it.  The steps below will guide you on how to do that.

 

 

HOW TO CONFIGURE PHPMYADMIN FOR MULTIPLE MYSQL SERVERS ON THE SAME COMPUTER

This section will guide you through how to setup phpMyAdmin for multiple MySQL Servers running on the same pc or the same server.  This guide is for configuring them as independent servers and NOT for combined database server configurations.  The method outlined here is ideal if you're wanting to create a database server seperate from the first one for someone else to use and so it doesn't pose a security risk to your database by giving someone access to your database server.

As I stated at the beginning of this article, I will be referencing to XAMPP as the initial install.

LET'S BEGIN.

Go to /xampp/ and copy/paste your phpmyadmin folder.

Rename it to something you can easily remember, such as phpmyadmin2.

Open the new folder and then open config.inc.php and add the following hightlighted lines:

$cfg['Servers'][$i]['auth_type']     = 'cookie';      // Authentication method (config, http or cookie based)
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['port'] = '3307';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = '';

You should set the port to whichever port you chose in STEP 9 above.

Now save and close this file.

 

Go to /xampp/apache/conf/extra and open httpd-xampp.conf in Notepad.

Locate the following and copy/paste a second instance of it.

Alias /phpmyadmin "C:/xampp/phpMyAdmin/"
<Directory "C:/xampp/phpMyAdmin">
AllowOverride AuthConfig
Order allow,deny
Allow from all
</Directory>

Now make the following edits with your correct info.

Alias /phpmyadmin2 "C:/xampp/phpMyAdmin2/"
<Directory "C:/xampp/phpMyAdmin2">
AllowOverride AuthConfig
Order allow,deny
Allow from all
</Directory>

Now save and close that file.

Restart Apache and launch MySQL if it's not already running.  Also launch the second server, MySQL2, if it's not currently running.

 

If everything was done correctly, you should be able to access the databases based on the extension you enter.  Here's two examples.

http://www.mydomain.com/phpmyadmin (This will take you to your first MySQL server)

http://www.mydomain.com/phpmyadmin2 (This will take you to your second MySQL server)

 

And now you're finished!  Now you have two seperate database servers and you can repeat these steps to make additional SQL server if that is neccessary.

 

 

** How To Completely Remove MySQL **

If you need to remove MySQL, here's how to do it.

First, go to Control Panel and then Add or Remove Programs.

Select MySQLServer 5.1 and click "Remove".

 

Now go to C:\Documents and Settings\All Users\ and make sure you can see the Application Data folder.

If you're using Vista, you will go to C:\Users\Username\ And look for a folder called "AppData".

If you cannot see this folder, then you need to make system and hidden files visible.  XP and Vista use the same way, just click on "Tools", then select Folder Options and then the View tab and turn Show hidden files and folders on.

 

Now delete the folder MySQL from here.

Go to C:\Program Files\ or wherever you chose to install MySQL and make sure the folder is gone.  If not, delete it.

The last step is to delete the data folder for it depending on where you chose to put this at.  Mine was at C:/Data/ for example.

You should be done!  Click here to return to top.