一、安裝以及初始化
軟件包來源
1. vendor, rpm
2. 官網，source code 編譯
3. 官網， 二進制包
二進制包安裝過程
1. 下載二進制包，解壓縮到
# tar -xf mariadb-5.5.46-linux-x86_64.tar.gz -C /usr/local/
2. 創建鏈接
# ln -sv mariadb-5.5.46-linux-x86_64.tar.gz mysql
3. 庫導出， 在ld.so.conf.d 下面穿件文件，寫有/usr/local/mysql/lib， 然后重新導入庫
# idconfig -v
4. 二進制文件導出， 在/etc/profile.d/下面創建一個寫有二進制目錄的文件即可
5. 頭文件導出， 把/usr/local/mysql/include/mysql 鏈接至/usr/local/include 下面
初始化數據庫， 運行安裝目錄，/usr/local/mysql/scripts 下的mysql_install_db
1. mysql_install_db: 下面三個選項通常要指定
--basedir : 安裝根目錄 --datadir : 數據庫存放目錄 --user : 服務器進程用戶, 要提前創建用來運行mysqld服務的系統用戶
2. 預備服務腳本： 如果是用二進制文件安裝，在support-files 目錄下可以找到
# cp usr/local/mysql/support_files/mysql.server /etc/rc.d/init.d/mysqld
修改其中一下變量： basedir = datadir =
3. 預備配置文件
# cp /usr/local/mysql/support_files/my_huge.cnf /etc/
4. 配置文件默認讀取順序
/etc/mysql/my.cnf –> /etc/my.cnf –> ~/my.cnf
生效規則：
1） 不同變量結果取所有文件的并集
2） 同一變量去最后一個配置文件的取值生效（~/my.cnf）
啟動服務后刪除匿名用戶
mysql> DROP user ""@"localhost"; mysql> DROP user ""@"127.0.0.1"; mysql> DROP user ""$"::1";
設置管理員賬戶和密碼
第一種方式
mysql> SET PASSWORD FOR "root"@"localhost" = PASSWORD("root")
第二種方式
mysql> USE user;
mysql> UPDATE user SET password = PASSWORD('root') WHERE User = "root";
mysql> FLUSH PRIVILEGES;
第三種， 使用mysqladmin 命令
添加遠程管理賬號；
mysql> GRANT ALL on *.* TO 'root'@'192.168.98.129' IDENTIFIED BY "rootpass";
二、數據庫客戶端連接命令
mysql: 常用選項如下
-u 用戶名 -h 服務器主機名或IP -p 用戶密碼 --port 端口號 --socket 套接字文件所在目錄
常用服務器端命令以及快捷鍵：
CLEAR \c : 取消當前行 HELP \h: 幫助文檔 SYSTEM \!: 執行系統shell命令 QUIT\q : 退出 STATUS\s : 返回狀態 EGO \G : 列顯示結果 SOURCE \. : 讀取sql 腳本 CHARSET \C : 修改字符集
mysql shell 的組合快捷鍵
ctrl + u: 刪除光標之前的所有內容 ctrl + w: 刪除光標之前的一個單詞 ctrl + y: 粘貼使用上面兩個命令刪除的內容 ctrl + a: 跳到行首 ctrl + e: 跳到行尾
遠程控制命令mysqladmin
mysqladmin [OPTIONS] command command
command: create DB_NAME drop DB_NAME debug:打開調試日志并記錄于error log中； status：顯示簡要狀態信息 --sleep #: 間隔時長 --count #: 顯示的批次 extended-status:輸出mysqld的各狀態變量及其值，相當于執行“mysql> SHOW GLOBAL STATUS” variables:輸出mysqld的各服務器變量 flush-hosts:清空主機相關的緩存：DNS解析緩存，此前因為連接錯誤次數過多而被拒絕訪問mysqld的主機列表 flush-logs:日志滾動，二進制日志和中繼日志 refresh:相當于同時使用flush-logs和flush-hosts flush-privileges: ：載入授權 reload:功能同flush-privileges flush-status:重置狀態變量的值 flush-tables:關閉當前打開的表文件句柄 flush-threads：清空線程緩存 kill:殺死指定的線程，可以一次殺死多個線程，以逗號分隔，但不能有多余空格 password:修改當前用戶的密碼； ping processlist：顯示mysql線程列表 shutdown:關閉mysqld進程； start-slave :啟動從服務器 stop-slave: :關閉從服務器 mysql數據類型
三、數據類型相關
數據類型
1. 數值型
TINYINT SMALINT MEDIUMINT INT BIGINT DECIMAL FLOAT DOUBAL BIT
2. 字符型
CHAR VARCHAR TINYTEXT MEDIUMTEXT TEXT LONGTEXT
3. 二進制型
BINARY VARBINARY TINYBLOB BLOB MEDIUMBLOB LONGBLOB ENUM SET
4.日期時間型
DATE TIME DATETIME TIMESTAMP YEAR
數據類型修飾符
1. CHAR, VARCHAR, TEXT 常用修飾
NOT NULL NULL DEFAULT 'strings' CHARACTER SET 'charset' mysql> SHOW VARIABLES LIKE '%CHAR%' ; ## 顯示當前字符集 COLLATION '某排序規則' mysql> SHOW COLLATION ; ## 顯示所有排序規則
2. BINARY, TEXT, BLOB 常用修飾
NOT NULL NULL DEFAULT, 不適用與BLOB
3. 整型類：
AUTO_INCREMENT UNSIGNED NOT NULL NULL DEFAULT
4. 浮點類
UNSIGNED NOT NULL NULL DEFAULT
5.時間日期類
NOT NULL NULL DEFAULT
7. ENUM , SET
NOT NULL NULL DEFAULT
四、mysql內置變量查詢和修改
變量類型：
1. GLOBAL: 全局變量,當前會話中修改以后不生效，只有開啟新會話才生效
2. SESSION: 只在當前會話中生效
無論以上哪種，在重啟服務后都消失，若要永久生效，需要寫入配置文件的服務器段
3. STATUS VARIABLES: 狀態變量，無法修改，但是記錄的服務器各個統計狀態參數
變量查看方式：
mysql> SHOW [SESSION|GLOBAL|STATUS] LIKE "%engine%"; mysql> SELECT @@GLOBAL.variable_name ; mysql> SELECT @@SESSION.variable_name ; mysql> SELECT * FROM INFORMATION_SHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME LIKE "PATTEN" ; mysql> SELECT * FROM INFORMATION_SHEMA.SESSION_VARIABLES WHERE SESSION_VARIABLES LIKE "PATTEN" ;
修改變量：
1. 臨時修改：
mysql> SET [GLOBAL|SESSION] VARIABLE_NAME == "value" GOLBAL: 修改后需要開啟新的會話才能生效 SESSION: 只在當前會話生效
2.永久修改： 編輯配置文件的server段
五、數據庫增刪查改基本操作
數據庫創建刪除修改
mysql> CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name ;
mysql> DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
mysql> ALTER {DATABASE|SCHEMA} db_name [DEFAULT] [CHARACTER SET=''] [DEFAULT] [COLLATE='']
表創建刪除
1. 直接創建表：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [create_specification] create_definition
2.復制已經存在表的表結構：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tb1_name [create_specification] LIKE old_tbl_name
3. 復制表數據：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tb1_name [create_specification] [create_definition] select_statement
create_definition:
1) 字符集，排序規則
CHARACTER SET [=] charset_name | COLLATE [=] collation_name
2) column_definition:
表修改
UPDATE： UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1=val1 [, col_name2=val2] ... [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
UPDATE通常情況下，必須要使用WHERE子句，或者使用LIMIT限制要修改的行數；
– -safe-updates 可以只修改符合條件的第一個
刪除行
DELETE: DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
清空表
TRUNCATE tb_name ： 用于清空表，但是保留表結構
MySQL SQL_MODE: SQL模式
通過修改全局變量修改 TRADITIONAL： STRICT_TRANS_TABLES： 僅對支持事物的表使用嚴格模式 STRICT_ALL_TABLES： 對于所有表使用嚴格模式 嚴格模式： 如果插入的數據超出表定義的范圍，對插入內容進行截短，使其滿足字段定義時的屬性
表屬性修改
ALTER TABLE tb_name
CHANGE [column_name] old_name new_name col_defination [FIRST AFTER column_name]
MODIFY [column_name] col_name column_definition [FIRST AFTER column_name]
ADD [COLUMN] col_name column_defination [FIRST AFTER col_defination]
ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option
當然這個命令其他字命令很多
mysql的基本查詢：
1. 選擇語句基本格式；
投影： SELECT col_name1,col_name2 …. FROM tb_name;
選擇： SELECT * FROM tb_name WHERE clause
WHERE clasue:
2. 布爾表達式：
< > =[=] >= <= IS NULL IS NOT NULL LIKE: 支持的通配符: %(任意長度的任意字符)， _（任意單個字符） RLIKE,REGEXP: 支持使用正則表達式 IN BETWEEN ... AND ..... : 在某一范圍內
組合條件測試：
NOT ! AND && OR ||
統計函數：
SUM(), AVG(), MAX(), MIN(), COUNT()
排序
ORDER BY col_name [DESC] DESC : 是否倒敘
SELECT語句的執行流程：
FROM clause --> WHERE clause --> GROUP BY --> HAVING clause --> ORDER BY ... --> SELECT --> LIMIT
SELECT語句的一些選項:
DISTINCT：指定的結果相同的只顯示一次； SQL_CACHE：緩存于查詢緩存中； SQL_NO_CACHE：不緩存查詢結果；
舉例導入hellodb.sql，以下操作在students表上執行
1、以ClassID分組，顯示每班的同學的人數； mysql>SELECT ClassID,COUNT(*) FROM students GROUP BY ClassID; 2、以Gender分組，顯示其年齡之和； mysql>SELECT Gender,SUM(Age) FROM students GROUP BY Gender; 3、以ClassID分組，顯示其平均年齡大于25的班級； mysql>SELECT ClassID FROM students GROUP BY ClassID HAVING AVG(Age) >25; 4、以Gender分組，顯示各組中年齡大于25的學員的年齡之和； mysql>SELECT Gender, SUM(Age) FROM students WHERE Age > 20 Group by Gender ;
mysql多表查詢
1. 左外聯結：只保留出現在左外連接運算之前（左邊）的關系中的元組；
left_tb LEFT JOIN right_tb ON 連接條件
2. 右外聯結：只保留出現在右外連接運算之后（右邊）的關系中的元組；
left_tb RIGHT JOIN right_tb ON 連接條件
3. 別名：表別名
字段別名 使用AS 關鍵字定義
舉例 ：導入hellodb.sql，完成以下題目：
1、顯示前5位同學的姓名、課程及成績； mysql>SELECT Name,Course FROM (SELECT Name, CourseID, Score FROM ( students AS st LEFT JOIN scores AS sc ON st.StuID = sc.StuID) WHERE Score IS NOT NULL ORDER BY Score DESC ) AS ts LEFT JOIN courses AS cs ON ts.CourseID = cs.CourseID LIMIT 5 2、顯示其成績高于80的同學的名稱及課程； mysql>SELECT Name,Course FROM (SELECT StuID, Course FROM scores AS sc LEFT JOIN courses AS cs ON sc.CourseID = cs.CourseID WHERE Score > 80) AS cc LEFT JOIN students as st ON cc.StuID = st.StuID ; 3、求前8位同學每位同學自己兩門課的平均成績，并按降序排列； mysql>SELECT Name,AVG(Score) FROM (SELECT * FROM students LIMIT 8) AS st LEFT JOIN scores AS sc ON st.StuID = sc.StuID GROUP BY Name ORDER BY AVG(Score) DESC ; 4、顯示每門課程課程名稱及學習了這門課的同學的個數； mysql>SELECT Course,COUNT(Name) FROM (SELECT Name,CourseID FROM (SELECT * FROM students LIMIT 8) AS st LEFT JOIN scores AS sc ON st.StuID = sc.StuID ) tp1 LEFT JOIN courses AS cs ON tp1.CourseID = cs.CourseID GROUP BY Name ;
MySQL的聯合查詢
SELECT clauase UNION SELECT clause UNION ... 把兩個或多個查詢語句的結果合并成一個結果進行輸出； 按照字段列拼接
MySQL視圖: 儲下來的SELECT語句；
CREATE VIEW view_name [(column_list)] AS select_statement 看起來像一個表，不過使用 SHOW TABLE STATUS 查看表屬性時所有都為空
六、MySQL·的鎖
什么是鎖
1. 鎖主要實現多用戶訪問相同資源時，實現并發訪問控制。例如兩個用戶同時修改同一表等
2. 執行操作時施加的鎖模式
讀鎖：共享鎖，可同時實加操作
防止其他人修改正在查詢的數據
寫鎖：獨占鎖，排它鎖
如果一個用戶在執行寫操作，則其他讀寫都需等待
鎖粒度：
1. 表鎖：table lock
鎖定了整張表
2.行鎖：row lock
鎖定了需要的行
粒度越小，開銷越大，但并發性越好；
粒度越大，開銷越小，但并發性越差；
鎖的實現位置：
1.MySQL鎖：可以使用顯式鎖
2.存儲引擎鎖：自動進行的（隱式鎖）；
顯式鎖(表級鎖)：
1. 手動施加鎖
LOCK TABLES tbl_name lock_type [, tbl_name lock_type] ... lock_type鎖類型：READ|WRITE
2. 取消枷鎖UNLOCK TABLES
顯示行級鎖
1. InnoDB存儲引擎也支持另外一種顯式鎖(鎖定挑選出的部分行，行級鎖 )：
SELECT ... LOCK IN SHARE MODE; SELECT ... FOR UPDATE;
2. 只是在這個SELECT語句執行過程中施加所， 當這個語句執行結束后，釋放所
七、transaction 事務的支持：
事務定義：
Mysql所支持的事務: 事務簡單來說，是一組Mysql*查詢語句*的語句，要么全部執行，要么全部不執行。把多個查詢語句當做一個工作單元。
事務所滿足的ACID測試：
1）Atomicity: 一個事物是不可分割的， 包含的語句要么同時執行，要么同時不執行
2）Consistency: 數據庫總是，從一個一致性狀態到另一個一致性狀態
3）Isolation: 一個事物所做的操作，在提交之前是不可見的。
4）Durability: 一旦事物提交了，其所作的修改就將永遠保存在數據庫中而永久有效， 不會因為其他操作產生數據丟失，即便是數據庫崩潰。
從內存同步硬盤的時間差還是有可能丟失的。
事物主要保證數據安全性，數據安全性越高并發性越差。主要體現在隔離性。
mysql的隔離級別：修改tx_isolation來調整隔離級別
1. READ-UNCOMMITTED: 讀未提交，臟讀, 不可重復讀， 幻讀。 在同一事物中，可以讀到未提交的數據修改。
2.READ-COMMITTED: 在一個事物中， 不能讀到尚未提交的事物。 但是當另一個事物提交修改后，即便在同一事物中，依然可以讀到其他事物提交后的修改。這就是不可重復讀
3.REPEATABLE-READ: 在同一事物中，即便另一事物調提交了對當前數據的修改， 讀到的數據是相同的。 但開啟新的事物時，會發現數據已經被修改， 這個是所謂的幻讀。
4.SERIALIZABLE : 串行化，事物和事物之間嚴格隔離， 當一個事物在對某一表操作時，另一事物對此表無論讀寫都將阻塞，直到前一事物操作完成。
MVCC：多版本并發控制
1. 每個事務啟動時，InnoDB為會每個啟動的事務提供一個當下時刻的快照；
2. 為了實現此功能，InnoDB會為每個表提供兩隱藏的字段，一個用于保存行的創建時間，一個用于保存行的失效時間；里面存儲的是系統版本號；（system version number）
3. MVCC只在兩個隔離級別下有效：READ COMMITTED和REPEATABLE READ
跟事務相關的常用命令
mysql> START TRANSACTION mysql> COMMIT mysql> ROLLBACK mysql> SAVEPOINT identifier mysql> ROLLBACK [WORK] TO [SAVEPOINT] identifier 如果沒有顯式啟動事務，每個語句都會當作一個獨立的事務，其執行完成后會被自動提交； mysql> SELECT @@global.autocommit; mysql> SET GLOBAL autocommit = 0; 關閉自動提交，請記得手動啟動事務，手動進行提交；
查看MySQL的事務隔離級別
mysql> SHOW GLOBAL VARIABLES LIKE 'tx_isolation'; mysql> SELECT @@global.tx_isolation;
八、MySQL存儲引擎
查看MySQL所支持的存儲引擎和表使用引擎
1. 查詢所支持的引擎
mysql> SHOW ENGINES ; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
2. 查看表屬性
mysql> SHOW TABLE STATUS IN db_name [LIKE pattern] [WHERE clause]; mysql> SHOW TABLE STATUS IN hellodb LIKE "stu%" \G;
Name: students Engine: InnoDB Version: 10 Row_format: Compact Rows: 25 Avg_row_length: 655 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: 26 Create_time: 2015-11-23 16:06:55 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment:
各項解釋：
Name: 表名
Engine: 存儲引擎
Version: 版本（表的當前版本）
Row_format: 行格式，創建表的命令中可以定義 {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
Rows: 表中的行數
Avg_row_length: 平均每行所包含的字節數；
Data_length: 表中數據總體大小，單位是字節
Max_data_length: 表能夠占用的最大空間，單位為字節，0表示沒有上限。
Index_length: 索引的大小，單位為字節
Data_free: 對于MyISAM表，表示已經分配但尚未使用的空間，其中包含此前刪除行之后騰出來的空間
Auto_increment: 下一個AUTO_INCREMENT的值；
Create_time: 表的創建時間；
Update_time：表數據的最近一次的修改時間；
Check_time：使用CHECK TABLE或myisamchk最近一次檢測表的時間；
Collation: 排序規則
Checksum: 如果啟用，則為表的checksum；
Create_options: 創建表時指定使用的其它選項；
Comment: 表的注釋信息
Innodb存儲引擎，所產生的文件格式：
1.參數innodb_file_per_table=OFF時：
每張表具有單獨的表結構文件tb_name.frm
數據內容使用共享表空間文件， ibdata#
2.參數innodb_file_per_table=ON時候：
每張表具有兩個獨立文件
tb.name.frm 表空間文件
tb_name.ibd
表空間文件(table space)： 由innodb管理的特有格式的數據文件，內部存儲索引和數據，支持聚簇索引
MyISAM存儲引擎產生的文件: 每個表都在數據庫目錄下存儲三個文件：
1. tb_name.frm ： 表結構
2. tb_name.MYD ： 數據
3. tb_name.MYI ： 索引
通過修改default_storage_engine來修改默認存儲引擎，需要寫在配置文件中。
各引擎特性
1. Innodb:
事物： 事務日志 外鍵： ＭＶＣＣ： 多版本并發機制，主要是用于支持事務 聚簇索引： 索引和表存在一起，創建表時候必須創建一個聚簇索，索引怎么排序，數據也會跟著怎么排序 聚簇索引只能有一個 聚簇索引之外的其他索引，通常稱為輔助索引 所有的輔助索引是指向聚簇索引的。而非指向元數據。 通常使用主鍵用于聚簇索引 無論據簇索引還是輔助索引都是Ｂ+樹索引 行級鎖： 間隙鎖，用來格隔離行 支持輔助索引： 自適應的hash索引： 支持熱備份:
2 MyISAM:
全文索引 支持表壓縮，但是壓縮后不能修改，用于制作數據倉庫，可節約空間提高性能 空間索引 表級鎖 延遲更新索引： 每當數據更新時，不需要立即更新索引，以降低I/O壓力 不支持事務，外鍵和行級鎖 崩潰無法安全可靠的恢復數據。 適用場景： 只讀數據， 較小的表， 崩潰后可以忍受數據恢復時間和數據丟失。
3.ARCHIVE：
僅支持INSERT和SELECT，支持很好壓縮功能； 適用于存儲日志信息，或其它按時間序列實現的數據采集類的應用； 不支持事務，不能很好的支持索引；
4. CSV：
將數據存儲為CSV格式；不支持索引；僅適用于數據交換場景，另外貌似精度很難保持；
5. BLACKHOLE：
沒有存儲機制，任何發往此引擎的數據都會丟棄；其會記錄二進制日志，因此，常用于多級復制架構中作中轉服務器；
6. MEMORY：
保存數據在內存中，內存表；常用于保存中間數據，如周期性的聚合數據等；也用于實現臨時表
支持hash索引，使用表級鎖，不支持BLOB和TEXT數據類型
7.MRG_MYISAM：
是MYISAM的一個變種，能夠將多個MyISAM表合并成一個虛表；
8.NDB：
是MySQL CLUSTER中專用的存儲引擎
第三方的存儲引擎：
1. OLTP類：
XtraDB: 增強的InnoDB，由Percona提供； 編譯安裝時，下載XtraDB的源碼替換MySQL存儲引擎中的InnoDB的源碼 PBXT: MariaDB自帶此存儲引擎 支持引擎級別的復制、外鍵約束，對SSD磁盤提供適當支持； 支持事務、MVCC
2. TokuDB: 使用Fractal Trees索引，沒有碎片問題，性能與換從無關，適用存儲大數據，擁有很壓縮比；已經被引入MariaDB；
列式存儲引擎： Infobright: 目前較有名的列式引擎，適用于海量數據存儲場景，如PB級別，專為數據分析和數據倉庫設計； 如果用于MySQL, 需要對Mysql做定制
開源社區存儲引擎：
1. Aria：前身為Maria，可理解為增強版的MyISAM(支持崩潰后安全恢復，支持數據緩存)
2.Groona：全文索引引擎，Mroonga是基于Groona的二次開發版，適用于搜索引擎
3.OQGraph: 由Open Query研發，支持圖結構的存儲引擎
4.SphinxSE: 為Sphinx全文搜索服務器提供了SQL接口
5.Spider: 能數據切分成不同分片，比較高效透明地實現了分片(shared)，并支持在分片上支持并行查詢；
如何選擇數據存儲引擎：
1.是否需要事務
2.備份的類型的支持
3.崩潰后的恢復
4.特有的特性
九、MySQL的用戶以及權限管理：
mysql用戶管理：
1.用戶格式：
username@{host_IP|host_name}
{host_IP|host_name} : 需要登錄mysql服務器的主機IP
2.mysql用戶管理的相關命令：
mysql> CREATE USER user_name IDENTIFIED BY {'auth_string' | PASSWORD 'hash_string'}
mysql> DROP USER user_name
mysql> RENAME USER old_user TO new_user
mysql> SET PASSWORD FOR 'bob'@'%.example.org' = PASSWORD('cleartext password');
mysql的權限控制：
1. 權限管理命令：
權限授予：
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user_specification [, user_specification] ...
[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
[WITH {GRANT OPTION | resource_option} ...]
GRANT PROXY ON user_specification TO user_specification [, user_specification] ... [WITH GRANT OPTION]
權限回收：
REVOKE priv_type [(column_list)][, priv_type [(column_list)]] ... ON [object_type] priv_level FROM user [, user] ... REVOKE PROXY ON user FROM user [, user] ...
查看用戶能夠使用的權限：
mysql>SHOW GRANTS FOR username@'hostname'
作用對象類型（priv_type）:
1. TABLE(默認)
2.FUNCTION
3.PROCEDURE
權限目標級別（priv_level）
1. * 所有： 庫，表，函數
2. *.* 所有庫的所有[TABLE|FUNCTION|PROCEDURE]
3. db_name.* db_name庫的所有
4. db_name.tbl_name
5. tbl_name
6. db_name.routine_name
WITH GRANT OPTION: 權限授予選項
1. MAX_QUERIES_PER_HOUR count
2. MAX_UPDATES_PER_HOUR count
3.MAX_CONNECTIONS_PER_HOUR count
4.MAX_USER_CONNECTIONS count
權限類型(priv_type)：
1. 管理類權限：
CREATE TEMPORARY TABLES：使用或者創建臨時表 CREATE USER：創建，刪除，重命名用戶 FILE：在服務器上讀或者寫, 包括備份，以及source 文件 LOCK TABLES：是否可以鎖表 PROCESS：是否能執行SHOW PROCESSLIST 命令查看mysql內部運行的線程 RELOAD：是否能使用FLUSH 和 RESET 命令 REPLICATION SLAVE：是否可以查詢主服務器有哪些從服務器 REPLICATION CLIENT：是否有權限成為從服務器 SHOW DATABASES：是否可以查詢服務器中有哪些數據庫 SHUTDOWN：關閉 SUPER：其它
2.庫級別和表級別：
ALTER：是否可以執行ALTER TABLE 命令 ALTER ROUTINE：修改存儲歷程，包括存儲函數 CREATE：創建表和庫 CREATE ROUTINE：創建歷程和函數 CREATE VIEW：創建視圖 DROP：刪除表和庫 EXECUTE：執行存儲歷程 GRNAT OPTION：把自己的權限轉贈給他人 INDEX：建立索引 SHOW VIEW：查看視圖
3.數據操作（表級別）：
SELECT:查詢 INSERT：增加 UPDATE：修改 DELETE：刪除 字段級別： SELECT(col1,...) UPDATE(col1,...) INSERT(col1,...)
4.所有權限：
ALL [PRIVILEGES]
ssl選項（ssl_option）：
SSL X509：證書格式 CIPHER 'cipher'加密方式 ISSUER 'issuer'證書頒發者 SUBJECT 'subject'拒絕某證書
幾個存儲用戶權限信息的表，全都存儲在mysql庫中
db: 庫級別權限； host: 主機級別權限，已廢棄 tables_priv: 表級別權限 colomns_priv：列級別的權限 procs_priv：存儲過程和存儲函數相關的權限 proxies_priv：代理用戶權限
事例：
1. 授予testuser能夠通過192.168.98.0/24網絡內的任意主機訪問當前mysql服務器的權限； mysql> GRANT SELECT ON TABLE *.* TO 'testuser'@'192.168.98.%' IDENTIFIED BY 'testuser'; 2. 讓此用戶能夠創建及刪除testdb數據庫，及庫中的表； GRANT CREATE,DROP ON TABLE testdb.* TO 'testuser'@'192.168.98.%' ; 3. 讓此用戶能夠在testdb庫上執行創建和刪除索引； GRANT INDEX ON TABLE testdb.* TO 'testuser'@'192.168.98.%' ; 4. 讓此用戶能夠在testdb.t2表上查詢id和name字段，并允許其將此權限轉授予其他用戶； GRANT GRANT OPTION,SELECT(id,name) ON TABLE testdb.t2 TO 'testuser'@'192.168.98.%' ; 5、讓此用戶能夠在testdb庫中的t1表中執行查詢、刪除、更新和插入操作； GRANT SELECT,DELETE,UPDATE,INSERT ON TABLE testdb.tb1 TO 'testuser'@'192.168.98.%';
十、MySQL查詢緩存
用于保存MySQL查詢語句返回的完整結果。被命中時，MySQL會立即返回結果，省去解析、優化和執行等階段。
如何檢查緩存
1. MySQL保存結果于緩存中：
2. 把SELECT語句本身做hash計算，計算的結果作為key，查詢結果作為value
什么樣的語句不會被緩存？
查詢語句中有一些不確定數據時，不會緩存：例如NOW(), CURRENT_TIME()；一般來說，如果查詢中包含用戶自定義函數、存儲函數、用戶變量、臨時表、mysql庫中系統表、或者任何包含權限的表，一般都不會緩存；
緩存會帶來額外開銷：
1、每個查詢都得先檢查是否命中；
2、查詢結果要先緩存；
緩存相關的服務器變量
MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE 'query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_strip_comments | OFF |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
query_cache_type: 查詢緩存類型；是否開啟緩存功能，開啟方式有三種{ON|OFF|DEMAND}；
DEMAND：意味著SELECT語句明確使用 SQL_CACHE 選項時才會緩存；
query_cache_size: 總空間，單位為字節，大小必須是1024的整數倍。MySQL啟動時，會一次分配并立即初始化這里指定大小的內存空間；這意味著，如果修改此大小，會清空緩存并重新初始化的。
query_cache_min_res_unit: 存儲緩存的最小內存塊；(query_cache_size - Qcache_free_memory)/Qcache_queries_in_cache能夠獲得一個理想的值。
query_cache_limit: 單個緩存對象的最大值，超出時則不預緩存；手動使用SQL_NO_CACHE可以人為地避免嘗試緩存返回結果超出此參數限定值的語句。
query_cache_wlock_invalidate: 如果某個表被其它用戶連接鎖住了，是否仍然從緩存中返回結果。OFF表示返回。
如何判斷命令率(緩存相關的狀態變量)：
MariaDB [hellodb]> SHOW GLOBAL STATUS LIKE 'Qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 16757008 | | Qcache_hits | 4 | | Qcache_inserts | 2 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 18 | | Qcache_queries_in_cache | 2 | | Qcache_total_blocks | 6 | +-------------------------+----------+ Qcache_hits： 命中次數 Qcache_free_memory： 剩余緩存空間，尚未劃分成塊的空間 Qcache_free_blocks： 空閑的塊數，劃分完成但還沒使用的空間 Qcache_total_blocks： 總塊數 Qcache_queries_in_cache： 在緩存中，緩存插入的次數。 Qcache_not_cached： 沒有緩存的 Qcache_lowmem_prunes： 因為內存太少而修剪內存的次數。 碎片整理：FLUSH QUERY_CACHE 清空緩存：RESET QUERY_CACHE
計算命中率：
MariaDB [hellodb]> SHOW GLOBAL STATUS WHERE Variable_name='Qcache_hits' OR Variable_name='Com_select'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Com_select | 24 | | Qcache_hits | 4 | +---------------+-------+ Com_select: 非緩存查詢次數 Qcache_hits： 緩存命中次數 Qcache_hits/(Com_select+Qcache_hits) 也應該參考另外一個指標：命中和寫入的比率，即Qcache_hits/Qcache_inserts的值，此比值如果能大于3:1，則表明緩存也是有效的。能達到10:1，為比較理想的情況。
緩存優化使用思路：
1、批量寫入而非多次單個寫入；
2、緩存空間不宜過大，因為大量緩存同時失效時會導致服務器假死；
3、必要時，使用SQL_CACHE和SQL_N0_CACHE手動控制緩存；
4、對寫密集型的應用場景來說，禁用緩存反而能提高性能；
十一、MySQL日志：
日志分類
1.查詢日志： 繁忙的服務器不建議記錄查詢日志
2.慢查詢日志：查詢執行時長超過指定時長的查詢，即為慢查詢
3.錯誤日志：
4.事務日志：ib_logfile0 ib_logfile1
隨機I/O轉換為順序I/O從而保證ACID的持久性。 只要事務提交，馬上寫入事務日志中。
事務日志有可能承擔讀操作，innodb_buffer可能會把裝不下的內容放入事務日志
5.日志文件組：
特性：
1）至少應該有兩個日志文件。
2）第一個滿了以后，啟動第二個。 第一個日志文件開始向磁盤同步。
3）如果事務剛寫到事務日志中數據庫崩潰，在重啟后，會把事務日志繼續同步到數據文件，從而達到一致性。
4）但如果事務日志所在的硬盤損壞導致崩潰，則無法恢復。所以要保證事務日志所在的存儲足夠可靠
5）為了分攤事務日志I/O和同步數據文件的I/O， 數據文件和事務日志要分開存放
相關參數：
innodb_log_file_size 事務日志大小
innodb_log_files_in_group 事務日志組個數
innodb_log_group_home_dir 事務日志所在位置，"./"是安裝目錄
innodb_flush_log_at_trx_commit 是否事務提交后馬上同步日志
可以放在固態硬盤上從而提高性能。
注意：盡可能使用小事務以提升事務引擎的性能。以保證盡量在回滾時，降低硬盤I/O開銷
查詢查詢日志： 繁忙的服務器不建議開啟
log={ON|OFF}:是否記錄所有語句的日志信息于一般查詢日志文件(general_log),5.6以后棄用，重復選項;
log_output={TABLE|FILE|NONE}
TABLE和FILE可以同時出現，用逗號分隔即可；
TABLE : 記錄到表中，在mysql庫中的general_log 表
FILE : 記錄到文件中，在general_log_file 選項指定日志存放位置
general_log：是否啟用查詢日志；
general_log_file：定義一般查詢日志保存的文件
慢查詢日志： 用于評估系統性能，procona有工具用來分析慢查尋日志
long_query_time： 10.000000
定義多長時間算是慢，單位為秒
slow_query_log={ON|OFF}
設定各用戶級別是否啟用慢查詢日志；它的輸出位置也取決log_output={TABLE|FILE|NONE}；
slow_query_log_file=www-slow.log
定義日志文件路徑及名稱；
log_slow_filter=admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
哪些查詢不記錄日志
log_slow_queries=ON
只有管理員才能修改，全局是否記錄慢查日志
log_slow_rate_limit=1
記錄速率
log_slow_verbosity
是否記錄詳細的慢查日志
錯誤日志：
1.服務器啟動和關閉過程中的信息；
2. 服務器運行過程中的錯誤信息；
3. 事件調度器運行一個事件時產生的信息；
4. 在復制架構中的從服務器上啟動從服務器線程時產生的信息；
log_error = /path/to/error_log_file
直接指向日志文件
log_warnings = {1|0}
是否記錄警告信息于錯誤日志中；
中繼日志:
從主服務器復制了來的二進制日志
二進制日志：有以下用途
1.引起mysql服務器改變的任何操作。
2.復制功能依賴于此日志。
3.從服務器通過復制主服務器的二進制日志完成主從復制，在執行之前保存于中繼日志中。
4.從服務器通?？梢躁P閉二進制日志以提升性能。
5.主要用于時間點恢復
5.數據庫復制
總結： 這部分內容總結，基本上來源于聽課的視頻和筆記。比較瑣碎，讀起來估計也沒有美感可言，希望讀者見諒。 后面會繼續總結備份，主從復制，高可用等內容
原創文章，作者：以馬內利，如若轉載，請注明出處：http://www.www58058.com/8033
