DataBase login/Connection failing

[diyhouse]

Hi guys I'm in the middle of a 29 to 31 clean upgrade on an Xubuntu base.

mysql Database appears to be running,.. but I cannot connect the backend to it,...

This I believe is due to the newly created database having a new 'passwd key',.. understandable,... what i cannot find is what the mysql root passwd is,... I thought this used to be the user login passwd,.. but this does not appear to be the case,...

I have linked my old config.xml's around so they have the credentials for the old database,... luckily I have a copy of the old (newly created ) credentials,.. but my attempts to reset the passwd for mysql mythtv user do not work as they used to as I 'think' I need root access... what am I missing....

Thanks guys

[wesnewell]

cat /home/<user>/.mythtv/config.xml
will give the database name, username, and password to log into the db.

[diyhouse]

Hi Wes,.. Many tx for getting back,.. I have checked both /home/USER/.mythtv and /home/mythtv/.mythtv and /etc/mythtv folders,..
and the only only xml file here is the config.xml.. which contains only the old credentials that I wish to 'change to',.. ( this is the config.xml file have saved from my previous build).

Maybe a fundamental question,.. do I need the root SQL passwd.. or can I change the mythtv passwd from just user level,.. I have tried, but maybe using wrong syntax,.. many tx

[diyhouse]

well found a whole bunch of scripts and methods to change the root sql password,.. but none seem to work.
the basics seem to be as follows:-
Once the mysql server has been stopped and run from the following comand line
sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &
mysql -u root ,... to get to the sql prompt,
then trying to execute the following statements fails
UPDATE mysql.user
SET authentication_string = PASSWORD('MyNewPass'), password_expired = 'N'
WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

this fails:-
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('Fed123') where user='root'' at line 1
all the web material I have found down not resolve the syntax issues I am experiencing,...
Can anyone shed any light on the correct syntax to change passwds for both root and user 'mythtv'...

Many tx

[diyhouse]

Update:- found these posts with some extra bits that seem to allow for passwd update;

Code: Select all

For Ubuntu 19 with MySQL 8.0.17-0ubuntu2, what ended up working for me was a combination of many answers:

In the MySQL's configuration file (/etc/mysql/mysql.conf.d/mysqld.cnf on my machine), under [mysqld], add:

skip-grant-tables = 1
plugin-load-add = auth_socket.so

Restart the MySQL Service;

Connect to MySQL: mysql -uroot;

Run:

UPDATE mysql.user SET authentication_string=null WHERE User='root';
FLUSH PRIVILEGES;

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'pass123';
Stop MySQL and comment skip-grant-tables in the configuration file;

Start MySQL again and this should now work: mysql -u root -ppass123.

[wesnewell]

Sorry about that.It is config.xml.

[diyhouse]

I have found this SQL statement:-

ALTER USER USER() IDENTIFIED BY 'auth_string' REPLACE 'current_auth_string';

But I am struggling with its usage,.. as I don't wont to mess-up my dbase,..
I envisage usage something like as follows, taking the output from the sql output

mysql> SELECT User,Host,plugin,authentication_string FROM user ;

ALTER USER mythtv IDENTIFIED BY '*62D4E0347A56D959B7D09C18970441034567ACE8BB8' REPLACE 'New_passwd';
or am I barking up the wrong tree..

[diyhouse]

OK,.. so I'm still pulling my hair out,.. whats left
I am using the following command:-

Any pointers/help gratefully received,.....

Does the BE need to be running and attached to the Dbase??? ( using the original created install SQL credentials )??? Just checking my understanding.

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

I get the following response;... AFAIK,.. have read,... I do not need to change the mythtv dbase passwd,.. as 'dropping' and 'creating' a new ( with old dbase) overrides the old passwd??

Code: Select all

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

Parsing configuration files:
  - checking: /home/cramm/.mythtv/config.xml
     parsing: /home/cramm/.mythtv/config.xml
  - checking: /home/cramm/.mythtv/backuprc
     parsing: /home/cramm/.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-1348-20210113150429.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' (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' (111) at /usr/share/mythtv/mythconverg_restore.pl line 933.

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

And SQL seems to be running fine...

Code: Select all

● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Sat 2021-01-16 14:26:19 GMT; 1 day 2h ago
    Process: 3996 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
   Main PID: 4020 (mysqld)
     Status: "Server is operational"
      Tasks: 39 (limit: 18776)
     Memory: 356.7M
     CGroup: /system.slice/mysql.service
             └─4020 /usr/sbin/mysqld

Jan 16 14:26:18 mythtv0 systemd[1]: Starting MySQL Community Server...
Jan 16 14:26:19 mythtv0 systemd[1]: Started MySQL Community Server.

[bill6502]

No, the backend doesn't have to be running in order to use SQL.

I appears, from the above, that the root SQL access issue is solved.

You could: DROP USER mythtv and start over.

MySQL v8 has a different syntax,

Code: Select all

# localhost user creation. 
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;

# If required, repeat the above for a mythtv user on remote host(s).
# For >>>example<<<, allow all hosts on 192.168.1.0/24:
CREATE USER IF NOT EXISTS 'mythtv'@'192.168.1.%' IDENTIFIED WITH mysql_native_password;
ALTER USER 'mythtv'@'192.168.1.%' IDENTIFIED BY 'mythtv';
GRANT ALL ON mythconverg.* TO 'mythtv'@'192.168.1.%';
FLUSH PRIVILEGES;

[diyhouse]

Tx's for responding Bill,.. So if I do a

Code: Select all

mysql> DROP USER mythtv;

from the sql prompt,.. and then enter your commands as given (line by line) how does this script overcome my issue of setting the passwd to that required by my old database,.. password: KCd22fwc, and hopefully enable me to restore my old dataBase....

[bill6502]

Change the ALTER line to this: ALTER USER 'mythtv'@'localhost' IDENTIFIED BY 'KCd22fwc';

Ref: https://dev.mysql.com/doc/refman/8.0/en/alter-user.html

[diyhouse]

Hi Bill,.. Many Tx for your help,.. have made some progress,.. but still unable to restore old Database..

Have run the following commands:-
drop user 'mythtv'@'localhost';

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

as root sql user....

I have then restarted the sql service,.. ( just in case )

and then tried my restore command,... this failed as before

Code: Select all

Checking database.
DBI connect('host=mythtv0:database=mythconverg','mythtv',...) failed: Can't connect to MySQL server on 'mythtv0' (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' (111) at /usr/share/mythtv/mythconverg_restore.pl line 933.

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

However,.. I now know from using the above credentials

Code: Select all

mysql -u mythtv -p KCd22fwc 

That I really can login to sql as mythtv user...

this is also confirmed from the following...

Code: Select all

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT User,Host,plugin,authentication_string FROM user WHERE User= 'mythtv';
+--------+-----------+-----------------------+-------------------------------------------+
| User   | Host      | plugin                | authentication_string                     |
+--------+-----------+-----------------------+-------------------------------------------+
| mythtv | localhost | mysql_native_password | *90BB6ADA6C222827F86554B7DD919C8E07C03A4D |
+--------+-----------+-----------------------+-------------------------------------------+

So what am I missing now,... restores have never been this problematic before,... BTW,... the IP address and hostname of host m/c are exactly the same as before rebuild... ( as this has caught me out before )

[paulh]

Just a guess but it could be the host: mythtv0 you probably need to use localhost?

[diyhouse]

Yabba dabba Doo,.. !!!!!!!!!!!!!!!!!!!!!!!
that was a good guess!!

Code: Select all

Executing command:
'mysql' --defaults-extra-file='/tmp/LpdhPoMbTV' --host='localhost' --port='3306' --user='mythtv' 'mythconverg'
mysql exited with status: 0
Restored 4588 of 4588 lines.
Successfully restored backup.
Found DBSchemaVer: 1348.
Setting database character set.

I will look to update schema etc tomorrow,.. and install card drivers and all the xmltv stuff....

Very grateful for your help guys,.. but what was wrong with the actual nodename as opposed to localhost,... its been that way in my config file for more years than I care to remember..

Many Thanks for your input Bill with the SQL stuff,.. I was really struggling to get my head around some of the sql syntax...