云计算 centos Android Python 微软 Windows 编程 mysql shell linux 开源 nginx Ubuntu wordpress 程序员 java apache Firefox php google

Linux運維 第四階段 (五)MySQL用戶和權限管理、日誌管理

一、用戶和權限管理:

1、相關概念:

mysql用戶:

類似VSFTPD虛擬用戶;

密碼,自己獨有的加密機制,PASSWORD函數;

用戶名@主機;

用戶名16字符以內,主機(主機名、ip、網絡地址、通配符(%,_);

--skip-name-resolve(跳過名稱解析,可提高用戶連接速度)

賬號是用來認證的;

>rename USER  ‘old_name’@’host’  TO  ‘new_name’@’host;  (重命名用戶)

 

》權限,授權後才能訪問表(數據);

mysql自身庫中的表:user,db,host,tables_priv,columns_priv,procs_priv:

user:user account,globalprivileges and other non-privileges column; 全局級別(SUPER,REPLICATION SLAVE,shutdown)

db:database-levelprivileges 庫級權限

host:obsolete  已廢棄

tables-priv:table-levelprivileges  表級權限(DELETE,ALTER,TRIGGER,SELECT,INSERT,UPDATE)

columns_priv:column-levelprivileges  列級別權限(SELECT,INSERT,UPDATE)

procs_priv:stored procedure andfunction privileges  存儲過程和存儲函數權限

proxies_priv:proxy-userprivileges  代理用戶權限

 

mysql在啟動時會讀取這六張表,並在內存中生成授權表,且每執行一個SQL語句都要檢查是否有權限讀取或寫入;

 

2、>SELECT  * FROM  db\G

>CREATE TEMPORARY  TABLE  tb_name; (臨時表——內存表);

USAGE (僅用作連接mysql-server和一些簡單命令的使用)

觸發器trigger:主動數據庫(在執行某些操作時,觸發已定義好的語句,如日誌記錄)

 

>help CREATE  USER  (創建用戶)

>CREATE USER  ‘username’@’host’  [IDENTIFIED BY  ‘new_pw’];

>FLUSH PRIVILEGES;

 

>HELP GRANT  (給用戶授權)

>GRANT privileges1,privileges2 ON [object_type]db_name.tb_name TO ‘username’@’host’ [IDENTIFIED BY ‘new_pw’]  [REQUIRE NONE|ssl_option]  [WITH with_option];

 

註:db_name.tb_name(priv_level):

*

*.*

db_name.*

db_name.tb_name

tb_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  EXECUTE  ON FUNCTION  db.abc  TO username@’%’;  (當無法判斷tb_name是什麽對象時使用此項,此例明確說明abc是函數,tb_name不一定都是表,也會是存儲過程PROCEDURE和存儲函數FUNCTION)

>GRANT UPDATE  ON  jiaowu.tutors TO  ‘username’@’%’  REQUIRE SSL;  (連接時必須要以SSL建立安全連接,還可以以X509方式)

>GRANT UPDATE(Age) ON jiaowu,stu TO ‘username’@’host’;  (僅授權某字段)

>GRANT SUPER ON *.* TO ‘username’@’host’;  (SUPER與WITH GRANTOPTION兩項要慎重授權)

 

>SHOW GRANTS FOR ‘username’@’host’;  (查看某用戶權限,字段授權與庫、表授權不在同一行)

 

>HELP REVOKE

>REVOKE priv1,priv2  ON  db_name.tb_name  FROM  ‘user’@’host’;

 

3、重新找回管理員密碼:

#service mysqld  stop

#vim /etc/rc.d/init.d/mysqld

$bindir/mysqld_safe  --skip-grant-tables  --skip-networking   --datadir="$datadir"--pid-file="$mysqld_pid……(添加--skip-grant-tables跳過授權表和--skip-networking禁止連接網絡)

#service mysqld  start

#mysql

>UPDATE mysql.user  set  PASSWORD=PASSWORD(‘new_password’)  WHERE User=’root’;

(僅能通過手動更改user表中內容,不能用SET及mysqladmin更改密碼,因為授權表已跳過)

#service mysqld  stop(停止服務將/etc/rc.d/init.d/mysqld改回原來狀態,重啟服務即可)。

 

 

二、日誌管理

1、錯誤日誌:

服務器啟動和關閉過程中的信息;

服務器運行過程中的錯誤信息;

事件調度器運行一個事件時產生的信息;

在從服務器上啟動從服務器進程時產生的信息;

local.localdomain.err  默認在數據目錄中,主機名.err

log_warnings 默認記錄(0不記錄|1記錄)

log_err

 

2、一般查詢日誌:默認關閉,若開啟有關數據庫所有操作都記錄,浪費磁盤空間,關鍵消耗系統資源(大量磁盤IO,任何產生IO的地方就是性能降低的地方)

general_log

general_log_file

localhost.log

log_output FILE|TABLE|NONE  (可保存至表中,註意若打開了general_log但log_output為NONE,則不會記錄任何信息)

 

3、慢查詢日誌:生產環境下分析用,提高性能的指標

long_query_time 默認為10S

log_slow_queries等同於slow_query_log

slow_query_log_file  或更改位置要重啟服務

log_output FILE|TABLE|NONE

 

>SET GLOBAL  slow_query_log=1  動態調整,永久生效要寫入配置文件

 

4、中繼日誌:在slave-server上要執行的日誌

relay-log

FIO(Fusion-IO)

IOPS:每秒執行的IO數,一般磁盤100,好的SCSI 200,SSD1000-2000

 

5、二進制日誌:任何引起或可能引起數據庫變化的操作,可用於復制及即時點恢復

二進制日誌滾動:達到設定的上限值;>FLUSH LOGS;每重啟服務;

二進制日誌最好單獨放在另一做了LVM的磁盤上;

格式:statement;row;mixed

事件:產生的時間(starttime);相對位置(position)

文件:索引文件mysql-bin.index、二進制日誌文件如mysql-bin.000001

命令:

#mysqlbinlog mysql-bin.000001(查看二進制日誌文件內容,不給選項則是文件全部內容)

--start-datetime  (格式:’yyyy-mm-dd  hh:mm:ss

--stop-datetime (不指stop則是到文件尾部)

--start-position

--stop-position

>SHOW MASTER  STATUS;  (查看當前正在使用的二進制日誌)

>SHOW BINARY  LOGS;  (查看服務器二進制日誌)

>SHOW BINLOG  EVENTS  IN  ‘mysql-bin.000001’[FROM  107];  (在mysql命令行模式下查看二進制日誌內容)

>PURGE BINARY  LOGS  TO  ‘mysql-bin.000005’;  (刪除5之前的二進制日誌文件,不包括5,不能用#rm -rf直接刪除二進制日誌,否則mysql會崩潰)

>FLUSH LOGS;  (僅滾動二進制日誌,錯誤日誌是關閉後再打開,在slave-server上是滾動中繼日誌)

 

binlog_cache_size

binlog_stmt_cache-size  調高性能可提高,但潛在的丟失的數據量也大,不建議調大

log_bin  是否啟用二進制日誌

sql_log_bin 用在即時點還原,還原前臨時關閉寫入二進制日誌,恢復好後再開啟

sync_binlog 設置多少次操作同步到磁盤,0不同步,此項與autocommit有關

max_binlog_cache_size  控制上限

max_binlog_size

 

6、事務日誌:

事務性存儲引擎來保證ACID(atomicity,consistency,isolation,durability)

事務日誌可用來rollback回滾,而二進制日誌是重放redo

二進制日誌是用來還原數據庫的,而事務日誌是用來保證事務本身的可靠性

innodb_flush_log_at_trx_commit  (

0,每秒同步並執行磁盤flush操作;

1,每事務同步,並執行磁盤flush操作;

2,每事務同步,但不執行磁盤flush操作,由OS決定什麽時候flush,常用此項)

innodb_log_buffer_size  內存緩沖大小,默認8M

innodb_log_file_size  日誌文件大小,默認5M

innodb_log_files_in_group  默認2(高級用法:可將做成兩組鏡像)

innodb_log_group_home_dir

innodb_mirrored_log_groups

 

>SHOW ENGINES;

MRG_MyISAM

ARCHIVE 歸檔,將表歸檔壓縮存放

CSV  利用文本文件存儲表(方便移植)

MEMORY 

BLACKHOLE

註:不建議使用混合引擎,要麽使用MyISAM,要麽使用InnoDB。

MyISAM:

不支持事務;

表鎖,鎖力度大,讀多性能還行,讀寫一樣多時性能會下降;

不支持外鍵;

BTREE索引,FULLTEXT,支持空間索引。

InnoDB:

OLTP在線事務處理;

行級鎖,鎖力度精細;

BTREE索引,聚簇索引,自適應hash索引;

表空間,raw磁盤設備(裸設備)

延伸阅读

评论