[Mysql]——用户管理
登录和退出
> mysql
-h 参数后面接hostname或者hostIP
-P 参数后面接Mysql服务的端口号，通过指定的端口号来进行连接
-u 参数后面接username用户名
-p 参数会提示密码
-e 参数可以可以直接加SQL语句，登录MYSQL服务器以后即可执行这个SQL语句，然后退出Mysql服务器
DatabaseName 参数指明登录到哪一个数据库中，如果没有该参数，会直接登录到Mysql数据库中.然后可以使用USE命令来选择数据库> quit;
创建用户
1. create user语句
> create user 'User-name'@'Host-name' (对应User表中的User和Host字段) IDENTIFIED BY 'password' (对应User表中的Password字段) || IDENTIFIED BY 'PASSWORD(password)'
2. insert into mysql.user（直接插入mysql.user表）
> insert into mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject) values('Host-name','User-name',PASSWPRD('password'),'','','');
> flush privileges;
删除用户
Methods-1
> drop user 'username'@'hostname';
Methods-2
> delete from mysql.user where User='username' and Host='hostname';
> flush privileges;
查询所有用户信息
> select User,group_concat(Host) from mysql.user group by User having User!=''; +---------+-----------------------------+ | User | group_concat(Host) | +---------+-----------------------------+ | root | localhost,centos2,127.0.0.1 | | test1 | localhost | | test2 | localhost | | test2_1 | localhost | | zabbix | localhost | +---------+-----------------------------+
> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user; +------------------------------+ | query | +------------------------------+ | User: 'root'@'127.0.0.1'; | | User: ''@'centos2'; | | User: 'root'@'centos2'; | | User: ''@'localhost'; | | User: 'root'@'localhost'; | | User: 'test1'@'localhost'; | | User: 'test2'@'localhost'; | | User: 'test2_1'@'localhost'; | | User: 'zabbix'@'localhost'; | +------------------------------+ 9 rows in set (0.02 sec)
查询某个用户的所有权限
Methods-1
> show grants for 'hostname'@'hostname' \G
Methods-2
> select * from mysql.user where User='username' and Host='hostname' \G
> show grants for 'root'@'localhost'\G *************************** 1. row *************************** Grants for root@localhost: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION 1 row in set (0.00 sec) > show grants for 'test1'@'localhost'\G *************************** 1. row *************************** Grants for test1@localhost: GRANT USAGE ON *.* TO 'test1'@'localhost' IDENTIFIED BY PASSWORD '*AD6EE75212922D967AF18D408481254CC164B39D' 1 row in set (0.00 sec) > select * from mysql.user where User='root' and Host='localhost'\G
修改/设置密码
1. 修改root用户密码
Methods-1
# mysqladmin -u root -p password 'new_password'
Enter password:--->在这里要输入正确的旧密码
Methods-2
> update mysql.user set Password=PASSWORD('new_password') where User='root' and Host='localhost';
> flush privileges;
Methods-3
> set password=PASSWORD('new_password');
2. 修改普通用户密码
Methods-1
> set password for 'username'@'hostname'=PASSWORD('new_password');
Methods-2 创建一个无初始密码的普通用户后可以直接用其登入mysql，登入后可以通过这个方法设置密码：
> set password=PASSWORD('new_password');
Methods-3
> update mysql.user set Password=PASSWORD('new_password') where User='username' and Host='hostname';
> flush privileges;
Methods-4
> grant select on *.* to 'username'@'hostname' identified by 'new_password';
3. 忘记root密码的解决
# /etc/init.d/mysqld start --skip-grant-tables
# mysql -u root -p --->这样可以无密码登陆进去了-> update mysql.user set Password=PASSWORD('new_password') where User='root' and Host='localhost';
> flush privileges;
