If the root MySQL password is required and can’t be located, it can be retrieved or reset.
- These instructions are intended specifically for recovering or resetting the root MySQL password.
- You will need root-level SSH access to a server running cPanel to recover the password; resetting the password can be done via WHM on cPanel servers and via SSH (as root) on servers that don’t run cPanel.
- To learn more about MySQL, check out our series: MySQL via Command Line 101: Basic Database Interaction and MySQL via Command Line 102: Basic User Interaction.
Step #1: Recovering the Root MySQL Password on a cPanel server
On servers running cPanel, the root MySQL credentials are stored locally in a file that only the root user can access. After you’ve connected to the server as root via SSH, you can view the credentials by running the following command:
That should output a result containing the MySQL password, which will appear similar to the following:
[root@host ~]# cat /root/.my.cnf
Make a note of the current password.
Step #2: Resetting the Root MySQL Password
If you need to reset the root MySQL password and have access to WebHost Manager on a cPanel server, you can do so by selecting MySQL Root Password from WHM’s menu. If you don’t have access to WHM, you still can change the password via CLI after stopping and restarting MySQL in single-user mode.
- Stop MySQL:
service mysql stop
- Start MySQL in single-user mode, bypassing password authorization, by pasting in the command below (the ampersand is required):
mysqld_safe --skip-grant-tables &
- Now type “mysql” to connect:
- At the mysql prompt, change the root password using the following three commands:
UPDATE mysql.user SET password=password("YourNewStrongPassword") WHERE user='root';
- Now stop MySQL and start it in normal mode:
service mysql stop
service mysql start
- Verify that you are able to connect to MySQL by entering “mysql”.
- Finally, update the MySQL root password in /etc/.my.cnf to match the new value. You can use vi, vim or nano:
vim /root/.my.cnfIn vim, you can press “a” or “i” to enter text insertion mode; pressing the escape key (Esc) on your keyboard returns you to command mode. For a refresher on editing files with vim, see New User Tutorial: Overview of the Vim Text Editor.
Tagged with: mysql