phpmyadmin Question posted in
The official documentation can be found here.

MariaDB on Debian: Syntax error: How to switch unix socket authentication back to password-based? – Phpmyadmin

I just installed Mariadb on my new unix system (Mariadb server version 10.1.37 Debian 9.6). I have the following users:

+------------+-----------------------+-------------+-----------+
| user       | authentication_string | plugin      | host      |
+------------+-----------------------+-------------+-----------+
| root       |                       | unix_socket | localhost |
| phpmyadmin |                       |             | localhost |
+------------+-----------------------+-------------+-----------+

I want to be able to log in as ‘root’ with a password (and not by using the unix_socket plugin). I tried to change to password-based authentication using ALTER USER command. I enter MariaDB from the root-account using “sudo mysql”. Then I tried each of the commands below: 

MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED WITH
mysql_native_password BY 'mypassword';

MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED VIA
mysql_native_password BY 'mypassword';

MariaDB [(none)]> ALTER USER root@localhost IDENTIFIED VIA 
mysql_native_password;
SET PASSWORD = PASSWORD('mypassword');

MariaDB [(none)]> ALTER USER root@localhost IDENTIFIED WITH 
mysql_native_password;
SET PASSWORD = PASSWORD('mypassword');

MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED WITH 
mysql_native_password;
SET PASSWORD = PASSWORD('mypassword');

In all cases I get the following error:
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 ‘…’ at line 1.

What is the right syntax/way to change the root login from socket to password-based? Any help would be appreciated, many thanks.

Tags:

2

Answers


  1. 0

    Seems like You’ve fresh installed MariaDB or MySQL.

    After installation You’ve to simply run following from root:

    mysql_secure_install

    or: 

    sudo mysql_secure_install

    More info here

    As I read manuals MariaDB has made auth socket feature.

    So try to uninstall plugin:

    UNINSTALL SONAME 'auth_socket';

    and/or switch to native password auth:

    ALTER USER root@localhost IDENTIFIED VIA mysql_native_password;

    if it will not work also, so try to empty plugin field of user table and flush privileges:

    USE mysql; 
UPDATE user SET plugin='' WHERE user ='root'; 
FLUSH PRIVILEGES;
    Login or Signup to reply.
  2. 0

    After sessions of safariing, exploring, finding and searching, these 3 commands help me for MariaDB case mentioned in this question:

    mysqld_safe --skip-grant-tables --skip-networking &

UPDATE user SET plugin='' WHERE user ='root'; 

UPDATE mysql.user SET 
    authentication_string = PASSWORD('UNCOMPROMISABLE_STRONGHOLD_PASSWORD') 
    WHERE User = 'root' AND Host = 'localhost';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search