3. mysql 用户和用户管理

MySQL用户和权限管理

1. 用户管理

元数据数据库：mysql
系统授权表：
db, host, user
columns_priv, tables_priv, procs_priv, proxies_priv
用户账号：
'USERNAME'@'HOST'
@'HOST':
主机名
IP地址或Network
通配符： % _
示例：172.16.%.%
创建用户：CREATE USER
CREATE USER 'USERNAME'@'HOST' [IDENTIFIED BY 'password']；
默认权限：USAGE
用户重命名：RENAME USER
RENAME USER old_user_name TO new_user_name; 删除用户：
DROP USER 'USERNAME'@'HOST‘
示例：删除默认的空用户
DROP USER ''@'localhost';

修改密码：
mysql>SET PASSWORD FOR 'user'@'host' = PASSWORD(‘password');
mysql>UPDATE mysql.user SET password=PASSWORD('password') （不立即生效）
WHERE clause;
此方法需要执行下面指令才能生效：
mysql> FLUSH PRIVILEGES; # 刷新
 #mysqladmin -u root -poldpass password ‘newpass’

忘记管理员密码的解决办法：
启动mysqld进程时，为其使用如下选项：
--skip-grant-tables
--skip-networking
使用UPDATE命令修改管理员密码
关闭mysqld进程，移除上述两个选项，重启mysqld

MariaDB [mysql]> create user test@'192.168.0.101' identified by 'centos';
Query OK, 0 rows affected (0.01 sec)

MariaDB [mysql]> select user,host,password from user;
+------+---------------+-------------------------------------------+
| user | host          | password                                  |
+------+---------------+-------------------------------------------+
| root | localhost     | *A4B6157319038724E3560894F7F932C8886EBFCF |
| root | centos7       | *A4B6157319038724E3560894F7F932C8886EBFCF |
| root | 127.0.0.1     | *A4B6157319038724E3560894F7F932C8886EBFCF |
| root | ::1           | *A4B6157319038724E3560894F7F932C8886EBFCF |
| test | 192.168.0.101 | *128977E278358FF80A246B5046F51043A2B1FCED |
+------+---------------+-------------------------------------------+

# 连接
mysql -utest -h192.168.0.101 -p
# 删除
MariaDB [mysql]> drop user test@'192.168.0.101'

2. MySQL权限管理

权限类别：

管理类
程序类
数据库级别
表级别
字段级别

管理类：

CREATE TEMPORARY TABLES
CREATE USER
FILE
SUPER
SHOW DATABASES
RELOAD
SHUTDOWN
REPLICATION SLAVE
REPLICATION CLIENT
LOCK TABLES
PROCESS

 程序类： FUNCTION、PROCEDURE、TRIGGER

CREATE
ALTER
DROP
EXCUTE

 库和表级别：DATABASE、TABLE

ALTER
CREATE
CREATE VIEW
DROP
INDEX
SHOW VIEW
GRANT OPTION：能将自己获得的权限转赠给其他用户

数据操作：

SELECT
INSERT
DELETE
UPDATE

字段级别：

SELECT(col1,col2,...)
UPDATE(col1,col2,...)
INSERT(col1,col2,...)

所有权限：

ALL PRIVILEGES 或 ALL

3.授权

GRANT 与 REVOKE

GRANT
 参考：https://dev.mysql.com/doc/refman/5.7/en/grant.html
 GRANT priv_type [(column_list)],... ON [object_type] priv_level TO 'user'@'host'
[IDENTIFIED BY 'password'] [WITH GRANT OPTION];
 priv_type: ALL [PRIVILEGES]
 object_type:TABLE | FUNCTION | PROCEDURE
 priv_level: *(所有库) | *.* | db_name.* | db_name.tbl_name | tbl_name(当前库
的表) | db_name.routine_name(指定库的函数,存储过程,触发器)  with_option: GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
示例：GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO
'someuser'@'somehost‘;

回收授权：REVOKE
REVOKE priv_type [(column_list)] [, priv_type
[(column_list)]] ... ON [object_type] priv_level FROM user [, user] ...
示例：
REVOKE DELETE ON testdb.* FROM 'testuser'@‘172.16.0.%’;
查看指定用户获得的授权：
Help SHOW GRANTS
SHOW GRANTS FOR 'user'@'host';
SHOW GRANTS FOR CURRENT_USER[()];
注意：MariaDB服务进程启动时会读取mysql库中所有授权表至内存
(1) GRANT或REVOKE等执行权限操作会保存于系统表中，MariaDB的服务进
程通常会自动重读授权表，使之生效
(2) 对于不能够或不能及时重读授权表的命令，可手动让MariaDB的服务进程
重读授权表：mysql> FLUSH PRIVILEGES;

MariaDB [(none)]> show grants for test2@'192.168.0.101';
+------------------------------------------------------------------------------------------------------------------+
| Grants for test2@192.168.0.101                                                                                   
| GRANT USAGE ON *.* TO 'test2'@'192.168.0.101' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
| GRANT SELECT (age, stuid, name) ON `hellodb`.`students` TO 'test2'@'192.168.0.101'                               |
+------------------------------------------------------------------------------------------------------------------+

MariaDB [(none)]> grant all on hellodb .* to test@'192.168.0.101' identified by '1234';
MariaDB [(none)]> grant select(stuid,name,age) on hellodb.students to test2@'192.168.0.101' identified by '1234';

# 删除权限
MariaDB [(none)]> revoke delete on hellodb.* from test@'192.168.0.101';

2 Mysql体系架构

2.1 存储引擎

InnoDB support for FULLTEXT indexes is available in MySQL 5.6.4 and later.
存储引擎比较：https://docs.oracle.com/cd/E17952_01/mysql-5.5-en/storage-engines.html

MyISAM引擎特点：

不支持事务
表级锁定
读写相互阻塞，写入不能读，读时不能写
只缓存索引
不支持外键约束
不支持聚簇索引
读取数据较快，占用资源较少
不支持MVCC（多版本并发控制机制）高并发
崩溃恢复性较差
MySQL5.5.5前默认的数据库引擎