Koozali.org: home of the SME Server

Legacy Forums => Experienced User Forum => Topic started by: pete on January 30, 2004, 04:57:46 AM

Title: MySql - Remote access
Post by: pete on January 30, 2004, 04:57:46 AM: We are not using SME as a security device and currently have no need to restrict access to mysql, we are simply trying to pilot a replica of our win2k environment.

We need to be able to provide access to mysql so we can use things like SQLyog.

I have tried running the following 3 commands as found in the forum archives,

service mysqld stop
/sbin/e-smith/config setprop mysqld LocalNetworkingOnly no
service mysqld start

but the problem appears to be that the server is not listening on the 3306 tcp port (we have tpools to test this, and no response on this port. We are using SME 6.0 final

Could some one please explain what i need to do to allow access to thios port, even if i allow only the 2 local subnets?

EDIT: PS i have allowed all the subnets in the remote access server panel.
Title: heres what you need
Post by: Anonymous on January 30, 2004, 05:10:05 PM: /sbin/e-smith/config setprop mysqld LocalNetworkingOnly no
/sbin/e-smith/expand-template /etc/my.cnf
/etc/rc.d/rc7.d/S90mysqld restart

This is the fix but I have had to run the commands twice for some reason for it to work

In your post you didn't expand the template
Title: MySql - Remote access
Post by: pete on January 31, 2004, 05:25:50 AM: Thanks i tried exactly that and still no dice - i tried to connect remotely using root/pass contained in /root/.myconf
Title: MySql - Remote access
Post by: Boris on January 31, 2004, 08:47:40 AM: install phpmyadmin and check your accounts setup.
by default root is allowed to connect from localhost and IP of the server only. Don't mess with this, but create separate account for yourself@192.168% or whatever your local network is for daily use.
Title: MySql - Remote access
Post by: pete on January 31, 2004, 02:33:26 PM: I have added a user that has access from any host, but still dont work.

Should the username format in mymmyadmin be "username" in the user field and the "10.0%" in the hosts field? or all in the username field like "username@10.0%" ?

do i need to write the username as "username@10.0%" when trying to connect from my remote machine on 10.0.0.3 ? or just "username"?

Below is the monty account i created and the existing horde one, using "horde" with no pass gets me connected, using "monty" doesnt?

Edit Delete Grants % horde No No Privileges
Edit Delete Grants 10.0.% monty No Select Insert Update Delete Create Drop Reload Shutdown Process File Grant References Index Alter
Title: MySql - Remote access
Post by: pete on January 31, 2004, 02:39:54 PM: Chnage the user monty to have any host access and it works, will practise the restrictions later :)

thanks for the tips.
Title: MySql - Remote access
Post by: pabs on February 01, 2004, 12:18:24 AM: Hi all,

I’m also trying to connect to mySQL using SQLyog, will these techniques work on my SME 5.6?

Thanks
Title: MySql - Remote access
Post by: Anonymous on February 01, 2004, 12:59:48 AM: Seems to - the 2 things i hadnt done right are both listed above
1. Set mysql to listen to other hosts - i have nping whihc can ping tcp ports and tell ya if they are open, once opened...
2....all i had to was set the user account up correctly in phpmyadmin, its not secure at all my way, but it gives me access, which is all i wanted to migrate some DBs using SQLyog from a mysql win2k machine.
Title: MySql - Remote access
Post by: pabs on February 01, 2004, 05:35:32 AM: I’ve confirmed that 3306 is now open using LANguard port scanner, and I have a user set up with all privileges, host %. But when I try to connect with SQLyog it gives an error “Error No. 2013 Lost connection to MySQL server during query”

Any ideas?
Title: MySql - Remote access
Post by: Anonymous on February 01, 2004, 05:39:04 AM: I think that means you didnt save the details on the connection dialog?
Title: MySql - Remote access
Post by: pabs on February 01, 2004, 06:01:30 AM: That doesn’t seem to be the problem (I wish that fixed it :-) ).

I’ve set up the remote network via the server-manager like this:
Network = 192.168.1.1 Subnet mask= 255.255.255.0

192.168.1.1 is the local address of the SME

Could this be the problem?(I really don’t know how I should set remote network)
Title: MySql - Remote access
Post by: Boris on February 01, 2004, 08:48:24 AM: Quote from: "pabs"
I’ve confirmed that 3306 is now open using LANguard port scanner, and I have a user set up with all privileges, host %. But when I try to connect with SQLyog it gives an error “Error No. 2013 Lost connection to MySQL server during query”

Any ideas?

for version 5.6 you need to update glibc and glibc-common from the latest version for RH 7.3
This fix was discussed before.
Title: MySql - Remote access
Post by: pabs on February 03, 2004, 12:52:42 AM: I upgraded my glibc and glibc-common and SQLyog connects with no problems. Also, I tried using a SSH tunnel setup in PuTTY to access the DB with SQLyog from a remote site and it worked great (no need to setup a remote network anymore).

Thanks everyone for the help.
Title: MySql - Remote access
Post by: greavesg on February 03, 2004, 10:40:53 AM: :-(
I've just upgraded to e-smith 6 final UD and tried all of the tips in listed, to get mysql to work on the network work. I've even edit the my.cnf file "live" to comment out the skip-network, and restarted mysqld!
I'm pulling my hair out now. help!
Title: MySql - Remote access
Post by: Anonymous on February 03, 2004, 03:04:41 PM: Your desperate? yet you provide no details on exactly what problem your having?

Post your details, include any log messages you can find....
Title: MySql - Remote access
Post by: Anonymous on February 03, 2004, 05:53:27 PM: :oops:

Okay. I've upgraded from version e-smith 5.6 to version 6. MySQL was visable to local network so web software like ezpublisher (which uses MySQL) was able to connect using users I created under e-smith v5.6.
Since the upgrade, none of my web software which requires MySQL works, I get various errors from each installed product from "your database cannot be connected" to the "FATAL DATABASE ERROR: database cannot be reached". mysqlcc times out.

I'll seek out the error logs and upload them later.

From what I understand here in this forum the skip-network param in the my.cnf needs to be commented out or set to no. Isn't that what the lines below try to do?
/sbin/e-smith/config setprop mysqld LocalNetworkingOnly no
/sbin/e-smith/expand-template /etc/my.cnf
/etc/rc.d/rc7.d/S90mysqld restart

I did the above and when I checked the my.cnf file there was no change ie it still had the skip-network param, and yes I've rebooted.
Should i also do /sbin/e-smith/signal-event upgrade?
Title: MySql - Remote access
Post by: Anonymous on February 03, 2004, 06:57:02 PM: Hello

I posted the required commands on how to open access to mysql it is the second post with the 3 command lines

then I add a user at the console using the user add info I found at mysql docs site giving myself access from my computer ip address and mysqlcc works fine you can also install the phpmyadmin contrib and it also works fine by going to

https://yourserver.com/phpmyadmin or
https://###.###.###.###/phpmyadmin your ip number you have given the sme server

mysql adding users

http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Database_Administration.html#Adding_users
Title: MySql - Remote access
Post by: BrunoGarin on February 03, 2004, 07:04:19 PM: Quote from: "greavesg"
:-(
I've just upgraded to e-smith 6 final UD and tried all of the tips in listed, to get mysql to work on the network work. I've even edit the my.cnf file "live" to comment out the skip-network, and restarted mysqld!
I'm pulling my hair out now. help!

Have you try this:
Add in your etc/my.cnf under [mysqld]
set-variable = thread_stack=256K

before to put your hair out
Title: MySql - Remote access
Post by: Boris on February 03, 2004, 07:05:59 PM: Quote from: "greavesg"
:-(
I've just upgraded to e-smith 6 final UD and tried all of the tips in listed, to get mysql to work on the network work. I've even edit the my.cnf file "live" to comment out the skip-network, and restarted mysqld!
I'm pulling my hair out now. help!

Did you do:
service mysqld stop
/sbin/e-smith/config setprop mysqld LocalNetworkingOnly no
service mysqld start
?
Title: MySql - Remote access
Post by: TrevorB on February 03, 2004, 09:58:21 PM: Also note that connections to MySQL behave differently if you are running sme in server-gateway or server-only mode.

In server-gateway any normal user should be able to connect to your databases, in server-only mode you need to create a mysql user with rights on the database.

More info on your setup will help.....

Trevor B
Title: MySql - Remote access
Post by: greavesg on February 03, 2004, 11:15:36 PM: Here comes the info

Like v5.6 this mode is server-gateway. I have phpmyadmin installed as well as web mail and it all works fine. I have two network cards, the machine is an old PII running @ 300Mhz with 512Mb ram.
I'm registered with dyndns.org... any other info? feel free to ask...
Is there suppose to be a change in the my.cmf after issuing those commands mentioned by guest? When I look at the file nothing changes.
Title: MySql - Remote access
Post by: greavesg on February 03, 2004, 11:32:24 PM: :-D
Okay, seem like I've found the problem. I created a user called mysql with which I was trying to login to the db. When I altered the name to something else bada-bing I'm in. Thanx for your support.
Now all I need is is some superglue..... and :pint:
Title: Web site restrictions
Post by: Guest on February 23, 2004, 03:43:35 PM: :-? Is there a way of restricting user access to certain websites within SME Server 5.6?
Title: Help please with SQL!!!!
Post by: Pajmon on March 05, 2004, 12:43:44 PM: I have tried to do PHPFileExchange on SME 6.0 from
http://www.seattleserver.com/projects.htm site.

I think I configure this exchange corectly but i have an error when I try to connect with SQLSERVER remotly from other machine:
"Unable to connect to xxx.xxx.xxx.xxx Can't connect to MySQL server on 'xxx.xxx.xxx.xxx' (111)"
When I try to conect to this server from MySQLFront i can't either.
Can enyone tried this before. SME is SERVER-ONLY-MODE. I don't change anything in configuration.
I add sql user and configure him to be able to remote access->'user'@'%'. If I do wrong???
Any ideas???I am newbie with mysql so be patient:)
Thx for any reply and sorry for not my best language.
Title: MySql - Remote access
Post by: mike_mattos on March 05, 2004, 09:09:29 PM: I just wated a day trying to get MySQL to work on the network, gave up, UPGRADED to 4.0 and seem to have fixed the problems!

Follow the instructions , there is one bug,

rpm -e --nodeps mysql

seems to be needed!

APart from that, great