MySQL全面優(yōu)化
1.優(yōu)化哲學(xué)
1.1 為什么優(yōu)化刮萌？
為了獲得成就感?
為了證實比系統(tǒng)設(shè)計者更懂?dāng)?shù)據(jù)庫?
為了從優(yōu)化成果來證實優(yōu)化者更有價值?
但通常事實證實的結(jié)果往往會和您期待相反！
優(yōu)化有風(fēng)險，涉足需謹(jǐn)慎掰邢！

1.2 優(yōu)化風(fēng)險
優(yōu)化不總是對一個單純的環(huán)境進行！還很可能是一個復(fù)雜的已投產(chǎn)的系統(tǒng)。
優(yōu)化手段本來就有很大的風(fēng)險怕篷，只不過你沒能力意識到和預(yù)見到！
任何的技術(shù)可以解決一個問題酗昼，但必然存在帶來一個問題的風(fēng)險廊谓！
對于優(yōu)化來說解決問題而帶來的問題控制在可接受的范圍內(nèi)才是有成果。
保持現(xiàn)狀或出現(xiàn)更差的情況都是失斅橄鳌蒸痹！
穩(wěn)定性和業(yè)務(wù)可持續(xù)性通常比性能更重要榛鼎！
優(yōu)化不可避免涉及到變更平匈，變更就有風(fēng)險孟辑！
優(yōu)化使性能變好，維持和變差是等概率事件践图！
優(yōu)化不能只是數(shù)據(jù)庫管理員擔(dān)當(dāng)風(fēng)險兽狭，但會所有的人分享優(yōu)化成果蒸健！
所以優(yōu)化工作是由業(yè)務(wù)需要驅(qū)使的＝戎Ｊ兑航瞭！

1.3 誰參與優(yōu)化
數(shù)據(jù)庫管理員
業(yè)務(wù)部門代表
應(yīng)用程序架構(gòu)師
應(yīng)用程序設(shè)計人員
應(yīng)用程序開發(fā)人員
硬件及系統(tǒng)管理員
存儲管理員

1.4 優(yōu)化方向
安全優(yōu)化（業(yè)務(wù)持續(xù)性）
性能優(yōu)化（業(yè)務(wù)高效性）

1.5 優(yōu)化的范圍及思路
優(yōu)化范圍：
存儲、主機和操作系統(tǒng):
主機架構(gòu)穩(wěn)定性
I/O規(guī)劃及配置
Swap
OS內(nèi)核參數(shù)
網(wǎng)絡(luò)問題
應(yīng)用程序:（Index，lock窍蓝，session）
應(yīng)用程序穩(wěn)定性和性能
SQL語句性能
串行訪問資源
性能欠佳會話管理
數(shù)據(jù)庫優(yōu)化:（內(nèi)存、數(shù)據(jù)庫設(shè)計、參數(shù)）
內(nèi)存
數(shù)據(jù)庫結(jié)構(gòu)(物理&邏輯)
實例配置
優(yōu)化效果和成本的評估：

2. 優(yōu)化工具的使用
   2.1 系統(tǒng)層面的
   2.1.1 CPU
   top
   top -H
   top -H -p pid
   top -H -p 9618
   CPU使用情況:
   %Cpu(s):
   0.1 us : 用戶進程占用CPU時間占比
   我們覺得越高越好,不要滿負(fù)荷
   0.2 sy : 系統(tǒng)本身和內(nèi)核工作CPU時間占比
   資源管理,維護,分配,回收等等.
   并發(fā)連接過高 .
   鎖爭用
   99.8 id : 空閑
   0.0 wa : 等待時CPU時間占比
   IO吞吐 有問題 : 硬件,鏈路,RAID
   IO/PS 峰值 : 大量隨機IO ,索引設(shè)計不合理,多表連接優(yōu)化不到位,大量子查詢,大事務(wù).
   鎖爭用

2.1.2 MEM
KiB Mem : 4028432 total, 3774464 free, 116240 used, 137728 buff/cache

1.2.1 名稱介紹
total :總內(nèi)存大小
free ：空閑的
used ：在使用的
buff/cache ：緩沖區(qū) 和 緩存

2.1.3 內(nèi)存管理子系統(tǒng)：
slab Allocator
buddy system
程序=指令+數(shù)據(jù)
對于page cache來講(OS buffer)

內(nèi)存的可用空間的計算 free +buffer cache
內(nèi)存回收(buffer)的方式:
(1) 寫入磁盤
(2) swap
對于數(shù)據(jù)庫來講：需要將swap屏蔽掉
2.1.4 swap
KiB Swap: 2097148 total, 2097148 free, 0 used. 3701464 avail Mem
Linux 6操作系統(tǒng)，默認(rèn)回收策略（buffer cache）您没，不立即回收策略
內(nèi)存使用達到100%-60%時候跟继，40% 會使用swap
Linux 7操作系統(tǒng)

內(nèi)存使用達到100%-30%（70%）時候纪挎，才會時候swap
cat /proc/sys/vm/swappiness
30
echo 0 >/proc/sys/vm/swappiness 的內(nèi)容改成0（臨時）
vim /etc/sysctl.conf
添加:
vm.swappiness=0
sysctl -p
2.2. iostat 命令
dd if=/dev/zero of=/tmp/bigfile bs=1M count=4096
iostat -dm 1
現(xiàn)象說明
IO 高 cpu us 也高,屬于正忱蚶迹現(xiàn)象
CPU us高 IO很低 ,MySQL 不在做增刪改查,有可能是存儲過程,函數(shù),排序,分組,多表連接
Wait,SYS 高 , IO低: IO出問題了,鎖等待過多的幾率比較大.
IOPS：每秒磁盤最多能夠發(fā)生的IO次數(shù)，這是個定值
頻繁小事務(wù),IOPS很高,達到閾值,可能IO吞吐量沒超過IO最大吞吐量.無法新的IO了
存儲規(guī)劃有問題.
2.3 數(shù)據(jù)庫優(yōu)化工具
show status
show variables
show index
show processlist
show slave status
show engine innodb status
desc /explain
slowlog

擴展類深度優(yōu)化:
pt系列
mysqlslap
sysbench
information_schema
performance_schema
sys

3. 優(yōu)化思路分解
   3.1 硬件優(yōu)化
   3.1.1 主機
   (1) 真實的硬件（PC Server）:
   DELL R系列 县耽，華為句喷，浪潮，HP兔毙，聯(lián)想 ,曙光
   (2)云產(chǎn)品：
   ECS唾琼、
   數(shù)據(jù)庫RDS、DRDS\Polardb , TDSQL
   (3) IBM 小型機:
   P6 570 595
   P7 720 750 780
   P8

3.1.2 CPU根據(jù)數(shù)據(jù)庫類型
OLTP 在線事務(wù)處理系統(tǒng).
OLAP 數(shù)據(jù)分析,處理
IO密集型：線上系統(tǒng)澎剥，OLTP主要是IO密集型的業(yè)務(wù)锡溯，高并發(fā)
CPU密集型：數(shù)據(jù)分析數(shù)據(jù)處理，OLAP，cpu密集型的祭饭，需要CPU高計算能力（i系列芜茵，IBM power系列）
CPU密集型： I 系列的，主頻很高倡蝙，核心少
IO密集型： E系列（至強）九串，主頻相對低，核心數(shù)量多

3.1.3 內(nèi)存
建議2-8倍cpu核心數(shù)量 （ECC）

3.1.4 磁盤選擇
SATA-III SAS Fc SSD（sata） pci-e ssd Flash
主機 RAID卡的BBU(Battery Backup Unit)關(guān)閉

3.1.3 存儲設(shè)備
根據(jù)存儲數(shù)據(jù)種類的不同寺鸥，選擇不同的存儲設(shè)備
配置合理的RAID級別(raid5猪钮、raid10、熱備盤)
r0 :條帶化 ,性能高
r1 :鏡像胆建，安全
r5 :校驗+條帶化烤低，安全較高+性能較高（讀），寫性能較低 （適合于讀多寫少）
r10：安全+性能都很高眼坏，最少四塊盤拂玻，浪費一半的空間（高IO要求）

3.1.4 網(wǎng)絡(luò)
1酸些、硬件買好的（單卡單口）
2宰译、網(wǎng)卡綁定（bonding），交換機堆疊
以上問題魄懂，提前規(guī)避掉沿侈。

3.2 操作系統(tǒng)優(yōu)化
3.2.1 Swap調(diào)整
echo 0 >/proc/sys/vm/swappiness的內(nèi)容改成0（臨時），
vim /etc/sysctl.conf
上添加vm.swappiness=0（永久）
sysctl -p
擴展:內(nèi)存管理

1. 三個區(qū)域 :
   常駐內(nèi)存集: 程序運行服務(wù)的
   頁緩存page cache:
   FREE list
   LRU list
   匿名頁:
2. Slab Allocator
   將 page cache 劃分成了好多條鏈狀結(jié)構(gòu).

類型一:
Free list(空閑的)
==================================================
| |
o o

類型二 :
LRU list (正在被使用的)

冷================================================熱
i i
0 0

3. buddy system (內(nèi)存伙伴系統(tǒng))

內(nèi)存回收和重利用
cat /proc/sys/vm/swappiness ====> 0

free list 上為 0;

buddy system 進行內(nèi)存回收和重利用

1. 優(yōu)先釋放 Cache(負(fù)責(zé)查詢類的內(nèi)存結(jié)構(gòu)),從冷到熱進行釋放.
2. Cache沒法釋放時,會根據(jù)buffer從冷導(dǎo)熱,進行回收和重用內(nèi)存
3. 所有可被釋放 buffer 或cache,已經(jīng)全部被回收重用了,還是內(nèi)存緊缺的話
   此時,swap還是會被使用.

內(nèi)存泄露問題:
8G 使用率達到了 95%以上
innodb_buffer_pool_size=2G
redo_buffer_size=256M
其他內(nèi)存總共: 1G 左右
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
3.2.2 IO調(diào)度策略
centos 7 默認(rèn)是 deadline
cat /sys/block/sda/queue/scheduler
cat /sys/block/vda/queue/scheduler

臨時修改為deadline(centos6)

echo deadline >/sys/block/sda/queue/scheduler
vi /boot/grub/grub.conf

更改到如下內(nèi)容:
kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet

IO ：
raid
no lvm
ext4或xfs
ssd
IO調(diào)度策略
提前規(guī)劃好以上所有問題市栗，減輕MySQL優(yōu)化的難度缀拭。
3.3 應(yīng)用層

開發(fā)過程規(guī)范,標(biāo)準(zhǔn)
減少慢SQL:不走索引,復(fù)雜邏輯,切割大事務(wù).
like '%aa%'
!= not in
limit >500w
DDL ---> show processlist ; ---> kill ---> Online DDL ,pt-osc
delete 大量數(shù)據(jù). ----> pt-archive
update : 索引 , 鎖.
避免業(yè)務(wù)邏輯錯誤,避免鎖爭用.
a : update id=10 ; update id=100;
b : update id=100; update id=10;
這個階段,需要我們DBA深入業(yè)務(wù),或者要和開發(fā)人員\業(yè)務(wù)人員配合實現(xiàn)

優(yōu)化,最根本的是"優(yōu)化"人.
----oldguo

MySQL參數(shù)優(yōu)化測試
mysqlslap --defaults-file=/etc/my.cnf
--concurrency=100 --iterations=1 --create-schema='test'
--query="select * from test.t100w where k2='FGCD'" engine=innodb
--number-of-queries=2000 -uroot -p123 -verbose

5. 數(shù)據(jù)庫實例層優(yōu)化細節(jié)：
   5.1 參數(shù)優(yōu)化
   Max_connections *****
   （1）簡介
   Mysql的最大連接數(shù)，如果服務(wù)器的并發(fā)請求量比較大填帽，可以調(diào)高這個值蛛淋，當(dāng)然這是要建立在機器能夠支撐的情況下，因為如果連接數(shù)越來越多篡腌，mysql會為每個連接提供緩沖區(qū)褐荷，就會開銷的越多的內(nèi)存，所以需要適當(dāng)?shù)恼{(diào)整該值嘹悼，不能隨便去提高設(shè)值叛甫。
   （2）判斷依據(jù)
   show variables like 'max_connections';
   +-----------------+-------+
   | Variable_name | Value |
   +-----------------+-------+
   | max_connections | 151 |
   +-----------------+-------+
   show status like 'Max_used_connections';
   +----------------------+-------+
   | Variable_name | Value |
   +----------------------+-------+
   | Max_used_connections | 101 |
   +----------------------+-------+

（3）修改方式舉例
vim /etc/my.cnf
Max_connections=1024

補充:
1.開啟數(shù)據(jù)庫時,我們可以臨時設(shè)置一個比較大的測試值
2.觀察show status like 'Max_used_connections';變化
3.如果max_used_connections跟max_connections相同,
那么就是max_connections設(shè)置過低或者超過服務(wù)器的負(fù)載上限了，
低于10%則設(shè)置過大.

小問題:
配置修改為1000,但是最終生效的是214個Connections? 為啥?
/etc/security/limits.conf
nofile 文件句柄數(shù)放開限制 65535

http 409錯誤,達到連接數(shù)上限

mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connecti
back_log ***
（1）簡介
mysql能暫存的連接數(shù)量杨伙，當(dāng)主要mysql線程在一個很短時間內(nèi)得到非常多的連接請求時候它就會起作用其监，如果mysql的連接數(shù)據(jù)達到max_connections時候，新來的請求將會被存在堆棧中限匣，等待某一連接釋放資源抖苦，該推棧的數(shù)量及back_log,如果等待連接的數(shù)量超過back_log，將不被授予連接資源。
back_log值指出在mysql暫時停止回答新請求之前的短時間內(nèi)有多少個請求可以被存在推棧中睛约，只有如果期望在一個短時間內(nèi)有很多連接的時候需要增加它
（2）判斷依據(jù)
show full processlist
發(fā)現(xiàn)大量的待連接進程時鼎俘，就需要加大back_log或者加大max_connections的值
（3）修改方式舉例
vim /etc/my.cnf
back_log=100
wait_timeout和interactive_timeout ****
（1）簡介
wait_timeout：指的是mysql在關(guān)閉一個非交互的連接之前所要等待的秒數(shù)
interactive_timeout：指的是mysql在關(guān)閉一個交互的連接之前所需要等待的秒數(shù)，比如我們在終端上進行mysql管理辩涝，使用的即使交互的連接贸伐，這時候，如果沒有操作的時間超過了interactive_time設(shè)置的時間就會自動的斷開怔揩，默認(rèn)的是28800捉邢，可調(diào)優(yōu)為7200。
wait_timeout:如果設(shè)置太小商膊，那么連接關(guān)閉的就很快伏伐，從而使一些持久的連接不起作用
（2）設(shè)置建議
如果設(shè)置太大，容易造成連接打開時間過長晕拆，在show processlist時候藐翎，能看到很多的連接 ，一般希望wait_timeout盡可能低

（3）修改方式舉例
wait_timeout=60
interactive_timeout=1200
長連接的應(yīng)用实幕，為了不去反復(fù)的回收和分配資源吝镣，降低額外的開銷。
一般我們會將wait_timeout設(shè)定比較小昆庇，interactive_timeout要和應(yīng)用開發(fā)人員溝通長鏈接的應(yīng)用是否很多末贾。如果他需要長鏈接，那么這個值可以不需要調(diào)整整吆。
另外還可以使用類外的參數(shù)彌補拱撵。
5.4 key_buffer_size *****
（1）簡介
key_buffer_size指定索引緩沖區(qū)的大小，它決定索引處理的速度表蝙，尤其是索引讀的速度
《1》此參數(shù)與myisam表的索引有關(guān)
《2》臨時表的創(chuàng)建有關(guān)（多表鏈接拴测、子查詢中、union）
在有以上查詢語句出現(xiàn)的時候府蛇，需要創(chuàng)建臨時表集索，用完之后會被丟棄
臨時表有兩種創(chuàng)建方式：
內(nèi)存中------->key_buffer_size
磁盤上------->ibdata1(5.6)
ibtmp1 (5.7）
（2）設(shè)置依據(jù)
可以使用檢查狀態(tài)值created_tmp_disk_tables得知：

mysql> show status like "created_tmp%";
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 1 |
+-------------------------+-------+

公式:
Created_tmp_disk_tables/Created_tmp_disk_tables+ Created_tmp_tables <10%

看以下例子：
在調(diào)用mysqldump備份數(shù)據(jù)時，大概執(zhí)行步驟如下：
180322 17:39:33 7 Connect root@localhost on
7 Query /*!40100 SET @@SQL_MODE='' /
7 Init DB guo
7 Query SHOW TABLES LIKE 'guo'
7 Query LOCK TABLES guo READ /!32311 LOCAL /
7 Query SET OPTION SQL_QUOTE_SHOW_CREATE=1
7 Query show create table guo
7 Query show fields from guo
7 Query show table status like 'guo'
7 Query SELECT /!40001 SQL_NO_CACHE */ * FROM guo
7 Query UNLOCK TABLES
7 Quit

其中欲诺，有一步是：show fields from guo抄谐。從slow query記錄的執(zhí)行計劃中，可以知道它也產(chǎn)生了 Tmp_table_on_disk扰法。

所以說蛹含，以上公式并不能真正反映到mysql里臨時表的利用率，有些情況下產(chǎn)生的 Tmp_table_on_disk 我們完全不用擔(dān)心塞颁，因此沒必要過分關(guān)注 Created_tmp_disk_tables浦箱，但如果它的值大的離譜的話吸耿，那就好好查一下，你的服務(wù)器到底都在執(zhí)行什么查詢了酷窥。
（3）配置方法
key_buffer_size=64M
query_cache_size ***
（1）簡介：
查詢緩存簡稱QC咽安，使用查詢緩沖，mysql將查詢結(jié)果存放在緩沖區(qū)中蓬推，今后對于同樣的select語句（區(qū)分大小寫）,將直接從緩沖區(qū)中讀取結(jié)果妆棒。

SQL層：
select * from t1 where name=:NAME;
select * from t1 where name=:NAME;

1、查詢完結(jié)果之后沸伏，會對SQL語句進行hash運算糕珊，得出hash值,我們把他稱之為SQL_ID
2、會將存儲引擎返回的結(jié)果+SQL_ID存儲到緩存中毅糟。

存儲方式：
例子：select * from t1 where id=10; 100次

1红选、將select * from t1 where id=10; 進行hash運算計算出一串hash值，我們把它稱之為“SQL_ID"
2姆另、將存儲引擎返回上來的表的內(nèi)容+SQLID存儲到查詢緩存中

使用方式：
1喇肋、一條SQL執(zhí)行時，進行hash運算迹辐，得出SQLID蝶防，去找query cache
2、如果cache中有右核，則直接返回數(shù)據(jù)行慧脱，如果沒有渺绒，就走原有的SQL執(zhí)行流程

一個sql查詢?nèi)绻詓elect開頭贺喝，那么mysql服務(wù)器將嘗試對其使用查詢緩存。
注：兩個sql語句宗兼，只要想差哪怕是一個字符（列如大小寫不一樣躏鱼；多一個空格等）,那么這兩個sql將使用不同的一個cache。

（2）判斷依據(jù)
mysql> show status like "%Qcache%";
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031360 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2002 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+---------+
8 rows in set (0.00 sec)

---------------------狀態(tài)說明--------------------
Qcache_free_blocks：緩存中相鄰內(nèi)存塊的個數(shù)殷绍。
如果該值顯示較大染苛，則說明Query Cache 中的內(nèi)存碎片較多了，F(xiàn)LUSH QUERY CACHE會對緩存中的碎片進行整理主到，從而得到一個空閑塊茶行。
注：當(dāng)一個表被更新之后，和它相關(guān)的cache
blocks將被free登钥。但是這個block依然可能存在隊列中畔师，除非是在隊列的尾部∧晾危可以用FLUSH QUERY CACHE語句來清空free blocks

Qcache_free_memory：Query Cache 中目前剩余的內(nèi)存大小看锉。通過這個參數(shù)我們可以較為準(zhǔn)確的觀察出當(dāng)前系統(tǒng)中的Query Cache 內(nèi)存大小是否足夠姿锭，是需要增加還是過多了。

Qcache_hits：表示有多少次命中緩存伯铣。我們主要可以通過該值來驗證我們的查詢緩存的效果呻此。數(shù)字越大，緩存效果越理想腔寡。

Qcache_inserts：表示多少次未命中然后插入焚鲜，意思是新來的SQL請求在緩存中未找到，不得不執(zhí)行查詢處理放前，執(zhí)行查詢處理后把結(jié)果insert到查詢緩存中恃泪。這樣的情況的次數(shù)越多，表示查詢緩存應(yīng)用到的比較少犀斋，效果也就不理想贝乎。當(dāng)然系統(tǒng)剛啟動后，查詢緩存是空的叽粹，這很正常览效。

Qcache_lowmem_prunes：
多少條Query因為內(nèi)存不足而被清除出QueryCache。通過“Qcache_lowmem_prunes”和“Qcache_free_memory”相互結(jié)合虫几，能夠更清楚的了解到我們系統(tǒng)中Query Cache 的內(nèi)存大小是否真的足夠锤灿，是否非常頻繁的出現(xiàn)因為內(nèi)存不足而有Query 被換出。這個數(shù)字最好長時間來看辆脸；如果這個數(shù)字在不斷增長但校，就表示可能碎片非常嚴(yán)重，或者內(nèi)存很少啡氢。（上面的free_blocks和free_memory可以告訴您屬于哪種情況）

Qcache_not_cached：不適合進行緩存的查詢的數(shù)量状囱，通常是由于這些查詢不是 SELECT 語句或者用了now()之類的函數(shù)。

Qcache_queries_in_cache：當(dāng)前Query Cache 中cache 的Query 數(shù)量倘是；
Qcache_total_blocks：當(dāng)前Query Cache 中的block 數(shù)量亭枷；。
Qcache_hits / (Qcache_inserts+Qcache_not_cached+Qcache_hits)
90/ 10000 0 90

如果出現(xiàn)hits比例過低搀崭，其實就可以關(guān)閉查詢緩存了叨粘。使用redis專門緩存數(shù)據(jù)庫

Qcache_free_blocks 來判斷碎片
Qcache_free_memory + Qcache_lowmem_prunes 來判斷內(nèi)存夠不夠
Qcache_hits 多少次命中 Qcache_hits / (Qcache_inserts+Qcache_not_cached+Qcache_hits)

（3）配置示例
mysql> show variables like '%query_cache%' ;
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
6 rows in set (0.00 sec)

mysql>
-------------------配置說明-------------------------------
以上信息可以看出query_cache_type為off表示不緩存任何查詢

各字段的解釋：
query_cache_limit：超過此大小的查詢將不緩存
query_cache_min_res_unit：緩存塊的最小大小，query_cache_min_res_unit的配置是一柄”雙刃劍”瘤睹，默認(rèn)是4KB升敲，設(shè)置值大對大數(shù)據(jù)查詢有好處，但如果你的查詢都是小數(shù)據(jù)查詢轰传，就容易造成內(nèi)存碎片和浪費驴党。
query_cache_size：查詢緩存大小 (注：QC存儲的最小單位是1024byte，所以如果你設(shè)定了一個不是1024的倍數(shù)的值绸吸，這個值會被四舍五入到最接近當(dāng)前值的等于1024的倍數(shù)的值鼻弧。)

query_cache_type：緩存類型设江，決定緩存什么樣的查詢，注意這個值不能隨便設(shè)置攘轩，必須設(shè)置為數(shù)字叉存，可選項目以及說明如下：
如果設(shè)置為0，那么可以說度帮，你的緩存根本就沒有用歼捏，相當(dāng)于禁用了。
如果設(shè)置為1笨篷，將會緩存所有的結(jié)果瞳秽，除非你的select語句使用SQL_NO_CACHE禁用了查詢緩存。
如果設(shè)置為2率翅，則只緩存在select語句中通過SQL_CACHE指定需要緩存的查詢练俐。

修改/etc/my.cnf,配置完后的部分文件如下：
query_cache_size=128M
query_cache_type=1
max_connect_errors ***
max_connect_errors是一個mysql中與安全有關(guān)的計數(shù)器值，它負(fù)責(zé)阻止過多嘗試失敗的客戶端以防止暴力破解密碼等情況冕臭，當(dāng)超過指定次數(shù)腺晾，mysql服務(wù)器將禁止host的連接請求，直到mysql服務(wù)器重啟或通過flush hosts命令清空此host的相關(guān)信息 max_connect_errors的值與性能并無太大關(guān)系辜贵。
修改/etc/my.cnf文件悯蝉，在[mysqld]下面添加如下內(nèi)容
max_connect_errors=2000
sort_buffer_size ***
（1）簡介：
每個需要進行排序的線程分配該大小的一個緩沖區(qū)。增加這值加速
ORDER BY
GROUP BY
distinct
union

（2）配置依據(jù)
Sort_Buffer_Size并不是越大越好托慨，由于是connection級的參數(shù)鼻由，過大的設(shè)置+高并發(fā)可能會耗盡系統(tǒng)內(nèi)存資源。
列如：500個連接將會消耗500*sort_buffer_size（2M）=1G內(nèi)存
（3）配置方法
修改/etc/my.cnf文件厚棵，在[mysqld]下面添加如下：
sort_buffer_size=1M
max_allowed_packet *****
（1）簡介：
mysql根據(jù)配置文件會限制蕉世，server接受的數(shù)據(jù)包大小。
（2）配置依據(jù)：
有時候大的插入和更新會受max_allowed_packet參數(shù)限制窟感，導(dǎo)致寫入或者更新失敗讨彼，更大值是1GB歉井，必須設(shè)置1024的倍數(shù)
（3）配置方法：
max_allowed_packet=32M
join_buffer_size ***
select a.name,b.name from a join b on a.id=b.id where xxxx
用于表間關(guān)聯(lián)緩存的大小柿祈，和sort_buffer_size一樣，該參數(shù)對應(yīng)的分配內(nèi)存也是每個連接獨享哩至。
盡量在SQL與方面進行優(yōu)化躏嚎，效果較為明顯。
優(yōu)化的方法：在on條件列加索引菩貌，至少應(yīng)當(dāng)是有MUL索引
thread_cache_size *****
(1)簡介
服務(wù)器線程緩存卢佣，這個值表示可以重新利用保存在緩存中線程的數(shù)量,當(dāng)斷開連接時,那么客戶端的線程將被放到緩存中以響應(yīng)下一個客戶而不是銷毀(前提是緩存數(shù)未達上限),如果線程重新被請求，那么請求將從緩存中讀取,如果緩存中是空的或者是新的請求箭阶，那么這個線程將被重新創(chuàng)建,如果有很多新的線程虚茶，增加這個值可以改善系統(tǒng)性能.
（2）配置依據(jù)
通過比較 Connections 和 Threads_created 狀態(tài)的變量戈鲁，可以看到這個變量的作用。
設(shè)置規(guī)則如下：1GB 內(nèi)存配置為8嘹叫，2GB配置為16婆殿，3GB配置為32，4GB或更高內(nèi)存罩扇，可配置更大婆芦。
服務(wù)器處理此客戶的線程將會緩存起來以響應(yīng)下一個客戶而不是銷毀(前提是緩存數(shù)未達上限)

試圖連接到MySQL(不管是否連接成功)的連接數(shù)
mysql> show status like 'threads_%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 8 |
| Threads_connected | 2 |
| Threads_created | 4783 |
| Threads_running | 1 |
+-------------------+-------+
4 rows in set (0.00 sec)

Threads_cached :代表當(dāng)前此時此刻線程緩存中有多少空閑線程。
Threads_connected:代表當(dāng)前已建立連接的數(shù)量喂饥，因為一個連接就需要一個線程消约，所以也可以看成當(dāng)前被使用的線程數(shù)。
Threads_created:代表從最近一次服務(wù)啟動员帮，已創(chuàng)建線程的數(shù)量或粮，如果發(fā)現(xiàn)Threads_created值過大的話，表明MySQL服務(wù)器一直在創(chuàng)建線程捞高，這也是比較耗cpu SYS資源被啼，可以適當(dāng)增加配置文件中thread_cache_size值。
Threads_running :代表當(dāng)前激活的（非睡眠狀態(tài)）線程數(shù)棠枉。并不是代表正在使用的線程數(shù)浓体，有時候連接已建立，但是連接處于sleep狀態(tài)辈讶。
(3)配置方法：
thread_cache_size=32

整理：
Threads_created ：一般在架構(gòu)設(shè)計階段命浴，會設(shè)置一個測試值，做壓力測試贱除。
結(jié)合zabbix監(jiān)控生闲，看一段時間內(nèi)此狀態(tài)的變化。
如果在一段時間內(nèi)月幌，Threads_created趨于平穩(wěn)碍讯，說明對應(yīng)參數(shù)設(shè)定是OK。
如果一直陡峭的增長扯躺，或者出現(xiàn)大量峰值捉兴，那么繼續(xù)增加此值的大小，在系統(tǒng)資源夠用的情況下（內(nèi)存）
innodb_buffer_pool_size *****
（1）簡介
對于InnoDB表來說录语，innodb_buffer_pool_size的作用就相當(dāng)于key_buffer_size對于MyISAM表的作用一樣倍啥。
（2）配置依據(jù)：
InnoDB使用該參數(shù)指定大小的內(nèi)存來緩沖數(shù)據(jù)和索引。
對于單獨的MySQL數(shù)據(jù)庫服務(wù)器澎埠，最大可以把該值設(shè)置成物理內(nèi)存的80%,一般我們建議不要超過物理內(nèi)存的70%虽缕。
（3）配置方法
innodb_buffer_pool_size=2048M
innodb_flush_log_at_trx_commit ******
（1）簡介
主要控制了innodb將log buffer中的數(shù)據(jù)寫入日志文件并flush磁盤的時間點，取值分別為0蒲稳、1氮趋、2三個伍派。
0，表示當(dāng)事務(wù)提交時剩胁，不做日志寫入操作拙已，而是每秒鐘將log buffer中的數(shù)據(jù)寫入日志文件并flush磁盤一次；
1摧冀，
每次事務(wù)的提交都會引起redo日志文件寫入倍踪、flush磁盤的操作，確保了事務(wù)的ACID索昂；
2建车，每次事務(wù)提交引起寫入日志文件的動作,但每秒鐘完成一次flush磁盤操作。

（2）配置依據(jù)
實際測試發(fā)現(xiàn)椒惨，該值對插入數(shù)據(jù)的速度影響非常大缤至，設(shè)置為2時插入10000條記錄只需要2秒，設(shè)置為0時只需要1秒康谆，而設(shè)置為1時則需要229秒领斥。因此，MySQL手冊也建議盡量將插入操作合并成一個事務(wù)沃暗，這樣可以大幅提高速度月洛。
根據(jù)MySQL官方文檔，在允許丟失最近部分事務(wù)的危險的前提下孽锥，可以把該值設(shè)為0或2嚼黔。
（3）配置方法
innodb_flush_log_at_trx_commit=1
雙1標(biāo)準(zhǔn)中的一個1
innodb_thread_concurrency ***
（1）簡介
此參數(shù)用來設(shè)置innodb線程的并發(fā)數(shù)量，默認(rèn)值為0表示不限制惜辑。
（2）配置依據(jù)
在官方doc上唬涧，對于innodb_thread_concurrency的使用，也給出了一些建議盛撑，如下：
如果一個工作負(fù)載中碎节，并發(fā)用戶線程的數(shù)量小于64，建議設(shè)置innodb_thread_concurrency=0抵卫；
如果工作負(fù)載一直較為嚴(yán)重甚至偶爾達到頂峰狮荔，建議先設(shè)置innodb_thread_concurrency=128，
并通過不斷的降低這個參數(shù)陌僵，96, 80, 64等等轴合，直到發(fā)現(xiàn)能夠提供最佳性能的線程數(shù)，
例如碗短，假設(shè)系統(tǒng)通常有40到50個用戶，但定期的數(shù)量增加至60题涨，70偎谁，甚至200总滩。你會發(fā)現(xiàn)，
性能在80個并發(fā)用戶設(shè)置時表現(xiàn)穩(wěn)定巡雨，如果高于這個數(shù)闰渔，性能反而下降。在這種情況下铐望，
建議設(shè)置innodb_thread_concurrency參數(shù)為80茵典，以避免影響性能猜欺。
如果你不希望InnoDB使用的虛擬CPU數(shù)量比用戶線程使用的虛擬CPU更多（比如20個虛擬CPU），
建議通過設(shè)置innodb_thread_concurrency 參數(shù)為這個值（也可能更低，這取決于性能體現(xiàn)）哥谷，
如果你的目標(biāo)是將MySQL與其他應(yīng)用隔離，你可以l考慮綁定mysqld進程到專有的虛擬CPU俄认。
但是需 要注意的是电媳，這種綁定，在myslqd進程一直不是很忙的情況下锻全，可能會導(dǎo)致非最優(yōu)的硬件使用率狂塘。在這種情況下，
你可能會設(shè)置mysqld進程綁定的虛擬 CPU鳄厌，允許其他應(yīng)用程序使用虛擬CPU的一部分或全部荞胡。
在某些情況下，最佳的innodb_thread_concurrency參數(shù)設(shè)置可以比虛擬CPU的數(shù)量小了嚎。
定期檢測和分析系統(tǒng)硝训，負(fù)載量、用戶數(shù)或者工作環(huán)境的改變可能都需要對innodb_thread_concurrency參數(shù)的設(shè)置進行調(diào)整新思。

128 -----> top cpu
設(shè)置標(biāo)準(zhǔn)：
1窖梁、當(dāng)前系統(tǒng)cpu使用情況，均不均勻
top

2夹囚、當(dāng)前的連接數(shù)纵刘，有沒有達到頂峰
show status like 'threads_%';
show processlist;
（3）配置方法：
innodb_thread_concurrency=8
方法:
1. 看top ,觀察每個cpu的各自的負(fù)載情況
2. 發(fā)現(xiàn)不平均,先設(shè)置參數(shù)為cpu個數(shù),然后不斷增加(一倍)這個數(shù)值
3. 一直觀察top狀態(tài),直到達到比較均勻時,說明已經(jīng)到位了.
innodb_log_buffer_size
此參數(shù)確定些日志文件所用的內(nèi)存大小，以M為單位荸哟。緩沖區(qū)更大能提高性能假哎，對于較大的事務(wù)，可以增大緩存大小鞍历。
innodb_log_buffer_size=128M

設(shè)定依據(jù)：
1舵抹、大事務(wù)： 存儲過程調(diào)用 CALL
2、多事務(wù)
innodb_log_file_size = 100M *****
設(shè)置 ib_logfile0 ib_logfile1
此參數(shù)確定數(shù)據(jù)日志文件的大小劣砍，以M為單位惧蛹，更大的設(shè)置可以提高性能.
innodb_log_file_size = 100M
innodb_log_files_in_group = 3 *****
為提高性能，MySQL可以以循環(huán)方式將日志文件寫到多個文件。推薦設(shè)置為3
read_buffer_size = 1M **
MySql讀入緩沖區(qū)大小香嗓。對表進行順序掃描的請求將分配一個讀入緩沖區(qū)迅腔，MySql會為它分配一段內(nèi)存緩沖區(qū)。如果對表的順序掃描請求非常頻繁靠娱，并且你認(rèn)為頻繁掃描進行得太慢沧烈，可以通過增加該變量值以及內(nèi)存緩沖區(qū)大小提高其性能。和 sort_buffer_size一樣像云，該參數(shù)對應(yīng)的分配內(nèi)存也是每個連接獨享
read_rnd_buffer_size = 1M **
MySql的隨機讀（查詢操作）緩沖區(qū)大小锌雀。當(dāng)按任意順序讀取行時(例如，按照排序順序)迅诬，將分配一個隨機讀緩存區(qū)腋逆。進行排序查詢時，MySql會首先掃描一遍該緩沖百框，以避免磁盤搜索闲礼，提高查詢速度，如果需要排序大量數(shù)據(jù)铐维，可適當(dāng)調(diào)高該值柬泽。但MySql會為每個客戶連接發(fā)放該緩沖空間，所以應(yīng)盡量適當(dāng)設(shè)置該值嫁蛇，以避免內(nèi)存開銷過大锨并。
注：順序讀是指根據(jù)索引的葉節(jié)點數(shù)據(jù)就能順序地讀取所需要的行數(shù)據(jù)。隨機讀是指一般需要根據(jù)輔助索引葉節(jié)點中的主鍵尋找實際行數(shù)據(jù)睬棚，而輔助索引和主鍵所在的數(shù)據(jù)段不同第煮，因此訪問方式是隨機的。
bulk_insert_buffer_size = 8M **
批量插入數(shù)據(jù)緩存大小抑党，可以有效提高插入效率包警，默認(rèn)為8M
tokuDB percona
myrocks
RocksDB
TiDB
MongoDB
binary log *****
log-bin=/data/mysql-bin
binlog_cache_size = 2M //為每個session 分配的內(nèi)存，在事務(wù)過程中用來存儲二進制日志的緩存, 提高記錄bin-log的效率底靠。沒有什么大事務(wù)害晦，dml也不是很頻繁的情況下可以設(shè)置小一點，如果事務(wù)大而且多暑中，dml操作也頻繁壹瘟，則可以適當(dāng)?shù)恼{(diào)大一點。前者建議是--1M鳄逾，后者建議是：即 2--4M
max_binlog_cache_size = 8M //表示的是binlog 能夠使用的最大cache 內(nèi)存大小
max_binlog_size= 512M //指定binlog日志文件的大小稻轨，如果當(dāng)前的日志大小達到max_binlog_size，還會自動創(chuàng)建新的二進制日志雕凹。你不能將該變量設(shè)置為大于1GB或小于4096字節(jié)殴俱。默認(rèn)值是1GB政冻。在導(dǎo)入大容量的sql文件時，建議關(guān)閉sql_log_bin粱挡，否則硬盤扛不住赠幕，而且建議定期做刪除俄精。
expire_logs_days = 7 //定義了mysql清除過期日志的時間询筏。
二進制日志自動刪除的天數(shù)。默認(rèn)值為0,表示“沒有自動刪除”竖慧。
log-bin=/data/mysql-bin
binlog_format=row
sync_binlog=1
雙1標(biāo)準(zhǔn)(基于安全的控制)：
sync_binlog=1 什么時候刷新binlog到磁盤嫌套，每次事務(wù)commit
innodb_flush_log_at_trx_commit=1
set sql_log_bin=0;
show status like 'com_%';
安全參數(shù) *****
Innodb_flush_method=(O_DIRECT, fsync)

1、fsync ：
（1）在數(shù)據(jù)頁需要持久化時圾旨，首先將數(shù)據(jù)寫入OS buffer中踱讨，然后由os決定什么時候?qū)懭氪疟P
（2）在redo buffuer需要持久化時，首先將數(shù)據(jù)寫入OS buffer中砍的，然后由os決定什么時候?qū)懭氪疟P
但痹筛，如果innodb_flush_log_at_trx_commit=1的話，日志還是直接每次commit直接寫入磁盤
2廓鞠、 Innodb_flush_method=O_DIRECT
（1）在數(shù)據(jù)頁需要持久化時帚稠，直接寫入磁盤
（2）在redo buffuer需要持久化時，首先將數(shù)據(jù)寫入OS buffer中床佳，然后由os決定什么時候?qū)懭氪疟P
但滋早，如果innodb_flush_log_at_trx_commit=1的話，日志還是直接每次commit直接寫入磁盤
最安全模式：
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
最高性能模式：
innodb_flush_log_at_trx_commit=0
innodb_flush_method=fsync

一般情況下砌们，我們更偏向于安全杆麸。
“雙一標(biāo)準(zhǔn)”
innodb_flush_log_at_trx_commit=1 ***************
sync_binlog=1 ***************
innodb_flush_method=O_DIRECT
偏向性能
innodb_flush_log_at_trx_commit=0
sync_binlog=0
innodb_flush_method=fsync

6. 參數(shù)優(yōu)化結(jié)果
   [mysqld]
   basedir=/data/mysql
   datadir=/data/mysql/data
   socket=/tmp/mysql.sock
   log-error=/var/log/mysql.log
   log_bin=/data/binlog/mysql-bin
   binlog_format=row
   skip-name-resolve
   server-id=52
   gtid-mode=on
   enforce-gtid-consistency=true
   log-slave-updates=1
   relay_log_purge=0
   max_connections=1024
   back_log=128
   wait_timeout=60
   interactive_timeout=7200
   key_buffer_size=16M
   query_cache_size=64M
   query_cache_type=1
   query_cache_limit=50M
   max_connect_errors=20
   sort_buffer_size=2M
   max_allowed_packet=32M
   join_buffer_size=2M
   thread_cache_size=200
   innodb_buffer_pool_size=1024M
   innodb_flush_log_at_trx_commit=1
   innodb_log_buffer_size=32M
   innodb_log_file_size=128M
   innodb_log_files_in_group=3
   binlog_cache_size=2M
   max_binlog_cache_size=8M
   max_binlog_size=512M
   expire_logs_days=7
   read_buffer_size=2M
   read_rnd_buffer_size=2M
   bulk_insert_buffer_size=8M
   [client]
   socket=/tmp/mysql.sock

再次壓力測試 ：
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from oldboy.t_100w where k2='FGCD'" engine=innodb --number-of-queries=200000 -uroot -p123 -verbose

7. 鎖的監(jiān)控及處理
   7.1 鎖等待模擬
   概念：
   Record Lock
   Next Lock
   GAP Lock
   X
   IX
   S
   IS
   概念：
   MDL : metadata lock
   元數(shù)據(jù),數(shù)據(jù)字典鎖?
   存儲位置(5.7):
   frm
   ibdata1
   mysql
   p_s
   sys
   information_schema
   什么時候觸發(fā)?
   DDL ----> MDL
   備份 ----> FTWRL
   手工觸發(fā) FTWRL

Table Lock
DDL 操作
lock table

Record Lock
update
delete
insert
select for update /lock in shared mode

GAP Lock
update t1 set salary=3000 where salary<3000;
1000
1999
1800
1500
insert into 1700

Next Lock

X
IX
S

IS
tx1:
USE oldboy
UPDATE t_100w SET k1='av' WHERE id=10;
tx2:

USE oldboy
UPDATE t_100w SET k1='az' WHERE id=10;
監(jiān)控鎖狀態(tài)
image.png

1. 看有沒有鎖等待

SHOW STATUS LIKE 'innodb_row_lock%';

2. 查看哪個事務(wù)在等待(被阻塞了)

USE information_schema
SELECT * FROM information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT';
trx_id : 事務(wù)ID號
trx_state : 當(dāng)前事務(wù)的狀態(tài)
trx_mysql_thread_id:連接層的,連接線程ID(SHOW PROCESSLIST ===>Id或trx_id )
trx_query : 當(dāng)前被阻塞的操作(一般是要丟給開發(fā)的)
7.3.查看鎖源,誰鎖的我!
SELECT * FROM sys.innodb_lock_waits; ## ====>被鎖的和鎖定它的之間關(guān)系

locked_table : 哪張表出現(xiàn)的等待
waiting_trx_id: 等待的事務(wù)(與上個視圖trx_id 對應(yīng))
waiting_pid : 等待的線程號(與上個視圖trx_mysql_thread_id)
blocking_trx_id : 鎖源的事務(wù)ID
blocking_pid : 鎖源的線程號
7.4. 找到鎖源的thread_id
SELECT * FROM performance_schema.threads WHERE processlist_id=15;
====> 41
7.5. 找到鎖源的SQL語句
-- 當(dāng)前在執(zhí)行的語句
SELECT * FROM performance_schema.events_statements_current WHERE thread_id=41;
-- 執(zhí)行語句的歷史
SELECT * FROM performance_schema.events_statements_history WHERE thread_id=41;

得出結(jié)果,丟給開發(fā)
表信息
被阻塞的
鎖源SQL
練習(xí):
一鍵獲得以上信息,請寫出具體的SQL語句

7.6 優(yōu)化項目:鎖的監(jiān)控及處理

1. 背景:
   硬件環(huán)境: DELL R720,E系列16核,48G MEM,SAS900G6,RAID10
   在例行巡檢時,發(fā)現(xiàn)9-11點時間段的CPU壓力非常高(80-90%)
2. 項目的職責(zé)
   2.1 通過top詳細排查,發(fā)現(xiàn)mysqld進程占比達到了700-800%
   2.2 其中有量的CPU是被用作的SYS和WAIT,us處于正常
   2.3 懷疑是MySQL 鎖 或者SQL語句出了問題
   2.4 經(jīng)過排查slowlog及鎖等待情況,發(fā)現(xiàn)有大量鎖等待及少量慢語句
   (1) pt-query-diagest 查看慢日志
   (2) 鎖等待有沒有?
   db03 [(none)]>show status like 'innodb_row_lock%';
   +-------------------------------+-------+
   | Variable_name | Value |
   +-------------------------------+-------+
   | Innodb_row_lock_current_waits | 0 |
   | Innodb_row_lock_time | 0 |
   | Innodb_row_lock_time_avg | 0 |
   | Innodb_row_lock_time_max | 0 |
   | Innodb_row_lock_waits | 0 |
   +-------------------------------+-------+
   情況一:
   有100多個current_waits,說明當(dāng)前很多鎖等待情況
   情況二:
   1000多個lock_waits,說明歷史上發(fā)生過的鎖等待很多
   2.5 查看那個事務(wù)在等待(被阻塞了)
   2.6 查看鎖源事務(wù)信息(誰鎖的我)
   2.7 找到鎖源的thread_id
   2.8 找到鎖源的SQL語句
3. 找到語句之后,和應(yīng)用開發(fā)人員進行協(xié)商
   (1)
   開發(fā)人員描述,此語句是事務(wù)掛起導(dǎo)致
   我們提出建議是臨時kill 會話,最終解決問題
   (2)
   開發(fā)人員查看后,發(fā)現(xiàn)是業(yè)務(wù)邏輯問題導(dǎo)致的死鎖,產(chǎn)生了大量鎖等待
   臨時解決方案,將阻塞事務(wù)的會話kill掉.
   最終解決方案,修改代碼中的業(yè)務(wù)邏輯
   項目結(jié)果:
   經(jīng)過排查處理,鎖等待的個數(shù)減少80%.解決了CPU持續(xù)峰值的問題.
   鎖監(jiān)控設(shè)計到的命令:

show status like 'innodb_rows_lock%'
select * from information_schema.innodb_trx;
select * from sys.innodb_lock_waits;
select * from performance_schema.threads;
select * from performance_schema.events_statements_current;
select * from performance_schema.events_statements_history;
7.7 死鎖監(jiān)控
show engine innodb status\G
show variables like '%deadlock%';
vim /etc/my.cnf
innodb_print_all_deadlocks = 1
主從延時?
主庫原因:
(1) binlog寫入不及時.
sync_binlog=1?
IO太慢. ---> SSD
(2) 傳統(tǒng)(無GTID)
傳輸日志binlog是串行工作的.
5.6 以后加入了GTID,并行傳輸.
(3) 其他原因
網(wǎng)絡(luò)慢
主庫壓力大
從庫太多
主從配置不一致
主從參數(shù)
主從版本
從庫原因:
SQL線程串行工作.
5.7 中加入Logical_clock 邏輯時鐘,可以實現(xiàn)SQL線程并行回放relaylog

8. 主從優(yōu)化：
   5.7 從庫多線程MTS
   基本要求:

5.7以上的版本(忘記小版本)
必須開啟GTID
binlog必須是row模式

gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

5.7 :
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=8
cpu核心數(shù)作為標(biāo)準(zhǔn)

CHANGE MASTER TO
MASTER_HOST='10.0.0.128',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_AUTO_POSITION=1;
start slave;
擴展PT工具的應(yīng)用:

1. pt工具安裝
   [root@master ~]# yum install -y percona-toolkit-3.1.0-2.el7.x86_64.rpm

2. 常用工具使用介紹
   2.1 pt-archiver 歸檔表

重要參數(shù)

--limit 100 每次取100行數(shù)據(jù)用pt-archive處理
--txn-size 100 設(shè)置100行為一個事務(wù)提交一次，
--where 'id<3000' 設(shè)置操作條件
--progress 5000 每處理5000行輸出一次處理信息
--statistics 輸出執(zhí)行過程及最后的操作統(tǒng)計浪感。（只要不加上--quiet昔头，默認(rèn)情況下pt- archive都會輸出執(zhí)行過程的）
--charset=UTF8 指定字符集為UTF8—這個最后加上不然可能出現(xiàn)亂碼。
--bulk-delete 批量刪除source上的舊數(shù)據(jù)(例如每次1000行的批量刪除操作)

注意: 需要歸檔表中至少有一個索引,做好是where條件列有索引

使用案例：
1.歸檔到數(shù)據(jù)庫
db01 [test]>create table test1 like t100w;
pt-archiver --source h=10.0.0.51,D=test,t=t100w,u=oldguo,p=123 --dest h=10.0.0.51,D=test,t=test1,u=oldguo,p=123 --where 'id<10000' --no-check-charset --no-delete --limit=1000 --commit-each --progress 1000 --statistics
2.只清理數(shù)據(jù)
pt-archiver --source h=10.0.0.51,D=test,t=t100w,u=oldguo,p=123 --where 'id<10000' --purge --limit=1 --no-check-charset
3.只把數(shù)據(jù)導(dǎo)出到外部文件影兽，但是不刪除源表里的數(shù)據(jù)
pt-archiver --source h=10.0.0.11,D=world,t=city,u=root,p=123 --where '1=1' --no-check-charset --no-delete --file="/tmp/archiver.dat"
2.2 pt-osc
pt-osc工作流程：
1揭斧、檢查更改表是否有主鍵或唯一索引，是否有觸發(fā)器
2赢笨、檢查修改表的表結(jié)構(gòu)未蝌，創(chuàng)建一個臨時表，在新表上執(zhí)行ALTER TABLE語句
create table bak like t1;
alter table bak add telnum char(11) not null;
3茧妒、在源表上創(chuàng)建三個觸發(fā)器分別對于INSERT UPDATE DELETE操作
create trigger
a
b
c
4萧吠、從源表拷貝數(shù)據(jù)到臨時表，在拷貝過程中桐筏，對源表的更新操作會寫入到新建表中
insert into bak select * from t1
5纸型、將臨時表和源表rename（需要元數(shù)據(jù)修改鎖，需要短時間鎖表）
6、刪除源表和觸發(fā)器狰腌，完成表結(jié)構(gòu)的修改除破。

pt-osc工具限制
1、源表必須有主鍵或唯一索引琼腔，如果沒有工具將停止工作
2瑰枫、如果線上的復(fù)制環(huán)境過濾器操作過于復(fù)雜，工具將無法工作
3丹莲、如果開啟復(fù)制延遲檢查光坝，但主從延遲時，工具將暫停數(shù)據(jù)拷貝工作
4甥材、如果開啟主服務(wù)器負(fù)載檢查盯另，但主服務(wù)器負(fù)載較高時，工具將暫停操作
5洲赵、當(dāng)表使用外鍵時鸳惯，如果未使用--alter-foreign-keys-method參數(shù)，工具將無法執(zhí)行
6叠萍、只支持Innodb存儲引擎表芝发，且要求服務(wù)器上有該表1倍以上的空閑空間。

pt-osc之a(chǎn)lter語句限制
1俭令、不需要包含alter table關(guān)鍵字后德，可以包含多個修改操作，使用逗號分開抄腔，如"drop clolumn c1, add column c2 int"
2瓢湃、不支持rename語句來對表進行重命名操作
3、不支持對索引進行重命名操作
4赫蛇、如果刪除外鍵绵患，需要對外鍵名加下劃線，如刪除外鍵fk_uid, 修改語句為"DROP FOREIGN KEY _fk_uid"

pt-osc之命令模板

--execute表示執(zhí)行

--dry-run表示只進行模擬測試

表名只能使用參數(shù)t來設(shè)置悟耘，沒有長參數(shù)

pt-online-schema-change
--host="127.0.0.1"
--port=3358
--user="root"
--password="root@root"
--charset="utf8"
--max-lag=10
--check-salve-lag='xxx.xxx.xxx.xxx'
--recursion-method="hosts"
--check-interval=2
--database="testdb1"
t="tb001"
--alter="add column c4 int"
--execute

例子：
pt-online-schema-change --user=oldguo --password=123 --host=10.0.0.51 --alter "add column age int default 0" D=test,t=t1 --print --execute

pt-online-schema-change --user=oldguo --password=123 --host=10.0.0.51 --alter "add index idx(name)" D=test,t=t1 --print --execute
2.3 pt-table-checksum
2.3.1 創(chuàng)建數(shù)據(jù)庫
Create database pt CHARACTER SET utf8;
創(chuàng)建用戶checksum并授權(quán)
GRANT ALL ON . TO 'checksum'@'10.0.0.%' IDENTIFIED BY 'checksum';
flush privileges;
2.3.2 參數(shù):
--[no]check-replication-filters：是否檢查復(fù)制的過濾器落蝙，默認(rèn)是yes，建議啟用不檢查模式暂幼。
--databases | -d：指定需要被檢查的數(shù)據(jù)庫筏勒，多個庫之間可以用逗號分隔。
--[no]check-binlog-format：是否檢查binlog文件的格式旺嬉，默認(rèn)值yes管行。建議開啟不檢查。因為在默認(rèn)的row格式下會出錯邪媳。
--replicate`：把checksum的信息寫入到指定表中捐顷。
--replicate-check-only：只顯示不同步信息

pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=pt.checksums --create-replicate-table --databases=test --tables=t1 h=10.0.0.51,u=checksum,p=checksum,P=3306

!/bin/bash

date >> /root/db/checksum.log
pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=120 --databases test --tables t1 -u'checksum' -p'checksum' -h'10.0.0.51' >> /tmp/checksum.log
date >> /root/db/checksum.log
2.4 pt-table-sync
主要參數(shù)介紹
--replicate ：指定通過pt-table-checksum得到的表.
--databases : 指定執(zhí)行同步的數(shù)據(jù)庫荡陷。
--tables ：指定執(zhí)行同步的表，多個用逗號隔開迅涮。
--sync-to-master ：指定一個DSN废赞，即從的IP，他會通過show processlist或show slave status 去自動的找主叮姑。
h= ：服務(wù)器地址唉地，命令里有2個ip，第一次出現(xiàn)的是Master的地址戏溺，第2次是Slave的地址渣蜗。
u= ：帳號屠尊。
p= ：密碼旷祸。
--print ：打印，但不執(zhí)行命令讼昆。
--execute ：執(zhí)行命令托享。

pt-table-sync --replicate=pt.checksums --databases test --tables t1 h=10.0.0.51,u=checksum,p=checksum,P=3306 h=10.0.0.52,u=checksum,p=checksum,P=3306 --print

pt-table-sync --replicate=pt.checksums --databases test --tables t1 h=10.0.0.51,u=checksum,p=checksum,P=3306 h=10.0.0.52,u=checksum,p=checksum,P=3306 --execute
2.5 mysql死鎖監(jiān)測
pt-deadlock-logger h='127.0.0.1' --user=root --password=123456
2.6 主鍵沖突檢查
pt-duplicate-key-checker --database=world h='127.0.0.1' --user=root --password=123456
2.7 pt-kill 語句
常用參數(shù)說明
--daemonize 放在后臺以守護進程的形式運行；
--interval 多久運行一次浸赫，單位可以是s,m,h闰围，d等默認(rèn)是s –不加這個默認(rèn)是5秒
--victims 默認(rèn)是oldest,只殺最古老的查詢。這是防止被查殺是不是真的長時間運行的查詢既峡，他們只是長期等待 這種種匹配按時間查詢羡榴，殺死一個時間最高值。
--all 殺掉所有滿足的線程
--kill-query 只殺掉連接執(zhí)行的語句运敢，但是線程不會被終止
--print 打印滿足條件的語句
--busy-time 批次查詢已運行的時間超過這個時間的線程校仑；
--idle-time 殺掉sleep 空閑了多少時間的連接線程，必須在--match-command sleep時才有效—也就是匹配使用 -- –match-command 匹配相關(guān)的語句传惠。
----ignore-command 忽略相關(guān)的匹配迄沫。 這兩個搭配使用一定是ignore-commandd在前 match-command在后，
--match-db cdelzone 匹配哪個庫
command有：Query卦方、Sleep羊瘩、Binlog Dump、Connect盼砍、Delayed insert尘吗、Execute、Fetch浇坐、Init DB睬捶、Kill、Prepare吗跋、Processlist侧戴、Quit宁昭、Reset stmt、Table Dump

例子：

殺掉空閑鏈接sleep 5秒的 SQL 并把日志放到/home/pt-kill.log文件中

/usr/bin/pt-kill --user=用戶名 --password=密碼 --match-command Sleep --idle-time 5 --victim all --interval 5 --kill --daemonize -S /tmp/mysql.sock --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &

查詢SELECT 超過1分鐘

/usr/bin/pt-kill --user=用戶名 --password=密碼 --busy-time 60 --match-info "SELECT|select" --victim all --interval 5 --kill --daemonize -S -S /tmp/mysql.sock --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &

Kill掉 select IFNULl.*語句開頭的SQL

pt-kill --user=用戶名 --password=密碼 --victims all --busy-time=0 --match-info="select IFNULl.*" --interval 1 -S /tmp/mysqld.sock --kill --daemonize --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &

kill掉state Locked

/usr/bin/pt-kill --user=用戶名 --password=密碼 --victims all --match-state='Locked' --victim all --interval 5 --kill --daemonize -S /tmp/mysqld.sock --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &

kill掉 a庫酗宋，web為10.0.0.11的鏈接

pt-kill --user=用戶名 --password=密碼 --victims all --match-db='a' --match-host='10.0.0.11' --kill --daemonize --interval 10 -S /tmp/mysqld.sock --pid=/tmp/ptkill.pid --print-log=/tmp/pt-kill.log &

指定哪個用戶kill

pt-kill --user=用戶名 --password=密碼 --victims all --match-user='root' --kill --daemonize --interval 10 -S /home/zb/data/my6006/socket/mysqld.sock --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log &

查詢SELECT 超過1分鐘路

pt-kill --user=用戶名 --password=密碼 --busy-time 60 --match-info "SELECT|select" --victim all --interval 5 --kill --daemonize -S /tmp/mysqld.sock --pid=/tmp/ptkill.pid --print --log=/tmp/pt-kill.log &

kill掉 command query | Execute

pt-kill --user=用戶名 --password=密碼 --victims all --match-command= "query|Execute" --interval 5 --kill --daemonize -S /tmp/mysqld.sock --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log &
2.8 其他

pt-find ---找出幾天之前建立的表
pt-slave-restart -----主從報錯积仗，跳過報錯
pt-summary ---整個系統(tǒng)的的概述
pt-mysql-summary ---MySQL的表述，包括配置文件的描述
pt-duplicate-key-checker ---檢查數(shù)據(jù)庫重復(fù)索引