2

I'm trying to work out how to reset the root mySQL users password. I've found:

https://www.tecmint.com/reset-root-password-in-mysql-8/

I tried the first option, and at first it appears to work:

root@admin:~# cat /tmp/init-file.txt
ALTER USER 'root'@'localhost' IDENTIFIED BY 'testPASS';
root@admin:~#

Then I run:

root@admin:~#  mysqld --user=mysql --init-file=/tmp/init-file.txt --console

root@admin:~#

Yet when I try and now login with the new password, I get:

root@admin:~# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

What am I doing wrong? I've never had to reset the root password before, so I'm not sure if I'm missing something silly

UPDATE: Interstingly, even though I'm doing "service mysql stop", it does seem like its still running:

root      4992  0.0  0.0  52700  3904 pts/0    S    15:33   0:00 sudo mysqld_safe --skip-grant-tables
root      4993  0.0  0.0   4504  1720 pts/0    S    15:33   0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
mysql     5580  1.1 15.8 10668984 1291760 pts/0 Sl  15:33   0:10 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/log/mysql/error.log --open-files-limit=2048 --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306

How would I go about killing it fully?

UPDATE 2:

Even after making sure mysql is fully closed, it still doesn't seem to work:

root@admin:~# mysqld --user=mysql --init-file=/tmp/init-file.txt --console
root@admin:~# service mysql start
root@admin:~# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I've even tried a "flush" in the file to reset passwords:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'xxx';

flush privileges;
Andrew Newby
  • 1,041
  • 1
  • 22
  • 48

3 Answers3

1

Instead of trying to reset the root password, you could try to skip loading the grant-tables alltogether, and then root password would be blank.

sudo mysqld stop
mysqld --skip-grant-tables --user=mysql &
mysql
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_passowrd';
quit;
service mysql start

or, if you really wanna use a script file:

mysqld stop
mysqld --skip-grant-tables --user=mysql &
mysql < init_file.txt
mysqld stop
service mysql start
TheCompWiz
  • 7,349
  • 16
  • 23
  • 1
    haha it keeps getting better: mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'xxxx'; ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement – Andrew Newby May 28 '19 at 16:17
  • Ok I'm giving up for the day. Will come back to this tomorrow. THe only reason I need to get in here is to do a "hack" so we can get phpmyadmin working (as it won't work due to: https://stackoverflow.com/questions/49948350/phpmyadmin-on-mysql-8-0 ) – Andrew Newby May 28 '19 at 16:27
  • The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement. Yup same for me. – Bret Weinraub Nov 12 '20 at 04:55
1

Try this:

grep 'A temporary password is generated' /var/log/mysqld.log | tail -1

and after run the following command to apply security on MySQL server. Simply execute below command and follow the security wizard prompts:

sudo mysql_secure_installation

Follow the onscreen instructions. Change your root account password and Press Y for all other operations to apply improved security.

Change the password for root? – Press y and change root password
Remove anonymous users? Press y Disallow root login remotely? Press y
Remove test database and access to it? (Press y Reload privilege tables now? Press y

kenlukas
  • 2,886
  • 2
  • 14
  • 25
Daniel Sobrinho
  • 11
  • 2
0

Did you stop the mysql process before trying to start it with mysqld? It could be that the mysql-daemon was already running, and as a result, the init-file was never executed. Try shutting down mysqld, then repeat the process.

TheCompWiz
  • 7,349
  • 16
  • 23
  • thanks - how do I go about that? I'm doing `service mysql stop`, but maybe thats not the same thing? – Andrew Newby May 28 '19 at 15:48
  • I've just updated my post. It seems like mysql is indeed still running. It doesn't seem to want to kill with just `service mysql stop` – Andrew Newby May 28 '19 at 15:51
  • `sudo mysqld stop` or `sudo killall mysqld` and if that doesn't work: `sudo killall -9 mysqld` – TheCompWiz May 28 '19 at 15:55
  • thanks, I did it again and made sure it was all killed off - but it still doesn't work. Do I need to flush priveledges maybe? – Andrew Newby May 28 '19 at 16:00
  • try restarting the server once more... it could be that it does need flushing, or re-reading of the mysql schema. – TheCompWiz May 28 '19 at 16:04
  • unfortunately not - still doesn't work even after a reboot :( I want to try the other method `sudo mysqld_safe --skip-grant-tables &` , and then `update user set authentication_string=PASSWORD("mynewpassword") where User='root';`, but I get an error about PASSWORD() (as its been depreciated). I'm not sure what to replace it with :/ – Andrew Newby May 28 '19 at 16:07
  • Yeah... the "PASSWORD" function is gone. Just do the `ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';` method. – TheCompWiz May 28 '19 at 16:10