MySQL 複製參數詳解

標籤：產生   cat   5.5   操作   offset   本地   ica   max   說明   

log-bin　　二進位日誌

server-id 早起版本必須添加  1-pow(2,32)-1 推薦使用 連接埠號碼+ip最後一位  5.6後可以動態修改

server-uuid （5.6以後） 預設存在 $datadir/auto.cnf 下         5.6後可以動態修改

log-bin-index  包含二進位記錄檔名

binlog-format  statement row mixed 三種  可動態修改  

binlog_cache_size=1M - 2M  

max_binlog_size 預設是1G  產生間隔在2分鐘以上 推薦128M 或者256M  

限制單個binlog大小 可以動態修改

sync_binlog=n

binlog在同步完之後,commit以後是否要刷到磁碟上 預設更高的效能設定成0 由作業系統控制是否刷到磁碟上

如果是教育系統,為了安全推薦設定成1

 

多少個SQL之後,調用fdatasync()函數重新整理 binlog 到disk

fsync() / fdatasync() 區別是什麼 

fsync() 是完全重新整理到磁碟,fdatasync 只重新整理資料 不重新整理metadata 可動態修改

expire_logs_days=n  推薦儲存7天

n天后自動刪除binlog

可動態修改

log_bin_trust_function_creators

開啟binlog時,是否允許建立儲存程式(除非有super許可權,或指定deterministic,reads sql data,nosql) 

可以動態修改

log_warnings 

如果這個值設定大於 1，會在錯誤記錄檔裡看到異常斷開的串連(Aborted connection)或者是被拒絕的請求

 

特殊說明

MySQL5.6.5，MySQL5.7.5以後,binlogv2 格式 gtid binlog_checksum

MySQL5.5 ——> MySQL5.6 沒問題

如果以後有  MySQL5.6 --------> MySQL5.5 同步的需求    發現同步不了報錯,

需要找到兩個參數

binlog_checksum  預設為CRC32  由於5.5沒有此參數所以需要設定為 none

log_bin_use_v1_row_events  預設為OFF 設定為 ON

這樣可以避免升級過程中的一些問題(之後建議將參數還原回原來的設定)

binlog_error_action 當不能寫binlog時,可以報出來錯,預設沒開啟：ignore_error

開啟可以使用 :abort_server

 

5.6引入 gtid

bin_log_image   三種模式 full  minimal  noblob 預設為full

binlog_rows_query_log_events 審計功能 

基於MySQL5.6 GTID的Crash Recovery是有Mysql-5.6.21 支援到 5.6.23 穩定

傳統複製裡面也是可以支援

master_info_repository  |file|   修改為 table

relay_log_info_repository |file|   修改為table

binlog_gtid_recovery_simpliefied  【5.6.23--5.6.21 simplified_binlog_gtid_recovery 】 這個功能剛出來不久

在掛掉時,可以找到自己同步到那裡 完成的同步GTID 上接著同步,重新拉一下日誌

如果沒啟用這個特性,會從binlog 全部搜尋一下,如果日誌很多,需要佔用的時間比較多

relay-master-log-file

exec-master-log-pos

 

master 其他參數

auto_increment_increment  位移量

auto_increment_offset　　 初始值

 

特別聲明:所有的過濾規則都不建議在主庫設定

binlog-do-db

binlog記錄db,除此外，其他db上的binlog都不記錄，存在風險

binlog-ignore-db

和上面規則相反

max_binlog_cache_size

binlog最大的cachesize 有大SQL寫入時需要用到，或者大資料Load DATA 時

binlog_stmt_cache_size

用於cache那些事物中非事物表產生的SQL語句。如果經常在事物中涉及到非事物表,需要加大該參數

max_binlog_stmt_cache_size

用於cache那些事物中非事務表產生的SQL語句 最大cache

binlog_direct_non_transactional_updates 

事物中既有事務表,也有非事務表的情況下，發生資料變更時,非事物表會直接寫binlog,而不是也和事務表一樣在buffer中

這個參數隻影響SBR 模式 RBR MBR 都不影響

 

gtid相關參數

gtid_mode  on 開啟gitd 的參數

enforce_gtid_consistency  

gtid_next 跳過錯誤的參數

gtid_purged

用於指定那個事務從 binlog忽略了 ,reset master 這個值會被清空

gtid_executed 執行到的gtid 號及 所有執行過的 gtid 號

和executed_gtid_set（show master status,show slave status）輸出的這列意思是一樣的

reset master 這個值會被清空

總結:

主庫開啟最少參數(必須參數)

server-id

server-uuid

log-bin

gtid-mode

enforce-gtid-consistency

其他參數

max_binlog_size

expire_logs_days

binlog_format  （row格式）

salve 上的參數

 server-id 

 server-uuid

 relay-log  io thread 讀取過來存到本地的日誌

relay-log-index 

read-only  設定從庫為唯讀 但是對super許可權不起作用

配置參數 (ignore/do 規則) 

server-id

relay-log

relaylog 檔案名稱首碼 可以是全路徑

不可動態修改

relay-log-index

relay-log索引檔案首碼名 和 relay-log一樣 也可以是fullpath

不可以動態修改

read-only

除非有super許可權 否則無法修改資料,不過不影響自主建立暫存資料表（temporary,table）analyze table optimize table 也不受影響

可以動態修改

 

slave其他參數

log-slow-slave-statements  slave複製產生的slow log 是否也要記錄

log-slave-updates   將master傳輸過來的變更操作,再次記錄成本地的binlog,用於二次複製,當作中繼發佈點　　　　　　　　 

max_relay_log_size 建議不用修改  限制relay log的大小

relay-log-info-file

relay-log-purge =1 應用完後刪除 應用完了 是否立即刪除

relay-log-recovery = 1 crash recover 的過程  slave崩潰或正常重啟時,未應用完的relay log會被刪掉,重新從master 請求binlog  再次產生relay log

replicate-same-server-id

skip-slave-start  在資料庫起來後不要自動啟用同步

slave_load_tmpdir

slave_transaction_retries 

start_parallel_workers 預設是關閉的 5.6引入基於gtid庫層級的並行賦值

master-info-file/relay-log-info-file  將來慢慢替換成 下面兩個

master_info_repository   file    -->  table 

relay_log_info_repository  file  ---> table

記錄relay-log 最新資訊

sync_master_info =n   保持預設

多少個事物/事件後調用fdatasync() 重新整理master.info 檔案

relay_log_purge relay

 

slave 的過濾參數

replicate-do-table 

replicate-ignore-table 

replicate-rewrite-db    把db1 的操作改到 db2 中

replicate-wild-do-table   萬用字元的方式制定哪些表被複製哪些表不被複製

replicate-wild-ignore-table

replicate-do-db

replicate-ignore-db

slave_net_timeout

slave_skip_errors

sql_slave_skip_counter 

、過濾條件可以線上更改 5.7.3以後

slave_net_timeout  如果主從複製 線程都為yes 但是資料過不來 建議調到 20 -30  預設為60

slave_skip_errors 

複製過程中忽略某些錯誤  

1032  找不到記錄

1053 指server是在關閉過程中

1062  重複主鍵

1050  表已經存在了

1051 表找不到

1054 列對不上

1146  表不存在

sql_slave_skip_counter 

忽略多少個複製事件，遇到個別錯誤(主鍵衝突,記錄不存在等等) 時,可以忽略這些事件 繼續複製過程

基於binlog+position 錯誤處理

stop slave

set global sql_slave_skip_counter=n

start slave

show slave status\G;

一搬一次 只忽略一個事件,除非很肯定,否則不要設定大於1 

 

其中  為了使用crash-safe-replication 功能,必須使用

relay-log-info-repository =table

relay-log-recovery

使用支援事務的引擎

使用crash-safe replication 功能：在mysql(slave) 故障掛起來後

在設定了relay-log-recovery=1 時 會清理掉已經存在的relay log 從 relay-log-info-repository 裡讀到的

同步位置,重新請求一份relay-log 實際上是做了一次：把read_master_log_pos 設定為Exec_mater_log_pos ,

master_log_file 設定成了 relay_master_log_file

note:在slave 上如果給指定了一個日誌的不正確的binlog position start slave 是什麼也不乾的

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  

 

MySQL 複製參數詳解