mysql除了root都不能登录_mysql 用户除了root一般不建议本地登录

1. 创建数据库:

mysql> create database zjzc DEFAULT CHARACTER SET utf8;

Query OK, 1 row affected (0.14 sec)

2.导入数据

v-lhb-db03:/root/20150512/zjzc# mysql -uroot -p1234567 zjzc

Warning: Using a password on the command line interface can be insecure.

3.创建用户

GRANT USAGE ON *.* TO 'zjzc_app'@'%' IDENTIFIED BY '1234567';

mysql> GRANT ALL PRIVILEGES ON zjzc.* TO 'zjzc_app'@'%';

Query OK, 0 rows affected (0.02 sec)

mysql> select Host,user from mysql.user;

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

| Host | user |

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

| % | test |

| % | zjzc_app |

| 127.0.0.1 | root |

| ::1 | root |

| localhost | |

| localhost | root |

| v-lhb-db03 | |

| v-lhb-db03 | root |

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

8 rows in set (0.00 sec)

mysql> show grants for 'zjzc_app'@'%';

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

| Grants for zjzc_app@% |

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

| GRANT USAGE ON *.* TO 'zjzc_app'@'%' IDENTIFIED BY PASSWORD '*6A7A490FB9DC8C33C2B025A91737077A7E9CC5E5' |

| GRANT ALL PRIVILEGES ON `zjzc`.* TO 'zjzc_app'@'%' |

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

2 rows in set (0.00 sec)

4.远程登录和本地登录:

v-lhb-db03:/root/20150512/zjzc# mysql -uzjzc_app -p1234567

Warning: Using a password on the command line interface can be insecure.

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

1.远程登录:

从192.168.32.14 登录192.168.32.119

v-dev-db01:/root# mysql -uzjzc_app -h192.168.32.119 -p1234567

Warning: Using a password on the command line interface can be insecure.

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

Your MySQL connection id is 52

Server version: 5.6.22-log Source distribution

Copyright (c) 2000, 2014, 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> show databases;

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

| Database |

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

| information_schema |

| test |

| zjzc |

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

3 rows in set (0.00 sec)

在119 本机上:

v-lhb-db03:/root/20150512/zjzc# /sbin/ifconfig -a

eth0 Link encap:Ethernet HWaddr 00:0C:29:FD:D7:EC

inet addr:192.168.32.119 Bcast:192.168.32.255 Mask:255.255.255.0

1.v-lhb-db03:/root/20150512/zjzc# mysql -uzjzc_app -h192.168.32.119 -p1234567 可以登录

2.v-lhb-db03:/root/20150512/zjzc# mysql -uzjzc_app -hlocalhost --可以登录

3.v-lhb-db03:/root/20150512/zjzc# mysql -uzjzc_app -p1234567

Warning: Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'zjzc_app'@'localhost' (using password: YES) --不能登录

--给本地授权

GRANT USAGE ON *.* TO 'zjzc_app'@'localhost';

mysql> show grants for 'zjzc_app'@'%';

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

| Grants for zjzc_app@% |

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

| GRANT USAGE ON *.* TO 'zjzc_app'@'%' IDENTIFIED BY PASSWORD '*6A7A490FB9DC8C33C2B025A91737077A7E9CC5E5' |

| GRANT ALL PRIVILEGES ON `zjzc`.* TO 'zjzc_app'@'%' |

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

2 rows in set (0.00 sec)

mysql> show grants for 'zjzc_app'@'localhost';

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

| Grants for zjzc_app@localhost |

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

| GRANT USAGE ON *.* TO 'zjzc_app'@'localhost' |

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

1 row in set (0.00 sec)

v-lhb-db03:/root/20150512/zjzc# mysql -uzjzc_app -p1234567

Warning: Using a password on the command line interface can be insecure.

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

还是不行:

GRANT USAGE ON *.* TO 'zjzc_app'@'localhost' IDENTIFIED BY '1234567';

mysql> show grants for 'zjzc_app'@'localhost';

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

| Grants for zjzc_app@localhost |

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

| GRANT USAGE ON *.* TO 'zjzc_app'@'localhost' IDENTIFIED BY PASSWORD '*6A7A490FB9DC8C33C2B025A91737077A7E9CC5E5' |

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

1 row in set (0.00 sec)