Mailing List Archive

On 05/06/2021 08:26, Phill Edwards wrote:
> I built a new backend based on Ubuntu 20.04. But I haven't been able to
> configure it to allow connections from remote frontends. That's a
> problem because my frontend is a separate machine to my backend. 
>
> I've followed steps from https://www.mythtv.org/wiki/Database_Setup
> <https://www.mythtv.org/wiki/Database_Setup>. 
>
> And I've changed the bind-address settings in the mysql conf files:
>
> /etc/mysql/mysql.conf.d/mysqld.cnf
> ...
> bind-address            = 0.0.0.0
> mysqlx-bind-address     = 0.0.0.0
> ...
>
>
> /etc/mysql/mysql.conf.d/mythtv.cnf
> [mysqld]
> bind-address=0.0.0.0
> max_connections=100

What works on my machine is to bind mysql to the actual LAN IP address
of the machine hosting the database, so not the wildcard 0.0.0.0 address.

mysqld]
bind-address=192.168.1.60
max_connections=100
table_open_cache=500
_______________________________________________
mythtv-users mailing list
mythtv-users@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-users
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org

On Sat, 5 Jun 2021 16:26:07 +1000, you wrote:

>I built a new backend based on Ubuntu 20.04. But I haven't been able to
>configure it to allow connections from remote frontends. That's a problem
>because my frontend is a separate machine to my backend.
>
>I've followed steps from https://www.mythtv.org/wiki/Database_Setup.
>
>And I've changed the bind-address settings in the mysql conf files:
>
>/etc/mysql/mysql.conf.d/mysqld.cnf
>...
>bind-address = 0.0.0.0
>mysqlx-bind-address = 0.0.0.0
>...
>
>
>/etc/mysql/mysql.conf.d/mythtv.cnf
>[mysqld]
>bind-address=0.0.0.0
>max_connections=100
>
>
>Can anyone advise what I need to do to allow remote database connections to
>mysql?
>
>(The irony is the native mythfrontend is proving very difficult to connect,
>yet 3rd party apps such as Android Leanfront and Kodi have no problem at
>all because they're using APIs).

Have you checked that there is not still another bind-address setting
somewhere in the config files that is overriding your new setting?

sudo su
cd /etc/mysql
grep -ir "bind-address" *

Unless you are willing to look up all the rules and figure out which
*.cnf file is loaded last, there should be only one bind-address=
which is not commented out anywhere in all the *.cnf files. And the
order the *.cnf files are loaded differs between MySQL and MariaDB,
just to confuse things further.

If you have IPv6 enabled, set bind-address=:: instead of
bind-address=0.0.0.0.
_______________________________________________
mythtv-users mailing list
mythtv-users@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-users
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org

>
> Have you checked that there is not still another bind-address setting
> somewhere in the config files that is overriding your new setting?
>

Yes. There are only 2 files with bind-address under /etc/mysql :

$ sudo grep -ir "bind-address" *
conf.d/mythtv.cnf:bind-address=0.0.0.0

mysql.conf.d/mysqld.cnf:bind-address = 0.0.0.0
mysql.conf.d/mysqld.cnf:mysqlx-bind-address = 0.0.0.0

If you have IPv6 enabled, set bind-address=:: instead of
> bind-address=0.0.0.0.
>

I did have IPv6 enabled (accidentally) but I have now disabled it. But I
still can't make a remote connection to mysql. BTW I'm testing this from a
shell on a remote machine like this:

$ mysql -u mythtv -h 192.168.0.120 -p mythconverg

Could it be that the problem is to do with granting permissions on tables?
https://www.mythtv.org/wiki/Database_Setup has examples of grant commands
but they're more arou what to do on initial setup when creating user
mythtv. If I'd done something wrong there and that could be the cause of
the problem, how can I fix that up now?

On Sun, 6 Jun 2021 14:21:46 +1000, you wrote:

>>
>> Have you checked that there is not still another bind-address setting
>> somewhere in the config files that is overriding your new setting?
>>
>
>Yes. There are only 2 files with bind-address under /etc/mysql :
>
>$ sudo grep -ir "bind-address" *
>conf.d/mythtv.cnf:bind-address=0.0.0.0
>
>mysql.conf.d/mysqld.cnf:bind-address = 0.0.0.0
>mysql.conf.d/mysqld.cnf:mysqlx-bind-address = 0.0.0.0
>
>If you have IPv6 enabled, set bind-address=:: instead of
>> bind-address=0.0.0.0.
>>
>
>I did have IPv6 enabled (accidentally) but I have now disabled it. But I
>still can't make a remote connection to mysql. BTW I'm testing this from a
>shell on a remote machine like this:
>
>$ mysql -u mythtv -h 192.168.0.120 -p mythconverg
>
>Could it be that the problem is to do with granting permissions on tables?
>https://www.mythtv.org/wiki/Database_Setup has examples of grant commands
>but they're more arou what to do on initial setup when creating user
>mythtv. If I'd done something wrong there and that could be the cause of
>the problem, how can I fix that up now?

The next thing to do is to check that MySQL/MariaDB is actually
binding correctly. So on the database box, run this:

sudo su
netstat -lnp | grep mysql

This is what I get on my IPv6 enabled system:

root@mypvr:/var/log/mythtv# netstat -lnp | grep mysql
tcp6 0 0 :::3306 :::* LISTEN
3011/mysqld
unix 2 [ ACC ] STREAM LISTENING 44751 3011/mysqld
/var/run/mysqld/mysqld.sock

You should see 0.0.0.0:3306 instead of :::3306. If you get that, then
you can look at the GRANT commands for the database.

So, what database are you using? User creation and GRANT commands are
a place where MySQL and MariaDB can be a little different. To allow
access to a user on your 192.168.0.0/24 subnet, you would need to do
commands like this:

sudo su
mysql
create user "username"@"192.168.0.%" identified by "password";
grant all on mythconverg.* to "username"@"192.168.0.%";
flush privileges;
quit

Change "username" and "password" as required. MySQL/MariaDB treat the
whole of "username"@"address" as the username, so
"username"@"address1" and "username"@"address2" are distinct names and
each have their own password and GRANTs. If you want the user to be
able to change the user database, add the phrase "with grant option"
to the GRANT command before the ; character. If you want full access
from any IP address instead of just from the 192.168.0.0/24 subnet,
then replace "192.168.0.%" with "%". To show the database users:

sudo su
mysql
use mysql;
select user,host from user;
quit

To show the grants for a user:

sudo su
mysql
show grants for "username"@"address";
quit
_______________________________________________
mythtv-users mailing list
mythtv-users@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-users
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org

I've resolved this. Thanks very much to those who provided help. I was
being a bit dumb!

Thanks to Stephen who showed me how to see which users I have. I thought
here would be only one mythtv user but I have two:

mysql> select user, host from user;
+------------------+-------------+
| user | host |
+------------------+-------------+
| mythtv | % |
| mythtv | 192.168.0.% |
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-------------+

I'd assumed that the mythtv password for the backend would be the same
password as for the frontend. But now I understand they're actually
different accounts and with their own password. So I reset the password for
mythtv@192.168.0.% and now the remote frontend can connect just fine.

Thanks again for your help, everybody!