LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - Software (https://www.linuxquestions.org/questions/linux-software-2/)
-   -   mysql cant access, it is denied, cant change root password it errors (https://www.linuxquestions.org/questions/linux-software-2/mysql-cant-access-it-is-denied-cant-change-root-password-it-errors-4175616099/)

sdowney717 10-21-2017 09:48 AM

mysql cant access, it is denied, cant change root password it errors
 
I tried various things found on web pages.
So far nothing works.
Do I need to purge and reinstall? just the mysql.
I already found at least 3 usability bugs in myth setup just trying to install it.
The mythtv setup fails to give a working install. Access denied everywhere.

Code:

root@scott-MS-7596:/home/scott# grep mythtv: /etc/group
mythtv:x:131:scott
root@scott-MS-7596:/home/scott# rm ~/.mythtv -rf && rm /home/mythtv/.mythtv -rf
root@scott-MS-7596:/home/scott# rm ~/.mythtv -rf && rm /home/mythtv/.mythtv -rf
root@scott-MS-7596:/home/scott# sudo dpkg-reconfigure mythtv-database
/var/lib/dpkg/info/mythtv-database.postinst: 1: /var/lib/dpkg/info/mythtv-database.postinst: ifconfig: not found
root@scott-MS-7596:/home/scott# sudo dpkg-reconfigure mythtv-common
root@scott-MS-7596:/home/scott# mythtv-setup
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'mythtv'@'localhost' (using password: YES)
Gtk-Message: GtkDialog mapped without a transient parent. This is discouraged.
Gtk-Message: GtkDialog mapped without a transient parent. This is discouraged.
/usr/bin/mythtv-setup: 42: /usr/bin/mythtv-setup: status: not found
Gtk-Message: GtkDialog mapped without a transient parent. This is discouraged.
Gtk-Message: GtkDialog mapped without a transient parent. This is discouraged.
root@scott-MS-7596:/home/scott# mysql stop
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
root@scott-MS-7596:/home/scott# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
root@scott-MS-7596:/home/scott# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
root@scott-MS-7596:/home/scott# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
root@scott-MS-7596:/home/scott# service mysql stop
root@scott-MS-7596:/home/scott# sudo dpkg-reconfigure mysql-server-5.5
dpkg-query: package 'mysql-server-5.5' is not installed and no information is available
Use dpkg --info (= dpkg-deb --info) to examine archive files,
and dpkg --contents (= dpkg-deb --contents) to list their contents.
/usr/sbin/dpkg-reconfigure: mysql-server-5.5 is not installed
root@scott-MS-7596:/home/scott# sudo dpkg-reconfigure mysql-server-5.7
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                OK
mysql.db                                          OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                        OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                OK
mysql.help_relation                                OK
mysql.help_topic                                  OK
mysql.innodb_index_stats                          OK
mysql.innodb_table_stats                          OK
mysql.ndb_binlog_index                            OK
mysql.plugin                                      OK
mysql.proc                                        OK
mysql.procs_priv                                  OK
mysql.proxies_priv                                OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                        OK
mysql.slave_worker_info                            OK
mysql.slow_log                                    OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                              OK
mysql.time_zone_transition                        OK
mysql.time_zone_transition_type                    OK
mysql.user                                        OK
The sys schema is already up to date (version 1.5.1).
Checking databases.
mythconverg.bdbookmark                            OK
mythconverg.capturecard                            OK
mythconverg.cardinput                              OK
mythconverg.channel                                OK
mythconverg.channelgroup                          OK
mythconverg.channelgroupnames                      OK
mythconverg.channelscan                            OK
mythconverg.channelscan_channel                    OK
mythconverg.channelscan_dtv_multiplex              OK
mythconverg.codecparams                            OK
mythconverg.credits                                OK
mythconverg.customexample                          OK
mythconverg.diseqc_config                          OK
mythconverg.diseqc_tree                            OK
mythconverg.displayprofilegroups                  OK
mythconverg.displayprofiles                        OK
mythconverg.dtv_multiplex                          OK
mythconverg.dtv_privatetypes                      OK
mythconverg.dvdbookmark                            OK
mythconverg.dvdinput                              OK
mythconverg.dvdtranscode                          OK
mythconverg.eit_cache                              OK
mythconverg.filemarkup                            OK
mythconverg.gallery_directories                    OK
mythconverg.gallery_files                          OK
mythconverg.housekeeping                          OK
mythconverg.inputgroup                            OK
mythconverg.internetcontent                        OK
mythconverg.internetcontentarticles                OK
mythconverg.inuseprograms                          OK
mythconverg.iptv_channel                          OK
mythconverg.jobqueue                              OK
mythconverg.jumppoints                            OK
mythconverg.keybindings                            OK
mythconverg.keyword                                OK
mythconverg.livestream                            OK
mythconverg.logging                                OK
mythconverg.mythlog                                OK
mythconverg.oldfind                                OK
mythconverg.oldprogram                            OK
mythconverg.oldrecorded                            OK
mythconverg.people                                OK
mythconverg.pidcache                              OK
mythconverg.playgroup                              OK
mythconverg.powerpriority                          OK
mythconverg.profilegroups                          OK
mythconverg.program                                OK
mythconverg.programgenres                          OK
mythconverg.programrating                          OK
mythconverg.recgrouppassword                      OK
mythconverg.recgroups                              OK
mythconverg.record                                OK
mythconverg.recorded                              OK
mythconverg.recordedartwork                        OK
mythconverg.recordedcredits                        OK
mythconverg.recordedfile                          OK
mythconverg.recordedmarkup                        OK
mythconverg.recordedprogram                        OK
mythconverg.recordedrating                        OK
mythconverg.recordedseek                          OK
mythconverg.recordfilter                          OK
mythconverg.recordingprofiles                      OK
mythconverg.recordmatch                            OK
mythconverg.scannerfile                            OK
mythconverg.scannerpath                            OK
mythconverg.settings                              OK
mythconverg.storagegroup                          OK
mythconverg.tvchain                                OK
mythconverg.tvosdmenu                              OK
mythconverg.upnpmedia                              OK
mythconverg.user_permissions                      OK
mythconverg.user_sessions                          OK
mythconverg.users                                  OK
mythconverg.videocast                              OK
mythconverg.videocategory                          OK
mythconverg.videocollection                        OK
mythconverg.videocountry                          OK
mythconverg.videogenre                            OK
mythconverg.videometadata                          OK
mythconverg.videometadatacast                      OK
mythconverg.videometadatacountry                  OK
mythconverg.videometadatagenre                    OK
mythconverg.videopart                              OK
mythconverg.videopathinfo                          OK
mythconverg.videosource                            OK
mythconverg.videotypes                            OK
sys.sys_config                                    OK
Upgrade process completed successfully.
Checking if update is needed.
root@scott-MS-7596:/home/scott# sudo service mysql start
root@scott-MS-7596:/home/scott# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
root@scott-MS-7596:/home/scott# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
root@scott-MS-7596:/home/scott#
root@scott-MS-7596:/home/scott# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
root@scott-MS-7596:/home/scott# service mysql stop
root@scott-MS-7596:/home/scott# sudo mysqld_safe --skip-grant-tables &
[1] 5762
root@scott-MS-7596:/home/scott# 2017-10-21T14:38:18.326291Z mysqld_safe Logging to syslog.
2017-10-21T14:38:18.331933Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-10-21T14:38:18.336336Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.
mysql -uroot
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
[1]+  Exit 1                  sudo mysqld_safe --skip-grant-tables
root@scott-MS-7596:/home/scott#

just tried this, commands do not work
https://www.howtoforge.com/setting-c...root-passwords
Code:

root@scott-MS-7596:/home/scott# service mysql stop
root@scott-MS-7596:/home/scott# sudo mysqld_safe --skip-grant-tables &
[1] 5762
root@scott-MS-7596:/home/scott# 2017-10-21T14:38:18.326291Z mysqld_safe Logging to syslog.
2017-10-21T14:38:18.331933Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-10-21T14:38:18.336336Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.
mysql -uroot
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
[1]+  Exit 1                  sudo mysqld_safe --skip-grant-tables
root@scott-MS-7596:/home/scott# service mysql stop
root@scott-MS-7596:/home/scott# mysqld_safe --skip-grant-tables &
[1] 6487
root@scott-MS-7596:/home/scott# 2017-10-21T14:45:15.585361Z mysqld_safe Logging to syslog.
2017-10-21T14:45:15.590832Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-10-21T14:45:15.595601Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

[1]+  Exit 1                  mysqld_safe --skip-grant-tables
root@scott-MS-7596:/home/scott#
root@scott-MS-7596:/home/scott# mysql -u root
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
root@scott-MS-7596:/home/scott#


sdowney717 10-21-2017 09:59 AM

I ran sudo apt remove mysql* --purge, and got rd of the crap.

Then I ran sudo apt install mysql-server
Set root password to mythtv, and I am able to login to mysql from the command line now.

So will reinstall myth and see what happens. It may fail again.

Code:

root@scott-MS-7596:/home/scott# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.19-0ubuntu1 (Ubuntu)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> exit
Bye
root@scott-MS-7596:/home/scott# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.

etc...

mythtv, well still failing to connect.

the password field is changed, and looks encrypted, how do you add a user with password? I tried several times adding other users, but it refuses to connect except with root.

Code:

mysql> select host, user, pass from mysql.user;
ERROR 1054 (42S22): Unknown column 'pass' in 'field list'
mysql>  desc mysql.user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default              | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                  | char(60)                          | NO  | PRI |                      |      |
| User                  | char(32)                          | NO  | PRI |                      |      |
| Select_priv            | enum('N','Y')                    | NO  |    | N                    |      |
| Insert_priv            | enum('N','Y')                    | NO  |    | N                    |      |
| Update_priv            | enum('N','Y')                    | NO  |    | N                    |      |
| Delete_priv            | enum('N','Y')                    | NO  |    | N                    |      |
| Create_priv            | enum('N','Y')                    | NO  |    | N                    |      |
| Drop_priv              | enum('N','Y')                    | NO  |    | N                    |      |
| Reload_priv            | enum('N','Y')                    | NO  |    | N                    |      |
| Shutdown_priv          | enum('N','Y')                    | NO  |    | N                    |      |
| Process_priv          | enum('N','Y')                    | NO  |    | N                    |      |
| File_priv              | enum('N','Y')                    | NO  |    | N                    |      |
| Grant_priv            | enum('N','Y')                    | NO  |    | N                    |      |
| References_priv        | enum('N','Y')                    | NO  |    | N                    |      |
| Index_priv            | enum('N','Y')                    | NO  |    | N                    |      |
| Alter_priv            | enum('N','Y')                    | NO  |    | N                    |      |
| Show_db_priv          | enum('N','Y')                    | NO  |    | N                    |      |
| Super_priv            | enum('N','Y')                    | NO  |    | N                    |      |
| Create_tmp_table_priv  | enum('N','Y')                    | NO  |    | N                    |      |
| Lock_tables_priv      | enum('N','Y')                    | NO  |    | N                    |      |
| Execute_priv          | enum('N','Y')                    | NO  |    | N                    |      |
| Repl_slave_priv        | enum('N','Y')                    | NO  |    | N                    |      |
| Repl_client_priv      | enum('N','Y')                    | NO  |    | N                    |      |
| Create_view_priv      | enum('N','Y')                    | NO  |    | N                    |      |
| Show_view_priv        | enum('N','Y')                    | NO  |    | N                    |      |
| Create_routine_priv    | enum('N','Y')                    | NO  |    | N                    |      |
| Alter_routine_priv    | enum('N','Y')                    | NO  |    | N                    |      |
| Create_user_priv      | enum('N','Y')                    | NO  |    | N                    |      |
| Event_priv            | enum('N','Y')                    | NO  |    | N                    |      |
| Trigger_priv          | enum('N','Y')                    | NO  |    | N                    |      |
| Create_tablespace_priv | enum('N','Y')                    | NO  |    | N                    |      |
| ssl_type              | enum('','ANY','X509','SPECIFIED') | NO  |    |                      |      |
| ssl_cipher            | blob                              | NO  |    | NULL                  |      |
| x509_issuer            | blob                              | NO  |    | NULL                  |      |
| x509_subject          | blob                              | NO  |    | NULL                  |      |
| max_questions          | int(11) unsigned                  | NO  |    | 0                    |      |
| max_updates            | int(11) unsigned                  | NO  |    | 0                    |      |
| max_connections        | int(11) unsigned                  | NO  |    | 0                    |      |
| max_user_connections  | int(11) unsigned                  | NO  |    | 0                    |      |
| plugin                | char(64)                          | NO  |    | mysql_native_password |      |
| authentication_string  | text                              | YES  |    | NULL                  |      |
| password_expired      | enum('N','Y')                    | NO  |    | N                    |      |
| password_last_changed  | timestamp                        | YES  |    | NULL                  |      |
| password_lifetime      | smallint(5) unsigned              | YES  |    | NULL                  |      |
| account_locked        | enum('N','Y')                    | NO  |    | N                    |      |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)

mysql> select host, user, authentication_string from>  desc mysql.user;
+------------+------------------+-------------------------------------------+
| host      | user            | authentication_string                    |
+------------+------------------+-------------------------------------------+
| localhost  | root            | *CC8F35F587CA5A556B4132C2407E556D92172FFC |
| localhost  | mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost  | mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost  | debian-sys-maint | *6DED012B849A612C30C08157FE26668A0C5D8A87 |
| [hostname] | [user]          | *182439D07E5F8331C2103E6572D6FDA5C76F8D3A |
| localhost  | mythtv          | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| localhost  | myhttv          | *CC8F35F587CA5A556B4132C2407E556D92172FFC |
+------------+------------------+-------------------------------------------+
7 rows in set (0.00 sec)

mysql>


sdowney717 10-21-2017 10:29 AM

Finally was able to get in to mysql from commandline with a user called mythtv.

Code:

mysql> ALTER USER 'mythtv'@'localhost' IDENTIFIED BY 'mythtv';
Query OK, 0 rows affected (0.00 sec)

mysql> select host, user, authentication_string from mysql.user;
+------------+------------------+-------------------------------------------+
| host      | user            | authentication_string                    |
+------------+------------------+-------------------------------------------+
| localhost  | root            | *CC8F35F587CA5A556B4132C2407E556D92172FFC |
| localhost  | mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost  | mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost  | debian-sys-maint | *6DED012B849A612C30C08157FE26668A0C5D8A87 |
| [hostname] | [user]          | *182439D07E5F8331C2103E6572D6FDA5C76F8D3A |
| localhost  | mythtv          | *CC8F35F587CA5A556B4132C2407E556D92172FFC |
| localhost  | myhttv          | *CC8F35F587CA5A556B4132C2407E556D92172FFC |
+------------+------------------+-------------------------------------------+
7 rows in set (0.00 sec)

mysql> ALTER USER 'mythtv'@'localhost' IDENTIFIED BY 'mythtv';
Query OK, 0 rows affected (0.00 sec)

mysql> DROP USER 'myhttv'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> select host, user, authentication_string from mysql.user;
+------------+------------------+-------------------------------------------+
| host      | user            | authentication_string                    |
+------------+------------------+-------------------------------------------+
| localhost  | root            | *CC8F35F587CA5A556B4132C2407E556D92172FFC |
| localhost  | mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost  | mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost  | debian-sys-maint | *6DED012B849A612C30C08157FE26668A0C5D8A87 |
| [hostname] | [user]          | *182439D07E5F8331C2103E6572D6FDA5C76F8D3A |
| localhost  | mythtv          | *CC8F35F587CA5A556B4132C2407E556D92172FFC |
+------------+------------------+-------------------------------------------+
6 rows in set (0.00 sec)

mysql> ALTER USER 'mythtv'@'localhost' IDENTIFIED BY 'mythtv';
Query OK, 0 rows affected (0.00 sec)

mysql> \q
Bye

scott@scott-MS-7596:~$ mysql -u mythtv -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 336
Server version: 5.7.19-0ubuntu1 (Ubuntu)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql>



All times are GMT -5. The time now is 01:11 AM.