Articles in this section

See more

Failed to access MySQL via SSH using root account

Avatar
Maxim Krasikov
Updated
Follow

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:

  1. Log into the Plesk server via SSH;

  2. Connect to MySQL:

    # plesk db

  3. Run the following command to create root user, put appropriate password replacing mypass:

    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';

  1. Grant all the privileges to this user:

    MYSQL_LIN: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';

  2. Save changes:

    MYSQL_LIN: FLUSH PRIVILEGES;

  3. Exit MySQL:

    MYSQL_LIN: exit;

Comments

6 comments

Sort by Date Votes
  • Avatar
    Che Shiva

    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.

    0
    Comment actions Permalink
  • Avatar
    Pavel Mikhaylov

    Hi Che,

    Thanks for sharing.

    I fixed the article, now it should be fine.

    0
    Comment actions Permalink
  • Avatar
    Abdelkarim Mateos

    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?

    0
    Comment actions Permalink
  • Avatar
    Dinara Aspembitova

    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.

    0
    Comment actions Permalink
  • Avatar
    Abdelkarim Mateos

    Server version: 10.2.24-MariaDB MariaDB Server

     

     

    0
    Comment actions Permalink
  • Avatar
    Maxim Krasikov

    Hello @Abdelkarim Mateos,

    The solution in the article was updated to be suitable for new MariaDB and MySQL versions.
    Thank you for pointing out!

    0
    Comment actions Permalink

Please sign in to leave a comment.

Have more questions? Submit a request

Related articles