mysql管理之道筆記

https://blog.hbis.fr/2013/01/31/zabbix-mysql_partitioning/

壓測收集統計資訊指令碼
#!/bin/sh

INTERVAL=5
PREFIX=$INTERVAL-sec-status
RUNFILE=/tmp/running
mysql -e 'show global variables' >> mysql-variables
while test -e $RUNFILE; do
   file=$(date +%F_%I)
   sleep=$(date +%s.%N | awk "{print $INTERVAL - (\$1 % $INTERVAL)}")
   sleep $sleep
   ts="$(date +"TS %s.%N %F %T")"
mysqladmin  -uroot -h 127.0.0.1 -P 3307 -p ext -i1 |awk '/Queries/{q=$4-qp;qp=$4}/Threads_connected/{tc=$4}/Threads_running/{printf "%5d %5d %5d\n", q,tc,$4}'
修改預設值
alter table tab_name modify column rental_duration tinyint(3) not null default 5;
alter table tab_name alter column rental_duration set default 5;


充分利用CPU多核處理能力
innodb_read_io_threads
innodb_read_io_threads

innodb_io_capacity   磁碟配置
    200               單盤SAS/SATA
    2000              SAS*12 RAID10
    5000               SSD
   50000              FUSION-IO

增加自適應重新整理髒頁功能
innodb_adaptive_flushing

如果你沒有全表掃描，發現youngs/s的值很小，那麼就應該增大innodb_old_blocks_pct或者減少innodb_old_blocks_time,
如果你進行了全表掃描，發現non-younngs/s值很小，那麼就應該增大innodb_old_blocks_time;

innodb_buffer_pool_instances>1

show engine innodb status\G:《SEMAPHORES》RW-shared OS wait(不多）,並且《INSERT BUFFER AND ADAPTIVE HASH INDEX》hash searchs/s佔比很多，建議開啟。

組提交工作模式只支援在sync_binlog=0的情況下，同樣，innodb_support_xa也必須等於0，其目的是保障innodb儲存引擎redolog事務日誌與binlog日誌的順序一致性；

innodb_purge_threads 引數不支援動態修改，需要新增my.cnf裡修改，並且要重啟才能生效，當設定為1時，需要結合innodb_purge_batch_size引數來使用，預設值是20；一般預設即可。

innodb_stats_on_metadata引數的作用是：每當查詢information_schema後設資料庫裡的表時，innodb還會隨機提取資料，更新information_shema.statistics表，並返回查詢結果，
當表很大時，耗費的時間很長，很多經常不訪問的資料也會進入innodb_buffer_pool緩衝池，並且analyze table和show table status語句也會造成隨機提取資料；
set global innodb_stats_on_metadate=off;

relay_log_recovery引數，這個引數的作用是：當slave從當機後，假如relay-log損壞了，導致一部分中繼日誌還沒有處理，則自動放棄所有未執行的relay-log，
並且重新從master獲取日誌，這樣就保證了relay-log的完整性；預設關閉，建議開啟；

建議開啟innodb的嚴格檢查模式
set global innodb_strict_mode=1;

innodb_file_format:預設antelope,建議Barracude，如果需要壓縮就必須設定成barracude。

如果有很多全表掃描innodb_old_blocks_time要設定大些；

delete a,b from user a join user2 b on a.id=b.id;

mysql升級
1.my.cnf引數註釋修改
2.innodb_fast_shutdown=0
3.mysqladmin shutdown
4.mysqld --defaults-file=/etc/my.cnf --skip-grant-tables &
5.$BASEDIR/bin/mysql_upgrade
6.mysqladmin shutdown
7.正常啟動

半同步複製
$BASEDIR/lib/plugin：半同步複製的安裝目錄
install plugin rpl_semi_sync_master soname 'semisync_master.so';
set global rpl_semi_sync_master_enabled=on

install plugin rpl_semi_sync_slave soname 'semisync_master.so';
set global rpl_semi_sync_slave_enabled=on

my.cnf
rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1

預熱buffer_pool緩衝
innodb_buffer_pool_dump_at_shutdown=1  :關閉時將熱資料匯入到ib_buffer_pool檔案中
innodb_buffer_pool_dump_now=1  手工把預熱資料匯入到本地
innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_load_now=1  手工把熱資料載入到記憶體

透過binlog日誌統計dml語句（row）
mysqlbinlog --no-defaults --base64-output=decode-rows -v -v mysql-bin.000011 |awk '/###/{if($0~/UPDATE|INSERT|DELETE/)count[$2" "$NF]++}END{for(i in count)print i,"\t",count[i]}'|column -t|sort -k3nr|more

binlog_format=MIXED
在以下情況下
1.NDB引擎，表的DML操作增、刪、改會以ROW格式記錄
2.SQL語句裡包含了UUID()函式
3.自增長欄位被更新了
4.包含了INSERT DELAYED語句
5.使用了使用者定義函式
6.使用了臨時表

set global transaction isolation level read committed;

如果是read-committed隔離級別，binlog_format=mixed和binlog_format=row的效果是一樣的；
mysql故障切換之事件排程器注意事項
事件只能在master上觸發，在slave上不能觸發，如果slave上觸發了，同步複製就會壞掉。當主從複製故障切換之後，事件的狀態還維持著slaveSIDE_disabled.
alter event 'event_name' enables;

人工誤刪除Innodb ibdata資料檔案，如何恢復
1.ps -ef | grep mysql 取得程式號pid
2.ll /proc/$(pid)/fd | grep -E "ib_|ibdata" 檢視要恢復的檔案
3.flush tables with read lock 進位制資料寫入
4.set global innodb_max_dirty_pages_pct=0 將mysql髒資料交給系統記憶體
5.show engine innodb status\G
    TRANSACTIONS
    ------------
    Trx id counter 656A6
    Purge done for trx's n:o < 656A6 undo n:o < 0
    確保後臺purge程式把undo log全部清除掉，事務ID要一致

    INSERT BUFFER AND ADAPTIVE HASH INDEX
    -------------------------------------
    Ibuf: size 1
    insert buffer合併插入快取等於1

    LOG
    ---
    Log sequence number 507775012
    Log flushed up to   507775012
    Last checkpoint at  507775012
    確保這3個值保持不再變回  
 
    BUFFER POOL AND MEMORY
    ----------------------
    Modified db pages  0
    確保髒資料等於0
6.複製檔案
7.賦權
8.sync；sync；sync
9./etc/init.d/mysql restart