How do I add another SQL database?

[dmphotography]

Hey there,
I hesitated at asking this here, but after failed attempts at finding my answer elsewhere, I knew there were some really sharp people here and the support is by far the best I've found yet.

What I'm wanting to do is create a completely separate mySQL database that is independent from the one XAMPP installs and having it run on the same server. The problem I'm having is finding the right documentation that details how to do this and helping me understand how it works. If I understand it correctly, I download MySQL from their website and install it in a separate directory and choose another port other than 3006 to run it on, which I chose 3007. But what to do from there is where I'm lost.

I'm not sure how I make the connection to the second database and do I need to install another instance of phpMyAdmin for the new database? What I was going to do is change the phpMyAdmin Alias name so I can add phpmyadmin to the root folder of the new website that will be accessing the separate database.

Any help will be greatly appreciated!

[glitzi85]

It is quite simple. Just make a copy of the phpmyadmin folder. For example you would have then:

xampp\phpMyAdmin
xampp\phpMyAdmin-3007

Now you open the file xampp\apache\conf\extra\httpd-xampp.conf and look for this Block:

Code: Select all

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

Just add one additional block like this:

Code: Select all

    Alias /phpmyadmin-3007 "C:/Helpful/xampp/phpMyAdmin-3007/"
    <Directory "C:/Helpful/xampp/phpMyAdmin-3007">
        AllowOverride AuthConfig
        Order allow,deny
        Allow from localhost
    </Directory>

Then open the phpMyAdmin-3007 folder and edit the file config.inc.php, in Line 31 you will find this:

Code: Select all

$cfg['Servers'][$i]['host'] = 'localhost';

directly after add this line:

Code: Select all

$cfg['Servers'][$i]['port'] = '3007';

Restart your Apache and you should be able to connect to your new MySQL Server on Port 3007 with http://localhost/phpmyadmin-3007 and your XAMPP MySQL still with http://localhost/phpmyadmin

glitzi

[dmphotography]

Wow,
I figured it was easy, but didn't expect it to be that simple.

Now I am having one small issue. I can access the SQL database XAMPP installed just fine, but the second database on port 3007 keeps giving me the following error:

phpMyAdmin - Error
#2003 - The server is not responding.

I tried uninstalling MySQL and deleting the data folders, then re-installing it and I'm still seeing this error. I did a search on it and none of the methods I found to fix it work. Any ideas?

Thank you very much by the way for simplifying that. It's so simple that I guess no one writes how to do it. Haha.

[dmphotography]

I solved my own question. Yay!

I noticed before in the my.ini file that is installed by the MySQL download that it mentions under both the client section and server section port = 3307. I thought that looked off, but never attempted to change it.

So what I did was go to C:/Program Files/MySQL/MySQL Server 5.1/bin and opened the my.ini file, then under [client] i changed the port=3307 to port=3007. I'm not sure this is required, but done so anyways.

Next I went to [mysqld] section and changed port=3307 to port=3007. I saved the file, then went to the "Services" and restarted the second instance of MySQL so the changes to the my.ini would be loaded. I was then able to log into the new database without any problems at all.

So there ya go.

It blows my mind how easy it is to waste so much time and hours upon hours trying to find how to do this and then discover it's just about one of the easiest configurations to set. Next time I won't hesitate to come here first if it's an issue that pertains to a component of XAMPP. Once again I'll say this has got to be the best support forum on the net.

[Izzy]

The normal MySQL port is 3306 and to change this say to 3007 then you need to edit the my.cnf file in the mysql\bin folder or it may look like a shortcut my file which you would drag into a text editor to read and edit.

BTW all your databases are stored in the mysql\data folder in folders under their own name.
You could perhaps copy the ones you want in to your other MySQL instance then run the mysql_update.exe file if required.

You posted while I was creating this, you fast thing.

[dmphotography]

LOL. Thank you for the additional info!

I am curious of one more thing pertaining to this and that is the Alias permission.

I'm wanting to know if you can assign an Alias to Allow from mydomain.com? I would like to be able to assign the second database to only be accessible via one domain and also do the same with the first. I tried setting it like that, but it gave me a 403 because I'm sure there's more to it than that.

I had it set to Allow from localhost so it can only be accessed from the server computer, but that's an inconvenience if you're not at the server PC when you wish to manage your database. And if it's set to "Allow from all", it would conflict with the second database unless I change the alias's name to something else. So what's the trick?

Oh, and one other thing, if I want to assign multiple allow from settings, do I do it like this?

Allow from localhost, myowndomain.com, anotherdomain.com

[Izzy]

Allow from localhost, myowndomain.com, anotherdomain.com

No comma separation.

http://httpd.apache.org/docs/2.2/mod/mod_authz_host.html#allow

http://httpd.apache.org/docs/2.2/mod/directives.html

You will find the Alias directive in the above also.

[dmphotography]

Hmmm, I'm still having 403 issues.

I have the following:

Alias /phpmyadmin "B:/xampp/phpMyAdmin/"
<Directory "B:/xampp/phpMyAdmin">
AllowOverride All
Order allow,deny
Allow from mydomain.com
</Directory>

And I also tried:

Alias /phpmyadmin "B:/xampp/phpMyAdmin/"
<Directory "B:/xampp/phpMyAdmin">
AllowOverride AuthConfig
Order allow,deny
Allow from mydomain.com
</Directory>

But I'm getting the 403 error every time I go to www.mydomain.com/phpmyadmin .

I snagged permissions to one of my vhosts config file and added this to it and it still made no difference:

Options +Indexes +FollowSymlinks Includes MultiViews Includes ExecCGI
AllowOverride All
Order allow,deny
Allow from mydomain.com

What am I missing?

[Izzy]

Any clue in the error.log file?

Are you enclosing your Alias in
<IfModule alias_module>
</IfModule>

You have cookie set in the config.inc.php file not config?

You don't have already an Alias for phpMyAdmin in the same conf file?

You restarted Apache after editing the conf file?

Not sure if phpMyAdmin can be accessed out side of localhost in XAMPP but I could be wrong and am checking.

Does it work if you use Allow from localhost ?

Try a .htaccess file in the phpMyAdmin folder
Allow from mydomain.com after returning the conf file to it's default.

[dmphotography]

Any clue in the error.log file?

[error] [client 192.168.1.1] client denied by server configuration: B:/xampp/phpmyadmin/

Are you enclosing your Alias in
<IfModule alias_module>
</IfModule>

I wasn't, but I went back and tried this and it gave me a 404 error then. From the link you gave me earlier, it says it's the mod_authz_host that resolves this, so I checked my httpd.conf file and made sure it was enabled after reading that link. When you mentioned the "IfModule" tags, I went back and tried <IfModule mod_authz_host> and <IfModule mod_authz_host.so> tags and neither one of them worked and both gave me 404 errors. I verified the file path and it is correct and works fine using Allow from localhost or Allow from 127.0.0.1, just not from the domain name.

You have cookie set in the config.inc.php file not config?

What?

You don't have already an Alias for phpMyAdmin in the same conf file?

Nope, I gave the second phpmyadmin the Alias /phpmyadmin2

You restarted Apache after editing the conf file?

A few times even, just to make sure it went through.

Not sure if phpMyAdmin can be accessed out side of localhost in XAMPP but I could be wrong and am checking.

Does it work if you use Allow from localhost ?

Yes, as I mentioned above. It works fine with localhost and 127.0.0.1.

Try a .htaccess file in the phpMyAdmin folder
Allow from mydomain.com after returning the conf file to it's default.

Does anything else need to be added to the .htaccess file besides the "Allow from mydomain.com"? I don't use .htaccess files because I read that it's not good to use a lot of them because it will hinder your server's performance, so if they're not already there, I leave them off. I use a index.php redirect file to send them back to the correct place if they stumble into a directory they're not suppose to be in.

[Izzy]

You did change the line:
$cfg['Servers'][$i]['host'] = 'localhost';
to reflect mydomain.com in the config.inc.php file?

The above line is in the config.default.php file in the libraries folder and would need to be copied into your config.inc.php file if not already there including the port line underneath.

Code: Select all

 * MySQL hostname or IP address
 *
 * @global string $cfg['Servers'][$i]['host']
 */
$cfg['Servers'][$i]['host'] = 'localhost';

/**
 * MySQL port - leave blank for default port
 *
 * @global string $cfg['Servers'][$i]['port']
 */
$cfg['Servers'][$i]['port'] = '';


You have cookie set in the config.inc.php file not config?

What?

/* Authentication type and info */
$cfg['Servers'][$i]['auth_type'] = 'cookie';

I don't use .htaccess files because I read that it's not good to use a lot of them because it will hinder your server's performance

That's is a myth unless you consider a .htaccess file over 1 meg in size containing spam blocking and other blocking rewrite rules that does not interfere with performance, along with many more .htaccess files on a server that has over 50 hosts on it all with .htaccess file pumping away - performance drop is negligible and immeasurable.

Anyhow, don't use a .htaccess file as it was only for testing this issue of yours.

[dmphotography]

You did change the line:
$cfg['Servers'][$i]['host'] = 'localhost';
to reflect mydomain.com in the config.inc.php file?

The above line is in the config.default.php file in the libraries folder and would need to be copied into your config.inc.php file if not already there including the port line underneath.

Code: Select all

 * MySQL hostname or IP address
 *
 * @global string $cfg['Servers'][$i]['host']
 */
$cfg['Servers'][$i]['host'] = 'localhost';

/**
 * MySQL port - leave blank for default port
 *
 * @global string $cfg['Servers'][$i]['port']
 */
$cfg['Servers'][$i]['port'] = '';


Yes, I did make sure this was set.

You have cookie set in the config.inc.php file not config?

What?

/* Authentication type and info */
$cfg['Servers'][$i]['auth_type'] = 'cookie';

I had it set to http, but I went ahead and changed it to cookie and it made no difference.

I don't use .htaccess files because I read that it's not good to use a lot of them because it will hinder your server's performance

That's is a myth unless you consider a .htaccess file over 1 meg in size containing spam blocking and other blocking rewrite rules that does not interfere with performance, along with many more .htaccess files on a server that has over 50 hosts on it all with .htaccess file pumping away - performance drop is negligible and immeasurable.

Anyhow, don't use a .htaccess file as it was only for testing this issue of yours.[/quote]

Was I correct on the content of the .htaccess file?

I am still getting the 403 error. I'm really puzzled by this because I cannot figure it out.
It would be one thing if it didn't work at all when using localhost, but the fact it does yet doesn't when setting it to Allow from domain.com puzzles me. I've tried a few different domain names too, as I have a few spare ones to play with and it just isn't making sense.

Have you tried this yourself and does it work for you???

[dmphotography]

Anyone have any other ideas why there is a permissions issue here? It works fine when set to Allow from all or Allow from 127.0.0.1, but it will not work with Allow from mydomain.com and I can't seem to find out why I'm getting the 403 for just this one.

[Izzy]

From the xampp\phpMyAdmin\libraries\config.default.php file near the top.

Code: Select all

/**
 * Your phpMyAdmin URL.
 *
 * Complete the variable below with the full URL ie
 *    http://www.your_web.net/path_to_your_phpMyAdmin_directory/
 *
 * It must contain characters that are valid for a URL, and the path is
 * case sensitive on some Web servers, for example Unix-based servers.
 *
 * In most cases you can leave this variable empty, as the correct value
 * will be detected automatically. However, we recommend that you do
 * test to see that the auto-detection code works in your system. A good
 * test is to browse a table, then edit a row and save it.  There will be
 * an error message if phpMyAdmin cannot auto-detect the correct value.
 *
 * @global string $cfg['PmaAbsoluteUri']
 */
$cfg['PmaAbsoluteUri'] = '';

This link on setting up Apache PHP MySQL etc (link) had the above under the phpMyAdmin Installation & Configuration which may help your case.

Perhaps also return your alias to the default Allow from all etc. if you set the above URI setting.

Looking at this error message:
[error] [client 192.168.1.1] client denied by server configuration: B:/xampp/phpmyadmin/

It would seem that the error message is telling that the error perhaps is in the configuration in B:/xampp/phpmyadmin/ not in the Apache httpd.xampp.conf file which points to the config.inc.php file and/or the config.default.php file - the latter is read first by phpMyAdmin then the former.

[dmphotography]

Thank you again very much Izzy for working with me on this. I will try what you said soon, but right now I am having trouble linking a vBulletin forum to the second database. It works fine when connected to the current one and I've successfully transferred the database for it to the other SQL database that's on port 3307.

I went into the config.php file for vBulletin and set the values for the new database, the port number from 3306 to 3307, the username root, the server localhost, and then the root password for the second database. But I keep getting a database error when I try accessing the forum.

The information is correct and when I change it back to the first database, it works fine. I tried exporting/importing the database again and it made no difference. All of the tables are present in the new SQL server too. I don't know where to find the error logs for MySQL, so I can't really troubleshoot it.

Any ideas anyone?