mysql 127.0.0.13306_MySQL 问题解决

问题如下：

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

我的问题是terminal中可以使用管理员账号登录mysql，但是在使用workbench时，链接就会出现上面的问题

问题如图

mysql1.png

网上看了许多方法，试验了几个，发现新建一个用户解决办法最简单。

创建用户

格式：create user ‘userName’@’localhost’ identified by ‘passward’;

mysql> create user 'dfzxk'@'localhost' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

给user上帝权限

mysql> grant all on *.* to dfzxk@"%" identified by "123456";

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql授权东西太多，不多说了

查看用户权限

mysql> show grants for dfzxk;

+--------------------------------------------+

| Grants for dfzxk@% |

+--------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'dfzxk'@'%' |

+--------------------------------------------+

1 row in set (0.00 sec)

mysql2.png

再次使用workbench，已经链接成功了。

遇到的坑

mysql> create user 'dflx'@'localhost' identified by '123456';

ERROR 1396 (HY000): Operation CREATE USER failed for 'dflx'@'localhost'

据说是已经建立了这个用户，我Ubuntu的xwindows桌面崩溃过一次，因为\目录太小的原因，我准备增大根目录的容量，把\home移动5g给它，可是现在卡在了这里。

sql语句查看一下

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 '%'

果然我的dflx用户还在，我workbench链接了一下，成功了，但是说没有授权，可是我workbench进行了，增加，查询，好像没有问题啊(嘻嘻)

mysql3.png

show databases;

use test;

show tables;

select * from user;

insert into user values('dfzxk','123456');

结果如下：

mysql0.png

学的是SQL server，对于mysql这些有些不太熟悉，但是sql语法大体通用的。

修改root密码如下

上面说的

mysql> set password for root@localhost=password("123456");

Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> select host,user,grant_priv from user;

+-----------+------------------+------------+

| host | user | grant_priv |

+-----------+------------------+------------+

| localhost | root | Y |

| localhost | mysql.session | N |

| localhost | mysql.sys | N |

| localhost | debian-sys-maint | Y |

| localhost | dflx | N |

| % | dflx | N |

+-----------+------------------+------------+

6 rows in set (0.04 sec)

mysql> select host,user,grant_priv,alter_priv,password_expired from user;

+-----------+------------------+------------+------------+------------------+

| host | user | grant_priv | alter_priv | password_expired |

+-----------+------------------+------------+------------+------------------+

| localhost | root | Y | Y | N |

| localhost | mysql.session | N | N | N |

| localhost | mysql.sys | N | N | N |

| localhost | debian-sys-maint | Y | Y | N |

| localhost | dflx | N | N | N |

| % | dflx | N | Y | N |

+-----------+------------------+------------+------------+------------------+

6 rows in set (0.00 sec)

mysql> select host,user,grant_priv,pass_last_change from user;

ERROR 1054 (42S22): Unknown column 'pass_last_change' in 'field list'

mysql> select host,user,grant_priv,pass_last_changed from user;

ERROR 1054 (42S22): Unknown column 'pass_last_changed' in 'field list'

mysql> select host,user,grant_priv,password_last_changed from user;

+-----------+------------------+------------+-----------------------+

| host | user | grant_priv | password_last_changed |

+-----------+------------------+------------+-----------------------+

| localhost | root | Y | 2018-07-01 17:10:59 |

| localhost | mysql.session | N | 2018-07-01 17:11:00 |

| localhost | mysql.sys | N | 2018-07-01 17:11:00 |

| localhost | debian-sys-maint | Y | 2018-07-01 17:11:01 |

| localhost | dflx | N | 2018-07-07 21:10:00 |

| % | dflx | N | 2018-07-07 21:24:55 |

+-----------+------------------+------------+-----------------------+

6 rows in set (0.00 sec)

mysql> set password for root@localhost=password("123456");

Query OK, 0 rows affected, 2 warnings (0.02 sec)

我Ubuntu的xwindows桌面崩溃过一次，因为\目录太小的原因，

当时安装系统时看网上教程一大推说，给根目录10g就行了，swap是内存二倍，

感觉这个建议很坑啊，网上一大推抱怨这个\目录10g的，可是给根目录增加容量

不容易啊！我准备增大根目录的容量，把\home移动5g给它，可是现在卡在了这里。

我整理了半天垃圾，现在还是这个情况,如果\目录剩余太小了，会发生很可怕的问题。

dfzxk@satan-master:~$ df -h

文件系统 容量 已用 可用 已用% 挂载点

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.9G 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/1003

参考文章:

[mysql Access denied for user root@localhost错误解决方法总结](http://www.111cn.net/database/mysql/44142.htm)

[MYSQL登录错误：mysqladmin: connect to server at 'localhost' failed](http://blog.csdn.net/j3smile/article/details/14134879)

[Failed to connect to mysql at 127.0.0.1:3306 with user root access denied for user 'root'@'localhost'(using password:YES)](https://stackoverflow.com/questions/25777943/failed-to-connect-to-mysql-at-127-0-0-13306-with-user-root-access-denied-for-us)

[

解决database - Failed to connect to mysql at 127.0.0.1:3306 with user root access denied for user 'root'@'localhost'](http://www.111cn.net/database/mysql/44142.htm)

[MySQL添加用户、删除用户与授权](http://www.cnblogs.com/wanghetao/p/3806888.html)

教您如何查看MySQL用户权限 [教您如何查看MySQL用户权限](http://www.cnblogs.com/cnteam/articles/4272460.html)

windows下出现问题

Microsoft Windows [版本 10.0.16299.125]

(c) 2017 Microsoft Corporation。保留所有权利。

C:\WINDOWS\system32>cd C:\mysql-5.7.20-winx64\mysql-5.7.20-winx64\bin

C:\mysql-5.7.20-winx64\mysql-5.7.20-winx64\bin>mysqld.exe

C:\mysql-5.7.20-winx64\mysql-5.7.20-winx64\bin>mysql

ERROR 1045 (28000): Access denied for user ‘ODBC’@’localhost’ (using password: NO)

C:\mysql-5.7.20-winx64\mysql-5.7.20-winx64\bin>mysql -u -p

ERROR 1045 (28000): Access denied for user ‘-p’@’localhost’ (using password: NO)

C:\mysql-5.7.20-winx64\mysql-5.7.20-winx64\bin>mysql -u root

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

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

mysql>