Connect to a mySQL Local server?

[Gerardo_Garca]

Hi all!

Today I finally setup my local web server with Apache2, PHP 7.0 and mySQL using Ubuntu 16.04
I set up the internal IP of that server to:

IP Adress: 192.168.0.63
Subnet Mask: 255.255.255.0
Gateway: 192.168.0.1

I made the appropriate Tests like run in another machine of the network on a browser:
http://192.168.0.63 in order to test Apache and Says “It Works”

Also I installed phpmyadmin, and test:
http://192.168.0.63/phpmyadmin
And Shows the webpage successfully

Also I installed Webmin (like CPanel) in order to manage services like samba sharing folders and another stuff
And installed mySQL Workbench, in order to manage Schemas and databases.

So the idea to achieve is this, Access to that mySQL Server in every machine of the local network.

All this Time I worked with mySQL running on my personal computer and I access by this way:

app.mdb = New MySQLCommunityServer

app.mDb.Host = “localhost”
app.mDb.UserName = “root”
app.mDb.Password = “heremypassword”

//Si se conecta a mySQL correctamente, entonces
If app.mDb.Connect Then
Msgbox “Si me pude conectar”
//Si creamos la base de datos
app.mDb.SQLExecute(“CREATE DATABASE IF NOT EXISTS salesdepartment”)

//Usamos la Base de datos salesdepartment
app.mDb.SQLExecute("use salesdepartment")
//Configuramos el tipo de entrada como UTF8 para ingresar caracteres y acentos correctamente
app.mDb.SQLExecute ("SET NAMES 'utf8'")
'EstatusConexion.Text = "Conectado a mySQL"

if app.mDb.Error then
  
end

Else
Msgbox “No me he podido conectar”
end

I tried to change localhost to 192.168.0.63 in order to access to my server, and it not connects.

What should I’m doing wrong?
Thanks

[Bob_Keeney]

Check the app.mdb.error and error message. It might tell something more.

I suspect you haven’t set up the security for that user and allow it access from the subdomain. This is a mySQL issue.

[Tomas_J]

By design no login from remote is allowed, so you first need to create a new user or to setup your mysqlserver properly. Without any claim to completeness here’s a step-by-step list you should check:

1. If you have webmin than you login on your server (i think default port is 10000 for webmin) should first check your webmin interface if mysql daemon/ server is up and running.
2. you should check your credentials, in most cases mysql root is different than your host root
3. create a new database
4. create a new dbuser within mysql and give this user priviledge to login from remote hosts

[Gerardo_Garca]

I checked the app.mdb.error and thats what it said: “Cant Connect to MySQL server on ‘192.168.0.63’ (61)”

[Jukka_Leino]

In my.cnf file theres line:
bind-address=YOUR-SERVER-IP
Try comment that out and restart mysqld

[Gerardo_Garca]

[quote=319682:@Jukka Leino]In my.cnf file theres line:
bind-address=YOUR-SERVER-IP
Try comment that out and restart mysqld[/quote]

Ok, I’ll try that,I thought that mySql isn’t running,i checked it doing:

mysqladmin -p -u root status

And I got:

Enter password: Uptime: 8205 Threads: 1 Questions: 1941 Slow queries: 0 Opens: 1059 Flush tables: 1 Open tables: 127 Queries per second avg: 0.236

So i got 2 questions:

1. will be like this: #bind-address=192.168.0.63
2. When I setup mysql server connection on Workbench on the server, I choose localhost, cuz i tried to put the IP: 192.168.0.63
   But I can’t.

Thanks in advance

[Jukka_Leino]

I think it’s
bind-address=127.0.0.1
and that you should comment out with “#”
bind-address allows connection only from localhost by default.

And make user with:
GRANT ALL PRIVILEGES ON yourdb.* TO ‘youruser’@‘192.168.0.%’;
so it allows connection from your local network to yourdb database with youruser…

[Tim_Hare]

Create a new user. As a security measure, by default ROOT is not accessible from the network. I would recommend leaving it that way.

[Gerardo_Garca]

[quote=319689:@Jukka Leino]I think it’s
bind-address=127.0.0.1
and that you should comment out with “#”
bind-address allows connection only from localhost by default.

And make user with:
GRANT ALL PRIVILEGES ON yourdb.* TO ‘youruser’@‘192.168.0.%’;
so it allows connection from your local network to yourdb database with youruser…[/quote]
I do that.

I made a new user, different that root assign the privileges
But Still without success even playing with bind-address and skip networking hashing it.

Without success, I was about to give up, at then Playing with WEBADMIN I found this:

MySQL Database Server menu -> MySQL Server Configuration ->MySQL Listening Address And check this option instead of “Any”
And I got how its option are activated, As Jukka said Only need to assign permissions to certain IP, but don’t mess with “#skip-networking”

I’ve read in other forums that this option are only for Localhost access, not remote.

So then Run my Xojo and says Ok, I can access to my database!!!

Regards and thanks all

[Phillip_Zedalis]

In MySQL make sure the username is “user@%” for any IP address. I think they default to “user@localhost”

Also you may need to run “FLUSH PRIVILEGES” after changing users/permissions.