I have followed that link earlier today, But could not reset it, every time i restart mysql in the safe mode, the PID of mysql in safe mode dies away !!!
Any other thing that could be going wrong ? or i am missing ?
a) moved the /etc/my.cnf to /etc/my.cnf_bak
b) created an empty my.cnf
c) and then stopped mysql and restarted it like this :-mysqld_safe --skip-grant-tables &
d) Issued these commands in turn :-
USE mysql;
UPDATE user SET password=PASSWORD(“NEW-ROOT-PASSWORD”) WHERE User=‘root’;
FLUSH privileges;
quit
e) moved back the old my.cnf_bak to my.cnf
f ) started mysql again normally :- /etc/init.d/mysql restart
g ) tried to enter again into mysql normally :- mysql -uroot -p
Glad you got it fixed. In your previous attempt, you were trying to pass MySQL the init.d script (/etc/init.d/mysql), which would not work. That script is used to start and stop MySQL. You were needing to create a separate SQL script and pass it to MySQL with the --init-file option. But you ended up doing mainly the same thing in a different way, so that’s the important part. =)
I ran into this problem and thought I would add some info.
There seems to be two approaches here:
1.) Bakup the current my.cnf, create an empty my.cnf, and start mysql in safe mode mysqld_safe -skip-grant-tables
2.) Use the mysqld --init-file=/home/me/mysql-init approach
I chose approach one.
I stopped only one PXC node.
The instructions all worked fine until I went to update the password. The password field is now called ‘authentication_string’ I am sure the field names changed after some version, not sure what version.
if UPDATE user SET password=PASSWORD(‘new-root-pass’) WHERE user = ‘root’; does not work for you try
UPDATE user SET authentication_string=PASSWORD(‘new-root-pass’) WHERE user = ‘root’;