ByHostigger

Resetting Your MYSQL Root Password

  1. The first step to resetting your root MySQL password on a Linux server is to stop MySQL. If you have a monitoring service for MySQL that will restart the service if it is down, make sure that service is also stopped for the time being, such as checkservd and cPanel. 
    /etc/init.d/mysql stop
  2. Next, start MySQL in Single User Mode and enter without a password.

    Warning:
    Restarting MySQL this way will allow anyone access to every database. To avoid this, stop eth0 and fuser -k any logged in user and touch /etc/nologin. 
    mysqld_safe --skip-grant-tables & mysql

    Note:
    Make sure to add & or the command prompt will not show.
  3. Enter the following commands in the MySQL prompt. The password below is only an example, replace 123456ABCDEF with the password of your choice. Our article Best Practice: Creating a Secure Password provides you with information on secure password best practices. 
    UPDATE mysql.user SET password=password("123456ABCDEF") 
WHERE user='root';
FLUSH PRIVILEGES;
exit;
  4. Stop MySQL safe and start MySQL and all other services that kept it from restarting like checkservd and cPanel normally. 
    /etc/init.d/mysql stop
/ect/init.d/mysql start
  5. Test your change by doing a test login, to log into MySQL, type it into the command line. 
    mysql
  6. You will be prompted to enter your password. Enter the new password, if you are logged in then you have successfully reset the MySQL root password.

Note:
If the MySQL user has been deleted, run the following query to recreate it:

INSERT INTO `mysql`.`user` ( `Host` , `User` , `Password`
 , `Select_priv` ,`Insert_priv` , `Update_priv` ,`Delete_priv` 
 , `Create_priv` , `Drop_priv` , `Reload_priv` , `Shutdown_priv` 
 , `Process_priv` , `File_priv` , `Grant_priv` , `References_priv` 
 , `Index_priv` , `Alter_priv` , `Show_db_priv` , `Super_priv` 
 ,`Create_tmp_table_priv` , `Lock_tables_priv` , `Execute_priv` 
 , `Repl_slave_priv`, `Repl_client_priv` , `Create_view_priv` 
 , `Show_view_priv` , `Create_routine_priv` , `Alter_routine_priv` 
 , `Create_user_priv` , `max_questions` , `max_updates`
 , `max_connections` , `max_user_connections` ) VALUES ( 'localhost'
 , 'root',PASSWORD('password1234'), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y'
 , 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y','Y', 'Y', 'Y', 'Y'
 , 'Y', 'Y', 'Y', 'Y', 'Y', '0', '0', '0', '0' );

Troubleshooting

You may encounter an error when trying to change the root password that looks like the example below:

mysql> show warnings; +---------+------+-----------------------------------------------+ 
| Level | Code | Message | +---------+------+-----------------------------------------------+ 
| Warning | 1265 | Data truncated for column 'Password' at row 1 | 
| Warning | 1265 | Data truncated for column 'Password' at row 2 | 
+---------+------+-----------------------------------------------+

 

If MySQL is giving you warnings when changing the password, type the following:
mysql>prompt

 

Then leave MySQL and run:

/usr/bin/mysql_fix_privilege_tables

Once this is complete, try to set the password again. If you have further issues, please contact our Support team and we will be happy to assist you!