[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;