Howto reset mysql password on ubuntu server 9.10

New IBM server has arrived, its IBM X3100-42X.

Standard install required; DNS, LAMP and OpenSSH server on  Ubuntu Server 9.10.

Problem came when I forgot password for MySQL. I think the solution isn’t far from what I done in FreeBSD.

First attempt login to MySQL server :

# mysql -uroot -p

Enter password:
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

Look MySQL process :

# ps ax | grep mysql

922 ?        S      0:00 /bin/sh /usr/bin/mysqld_safe
1030 ?        Sl     0:00 /usr/sbin/mysqld –basedir=/usr –datadir=/var/lib/mysql –user=mysql –pid-file=/var/run/mysqld/mysqld.pid –socket=/var/run/mysqld/mysqld.sock –port=3306
1031 ?        S      0:00 logger -t mysqld -p daemon.error
6967 pts/1    S+     0:00 grep mysql

# /etc/init.d/mysql stop

* Stopping MySQL database server mysqld                                                                                                              [ OK ]
#

Start MySQL without password :

# /usr/bin/mysqld_safe –skip-grant-tables &

[1] 7137
091125 20:32:49 mysqld_safe Logging to syslog.
091125 20:32:49 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

# mysql -uroot -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.37-1ubuntu5 (Ubuntu)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

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(‘newpasswordhere‘) where user=’root’;

Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye
#

Try again :

# mysql -uroot -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 5.1.37-1ubuntu5 (Ubuntu)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

Kill MySQL

# ps ax | grep mysql

7137 pts/1    S      0:00 /bin/sh /usr/bin/mysqld_safe –skip-grant-tables
7250 pts/1    Sl     0:00 /usr/sbin/mysqld –basedir=/usr –datadir=/var/lib/mysql –user=mysql –skip-grant-tables –pid-file=/var/run/mysqld/mysqld.pid –socket=/var/run/mysqld/mysqld.sock –port=3306
7251 pts/1    S      0:00 logger -t mysqld -p daemon.error
7265 pts/1    S+     0:00 grep mysql

# kill -9 7137

# ps ax | grep mysql

7250 pts/1    Sl     0:00 /usr/sbin/mysqld –basedir=/usr –datadir=/var/lib/mysql –user=mysql –skip-grant-tables –pid-file=/var/run/mysqld/mysqld.pid –socket=/var/run/mysqld/mysqld.sock –port=3306
7251 pts/1    S      0:00 logger -t mysqld -p daemon.error
7267 pts/1    S+     0:00 grep mysql
[1]+  Killed                  /usr/bin/mysqld_safe –skip-grant-tables
# kill -9 7251
# ps ax | grep mysql
7250 pts/1    Sl     0:00 /usr/sbin/mysqld –basedir=/usr –datadir=/var/lib/mysql –user=mysql –skip-grant-tables –pid-file=/var/run/mysqld/mysqld.pid –socket=/var/run/mysqld/mysqld.sock –port=3306
7269 pts/1    S+     0:00 grep mysql
# kill -9 7520
bash: kill: (7520) – No such process
# kill -9 7250
# ps ax | grep mysql
7271 pts/1    S+     0:00 grep mysql

Start MySQL :

# /etc/init.d/mysql start
* Starting MySQL database server mysqld                                                                                                              [ OK ]
* Checking for corrupt, not cleanly closed and upgrade needing tables.
#

Back to work!