轉(zhuǎn)：https://blog.csdn.net/qq394829044/article/details/53203645
接續(xù)上一篇關(guān)于mysql雙機(jī)熱備實(shí)現(xiàn)原理分析，在本文經(jīng)過深思熟慮和多次用不同的方式實(shí)測試后。最后在這篇文章中边涕，用一個(gè)小例子來完成mysql雙機(jī)熱備的實(shí)現(xiàn)紫新。
Mysql數(shù)據(jù)庫沒有增量備份的機(jī)制，當(dāng)數(shù)據(jù)量太大的時(shí)候備份是一個(gè)很大的問題憎蛤。還好mysql數(shù)據(jù)庫提供了一種主從備份的機(jī)制捞烟，其實(shí)就是把主數(shù)據(jù)庫的所有的數(shù)據(jù)同時(shí)寫到備份的數(shù)據(jù)庫中吠卷。實(shí)現(xiàn)mysql數(shù)據(jù)庫的熱備份召庞。?
要想實(shí)現(xiàn)雙機(jī)的熱備，首先要了解主從數(shù)據(jù)庫服務(wù)器的版本的需求来破。要實(shí)現(xiàn)熱備mysql的版本都高于3.2篮灼。還有一個(gè)基本的原則就是作為從數(shù)據(jù)庫的數(shù)據(jù)版本可以高于主服務(wù)器數(shù)據(jù)庫的版本，但是不可以低于主服務(wù)器的數(shù)據(jù)庫版本徘禁。
當(dāng)然要實(shí)現(xiàn)mysql雙機(jī)熱備诅诱，除了mysql本身自帶的REPLICATION功能可以實(shí)現(xiàn)外，也可以用Heartbeat這個(gè)開源軟件來實(shí)現(xiàn)送朱。不過本文主要還是講如何用mysql自帶的REPLICATION來實(shí)現(xiàn)mysql雙機(jī)熱備的功能娘荡。
由于Mysql不同版本之間的（二進(jìn)制日志）binlog格式可能會(huì)不太一樣，因此最好的搭配組合是主（Master）服務(wù)器的Mysql版本和從（Slave）服務(wù)器版本相同或者更低驶沼，主服務(wù)器的版本肯定不能高于從服務(wù)器版本炮沐。
本次我用于測試的兩臺(tái)服務(wù)器版本都是Mysql-5.5.17。
2. Mysql?建立主－從服務(wù)器雙機(jī)熱備配置步驟
A服務(wù)器（主服務(wù)器Master）：59.151.15.36
B服務(wù)器（從服務(wù)器Slave）：218.206.70.146
主從服務(wù)器的Mysql版本皆為5.5.17
Linux環(huán)境下
將主服務(wù)器需要同步的數(shù)據(jù)庫內(nèi)容進(jìn)行備份一份回怜，上傳到從服務(wù)器上大年，保證始初時(shí)兩服務(wù)器中數(shù)據(jù)庫內(nèi)容一致。
不過這里說明下玉雾，由于我是利用Mysql在安裝后就有的數(shù)據(jù)庫test進(jìn)行測試的翔试，所以兩臺(tái)服務(wù)器里面是沒有建立表的，只不分別在test里面建立了同樣的一張空表tb_mobile;
Sql語句如下：
mysql> create table tb_mobile( mobile VARCHAR(20) comment'手機(jī)號(hào)碼', time timestamp DEFAULT now() comment'時(shí)間' );
2.2.1?創(chuàng)建同步用戶
進(jìn)入mysql操作界面复旬，在主服務(wù)器上為從服務(wù)器建立一個(gè)連接帳戶垦缅，該帳戶必須授予REPLICATION SLAVE權(quán)限。因?yàn)閺膍ysql版本3.2以后就可以通過REPLICATION對(duì)其進(jìn)行雙機(jī)熱備的功能操作驹碍。
操作指令如下：
mysql> grant replication slave on *.* to 'replicate'@'218.206.70.146' identified by '123456';
mysql> flush privileges;
創(chuàng)建好同步連接帳戶后壁涎，我們可以通過在從服務(wù)器（Slave）上用replicat帳戶對(duì)主服務(wù)器（Master）數(shù)據(jù)庫進(jìn)行訪問下凡恍，看下是否能連接成功。
在從服務(wù)器（Slave）上輸入如下指令：
[root@YD146 ~]# mysql -h59.151.15.36 -ureplicate -p123456
如果出現(xiàn)下面的結(jié)果粹庞，則表示能登錄成功咳焚，說明可以對(duì)這兩臺(tái)服務(wù)器進(jìn)行雙機(jī)熱備進(jìn)行操作。
2.2.2?修改mysql配置文件
如果上面的準(zhǔn)備工作做好庞溜，那邊我們就可以進(jìn)行對(duì)mysql配置文件進(jìn)行修改了革半，首先找到mysql配置所有在目錄，一般在安裝好mysql服務(wù)后流码，都會(huì)將配置文件復(fù)制一一份出來放到/ect目錄下面又官，并且配置文件命名為：my.cnf。即配置文件準(zhǔn)確目錄為/etc/my.cnf
(Linux下用rpm包安裝的MySQL是不會(huì)安裝/etc/my.cnf文件的漫试，
至于為什么沒有這個(gè)文件而MySQL卻也能正常啟動(dòng)和作用六敬，在點(diǎn)有兩個(gè)說法，
第一種說法驾荣，my.cnf只是MySQL啟動(dòng)時(shí)的一個(gè)參數(shù)文件外构，可以沒有它，這時(shí)MySQL會(huì)用內(nèi)置的默認(rèn)參數(shù)啟動(dòng)播掷，
第二種說法审编，MySQL在啟動(dòng)時(shí)自動(dòng)使用/usr/share/mysql目錄下的my-medium.cnf文件，這種說法僅限于rpm包安裝的MySQL歧匈，
解決方法垒酬，只需要復(fù)制一個(gè)/usr/share/mysql目錄下的my-medium.cnf文件到/etc目錄，并改名為my.cnf即可件炉。)
找到配置文件my.cnf打開后勘究，在[mysqld]下修改即可：
[mysqld]
server-id = 1
log-bin=mysql-bin??????????????? //其中這兩行是本來就有的，可以不用動(dòng)斟冕，添加下面兩行即可
binlog-do-db = test
binlog-ignore-db = mysql
2.2.3?重啟mysql服務(wù)
修改完配置文件后口糕，保存后，重啟一下mysql服務(wù)宫静，如果成功則沒問題走净。
2.2.4?查看主服務(wù)器狀態(tài)
進(jìn)入mysql服務(wù)后，可通過指令查看Master狀態(tài)孤里，輸入如下指令：
注意看里面的參數(shù)伏伯，特別前面兩個(gè)File和Position，在從服務(wù)器（Slave）配置主從關(guān)系會(huì)有用到的捌袜。
注：這里使用了鎖表说搅，目的是為了產(chǎn)生環(huán)境中不讓進(jìn)新的數(shù)據(jù)，好讓從服務(wù)器定位同步位置虏等，初次同步完成后弄唧，記得解鎖适肠。
2.3.1修改配置文件
因?yàn)檫@里面是以主－從方式實(shí)現(xiàn)mysql雙機(jī)熱備的，所以在從服務(wù)器就不用在建立同步帳戶了候引，直接打開配置文件my.cnf進(jìn)行修改即可侯养，道理還是同修改主服務(wù)器上的一樣，只不過需要修改的參數(shù)不一樣而已澄干。如下：
[mysqld]
server-id = 2
log-bin=mysql-bin
replicate-do-db = test
replicate-ignore-db = mysql,information_schema,performance_schema
2.3.2重啟mysql服務(wù)
修改完配置文件后逛揩，保存后，重啟一下mysql服務(wù)麸俘，如果成功則沒問題辩稽。
2.3.3用change mster?語句指定同步位置
這步是最關(guān)鍵的一步了，在進(jìn)入mysql操作界面后从媚，輸入如下指令：
mysql>stop slave;????????? //先停步slave服務(wù)線程逞泄，這個(gè)是很重要的，如果不這樣做會(huì)造成以下操作不成功拜效。
mysql>change master to
>master_host='59.151.15.36',master_user='replicate',master_password='123456',
> master_log_file=' mysql-bin.000016 ',master_log_pos=107;
注：master_log_file, master_log_pos由主服務(wù)器（Master）查出的狀態(tài)值中確定喷众。也就是剛剛叫注意的。master_log_file對(duì)應(yīng)File, master_log_pos對(duì)應(yīng)Position紧憾。Mysql 5.x以上版本已經(jīng)不支持在配置文件中指定主服務(wù)器相關(guān)選項(xiàng)侮腹。
遇到的問題，如果按上面步驟之后還出現(xiàn)如下情況：
則要重新設(shè)置slave稻励。指令如下
mysql>stop slave;
mysql>reset slave;
之后停止slave線程重新開始。成功后愈涩，則可以開啟slave線程了望抽。
mysql>start slave;
2.3.4查看從服務(wù)器（Slave）狀態(tài)
用如下指令進(jìn)行查看
mysql> show slave status\G;
查看下面兩項(xiàng)值均為Yes，即表示設(shè)置從服務(wù)器成功履婉。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
之前開始已經(jīng)說過了在數(shù)據(jù)庫test只有一個(gè)表tb_mobile沒有數(shù)據(jù)煤篙，我們可以先查看下兩服務(wù)器的數(shù)據(jù)庫是否有數(shù)據(jù)：
Master:59.151.15.36
Slave:218.206.70.146
好了，現(xiàn)在可以在Master服務(wù)器中插入數(shù)據(jù)看下是否能同步毁腿。
Master:59.151.15.36
Slave:218.206.70.146
可以從上面兩個(gè)截圖上看出辑奈，在Master服務(wù)器上進(jìn)行插入的數(shù)據(jù)在Slave服務(wù)器可以查到，這就表示雙機(jī)熱備配置成功了已烤。
3. Mysql?建立主－主服務(wù)器雙機(jī)熱備配置步驟
服務(wù)器還是用回現(xiàn)在這兩臺(tái)服務(wù)器
3.1創(chuàng)建同步用戶
同時(shí)在主從服務(wù)器建立一個(gè)連接帳戶鸠窗，該帳戶必須授予REPLIATION SLAVE權(quán)限。這里因?yàn)榉?wù)器A和服務(wù)器B互為主從胯究，所以都要分別建立一個(gè)同步用戶稍计。
服務(wù)器A：
mysql> grant replication slave on *.* to 'replicate'@'218.206.70.146' identified by '123456';
mysql> flush privileges;
服務(wù)器B：
mysql> grant replication slave on *.* to 'replicate'@'59.151.15.36' identified by '123456';
mysql> flush privileges;
3.2修改配置文件my.cnf
服務(wù)器A
[mysqld]
?????? server-id = 1
log-bin=mysql-bin?
binlog-do-db = test
?????? binlog-ignore-db = mysql
#主－主形式需要多添加的部分
?????? log-slave-updates
?????? sync_binlog = 1
?????? auto_increment_offset = 1
?????? auto_increment_increment = 2
?????? replicate-do-db = test
?????? replicate-ignore-db = mysql,information_schema
服務(wù)器B：
[mysqld]
server-id = 2
log-bin=mysql-bin?
master-slave need
?????? replicate-do-db = test
?????? replicate-ignore-db = mysql,information_schema,performance_schema
#主－主形式需要多添加的部分
?????? binlog-do-db = test
?????? binlog-ignore-db = mysql
?????? log-slave-updates
?????? sync_binlog = 1
?????? auto_increment_offset = 2
?????? auto_increment_increment = 2
3.3分別重啟A服務(wù)器和B服務(wù)器上的mysql服務(wù)
重啟服務(wù)器方式和上面的一樣，這里就不做講解了裕循。
3.4分別查A服務(wù)器和B服務(wù)器作為主服務(wù)器的狀態(tài)
服務(wù)器A：
服務(wù)器B：
3.5分別在A服務(wù)器和B服務(wù)器上用change master to?指定同步位置
服務(wù)器A：
mysql>change master to
>master_host='218.206.70.146',master_user='replicate',master_password='123456',
> master_log_file=' mysql-bin.000011 ',master_log_pos=497;
服務(wù)器B：
mysql>change master to
>master_host='59.151.15.36',master_user='replicate',master_password='123456',
> master_log_file=' mysql-bin.000016 ',master_log_pos=107;
3.6?分別在A和B服務(wù)器上重啟從服務(wù)線程
mysql>start slave;
3.7?分別在A和B服務(wù)器上查看從服務(wù)器狀態(tài)
mysql>show slave status\G;
查看下面兩項(xiàng)值均為Yes臣嚣，即表示設(shè)置從服務(wù)器成功净刮。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
3.8?測試主－主同步例子
測試服務(wù)器A：
在服務(wù)器A上插入一條語句如下圖所示：
之后在服務(wù)器B上查看是否同步如下圖所示：
測試服務(wù)器B：
在服務(wù)器B上插入一條語句如下圖所示：
然后在從服務(wù)器A上查看是否有同步數(shù)據(jù)如下圖所示：
最后從結(jié)果可以看出主－主形式的雙機(jī)熱備是能成功實(shí)現(xiàn)的。
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ù)庫穷吮。如果有多個(gè)數(shù)據(jù)可以用逗號(hào)分隔，或者使用多個(gè)binlog-do-dg選項(xiàng)饥努。
Binglog-ingore-db
表示不需要記錄二進(jìn)制日志的數(shù)據(jù)庫捡鱼，如果有多個(gè)數(shù)據(jù)庫可用逗號(hào)分隔，或者使用多binglog-ignore-db選項(xiàng)酷愧。
Replicate-do-db
表示需要同步的數(shù)據(jù)庫驾诈，如果有多個(gè)數(shù)據(jù)可用逗號(hào)分隔，或者使用多個(gè)replicate-do-db選項(xiàng)溶浴。
Replicate-ignore-db
表示不需要同步的數(shù)據(jù)庫乍迄，如果有多個(gè)數(shù)據(jù)庫可用逗號(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
配置從庫上的更新操作是否寫入二進(jìn)制文件，如果這臺(tái)從庫饥臂，還要做其他從庫的主庫逊躁，那么就需要打這個(gè)參數(shù)，以便從庫的從庫能夠進(jìn)行日志同步隅熙。
Slave-skip-errors
在復(fù)制過程稽煤，由于各種原因?qū)е耣inglo中的sql出錯(cuò)，默認(rèn)情況下囚戚，從庫會(huì)停止復(fù)制酵熙，要用戶介入⊥涮裕可以設(shè)置slave-skip-errors來定義錯(cuò)誤號(hào)绿店，如果復(fù)制過程中遇到的錯(cuò)誤是定義的錯(cuò)誤號(hào)，便可以路過。如果從庫是用來做備份假勿，設(shè)置這個(gè)參數(shù)會(huì)存在數(shù)據(jù)不一致借嗽，不要使用。如果是分擔(dān)主庫的查詢壓力转培，可以考慮恶导。
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中最后的語句丟失了肌索。要想防止這種情況蕉拢，可以使用sync_binlog全局變量，使binlog在每Ｎ次binlog寫入后與硬盤同步诚亚。當(dāng)sync_binlog變量設(shè)置為１是最安全的晕换，因?yàn)樵赾rash崩潰的情況下，你的二進(jìn)制日志binary log只有可能丟失最多一個(gè)語句或者一個(gè)事務(wù)站宗。但是闸准，這也是最慢的一種方式（除非磁盤有使用帶蓄電池后備電源的緩存cache,使得同步到磁盤的操作非常快）梢灭。
即使sync_binlog設(shè)置為１夷家，出現(xiàn)崩潰時(shí)，也有可能表內(nèi)容和binlog內(nèi)容之間存在不一致性敏释。如果使用InnoDB表瘾英，Mysql服務(wù)器處理COMMIT語句，它將整個(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ù)器保持同步（不接收回滾的語句）修壕。
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)在已有的最大自增值做為初始值。
如何解決MySQL主從同步錯(cuò)誤的SQL
解決：
stop slave;
#表示跳過一步錯(cuò)誤墅诡，后面的數(shù)字可變
set global sql_slave_skip_counter =1;
start slave;
之后再用mysql> show slave status\G? 查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
ok桐智，現(xiàn)在主從同步狀態(tài)正常了末早。
