Common Debugging Methods for MySQL

Two types of common bugs when we configure MySQL with root access.

Versions
Environment	Ubuntu 19.10
MySQL	8.0.19

Problem: root user denied access

1	$ systemctl stop mysql;

Create temporary path.

1	$ sudo mkdir -p /var/run/mysqld

Change permissions.

1	$ sudo chown mysql:mysql /var/run/mysqld

Enter MySQL in safe mode.

1	$ sudo mysqld_safe --skip-grant-tables

Get into MySQL.

1	$ mysql -uroot

Some people successfully solve the problem by alter user, but it didn’t work in my case. So here’s the work-around:

1	> use mysql;

1	> select user, host from user;

1	> drop user root@localhost;

1	> flush privileges;

1	> create user root@localhost identified by '';

1	> flush privileges;

And now we should be able to use mysql -uroot to get into MySQL.

Grant privileges. First get into the safe mode via the commands before. After get into MySQL:

1	> GRANT ALL PRIVILEGES ON . TO 'root'@'localhost' WITH GRANT OPTION;

For security purposes, create another standard user without root access:

1	> use mysql;

1	> create user NAME@localhost identified by '';

1	> flush privileges;

And the problem shall be solved.