After the installation of MySQL server or MariaDB on Ubuntu, I would like to use phpMyAdmin or Adminer to access the MySQL server. But the first time the error comes like below.

Access denied for user ‘root’@’localhost’

mysqli_real_connect(): (HY000/1698): Access denied for user ‘root’@’localhost’

MySQL connect error on phpMyAdmin

 

MySQL connect error on Adminer

Here is the solution to make the root connect to a server without error.

First SSH to the terminal window of the Ubuntu 18.04.

Run the following command to check the MySQL root user’s information.

davidyin@example:~$ sudo mysql -uroot -p
[sudo] password for davidyin:
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 169
Server version: 10.1.38-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]> SELECT User, Host, plugin FROM mysql.user;
+——+———–+————-+
| User | Host | plugin |
+——+———–+————-+
| root | localhost | unix_socket |
+——+———–+————-+
1 row in set (0.00 sec)

MySQL root user plugin

I saw the root user is using unix_socket. I will change it to mysql native password.

MariaDB [(none)]> UPDATE mysql.user SET plugin=’mysql_native_password’ WHERE User=’root’;
Query OK, 1 row affected (0.11 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.08 sec)

MariaDB [(none)]> exit;
Bye

Update MySQL root user auth method

 

Now, at this step, I can access Adminer and phpMyAdmin without any problems.

Another workaround I found by Google. Someone creates a new MySQL user and give the superuser permission to it.  Then use this superuser to access MySQL server.

David Yin

David is a blogger, geek, web developer. Founder of FreeInOutBoard.com. He check David Yin's Blog everyday. If you like his post, you can say thank you here

One Reply to “Hot to fix the problem on the new installation MariaDB”

Leave a Reply

Your email address will not be published. Required fields are marked *