If your a terrible DBA like me here are the six steps to reset a forgotten MySQL root user password:
1. stop mysql
# /etc/init.d/mysql stop
The output should look like this:
* Stopping MySQL database server mysqld
…done.
2. restart mysql without a password
# mysqld_safe –skip-grant-tables &
The output should look like this:
[1] 28808
100222 18:32:27 mysqld_safe Logging to syslog.
100222 18:32:27 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
3. connect to mysql using the mysql client
# mysql -u root
The output should look like this:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.37-1ubuntu5.1 (Ubuntu)
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
4. set a new mysql root user password
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update user set password=PASSWORD(“NEWROOTPASSWORDHERE“) where User=’root’;
Query OK, 0 rows affected (0.02 sec)
Rows matched: 3 Changed: 0 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.09 sec)
mysql> quit
Bye
5. stop mysql
# /etc/init.d/mysql stop
The output should look like this:
* Stopping MySQL database server mysqld
100222 18:35:29 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
…done.
[1]+ Done mysqld_safe –skip-grant-tables
6. restart mysql
# /etc/init.d/mysql start
Who needs to remember passwords? 