MySQL: Recover root password

 

 

 

 

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

 

 

1 Stop MySQL server:

# service mysql start

 

2. Add –skip-grant-tables to your mysql start script

# nano /etc/rc3.d/S20mysql

 

——————-

 

case "${1:-''}" in
  'start')
        sanity_checks;
        # Start daemon
        log_daemon_msg "Starting MySQL database server" "mysqld"
        if mysqld_status check_alive nowarn; then
           log_progress_msg "already running"
           log_end_msg 0
        else
            # Could be removed during boot
            test -e /var/run/mysqld || install -m 755 -o mysql -g root -d /var/run/mysqld
 
            # Start MySQL!
            /usr/bin/mysqld_safe –skip-grant-tables > /dev/null 2>&1 &
 
            # 6s was reported in #352070 to be too few when using ndbcluster
            for i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14; do
———————
 

 

3. Start your server again

 

# service -mysql start

 

4. Log in using mysql root user without entering a password:

# mysql -u root mysql

 

And let's do the actual reset:

 

——-

 

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 5.1.49-3 (Debian)
 
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> update user set password=Password('1234') where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql> exit
Bye
 
——————
 
5. Stop the server one more time
 
# service mysql stop
 
6. Remove –skip-grant-table from your mysql startup command
 
# nano /etc/rc3.d/S20mysql
 
 
7. Start your mysqlserver
 
# service mysql start
 
 
8. Enjoy your server:
 
 
———————-
root@debian:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 5.1.49-3 (Debian)
 
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql>
————————-
 

 

Tagged , , . Bookmark the permalink.

Leave a Reply