0

I tried the suggestions in Resetting root password for mariadb does not work

ERROR 1698 (28000): Access denied for user 'root'@'localhost' only talks about Ubuntu, not centos 8

Was able to login with mysql -u root but there was no user root in mysql table!!

So, the set password statement executes with 0 affected rows.

Did not work even after server restart.

Bislinks Dotcom
  • 1
  • 3

1 Answers1

0

Login to centos server from ssh. Then stop mysqld service and run mysqld_safe with —skip-grant-tables option to be in insecure mode and to reset the password. Then login to mysql server using mysql command without any -u or -p parameter as you are in insecure mode. Then update the root password flush privilage then exit from msyql console. Then use mysqladmin to stop mysql server which will ask for password give recently saved new password. Then finally start the mysqld service.

$ systemctl stop mysqld
$ mysqld_safe --skip-grant-tables &
$ mysql
mysql> UPDATE mysql.user SET Password=PASSWORD('new-password') WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;
$ mysqladmin -u root -p shutdown
$ systemctl start mysqld

Edit: ---------------------------------------

$ sudo systemctl stop mariadb
$ sudo mysqld_safe --skip-grant-tables --skip-networking &
$ mysql -u root
MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
MariaDB [(none)]>  FLUSH PRIVILEGES;
MariaDB [(none)]> exit;
$ sudo kill `/var/run/mariadb/mariadb.pid`
$ sudo systemctl start mariadb
blacksheep_trident
  • 172
  • 1
  • 10
  • Failed at step 7 [root@gaze ~]# mysqladmin -u root -p shutdown Enter password: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: YES)' [root@gaze ~]# – Bislinks Dotcom Sep 13 '20 at 16:04
  • skip that step and start mysqld service again i.e. run last command systemctl start mysqld – blacksheep_trident Sep 13 '20 at 16:11
  • [root@gaze ~]# systemctl start mysqld Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details. [root@gaze ~]# – Bislinks Dotcom Sep 13 '20 at 17:00
  • Even though there was error starting mysqld, I was still able to login as a standard user! [root@gaze ~]# mysql -u server -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 112 Server version: 10.5.5-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> – Bislinks Dotcom Sep 13 '20 at 17:02
  • Try using systemctl stop mariadb or service mysqld stop and then start it again – blacksheep_trident Sep 13 '20 at 17:09
  • systecmctl stop/start did not work: Restarted the VPS. Could not login with new password as root after restart – Bislinks Dotcom Sep 13 '20 at 17:19
  • what command do you use to start mariadb service is it service mysqld start or systemctl. First you need to stop the server and check its status like (systemctl status mariadb or mysql) then run mysqld_safe --skip-grant-tables & and update password then restart the service – blacksheep_trident Sep 13 '20 at 17:29
  • I used systemctl. – Bislinks Dotcom Sep 13 '20 at 17:45
  • I did exactly as outlined in your answer – Bislinks Dotcom Sep 13 '20 at 17:50
  • Then i will change the above commands please keep track – blacksheep_trident Sep 13 '20 at 17:51
  • hey @Bislinks Dotcom will you please try the edited version of the command in my answer added as edit right now. – blacksheep_trident Sep 13 '20 at 17:58
  • please take a note that i had made a small mistake on the first command. i wrongly typed systemctl stop mysqld instead of systemctl stop mariadb. other commands are okey – blacksheep_trident Sep 13 '20 at 18:03
  • MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPass'; ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement MariaDB [(none)]> – Bislinks Dotcom Sep 13 '20 at 18:04
  • I used `systemctl stop mariadb` – Bislinks Dotcom Sep 13 '20 at 18:09
  • use sudo kill `/var/run/mariadb/mariadb.pid` first then run ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPass' if you are still unable to use alter user then use UPDATE mysql.user SET password = PASSWORD('new_password') WHERE User = 'root' ; – blacksheep_trident Sep 13 '20 at 18:10
  • `[root@gaze ~]# kill /var/run/mariadb/mariadb.pid -bash: kill: /var/run/mariadb/mariadb.pid: arguments must be process or job IDs [root@gaze ~]# ` – Bislinks Dotcom Sep 13 '20 at 18:14
  • are you on team viewer! if yes i will like to help you with remote connection . – blacksheep_trident Sep 13 '20 at 18:17
  • No. I don't want to do Team Viewer. – Bislinks Dotcom Sep 13 '20 at 18:24
  • ok then follow through these two tutorial https://linuxize.com/post/how-to-reset-a-mysql-root-password/ and https://robbinespu.github.io/eng/2018/03/29/Reset_mariadb_root_password.html. I hope you get your job done. Thank you! – blacksheep_trident Sep 13 '20 at 18:26
  • I already saw the github post before posting this question. Did not help – Bislinks Dotcom Sep 13 '20 at 18:32