Linux運維 第四階段 （五）MySQL使用者和權限管理、日志管理

一、使用者和權限管理：

１、相關概念：

》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磁盤裝置（裸裝置）

本文轉自 chaijowin 51CTO部落格，原文連結：http://blog.51cto.com/jowin/1683615，如需轉載請自行聯系原作者