3

I have long since forgotten the password for the root user on one of my boxes. Is there a way I can change it without having to log in to the instance, or will I have to reinstall?

Nick Stinemates
  • 41,511
  • 21
  • 59
  • 60
  • This might help "[Recover MySQL root password](http://www.cyberciti.biz/tips/recover-mysql-root-password.html)". – jfs Sep 10 '08 at 05:43

3 Answers3

4

A quick Google resulted in this answer. In the root shell type:

mysqladmin -u root password <password>
Nick Stinemates
  • 41,511
  • 21
  • 59
  • 60
  • 1
    Its good to have the answer here even though it is discoverable on Google. It helps Stack Overflow become a more comprehensive repository of programming answers so I'm going to mod up this question. – Adam Pierce Sep 10 '08 at 05:45
  • This is not a very secure solution as the password is now part of your history. – user12345 Mar 27 '14 at 04:01
4

Step 1

Stop database:

shell> /etc/init.d/mysql stop

Step 2

Restart database

  • without password autentification
  • without connection to the network

Access to database is only possible through it's sock file '/var/lib/mysql/mysql.sock'.

shell> mysqld --user=mysql --pid-file=/var/lib/mysql/mysqld.pid \
       --socket=/var/lib/mysql/mysql.sock --datadir=/var/lib/mysql \
       --skip-grant-tables --skip-networking  &

Step 3

Connect to the database and change password:

shell> mysql --database mysql --socket=/var/lib/mysql/mysql.sock

If you want to, show all users:

mysql> select User, password from user;

Set new password:

mysql> update user set password=password('NEW PASS') WHERE User='USERNAME';

Leave database connection:

mysql> exit

Step 4

Restart database server "normally".

shell> kill `cat /var/lib/mysql/mysqld.pid`
shell> /etc/init.d/mysql start
BlaM
  • 28,465
  • 32
  • 91
  • 105
0

If you are running an Ubuntu server (possibly also Debian?) you can easily reset.

If you are on 12.04:

sudo dpkg-reconfigure mysql-server-5.5

If you are on 10.04:

sudo dpkg-reconfigure mysql-server-5.1

If you are not sure which mysql-server version is installed you can try: 

dpkg --get-selections | grep mysql-server

See for more info:

https://help.ubuntu.com/12.04/serverguide/mysql.html https://help.ubuntu.com/10.04/serverguide/mysql.html

user12345
  • 2,876
  • 2
  • 24
  • 25