[Solved] Switch Backends

[pinnerite]

I have given up trying to get my local backend server to record new recordings on its menu.

I am now trying to link to my original backend server on a different machine in another part of my house.
Its address is 192.168.1.5 - hostname origen.

It has a local frontend that works with it perfectly.

I want my local frontend (192.168.1.100 - hostname lakeland) to become a remote frontend to origen.

I have changed the hostname in config.xml in every copy on lakeland from lakeland to 192.168.1.5
Two copies also have LocalHostName of 'lakeland'. I have left those unchanged.
They are in:

/home/alan/.mythtv ;and
/usr/share/mythtv

I have ssh'd into origen to check the backend with:
$ sudo service mythtv-backend status
It is running.
Needless to say, all I get on starting my local frontend is "Waiting for database to start".

[bill6502]

I'd also mention if (from lakeland) this works: mysql --host=192.168.1.5 --user=mythtv -p mythconverg
(assuming mythtv is the user from config.xml). PS, you should only need to change 1 config.xml file.
The one in ~alan/.mythtv should be a symbolic link to the actual file.

[pinnerite]

Thank you.
$ mysql --host=192.168.1.5 --user=mythtv -p mythconverg
Whatever password I respond with (or none) the result is:
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.5:3306' (111)
Prepending sudo makes no difference.

[bill6502]

That helps narrow down the issue. I'd look at the bind-address setting for starters.
In the following, the file to change is /etc/mysql/mariadb.conf.d/80-mythtv.cnf
never ones that a distribution's package manager might change (and erase your
setting).

Code: Select all

On lakeland: 
grep bind-address --recursive /etc/mysql
...
/etc/mysql/mariadb.conf.d/60-galera.cnf:#bind-address = 0.0.0.0
/etc/mysql/mariadb.conf.d/80-mythtv.cnf:bind-address=*
/etc/mysql/mariadb.conf.d/50-server.cnf:bind-address            = 127.0.0.1

Your filenames may be different.

Restart the SQL server after making any changes. Then test with the mysql command above.
If it still fails, see if the error message changed to: Access denied for user 'mythtv'@.....
If so, look for a file named mc.sql and use the CREATE and ALTER lines to add the new frontend.

[pinnerite]

alan@lakeland:~$ grep bind-address --recursive /etc/mysql
/etc/mysql/conf.d/mythtv.cnf:# bind-address=::
/etc/mysql/conf.d/mythtv.cnf:# bind-address=192.168.1.100
/etc/mysql/conf.d/mythtv.cnf:bind-address=192.168.1.5
/etc/mysql/mysql.conf.d/mysqld.cnf~:# bind-address = 127.0.0.1
/etc/mysql/mysql.conf.d/mysqld.cnf~:bind-address = 192.168.1.5
/etc/mysql/mysql.conf.d/mysqld.cnf~:# mysqlx-bind-address = 127.0.0.1
/etc/mysql/mysql.conf.d/mysqld.cnf~:mysqlx-bind-address = 192.168.1.5
/etc/mysql/mysql.conf.d/mysqld.cnf:# bind-address = 127.0.0.1
/etc/mysql/mysql.conf.d/mysqld.cnf:bind-address = 192.168.1.100
/etc/mysql/mysql.conf.d/mysqld.cnf:# mysqlx-bind-address = 127.0.0.1
/etc/mysql/mysql.conf.d/mysqld.cnf:mysqlx-bind-address = 192.168.1.100
grep: /etc/mysql/debian.cnf: Permission denied
alan@lakeland:~$

[bill6502]

That's allowing 192.168.1.5 to connect to SQL, no other addresses (the 3rd line after the grep).
I'd remove it and un-comment the 1st line and change :: to * (that allows all IP addresses.)

All the cruft in /etc/mysql/mysql.conf.d/mysqld.cnf should be removed (in my opinion as it just
confuses the issue and is unnecessary,) The only valid lines, which normally aren't commented are
bind-address = 127.0.0.1 and mysqlx-bind-address = 127.0.0.1. Then never edit that
file again.

[pinnerite]

But those replies are all on the remote front-end machine (192.168.1.100, lakeland) not on the backend server (origen).
Should the remote front-end (lakeland) not be bindfing to origen (192.168.1.5)?

[bill6502]

OK, to review (for mysql) FE lakeland=192.168.1.100 wants to get to the DB on origen=192.168.1.5

So I should have had you do the grep on origen.

[pinnerite]

alan@origen:~$ grep bind-address --recursive /etc/mysql
/etc/mysql/conf.d/mythtv.cnf:#bind-address=::
/etc/mysql/mysql.conf.d/mysqld.cnf:bind-address = 127.0.0.1
/etc/mysql/mysql.conf.d/mysqld.cnf:mysqlx-bind-address = 127.0.0.1
grep: /etc/mysql/debian.cnf: Permission denied

[bill6502]

SQL on origen is only listening on the 127.0.0.1 address. Remove the comment from
/etc/mysql/conf.d/mythtv.cnf:#bind-address=:: and restart SQL. Retest from lakeland
with mysql --host=192.168.1.5 --user=mythtv -p mythconverg command.

[pinnerite]

alan@lakeland:~$ sudo mysql --host=192.168.1.5 --user=mythtv -p mythconverg
Enter password:
ERROR 1045 (28000): Access denied for user 'mythtv'@'192.168.1.100' (using password: YES)
alan@lakeland:~$

[bill6502]

Right, viewtopic.php?p=27723#p27723 was my answer for that.

If you have the locate command you can just do: locate mc.sql fo find the file. I should
have included the GRANT as well. Just do the 2nd - 4th lines in the file replacing localhost
with your choice. I use: '192.168.1.%'

[pinnerite]

alan@origen:~$ grep bind-address --recursive /etc/mysql
/etc/mysql/conf.d/mythtv.cnf:bind-address = 192.168.1.%
/etc/mysql/mysql.conf.d/mysqld.cnf:bind-address = 192.168.1.%
/etc/mysql/mysql.conf.d/mysqld.cnf:mysqlx-bind-address = 192.168.1.%

alan@origen:~$ mysql --host=192.168.1.5 --user=mythtv -p mythconverg
returns:
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.5:3306' (111)

alan@origen:~$ locate mc.sql
/usr/share/mythtv/sql/legacy.mc.sql
/usr/share/mythtv/sql/mc.sql

[bill6502]

The 192.168.1.% format is valid for the commands like ALTER above, not for the bind-address.
The percent sign is just a wild card in SQL.

For /etc/mysql/conf.d/mythtv.cnf I use: bind-address=*

For detail, see: https://dev.mysql.com/doc/refman/8.0/en ... nd_address

Looks like 192.168.1.0/24 would work. Or a comma separated list of IPs.

The 2 files locate found will have the SQL to add the remote host. Like:

Code: Select all

CREATE USER IF NOT EXISTS 'mythtv'@'lakeland' IDENTIFIED WITH mysql_native_password;
ALTER USER 'mythtv'@'lakeland' IDENTIFIED BY 'mythtv';
GRANT ALL ON mythconverg.* TO mythtv@lakeland;
FLUSH PRIVILEGES;

[pinnerite]

Sorry for the delayed return to this.
The following changes were made:
On lakeland:
1) /etc/mysql/mysql,conf.d/mythtv.cnf:
bind-address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1
2) /etc/share/mythtv/sql/mc.sql
Every expression 'mythtv'@'localhost' changed to 'mythtv'@'lakeland'
3) Same on /etc/share/mythtv/sql/legacy.mc.sql

4) alan@lakeland:~$ mysql --host=192.168.1.5 --user=mythtv -p mythconverg
Returns
ERROR 1045 (28000): Access denied for user 'mythtv'@'lakeland' (using password: YES)

On Origen:
alan@origen:~$ grep bind-address --recursive /etc/mysql
/etc/mysql/conf.d/mythtv.cnf:bind-address=::
/etc/mysql/mysql.conf.d/mysqld.cnf:# bind-address = 127.0.0.1
/etc/mysql/mysql.conf.d/mysqld.cnf:bind-address = 192.168.1.%
/etc/mysql/mysql.conf.d/mysqld.cnf:# mysqlx-bind-address = 127.0.0.1
/etc/mysql/mysql.conf.d/mysqld.cnf:mysqlx-bind-address = 192.168.1.%

The lakeland frontend still will not start up.