On the other hand, If you have set a root password, but forgot what it was, you can set a new password with the following procedure:
Stop MySql Service
First we need to stop the running process of the MySql server.
/etc/init.d/mysqld stop
If the above command does not work for you, you will need to find the appropriate comand for your linux distribution. Depending on what type of MySql server you are running, other variants of the init.d script are mysqld-max and mysqld-ndb.
Start without Grant Tables
Now we need to start the MySql server without any grant tables. This means the MySql server should be running without any access restrictions.
mysqld_safe --skip-grant-tables &
Once you have got mysql running with the “–skip-grant-tables” option, we now need to login to the mysql server. Open a new shell and type in the following:
mysql -u root
It should now take you to the Mysql prompt:
Now lets update the Mysql root users password. In this example I an updating the password to be “FpS0Fb7W”:
UPDATE mysql.user SET Password = PASSWORD('FpS0Fb7W') WHERE User = 'root';
Query OK, 2 rows affected (0.21 sec) Rows matched: 2 Changed: 2 Warnings: 0
Usually you will have more than one set of privileges for your root account. One for localhost access and the other for access from anywhere (%). If you have more than one root user account privilege, it will update both accounts. In order to update only the localhost priviledge, use:
UPDATE user SET Password=PASSWORD('YOUR_PASSWORD') WHERE Host='localhost' AND User='root';
Now lets reload the Mysql Databases privileges
FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
Gotchas
If you ever happened to get an error (when starting mysql with –skip-grant-tables) similar to the one shown below:
mysql ended
Then its probabily because there are some permission problems. Shown below is what the mysql error log outputted in that instance.
060519 09:01:39 mysqld started /usr/sbin/mysqld-max: Can't read dir of '/root/tmp/' (Errcode: 13) /usr/sbin/mysqld-max: Can't create/write to file '/root/tmp/ibwA6mDj' (Errcode: 13) 060519 9:01:39 InnoDB: Error: unable to create temporary file; errno:13 060519 9:01:39 [ERROR] Can't init databases 060519 9:01:39 [ERROR] Aborting 060519 9:01:39 [Note] /usr/sbin/mysqld-max: Shutdown complete 060519 09:01:40 mysqld ended
This basically means that your systems $TMPDIR definition is set to the root directory. To fix, just do the following:
export TMPDIR=/tmp/
This will then set the TMPDIR to the appropriate directory and fixes the error.