mysql怎么看主備 mysql 查看主備命令

MySQL實(shí)戰(zhàn)問題03 mysql如何保證主備一致

MySQL 主備的基本原理主備流程切換

創(chuàng)新互聯(lián)于2013年成立，先為臨猗等服務(wù)建站，臨猗等地企業(yè)，進(jìn)行企業(yè)商務(wù)咨詢服務(wù)。為臨猗企業(yè)網(wǎng)站制作PC+手機(jī)+微官網(wǎng)三網(wǎng)同步一站式服務(wù)解決您的所有建站問題。

在狀態(tài) 1 中，客戶端的讀寫都直接訪問節(jié)點(diǎn) A，而節(jié)點(diǎn) B 是 A 的備庫(kù)，只是將 A 的更新都同步過來，到本地執(zhí)行。這樣可以保持節(jié)點(diǎn) B 和 A 的數(shù)據(jù)是相同的

當(dāng)需要切換的時(shí)候，就切成狀態(tài) 2。這時(shí)候客戶端讀寫訪問的都是節(jié)點(diǎn) B，而節(jié)點(diǎn) A 是 B 的備庫(kù)。

M-S模式中, 為什么建議把備庫(kù)設(shè)為readonly? 有時(shí)候一些運(yùn)營(yíng)類的查詢語句會(huì)被放到備庫(kù)上去查，設(shè)置為只讀可以防止誤操作； 防止切換邏輯有 bug，比如切換過程中出現(xiàn)雙寫，造成主備不一致 可以用 readonly 狀態(tài)，來判斷節(jié)點(diǎn)的角色。 把備庫(kù)設(shè)置成只讀了，還怎么跟主庫(kù)保持同步更新呢？

因?yàn)?readonly 設(shè)置對(duì)超級(jí) (super) 權(quán)限用戶是無效的，而用于同步更新的線程，就擁有超級(jí)權(quán)限。

節(jié)點(diǎn) A 到 B 這條線的內(nèi)部流程是什么樣的

下圖畫出的就是一個(gè) update 語句在節(jié)點(diǎn) A 執(zhí)行，然后同步到節(jié)點(diǎn) B 的完整流程圖

備庫(kù) B 跟主庫(kù) A 之間維持了一個(gè)長(zhǎng)連接。主庫(kù) A 內(nèi)部有一個(gè)線程，專門用于服務(wù)備庫(kù) B 的這個(gè)長(zhǎng)連接

一個(gè)事務(wù)日志同步的完整過程是這樣的: 在備庫(kù) B 上通過 change master 命令，設(shè)置主庫(kù) A 的 IP、端口、用戶名、密碼，以及要從哪個(gè)位置開始請(qǐng)求 binlog，這個(gè)位置包含文件名和日志偏移量。 在備庫(kù) B 上執(zhí)行 start slave 命令，這時(shí)候備庫(kù)會(huì)啟動(dòng)兩個(gè)線程，就是圖中的 io_thread 和 sql_thread。其中 io_thread 負(fù)責(zé)與主庫(kù)建立連接。 主庫(kù) A 校驗(yàn)完用戶名、密碼后，開始按照備庫(kù) B 傳過來的位置，從本地讀取 binlog，發(fā)給 B。 備庫(kù) B 拿到 binlog 后，寫到本地文件，稱為中轉(zhuǎn)日志（relay log）。 sql_thread 讀取中轉(zhuǎn)日志，解析出日志里的命令，并執(zhí)行。 binlog 的三種格式對(duì)比

三種格式分別是: statement row mixed

為了便于描述 binlog 的這三種格式間的區(qū)別, 創(chuàng)建并初始化一個(gè)表

mysql CREATE TABLE `t` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `a` (`a`), KEY `t_modified`(`t_modified`) ) ENGINE=InnoDB; insert into t values(1,1,'2018-11-13'); insert into t values(2,2,'2018-11-12'); insert into t values(3,3,'2018-11-11'); insert into t values(4,4,'2018-11-10'); insert into t values(5,5,'2018-11-09');

刪除一行, 分析binlog

mysql delete from t /*comment*/ where a=4 and t_modified='2018-11-10' limit 1; 查詢binlog命令: mysql show binlog events in 'master.000001'; 當(dāng) binlog_format=statement 時(shí)

binlog 里面記錄的就是 SQL 語句的原文:

分析一下上圖輸出的結(jié)果: 第一行可以先忽略. 第二行是一個(gè) BEGIN，跟第四行的 commit 對(duì)應(yīng)，表示中間是一個(gè)事務(wù)； 第三行是真實(shí)的執(zhí)行語句, 在delete命令前,還有一個(gè)use test命令, 是mysql自動(dòng)添加的. 最后一行是一個(gè) COMMIT, 包含一個(gè)xid. 如果使用statement格式, 記錄到binlog 的是語句原文. 會(huì)有什么問題出現(xiàn)呢?

如果delete 帶有 limit, 很可能出出現(xiàn)住主備數(shù)據(jù)庫(kù)不一致的情況

在主庫(kù)執(zhí)行這條 SQL 語句的時(shí)候，用的是索引 a；而在備庫(kù)執(zhí)行這條 SQL 語句的時(shí)候，卻使用了索引 t_modified

當(dāng) binlog_format= row 時(shí):

與statement相比, begin 與 commit 是一致的, 但是row格式?jīng)]有記錄原文, 而是替換成了兩個(gè)event, 粉筆是table_map 與 delete_rows

Table_map event，用于說明接下來要操作的表是 test 庫(kù)的表 t; Delete_rows event，用于定義刪除的行為。 使用mysqlbinlog工具分析解析binlog中內(nèi)容 mysqlbinlog -vv data/master.000001 --start-position=8900;

解析結(jié)果:

server id 1，表示這個(gè)事務(wù)是在 server_id=1 的這個(gè)庫(kù)上執(zhí)行的。 每個(gè) event 都有 CRC32 的值，這是因?yàn)閰?shù) binlog_checksum 設(shè)置成了 CRC32。 Table_map event 顯示了接下來要打開的表，map 到數(shù)字 226?，F(xiàn)在我們這條 SQL 語句只操作了一張表，如果要操作多張表呢？每個(gè)表都有一個(gè)對(duì)應(yīng)的 Table_map event、都會(huì) map 到一個(gè)單獨(dú)的數(shù)字，用于區(qū)分對(duì)不同表的操作。 在 mysqlbinlog 的命令中，使用了 -vv 參數(shù)是為了把內(nèi)容都解析出來，所以從結(jié)果里面可以看到各個(gè)字段的值（比如，@1=4、 @2=4 這些值）。 binlog_row_image 的默認(rèn)配置是 FULL，因此 Delete_event 里面，包含了刪掉的行的所有字段的值。如果把 binlog_row_image 設(shè)置為 MINIMAL，則只會(huì)記錄必要的信息，在這個(gè)例子里，就是只會(huì)記錄 id=4 這個(gè)信息。 最后的 Xid event，用于表示事務(wù)被正確地提交了。 為什么會(huì)有 mixed 格式的 binlog？為什么會(huì)有 mixed 這種 binlog 格式的存在場(chǎng)景？ 因?yàn)橛行?statement 格式的 binlog 可能會(huì)導(dǎo)致主備不一致，所以要使用 row 格式。 但 row 格式的缺點(diǎn)是，很占空間。比如你用一個(gè) delete 語句刪掉 10 萬行數(shù)據(jù)，用 statement 的話就是一個(gè) SQL 語句被記錄到 binlog 中，占用幾十個(gè)字節(jié)的空間。但如果用 row 格式的 binlog，就要把這 10 萬條記錄都寫到 binlog 中。這樣做，不僅會(huì)占用更大的空間，同時(shí)寫 binlog 也要耗費(fèi) IO 資源，影響執(zhí)行速度。 所以，MySQL 就取了個(gè)折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己會(huì)判斷這條 SQL 語句是否可能引起主備不一致，如果有可能，就用 row 格式，否則就用 statement 格式。 如何解決雙M結(jié)構(gòu)的循環(huán)復(fù)制問題解決兩個(gè)節(jié)點(diǎn)間的循環(huán)復(fù)制的問題的邏輯 規(guī)定兩個(gè)庫(kù)的 server id 必須不同，如果相同，則它們之間不能設(shè)定為主備關(guān)系； 一個(gè)備庫(kù)接到 binlog 并在重放的過程中，生成與原 binlog 的 server id 相同的新的 binlog； 每個(gè)庫(kù)在收到從自己的主庫(kù)發(fā)過來的日志后，先判斷 server id，如果跟自己的相同，表示這個(gè)日志是自己生成的，就直接丟棄這個(gè)日志。 按照這個(gè)邏輯，如果我們?cè)O(shè)置了雙 M 結(jié)構(gòu)，日志的執(zhí)行流就會(huì)變成這樣： 從節(jié)點(diǎn) A 更新的事務(wù)，binlog 里面記的都是 A 的 server id； 傳到節(jié)點(diǎn) B 執(zhí)行一次以后，節(jié)點(diǎn) B 生成的 binlog 的 server id 也是 A 的 server id； 再傳回給節(jié)點(diǎn) A，A 判斷到這個(gè) server id 與自己的相同，就不會(huì)再處理這個(gè)日志。所以，死循環(huán)在這里就斷掉了。

MySQL - 主備

在實(shí)際的生產(chǎn)中，為了解決Mysql的單點(diǎn)故障，一般都會(huì)采用「主備模式」。

MySQL幾乎所有的高可用架構(gòu)，都直接依賴于 binlog。雖然這些高可用架構(gòu)已經(jīng)呈現(xiàn)出越來越復(fù)雜的趨勢(shì)，但都是從最基本的一主一備演化過來的。

下圖為主備切換流程

在狀態(tài) 1 中，客戶端的讀寫都直接訪問節(jié)點(diǎn) A，而節(jié)點(diǎn) B 是 A 的備庫(kù)，只是將 A 的更新都同步過來，到本地執(zhí)行。這樣可以保持節(jié)點(diǎn) B 和 A 的數(shù)據(jù)是相同的。

當(dāng)需要切換的時(shí)候，就切成狀態(tài) 2。這時(shí)候客戶端讀寫訪問的都是節(jié)點(diǎn) B，而節(jié)點(diǎn) A 是 B 的備庫(kù)。

在狀態(tài) 1 中，雖然節(jié)點(diǎn) B 沒有被直接訪問，但是依然建議把節(jié)點(diǎn) B（也就是備庫(kù)）設(shè)置成只讀（readonly）模式。這樣做，有以下幾個(gè)考慮：

圖下圖 中畫出的就是一個(gè) update 語句在節(jié)點(diǎn) A 執(zhí)行，然后同步到節(jié)點(diǎn) B 的完整流程圖。

備庫(kù) B 跟主庫(kù) A 之間維持了一個(gè)長(zhǎng)連接。主庫(kù) A 內(nèi)部有一個(gè)線程，專門用于服務(wù)備庫(kù) B 的這個(gè)長(zhǎng)連接。一個(gè)事務(wù)日志同步的完整過程是這樣的：

在備庫(kù) B 上通過 change master 命令，設(shè)置主庫(kù) A 的 IP、端口、用戶名、密碼，以及要從哪個(gè)位置開始請(qǐng)求 binlog，這個(gè)位置包含文件名和日志偏移量。

在備庫(kù) B 上執(zhí)行 start slave 命令，這時(shí)候備庫(kù)會(huì)啟動(dòng)兩個(gè)線程，就是圖中的 io_thread 和 sql_thread。其中 io_thread 負(fù)責(zé)與主庫(kù)建立連接。

主庫(kù) A 校驗(yàn)完用戶名、密碼后，開始按照備庫(kù) B 傳過來的位置，從本地讀取 binlog，發(fā)給 B。

備庫(kù) B 拿到 binlog 后，寫到本地文件，稱為中轉(zhuǎn)日志（relay log）。

sql_thread 讀取中轉(zhuǎn)日志，解析出日志里的命令，并執(zhí)行。

主庫(kù)需要復(fù)制新增binlog到從庫(kù)才能完成同步，這個(gè)同步過程就是同步延遲。主從延遲最直接的表現(xiàn)是，備庫(kù)消費(fèi)中轉(zhuǎn)日志（relay log）的速度，比主庫(kù)生產(chǎn) binlog 的速度要慢。

「同步策略」：Master會(huì)等待所有的Slave都回應(yīng)后才會(huì)提交，這個(gè)主從的同步的性能會(huì)嚴(yán)重的影響。

「半同步策略」：Master至少會(huì)等待一個(gè)Slave回應(yīng)后提交。

「異步策略」：Master不用等待Slave回應(yīng)就可以提交。

「延遲策略」：Slave要落后于Master指定的時(shí)間。

對(duì)于不同的業(yè)務(wù)需求，有不同的策略方案，但是一般都會(huì)采用最終一致性，不會(huì)要求強(qiáng)一致性，畢竟強(qiáng)一致性會(huì)嚴(yán)重影響性能。

如何查詢mysql主從ip地址

方法一

點(diǎn)擊，電腦桌面上的 開始 --- 選擇 運(yùn)行

在運(yùn)行程序輸入框中輸入 “cmd”（注意不要講引號(hào)也復(fù)制進(jìn)去?。?/p>

進(jìn)入dos界面后，大家再次輸入 ipconfig /all 就會(huì)出現(xiàn)dos界面上邊就有顯示自己的電腦ip地址

方法二

打開打開網(wǎng)絡(luò)和共享中心 查看本地連接，大家也可以直接查看本地連接。這都一樣的

打開，本地連接之后 選擇屬性。 在出現(xiàn)的界面上 選擇“詳細(xì)信息”

在打開的 詳細(xì)信息 之后就會(huì)顯示自己的電腦ip地址了

方法三

百度搜索：ip地址查詢

查看mysql是否為雙機(jī)

mysql雙機(jī)熱備實(shí)現(xiàn)原理分析，在本文經(jīng)過深思熟慮和多次用不同的方式實(shí)測(cè)試后。最后在這篇文章中，用一個(gè)小例子來完成mysql雙機(jī)熱備的實(shí)現(xiàn)。

Mysql數(shù)據(jù)庫(kù)沒有增量備份的機(jī)制，當(dāng)數(shù)據(jù)量太大的時(shí)候備份是一個(gè)很大的問題。還好mysql數(shù)據(jù)庫(kù)提供了一種主從備份的機(jī)制，其實(shí)就是把主數(shù)據(jù)庫(kù)的所有的數(shù)據(jù)同時(shí)寫到備份的數(shù)據(jù)庫(kù)中。實(shí)現(xiàn)mysql數(shù)據(jù)庫(kù)的熱備份。

要想實(shí)現(xiàn)雙機(jī)的熱備，首先要了解主從數(shù)據(jù)庫(kù)服務(wù)器的版本的需求。要實(shí)現(xiàn)熱備mysql的版本都高于3.2。還有一個(gè)基本的原則就是作為從數(shù)據(jù)庫(kù)的數(shù)據(jù)版本可以高于主服務(wù)器數(shù)據(jù)庫(kù)的版本，但是不可以低于主服務(wù)器的數(shù)據(jù)庫(kù)版本。

當(dāng)然要實(shí)現(xiàn)mysql雙機(jī)熱備，除了mysql本身自帶的REPLICATION功能可以實(shí)現(xiàn)外，也可以用Heartbeat這個(gè)開源軟件來實(shí)現(xiàn)。不過本文主要還是講如何用mysql自帶的REPLICATION來實(shí)現(xiàn)mysql雙機(jī)熱備的功能。

1. 準(zhǔn)備服務(wù)器

由于Mysql不同版本之間的（二進(jìn)制日志）binlog格式可能會(huì)不太一樣，因此最好的搭配組合是主（Master）服務(wù)器的Mysql版本和從（Slave）服務(wù)器版本相同或者更低，主服務(wù)器的版本肯定不能高于從服務(wù)器版本。

本次我用于測(cè)試的兩臺(tái)服務(wù)器版本都是Mysql-5.5.17。

2. Mysql 建立主－從服務(wù)器雙機(jī)熱備配置步驟

2.1環(huán)境描述

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ù)庫(kù)內(nèi)容進(jìn)行備份一份，上傳到從服務(wù)器上，保證始初時(shí)兩服務(wù)器中數(shù)據(jù)庫(kù)內(nèi)容一致。

不過這里說明下，由于我是利用Mysql在安裝后就有的數(shù)據(jù)庫(kù)test進(jìn)行測(cè)試的，所以兩臺(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 主服務(wù)器Master配置

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ù)庫(kù)進(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 從服務(wù)器Slave配置

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操作界面后，輸入如下指令：

mysqlstop slave; //先停步slave服務(wù)線程，這個(gè)是很重要的，如果不這樣做會(huì)造成以下操作不成功。

mysqlchange 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。指令如下

mysqlstop slave;

mysqlreset slave;

之后停止slave線程重新開始。成功后，則可以開啟slave線程了。

mysqlstart 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

2.4 測(cè)試同步

之前開始已經(jīng)說過了在數(shù)據(jù)庫(kù)test只有一個(gè)表tb_mobile沒有數(shù)據(jù)，我們可以先查看下兩服務(wù)器的數(shù)據(jù)庫(kù)是否有數(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：

mysqlchange 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：

mysqlchange 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ù)線程

mysqlstart slave;

3.7 分別在A和B服務(wù)器上查看從服務(wù)器狀態(tài)

mysqlshow slave status\G;

查看下面兩項(xiàng)值均為Yes，即表示設(shè)置從服務(wù)器成功。

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

3.8 測(cè)試主－主同步例子

測(cè)試服務(wù)器A：

在服務(wù)器A上插入一條語句如下圖所示：

之后在服務(wù)器B上查看是否同步如下圖所示：

測(cè)試服務(wù)器B：

在服務(wù)器B上插入一條語句如下圖所示：

然后在從服務(wù)器A上查看是否有同步數(shù)據(jù)如下圖所示：

最后從結(jié)果可以看出主－主形式的雙機(jī)熱備是能成功實(shí)現(xiàn)的。

4. 配置參數(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ù)制，要用戶介入?？梢栽O(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中最后的語句丟失了。要想防止這種情況，可以使用sync_binlog全局變量，使binlog在每N次binlog寫入后與硬盤同步。當(dāng)sync_binlog變量設(shè)置為1是最安全的，因?yàn)樵赾rash崩潰的情況下，你的二進(jìn)制日志binary log只有可能丟失最多一個(gè)語句或者一個(gè)事務(wù)。但是，這也是最慢的一種方式（除非磁盤有使用帶蓄電池后備電源的緩存cache,使得同步到磁盤的操作非常快）。

即使sync_binlog設(shè)置為1，出現(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主備切換是什么意思？

這一般是數(shù)據(jù)庫(kù)的安全策略，對(duì)于一些安全性要求比較高的系統(tǒng)，數(shù)據(jù)庫(kù)通常是由主服務(wù)器和備份服務(wù)器組成，主備同時(shí)運(yùn)行，主服務(wù)器有數(shù)據(jù)改動(dòng)后，立刻會(huì)同步到備份服務(wù)器。所以在日常運(yùn)維工作中，為了防患于未然，經(jīng)常會(huì)進(jìn)行主備切換，就是把生產(chǎn)對(duì)接的服務(wù)器從主數(shù)據(jù)庫(kù)切換到備份庫(kù)上，使用備份庫(kù)運(yùn)行一段時(shí)間，看看備份庫(kù)運(yùn)行是否正常，數(shù)據(jù)是否正確等。

切換的操作只需將連接池中，數(shù)據(jù)庫(kù)服務(wù)器的Ip換成備份庫(kù)Ip就可以了。

MySQL主備庫(kù)數(shù)據(jù)一致性校驗(yàn)及修復(fù)

很多時(shí)候需要把一個(gè)從庫(kù)提升為主庫(kù)，但對(duì)從庫(kù)和主庫(kù)的數(shù)據(jù)一致性不敢保證，這時(shí)我們就可以利用 pt-table-checksum來檢查主庫(kù)數(shù)據(jù)的一致性，如果存在不一致的數(shù)據(jù)，我們可以利用pt-table-sync來修復(fù)這些不一致的數(shù)據(jù)。

在主（master）上通過執(zhí)行校驗(yàn)的查詢對(duì)復(fù)制的一致性進(jìn)行檢查，對(duì)比主從的校驗(yàn)值，從而產(chǎn)生結(jié)果。

下面通過實(shí)際的例子來解釋該工具如何使用：

主庫(kù)（10.8.23.209）數(shù)據(jù)：

從庫(kù)（10.8.23.208）數(shù)據(jù)：

從庫(kù)（10.8.23.210）數(shù)據(jù)：

很明顯主備數(shù)據(jù)不一致，我們使用工具來檢測(cè)下：

校驗(yàn)命令參數(shù)解釋：

校驗(yàn)結(jié)果字段解釋：

好了，命令以及常用參數(shù)都介紹了，一起解釋下上面執(zhí)行的效果，通過DIFFS 是1 就可以看出主從的表數(shù)據(jù)不一致。怎么不一致呢？ 通過指定—replicate=test.checksums 參數(shù)，就說明把檢查信息都寫到了checksums表中。

進(jìn)入備庫(kù)（10.8.23.208）中查看checksums表的信息：

進(jìn)入備庫(kù)（10.8.23.210）中查看checksums表的信息：

通過上面找到了這些不一致的數(shù)據(jù)，如何修復(fù)呢？利用另外一個(gè)工具 pt-table-sync。

高效的同步MySQL表之間的數(shù)據(jù)，他可以做單向和雙向同步的表數(shù)據(jù)。他可以同步單個(gè)表，也可以同步整個(gè)庫(kù)。它不同步表結(jié)構(gòu)、索引、或任何其他模式對(duì)象。所以在修復(fù)一致性之前需要保證他們表存在。接著上面的復(fù)制情況，主庫(kù)和從庫(kù)的aaa表數(shù)據(jù)不一致，需要修復(fù)。

參數(shù)解釋：

命令介紹完了，一起解釋下執(zhí)行的效果：通過（--print）打印出來了修復(fù)數(shù)據(jù)的sql語句，可以手動(dòng)的去從行執(zhí)行，讓他們數(shù)據(jù)保持一致性。那能否直接執(zhí)行？當(dāng)然可以，通過(--execute)

沒發(fā)現(xiàn)任何異常，然后檢查主從數(shù)據(jù)的一致性：

主庫(kù)（10.8.23.209）數(shù)據(jù)：

從庫(kù)（10.8.23.208）數(shù)據(jù)：

從庫(kù)（10.8.23.210）數(shù)據(jù)：

OK，數(shù)據(jù)已經(jīng)保持一致了。

不過建議還是--print 打印出來的好，這樣就可以知道那些數(shù)據(jù)有問題，可以人為的干預(yù)下。

不然直接執(zhí)行了，出現(xiàn)問題之后不好處理?？傊€是在處理之前做好數(shù)據(jù)的備份工作。

網(wǎng)頁名稱：mysql怎么看主備 mysql 查看主備命令
分享URL：http://muchs.cn/article40/docoheo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供域名注冊(cè)、網(wǎng)站制作、網(wǎng)站維護(hù)、標(biāo)簽優(yōu)化、ChatGPT、關(guān)鍵詞優(yōu)化

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)