ServerPilot configures MySQL to manage databases on your server. Typically, when logged into SSH as the root user, you would only need to type the following at the command line (SSH) prompt to access MySQL:
mysql
This command will automatically reference the file which contains the MySQL root user's MySQL password so you do not have to type it in. The root password for MySQL is contained in this file:
/root/.my.cnf
Should you need to change that password for any reason, a few steps need to be taken. Please note there are two different processes here: One for a situation in which the root MySQL password is currently working, and one for a non-working root MySQL password.
Note: The MySQL error log is a good place to find issues with MySQL, and is located here:
/var/log/mysql/error.log
This section covers changing a MySQL root password that is working, meaning you can log into MySQL as the root user by simply typing mysql at the SSH prompt. There will not be any downtime for this process if these steps are followed.
Log into SSH as the root user, then log into MySQL by typing:
mysql
At the MySQL prompt, type the following, but replace put-your-new-password-here with the actual password you want to use. You do need to leave the single quotes around the new password. Please make a note of the password, as you will need to put it into a file in the next step.
SET PASSWORD FOR root@'localhost' = PASSWORD('put-your-new-password-here');
Quit MySQL by typing:
quit
Next, you'll need to update the file that contains the MySQL root user's password so you can SSH into your server as root and run the mysql command witout typing a password.
sudo nano /root/.my.cnf
Update the password field to the new password you entered above, save, and exit the text editor.
Lastly, test to make sure the new password works:
mysql
All set!
This section covers regaining root access to MySQL when receiving this error:
ERROR 1045: Access denied for user: 'root@localhost' (Using password: YES)
Log into your server via SSH as the root user, and stop MySQL by typing:
sudo service mysql stop
Then run the following command:
sudo test ! -e /var/run/mysqld && sudo mkdir -p /var/run/mysqld && sudo chown mysql:mysql /var/run/mysqld
It will not return any output. Start MySQL into a special mode by typing this:
sudo mysqld --skip-grant-tables --skip-networking &
Its output should be similar to below, though the number displayed will be different:
sudo mysqld --skip-grant-tables --skip-networking & [1] 14744
You'll then be returned to the standard SSH prompt. Now, type the following to log into MySQL:
mysql
You will not be prompted for a password, and it should look something like this:
~# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.25-0ubuntu0.16.04.2-log (Ubuntu) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective Owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Issue the following command that will allow you to change the password:
FLUSH PRIVILEGES;
Which yields:
Query OK, 0 rows affected (0.00 sec)
Next, we reset the actual password by changing put-your-new-password-here to your actual password. Please make a note of the password you set, as we will need to enter it into the /root/.my.cnf file when we're done, and please also retain the single quotes around the new password.
SET PASSWORD FOR root@'localhost' = PASSWORD('put-your-new-password-here');
You should receive a notification such as:
Query OK, 0 rows affected, 1 warning (0.00 sec)
Exit MySQL by typing:
quit
Next, type the following command:
sudo killall mysqld
No output will be returned. Now, we start MySQL normally:
sudo service mysql start
Lastly, if you changed the password away from what was in /root/.my.cnf, you'll need to update that file. If you used the same value, you do not need to perform this step:
sudo nano /root/.my.cnf
Update the password field there, then save and exit the text editor.
You should now be able to successfully log into MySQL as the root user by typing the following command:
mysql