MySQL 主從的一些基礎(chǔ)知識(shí)

本篇文章給大家分享的是有關(guān)MySQL 主從的一些基礎(chǔ)知識(shí)，小編覺(jué)得挺實(shí)用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話不多說(shuō)，跟著小編一起來(lái)看看吧。

MySQL復(fù)制原理

1. 主庫(kù)修改時(shí)會(huì)把數(shù)據(jù)變更作為event記錄在binlog中,主庫(kù)上的sync_binlog參數(shù)控制binlog日志刷新到磁盤(pán).

2. 主庫(kù)推送binlog 中的事件到從庫(kù)的中繼日志relay log，之后從庫(kù)根據(jù)中繼日志relay log重做數(shù)據(jù)變更操作。通過(guò)邏輯復(fù)制達(dá)到主從同步。

MySQL通過(guò)3個(gè)現(xiàn)成來(lái)完成主從數(shù)據(jù)庫(kù)之間的復(fù)制:

1.binlog dump 跑在主庫(kù)上，負(fù)責(zé)讀取數(shù)據(jù)庫(kù)事件并發(fā)送給I/O線程

2.I/O線程 跑在從庫(kù)上，當(dāng)從庫(kù)start slave時(shí) 創(chuàng)建I/O線程 鏈接到master 將主庫(kù)的事件寫(xiě)入到relaylog

3.SQL線程跑在從庫(kù)上，負(fù)責(zé)讀取relay log并更新從庫(kù)。

4.Master.inf and relay-log.info 用來(lái)保存復(fù)制的進(jìn)度。

三種復(fù)制方法, MIX,STATEMENT,ROW

1.  statement

基于語(yǔ)句的復(fù)制,可能會(huì)因?yàn)榇鎯?chǔ)過(guò)程或觸發(fā)器,時(shí)間函數(shù)導(dǎo)致主備不一致，數(shù)據(jù)量小。

2. row

行級(jí)數(shù)據(jù)復(fù)制,缺點(diǎn)是數(shù)據(jù)量較大，但是不會(huì)因?yàn)橛|發(fā)器等外在條件影響數(shù)據(jù)

3.   Mix

默認(rèn)采用statement模式 如果語(yǔ)句中包含更新時(shí)間，用戶相關(guān)的函數(shù)等

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

雙主復(fù)制架構(gòu)：

由master1 作為讀寫(xiě)庫(kù)，master2 作為只讀庫(kù)。

同時(shí)雙向作為對(duì)方的slave。

當(dāng)維護(hù)時(shí)

1.       先停掉master1上的slave。免得當(dāng)master2 做維護(hù)時(shí)影響到master1.

2.       停掉master2 上的slave。 開(kāi)始維護(hù)操作，比如創(chuàng)建索引。

3.       打開(kāi)master2上的slave 讓master2 與master1 同步。

4.       將讀寫(xiě)操作轉(zhuǎn)移到master2 ，確保master1 上無(wú)寫(xiě)入操作

5.       將master1 的slave打開(kāi)， 讓其與salve2 同步完成。

搭建mysql復(fù)制：

1.       建立一個(gè)用于replication的賬號(hào)：

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

2.       修改my.cnf 開(kāi)啟binlog 并且設(shè)置server-id的值
log-bin = xxxxx.log
server-id=1

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

4.       檢查當(dāng)前日志偏移量 show master status

5.       備份或者通過(guò)直接shutdown數(shù)據(jù)庫(kù) 拷貝數(shù)據(jù)文件的方式到slave庫(kù)。

6.       Unlock tables;

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

8.       使用延遲復(fù)制開(kāi)啟salve ./bin/mysql_safe –skip-slave-start&

9.       對(duì)數(shù)據(jù)庫(kù)服務(wù)做響應(yīng)設(shè)置
mysql>change master to master_host=’’,master_user=’’,master_password=’’,master_logfile=’’,master_log_pos=’’;

10.   從庫(kù)上輸入:start slave;

11.   show process\G檢查slave進(jìn)程

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

13.   最后注意的幾點(diǎn)就是MHA架構(gòu)可以通過(guò)自動(dòng)抽取缺失的log進(jìn)行catchup。也可以通過(guò)mysqlbinlog工具來(lái)抽取之前主庫(kù)為宕機(jī)時(shí)未抽取的log 傳送到備庫(kù)上去。

半同步復(fù)制:

在mysql 5.5之前，由于主備庫(kù)之間都處于異步復(fù)制的狀態(tài)，如果主庫(kù)在commit了之后而崩壞，此時(shí)binlog尚未傳到slave的庫(kù)上。則會(huì)丟失數(shù)

而5.5之后mysql推出了半同步復(fù)制狀態(tài)。在默認(rèn)狀態(tài)下mysql需要保證有任意一臺(tái)備庫(kù)的relaylog 接收到的更改之后才會(huì)確認(rèn)commit已經(jīng)成功。如果中途斷網(wǎng)的話 則會(huì)再自動(dòng)切換回半同步復(fù)制模式。

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

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

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

4.select * from mysql.plugin

5.在主備庫(kù)上設(shè)置全局參數(shù)打開(kāi)半同步set global rpl_semi_sync_master_enabled=1;

         Set global rpl_semi_sync_master_timeout=3000;

         從庫(kù)上
         set global rpl_semi_sync_slave=1;

6如果已經(jīng)啟動(dòng)了異步復(fù)制 需要停止一下io_thread. Stop slave io_thread; start slave io_thread;

Show status like ‘%semi_sync%’

需要注意的幾個(gè)結(jié)果有:

rpl_semi_syc_master_status   ON 代表打開(kāi);

rpl_semi_syc_master_yes/no_tx 表示 同步或非同步的transaction次數(shù)

rpl_semi_syc_master_timeout 標(biāo)示等待多久會(huì)判斷slave超時(shí)。

如果從庫(kù)斷開(kāi)后重新連接上了，會(huì)自動(dòng)轉(zhuǎn)回半同步狀態(tài)。

Mysql replication 一些有用的參數(shù)：

1．  Log-slave-updates 從庫(kù)是否需要記binlog  這個(gè)對(duì)于從庫(kù)傳從庫(kù)是比較有幫助的

2．  Master-connect-retry 在從庫(kù)和主庫(kù)失去連接時(shí)的重試間隔 默認(rèn)60秒

3．  ./bin/Mysqld_safe –read-only 讓以避免超級(jí)用戶以外的人修改從庫(kù)的數(shù)據(jù)

日常維護(hù)命令:

1.       查看從庫(kù)狀態(tài) show slave status.比較關(guān)心的2狀態(tài)一個(gè)是SLAVE_IO_Running和Slave_SQL_Running.

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

3.       從庫(kù)出錯(cuò)：

a)         如果需要跳過(guò)某些導(dǎo)致replication失敗的語(yǔ)句可以使用set global SQL_SLAVE_SKIP_COUNTER=N. N=1|2. 當(dāng)主庫(kù)沒(méi)有自增長(zhǎng)時(shí)使用1 自增長(zhǎng)用2.

b)         Log event entry exceeded max_allowed_packet 一般是因?yàn)橛虚L(zhǎng)字符串或blob無(wú)法傳輸。 此時(shí)需要增加主庫(kù)上該參數(shù)的大小。

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

一些簡(jiǎn)單的提高從庫(kù)性能的架構(gòu)

1.通過(guò)master1 傳給master2  master2使用blackhole 引擎開(kāi)始slave-log-update。
  另外的slave 通過(guò) replace-do-db的方式只復(fù)制部分?jǐn)?shù)據(jù)庫(kù)。這種方法的缺點(diǎn)是一旦master1宕機(jī)之后會(huì)比較麻煩需要提前商量好異常處理方案。

2.mysql 5.6之后有多線程復(fù)制，允許從庫(kù)并行更新。Slave_parallel_works=2. Percona分支的mysql還可以用淘寶丁奇的mysql transfer來(lái)實(shí)現(xiàn)從主庫(kù)多線程應(yīng)用中繼日志來(lái)解決。

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

切換主從：

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

2. S1上Stop slave.打開(kāi)log-bin模式。 同時(shí)關(guān)閉log-slave-updates.

3. Reset master.

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

5. 刪除S1上的master.info 和relay-log.info 否則下次還是按照從庫(kù)啟動(dòng)

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

以上就是MySQL 主從的一些基礎(chǔ)知識(shí)，小編相信有部分知識(shí)點(diǎn)可能是我們?nèi)粘９ぷ鲿?huì)見(jiàn)到或用到的。希望你能通過(guò)這篇文章學(xué)到更多知識(shí)。更多詳情敬請(qǐng)關(guān)注億速云行業(yè)資訊頻道。