MySQL的安全管理

MySQL如何進行用戶管理

用戶管理

• 創(chuàng)建用戶

create user 'local'@'localhost' identified by 'password'
# 本機登錄用戶
create user 'local'@'192.168.0.1' identified by 'password'
# 遠程登錄用戶
create user 'local'@'192.168.0.%' identified by 'password'
# 網(wǎng)段用戶授權

local 用戶名
localhost 只允許在本機登錄（MySQL安裝的機器）
password 密碼

• 查詢用戶

select user,host from mysql.user;
select * from mysql.user\G;

\G 表示將查詢結果進行按列打印苹粟，可以使每個字段打印到單獨的行有滑。即將查到的結構旋轉90度變成縱向。

• 刪除用戶

drop user 'local'@'192.168.0.1';

控制MySQL用戶權限

• 權限分類

1. 全局權限
2. 數(shù)據(jù)庫權限
3. 表和列權限

• 常用權限

show privileges;
# 顯示MySQL支持的所有權限

• 用戶授權

grant insert,delete,select,update on database.* to 'local'@'localhost';
# 給 'local'@'localhost' 用戶賦予 database 數(shù)據(jù)庫下所有表增刪改查權限

grant insert,delete,select,update on database.table to 'local'@'localhost';
# 給用戶賦予數(shù)據(jù)庫下某個表的增刪改查權限

grant select(id,name) on database.table to local@localhost;
# 給用戶 local@localhost 賦予 database.table 的查詢id吼驶，name字段的權限

• 查詢權限

show grants for 'local'@'localhost';

• 權限回收

revoke select,insert,delete on database.* from 'local'@'localhost';

• 權限授予的原則

使用不同的用戶
權限最小原則
避免使用root

MySQL角色的管理

• 創(chuàng)建角色

create role 'java_developer'

• 給角色賦權

grant select,update,delete,insert on database.* to 'java_developer';

• 使用角色

create user 'test'@'localhost' indentified by '123456';
grant 'java_developer' to 'test'@'localhost';
# 給用戶分配角色
show grants for 'test'@'localhost';

• 撤銷角色

revoke java_developer from 'test'@'localhost';

• 刪除角色

drop role java_developer;

MySQL密碼的管理

• 修改root密碼

alter user user() identified by 'password'

• 修改普通用戶的密碼

alter user 'local'@'localhost' identified by 'password'

• 設置隨機密碼

create user 'local'@'localhost' identified by random password;
# 新建用戶隨機密碼
alter user 'local'@'localhost' identified by random password;
# 修改已經(jīng)存在的用戶隨機密碼

• 強制使用強密碼

install component 'file://component_validate_password';
show global variables like 'validate_password%';
# 顯示密碼規(guī)則

• 密碼試錯

create user 'test'@'localhost' identified by '123456' failed_login_attempts 4 password_lock_time 3;
# 連續(xù)錯誤輸入4次就會鎖定3天
grant select on *.* to 'test'@'localhost';
# 賦權，登錄測試

如何限制用戶使用資源

• 用戶資源限制的參數(shù)

• 資源限制的使用

創(chuàng)建用戶時限制資源

create user 'test'@'localhost' identified by '123456'  with max_queries_per_hour 500  max_updates_per_hour 100  max_connections_per_hour 50  max_user_connections 50;

• 修改現(xiàn)有用戶的資源限制

alter user 'test'@'localhost' with max_queries_per_hour 100;  
alter user 'test'@'localhost' with max_queries_per_hour 0;  
# 取消限制 設為 0

• 重置資源使用計數(shù)

flush user_resources;
# 或者
flush privileges;
# 兩種店煞，或者重新設置資源次數(shù)也會重置

忘記root密碼怎么辦蟹演？

1. 配置文件增加跳過權限驗證的配置

vim /data/mysql/conf/my.cnf
# 在[mysqld]下增加
skip-grant-tables

2. 重啟mysql
3. 免密方式登錄mysql

mysql -uroot -p

4. 修改root密碼

update mysql.user set authentication_string='' where user = 'root' and host = 'localhost';
# 再去掉免密參數(shù)
vim /data/mysql/conf/my.cnf
# 刪除 skip-grant-tables

5. 重啟MySQL
6. 修改密碼并重新登錄

# 空密碼登錄MySQL
mysql -uroot -p
# 修改密碼
alter user user() identified by '123456';

SQL MODE 詳解

• 查詢SQL_MODE

select @@global.sql_mode;

# 查詢當前會話的SQL_MODE
select @@session.sql_mode;

• 設置SQL_MODE

# 啟動時設置
mysql --sql-mode=""

# 配置文件中增加
vim /data/mysql/conf/my.cnf
# 在[mysqld]下新增，多個,隔開
sql-mode=""

# 設置全局
set global sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";

# 設置會話級別
set session sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";
# 原有基礎上刪除某一個sql_mode
set session sql_mode=sys.list_drop(@@session.sql_mode,'NO_ENGINE_SUBSTITUTION')顷蟀；
# 原有基礎上添加一個sql_mode
set session sql_mode=sys.list_add(@@session.sql_mode,'NO_ENGINE_SUBSTITUTION')酒请；

• 常見SQL MODE解釋

特定場景下使用MySQL數(shù)據(jù)庫對象

視圖創(chuàng)建與應用

• 作用

簡化查詢
保護數(shù)據(jù)
重用查詢

• 創(chuàng)建視圖

# 舉例
# 經(jīng)常使用的SQL
select * from student_info where score >= 90;

# 創(chuàng)建視圖
create view view_test as select * from student_info where score >= 90;
# 查詢視圖,相當于執(zhí)行SQL
select * from view_test;

# 查看視圖結構
show create view view_test;
# 刪除視圖
drop view view_test;

存儲過程

存儲過程的使用方法

• 創(chuàng)建存儲過程

# 設置SQL語句以 ;; 結束 
delimiter ;;
# database 創(chuàng)建存儲過程
create procedure user_count_proc(out s int)
begin
select count(*) into s from mysql.user;
end;;
# 改回 ; 結束
delimiter ;

• 使用存儲過程

# 把存儲過程的結果賦予 變量s
call user_count_proc(@s);
# 查詢變量s
select @s;

• 查詢存儲過程定義

show create procedure user_count_proc \G;

• 刪除存儲過程

drop procedure user_count_proc;

• 案例

# 創(chuàng)建測試數(shù)據(jù)
...
# 創(chuàng)建存儲過程
delimiter ;;
# database 創(chuàng)建存儲過程
create procedure insert_test()
begin
declare i int;
set i=1;
# 循環(huán)插入
while(i<=10000)do
insert into table()a,b) values('xxx',i);
set i = i+1;
end while;
end;;

delimiter ;

# 運行
call insert_test()

• 存儲過程的缺點

1. 不方便維護
2. 存儲開銷大
3. 可移植性差

觸發(fā)器的創(chuàng)建與使用

• 創(chuàng)建觸發(fā)器

create trigger insert_class_info after insert on class_info_01 for each row insert into class_info_02(id,name) values (new.id,new.name);
# class_info_01 表有insert操作就會觸發(fā)往 class_info_02 表里面寫入 class_info_01 表里面新增的記錄

# 測試

• 觸發(fā)器的應用場景

1. 數(shù)據(jù)復制和同步
2. 審計
3. 數(shù)據(jù)備份

MySQL版本特性

• MySQL5.5

• 引入的新功能

1. 半同步復制
2. 實時線程
3. Buffer Pool可拆分為多個Instances
4. 元數(shù)據(jù)鎖
5. 默認存儲引擎改成InnoDb
6. 安全性改進

• 缺點

1. 不支持全文搜索
2. 不支持并行復制
3. 并發(fā)能力差

• MySQL5.6

• 引入的新功能

1. 支持GTID復制
2. 密碼策略調整
3. 元數(shù)據(jù)鎖的優(yōu)化
4. 引入了基于庫的并行復制
5. 引入了ICP,MRR等特性
6. Undo Log可保存在獨立表空間中

• 缺點

1. 基于庫的并行復制在大多數(shù)場景都不適用
2. 很多全局參數(shù)不能動態(tài)修改
3. 不支持JSON數(shù)據(jù)類型

• MySQL5.7

• 引入的新功能

1. 組復制
2. 基于WRITESET的并行復制
3. 虛擬列
4. 原生支持JSON類型
5. 支持動態(tài)調整很多全局參數(shù)
6. 支持臨時表設置為InnoDB存儲引擎

• 缺點

1. 社區(qū)版無額外端口
2. 大表增加字段開銷大
3. 所有線程優(yōu)先級都一樣，并且都是共享的

• MySQL8.0

• 引入的新功能

1. 事務性數(shù)據(jù)字典
2. 快速加列
3. 原子DDL
4. 不可見索引
5. 降序索引
6. 角色管理
7. 資源組
8. 持久化全局變量
9. Hash Join
10. 額外端口
11. 自增主鍵的持久化

• 數(shù)據(jù)字典的好處等曼？

1. 保證數(shù)據(jù)的完整性和一致性
2. 支持事務的回滾和恢復
3. 提高系統(tǒng)的性能和擴展性

DBA必備選擇存儲引擎的技能

• 存儲引擎常見操作

查看MySQL支持哪些存儲引擎

show engines\G;

設置存儲引擎

use database;
# 創(chuàng)建時
create table user(id int) engine=InnoDB;
# 修改當前會話的默認存儲引擎
set default_storage_engine=Memory;

修改存儲引擎

alter table user engine=InnoDB;

• 常用的存儲引擎

1. InnoDB
2. MyISAM
3. TokuDB
4. Columnstore
5. MEMORY
6. CSV

• InnoDB的優(yōu)點

1. 支持事務
2. 行級鎖
3. 并發(fā)控制
4. 高新能
5. 支持熱備份

• InnoDB的適用場景

1. 高可靠性應用程序
2. 對并發(fā)要求高的場景
3. 數(shù)據(jù)一致性要求高的場景

• MyISAM的缺點

1. 并發(fā)能力差
2. 不支持事務
3. 備份困難
4. 緩存池只緩存索引里烦，不緩存數(shù)據(jù)
5. 不支持外鍵約束

• TokuDB的特點

1. 高壓縮比
2. 快速寫入和刪除
3. 在線索引創(chuàng)建，字段增加刪除和重名名
4. 在線備份
5. 快速恢復

• TokuDB適用場景

1. 高并發(fā)寫入的場景
2. 數(shù)據(jù)量大的場景
3. 要求高可靠性的場景

• Columnstore的特點

1. 列式存儲
2. 壓縮
3. 分布式架構
4. 高性能查詢
5. 實時數(shù)據(jù)加載

• Columnstore的適用場景

1. 特別適用大數(shù)據(jù)的場景
2. 高并發(fā)OLTP的應用場景
3. 實時分析場景

• MEMORY的特點

1. 讀寫快
2. 表鎖
3. 不支持事務
4. 容易丟失數(shù)據(jù)
5. 主從架構中的問題

• MEMORY存儲引擎的應用場景

1. 臨時表
2. 緩存表
3. 計數(shù)器和排行榜

• CSV的特點

1. 數(shù)據(jù)文件CSV格式禁谦，可直接查看數(shù)據(jù)
2. CSV存儲引擎不支持索引
3. CSV存儲引擎不支持分區(qū)

• CSV存儲引擎的適用場景

1. 臨時性數(shù)據(jù)存儲
2. 數(shù)據(jù)導出
3. 備份和恢復

MySQL必背知識點