Not being able to reset ROOT Password for Mysql !!!!

[systemali]

Hello every one !!

I am trying to start mysql in safe mode to reset my root password, and i get this error below :-

I first stop it :-

root@DatabackupSRV ~/home # /etc/init.d/mysql stop
Shutting down MySQL. [ OK ]

Then try to start in safe mode :-

root@DatabackupSRV /var/lib/mysql # mysqld_safe --skip-grant-tables &
[1] 24880
root@DatabackupSRV /var/lib/mysql # 130516 22:57:01 mysqld_safe Logging to ‘/var/log/mysqld.log’.
130516 22:57:01 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130516 22:57:03 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

I check the /var/log/messages and this is what is see :-

root@DatabackupSRV /var/lib/mysql # tail -f /var/log/messages
May 16 20:53:19 DatabackupSRV smbd[10900]: [2013/05/16 20:53:19, 0] lib/util_sock.c:write_data(568)
May 16 20:53:19 DatabackupSRV smbd[10900]: write_data: write failure in writing to client 10.222.37.17. Error Broken pipe
May 16 20:53:19 DatabackupSRV smbd[10900]: [2013/05/16 20:53:19, 0] lib/util_sock.c:send_smb(767)
May 16 20:53:19 DatabackupSRV smbd[10900]: Error writing 75 bytes to client. -1. (Broken pipe)
May 16 21:44:34 DatabackupSRV yum: Installed: Percona-SQL-shared-50-5.0.92-b23.89.rhel5.x86_64
May 16 21:44:35 DatabackupSRV yum: Installed: perl-DBD-MySQL-3.0007-2.el5.x86_64
May 16 21:52:25 DatabackupSRV smbd[12713]: [2013/05/16 21:52:25, 0] lib/util_sock.c:read_data(540)
May 16 21:52:25 DatabackupSRV smbd[12713]: read_data: read failure for 4 bytes to client 10.222.37.18. Error = Connection timed out
May 16 21:56:39 DatabackupSRV smbd[24143]: [2013/05/16 21:56:39, 0] lib/util_sock.c:read_data(540)
May 16 21:56:39 DatabackupSRV smbd[24143]: read_data: read failure for 4 bytes to client 10.222.11.25. Error = Connection reset by peer

Can any one point out what am i missing ? OR what wrong am i doing

Thank you !!

[scott.nemes]

Try the below guide, section C.5.4.1.2. Resetting the Root Password: Unix Systems

http://dev.mysql.com/doc/refman/5.5/en/resetting-permissions.html

[systemali]

Hello Scott,

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 ?

Thank you

[scott.nemes]

Try placing the init file SQL script in your MySQL data directory, and make sure it is owned by mysql and give it a chmod of 700.

Then run the below, but change the path to match your data dir and whatever file name you used:
mysqld_safe --init-file=/datadir/mysql-init &

Once the server starts up, attempt to login as root using whatever password you set in the init script.

If that does not work, post your error log contents here so we can take a look at that. =)

[systemali]

Scott,

Pardon my ignorance, can yo be more precise as to where is this file present ( init file ) on the server ?

Is it the file that is in this location :- /etc/init.d/mysqld or something else that i am not getting to

Please suggest !!

Thank you

[systemali]

Hello Scott,

Assuming that i need to copy the file from /etc/init.d/mysql to my data directory :-

This is what i did, [ ALL THIS IS AT MY SLAVE or REPLICATION SERVER ]

My datadir is at :- /var/lib/mysql

a) copied mysql file from /etc/init.d/mysql to /var/lib/mysql/

NOTE :- there was a directory by the name of mysql at data directory location, i had to rename it to copy the file here.

b) I then, chmod 700 mysql & chown mysql:mysql mysql

c) started mysql :- mysqld_safe --init-file=/var/lib/mysql/mysql &

This is the error i got on the screen :-

root@DatabackupSRV /var/lib/mysql # mysqld_safe --init-file=/var/lib/mysql/mysql &
[1] 24911
root@DatabackupSRV /var/lib/mysql # 130517 15:50:59 mysqld_safe Logging to ‘/var/log/mysqld.log’.
130517 15:50:59 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130517 15:51:01 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

When i tail the logs i get this error :-

root@DatabackupSRV /var/lib/mysql # tail -f /var/log/messages
May 16 23:23:48 DatabackupSRV smbd[25411]: [2013/05/16 23:23:48, 0] lib/util_sock.c:read_data(540)
May 16 23:23:48 DatabackupSRV smbd[25411]: read_data: read failure for 4 bytes to client 10.222.37.17. Error = Connection timed out
May 16 23:23:48 DatabackupSRV smbd[25411]: [2013/05/16 23:23:48, 0] lib/util_sock.c:write_data(568)
May 16 23:23:48 DatabackupSRV smbd[25411]: write_data: write failure in writing to client 10.222.37.17. Error Broken pipe
May 16 23:23:48 DatabackupSRV smbd[25411]: [2013/05/16 23:23:48, 0] lib/util_sock.c:send_smb(767)
May 16 23:23:48 DatabackupSRV smbd[25411]: Error writing 75 bytes to client. -1. (Broken pipe)
May 17 00:01:41 DatabackupSRV smbd[25414]: [2013/05/17 00:01:41, 0] lib/util_sock.c:read_data(540)
May 17 00:01:41 DatabackupSRV smbd[25414]: read_data: read failure for 4 bytes to client 10.222.37.18. Error = Connection reset by peer
May 17 00:12:52 DatabackupSRV smbd[5908]: [2013/05/17 00:12:52, 0] lib/util_sock.c:read_data(540)
May 17 00:12:52 DatabackupSRV smbd[5908]: read_data: read failure for 4 bytes to client 10.222.11.22. Error = No route to host

Please let me know where am i wrong here

Thank you for your time as always !!

[systemali]

Scott,

Pheww…finally i managed to change the password

What i did was, :-

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

Horryyy yyy!!!

May help some one some time

[scott.nemes]

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. =)

[systemali]

Ohh oke,

Scott, I really really appreciate all your assistance this regards,

Yes, at the end resolution is what matter

Take care

[kyakruu]

===

[Jamie]

Visit this link on how to reset root password - http://tekslate.com/user-account-management-mysql/

[rwigfall]

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’;