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!

Resetting the Root Password in CentOS 8

  • Restart the server and press the e button in the GRUB start menu to edit the startup entry.
  • Add the following parameters at the end of the line starting with linux ($root)/:

rd.break enforcing=0

  • The line should now look something like this:

linux ($root)/vmlinuz-4.18.0-80.11.2.el8_0.x86_64 root=/dev/mapper/centos-root\ ro crashkernel=auto resume=/dev/mapper/centos-swap rd.lvm.lv=centos/root rd.l\ vm.lv=centos/swap rd.break enforcing=0

The parameter rd.break causes the boot process to be interrupted before control is passed from initramfs to systemd. This allows the initramfs prompt to be used for command input, and the parameter enforcing=0 puts SELinux in permissive mode. This saves the later – and possibly very time-consuming – relabeling of the file system, which would be necessary if SELinux were switched off.

  • Press Ctrl+x to boot the system with the changed parameters, and the switch_root prompt of initramfs will be displayed.
  • Since the filesystem under /sysroot/ is mounted with read-only permissions, you must first remount it with write permissions:

switch_root:/# mount -o remount,rw /sysroot

  • Now switch to a chroot environment:

switch_root:/# chroot /sysroot

The prompt changes to sh-4.4#.

  • Now you can change the password via passwd:

sh-4.4# passwd
Enter new UNIX password:
Retype new UNIX password:
passwd: password updated successfully

touch /.autorelabel

  • To leave the chroot environment, type the following command:

exit

  • Reboot the server. To do this, enter the following command:

reboot