MySQL开启远程无密码登录_mysql用户修改登录密码及开启远程登录

标签：

一、修改用户登录密码：

mysql> show databases;

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

mysql> quit

Bye

[root@rhel204 ~]# mysqladmin -uroot -p password --修改用户密码

Enter password:

New password:

Confirm new password:

[root@rhel204 ~]# mysql -uroot -p --以新密码登录

Enter password:

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

……

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

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

4 rows in set (0.10 sec)

二、开启用户远程登录权限

--加-h参数远程登录mysql数据库提示如下错误

C:\Users\Administrator>mysql -uroot -p -h192.168.1.204

Enter password: *****

ERROR 1130 (HY000): Host ‘192.168.1.123‘ is not allowed to connect to this MySQL server

错误分析：主机‘192.168.1.123‘不允许连接到mysql数据库(没权限)。

[root@rhel204 ~]# myslq -uroot -p --本地登录

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 user,password,host from user; --查看用户信息，的确root账号只允许本地登录。

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

| user | password | host |

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

| root | *76C1B58DE0F08B3169E76CEDD6DD814B32A36F78 | localhost |

| root | *76C1B58DE0F08B3169E76CEDD6DD814B32A36F78 | rhel204.com |

| root | *76C1B58DE0F08B3169E76CEDD6DD814B32A36F78 | 127.0.0.1 |

| root | *76C1B58DE0F08B3169E76CEDD6DD814B32A36F78 | ::1 |

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

4 rows in set (0.00 sec)

mysql>grant all privileges on *.* to root@‘%‘ identified by ‘rusky‘; --授权

Query OK, 0 rows affected (0.03 sec)

或：grant all on db1.* to username1@‘%‘; --授权用户username1从任一客户端远程登录数据库db1，并允许对库db1做所有操作。

"%"表示任何主机都可以远程登录到该服务器上访问。

*.*表示所有库的所有对象。

如果要限制只有某台机器可以访问，将%换成相应的IP即可，如：

GRANT ALL PRIVILEGES ON *.* TO username2@‘192.168.1.123’; --可省略IDENTIFIED BY ‘密码‘;或with grant option;

mysql> flush privileges; --刷新权限

Query OK, 0 rows affected (0.04 sec)

C:\Users\Administrator>mysql -uroot -p -h192.168.1.204 --root账号远程登录

Enter password: *****

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

……

mysql>

三、创建用户时就限制用户的权限：

create user   ‘lxj‘@‘%‘    identified  by ‘123123‘;

@后面参数指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost, 如果想让该用户可以从任意远程主机登陆,可以使用通配符%.

标签：