Applicable to:
- Plesk Onyx for Linux
Symptoms
Failed to access MySQL via SSH using root account even if server root user password is modified.
Cause
By default there is no root user in MySQL for Plesk installation.
Resolution
To get root privileges in MySQL simply login with the admin username instead with one the following commands:
# mysql -uadmin -p`cat /etc/psa/.psa.shadow` psa
OR
# plesk db
If it is absolutely necessary, user root can be created with the following steps:
-
Log into the Plesk server via SSH;
-
Connect to MySQL:
# plesk db
-
Run the following command to create
rootuser, put appropriate password replacingmypass:MYSQL_LIN: CREATE USER 'root'@'localhost' IDENTIFIED BY 'mypass';
Note: If error is shown "ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'localhost'"Remove and recreate user
root:MYSQL_LIN: DROP USER 'root'@'localhost';
MYSQL_LIN: CREATE USER 'root'@'localhost' IDENTIFIED BY 'mypass';
-
Grant all the privileges to this user:
MYSQL_LIN: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';
-
Save changes:
MYSQL_LIN: FLUSH PRIVILEGES;
-
Exit MySQL:
MYSQL_LIN: exit;
Comments
6 comments
I get this error
The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.
Hi Che,
Thanks for sharing.
I fixed the article, now it should be fine.
MariaDB [psa]>
MariaDB [psa]> CREATE USER 'root'@'localhost' IDENTIFIED BY PASSWORD PASSWORD('My#PasswordWithExtendeCharate$sr');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PASSWORD('0#vVs0u3ahbQw')' at line 1
Whta is wrong?
Hello @Abdelkarim Mateos,
Could you please clarify the version of MySQL server and provide the output of the command below:
--------
mysql -V
---------
I would like to test the command on my test server with the same MySQL version and check if the error is reproduced.
Server version: 10.2.24-MariaDB MariaDB Server
Hello @Abdelkarim Mateos,
The solution in the article was updated to be suitable for new MariaDB and MySQL versions.
Thank you for pointing out!
Please sign in to leave a comment.