[Expert] Distant connection MYSQL DB

[Pseudonyme]

Hi all,

I try to connect from one server website (SER1) to another server
Mysql Database (SER 2).

http://kb.mediatemple.net/questions/264/Setting+up+external+MySQL+connections
http://ubuntuforums.org/showthread.php?t=660811
http://dev.mysql.com/doc/refman/5.1/en/windows-and-ssh.html
http://www.debianhelp.co.uk/remotemysql.htm

Linux 2.6.18 and MySQL 5

On SER2 where the mysql server is installed I edited

vi /etc/mysql/my.cnf
and changed
bind-address = 172.20.5.2 (the SER 1 I.P.)

The point is MySQL refuse to restart. It stops fine, but FAIL to start
because of that line added.

>>> How to connect to Mysql SER 2 from another server ?

I initially thought that it had to deal with PHP.INI but probably
that's actually not the case.

Thank you for any info from your experience and kl.

Norman

[Robert Gonzalez]

So adding the bind-address directive causes mysql to not restart? Have you looked at you mysql logs? Were you root when you tried to restart?

--
This group is managed and maintained by the development staff at 360 PSG. An enterprise application development company utilizing open-source technologies for todays small-to-medium size businesses.

For information or project assistance please visit :
http://www.360psg.com

You received this message because you are subscribed to the Google Groups "Professional PHP Developers" group.
To post to this group, send email to Professi...@googlegroups.com
To unsubscribe from this group, send email to Professional-P...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/Professional-PHP

[Pseudonyme]

Dear Sir : The bind-address directive in that file /etc/mysql/my.cnf
causes mysql to not restart when i was logged with root.

And in ssh, when i do the command mysql to use mysql using ssh, I
have :
[root@dedicated ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
password: NO)

I've checked that the user root in the mysql table had all privileges
required and I flushed again privileges and mysql rebooted.

As consequences :
1/ I cannot log in Mysql from another server.
2/ I cannot work with Mysql using SSH and all Cron automatic events
playing with mysql cannot be played any more. Which is quite dramatic.

Linux 2.6.18-164.el5 and Mysql 5 (2009)
Thank you very much for your information,

Norman




On 8 sep, 18:20, Robert Gonzalez <robert.anthony.gonza...@gmail.com>
wrote:

> So adding the bind-address directive causes mysql to not restart? Have you
> looked at you mysql logs? Were you root when you tried to restart?
>
>
>
> On Wed, Sep 8, 2010 at 7:02 AM, Pseudonyme <normancougl...@gmail.com> wrote:
> > Hi all,
>
> > I try to connect from one server website (SER1) to another server
> > Mysql Database (SER 2).
>

> >http://kb.mediatemple.net/questions/264/Setting+up+external+MySQL+con...

[Jack Timmons]

I'm not sure if I can help you, or complain you're asking MySQL
questions on a PHP list.

If you're trying to access MySQL remotely (IE: Website is on one
server, DB is on another), you don't need any funky binding of
addresses. Bind the address as you normally would, open up the port,
and connect to it.

--
Jack Timmons
@_Codeacula

[Pseudonyme]

Dear Jack : Apologizes for using this group where I am used to work
this. I searched a Professional+Mysql+Developers group, and did not
find out. http://groups.google.fr/groups/dir?lnk=srgmt&hl=fr&q=Professional+Mysql+Developers

We cannot open a port with iptables : it is forbidden by the hosting
company governance guidelines. That's the issue. We must connect to
Mysql from an external server using standard methods like a little
change in PHP.INI or MY.CNF.

Logged as "Root" typing the command mysql to interact with the mysql
databases from SSH does not work either.

Thank you Jack.




On 9 sep, 13:03, Jack Timmons <codeac...@codeacula.com> wrote:

> On Wed, Sep 8, 2010 at 9:02 AM, Pseudonyme <normancougl...@gmail.com> wrote:
> > Hi all,
>
> > I try to connect from one server website (SER1) to another server
> > Mysql Database (SER 2).
>

> >http://kb.mediatemple.net/questions/264/Setting+up+external+MySQL+con...

[Jack Timmons]

On Thu, Sep 9, 2010 at 6:21 AM, Pseudonyme <normanc...@gmail.com> wrote:
>
> Dear Jack : Apologizes for using this group where I am used to work
> this. I searched a Professional+Mysql+Developers group, and did not
> find out. http://groups.google.fr/groups/dir?lnk=srgmt&hl=fr&q=Professional+Mysql+Developers
>
> We cannot open a port with iptables : it is forbidden by the hosting
> company governance guidelines. That's the issue. We must connect to
> Mysql from an external server using standard methods like a little
> change in PHP.INI or MY.CNF.
>
> Logged as "Root" typing the command mysql to interact with the mysql
> databases from SSH does not work either.
>
> Thank you Jack.

If you're using shared hosting, good luck.

Your MySQL command wasn't typed in correctly. Specify a username and
password when you do it. Use the help parameter to figure out what
you're doing wrong.

You can always open an external port and redirect it to the correct
one. Or, as I said, if you're on shared hosting, good luck.

Also: http://forums.mysql.com/

I don't fully understand or realize what you're trying to accomplish.
To me, it sounds like you're saying "I'm trying to connect to MySQL
remotely." If that's the case and you're on shared hosting that blocks
it, you're more than likely not going to be able to do it at all. When
you say "I'm trying to connect to a MySQL server from an external
server", you're just telling me that exact thing: that you're trying
to connect remotely.

And unless there's some killer voodoo I haven't seen practiced, yet,
you're likely not going to be able to do it and simply need to cough
up the cash to get a server you can manage yourself, or create an API
for your website the interact with.

--
Jack Timmons
@_Codeacula

[Pseudonyme]

Hi Jack,
I'm trying to connect to MySQL remotely using 2 dedicated servers :
one where the website is online, and a second one where the mysql DB
is hosted.
We know there are many groups and forums with hundred of passwords and
emails, but we prefer the Goog groups.

1/ Our Cron script managing Mysql are made for an SSH ROOT without
password. A master ROOT who types mysql in ssh and the mysql command
lines are alive. Having a password for ROOT is not an option because
none of our scripts would work any longer.
We tryed to delete the ROOT user in Mysql. We tryed to insert a ROOT
user in Mysql w/o password : DID NOT WORK AT ALL TO GET RID OF THE
F***K PASSWORD FOR ROOT IN SSH WHILE USING MYSQL.

2/ Connect remotely did not work. We read 5-6 times your kind
messages, and we said "Bind the address as you normally would, open up
the port". That one, we did not get it. Searched throughout the web,
and could not retrieve the 2-3 lines to insert in a specific file.

$host = "207.24.25.26";
$user = "Jack07";
$pass = "777111";
$bdd = "hinboxcom";

Did not work from SER 1 website hosted to MySql hosted on SER 2

Thank you Jack.





On 9 sep, 13:32, Jack Timmons <codeac...@codeacula.com> wrote:

> On Thu, Sep 9, 2010 at 6:21 AM, Pseudonyme <normancougl...@gmail.com> wrote:
>
> > Dear Jack : Apologizes for using this group where I am used to work
> > this. I searched a Professional+Mysql+Developers group, and did not

> > find out.http://groups.google.fr/groups/dir?lnk=srgmt&hl=fr&q=Professional+Mys...

[Jack Timmons]

On Thu, Sep 9, 2010 at 6:54 AM, Pseudonyme <normanc...@gmail.com> wrote:
>
> Hi Jack,
> I'm trying to connect to MySQL remotely using 2 dedicated servers :
> one where the website is online, and a second one where the mysql DB
> is hosted.
> We know there are many groups and forums with hundred of passwords and
> emails, but we prefer the Goog groups.
>
> 1/ Our Cron script managing Mysql are made for an SSH ROOT without
> password. A master ROOT who types mysql in ssh and the mysql command
> lines are alive. Having a password for ROOT is not an option because
> none of our scripts would work any longer.
> We tryed to delete the ROOT user in Mysql. We tryed to insert a ROOT
> user in Mysql w/o password : DID NOT WORK AT ALL TO GET RID OF THE
> F***K PASSWORD FOR ROOT IN SSH WHILE USING MYSQL.
>
> 2/ Connect remotely did not work. We read 5-6 times your kind
> messages, and we said "Bind the address as you normally would, open up
> the port". That one, we did not get it. Searched throughout the web,
> and could not retrieve the 2-3 lines to insert in a specific file.
>
>        $host     = "207.24.25.26";
>        $user     = "Jack07";
>        $pass     = "777111";
>        $bdd      = "hinboxcom";
>
> Did not work from SER 1 website hosted to MySql hosted on SER 2

Who is your hosting provider?

--
Jack Timmons
@_Codeacula

[Pseudonyme]

> Remote connection was working in our previous installation.
> Connect to Mysql from SSH using root with NO password was working fine as well.

The hosting cpy has been sold, sold, sold and resold ... the original
one is Valueweb, name we still have on our invoices : http://www.valueweb.com

As we want to
> Connect remotly to Mysql from another server
> Use root in SSH and access mysql with no password.

Thank you,


On 9 sep, 14:31, Jack Timmons <codeac...@codeacula.com> wrote:

[Pseudonyme]

From SSH :

[root@dedicated cron-scri]# sh /etc/script/01_cpm
: No such file or directoryline 1: /etc/script/06_cpm.sql

[root@dedicated cron-scri]# more /etc/script/01_cpm
mysql --user=root --password=YYY222 --database=Bonjour_prod < /etc/
script/06_cpm.sql

Our Mysql scripts do not work anymore. When sh the listed script ...
it cannot connect to mysql has it did for years.

[Pseudonyme]

Also tried to be 100% clear regarding 2 tough problems :
http://forums.mysql.com/read.php?10,384786,384786#msg-384786
Thank you very much for your help

Norman

[Robert Gonzalez]

Can I be the first to say HOLE. E. SH!T. You want to allow remote connections to your database using the ROOT user WITHOUT a freaking password?!?!?! Are you serious? Seriously?

On a side note, commenting out the bind-address directive in my.cnf and restarting mysqld typically allows remote connections. But please, for the love of all things Timmons, DO NOT ALLOW REMOTE ACCESS TO

[Robert Gonzalez]

As I was saying...

DO NOT ALLOW REMOTE ACCESS TO YOUR DATABASE SERVER WHERE YOUR ROOT USER HAS NO PASSWORD. EVER. UNLESS YOU DO NOT VALUE YOUR DATA, YOUR SECURITY OR YOUR REPUTATION AT ALL.

I've said my peace. Carry on.

Robert

[Pseudonyme]

Robert : under ENSIM, as you are already connected to SSH, you don't
need to connect again to Mysql.
Connected to SSH, you can manage Mysql with no password prompt.

The story is more : how to remotely connect.

And that one should work and does not work probably due to the
password problem :

sh /etc/script/01_cpm

Result :

: No such file or directoryline 1: /etc/script/06_cpm.sql

[Robert Gonzalez]

Look closely at your output...

You are calling /etc/script/01_cpm but the file it is trying to find is /etc/script/06_cpm.sql, which it is telling you is not found. Even if you were able to connect you wouldn't be doing anything since there is no file to parse.

Check to make sure the file is there.

On Thu, Sep 9, 2010 at 10:28 AM, Pseudonyme <normanc...@gmail.com> wrote:

And that one should work and does not work probably due to the
password problem :

sh /etc/script/01_cpm

Result :

: No such file or directoryline 1: /etc/script/06_cpm.sql

more /etc/script/01_cpm
mysql --user=root --password=YYY222 --database=Bonjour_prod < /etc/
script/06_cpm.sql

[Pseudonyme]

This is the SSH exact session

login as: root

ro...@207.36.196.230's password:

[root@dedicated ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
password: N
O)

[root@dedicated ~]# sh /etc/cron-scri/script_bonjour
: No such file or directoryline 1: /etc/cron-scri/sql-cpm/cpm_1h.sql

[root@dedicated ~]# more /etc/cron-scri/script_ bonjour
mysql --user=root --password=M5D16G4 --database=Aglond_prod < /etc/
cron-scri/sql-cpm/cpm_1h.sql

[root@dedicated ~]# more /etc/cron-scri/sql-cpm/cpm_1h.sql
INSERT INTO … blablabla (the whole sql script)

So, the script is there. But sh /etc/cron-scri/script_bonjour Does
not want to work. We are guessing that's because SSH cannot connect to
mysql (ERROR 1045).

Thank you for your help;

Norman

[Robert Gonzalez]

From the looks of your scripts I'd say there is a root password set. At the command line try:

[root@dedicated ~]# mysql -h localhost -u root -p <ENTER_THE_PASSWORD_FROM_THE_SCRIPT_HERE>

See if that allows a connection.

If not, try

[root@dedicated ~]# mysql -h localhost -u root

And see if that does it.

--

[Pseudonyme]

Robert : we cannot run the mysql query directly without password for
Root in SSH. That destroys everything.
ENSIM + Linux was totally fine.
PLESK + Linux destroys all our script. Not possible to connect to
mysql directly from SSH and have CRON running the scripts.

The hosting company says :


I'm afraid that, ultimately, your database administrator and/or
webmaster will need to ascertain local connectivity to mysql first,
then manage remote access to these resources. The service is running
and the port is open, so if a proper call with the correct credentials
is made you should not experience any issues.

One thing to note, if you are managing your mysql from within plesk
you will need to make sure to use the credentials you provided in
plesk.

>> That does not help me at all... so shitty, terrible situation for us



THIS. this does not work : sh /etc/cron-scri/script_bonjour

[root@dedicated ~]# sh /etc/cron-scri/script_bonjour
: No such file or directoryline 1: /etc/cron-scri/sql-cpm/cpm_1h.sql

[root@dedicated ~]# more /etc/cron-scri/script_ bonjour
mysql --user=root --password=M5D16G4 --database=Aglond_prod < /etc/
cron-scri/sql-cpm/cpm_1h.sql
[root@dedicated ~]# more /etc/cron-scri/sql-cpm/cpm_1h.sql
INSERT INTO … blablabla (the whole sql script)

Thank you for any help.

[Robert Gonzalez]

What that tells me is that Ensim was doing something for you behind the scenes that Plesk is not doing. I'm guessing the control panel created something somewhere that handled the authentication to your database for you. Sorry, I can't help with that.

The rest of your issues can be investigated though, if you have access to your machine using SSH. Can you SSH to the box?

--