Koozali.org: home of the SME Server

Obsolete Releases => SME Server 7.x => Topic started by: crackerboy on July 29, 2007, 11:41:00 PM

Title: External access MySQL
Post by: crackerboy on July 29, 2007, 11:41:00 PM: I have several forums that i host some for me and some for my friends and i will go for a solution with 3 servers.2 webservers with one backup webserver and SME that have the database, this is because of security also.

How can i allow external access to MySQL?
I have tried many commands but none of them work
I have searched the forum but those comands as i said didnt work.
Title: External access MySQL
Post by: byte on July 29, 2007, 11:46:14 PM: Did you have a look at the wiki ?

http://wiki.contribs.org/MySQL
Title: External access MySQL
Post by: crackerboy on July 29, 2007, 11:56:26 PM: OK wich port is the MySQL listen on then i tried 3306 in telnet but that didnt do the trick but i see i used the right command though
Title: External access MySQL
Post by: raem on July 30, 2007, 02:30:47 AM: crackerboy

you also need to do

config set mysqld service access public status enabled TCPPort 3306
signal-event remoteaccess-update
signal-event reboot

I updated the Wiki

Read the warning I added which says:
Keep in mind this enables access to your MySQL database for ANYONE, so make sure you have strong passwords on ALL your MySQL databases. Alternatively it would be a more secure approach to require external (remote) users to establish a VPN connection and effectively become part of the local network. In that case do not change the mysql access to public status using the above command.
Title: External access MySQL
Post by: crackerboy on July 30, 2007, 06:43:52 PM: Host 192.168.0.20(which is in my green Zone on the firewall,SME is on the Orange zone or DMZ) is not allowed to connect to this MySQL server i get when i am trying these commands and /etc/rc.d/init.d/mysqld restart dont restart the MySQL server but starts at boot.

What i am missing here?
Title: External access MySQL
Post by: Stefano on July 30, 2007, 07:07:13 PM: Quote from: "crackerboy"
Host 192.168.0.20(which is in my green Zone on the firewall,SME is on the Orange zone or DMZ) is not allowed to connect to this MySQL server i get when i am trying these commands and /etc/rc.d/init.d/mysqld restart dont restart the MySQL server but starts at boot.

What i am missing here?

hi..

so, you've done
Code: [Select]
config setprop mysqld LocalNetworkingOnly no config set mysqld service access public status enabled TCPPort 3306 signal-event remoteaccess-update signal-event reboot ?

how are you trying to connect to mysql on sme?

have you tried to install phpmyadmin contrib?

are you sure the db are you connecting to and the username you are using to connect are correct and have the rights to do? I mean, if the user is granted only from localhost, if you try to connect from another host you get an error..

my 2c

Ciao

Stefano
Title: External access MySQL
Post by: raem on July 30, 2007, 07:07:49 PM: crackerboy

> Host 192.168.0.20 is not allowed to connect to this MySQL server...
> What i am missing here?[/quote]

You also need to add suitable passwords and users for each mysql database you wish to access. You need to specify the host & user you are wanting to connect from when you setup the privileges in mysql.

I suggest you read the users & password sections of the mysql manual online at http://www.mysql.org
eg
http://www.mysql.org/doc/refman/4.1/en/adding-users.html
You usually need to add both the user@localhost and user@% users.

Also search these forums on "grant all privileges" for numerous examples.
Title: External access MySQL
Post by: cactus on July 31, 2007, 04:32:06 PM: Quote from: "RayMitchell"
You usually need to add both the user@localhost and user@% users.
That is only true if you want to access the system from remote hosts and localhost under the same username, otherwise one of them would be enough.
Title: External access MySQL
Post by: raem on August 01, 2007, 02:11:12 AM: cactus

Quote
That is only true if you want to access the system from remote hosts and localhost under the same username, otherwise one of them would be enough.

As I understand it, for localhost access you only need to use
user@localhost

but if you want remote host access, then you need to use both
user@localhost and user@%

(or a specific host address ie user@IP)

I did say usually, and the subject was remote host access.
Title: External access MySQL
Post by: crackerboy on August 04, 2007, 05:50:03 PM: Code: [Select]
config setprop mysqld LocalNetworkingOnly no config set mysqld service access public status enabled TCPPort 3306 signal-event remoteaccess-update signal-event reboot

That didnt do the trick and we need this to transfer the databases from SME into a Debian Server.Phpmyadmin get a bunch of errors and maybe its because debian have newer PHP and MySQL,i dont know.My friend use MySQL Admin and isnt so glad in phpmyadmin and not after this with errors.

Can someone help us so we can transfer the databases?

I tried this one also as it was in the MySQL manual for SME

Code: [Select]
config set mysqld service access public status enabled TCPPort 3306 signal-event remoteaccess-update signal-event reboot
Title: External access MySQL
Post by: crackerboy on August 04, 2007, 06:08:07 PM: it worked if i use /etc/rc.d/rc7.d/S50mysqld restart
Title: External access MySQL
Post by: pfloor on August 04, 2007, 06:50:21 PM: Quote
Can someone help us so we can transfer the databases?

Did you try?:

Code: [Select]
mysqldump database_name > database_name.sql

Then copy the dump to new server and import it:

Code: [Select]
mysql < database_name.sql

This should work with different MySQL versions. You should consult the manual about the "compatable" option if the machine you are restoring it on has MySQL earlier than 4.1.2.
Title: External access MySQL
Post by: crackerboy on August 05, 2007, 06:20:17 PM: it went well my friend did make a scipt to import so now its up and running.Thanks guys :D