| View previous topic :: View next topic |
| Author |
Message |
ONEEYEMAN
Advocate
Joined: 01 Mar 2005
Posts: 3678
|
 Posted: Tue Dec 20, 2022 3:37 pm Post subject: Allow remote access to mySQL |
 |
|
Hi, ALL,
I'm trying to give my mySQL server remote access FROM MY NETWORK machines.
I am using KDE with OpenRC fresh install and set up server there.
Checked the /etc/mysql/my.cnf - it contained only one line
Checked /etc/mysql/mysql.d/50-distro-server.cnf - found the "Bind Address" option, but its not for OpenRC.
What are my options?
Thank you. |
|
| Back to top |
|
 |
grknight
Retired Dev
Joined: 20 Feb 2015
Posts: 2173
|
| Posted: Tue Dec 20, 2022 3:57 pm Post subject: |
|
|
In a file such as /etc/mysql/mysql.d/server.cnf, put:
| Code: |
[mysqld]
bind-address = 0.0.0.0
skip_name_resolve
|
That gives networking access.
Then create users in the server such as "exampleuser"@"192.168.%" |
|
| Back to top |
|
|
ONEEYEMAN
Advocate
Joined: 01 Mar 2005
Posts: 3678
|
| Posted: Tue Dec 20, 2022 4:26 pm Post subject: |
|
|
Hi,
Here are the list of configuration files.
I presume I should use the first one?
And I don't need "0.0.0.0" - I can live with "192.168.1.0". I think this should be fine, right?
Thank you. |
|
| Back to top |
|
|
alamahant
Advocate
Joined: 23 Mar 2019
Posts: 3961
|
| Posted: Tue Dec 20, 2022 4:27 pm Post subject: |
|
|
| Quote: |
Checked /etc/mysql/mysql.d/50-distro-server.cnf - found the "Bind Address" option, but its not for OpenRC.
|
Why not?
Simply change it to your mariadb host ip like
| Code: |
bind-address = 192.168.44.32
|
A better way is to use ssh local port forwarding from the client machines to the server like
| Code: |
ssh -NL 3306:127.0.0.1:3306 root@<mysql-server>
|
you will need to allow port forwarding in sshd_config of server.
_________________
Last edited by alamahant on Tue Dec 20, 2022 4:36 pm; edited 1 time in total |
|
| Back to top |
|
|
grknight
Retired Dev
Joined: 20 Feb 2015
Posts: 2173
|
| Posted: Tue Dec 20, 2022 4:35 pm Post subject: |
|
|
| ONEEYEMAN wrote: | Hi,
Here are the list of configuration files.
I presume I should use the first one?
And I don't need "0.0.0.0" - I can live with "192.168.1.0". I think this should be fine, right?
Thank you. |
No, a bind address is not a mask. It must be 0.0.0.0 (for all addresses) or exist as an address on a NIC. |
|
| Back to top |
|
|
ONEEYEMAN
Advocate
Joined: 01 Mar 2005
Posts: 3678
|
| Posted: Wed Dec 21, 2022 3:15 pm Post subject: |
|
|
Hi,
| grknight wrote: | In a file such as /etc/mysql/mysql.d/server.cnf, put:
| Code: |
[mysqld]
bind-address = 0.0.0.0
skip_name_resolve
|
That gives networking access.
Then create users in the server such as "exampleuser"@"192.168.%"
|
I added this portion to my.cnf and still can't connect.
In the past (with 5.x series) one had to grant privileges to the user to connect remotely.
I presume this is still the case?
Thank you. |
|
| Back to top |
|
|
Hu
Administrator
Joined: 06 Mar 2007
Posts: 23471
|
| Posted: Wed Dec 21, 2022 4:32 pm Post subject: |
|
|
As always when debugging an error, specifics are important. In what way can you not connect? Is the server refusing the TCP connection? Is it accepting the connection, but refusing to allow you to authenticate? Is it allowing you to authenticate, but then refusing to serve any SQL queries?
Yes, MySQL treats the origin host as part of the account data, so a grant for me@localhost will not allow me from a remote machine. The specific output on failure should tell us whether this is your problem. Also, please provide the SHOW GRANTS for whichever user you expect the remote machine to use. |
|
| Back to top |
|
|
szatox
Advocate
Joined: 27 Aug 2013
Posts: 3650
|
| Posted: Wed Dec 21, 2022 6:10 pm Post subject: |
|
|
For completeness, it might not even allow you to connect at 127.0.0.1, since @localhost in mysql terminology refers a unix socket interface and not the local loopback.
"Might not" rather than "will not" because I'm not quite sure it is a definitive answer; I think there was some configuration option which influences this behavior but it's a very vague memory so take it with a grain of salt. |
|
| Back to top |
|
|
ONEEYEMAN
Advocate
Joined: 01 Mar 2005
Posts: 3678
|
| Posted: Thu Dec 22, 2022 4:07 am Post subject: |
|
|
Hu,
The error says "Host 192.168.1.213 is not allowed to connect to MySQL server".
Thank you. |
|
| Back to top |
|
|
|
Portage & Programming
