If you have forgotten your root password previously set , you can set a new password.
For Windows system, follow the steps listed below.
Use the following procedure for resetting the password for any MySQL root accounts on Windows:
- Log on to your system as Administrator.
- Stop the MySQL server if it is running. If server is running as a Windows service, go to the Services manager to stop MySQL service.
- Create a text file and place the following statements in it. Replace the password with the password that you want to use.
UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGE;
Here FLUSH statement tells the server to reload the grant table into memory.
- Save the file. For this example, the file will be named
C:\mysql-init.txt.
- Open a console window to get to the command prompt:
- Start the MySQL server that looks like :
C:\> C:\mysql\bin\mysqld-nt --init-file=C:\mysql-init.txt
-
After the server has started successfully, delete c:\mysql-init.txt.
- Stop the MySQL server, then restart it in normal mode again. If you run the server as a service, start it from the Windows Services window. You should now be able to connect to MySQL as
root using the new password.
For unix kind of system,follow the steps listed below.
1. Log on to your system as the Unix mysql user that the mysqld server runs as.
2. Locate the .pid file that contains the server’s process ID. The exact location and name of this file depend on your distribution, host name, and configuration. Common locations are /var/lib/mysql/, /var/run/mysqld/, and /usr/local/mysql/data/. Generally, the file name has an extension of .pid and begins with either mysqld or your system’s host name.
You can stop the MySQL server by sending a normal kill (not kill -9) to the mysqld process, using the path name of the .pid file in the following command:
shell> kill `cat /mysql-data-directory/host_name.pid`
3. Create a text file and place the following statements in it. Replace the password with the password that you want to use.
UPDATE mysql.user SET Password=PASSWORD(’MyNewPass’) WHERE User=’root’;
FLUSH PRIVILEGES;
The FLUSH statement tells the server to reload the grant tables into memory.
4. Save the file. For this example, the file will be named /home/me/mysql-init.
5. Start the MySQL server with the special –init-file option:
shell> mysqld_safe –init-file=/home/me/mysql-init &
6. After the server has started successfully, delete /home/me/mysql-init.
You should now be able to connect to MySQL as root using the new password.
You can also set the new password using the mysql client if this is run under your local box:
1. Stop mysqld and restart it with the –skip-grant-tables option.
2. Connect to the mysqld server with this command:
shell> mysql
3. Issue the following statements in the mysql client. Replace the password with the password that you want to use.
mysql> UPDATE mysql.user SET Password=PASSWORD(’MyNewPass’)
-> WHERE User=’root’;
mysql> FLUSH PRIVILEGES;
You should now be able to connect to MySQL as root using the new password.