記錄一下mysql相關的知識點，方便以後查閱，持續更新
資料庫設計
1、一般都使用 INNODB 儲存引擎，除非讀寫比率<1%,才考慮使用 MYISAM 儲存引擎;其他儲存引擎請在 DBA 的建議下使用。
2、Stored procedure (包括儲存過程，函式，觸發器)對於 MYSQL 來說還不是很成熟，沒有完善的出錯記錄處理，不建議使用。
3、UUID()，USER()這樣的MySQL INSIDE函式對於複製來說是很危險的，會導致主備資料不一致，所以請不要使用。如果一定要使用UUID作為主鍵，讓應用程式來產生。
4、請不要使用外來鍵約束，如果資料存在外來鍵關係，請在程式層面實現。
5、選擇合適的字符集，無emoji使用utf8，有emoji使用utf8mb4。
6.選擇合適的型別。
7.新增邏輯刪除，建立，修改時間。
8.新增表，欄位註釋
9.主鍵使用bigint(20)，主外來鍵型別一致。
10.新增表，欄位註釋
11.新增索引
設計正規化
第一正規化：要求有主鍵，並且要求每一個欄位原子性不可再分
第二正規化：要求所有非主鍵欄位完全依賴主鍵，不能產生部分依賴
第三正規化：所有非主鍵欄位和主鍵欄位之間不能產生傳遞依賴
反正規化化：指的是通過增加冗餘或重複的資料來提高資料庫的讀效能。MySQL索引
索引用於快速找出在某個列中有一特定值的行，不使用索引，MySQL必須從第一條記錄開始讀完整個表，直到找出相關的行，表越大，查詢資料所花費的時間就越多，如果表中查詢的列有一個索引，MySQL能夠快速到達一個位置去搜尋資料檔案，而不必檢視所有資料，那麼將會節省很大一部分時間。
MySQL中索引的優點和缺點和使用原則
優點：
1、所有的MySql列型別(欄位型別)都可以被索引，也就是可以給任意欄位設定索引
2、大大加快資料的查詢速度
缺點：
1、建立索引和維護索引要耗費時間，並且隨著資料量的增加所耗費的時間也會增加
2、索引也需要佔空間，我們知道資料表中的資料也會有最大上線設定的，如果我們有大量的索引，索引檔案可能會比資料檔案更快達到上線值
3、當對錶中的資料進行增加、刪除、修改時，索引也需要動態的維護，降低了資料的維護速度。
使用原則：
通過上面說的優點和缺點，我們應該可以知道，並不是每個欄位度設定索引就好，也不是索引越多越好，而是需要自己合理的使用。
1、對經常更新的表就避免對其進行過多的索引，對經常用於查詢的欄位應該建立索引，
2、資料量小的表最好不要使用索引，因為由於資料較少，可能查詢全部資料花費的時間比遍歷索引的時間還要短，索引就可能不會產生優化效果。
3、在一同值少的列上(欄位上)不要建立索引，比如在學生表的”性別”欄位上只有男，女兩個不同值。相反的，在一個欄位上不同值較多可以建立索引。
索引的分類
注意：索引是在儲存引擎中實現的，也就是說不同的儲存引擎，會使用不同的索引
MyISAM和InnoDB儲存引擎：只支援BTREE索引， 也就是說預設使BTREE，不能夠更換。
MEMORY/HEAP儲存引擎：支援HASH和BTREE索引。
索引我們分為四類來講 單列索引(普通索引，唯一索引，主鍵索引)、組合索引、全文索引、空間索引、
1.單列索引：一個索引只包含單個列，但一個表中可以有多個單列索引。 這裡不要搞混淆了。
普通索引：MySQL中基本索引型別，沒有什麼限制，允許在定義索引的列中插入重複值和空值，純粹為了查詢資料更快一點。
唯一索引：索引列中的值必須是唯一的，但是允許為空值，
主鍵索引：是一種特殊的唯一索引，不允許有空值。
2.組合索引
在表中的多個欄位組合上建立的索引，只有在查詢條件中使用了這些欄位的左邊欄位時，索引才會被使用，使用組合索引時遵循最左字首集合。這個如果還不明白，等後面舉例講解時在細說
3.全文索引
全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT型別欄位上使用全文索引，介紹了要求，說說什麼是全文索引，就是在一堆文字中，通過其中的某個關鍵字等，就能找到該欄位所屬的記錄行，比如有”你是個靚仔，靚女 …” 通過靚仔，可能就可以找到該條記錄。這裡說的是可能，因為全文索引的使用涉及了很多細節，我們只需要知道這個大概意思。
4.空間索引
空間索引是對空間資料型別的欄位建立的索引，MySQL中的空間資料型別有四種，GEOMETRY、POINT、LINESTRING、POLYGON。在建立空間索引時，使用SPATIAL關鍵字。要求，引擎為MyISAM，建立空間索引的列，必須將其宣告為NOT NULL。
MySQL中的儲存引擎
在MySQL 5.7版本中，MySQL支援的儲存引擎有:
InnoDB
MyISAM
Memory
CSV
Archive
Blackhole
Merge:
Federated
Example
InnoDB：支援事務操作(如 begin， commit，rollback命令)，支援行級鎖，行級鎖相對於表鎖，其粒度更細，允許併發量更大，InnoDB儲存引擎也是MySQL 5.7版本中預設的儲存引擎。其缺點是：儲存空間會佔用比較大。
MyISAM：該儲存引擎儲存佔用的空間相對與InnoDB儲存引擎來說會少很多，但其支援的為表鎖，其併發效能會低很多，而且不支援事務，通常只應用於只讀模式的應用。它是MySQL最原始的儲存引擎。
Memory：該儲存引擎最大的特點是，所有資料均儲存在記憶體中，之前還有個名字叫做 「Heap」。
應用場景: 主要儲存一些需要快速訪且非關鍵資料，為什麼不是關鍵資料呢？就因為其所有資料儲存在記憶體中，也可以理解為不安全。
CSV：首先先認識一下CSV，CSV檔案其實就是用逗號分隔開的文字檔案，常用於資料轉換，該型別平時用的比較少，不支援索引。
Archive：存檔檔案，主要用於儲存很少用到的引用檔案，
Example：該儲存引擎主要用於展示如何自行編寫一個儲存引擎，一般不會用作生產環境使用。
mysql事務
張三有1000塊錢，李四也有1000塊錢，張三給李四500,還剩下500，李四此時就有1500。我們想象著會執行下面的mysql語句：
update table user set money=500 where name = “張三”；
update table user set money=1500 where name = “李四”；
但是在計算機中可能會不一樣。可能上面語句執行了下面的沒有執行，因此為了保證兩條語句要麼都執行，要麼都不執行，這時候就用到了事務。
事務的意思是一條或者是一組語句組成一個單元，這個單元要麼全部執行，要麼全不執行。
事務具有四個特性，也是面試常考的四個特性ACID：
A（原子性Atomicity）：原子性指的是事務是一個不可分割的，要麼都執行要麼都不執行。
C（一致性Consistency）：事務必須使得資料庫從一個一致性狀態，到另外一個一致性狀態。
I（隔離性Isolation）：指的是一個事務的執行，不能被其他的事務所干擾。
D（永續性Durability）：永續性指的是一個事務一旦提交了之後，對資料庫的改變就是永久的。
事務併發帶來的問題
如果要提升系統的吞吐量，當有多個任務需要處理時，應當讓多個事務同時執行，這就是事務的併發。既然事務存在併發執行，那必然產生同一個資料操作時的衝突問題
更新丟失（Lost Update），當兩個事務更新同一行資料時，雙方都不知道對方的存在，就有可能覆蓋對方的修改。比如兩個人同時編輯一個文件，最後一個改完的人總會覆蓋掉前面那個人的改動。
髒讀（Dirty Reads），一個事務在執行時修改了某條資料，另一個事務正好也讀取了這條資料，並基於這條資料做了其他操作，因為前一個事務還沒提交，如果基於修改後的資料進一步處理，就會產生無法挽回的損失。
不可重複讀（Non-Repeatable Reads），同樣是兩個事務在操作同一資料，如果在事務開始時讀了某資料，這時候另一個事務修改了這條資料，等事務再去讀這條資料的時候發現已經變了，這就是沒辦法重複讀一條資料。
幻讀（Phantom Read），與上方場景相同，事務一開始按某個查詢條件沒查出任何資料，結果因為另一個事務的影響，再去查時卻查到了資料，這種就像產生幻覺了一樣，被稱作幻讀。
事務的四種隔離級別
讀未提交 （Read uncommitted），讀未提交其實就是事務沒提交就可以讀，很顯然這種隔離級別會導致讀到別的還沒提交的資料，一旦基於讀到的資料做了進一步處理，而另一個事務最終回滾了操作，那麼資料就會錯亂，而且很難追蹤。總的來說說，讀未提交級別會導致髒讀。
讀提交 （Read committed），顧名思義就是事務提交後才能讀，假設你拿著銀行卡去消費，付錢之前你看到卡里有2000元，這個時候你老婆在淘寶購物，趕在你前面完成了支付，這個時候你再支付的時候就提示餘額不足，但是分明你看到卡里的錢是夠的啊。這就是兩個事務在執行時，事務A一開始讀取了卡里有2000元，這個時候事務B把卡里的錢花完了，事務A最終再確認餘額的時候發現卡里已經沒有錢了。很顯然，讀提交能解決髒讀問題，但是解決不了不可重複讀。
Sql Server，Oracle的預設隔離級別是Read committed。
可重複讀（ Repeatable read），看名字就看出來了，它的出現就是為了解決不可重複讀問題，事務A一旦開始執行，無論事務B怎麼改資料，事務A永遠讀到的就是它剛開始讀的值。那麼問題就來了，假設事務B把id為1的資料改成了2，事務A並不知道id發生了變化，當事務A新增資料的時候卻發現為2的id已經存在了，這就是幻讀。
MySQL的預設隔離級別就是Repeatable read。
序列化（ serializable），這個就是最無敵的存在了，所有的事務串起來一個個執行，因為沒有併發的場景出現了，什麼幻讀、髒讀、不可重複讀統統都不存在的。但是同樣的，基本併發能力會非常差。最終，到底什麼隔離級別完全要根據自己的業務場景選擇，沒有最好的，只有最適合的。
| 事務隔離級別 | 髒讀 | 不可重複讀 | 幻讀 |
|---|---|---|---|
| 讀未提交（read-uncommitted） | 是 | 是 | 是 |
| 不可重複讀（read-committed） | 否 | 是 | 是 |
| 可重複讀（repeatable-read） | 否 | 否 | 是 |
| 序列化（serializable） | 否 | 否 | 否 |
1、事務隔離級別為讀提交時，寫資料只會鎖住相應的行
2、事務隔離級別為可重複讀時，如果檢索條件有索引（包括主鍵索引）的時候，預設加鎖方式是next-key 鎖；如果檢索條件沒有索引，更新資料時會鎖住整張表。一個間隙被事務加了鎖，其他事務是不能在這個間隙插入記錄的，這樣可以防止幻讀。****
3、事務隔離級別為序列化時，讀寫資料都會鎖住整張表
4、隔離級別越高，越能保證資料的完整性和一致性，但是對併發效能的影響也越大
sql語句
一、基礎
1、建立資料庫
CREATE DATABASE database-name
2、刪除資料庫
drop database dbname
3、備份sql server
— 建立 備份資料的 device
USE master
EXEC sp_addumpdevice ‘disk’, ‘testBack’, ‘c:mssql7backupMyNwind_1.dat’
— 開始 備份
BACKUP DATABASE pubs TO testBack
4、建立新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根據已有的表建立新表：
A：create table tab_new like tab_old (使用舊錶建立新表)
B：create table tab_new as select col1,col2… from tab_old definition only
5、刪除新表
drop table tabname
6、增加一個列
Alter table tabname add column col type注：列增加後將不能刪除。DB2中列加上後資料型別也不能改變，唯一能改變的是增加varchar型別的長度。
7、新增主鍵： Alter table tabname add primary key(col)
刪除主鍵： **Alter table tabname drop primary key(col)
8、建立索引：create [unique] index idxname on tabname(col….)
刪除索引：drop index idxname
注：索引是不可更改的，想更改必須刪除重新建。
9、建立檢視：create view viewname as select statement
刪除檢視：drop view viewname
10、幾個簡單的基本的sql語句
選擇：select * from table1 where 範圍
插入：insert into table1(field1,field2) values(value1,value2)
刪除：delete from table1 where 範圍更新：update table1 set field1=value1 where 範圍
查詢：select * from table1 where field1 like ’%value1%’ —like的語法很精妙，查資料!
排序：select * from table1 order by field1,field2 [desc]
總數：select count as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1
11、幾個高階查詢運算詞
A： UNION 運算子
UNION 運算子通過組合其他兩個結果表（例如 TABLE1 和 TABLE2）並消去表中任何重複行而派生出一個結果表。當 ALL 隨 UNION 一起使用時（即 UNION ALL），不消除重複行。兩種情況下，派生表的每一行不是來自 TABLE1 就是來自 TABLE2。
B：EXCEPT 運算子
EXCEPT運算子通過包括所有在 TABLE1 中但不在 TABLE2 中的行並消除所有重複行而派生出一個結果表。當 ALL 隨 EXCEPT 一起使用時 (EXCEPT ALL)，不消除重複行。
C：INTERSECT 運算子
INTERSECT運算子通過只包括 TABLE1 和 TABLE2 中都有的行並消除所有重複行而派生出一個結果表。當 ALL隨 INTERSECT 一起使用時 (INTERSECT ALL)，不消除重複行。
注：使用運算詞的幾個查詢結果行必須是一致的。
12、使用外連線
A、left （outer） join：
左外連線（左連線）：結果集幾包括連線表的匹配行，也包括左連線表的所有行。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B：right （outer） join:
右外連線(右連線)：結果集既包括連線表的匹配連線行，也包括右連線表的所有行。
C：full/cross （outer） join：
全外連線：不僅包括符號連線表的匹配行，還包括兩個連線表中的所有記錄。
12、分組:Group by:
一張表，一旦分組 完成後，查詢後只能得到組相關的資訊。
組相關的資訊：（統計資訊） count,sum,max,min,avg 分組的標準)
在SQLServer中分組時：不能以text,ntext,image型別的欄位作為分組依據
在selecte統計函式中的欄位，不能和普通的欄位放在一起；
13、對資料庫進行操作：
分離資料庫： sp_detach_db;附加資料庫：sp_attach_db 後接表明，附加需要完整的路徑名
14.如何修改資料庫的名稱:
sp_renamedb ‘old_name’, ‘new_name’
mysql優化
1、選取最適用的欄位屬性
MySQL可以很好的支援大資料量的存取，但是一般說來，資料庫中的表越小，在它上面執行的查詢也就會越快。因此，在建立表的時候，為了獲得更好的效能，我們可以將表中欄位的寬度設得儘可能小。
例如，在定義郵政編碼這個欄位時，如果將其設定為CHAR(255),顯然給資料庫增加了不必要的空間，甚至使用VARCHAR這種型別也是多餘的，因為CHAR(6)就可以很好的完成任務了。同樣的，如果可以的話，我們應該使用MEDIUMINT而不是BIGIN來定義整型欄位。
另外一個提高效率的方法是在可能的情況下，應該儘量把欄位設定為NOTNULL，這樣在將來執行查詢的時候，資料庫不用去比較NULL值。
對於某些文字欄位，例如“省份”或者“性別”，我們可以將它們定義為ENUM型別。因為在MySQL中，ENUM型別被當作數值型資料來處理，而數值型資料被處理起來的速度要比文字型別快得多。這樣，我們又可以提高資料庫的效能。
2、使用連線（JOIN）來代替子查詢(Sub-Queries)
MySQL從4.1開始支援SQL的子查詢。這個技術可以使用SELECT語句來建立一個單列的查詢結果，然後把這個結果作為過濾條件用在另一個查詢中。例如，我們要將客戶基本資訊表中沒有任何訂單的客戶刪除掉，就可以利用子查詢先從銷售資訊表中將所有發出訂單的客戶ID取出來，然後將結果傳遞給主查詢，如下所示：
DELETE FROM customerinfo
WHERE CustomerID NOT IN (SELECT CustomerID FROM salesinfo)
使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的SQL操作，同時也可以避免事務或者表鎖死，並且寫起來也很容易。但是，有些情況下，子查詢可以被更有效率的連線（JOIN）..替代。例如，假設我們要將所有沒有訂單記錄的使用者取出來，可以用下面這個查詢完成：
SELECT * FROM customerinfo
WHERE CustomerID NOT IN (SELECTC ustomerID FROM salesinfo)
如果使用連線（JOIN）..來完成這個查詢工作，速度將會快很多。尤其是當salesinfo表中對CustomerID建有索引的話，效能將會更好，查詢如下：
SELECT * FROM customerinfo
LEFT JOIN salesinfo ON customerinfo.CustomerID=salesinfo.CustomerID
WHERE salesinfo.CustomerID ISNULL
連線（JOIN）..之所以更有效率一些，是因為MySQL不需要在記憶體中建立臨時表來完成這個邏輯上的需要兩個步驟的查詢工作。
3、使用聯合(UNION)來代替手動建立的臨時表
MySQL從4.0的版本開始支援union查詢，它可以把需要使用臨時表的兩條或更多的select查詢合併的一個查詢中。在客戶端的查詢會話結束的時候，臨時表會被自動刪除，從而保證資料庫整齊、高效。使用union來建立查詢的時候，我們只需要用UNION作為關鍵字把多個select語句連線起來就可以了，要注意的是所有select語句中的欄位數目要想同。下面的例子就演示了一個使用UNION的查詢。
SELECT Name,Phone FROM client UNION
SELECT Name,BirthDate FROM author UNION
SELECT Name,Supplier FROM product
4、事務
儘管我們可以使用子查詢（Sub-Queries）、連線（JOIN）和聯合（UNION）來建立各種各樣的查詢，但不是所有的資料庫操作都可以只用一條或少數幾條SQL語句就可以完成的。更多的時候是需要用到一系列的語句來完成某種工作。但是在這種情況下，當這個語句塊中的某一條語句執行出錯的時候，整個語句塊的操作就會變得不確定起來。設想一下，要把某個資料同時插入兩個相關聯的表中，可能會出現這樣的情況：第一個表中成功更新後，資料庫突然出現意外狀況，造成第二個表中的操作沒有完成，這樣，就會造成資料的不完整，甚至會破壞資料庫中的資料。要避免這種情況，就應該使用事務，它的作用是：要麼語句塊中每條語句都操作成功，要麼都失敗。換句話說，就是可以保持資料庫中資料的一致性和完整性。事物以BEGIN關鍵字開始，COMMIT關鍵字結束。在這之間的一條SQL操作失敗，那麼，ROLLBACK命令就可以把資料庫恢復到BEGIN開始之前的狀態。
BEGIN; INSERT INTO salesinfo SET CustomerID=14; UPDATE inventory SET Quantity=11 WHERE item='book'; COMMIT;
事務的另一個重要作用是當多個使用者同時使用相同的資料來源時，它可以利用鎖定資料庫的方法來為使用者提供一種安全的訪問方式，這樣可以保證使用者的操作不被其它的使用者所干擾。
5、鎖定表
儘管事務是維護資料庫完整性的一個非常好的方法，但卻因為它的獨佔性，有時會影響資料庫的效能，尤其是在很大的應用系統中。由於在事務執行的過程中，資料庫將會被鎖定，因此其它的使用者請求只能暫時等待直到該事務結束。如果一個資料庫系統只有少數幾個使用者來使用，事務造成的影響不會成為一個太大的問題；但假設有成千上萬的使用者同時訪問一個資料庫系統，例如訪問一個電子商務網站，就會產生比較嚴重的響應延遲。
其實，有些情況下我們可以通過鎖定表的方法來獲得更好的效能。下面的例子就用鎖定表的方法來完成前面一個例子中事務的功能。
LOCK TABLE inventory WRITE SELECT Quantity FROM inventory WHERE Item='book';
...
UPDATE inventory SET Quantity=11 WHERE Item='book'; UNLOCKTABLES
這裡，我們用一個select語句取出初始資料，通過一些計算，用update語句將新值更新到表中。包含有WRITE關鍵字的LOCKTABLE語句可以保證在UNLOCKTABLES命令被執行之前，不會有其它的訪問來對inventory進行插入、更新或者刪除的操作。
6、使用外來鍵
鎖定表的方法可以維護資料的完整性，但是它卻不能保證資料的關聯性。這個時候我們就可以使用外來鍵。
例如，外來鍵可以保證每一條銷售記錄都指向某一個存在的客戶。在這裡，外來鍵可以把customerinfo表中的CustomerID對映到salesinfo表中CustomerID，任何一條沒有合法CustomerID的記錄都不會被更新或插入到salesinfo中。
CREATE TABLE customerinfo( CustomerIDINT NOT NULL,PRIMARYKEY(CustomerID))TYPE=INNODB;
CREATE TABLE salesinfo( SalesIDNT NOT NULL,CustomerIDINT NOT NULL,
PRIMARYKEY(CustomerID,SalesID),
FOREIGNKEY(CustomerID) REFERENCES customerinfo(CustomerID) ON DELETE CASCADE)TYPE=INNODB;
注意例子中的引數“ON DELETE CASCADE”。該引數保證當customerinfo表中的一條客戶記錄被刪除的時候，salesinfo表中所有與該客戶相關的記錄也會被自動刪除。如果要在MySQL中使用外來鍵，一定要記住在建立表的時候將表的型別定義為事務安全表InnoDB型別。該型別不是MySQL表的預設型別。定義的方法是在CREATETABLE語句中加上TYPE=INNODB。如例中所示。
7、使用索引
索引是提高資料庫效能的常用方法，它可以令資料庫伺服器以比沒有索引快得多的速度檢索特定的行，尤其是在查詢語句當中包含有MAX(),MIN()和ORDERBY這些命令的時候，效能提高更為明顯。
那該對哪些欄位建立索引呢？
一般說來，索引應建立在那些將用於JOIN,WHERE判斷和ORDERBY排序的欄位上。儘量不要對資料庫中某個含有大量重複的值的欄位建立索引。對於一個ENUM型別的欄位來說，出現大量重複值是很有可能的情況
例如customerinfo中的“province”..欄位，在這樣的欄位上建立索引將不會有什麼幫助；相反，還有可能降低資料庫的效能。我們在建立表的時候可以同時建立合適的索引，也可以使用ALTERTABLE或CREATEINDEX在以後建立索引。此外，MySQL從版本3.23.23開始支援全文索引和搜尋。全文索引在MySQL中是一個FULLTEXT型別索引，但僅能用於MyISAM型別的表。對於一個大的資料庫，將資料裝載到一個沒有FULLTEXT索引的表中，然後再使用ALTERTABLE或CREATEINDEX建立索引，將是非常快的。但如果將資料裝載到一個已經有FULLTEXT索引的表中，執行過程將會非常慢。
8、優化的查詢語句
絕大多數情況下，使用索引可以提高查詢的速度，但如果SQL語句使用不恰當的話，索引將無法發揮它應有的作用。
下面是應該注意的幾個方面。
首先，最好是在相同型別的欄位間進行比較的操作。
在MySQL3.23版之前，這甚至是一個必須的條件。例如不能將一個建有索引的INT欄位和BIGINT欄位進行比較；但是作為特殊的情況，在CHAR型別的欄位和VARCHAR型別欄位的欄位大小相同的時候，可以將它們進行比較。
其次，在建有索引的欄位上儘量不要使用函式進行操作。
例如，在一個DATE型別的欄位上使用YEAE()函式時，將會使索引不能發揮應有的作用。所以，下面的兩個查詢雖然返回的結果一樣，但後者要比前者快得多。
- 第三，在搜尋字元型欄位時，我們有時會使用LIKE關鍵字和萬用字元，這種做法雖然簡單，但卻也是以犧牲系統效能為代價的。
例如下面的查詢將會比較表中的每一條記錄。
SELECT * FROM books
WHERE name like"MySQL%"
但是如果換用下面的查詢，返回的結果一樣，但速度就要快上很多：
SELECT * FROM books
WHERE name＞="MySQL" andname ＜"MySQM"最後，應該注意避免在查詢中讓MySQL進行自動型別轉換，因為轉換過程也會使索引變得不起作用。
mysql日誌
內容轉自詳細分析MySQL的日誌
在MariaDB/MySQL中，主要有5種日誌檔案：
1.錯誤日誌(error log)：記錄mysql服務的啟停時正確和錯誤的資訊，還記錄啟動、停止、執行過程中的錯誤資訊。
2.查詢日誌(general log)：記錄建立的客戶端連線和執行的語句。
3.二進位制日誌(bin log)：記錄所有更改資料的語句，可用於資料複製。
4.慢查詢日誌(slow log)：記錄所有執行時間超過long_query_time的所有查詢或不使用索引的查詢。
5.中繼日誌(relay log)：主從複製時使用的日誌。
日誌重新整理操作
以下操作會重新整理日誌檔案，重新整理日誌檔案時會關閉舊的日誌檔案並重新開啟日誌檔案。對於有些日誌型別，如二進位制日誌，重新整理日誌會滾動日誌檔案，而不僅僅是關閉並重新開啟。
mysql> FLUSH LOGS;
shell> mysqladmin flush-logs
shell> mysqladmin refresh
錯誤日誌
錯誤日誌是最重要的日誌之一，它記錄了MariaDB/MySQL服務啟動和停止正確和錯誤的資訊，還記錄了mysqld例項執行過程中發生的錯誤事件資訊。
可以使用” –log-error=[file_name] “來指定mysqld記錄的錯誤日誌檔案，如果沒有指定file_name，則預設的錯誤日誌檔案為datadir目錄下的 hostname.err ，hostname表示當前的主機名。
也可以在MariaDB/MySQL配置檔案中的mysqld配置部分，使用log-error指定錯誤日誌的路徑。
如果不知道錯誤日誌的位置，可以檢視變數log_error來檢視。
show variables like ‘log_error’;
在MySQL 5.5.7之前，重新整理日誌操作(如flush logs)會備份舊的錯誤日誌(以_old結尾)，並建立一個新的錯誤日誌檔案並開啟，在MySQL 5.5.7之後，執行重新整理日誌的操作時，錯誤日誌會關閉並重新開啟，如果錯誤日誌不存在，則會先建立。
在MariaDB/MySQL正在執行狀態下刪除錯誤日誌後，不會自動建立錯誤日誌，只有在重新整理日誌的時候才會建立一個新的錯誤日誌檔案。
一般查詢日誌
查詢日誌分為一般查詢日誌和慢查詢日誌，它們是通過查詢是否超出變數 long_query_time 指定時間的值來判定的。在超時時間內完成的查詢是一般查詢，可以將其記錄到一般查詢日誌中，但是建議關閉這種日誌（預設是關閉的），超出時間的查詢是慢查詢，可以將其記錄到慢查詢日誌中。
使用” –general_log={0|1} “來決定是否啟用一般查詢日誌，使用” –general_log_file=file_name “來指定查詢日誌的路徑。不給定路徑時預設的檔名以 hostname.log 命名。
和查詢日誌有關的變數有：
long_query_time = 10 ``# 指定慢查詢超時時長，超出此時長的屬於慢查詢，會記錄到慢查詢日誌中
log_output={TABLE|FILE|NONE} ``# 定義一般查詢日誌和慢查詢日誌的輸出格式，不指定時預設為file
TABLE表示記錄日誌到表中，FILE表示記錄日誌到檔案中，NONE表示不記錄日誌。只要這裡指定為NONE，即使開啟了一般查詢日誌和慢查詢日誌，也都不會有任何記錄。
和一般查詢日誌相關的變數有：
general_log=off ``# 是否啟用一般查詢日誌，為全域性變數，必須在global上修改。sql_log_off=off # 在session級別控制是否啟用一般查詢日誌，預設為off，即啟用
`general_log_file=/mydata/data/hostname.log# 預設是庫檔案路徑下主機名加上.log`
在MySQL 5.6以前的版本還有一個”log”變數也是決定是否開啟一般查詢日誌的。在5.6版本開始已經廢棄了該選項。
預設沒有開啟一般查詢日誌，也不建議開啟一般查詢日誌。此處開啟該型別的日誌，看看是如何記錄一般查詢日誌的
慢查詢日誌
查詢超出變數 long_query_time 指定時間值的為慢查詢。但是查詢獲取鎖(包括鎖等待)的時間不計入查詢時間內。
mysql記錄慢查詢日誌是在查詢執行完畢且已經完全釋放鎖之後才記錄的，因此慢查詢日誌記錄的順序和執行的SQL查詢語句順序可能會不一致(例如語句1先執行，查詢速度慢，語句2後執行，但查詢速度快，則語句2先記錄)。
注意，MySQL 5.1之後就支援微秒級的慢查詢超時時長，對於DBA來說，一個查詢執行0.5秒和執行0.05秒是非常不同的，前者可能索引使用錯誤或者走了表掃描，後者可能索引使用正確。
另外，指定的慢查詢超時時長表示的是超出這個時間的才算是慢查詢，等於這個時間的不會記錄
和慢查詢有關的變數：
long_query_time=10 ``# 指定慢查詢超時時長(預設10秒)，超出此時長的屬於慢查詢log_output={TABLE|FILE|NONE} ``# 定義一般查詢日誌和慢查詢日誌的輸出格式，預設為filelog_slow_queries={``yes``|no} ``# 是否啟用慢查詢日誌，預設不啟用slow_query_log={1|ON|0|OFF} ``# 也是是否啟用慢查詢日誌，此變數和log_slow_queries修改一個另一個同時變化``slow_query_log_file=``/mydata/data/hostname-slow``.log ``#預設路徑為庫檔案目錄下主機名加上-slow.loglog_queries_not_using_indexes=OFF ``# 查詢沒有使用索引的時候是否也記入慢查詢日誌
慢查詢在SQL語句調優的時候非常有用，應該將它啟用起來，且應該讓慢查詢閾值儘量小，例如1秒甚至低於1秒。就像一天執行上千次的1秒語句，和一天執行幾次的20秒語句，顯然更值得去優化這個1秒的語句。
二進位制日誌
二進位制日誌包含了引起或可能引起資料庫改變(如delete語句但沒有匹配行)的事件資訊，但絕不會包括select和show這樣的查詢語句。語句以”事件”的形式儲存，所以包含了時間、事件開始和結束位置等資訊。
二進位制日誌是以事件形式記錄的，不是事務日誌(但可能是基於事務來記錄二進位制日誌)，不代表它只記錄innodb日誌，myisam表也一樣有二進位制日誌。
對於事務表的操作，二進位制日誌只在事務提交的時候一次性寫入(基於事務的innodb二進位制日誌)，提交前的每個二進位制日誌記錄都先cache，提交時寫入**。對於非事務表的操作，每次執行完語句就直接寫入。
MariaDB/MySQL預設沒有啟動二進位制日誌，要啟用二進位制日誌使用 –log-bin=[on|off|file_name] 選項指定，如果沒有給定file_name，則預設為datadir下的主機名加”-bin”，並在後面跟上一串數字表示日誌序列號，如果給定的日誌檔案中包含了字尾(logname.suffix)將忽略字尾部分。
或者在配置檔案中的[mysqld]部分設定log-bin也可以。注意：對於mysql 5.7，直接啟動binlog可能會導致mysql服務啟動失敗，這時需要在配置檔案中的mysqld為mysql例項分配server_id。
[mysqld]
# server_id=1234
log-bin=[on|filename]
mysqld還建立一個二進位制日誌索引檔案，當二進位制日誌檔案滾動的時候會向該檔案中寫入對應的資訊。所以該檔案包含所有使用的二進位制日誌檔案的檔名。預設情況下該檔案與二進位制日誌檔案的檔名相同，副檔名為’.index’。要指定該檔案的檔名使用 –log-bin-index[=file_name] 選項。當mysqld在執行時不應手動編輯該檔案，免得mysqld變得混亂。
當重啟mysql服務或重新整理日誌或者達到日誌最大值時，將滾動二進位制日誌檔案，滾動日誌時只修改日誌檔名的數字序列部分。
二進位制日誌檔案的最大值通過變數 max_binlog_size 設定(預設值為1G)。但由於二進位制日誌可能是基於事務來記錄的(如innodb表型別)，而事務是絕對不可能也不應該跨檔案記錄的，如果正好二進位制日誌檔案達到了最大值但事務還沒有提交則不會滾動日誌，而是繼續增大日誌，所以 max_binlog_size 指定的值和實際的二進位制日誌大小不一定相等。
因為二進位制日誌檔案增長迅速，但官方說明因此而損耗的效能小於1%，且二進位制目的是為了恢復定點資料庫和主從複製，所以出於安全和功能考慮，極不建議將二進位制日誌和datadir放在同一磁碟上。
mysql備份與恢復
mysql複製
內容轉自深入MySQL複製
複製的基本概念和原理
mysql複製是指從一個mysql伺服器(MASTER)將資料通過日誌的方式經過網路傳送到另一臺或多臺mysql伺服器(SLAVE)，然後在slave上重放(replay或redo)傳送過來的日誌，以達到和master資料同步的目的。
它的工作原理很簡單。首先確保master資料庫上開啟了二進位制日誌，這是複製的前提。
- 在slave準備開始複製時，首先要執行
change master to語句設定連線到master伺服器的連線引數，在執行該語句的時候要提供一些資訊，包括如何連線和要從哪複製binlog，這些資訊在連線的時候會記錄到slave的datadir下的master.info檔案中，以後再連線master的時候將不用再提供這新資訊而是直接讀取該檔案進行連線。
在slave上有兩種執行緒，分別是IO執行緒和SQL執行緒。
- IO執行緒用於連線master，監控和接受master的binlog。當啟動IO執行緒成功連線master時，master會同時啟動一個dump執行緒，該執行緒將slave請求要複製的binlog給dump出來，之後IO執行緒負責監控並接收master上dump出來的二進位制日誌，當master上binlog有變化的時候，IO執行緒就將其複製過來並寫入到自己的中繼日誌(relay log)檔案中。
- slave上的另一個執行緒SQL執行緒用於監控、讀取並重放relay log中的日誌，將資料寫入到自己的資料庫中。如下圖所示。
站在slave的角度上看，過程如下：
站在master的角度上看，過程如下(預設的非同步複製模式，前提是設定了sync_binlog=1，否則binlog刷盤時間由作業系統決定)：
所以，可以認為複製大致有三個步驟：
- 資料修改寫入master資料庫的binlog中。
- slave的IO執行緒複製這些變動的binlog到自己的relay log中。
- slave的SQL執行緒讀取並重新應用relay log到自己的資料庫上，讓其和master資料庫保持一致。
從複製的機制上可以知道，在複製進行前，slave上必須具有master上部分完整內容作為複製基準資料。例如，master上有資料庫A，二進位制日誌已經寫到了pos1位置，那麼在複製進行前，slave上必須要有資料庫A，且如果要從pos1位置開始複製的話，還必須有和master上pos1之前完全一致的資料。如果不滿足這樣的一致性條件，那麼在replay中繼日誌的時候將不知道如何進行應用而導致資料混亂。也就是說，複製是基於binlog的position進行的，複製之前必須保證position一致。(注：這是傳統的複製方式所要求的)
可以選擇對哪些資料庫甚至資料庫中的哪些表進行復制。預設情況下，MySQL的複製是非同步的。slave可以不用一直連著master，即使中間斷開了也能從斷開的position處繼續進行復制。
MySQL 5.6對比MySQL 5.5在複製上進行了很大的改進，主要包括支援GTID(Global Transaction ID,全域性事務ID)複製和多SQL執行緒並行重放。GTID的複製方式和傳統的複製方式不一樣，通過全域性事務ID，它不要求複製前slave有基準資料，也不要求binlog的position一致。
MySQL 5.7.17則提出了組複製(MySQL Group Replication,MGR)的概念。像資料庫這樣的產品，必須要儘可能完美地設計一致性問題，特別是在叢集、分散式環境下。Galera就是一個MySQL叢集產品，它支援多主模型(多個master)，但是當MySQL 5.7.17引入了MGR功能後，Galera的優勢不再明顯，甚至MGR可以取而代之。MGR為MySQL叢集中多主複製的很多問題提供了很好的方案，可謂是一項革命性的功能。
複製的好處
圍繞下面的拓撲圖來分析：
主要有以下幾點好處：
1.提供了讀寫分離的能力。
replication讓所有的slave都和master保持資料一致，因此外界客戶端可以從各個slave中讀取資料，而寫資料則從master上操作。也就是實現了讀寫分離。
需要注意的是，為了保證資料一致性，寫操作必須在master上進行。
通常說到讀寫分離這個詞，立刻就能意識到它會分散壓力、提高效能。
2.為MySQL伺服器提供了良好的伸縮(scale-out)能力。
由於各個slave伺服器上只提供資料檢索而沒有寫操作，因此”隨意地”增加slave伺服器數量來提升整個MySQL群的效能，而不會對當前業務產生任何影響。
之所以”隨意地”要加上雙引號，是因為每個slave都要和master建立連線，傳輸資料。如果slave數量巨多，master的壓力就會增大，網路頻寬的壓力也會增大。
3.資料庫備份時，對業務影響降到最低。
由於MySQL伺服器群中所有資料都是一致的(至少幾乎是一致的)，所以在需要備份資料庫的時候可以任意停止某一臺slave的複製功能(甚至停止整個mysql服務)，然後從這臺主機上進行備份，這樣幾乎不會影響整個業務(除非只有一臺slave，但既然只有一臺slave，說明業務壓力並不大，短期內將這個壓力分配給master也不會有什麼影響)。
4.能提升資料的安全性。
這是顯然的，任意一臺mysql伺服器斷開，都不會丟失資料。即使是master當機，也只是丟失了那部分還沒有傳送的資料(非同步複製時才會丟失這部分資料)。
5.資料分析不再影響業務。
需要進行資料分析的時候，直接劃分一臺或多臺slave出來專門用於資料分析。這樣OLTP和OLAP可以共存，且幾乎不會影響業務處理效能。
複製分類和特性
MySQL支援兩種不同的複製方法：傳統的複製方式和GTID複製。MySQL 5.7.17之後還支援組複製(MGR)。
- (1).傳統的複製方法要求複製之前，slave上必須有基準資料，且binlog的position一致。
- (2).GTID複製方法不要求基準資料和binlog的position一致性。GTID複製時，master上只要一提交，就會立即應用到slave上。這極大地簡化了複製的複雜性，且更好地保證master上和各slave上的資料一致性。
從資料同步方式的角度考慮，MySQL支援4種不同的同步方式：同步(synchronous)、半同步(semisynchronous)、非同步(asynchronous)、延遲(delayed)。所以對於複製來說，就分為同步複製、半同步複製、非同步複製和延遲複製。
同步複製
客戶端傳送DDL/DML語句給master，master執行完畢後還需要等待所有的slave都寫完了relay log才認為此次DDL/DML成功，然後才會返回成功資訊給客戶端。同步複製的問題是master必須等待，所以延遲較大，在MySQL中不使用這種複製方式。
例如上圖中描述的，只有3個slave全都寫完relay log並返回ACK給master後，master才會判斷此次DDL/DML成功。
半同步複製
客戶端傳送DDL/DML語句給master，master執行完畢後還要等待一個slave寫完relay log並返回確認資訊給master，master才認為此次DDL/DML語句是成功的，然後才會傳送成功資訊給客戶端。半同步複製只需等待一個slave的回應，且等待的超時時間可以設定，超時後會自動降級為非同步複製，所以在區域網內(網路延遲很小)使用半同步複製是可行的。
例如上圖中，只有第一個slave返回成功，master就判斷此次DDL/DML成功，其他的slave無論複製進行到哪一個階段都無關緊要。
非同步複製
客戶端傳送DDL/DML語句給master，master執行完畢立即返回成功資訊給客戶端，而不管slave是否已經開始複製。這樣的複製方式導致的問題是，當master寫完了binlog，而slave還沒有開始複製或者複製還沒完成時，slave上和master上的資料暫時不一致，且此時master突然當機，slave將會丟失一部分資料。如果此時把slave提升為新的master，那麼整個資料庫就永久丟失這部分資料。
延遲複製
顧名思義，延遲複製就是故意讓slave延遲一段時間再從master上進行復制。
配置一主一從
mysql支援一主一從和一主多從。但是每個slave必須只能是一個master的從，否則從多個master接受二進位制日誌後重放將會導致資料混亂的問題。
以下是一主一從的結構圖：
在開始傳統的複製(非GTID複製)前，需要完成以下幾個關鍵點，這幾個關鍵點指導後續複製的所有步驟。
- 為master和slave設定不同的
server-id，這是主從複製結構中非常關鍵的標識號。到了MySQL 5.7，似乎不設定server id就無法開啟binlog。設定server id需要重啟MySQL例項。 - 開啟master的binlog。剛安裝並初始化的MySQL預設未開啟binlog，建議手動設定binlog且為其設定檔名，否則預設以主機名為基名時修改主機名後會找不到日誌檔案。
- 最好設定master上的變數
sync_binlog=1(MySQL 5.7.7之後預設為1，之前的版本預設為0)，這樣每寫一次二進位制日誌都將其重新整理到磁碟，讓slave伺服器可以儘快地複製。防止萬一master的二進位制日誌還在快取中就當機時，slave無法複製這部分丟失的資料。 - 最好設定master上的redo log的刷盤變數
innodb_flush_log_at_trx_commit=1(預設值為1)，這樣每次提交事務都會立即將事務刷盤保證永續性和一致性。 - 在slave上開啟中繼日誌relay log。這個是預設開啟的，同樣建議手動設定其檔名。
- 建議在master上專門建立一個用於複製的使用者，它只需要有複製許可權
replication slave用來讀取binlog。 - 確保slave上的資料和master上的資料在”複製的起始position之前”是完全一致的。如果master和slave上資料不一致，複製會失敗。
- 記下master開始複製前binlog的position，因為在slave連線master時需要指定從master的哪個position開始複製。
- 考慮是否將slave設定為只讀，也就是開啟
read_only選項。這種情況下，除了具有super許可權(mysql 5.7.16還提供了super_read_only禁止super的寫操作)和SQL執行緒能寫資料庫，其他使用者都不能進行寫操作。這種禁寫對於slave來說，絕大多數場景都非常適合。
一主多從
一主多從有兩種情況，結構圖如下。
以下是一主多從的結構圖(和一主一從的配置方法完全一致)：
以下是一主多從，但某slave是另一群MySQL例項的master：
配置一主多從時，需要考慮一件事：slave上是否要開啟binlog? 如果不開啟slave的binlog，效能肯定要稍微好一點。但是開啟了binlog後，可以通過slave來備份資料，也可以在master當機時直接將slave切換為新的master。此外，如果是上面第二種主從結構，這臺slave必須開啟binlog。可以將某臺或某幾臺slave開啟binlog，並在mysql動靜分離的路由演算法上稍微減少一點到這些slave上的訪問權重。
上面第一種一主多從的結構沒什麼可解釋的，它和一主一從的配置方式完全一樣，但是可以考慮另一種情況：向現有主從結構中新增新的slave。
本作品採用《CC 協議》，轉載必須註明作者和本文連結