MySQL 主從的一些基礎知識

本篇文章給大家分享的是有關MySQL 主從的一些基礎知識，小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

MySQL復制原理

1. 主庫修改時會把數據變更作為event記錄在binlog中,主庫上的sync_binlog參數控制binlog日志刷新到磁盤.

2. 主庫推送binlog 中的事件到從庫的中繼日志relay log，之后從庫根據中繼日志relay log重做數據變更操作。通過邏輯復制達到主從同步。

MySQL通過3個現成來完成主從數據庫之間的復制:

1.binlog dump 跑在主庫上，負責讀取數據庫事件并發送給I/O線程

2.I/O線程 跑在從庫上，當從庫start slave時 創建I/O線程 鏈接到master 將主庫的事件寫入到relaylog

3.SQL線程跑在從庫上，負責讀取relay log并更新從庫。

4.Master.inf and relay-log.info 用來保存復制的進度。

三種復制方法, MIX,STATEMENT,ROW

1.  statement

基于語句的復制,可能會因為存儲過程或觸發器,時間函數導致主備不一致，數據量小。

2. row

行級數據復制,缺點是數據量較大，但是不會因為觸發器等外在條件影響數據

3.   Mix

默認采用statement模式 如果語句中包含更新時間，用戶相關的函數等

Mysqlbinlog –vv logfilename –base64-output=DECODE-ROWS –start-pos=number 才能解析row模式的內容。

雙主復制架構：

由master1 作為讀寫庫，master2 作為只讀庫。

同時雙向作為對方的slave。

當維護時

1.       先停掉master1上的slave。免得當master2 做維護時影響到master1.

2.       停掉master2 上的slave。 開始維護操作，比如創建索引。

3.       打開master2上的slave 讓master2 與master1 同步。

4.       將讀寫操作轉移到master2 ，確保master1 上無寫入操作

5.       將master1 的slave打開， 讓其與salve2 同步完成。

搭建mysql復制：

1.       建立一個用于replication的賬號：

Create replac slave on *.* TO ‘repl’@’192.168.7.200’ identified by ‘1234test’;

2.       修改my.cnf 開啟binlog 并且設置server-id的值
log-bin = xxxxx.log
server-id=1

3.       鎖住所有的表，lock tables with read lock

4.       檢查當前日志偏移量 show master status

5.       備份或者通過直接shutdown數據庫 拷貝數據文件的方式到slave庫。

6.       Unlock tables;

7.       修改slave的my.cnf  server-id=2

8.       使用延遲復制開啟salve ./bin/mysql_safe –skip-slave-start&

9.       對數據庫服務做響應設置
mysql>change master to master_host=’’,master_user=’’,master_password=’’,master_logfile=’’,master_log_pos=’’;

10.   從庫上輸入:start slave;

11.   show process\G檢查slave進程

12.   show variables like ‘%sync_binlog%’ 0的表示mysql不控制binlog的刷新，由文件系統控制。

13.   最后注意的幾點就是MHA架構可以通過自動抽取缺失的log進行catchup。也可以通過mysqlbinlog工具來抽取之前主庫為宕機時未抽取的log 傳送到備庫上去。

半同步復制:

在mysql 5.5之前，由于主備庫之間都處于異步復制的狀態，如果主庫在commit了之后而崩壞，此時binlog尚未傳到slave的庫上。則會丟失數

而5.5之后mysql推出了半同步復制狀態。在默認狀態下mysql需要保證有任意一臺備庫的relaylog 接收到的更改之后才會確認commit已經成功。如果中途斷網的話 則會再自動切換回半同步復制模式。

安裝:
1.首先需要確認mysql是否可以使用該功能,select @@have_dynamic_loading =>YES

2到$MYSQL_HOME/lib/plugin/下找到主庫/從庫插件semisync_master/slave.so

3.主備庫上安裝該插件 install plugin rpl_semi_sync_master/salve soname ‘semisync_master/slave.so’

4.select * from mysql.plugin

5.在主備庫上設置全局參數打開半同步set global rpl_semi_sync_master_enabled=1;

         Set global rpl_semi_sync_master_timeout=3000;

         從庫上
         set global rpl_semi_sync_slave=1;

6如果已經啟動了異步復制 需要停止一下io_thread. Stop slave io_thread; start slave io_thread;

Show status like ‘%semi_sync%’

需要注意的幾個結果有:

rpl_semi_syc_master_status   ON 代表打開;

rpl_semi_syc_master_yes/no_tx 表示 同步或非同步的transaction次數

rpl_semi_syc_master_timeout 標示等待多久會判斷slave超時。

如果從庫斷開后重新連接上了，會自動轉回半同步狀態。

Mysql replication 一些有用的參數：

1．  Log-slave-updates 從庫是否需要記binlog  這個對于從庫傳從庫是比較有幫助的

2．  Master-connect-retry 在從庫和主庫失去連接時的重試間隔 默認60秒

3．  ./bin/Mysqld_safe –read-only 讓以避免超級用戶以外的人修改從庫的數據

日常維護命令:

1.       查看從庫狀態 show slave status.比較關心的2狀態一個是SLAVE_IO_Running和Slave_SQL_Running.

2.       有時候主備庫差距越來越大，可以在空閑的時候使用flush tables with read lock.
show master status 查看binlog的position。接下來使用select master_pos_wait(‘logiflename’,’log_pos’); 這個語句會阻塞直到從庫同步到該點。 然后再解鎖表。

3.       從庫出錯：

a)         如果需要跳過某些導致replication失敗的語句可以使用set global SQL_SLAVE_SKIP_COUNTER=N. N=1|2. 當主庫沒有自增長時使用1 自增長用2.

b)         Log event entry exceeded max_allowed_packet 一般是因為有長字符串或blob無法傳輸。 此時需要增加主庫上該參數的大小。

c)         多主復制時自增長變量沖突的話 在雙主上需要設置auto_increment_increment=2,auto_increment_offset=1;(1,3,5,7….) auto_increment_increment=2,auto_increment_offset=0;(2,4,6,8….)

一些簡單的提高從庫性能的架構

1.通過master1 傳給master2  master2使用blackhole 引擎開始slave-log-update。
  另外的slave 通過 replace-do-db的方式只復制部分數據庫。這種方法的缺點是一旦master1宕機之后會比較麻煩需要提前商量好異常處理方案。

2.mysql 5.6之后有多線程復制，允許從庫并行更新。Slave_parallel_works=2. Percona分支的mysql還可以用淘寶丁奇的mysql transfer來實現從主庫多線程應用中繼日志來解決。

Http://dinglin.iteye.com/blog/188640.

切換主從：

1.先確保所有從庫都執行了relay log中的全部更新。 在每個從庫上執行 stop slave io_thread。 然后用show process \G 直到看到 state: has read all relay log之后標志更新全都執行完畢。

2. S1上Stop slave.打開log-bin模式。 同時關閉log-slave-updates.

3. Reset master.

4. S2上執行stop slave 然后 change master to master_host=S1. Start slave.

5. 刪除S1上的master.info 和relay-log.info 否則下次還是按照從庫啟動

6.主庫修復后 可以按照s2的配置方法 重置到S1。

以上就是MySQL 主從的一些基礎知識，小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注億速云行業資訊頻道。