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,224

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,429
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
  • 3,404
Daniel Sobrinho
  • 11
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,429