Configuring new front end; database connect question
Moderator: Forum Moderators
Configuring new front end; database connect question
I have combined frontend / backend in good working order.
I installed mythtv on a modest PC in my spare room; I'd like to use it as a frontend.
When I start mythfrontend it cannot find the database.
I entered the connect information, which was saved in ~/.mythtv/config.xml:
<Database>
<PingHost>1</PingHost>
<Host>192.168.0.xxx</Host>
<UserName>mythtv</UserName>
<Password>mythtv</Password>
<DatabaseName>mythconverg</DatabaseName>
<Port>3306</Port>
</Database>
I can ping the backend host system.
My question is the port. On the backend, the port in it's ~/.mythtv/config.xml shows port 0.
Is this for "internal" connections, where 3306 is a default for external connections? And where would that be set?
Do I need to stop my back end and reconfigure the database connect information (to set the port to 3306) and restart mariadb?
Sorry if this seems obvious; I really don't want to muss things up.
Thanks
Stefan
I installed mythtv on a modest PC in my spare room; I'd like to use it as a frontend.
When I start mythfrontend it cannot find the database.
I entered the connect information, which was saved in ~/.mythtv/config.xml:
<Database>
<PingHost>1</PingHost>
<Host>192.168.0.xxx</Host>
<UserName>mythtv</UserName>
<Password>mythtv</Password>
<DatabaseName>mythconverg</DatabaseName>
<Port>3306</Port>
</Database>
I can ping the backend host system.
My question is the port. On the backend, the port in it's ~/.mythtv/config.xml shows port 0.
Is this for "internal" connections, where 3306 is a default for external connections? And where would that be set?
Do I need to stop my back end and reconfigure the database connect information (to set the port to 3306) and restart mariadb?
Sorry if this seems obvious; I really don't want to muss things up.
Thanks
Stefan
Re: Configuring new front end; database connect question
I found another thread which suggested a solution:
viewtopic.php?f=36&t=4198
I shut down the front end, used mythtvsetup to change the hostname to the static IP address, and restarted.
The combined front end / back end works, but the remote front end still can't connect.
I tried using mysql to connect from the remote front end host:
mysql -umythtv -h192.168.0.xxx -p mythconverg
It returned "ERROR 1045 (28000): Access denied"
Any suggestions?
viewtopic.php?f=36&t=4198
I shut down the front end, used mythtvsetup to change the hostname to the static IP address, and restarted.
The combined front end / back end works, but the remote front end still can't connect.
I tried using mysql to connect from the remote front end host:
mysql -umythtv -h192.168.0.xxx -p mythconverg
It returned "ERROR 1045 (28000): Access denied"
Any suggestions?
Re: Configuring new front end; database connect question
Is that the full error message? I'd expect a user name.
To be clear, you restarted mysql then mythtv-backend.
Perhaps this will help add a remote user: viewtopic.php?f=36&t=4737&p=23252#p23252
To be clear, you restarted mysql then mythtv-backend.
Perhaps this will help add a remote user: viewtopic.php?f=36&t=4737&p=23252#p23252
Re: Configuring new front end; database connect question
Thanks, Bill.
I stopped mythtv-backend, stopped and restarted mysql, restarted the backend.
I confirmed that I could schedule new programs so I guess the internal connection is working.
This is what I get when I try to correct from other systems:
[stefan@linuxgame ~]$ mysql -umythtv -h192.168.0.xxx -p mythconverg
Enter password:
ERROR 1045 (28000): Access denied for user 'mythtv'@'192.168.0.yyy' (using password: YES)
I'll check out the article on adding a remote user.
I stopped mythtv-backend, stopped and restarted mysql, restarted the backend.
I confirmed that I could schedule new programs so I guess the internal connection is working.
This is what I get when I try to correct from other systems:
[stefan@linuxgame ~]$ mysql -umythtv -h192.168.0.xxx -p mythconverg
Enter password:
ERROR 1045 (28000): Access denied for user 'mythtv'@'192.168.0.yyy' (using password: YES)
I'll check out the article on adding a remote user.
Re: Configuring new front end; database connect question
OK. Found some interesting stuff.
I took a break to update the combined BE/FE to Fedora 35. Checked to see that MythTV was operational. Found that the front end couldn't connect to the database! I used the settings in front end to change the dotted quad IP address (of the host computer) back to 'Localhost'. The front end was able connect, and mythtv was operational. (I could watch TV, make recordings, etc.)
Since I had a console up on the BE/FE, I thought I'd try connecting to the database via mysql:
[stefan@linuxgame ~]$ mysql -umythtv -h192.168.0.xxx -p mythconverg
Enter password:
ERROR 1045 (28000): Access denied for user 'mythtv'@'192.168.0.xxx' (using password: YES)
Hmmm! But when I used "-hLocalhost", mysql was able to connect!
Again, this was on the host running the combined back end / front end.
I tried adding the port on the mysql command line as well.
I installed firewall-config and checked the "mysql" box. TBH, I'm not conversant with using the firewall tool, and I'm not sure if I did it right. In any case, I couldn't connect to the BE/FE host database from my main workstation.
Any help appreciated.
I took a break to update the combined BE/FE to Fedora 35. Checked to see that MythTV was operational. Found that the front end couldn't connect to the database! I used the settings in front end to change the dotted quad IP address (of the host computer) back to 'Localhost'. The front end was able connect, and mythtv was operational. (I could watch TV, make recordings, etc.)
Since I had a console up on the BE/FE, I thought I'd try connecting to the database via mysql:
[stefan@linuxgame ~]$ mysql -umythtv -h192.168.0.xxx -p mythconverg
Enter password:
ERROR 1045 (28000): Access denied for user 'mythtv'@'192.168.0.xxx' (using password: YES)
Hmmm! But when I used "-hLocalhost", mysql was able to connect!
Again, this was on the host running the combined back end / front end.
I tried adding the port on the mysql command line as well.
I installed firewall-config and checked the "mysql" box. TBH, I'm not conversant with using the firewall tool, and I'm not sure if I did it right. In any case, I couldn't connect to the BE/FE host database from my main workstation.
Any help appreciated.
Last edited by StefanJ on Sun Jan 16, 2022 12:18 am, edited 1 time in total.
Re: Configuring new front end; database connect question
When logged in to mysql as user mythtv (and localhost), type: SHOW GRANTS;
Re: Configuring new front end; database connect question
Thanks, Bill!
Doesn't look like the database is allowing outside connections on this level.
The official docs for configuring a remote frontend don't go into this; they may need updating.
I also checked for what mysql was doing, listener-wise. Not seeing port 3306.
Code: Select all
MariaDB [mythconverg]> show grants;
+---------------------------------------------------------------------------------------------------------------+
| Grants for mythtv@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `mythtv`@`localhost` IDENTIFIED BY PASSWORD '*CC8F35F587CA5A556B4132C2407E556D92172FFC' |
| GRANT ALL PRIVILEGES ON `mythconverg`.* TO `mythtv`@`localhost` |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
The official docs for configuring a remote frontend don't go into this; they may need updating.
I also checked for what mysql was doing, listener-wise. Not seeing port 3306.
Code: Select all
stefan@hp-fedora etc]$ sudo netstat -l -n -p | grep mysql
[sudo] password for stefan:
unix 2 [ ACC ] STREAM LISTENING 23340 995/mariadbd /var/lib/mysql/mysql.sock
Re: Configuring new front end; database connect question
Login to mysql as the root user, you'll need to do that if you want to add a remote user.
Then do SHOW GRANTS like this (my output added):
viewtopic.php?f=36&t=4737#p23252 has the
commands valid for mysql v8, which were tested on an Ubuntu MariaDB
host. Change localhost to whatever you want, like an IP or 192.168.1.%
You may have a mc.sql file on your distribution that has the commands
required.
To answer your question about having 0 as a port number in config.xml,
that's OK because internally, MythTV converts it to 3306.
I like this for checking if the server is running: nmap -p3306 --reason localhost
Then do SHOW GRANTS like this (my output added):
Code: Select all
mysql> SHOW GRANTS FOR mythtv;
+---------------------------------------------------------+
| Grants for mythtv@% |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO `mythtv`@`%` |
| GRANT ALL PRIVILEGES ON `mythconverg`.* TO `mythtv`@`%` |
+---------------------------------------------------------+
commands valid for mysql v8, which were tested on an Ubuntu MariaDB
host. Change localhost to whatever you want, like an IP or 192.168.1.%
You may have a mc.sql file on your distribution that has the commands
required.
To answer your question about having 0 as a port number in config.xml,
that's OK because internally, MythTV converts it to 3306.
I like this for checking if the server is running: nmap -p3306 --reason localhost
Re: Configuring new front end; database connect question
Thanks for the fast reply! Results later.
Where can I find "mc.sql?" Is it part of mythtv? Searching repositories doesn't find mention of it.
(Oh, found a location in another thread: /usr/share/mythtv/sql/mc.sql. I'll try it after morning routines are done.)
(I got the mythtv package from rpmfusion-free.)
Where can I find "mc.sql?" Is it part of mythtv? Searching repositories doesn't find mention of it.
(Oh, found a location in another thread: /usr/share/mythtv/sql/mc.sql. I'll try it after morning routines are done.)
(I got the mythtv package from rpmfusion-free.)
Re: Configuring new front end; database connect question
Well, this is interesting.
I started mysql with sudo, then:
But:
This worked better:
I found mc.sql in /usr/share/doc/mythtv-docs/database. Is this the kind of edit you'd suggest?
Output of nmap:
Thanks again,
stefan
I started mysql with sudo, then:
Code: Select all
MariaDB [mythconverg]> SHOW GRANTS FOR mythtv;
ERROR 1044 (42000): Access denied for user 'mythtv'@'localhost' to database 'mysql'
Code: Select all
MariaDB [mythconverg]> show grants;
+---------------------------------------------------------------------------------------------------------------+
| Grants for mythtv@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `mythtv`@`localhost` IDENTIFIED BY PASSWORD '*CC8F35F587CA5A556B4132C2407E556D92172FFC' |
| GRANT ALL PRIVILEGES ON `mythconverg`.* TO `mythtv`@`localhost` |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
Code: Select all
MariaDB [mythconverg]> show grants for mythtv@localhost;
+---------------------------------------------------------------------------------------------------------------+
| Grants for mythtv@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `mythtv`@`localhost` IDENTIFIED BY PASSWORD '*CC8F35F587CA5A556B4132C2407E556D92172FFC' |
| GRANT ALL PRIVILEGES ON `mythconverg`.* TO `mythtv`@`localhost` |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
I found mc.sql in /usr/share/doc/mythtv-docs/database. Is this the kind of edit you'd suggest?
Code: Select all
CREATE DATABASE IF NOT EXISTS mythconverg;
CREATE USER IF NOT EXISTS 'mythtv'@'localhost' IDENTIFIED WITH mysql_native_password;
ALTER USER 'mythtv'@'192.168.0.%' IDENTIFIED BY 'mythtv';
GRANT ALL ON mythconverg.* TO mythtv@192.168.0.%;
FLUSH PRIVILEGES;
GRANT CREATE TEMPORARY TABLES ON mythconverg.* TO mythtv@192.168.0.%;
FLUSH PRIVILEGES;
ALTER DATABASE mythconverg DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Output of nmap:
Code: Select all
[stefan@hp-fedora ~]$ nmap -p3306 --reason localhost
Starting Nmap 7.91 ( https://nmap.org ) at 2022-01-16 10:59 PST
Nmap scan report for localhost (127.0.0.1)
Host is up, received conn-refused (0.00035s latency).
Other addresses for localhost (not scanned): ::1
PORT STATE SERVICE REASON
3306/tcp open mysql syn-ack
Nmap done: 1 IP address (1 host up) scanned in 0.05 seconds
stefan
Re: Configuring new front end; database connect question
I don't know what command line you used: sudo mysql --user=root... It looks like the mysql user was mythtv.
Some distributions have SQL configured to allow: sudo mysql mysql
The SQL in /usr/share/doc/mythtv-docs/database is up to date. Works for MariaDB and MySQL v8.
Some distributions have SQL configured to allow: sudo mysql mysql
The SQL in /usr/share/doc/mythtv-docs/database is up to date. Works for MariaDB and MySQL v8.
Re: Configuring new front end; database connect question
Ahh, user root, not just using sudo. I logged into mysql as user root this time:
It looks like it should allow incoming, not just local host, if I understand `mythtv`@`%` correctly.
Now, is the next goal here to create a NEW user, 'mythtv'@'192.168.0.%', using these lines?
I don't want to mess up the existing user so I want to be sure I'm doing this correctly. It has been about 15 years since I used SQL extensively. (Oddly enough, while on the development group for Oracle's video server.)
Code: Select all
[stefan@hp-fedora ~]$ sudo mysql -uroot -hLocalhost -p mythconverg
[sudo] password for stefan:
Enter password:
SNIP
MariaDB [mythconverg]> show grants for mythtv;
+-------------------------------------------------------------------------------------------------------+
| Grants for mythtv@% |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `mythtv`@`%` IDENTIFIED BY PASSWORD '*7A1099EACF54D15948AF9429999D3448207BE68F' |
| GRANT ALL PRIVILEGES ON `mythconverg`.* TO `mythtv`@`%` |
+-------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
Now, is the next goal here to create a NEW user, 'mythtv'@'192.168.0.%', using these lines?
Code: Select all
CREATE USER IF NOT EXISTS 'mythtv'@'192.168.0.%' IDENTIFIED WITH mysql_native_password;
ALTER USER 'mythtv'@'192.168.0.%' IDENTIFIED BY 'mythtv';
GRANT ALL ON mythconverg.* TO mythtv@192.168.0.%;
FLUSH PRIVILEGES;
GRANT CREATE TEMPORARY TABLES ON mythconverg.* TO mythtv@192.168.0.%;
FLUSH PRIVILEGES;
Re: Configuring new front end; database connect question
I'd do the above. If you want to try it 1st, substitute some other name, like 'justtesting'@'192. ...'
and you can do DROP USER 'justtesting'@'192. ...'
and you can do DROP USER 'justtesting'@'192. ...'
SOLVED Re: Configuring new front end; database connect question
Logged into mysql as root, I created a new user and checked grants:
AND I was able to log into mysql using "testuser" from both the myth BE/FE host, and my primary workstation.
Now, to create mythtv@192.168.0.%:
(also logged in as root)
Based on the other thread whre the fellow is having trouble with mc.sql, I have a bad feeling about the above . . . but I continued creating the user. This looks promising at least:
AND . . . . (drumroll)
I could log into sql using the new user locally and on my main workstation.
And when I ran upstairs to the spare room and started mythfrontend, it immediately found the backend and I was able to stream video.
Mission accomplished. Thank you, Bill!
Code: Select all
MariaDB [mythconverg]> CREATE USER IF NOT EXISTS 'testuser'@'192.168.0.%' IDENTI
FIED WITH mysql_native_password;
Query OK, 0 rows affected (0.250 sec)
MariaDB [mythconverg]> ALTER USER 'testuser'@'192.168.0.%' IDENTIFIED BY 'mythtv';
Query OK, 0 rows affected (0.019 sec)
MariaDB [mythconverg]> GRANT ALL ON mythconverg.* TO 'testuser'@'192.168.0.%';
Query OK, 0 rows affected (0.029 sec)
MariaDB [mythconverg]> flush privileges;
Query OK, 0 rows affected (0.001 sec)
MariaDB [mythconverg]> GRANT CREATE TEMPORARY TABLES ON mythconverg.* TO 'testuser'@'192.168.0.%';
Query OK, 0 rows affected (0.024 sec)
MariaDB [mythconverg]> flush privileges;
Query OK, 0 rows affected (0.001 sec)
MariaDB [mythconverg]> show grants for 'testuser'@'192.168.0.%';
+-------------------------------------------------------------------------------------------------------------------+
| Grants for testuser@192.168.0.% |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `testuser`@`192.168.0.%` IDENTIFIED BY PASSWORD '*CC8F35F587CA5A556B4132C2407E556D92172FFC' |
| GRANT ALL PRIVILEGES ON `mythconverg`.* TO `testuser`@`192.168.0.%` |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)
Now, to create mythtv@192.168.0.%:
(also logged in as root)
Code: Select all
MariaDB [mythconverg]> CREATE USER IF NOT EXISTS 'mythtv'@'192.168.0.%' IDENTIFIED WITH 'mythtv';
ERROR 1524 (HY000): Plugin 'mythtv' is not loaded
MariaDB [mythconverg]> CREATE USER IF NOT EXISTS 'mythtv'@'192.168.0.%' IDENTIFIED WITH mysql_native_password;
Query OK, 0 rows affected (0.033 sec)
Code: Select all
MariaDB [mythconverg]> show grants for 'mythtv'@'192.168.0.%';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for mythtv@192.168.0.% |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `mythtv`@`192.168.0.%` IDENTIFIED BY PASSWORD '*CC8F35F587CA5A556B4132C2407E556D92172FFC' |
| GRANT ALL PRIVILEGES ON `mythconverg`.* TO `mythtv`@`192.168.0.%` |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
I could log into sql using the new user locally and on my main workstation.
And when I ran upstairs to the spare room and started mythfrontend, it immediately found the backend and I was able to stream video.
Mission accomplished. Thank you, Bill!