[mythtv-users] Can't connect to remote backend database

[Stephen Worthington]

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 at 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