登录
授权(创建)用户
回收权限
删除用户
修改密码
登录
发现远程访问时无法登录mysql
[root@VM_0_15_centos ~]# mysql -h106.54.3.104 -uroot -p'qingdao@123'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1130 (HY000): Host '118.24.193.33' is not allowed to connect to this MySQL server
登录参数
mysql -h192.168.5.240 -P 3306 -u root -p'123' mysql -e 'show table'
参数
描述
默认
-h192.168.5.240
指定主机名
默认localhost
-P 3306
指定端口
默认3306
-u root
用户
默认root
-p'123'
密码
默认空密码
mysql
进入的数据库
/
-e 'show table'
指定的sql命令
/
授权(创建)用户
mysql库下面存放授权信息
user表存放----全局权限
db表存放----库级别权限
tables_priv表存放----表级别授权
columns_priv表存放----列级别授权
user表字段
描述
Host
主机
User
用户
max_questions
最大请求数
max_updates
最大更新数
max_connections
最大连接数
max_user_connections
最大用户连接数
authentication_string
密码
password_expired
密码过期情况
password_last_changed
密码上次更新
password_lifetime
密码生存周期
account_locked
账户锁
给某个用户授权时,如果该用户不存在,则会自动创建该用户.要指定密码
mysql> grant all on *.* to 'yang1'@'118.24.193.33' IDENTIFIED BY 'mypass.123A';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all on company.* to 'zhuzhu1'@'118.24.193.33' IDENTIFIED BY 'mypass.123B';
Query OK, 0 rows affected, 1 warning (0.00 sec)
那么上面创建用户的命令都是在会把创建痕迹放在哪里呢?
mysql> select * from mysql.user\G
发现yang1用户的权限是全局级别权限可以在mysql.user表中查看到信息
mysql> select * from mysql.db\G
发现zhuzhu1用户的权限是库级别权限可以在mysql.db表中查看到信息
表中Grant_priv: N字段不允许将该权限授权给其他用户
现在通过刚刚建立的两张表以及可以在另一台服务器上访问数据库了
设置的权限不同,能操作的范围也不同
[root@VM_0_15_centos ~]# mysql -h106.54.3.104 -uyang1 -p'mysql.123A'
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| company |
| mysql |
| performance_schema |
| school |
| sumyum |
| sys |
| test1 |
+--------------------+
8 rows in set (0.03 sec)
mysql>
[root@VM_0_15_centos ~]# mysql -h106.54.3.104 -uzhuzhu1 -p'mypass.123B'
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| company |
+--------------------+
2 rows in set (0.04 sec)
mysql>
权限管理
grant设置权限
grant 权限列表 on 库名.表名 to '用户名'@'客户端主机' [identified by'密码' with option参数]
权限列表
描述
all
所有权限 (不包括授权权限)
select,update
指定权限
表名库名选项
描述
* . *
所有库下的所有表
web.*
web库下的所有表
web.stu_info
web库下的stu_info表
select(col1),insert(col1,col2) ON web.stu_info
对列赋予不同的权限
客户端主机
描述
%
所有主机
192.168.1.%
指定网段
192.168.1.22
指定主机
localhost
指定主机
对列赋予不同的权限
mysql> grant select(dept_name,dept_comment),insert(dept_name) on company.department1 to 'admin'@'%' identified by 'qwQW123@#!!';
Query OK, 0 rows affected, 1 warning (0.03 sec)
[root@VM_0_15_centos ~]# mysql -h106.54.3.104 -uadmin -p'qwQW123@#!!'
mysql> use company;
mysql> insert into department1(dept_name, dept_comment) values('tomp','tompfdfreger');
ERROR 1143 (42000): INSERT command denied to user 'admin'@'118.24.193.33' for column 'dept_comment' in table 'department1'
mysql> insert into department1(dept_name) values('tomp');
Query OK, 1 row affected (0.07 sec)
查看权限show grants for
查看当前权限
mysql> show grants\G
Grants for admin@%: GRANT SELECT (dept_comment, dept_name), INSERT (dept_name) ON `company`.`department1` TO 'admin'@'%'
2 rows in set (0.03 sec)
查看指定用户权限
mysql> show grants for 'admin'@'%'\G
Grants for admin@%: GRANT SELECT (dept_comment, dept_name), INSERT (dept_name) ON `company`.`department1` TO 'admin'@'%'
2 rows in set (0.03 sec)
回收权限
回收某一个用户的权限时,可以先查看权限,然后复制,将to改为from
回收全部权限
回收admin
mysql> revoke SELECT (dept_comment, dept_name), INSERT (dept_name) ON `company`.`department1` from 'admin'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'admin'@'%'\G
*************************** 1. row ***************************
Grants for admin@%: GRANT USAGE ON *.* TO 'admin'@'%'
1 row in set (0.00 sec)
回收yang1
mysql> show grants for 'yang1'@'118.24.193.33'\G
*************************** 1. row ***************************
Grants for yang1@118.24.193.33: GRANT ALL PRIVILEGES ON *.* TO 'yang1'@'118.24.193.33'
1 row in set (0.00 sec)
mysql> revoke ALL PRIVILEGES ON *.* from 'yang1'@'118.24.193.33';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'yang1'@'118.24.193.33'\G
*************************** 1. row ***************************
Grants for yang1@118.24.193.33: GRANT USAGE ON *.* TO 'yang1'@'118.24.193.33'
1 row in set (0.00 sec)
回收部分权限
mysql> grant all on school.* to 'admin2'@'%' identified by'1q2w3eQWE!';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show grants for 'admin2'@'%'\G
*************************** 1. row ***************************
Grants for admin2@%: GRANT USAGE ON *.* TO 'admin2'@'%'
*************************** 2. row ***************************
Grants for admin2@%: GRANT ALL PRIVILEGES ON `school`.* TO 'admin2'@'%'
2 rows in set (0.00 sec)
mysql> revoke insert ON `school`.* from 'admin2'@'%';
Query OK, 0 rows affected (0.00 sec)
删除用户drop user
5.6版本删除用户前需要先拿掉权限
如果直接删除用户,权限还会存在
会造成如果下次创建同名用户,会继续使用删除之前的权限
5.7则不会
drop user
mysql> show grants for 'admin2'@'%'\G
*************************** 1. row ***************************
Grants for admin2@%: GRANT USAGE ON *.* TO 'admin2'@'%'
*************************** 2. row ***************************
Grants for admin2@%: GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `school`.* TO 'admin2'@'%'
2 rows in set (0.00 sec)
mysql> drop user 'admin2'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'admin2'@'%'\G
ERROR 1141 (42000): There is no such grant defined for user 'admin2' on host '%'
删除用户
DELETE语句
修改用户密码
root用户修改root用户密码
方法1
[root@VM_0_7_centos ~]# mysqladmin -uroot -p'qingdao@123' password 'qingdao@123NEW'
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
方法二
mysql> update mysql.user set authentication_string=password('qingdao@123NEW2') where user='root' and host='localhost';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
使用这种方法需要刷新授权
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
方法三
mysql> set password=password('qingdao@123NEW3');
Query OK, 0 rows affected, 1 warning (0.00 sec)
root用户修改普通用户密码
方法一
mysql> set password for 'yang1'@'118.24.193.33'=password('yang1!@#NEW1');
Query OK, 0 rows affected, 1 warning (0.00 sec)
方法二
参考上面方法二
普通用户修改普通用户密码
set命令修改当前用户密码
忘记root密码
5.7.6之后的版本
在/etc/my.cnf配置文件中添加字段
[root@VM_0_7_centos ~]# tail -n 1 /etc/my.cnf
skip-grant-tables
重启服务
[root@VM_0_7_centos ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
现在可以无密码登录
[root@VM_0_7_centos ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.27 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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>
查看密码表
mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | *A32A774CFD115F70CC83AB40D7A4F9E4AA9E8DD3 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
重置密码
mysql> update mysql.user set authentication_string=password('qingdao@123') where user='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
退出mysql
mysql> \q
Bye
注释skip-grant-tables字段
[root@VM_0_7_centos ~]# tail -n 1 /etc/my.cnf
# skip-grant-tables
重启mysql服务
[root@VM_0_7_centos ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
登录mysql
[root@VM_0_7_centos ~]# mysql -uroot -p'qingdao@123'
mysql: [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 2
Server version: 5.7.27 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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>
5.7.6之前的怎么操作呢
只有两个区别
5.7.6之前的可以设置简单密码
5.7.6之前的版本需要将authentication_string字段改为password字段
330
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
