MySQL Administrator

[vps-vince]

Hi all,
I want to start using the MySQL administrator
http://www.mysql.com/products/tools/administrator/
But cannot seem to get started as I keep getting a connot connect error 1045 which would indicate a wrong username or password.

Anybody else tried this tool with success?

Thanks,

- Vince

[Hvu]

Have you allowed your username to be connected via outside IP? Usually mysql only allows localhost. Change host to anyhost in phpmyadmin

[charles]

Mke sure you open port 3306 (both udp and tcp i think) in the firewall.

hth
charles

[vps-vince]

To Hvu:
it is already set to anyhost :confused:

To Charles:
Port 3306 is already open as I have Hyperspin monitoring service for MySQL.

Support replied the following.
The reason you are unable to connect is because the username 'root' is restricted to local access only by the MySQL server, because it is a security issue to do otherwise. I can enable this feature if you would like, but you must understand that it can potentially leave your databases open to prying eyes. Either way, if you'd still like me to enabled this feature, just let me know.

How worrying is this reply to users here?
I wanted to use MySQL Administrator to better monitor and understand what's going on with MySQL usage, as I find the SSH methods quite draconian - in the dark ages. :rolleyes:

Not sure what to do.

- Vince

[nadzri]

I'm not sure if it'll work for mysql administrator, but you can try port forwarding through ssh. You still have to set it up and connect via ssh etc, but there should be no "prying eyes".

[vps-vince]

MySQL Administrator also has advanced connection options for SSL and can use a named pipe instead of the TCP/IP port.
Would this last option be more secure, and how can we set this up?

Other than the above, how about installing the Linux version locally on my VPS?
http://dev.mysql.com/downloads/administrator/1.1.html

[charles]

I'm not sure if it'll work for mysql administrator, but you can try port forwarding through ssh. You still have to set it up and connect via ssh etc, but there should be no "prying eyes".

Yes this should work, and this is easy to setup with putty for example. I personally would not dream of opening up access without doing it over ssh/ssl.

charles

[vps-vince]

Support reply:
As far as using SSL and/or named-pipes, no, it won't necessarily be more secure. The simple fact that you've got MySQL allowing remote access is a very high security risk. By default, MySQL only listens locally. If, in the future, there is a MySQL exploit that is discovered, for the simple fact that your MySQL server is accepting remote connections, it will most likely be vulnerable.

I can see there point, and appreciate their concerns/best advice.
But how does everyone else use this and other popular tools (navicat) if such a security risk?

Dunno what to do now, but desperate to have a tool that gives me information on how my MySQL server is performing and views of any potential performance issues.

- Vince

P.S. Charles: ticket number is AKO-24450-737

[charles]

Vince, it is secure. Mysql should not be configured to allow remote connections. A connection over ssh comes from localhost. So disallow remote access, block 3306 on the firewall, and tunnel your program over ssh.

charles

[nadzri]

Solution I can think of right now:

1) open root access only to your ip. If you have a dynamic ip I guess you just have to work at it a bit more to change the ip root can connect from.
2) still firewall the mysql port, but allow your ip to pass through [apf -a your-ip].
3) when you're done, remove remote mysql access for root and ip from firewall, just to be safe.

W/o SSL you're still vulnerable to 'prying eyes' though.

[nadzri]

Hmm, I can't seem to edit my post...

Anyway, ssh tunnelling with putty doesn't seem to work for me. It seems to disconnect soon after and this is using mysql root user.

Vince, mytop seems a nice console program to monitor your server (http://jeremy.zawodny.com/mysql/mytop/).

[Zaf]

Vince, mytop seems a nice console program to monitor your server (http://jeremy.zawodny.com/mysql/mytop/).Is that url working?

[nadzri]

not with the closing parenthesis... :)

http://jeremy.zawodny.com/mysql/mytop/

[vps-vince]

Vince, it is secure. Mysql should not be configured to allow remote connections. A connection over ssh comes from localhost. So disallow remote access, block 3306 on the firewall, and tunnel your program over ssh.


Can someone help with telling me how to do the tunnel over ssh please?
I would really like to use this tool as many DB resources on the net reccomend it.

Has anybody here tried it yet?

Thanks,

- Vince

[netrider]

http://www.deftechgroup.com/wiki/index.php/VPS:HOWTO:Remotely_Administer_mySQL

[vps-vince]

http://www.deftechgroup.com/wiki/index.php/VPS:HOWTO:Remotely_Administer_mySQL

Thanks for the link, I had seen it and managed to get the tunneling to work, but still unable to get MySQL Administrator to connect.

must have wrong configuration setup for this app, so really looking forward to more info on that in point 2 of the Wiki :D

- vince

[oof]

I don't know MySQL Administrator but the main thing about configuring your local client is to set host to 127.0.0.1 (this stops it trying to use a socket).

Have you tried using the command line mysql client to test your SSH tunnel?
mysql -h127.0.0.1 -Pport -uusername -p

[Hvu]

I got it working, make sure your useraccount has the allowed hosts to be "%" rather "localhost"

http://img285.imageshack.us/img285/8462/mysqladmin4ak.jpg

[GalCom]

Hmm, well that seems to be doing something.. though I'm now getting MySQL Error 2013 - Lost connection to MySQL server during query... have tried logging in with both root and user mysql passwords with no luck :/

[oof]

I'd suggest the ssh tunnel rather than opening up your firewall to everyone...

Just set up MySQL Administrator myself and it works fine using host 127.0.0.1 port 3307 (I have a local mysqld on 3306). I really like the health monitoring graphs - could be a good alternative to mytop.

Only problem seems to be if you go to Service Control it drops the connection...

[vps-vince]

I don't know MySQL Administrator but the main thing about configuring your local client is to set host to 127.0.0.1 (this stops it trying to use a socket).

Have you tried using the command line mysql client to test your SSH tunnel?
mysql -h127.0.0.1 -Pport -uusername -p

No luck still, get this:

# mysql -h127.0.0.1 -Pport -uusername -p
Unknown suffix 'p' used for variable 'port' (value 'port')
mysql: Error while setting value 'port' to 'port'

[rp9]

oof, are you using the ssh tunnel client or going through putty?

Through putty following the set-up here:
http://www.vbmysql.com/articles/security/gui-tunnel.html

I can get MySQL Error 2013 - Lost connection to MySQL server during query.

Using the ssh tunnel I can't get anywhere. If you're using that can you please let us know what settings you have for your tunnel and also in mysql administrator (the info in the wiki seems to get cut off.) (ie, any advanced connection options?)

TIA,
Rich

[rp9]

Have now got the MySQL Error 2013 - Lost connection to MySQL server during query error via the ssh tunnel client as well. I had to change the setting Forward to "local to remote".

I've the access hosts at the remote end set to %

[oof]

mysql: Error while setting value 'port' to 'port'Vince - port should be 3307 (or whatever number you set up for the local end of the ssh tunnel). If you don't have a local copy of mysqld running you can forward 3306 and you don't need to specify a port at all, ie.
mysql -h127.0.0.1 -uroot -p if you don't have local mysqld, or
mysql -h127.0.0.1 -P3307 -uroot -p if you do.

Rich - I'm using putty, set up exactly as that article. No advanced setup, no access hosts changes (because Administrator doesn't know it's not talking to a local mysql server, likewise the VPS mysql server thinks it's talking to a local client... Does that make sense?).

Maybe these will help:
http://img24.imageshack.us/img24/1225/putty0ns.png
http://img24.imageshack.us/img24/2226/mysqladmin2qm.png

[rp9]

oof, thanks that makes sense.

I'm still getting the lost connection to server error.

edit 1: I did upgrade putty to 0.58...but that didn't chage things :|

edit 2: I've tried this on my other server and connected instantly so I think I've now got my side set up at least.



(heh, when I click on the right ok button and not on your screenshot doh! :rolleyes: )

Cheers,
Rich

[oof]

Ok - server issue then. I guess you don't have "skip-networking" in my.cnf or you wouldn't connect at all. I'm using 4.1.14 standard - don't know if that would make a difference.

[rp9]

I'm using a nearly empty my.cnf (that's my next task!) and MySQL 4.0.23-standard

(the server I can connect to has the same my.cnf and uses 4.0.22-standard)

[rp9]

Solved it.

In /etc/ssh/sshd_config I changed:
AllowTcpForwarding no

to
AllowTcpForwarding yes

restarted sshd and I got connected fine.

Are there any implications of setting this that I should be aware of?

Cheers,
Rich