Hot to fix the problem on the new installation MariaDB

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:
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)

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

 

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.