Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

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

systemalisystemali AdvisorCurrent User Role Beginner
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 :-

[email protected] ~/home # /etc/init.d/mysql stop
Shutting down MySQL. [ OK ]

Then try to start in safe mode :-

[email protected] /var/lib/mysql # mysqld_safe --skip-grant-tables &
[1] 24880
[email protected] /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 :-

[email protected] /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 !!

Comments

  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    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
  • systemalisystemali Advisor Current User Role Beginner
    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.nemesscott.nemes MySQL Sage Current User Role Patron
    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. =)
  • systemalisystemali Advisor Current User Role Beginner
    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
  • systemalisystemali Advisor Current User Role Beginner
    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 :-

    [email protected] /var/lib/mysql # mysqld_safe --init-file=/var/lib/mysql/mysql &
    [1] 24911
    [email protected] /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 :-

    [email protected] /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 !!
  • systemalisystemali Advisor Current User Role Beginner
    Scott,,

    Pheww.....finally i managed to change the password :D

    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 :D
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    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. =)
  • systemalisystemali Advisor Current User Role Beginner
    Ohh oke,

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

    Yes, at the end resolution is what matter :)

    Take care
  • kyakruukyakruu Entrant Inactive User Role Beginner
    ===
  • JamieJamie Entrant Current User Role Beginner
    Visit this link on how to reset root password - http://tekslate.com/user-account-management-mysql/
  • rwigfallrwigfall Entrant Current User Role Beginner
    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';
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.