MySQL-->基础知识-->MySQL 常见 问题汇总

 

1.1.1   登录mysql 报错1 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

 

[root@localhost ~]# mysql -u root–p

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

 

解决方法：

重启mysql服务：

$service mysqld start

[root@localhost ~]# service mysqld start

Starting mysqld:                                          [  OK  ]

1.1.2   登录mysql 报错2 ERROR 1045 (28000): Access denied for user'root'@'localhost' (using password: YES)

$mysql -u root@localhost

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

解决方法：

1.关闭mysql

   # service mysql stop

2.屏蔽权限

[root@localhost ~]# mysqld_safe --skip-grant-table

140711 07:30:51 mysqld_safe Logging to '/var/log/mysqld.log'.

140711 07:30:51 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

 

1.1.3   登录mysql 报错3 ERROR 1044 (42000): Access denied for user''@'localhost' to database 'df01'

ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'df01'

因为mysql数据库的user表里，存在用户名为空的账户即匿名账户，导致登录的时候是虽然用的是root，但实际是匿名登录的

 

[root@localhost ~]#mysql -u root -p

Enter password:

Welcome tothe MySQL monitor. Commandsendwith;or \g.

Your MySQL connection idis3

Server version:5.1.66Source distribution

 

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

 

Oracle is a registered trademarkof Oracle Corporationand/or its

affiliates. Other names may be trademarksof their respective

owners.

 

Type'help;'or'\h'for help. Type'\c'to clearthecurrent input statement.

 

mysql> use mysql;

Reading table informationfor completionoftableand column names

You can turn off this featureto get a quicker startupwith-A

 

Database changed

mysql> delete from user where USER="";

Query OK,0 rows affected(0.00 sec)

 

mysql> UPDATE user SET Password=PASSWORD('123456') where USER='root';

Query OK,4 rows affected(0.00 sec)

Rows matched:4 Changed:4 Warnings:0

 

mysql>FLUSH PRIVILEGES;

Query OK,0 rows affected(0.00 sec)

 

mysql>Quit

Bye

[root@localhost ~]#

 

注意：

--mysql 新设置用户或更改密码后需用flush privileges刷新MySQL的系统权限相关表，否则会出现拒绝访问，还有一种方法，就是重新启动mysql服务器，来使新设置生效。

命令本质上的作用是将当前user和privilige表中的用户信息/权限设置从mysql库(MySQL数据库的内置库)中提取到内存里。MySQL用户数据和权限有修改后，希望在"不重启MySQL服务"的情况下直接生效，那么就需要执行这个命令。通常是在修改ROOT帐号的设置后，怕重启后无法再登搜索录进来，那么直接flush之后就可以看权限设置是否生效。而不必冒太大风险。 

1.1.4   登录mysql 报错4  1130 host is not allowed to connect to this mysql server

当用navicat或是其它工具连接mysql数据库时报错如下：

 1130 host is not allowed to connect to this mysql server 

解决办法如下：

解决方法：(建议采用方法二、四，基于安全考虑)

方法一：授于root用户在任何客户端连接服务器MySQL

案例、使用用户：root 从任何主机连接到mysql服务器的话。

 

GRANT ALL PRIVILEGESON*.*TO'root'@'%'IDENTIFIEDBY'mypassword' WITH GRANTOPTION;

FLUSH PRIVILEGES;

 

方法二：授于root用户在特定客户端连接服务器MySQL

案例、使用用户：root 从192.168.1.101客户端连接到mysql服务器的话。

 

GRANT ALL PRIVILEGESON*.*TO'root'@'192.168.1.101'IDENTIFIEDBY'mypassword' WITH GRANTOPTION;

FLUSH PRIVILEGES;

 

 方法三：授于特定自定义用户在任何客户端连接服务器MySQL

案例、使用用户：user01 密码：mypassword从任何主机连接到mysql服务器的话。

 

GRANT ALL PRIVILEGESON*.*TO'user01'@'%' IDENTIFIED BY'mypassword'WITH GRANTOPTION;

FLUSH PRIVILEGES;

 

方法四：授于特定自定义用户在特定客户端连接服务器MySQL

 

案例、允许用户user01 从ip为192.168.1.101的客户机连接到mysql服务器，并使用mypassword作为密码

 

GRANT ALL PRIVILEGESON*.*TO'user01'@'192.168.1.101' IDENTIFIED BY'mypassword'WITH GRANTOPTION;

FLUSH PRIVILEGES;

 

注意：

Grant授权命令说明

grant all privileges on数据库名.表名 to 用户名@用户地址 identified by '连接口令';

例如：

--授于 user01用户,在用客户端 192.168.1.101 连接

grant allprivilegeson*.*to'user01'@'192.168.1.101' identifiedby'123456'WITH GRANTOPTION;

FLUSH PRIVILEGES;

 

--授于 root用户在任何客户端连接

grant all privilegeson*.*to'root'@'%' WITH  GRANT  OPTION;

FLUSH PRIVILEGES;

 

方法五： 修改表(不建议这样做，不安全)

可能是你的帐号不允许从远程登陆，只能在localhost。这个时候只要在localhost的那台电脑，登入mysql后，更改 "mysql" 数据库里的 "user"表里的 "host"项，从"localhost"改称"%"

 

[root@localhost ~]#mysql–u root–p

mysql> select host, user from user;

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

| host                 | user |

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

| 127.0.0.1            | root |

| localhost            | root |

| localhost.localdomain | root |

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

3 rows in set (0.00 sec)

 

mysql>use mysql;

mysql>update user set host = '%' where user = 'root';

 

mysql> select host, user from user;

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

| host                 | user |

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

| %                    | root |

| %                    | root |

| %                    | root |

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

3 rows in set (0.00 sec)

mysql>FLUSH PRIVILEGES;