Trying to Restore Dbase..

[diyhouse]

I have done a clean install of 'basic' ubuntu 24.04,...
Before I waffle on,.. I love the Control-panel interface,.. this makes installation so much easier, my thanks to all contributors..even if I am having a problem,
Using the control-panel, I have installed a clean mythtv combined FE/BE. based on a 34 install.. and setup as best I can...
The default config.xml uses the wrong passwd,. ( fortunately I kept the original system generated one, and my original ), so I was able to use the default created passwd,.. login to SQL,.. then modify the passwd to match my config.xml.
In the process I have followed a few setup things grant privs etc.. the standard ones.
I have then tried to restore my database as follows:-

/usr/share/mythtv/mythconverg_restore.pl --verbose --drop_database --create_database --filename /mnt/Backups/DBaseFiles/Daily/mythconverg-1361-20231127163245.sql.gz

this returns the following output:-

Configuring environment:
- username: mark
- HOME: /home/mark
- MYTHCONFDIR: /home/mark/.mythtv

Parsing configuration files:
- checking: /home/mark/.mythtv/config.xml
parsing: /home/mark/.mythtv/config.xml
- checking: /home/mark/.mythtv/backuprc
parsing: /home/mark/.mythtv/backuprc

Applying command-line arguments.

Checking configuration.

Database Information:
DBHostName: mythtv0
DBPort: 3306
DBUserName: mythtv
DBPassword: XXX
DBName: mythconverg
DBSchemaVer:
DBBackupDirectory: /mnt/Backups/DBaseFiles/Daily
DBBackupFilename: mythconverg-1361-20231127163245.sql.gz
drop_database: yes
create_database: yes

Executables:
mysql_client: mysql
uncompress: gzip -d

Miscellaneous:
partial_restore: no
restore_xmltvids: no
change_hostname: no

Checking database.
DBI connect('host=mythtv0:database=mythconverg','mythtv',...) failed: Can't connect to MySQL server on 'mythtv0:3306' (111) at /usr/share/mythtv/mythconverg_restore.pl line 933.

Preparing initial database.
DBI connect('host=mythtv0','mythtv',...) failed: Can't connect to MySQL server on 'mythtv0:3306' (111) at /usr/share/mythtv/mythconverg_restore.pl line 933.

Unable to connect to database.
database: mythconverg
host: mythtv0
username: mythtv
password: lCxsunfHc

Please check your configuration files to verify the database connection
information is correct. The files that are used to retrieve connection
information are prefixed with "parsing" in the "Parsing configuration files"
section of the --verbose output.

Looking further:-

● mysql.service - MySQL Community Server
Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; preset: enabled)
Active: active (running) since Sun 2024-06-16 13:20:44 BST; 4h 26min ago

mysql is running, and I can use the credentials to log into mysql....

However,.. I do not know what the 'root' passwd is for mysql,.. and I am not sure what the correct setting for the database are as I found references to mysql setup,.. with a caveat being pre 34,.. but I cannot find what is the current setting for 34,.. and I cannot see in the control-panel a means of creating a new clean database from a template for myth to use..
I have also tried to run the backend from the command line,.. that to fails, not being able to connect to the database.
I have a config.xml in my /home/mark/.mythtv,.. and the etc/mythtv/config.xml as linked to it as well,.. as is the mythtv user,..

Code: Select all

mark@mythtv0:/etc/mythtv$ ls -l
total 8.0K
   0 lrwxrwxrwx 1 root root   29 Jun 16 13:13 config.xml -> /home/mark/.mythtv/config.xml
4.0K -rw-r----- 1 root root  452 Jun 16 13:10 old_config.xml
4.0K -rw-r--r-- 1 root root 1.1K May 30 12:13 session-settings

I have the same host name,.. and the same ip address.. and my database is from a Ver32 Mythtv
Is anyone able to shed some light on my errors and what I may have done wrong...

[white_haired_uncle]

e default created passwd,.. login to SQL,.. t

Okay, then mysql is running and listening on SOME port. How did you login to mysql (from which host, and what was the command you ran to connect, particularly the "-h foo" part)?

failed: Can't connect to MySQL server on 'mythtv0:3306'

Run this:

Code: Select all

mythtv0$ netstat -an | grep 3306

and this:

Code: Select all

mythtv0$  grep -r bind-add  /etc/mysql/

However,.. I do not know what the 'root' passwd is for mysql,..

We can get around that, but right now your problem is that mysql is listening on a certain port/NIC, and you're trying to connect to something else.

[bill6502]

Also, it appears that your config.xml is using a host of mythtv0. If this is being run on that host,
then the <Host> entry should be: localhost, which is added when the MythTV package is installed.

[diyhouse]

Many tx for the quick response guys,.. ( had to pause this end,.. as wife wanted diner together,.. )

How did you login to mysql

as follows:

mysql -u mythtv -pftxsunfHc
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
.
. <cut>
.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

that's a login on localhost,.. ie actually sitting on the host Mythtv0 m/c in a console window

output from netstat;-

mark@mythtv0:/etc/mythtv$ sudo netstat -an | grep 3306
[sudo] password for mark:
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:33060 0.0.0.0:* LISTEN

and from grep,..

mark@mythtv0:/etc/mythtv$ sudo grep -r bind-add /etc/mysql/
/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
/etc/mysql/conf.d/mythtv.cnf:#bind-address=::

..and Bill I have had mythtv0 as my host name,. as entered in the config.xml.. since mythtv adapted the config.xml file...
but happy to change if you think this would help

[bill6502]

Try: mysql -h mythtv0 -u mythtv -pftxsunfHc and see if it fails (probably isn't in mysql.user.)
localhost also has the benefit of using a socket rather than a TCP connection.

[diyhouse]

Hummm,.. some more diagnostic stuff guys,.. plus other things I could test/check for you..
Txs...

mark@mythtv0:/etc/mythtv$ mysql -u mythtv -ppasswd
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
.
.<cut>
.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

mysql -h mythtv0 -u mythtv -ppasswd
mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 2003 (HY000): Can't connect to MySQL server on 'mythtv0:3306' (111)

and

mark@mythtv0:/etc/mythtv$ mysql -h 127.0.0.1 -u mythtv -ppasswd
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 158
Server version: 8.0.37-0ubuntu0.24.04.1 (Ubuntu)
.
.<cut>

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> quit

and

mysql -h 192.168.3.47 -u mythtv -ppasswd
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.3.47:3306' (111)

and

mark@mythtv0:/etc/mythtv$ ping mythtv0
PING mythtv0 (127.0.1.1) 56(84) bytes of data.
64 bytes from mythtv0 (127.0.1.1): icmp_seq=1 ttl=64 time=0.026 ms
64 bytes from mythtv0 (127.0.1.1): icmp_seq=2 ttl=64 time=0.025 ms
64 bytes from mythtv0 (127.0.1.1): icmp_seq=3 ttl=64 time=0.026 ms
^C
--- mythtv0 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2052ms
rtt min/avg/max/mdev = 0.025/0.025/0.026/0.000 ms

and

mark@mythtv0:/etc/mythtv$ cat /etc/hostname
mythtv0

and

mark@mythtv0:/etc/mythtv$ ifconfig
enp3s0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.3.47 netmask 255.255.255.0 broadcast 192.168.3.255
inet6 fe80::a60:6eff:fec5:9135 prefixlen 64 scopeid 0x20<link>
ether 08:60:6e:c5:91:35 txqueuelen 1000 (Ethernet)
RX packets 237009 bytes 63022630 (63.0 MB)
RX errors 0 dropped 29093 overruns 0 frame 0
TX packets 80271 bytes 8322680 (8.3 MB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0

lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536
inet 127.0.0.1 netmask 255.0.0.0
inet6 ::1 prefixlen 128 scopeid 0x10<host>
loop txqueuelen 1000 (Local Loopback)
RX packets 1055 bytes 81349 (81.3 KB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 1055 bytes 81349 (81.3 KB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0

[bill6502]

You'll have a /usr/share/mythtv/sql/mc.sql file that got executed when MythTV was installed.
It only adds the mythtv user for localhost.

Code: Select all

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

Run the above commands replacing localhost with mythtv0 (or change mythtv0 to localhost in config.xml
the preferred solution.)

That was probably in your old mysql.users table, which isn't part of the MythTV backup DB file.

[bill6502]

sudo mysql mysql will get you in as the root SQL user.

[white_haired_uncle]

mysql is only listening on the loopback interface (127.0.0.1 aka localhost). If you only have the one FE and it's on mythtv0 you can just use localhost instead of mythtv0 in your config.xml (though it may also be necessary to update mysql to allow connection from mythtv@localhost).

Or, if you want to allow connections from other hosts, such as another FE, you can configure mysql to listen on more than just localhost. For example, to listen on any IPv4 interface, change:

Code: Select all

/etc/mysql/conf.d/mythtv.cnf:#bind-address=::

to

Code: Select all

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

EDIT: Sorry, looks like Bill and I were answering at the same time. Listen to him (though just changing the permissions for mythtv won't be sufficient, you will have to change bind-address as well if you go that route).

[diyhouse]

Bill,.. many tx,.. have entered mysql commands into mysql, as advise ( changed host name to localhost). having logged in as root.
did a restart mysql,.. restore failed,.. did a reboot,..
tried a restore as before, error message is the same as before in both cases.
tx guys...

[bill6502]

In the output of the restore command, did the

Code: Select all

Checking configuration.

Database Information:
DBHostName: localhost
DBPort: 3306
DBUserName: mythtv
DBPassword: XXX
DBName: mythconverg

section look OK?

For now, just be sure the mysql -h... -u... -p mythconverg command works using the config.xml file (as you have been).
Once that's OK, move on to the backup.

[diyhouse]

Hi Bill,.. on running restore:-
I get the following errors.

Checking configuration.

Database Information:
DBHostName: localhost
DBPort: 3306
DBUserName: mythtv
DBPassword: XXX
DBName: mythconverg
DBSchemaVer:
DBBackupDirectory: /mnt/Backups/DBaseFiles/Daily
DBBackupFilename: mythconverg-1361-20231127163245.sql.gz
drop_database: yes
create_database: yes

Executables:
mysql_client: mysql
uncompress: gzip -d

Miscellaneous:
partial_restore: no
restore_xmltvids: no
change_hostname: no

Checking database.
DBI connect('host=localhost:database=mythconverg','mythtv',...) failed: Access denied for user 'mythtv'@'localhost' (using password: YES) at /usr/share/mythtv/mythconverg_restore.pl line 933.

Preparing initial database.
DBI connect('host=localhost','mythtv',...) failed: Access denied for user 'mythtv'@'localhost' (using password: YES) at /usr/share/mythtv/mythconverg_restore.pl line 933.

Unable to connect to database.
database: mythconverg
host: localhost
username: mythtv
password: xxxxxxsun

In essence same failure,..also however previously,

mysql -umythtv -pxxxxxxxsun ,... DID work,.. now it does not.

trying to move forward, I have run the following:-

from within SQL> drop database mythconverg;

followed by

sudo mysql mysql < /usr/share/mythtv/sql/mc.sql # This I assume (hope) is the default create database script...

but still

mysql -h localhost -u mythtv -pxxxxx # fails

as does

mysql -h mythtv0 -u mythtv -pxxxxxx #fails

and just for clarity.. this is my config.xml file

<Configuration>
<LocalHostName>mythtv0</LocalHostName>
<Database>
<PingHost>true</PingHost>
<Host>localhost</Host>
<UserName>mythtv</UserName>
<Password>xxxxxsun</Password>
<DatabaseName>mythconverg</DatabaseName>
<Port>3306</Port>
</Database>
<WakeOnLAN>
<Enabled>true</Enabled>
<SQLReconnectWaitTime>0</SQLReconnectWaitTime>
<SQLConnectRetry>5</SQLConnectRetry>
<Command>echo 'WOLsqlServerCommand not set'</Command>
</WakeOnLAN>
<UPnP>
<UDN>
<MediaRenderer>5f342e4c-8584-4a88-9bc6-3c4fd08963e3</MediaRenderer>
</UDN>
</UPnP>
</Configuration>

Is this 'sort off' correct?

[white_haired_uncle]

Code: Select all

Checking database.
DBI connect('host=localhost:database=mythconverg','mythtv',...) failed: Access denied for user 'mythtv'@'localhost' (using password: YES) at /usr/share/mythtv/mythconverg_restore.pl line 933.

In essence same failure,..also however previously,

No, totally different failure. Before, you weren't connecting to the database. Now you are, but either 'mythtv'@'localhost' is not configured, or you are not using the correct password.

I don't know if you have edited that mc.sql file or not, but whatever it is using for the mythtv password must match what you have in config.xml and on the CLI. For example, if you did edit mc.sql and/or it includes:

Code: Select all

ALTER USER 'mythtv'@'localhost' IDENTIFIED BY 'mythtv';

then mythtv is the password for mythtv when connecting to 'localhost'.

You could of course, as root (mysql's root):

Code: Select all

mysql> ALTER USER 'mythtv'@'localhost' IDENTIFIED BY 'xxxxxsun';

(or 'xxxxxxsun', I assume you've edited out the real password, but if not notice that there's 5 x's in one of your examples and 6 in the other).

[diyhouse]

Yes your quiet correct,.. I was just trying to protect my passwds in some way...

So, I have updated the passwd as per root login to SQL,..
I should have done this when I 'created' a new default database...

Success,.. I have now restored my old 32 database,.. all 4548 lines....
And running the backend,.. ( having fixed where the backup is made,. ( lack of initial privs. ) the backend has updated the database and the backend sort of runs,. albeit no DBase

Jun 17 17:06:42 mythtv0 mythbackend[3578]: mythbackend[3578]: I CoreContext mythcontext.cpp:595 (LoadDatabaseSettings) Empty LocalHostName. This is typical.
Jun 17 17:06:42 mythtv0 mythbackend[3578]: mythbackend[3578]: I CoreContext mythcontext.cpp:650 (LoadDatabaseSettings) Using a profile name of: 'mythtv0' (Usually the same as th>
Jun 17 17:06:42 mythtv0 mythbackend[3578]: mythbackend[3578]: I CoreContext mythcontext.cpp:891 (TestDBconnection) Start up testing connections. DB localhost, BE , attempt 0, st>
Jun 17 17:06:43 mythtv0 mythbackend[3578]: mythbackend[3578]: E CoreContext mythdbcon.cpp:247 (OpenDatabase) [DBManager0] Unable to connect to database!
Jun 17 17:06:43 mythtv0 mythbackend[3578]: mythbackend[3578]: E CoreContext mythdbcon.cpp:248 (OpenDatabase) Driver error was [1/1045]:
QMYSQL: Unable to connect
Database error was:
Access denied for user 'mythtv'@'localhost' (using password: YES)
Jun 17 17:06:44 mythtv0 mythbackend[3578]: mythbackend[3578]: I CoreContext mythcontext.cpp:1019 (TestDBconnection) Start up failure. host localhost, status dbStartedFail
Jun 17 17:06:44 mythtv0 mythbackend[3578]: mythbackend[3578]: I CoreContext mythcontext.cpp:1189 (UPnPautoconf) UPNP Search 2 secs
Jun 17 17:06:44 mythtv0 mythbackend[3578]: mythbackend[3578]: I CoreContext mythcontext.cpp:1205 (UPnPautoconf) UPNP Search 1 secs
Jun 17 17:06:45 mythtv0 mythbackend[3578]: mythbackend[3578]: I CoreContext mythcontext.cpp:1205 (UPnPautoconf) UPNP Search 1 secs
Jun 17 17:06:45 mythtv0 mythbackend[3578]: mythbackend[3578]: I CoreContext mythcontext.cpp:1205 (UPnPautoconf) UPNP Search 1 secs

But also note,

mysql -h mythtv0 -u mythtv -p

still fails,.. So I guess this is still the fundamental problem,.. even though we have progress on the restore.

should I run Bills sql lines again,.. Pls help what does the 'mysql_native_password' bit do... in idiot terms...

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

[bill6502]

^^^ is just a password that is encrypted and stored in the mysql.users table. It's also
not seeded, that is if the password is mythtv, the encrypted value will be the same
for anyone that chooses to use it. It is deprecated (will not be allowed to be used in
the future.)