Failed to Connect to MySQL at 127.0.0.1:3306 with user root Access denied for user ‘root’@’localhost’

My problem is that the administrator account can be used to log in to mysql in Terminal, but the above problem will occur when using Workbench

The problem is shown in figure

I looked at many methods online, tried a few, and found that creating a new user solution is the easiest.

Create user ‘userName’@’localhost’ IDENTIFIED by ‘passward’;

mysql> create user 'dfzxk'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
Copy the code

Give user God permission

mysql> grant all on *.* to dfzxk@"%" identified by "123456"; Query OK, 0 rows affected (0.00 SEC)Copy the code

Mysql licenses too many things, so I won’t go into that

Viewing User Rights

mysql> show grants for dfzxk;
+--------------------------------------------+
| Grants for dfzxk@%                         |
+--------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'dfzxk'@The '%' |
+--------------------------------------------+
1 row in set (0.00 sec)
Copy the code

Using workbench again, the link is successful.

In the pit of

mysql> create user 'dflx'@'localhost' identified by '123456';
ERROR 1396 (HY000): Operation CREATE USER failed for 'dflx'@'localhost'
Copy the code

It is said that the user has been set up, my Ubuntu XWindows desktop crashed once, because the \ directory is too small, I am going to increase the capacity of the root directory, move the \home 5g to it, but now it is stuck here.

SQL statement to check

mysql> show database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_shiyan       |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select host,user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | dfzxk            |
| localhost | debian-sys-maint |
| localhost | dflx             |
| localhost | dfzxk            |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
7 rows in set (0.00 sec)

mysql> show grants for dflx;
ERROR 1141 (42000): There is no such grant defined for user 'dflx' on host The '%'
Copy the code

My DFLX user is still there. I linked to the DFLX user by workbench, and it succeeded, but said that there was no authorization, but I added the query by Workbench, and it seems that there is no problem.

show databases;
use test;
show tables;
select * from user;
insert into user values('dfzxk'.'123456');
Copy the code

The results are as follows:

Learn SQL Server, some not familiar with mysql, but SQL syntax general.

The above said

My Ubuntu XWindows desktop crashed once, because \ directory is too small, when installing the system to see a large online tutorial said, to the root directory 10G on the line, swap is twice the memory, feel this suggestion is very pit ah, a big push on the Internet complain about this \ directory 10G, but to the root directory to increase the capacity is not easy ah! I was going to increase the capacity of the root directory and move 5g to \ Home, but now I'm stuck here.Copy the code

I sorted out the garbage for a long time, and now it is still the same situation. If the \ directory surplus is too small, a terrible problem will occur.

dfzxk@satan-master:~$df -h File system capacity used Available Used % Mount point udev 1.9g 0 1.9g 0% /dev TMPFS 382M 41M 342M 11% /run /dev/sda8 9.4g 7.0g 2.0g 79% / /dev/sda9 47G 13G 32G 30% /usr TMPFS 1.9g 65M 1.8g 4% /dev/ SHM TMPFS 5.0m 4.0k 5.0m 1% /run/lock TMPFS 1.2g 0 1.9g 0% /sys/fs/cgroup /dev/sda10 22G 1.4g 19G 7% /home /dev/sda1 196M 29M 168M 15% /boot/efi TMPFS 382M 40K 382M  1% /run/user/1003Copy the code

Mysql Access denied for user root@localhost

Mysqladmin: connect to server at ‘localhost’ failed

Failed to connect to mysql at 127.0.0.1:3306 with user root access denied for user ‘root’@’localhost'(using Password :YES) Failed to connect to mysql at 127.0.0.1:3306 with user root access denied for user ‘root’@’localhost’

MySQL add, delete, and authorize users

How to query MySQL user permissions