Trying to connect to MySQL from local program

[screech8780]

I am using DreamweaverMX2004, and am trying to set up a user registration for my site. I have been able to create it successfully on my local server, but when I attempt to make the connection to the database on the remote server, I get an error saying that I cannot connect... urrghhh! I have tried connecting the way I connect to plesk and still nothing....

Thanks...

 

[justyxxxx]

If it's a Plesk server running MYSQL, then you need to tell SQL to allow remote access to MYSQL from another server. Run the following on the Plesk MYSQL database server that you're trying to access remotely, changing the password, ip address, and username:

To allow certain ips access to MYSQL:

mysql -uadmin -p`cat /etc/psa/.psa.shadow` -Dmysql -e "insert into user values ('111.111.111.111', 'databaseusernamehere', password('databasepasswordhere'), 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N')"

and then run:

mysql -uadmin -p`cat /etc/psa/.psa.shadow` -e "flush privileges;"
(Note:_ The Y's and N's above indicate permissions)

 

[screech8780]

That is a bit confusing for me. I don't like attempting something that I am not familiar with... This is what I have going on... I am running Plesk Reloaded 7.5.1, it has ASP, PHP, MySQL, and I think there may be some other stuff too. I have access to the MySQL through phpadmin 2.6.0-pl3 and MySQL 3.23.58. Phpmyadmin is not giving me an option to set privledges, and when I try to connect remotely using port :8443, with my admin credentials, I get the following error:

A server timeout error has occured. Here are the possible reasons.
1.Please make sure the server is up and running.
2.Please verify that the ODBC DSN exists on the testing server.

It is probably something simple, but it has managed to elude me.

 

[justyxxxx]

I guess perhaps I don't understand your question. Basically, if you're trying to access MySQL on Plesk Server A from another computer, let's say Server B, you have to tell MYSQL on Plesk A to allow Server B's ip address access to MYSQL. That is what the command that I gave you does. If you're trying to access it locally, then you don't have to do this. Generally only local is allowed access to MYSQL unless you tell it otherwise. You cannot allow certain computers access to MYSQL via the Control Panel - not yet anyway. But, you can allow them access via command line, which is what the above command that I previously gave you does. Replace 111.111.111.111 with the address of Server B.

To see what ip addresses are allowed access to MYSQL, the following will give you all external IPs to which you gave MySQL access:
# mysql -uadmin -p`cat /etc/psa/.psa.shadow` -Dmysql -e"select Host from user where Host!='localhost';"

 

[Jllynch]

A program like webmin makes it easy to make these changes through its GUI so you don't have to issue that scary looking MySQL command above.

You can get it here;

www.webmin.com

 

[tobtob]

Thanks for posting the info on how to access mysql remotely.

In my case I need to grant access from "ANY" ip address but limited to a unique USERNAME and PASSWORD.

Can I simply replace the IP in your example with a % sign ?

Thanks,

 

[tobtob]

Ok....... I answered my own question. replacing the IP with a % sign does what I want.

 

[manarak]

the mySQL port is 3306

 

[Coldcutt]

Hi, sorry I am very new to all the command line stuff.
But where do I run the mysql command you mention from?

I have tried running it from the standard login location, and I am guessing it must be run from within a specific directory, is that right?

If so could anyone tell me the exact location?

Thank you kindly.

 

[justyxxxx]

I use Redhat Enterprise 3 and all I have to do is enter the command lines via a telnet, SSH or via the terminal after logging in as a user with the appropriate permissions (such as root, for example). I don't have to go to any specific directories to run them.

I accidentally included a # sign in the beginning of one of the command lines. You can delete the # sign at the beginning and proceed with entering mysql . . . . etc

 

[EvolutionCrazy]

or just install phpmyadmin and create users with access from % (instead of localhost) as you want

cd /home/httpd/vhosts/default/htdocs/
mkdir private
cd private
wget [url]http://heanet.dl.sourceforge.net/sourceforge/phpmyadmin/phpMyAdmin-2.6.2-pl1.tar.gz[/url]
gzip -d phpMyAdmin-2.6.2-pl1.tar.gz
tar -x --file=phpMyAdmin-2.6.2-pl1.tar
cd phpMyAdmin-2.6.2-pl1

then (change nano with vi or any other editor)

nano config.inc.php

and place there user e password that you are using to access plesk
(admin e password)

select also to protect it with auth or cookie method of course otherwise everybody can access to it and hack everything

then you are ready to administer your users/database from

http://yourip/private/phpMyAdmin-2.6.2-pl1/

 

[cancer10]

Make sure the server firewall is not blocking the mysql port.

 

[inc595]

The problem with creating users outside of Plesk is if you use psadump you will only backup those DBs made with Plesk. The command line is not scary once you make sense of what the command is trying to do. You can install your own phpMyAdmin on your site and login as your Plesk admin user. This will allow you to access all the databases and priviledges on the MySQL server. Rather then creating new users you can modify their privileges. Allowing only certain users access from outside helps in security. I use this for my config.inc.php for my phpMyAdmin to gain administrator level access to MySQL.

<?php
/* Servers configuration */
$i = 0;
/* Server localhost (http) [1] */
$i++;
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['extension'] = 'mysql';
$cfg['Servers'][$i]['connect_type'] = 'socket';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['auth_type'] = 'http';
/* End of servers configuration */
?>