A钧舌、MySql雙機(jī)熱備概述
雙機(jī)熱備就是使用MySQL提供的一種主從備份機(jī)制實(shí)現(xiàn)尿褪。所謂雙機(jī)熱備其實(shí)是一個(gè)復(fù)制的過程徐伐，復(fù)制過程中一個(gè)服務(wù)器充當(dāng)主服務(wù)器，一個(gè)或多個(gè)服務(wù)器充當(dāng)從服務(wù)女嘲。這個(gè)復(fù)制的過程實(shí)質(zhì)上是從服務(wù)器復(fù)制主服務(wù)器上MySQL的二進(jìn)制日志（bin-log）畜份，并在從服務(wù)器上還原主服務(wù)器上的操作。
雙機(jī)熱備的實(shí)現(xiàn)需要MySQL的版本高于3.2欣尼。另外由于這種備份是基于MySQL二進(jìn)制日志實(shí)現(xiàn)爆雹，所以主從服務(wù)器上的MySQL版本最好能夠一樣，至少?gòu)姆?wù)器的MySQL版本不可以低于主服務(wù)器的數(shù)據(jù)庫(kù)版本愕鼓。因?yàn)镸ySQL不同的版本之間二進(jìn)制日志可能不一樣钙态。
配置參數(shù)說明
Server-id
ID值唯一的標(biāo)識(shí)了復(fù)制群集中的主從服務(wù)器，因此它們必須各不相同菇晃。Master_id必須為1到232－1之間的一個(gè)正整數(shù)值册倒，slave_id值必須為2到232－1之間的一個(gè)正整數(shù)值。
Log-bin
表示打開binlog磺送，打開該選項(xiàng)才可以通過I/O寫到Slave的relay-log驻子，也是可以進(jìn)行replication的前提。
Binlog-do-db
表示需要記錄二進(jìn)制日志的數(shù)據(jù)庫(kù)估灿。如果有多個(gè)數(shù)據(jù)可以用逗號(hào)分隔拴孤，或者使用多個(gè)binlog-do-dg選項(xiàng)。
Binglog-ingore-db
表示不需要記錄二進(jìn)制日志的數(shù)據(jù)庫(kù)甲捏，如果有多個(gè)數(shù)據(jù)庫(kù)可用逗號(hào)分隔演熟，或者使用多binglog-ignore-db選項(xiàng)。
Replicate-do-db
表示需要同步的數(shù)據(jù)庫(kù)司顿，如果有多個(gè)數(shù)據(jù)可用逗號(hào)分隔芒粹，或者使用多個(gè)replicate-do-db選項(xiàng)。
Replicate-ignore-db
表示不需要同步的數(shù)據(jù)庫(kù)大溜，如果有多個(gè)數(shù)據(jù)庫(kù)可用逗號(hào)分隔化漆，或者使用多個(gè)replicate-ignore-db選項(xiàng)。
Master-connect-retry
master-connect-retry=n表示從服務(wù)器與主服務(wù)器的連接沒有成功钦奋，則等待n秒（s）后再進(jìn)行管理方式（默認(rèn)設(shè)置是60s）座云。如果從服務(wù)器存在mater.info文件，它將忽略些選項(xiàng)付材。
Log-slave-updates
配置從庫(kù)上的更新操作是否寫入二進(jìn)制文件朦拖，如果這臺(tái)從庫(kù)，還要做其他從庫(kù)的主庫(kù)厌衔，那么就需要打這個(gè)參數(shù)璧帝，以便從庫(kù)的從庫(kù)能夠進(jìn)行日志同步。
Slave-skip-errors
在復(fù)制過程富寿，由于各種原因?qū)е耣inglo中的sql出錯(cuò)睬隶，默認(rèn)情況下锣夹，從庫(kù)會(huì)停止復(fù)制，要用戶介入苏潜∫迹可以設(shè)置slave-skip-errors來定義錯(cuò)誤號(hào)，如果復(fù)制過程中遇到的錯(cuò)誤是定義的錯(cuò)誤號(hào)恤左，便可以路過砖顷。如果從庫(kù)是用來做備份，設(shè)置這個(gè)參數(shù)會(huì)存在數(shù)據(jù)不一致赃梧，不要使用。如果是分擔(dān)主庫(kù)的查詢壓力豌熄，可以考慮授嘀。
Sync_binlog=1 Or N
Sync_binlog的默認(rèn)值是0，這種模式下锣险，MySQL不會(huì)同步到磁盤中去蹄皱。這樣的話，Mysql依賴操作系統(tǒng)來刷新二進(jìn)制日志binary log芯肤，就像操作系統(tǒng)刷新其他文件的機(jī)制一樣巷折。因此如果操作系統(tǒng)或機(jī)器（不僅僅是Mysql服務(wù)器）崩潰，有可能binlog中最后的語(yǔ)句丟失了崖咨。要想防止這種情況锻拘，可以使用sync_binlog全局變量，使binlog在每Ｎ次binlog寫入后與硬盤同步击蹲。當(dāng)sync_binlog變量設(shè)置為１是最安全的署拟，因?yàn)樵赾rash崩潰的情況下，你的二進(jìn)制日志binary log只有可能丟失最多一個(gè)語(yǔ)句或者一個(gè)事務(wù)歌豺。但是推穷，這也是最慢的一種方式（除非磁盤有使用帶蓄電池后備電源的緩存cache,使得同步到磁盤的操作非常快）类咧。
即使sync_binlog設(shè)置為１馒铃，出現(xiàn)崩潰時(shí)，也有可能表內(nèi)容和binlog內(nèi)容之間存在不一致性痕惋。如果使用InnoDB表区宇，Mysql服務(wù)器處理COMMIT語(yǔ)句，它將整個(gè)事務(wù)寫入binlog并將事務(wù)提交到InnoDB中值戳。如果在兩次操作之間出現(xiàn)崩潰萧锉，重啟時(shí)，事務(wù)被InnoDB回滾述寡，但仍然存在binlog中柿隙∫抖矗可以用-innodb-safe-binlog選項(xiàng)來增加InnoDB表內(nèi)容和binlog之間的一致性。（注釋：在Mysql 5.1版本中不需要-innodb-safe-binlog禀崖；由于引入了XA事務(wù)支持衩辟，該選項(xiàng)作廢了），該選項(xiàng)可以提供更大程度的安全波附，使每個(gè)事務(wù)的binlog(sync_binlog=1)和（默認(rèn)情況為真）InnoDB日志與硬盤同步艺晴，該選項(xiàng)的效果是崩潰后重啟時(shí)，在滾回事務(wù)后掸屡，Mysql服務(wù)器從binlog剪切回滾的InnoDB事務(wù)封寞。這樣可以確保binlog反饋InnoDB表的確切數(shù)據(jù)等，并使從服務(wù)器保持與主服務(wù)器保持同步（不接收回滾的語(yǔ)句）仅财。
Auto_increment_offset和Auto_increment_increment
Auto_increment_increment和auto_increment_offset用于主－主服務(wù)器（master-to-master）復(fù)制狈究，并可以用來控制AUTO_INCREMENT列的操作。兩個(gè)變量均可以設(shè)置為全局或局部變量盏求，并且假定每個(gè)值都可以為1到65,535之間的整數(shù)值抖锥。將其中一個(gè)變量設(shè)置為0會(huì)使該變量為1。
這兩個(gè)變量影響AUTO_INCREMENT列的方式：auto_increment_increment控制列中的值的增量值碎罚，auto_increment_offset確定AUTO_INCREMENT列值的起點(diǎn)磅废。
如果auto_increment_offset的值大于auto_increment_increment的值，則auto_increment_offset的值被忽略荆烈。例如：表內(nèi)已有一些數(shù)據(jù)拯勉，就會(huì)用現(xiàn)在已有的最大自增值做為初始值。
B憔购、 MySQL雙機(jī)熱備份配置步驟
假設(shè)主服務(wù)器A(master)谜喊、從服務(wù)器為B(slave)。兩服務(wù)器Ip為：A——10.0.21.223倦始，B——10.0.21.221斗遏。本文服務(wù)器A采用windows操作系統(tǒng)，服務(wù)器B采用centos7.9鞋邑，目的是為了一次說明windows和Linux不同環(huán)境中的配置诵次。
步驟一：主服務(wù)器授權(quán)
在主服務(wù)器A上創(chuàng)建一個(gè)username和password供從服務(wù)器B訪問時(shí)使用。在MySQL命令行下輸入
mysql>GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO backup@10.0.21.221 IDENTIFIED BY'123456';
步驟二：數(shù)據(jù)復(fù)制
將A上已有的數(shù)據(jù)備份到B上枚碗，以便主從數(shù)據(jù)庫(kù)建立的時(shí)候兩個(gè)數(shù)據(jù)庫(kù)的數(shù)據(jù)保持一致（這里復(fù)制的方法有很多逾一，但必須保證兩個(gè)數(shù)據(jù)庫(kù)的數(shù)據(jù)完全相同）。
步驟三：配置主服務(wù)器
主服務(wù)器A是windows操作系統(tǒng)肮雨，其mysql配置文件在mysql安裝目錄下的my.ini文件遵堵。我們對(duì)主服務(wù)器的配置都在my.ini中。
停止A服務(wù)器上的mysql服務(wù)，打開my.ini配置文件陌宿。在my.ini中找到[mysqld]锡足，并在[mysqld]區(qū)域內(nèi)做如下修改：
[mysqld]
server-id=1#是1~2^23-1內(nèi)的唯一值且不能與B或其它slave服務(wù)中的配置相同
log-bin = bin_log#日志文件以bin_log為前綴,如果不給log-bin賦值,日志文件將以#master-server-hostname為前綴
binlog-ignore-db= test#日志文件跳過的數(shù)據(jù)庫(kù)(可選屬性 ),如果有多個(gè)數(shù)據(jù)庫(kù)，可以重復(fù)配置這個(gè)屬性
binlog-do-db= db_test #日志文件操作的數(shù)據(jù)庫(kù)(可選屬性 壳坪，默認(rèn)所有數(shù)據(jù)庫(kù)的相關(guān)操作都寫入二進(jìn)制日志文件) ,如果有多個(gè)數(shù)據(jù)庫(kù)舶得，可以重復(fù)配置這個(gè)屬性
expire_logs_days=30 #設(shè)置bin-log日志保存的天數(shù)
步驟四：重啟A中的MySQL服務(wù)
配置完上面選項(xiàng)后，重啟A上的MySQL服務(wù)爽蝴，這時(shí)候A上所有對(duì)qxpt_demo數(shù)據(jù)庫(kù)的更新操作都會(huì)被記錄到二進(jìn)制日志中沐批。
步驟五：配置slave
本文中slave所在的B服務(wù)器是linux環(huán)境，其MySQL配置文件在/etc/my.cnf中蝎亚。
在slave的my.cnf中 [mysqld]九孩，并在其中做如下配置：
[mysqld]
server-id=2#唯一并與主服務(wù)器上的server-id不同。
replicate-do-db= db_test#復(fù)制操作要針對(duì)的數(shù)據(jù)庫(kù)(可選发框，默認(rèn)為全部)躺彬，同樣如果有多個(gè)數(shù)據(jù)庫(kù)則可配置多次本屬性。
expire_logs_days=30 #設(shè)置bin-log日志保存的天數(shù)
步驟六：重啟并對(duì)slave進(jìn)行設(shè)置
重啟B服務(wù)器上的MySQL服務(wù)缤底，用mysql -uroot -p 登錄MySQL，并執(zhí)行如下命令：
mysql> change master to \
-> master_host='10.0.21.221',//主服務(wù)器的IP地址
-> master_user='backup',//同步數(shù)據(jù)庫(kù)的用戶
-> master_password=’123456’;//同步數(shù)據(jù)庫(kù)的密碼
注：MySQL 5.1.7之前上面命令中的參數(shù)都可以在my.cnf配置文件的[mysqld]區(qū)域中配置番捂。
- 參數(shù)說明
mysql>show slave status个唧；
mysql>change master to master_host='10.0.21.221',
master_user='backup',
master_password='123456',
master_port=3306,
master_log_file='test-mysql-bin.000001',
master_log_pos=1612,
master_connect_retry=30;
master_host='11.11.11.61'：主節(jié)點(diǎn)的IP地址
master_user='admin'：剛才在Master建立的用于同步數(shù)據(jù)庫(kù)的用戶
master_password='xiaoliu'：主節(jié)點(diǎn)同步用戶的密碼
master_port=3306：Master節(jié)點(diǎn)數(shù)據(jù)庫(kù)的端口
master_log_file='lyz-mysql-bin.000001'：指定Slave從哪個(gè)日志文件開始讀取復(fù)制文件(可在Master上使用show master status查看到日志文件名)
master_log_pos=1312：從哪個(gè)POSITION號(hào)開始讀(可在Master上使用show master status查看）
master_connect_retry=30：當(dāng)重新建立主從連接時(shí)，如果連接建立失敗设预，間隔多久后重試,單位為秒徙歼，默認(rèn)設(shè)置為60秒，同步延遲調(diào)優(yōu)參數(shù)鳖枕。
步驟七：重啟并對(duì)slave進(jìn)行設(shè)置
MySQL中執(zhí)行show slave status魄梯；命令查看slave狀態(tài)：
這時(shí)候slave還未啟動(dòng)，我們只要在MySQL中執(zhí)行start slave; 即可啟動(dòng)slave宾符。這時(shí)候slave_io_running和slave_sql_runing的值都應(yīng)該變?yōu)閥es酿秸。
經(jīng)過以上六個(gè)步驟我們就可以實(shí)現(xiàn)從A到B的單向熱備份。這時(shí)候我們對(duì)A中db_test數(shù)據(jù)庫(kù)進(jìn)行修改等影響數(shù)據(jù)庫(kù)數(shù)據(jù)及結(jié)構(gòu)的操作會(huì)被復(fù)制到B中魏烫。
C辣苏、 MySQL雙機(jī)熱備份原理
MySQL的雙機(jī)熱備份是一個(gè)異步的復(fù)制（replication），slave的備份數(shù)據(jù)實(shí)際上就是從master端獲取其binlog日志后哄褒，在自己身上完全順序的執(zhí)行日志中記錄的各種操作稀蟋。實(shí)現(xiàn)整個(gè)復(fù)制操作主要由三個(gè)步驟：
(1) master將改變記錄到二進(jìn)制日志(bin-log)中；
(2)slave將master的bin-log拷貝到它的中繼日志(relay log)呐赡；
(3) slave重做中繼日志中的事件退客，將改變反映它自己的數(shù)據(jù)。
在網(wǎng)上找了一個(gè)個(gè)人感覺比較好的圖，如下：
結(jié)合本圖可以看出雙機(jī)熱備的具體步驟如下：
(1)在每個(gè)事務(wù)更新數(shù)據(jù)完成之前萌狂，master在二日志記錄這些改變档玻。MySQL將事務(wù)串行的寫入二進(jìn)制日志，在事件寫入二進(jìn)制日志完成后粥脚，提交事物窃肠。
(2)slave開始一個(gè)工作線程——I/O線程。slave的io進(jìn)程鏈接到master后刷允，向master請(qǐng)求指定位置的日志內(nèi)容冤留。
(3) master接受到slave的請(qǐng)求后，通過master的io讀取指定日志內(nèi)容及bin-log文件的相關(guān)信息（slave status中Master_Log_File和Read_Master_Log_Pos屬性）树灶，并返回給slave的io進(jìn)程纤怒。
(4)slave接收到信息后，將接受到的日志內(nèi)容依次添加到slave端的relay-log（在MySQLdata目錄中）的最末端天通，并讀取master傳過來的bin-log文件信息泊窘，將bin-log的文件信息存到master.info文件中，以便下一次請(qǐng)求能夠清楚的告訴master需要讀取bin-log的哪個(gè)位置開始往后的日志內(nèi)容像寒。
(5)slave的sql進(jìn)程檢測(cè)到relay-log中新增內(nèi)容后烘豹，會(huì)立刻解析relay-log的內(nèi)容，將其解析成在master端真實(shí)執(zhí)行的那些可執(zhí)行內(nèi)容诺祸，并在slave自身執(zhí)行携悯。
PS 操作從庫(kù)修改一條記錄報(bào)錯(cuò)
先操作從庫(kù)修改一條記錄，又去主庫(kù)修改筷笨，會(huì)造成同步出錯(cuò)
去從庫(kù)執(zhí)行
mysql>stop slave;
mysql>set global sql_slave_skip_counter=1;
mysql>start slave;
或者在主庫(kù)執(zhí)行憔鬼，記住這里的File_size，后面從庫(kù)的pos會(huì)用到
mysql>show master logs;
然后去從庫(kù)執(zhí)行
mysql>show slave status胃夏；
mysql>stop slave;
mysql>change master to master_log_pos=154, master_log_file='bin_log.000009';
mysql>start slave
mysql>show slave status轴或；
