Changing MySQL Root Password

Last modified: 21 May 2007

WARNING: This documentation covers Parallels H-Sphere versions up to 3.1. For the latest up-to-date Parallels H-Sphere documentation, please proceed to the official Parallels site.

This document explains how to change the root user password in MySQL access privilege database.

Option 1

1. Login as root to the box with the MySQL server.
2. Stop MySQL server.
3. Open the mysql server startup script. This is the file you have just executed to stop MySQL server.
4. Find the line that contains the mysqld_safe command and add --skip-grant-tables as its parameter.
5. Start MySQL server.
6. Login as the mysql user and connect to the mysql user/permission database and run the update queries:
   # mysql -u root mysql
   mysql> UPDATE user SET Password=PASSWORD('newrootpassword') WHERE User='root';
   mysql> FLUSH PRIVILEGES;
   replacing newrootpassword with the new root password to the box with the MySQL server.
7. Exit mysql database by typing \q.
8. Exit mysql user console by typing exit
9. Stop MySQL server.
10. Open the mysql server startup script and remove the --skip-grant-tables parameter you added above.
11. Start MySQL server.
12. Open the file ~mysql/.my.cnf and update the password in the corresponding line.

Option 2

1. Stop the MySQL daemon:
   kill `pidof mysqld`
   ps auxw | grep mysql
2. Temporarily create a text file in the following location:
   /hsphere/local/config/mysql/file_name
   This file must contain a string with an sql command similar to this one:
   SET PASSWORD FOR 'root'@'localhost' = PASSWORD('your_new_mysql_password');
3. Manually start mysql with a special option:
   mysqld_safe --init-file=/hsphere/local/config/mysql/file_name &
4. Check whether the new password is working:
   mysql -p
   If everything is fine, you'll get a screen like this:
   Welcome to the MySQL monitor. Commands end with ; or \g.
   Your MySQL connection id is 2 to server version: 5.0.27-standard-log
   Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
5. Kill mysql:
   kill `pidof mysqld`
6. Remove the temporary file:
   rm -f /hsphere/local/config/mysql/file_name
7. Start MySQL server.
8. Open the file ~mysql/.my.cnf and update the password in the corresponding line.

This procedure is simplier, faster and more secure as there is neither editing the script rc.d/mysqld startup nor using the command --skip-grant-tables.