DataBase login/Connection failing
Moderator: Forum Moderators
DataBase login/Connection failing
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
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
Re: DataBase login/Connection failing
cat /home/<user>/.mythtv/config.xml
will give the database name, username, and password to log into the db.
will give the database name, username, and password to log into the db.
Last edited by wesnewell on Sat Jan 16, 2021 3:03 pm, edited 1 time in total.
BE/FE-Asrock AB350 Pro Ryzen 3 3200G, 6 atsc tuners. FE's-GF8200's Athlon II, Ryzen 3 2200G. Mythtv user since 2005.
Re: DataBase login/Connection failing
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
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
Re: DataBase login/Connection failing
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
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
Re: DataBase login/Connection failing
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.
Re: DataBase login/Connection failing
Sorry about that.It is config.xml.
BE/FE-Asrock AB350 Pro Ryzen 3 3200G, 6 atsc tuners. FE's-GF8200's Athlon II, Ryzen 3 2200G. Mythtv user since 2005.
Re: DataBase login/Connection failing
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..
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..
Re: DataBase login/Connection failing
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??
And SQL seems to be running fine...
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
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.
Re: DataBase login/Connection failing
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,
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;
Re: DataBase login/Connection failing
Tx's for responding Bill,.. So if I do a
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....
Code: Select all
mysql> DROP USER mythtv;
Re: DataBase login/Connection failing
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
Ref: https://dev.mysql.com/doc/refman/8.0/en/alter-user.html
Re: DataBase login/Connection failing
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
However,.. I now know from using the above credentials
That I really can login to sql as mythtv user...
this is also confirmed from the following...
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 )
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
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 |
+--------+-----------+-----------------------+-------------------------------------------+
Re: DataBase login/Connection failing
Just a guess but it could be the host: mythtv0 you probably need to use localhost?
Re: DataBase login/Connection failing
Yabba dabba Doo,.. !!!!!!!!!!!!!!!!!!!!!!!
that was a good guess!!
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...
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.
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...