[mythtv-users] Can't connect to remote backend database
Stephen Worthington
stephen_agent at jsw.gen.nz
Sun Jun 6 10:38:42 UTC 2021
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
More information about the mythtv-users
mailing list