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’
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:
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)
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;
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.