MySQL Replication Master / Slaver 讓資料庫資料有備援MySQL Replication 使用 SSL 連線同步資料

Percona Toolkit 是一套管理 MySQL 工具,我會用其中的 pt-table-checksum 及 pt-table-sync 來檢查、修復 MySQL Replication 可能產生不同步的資料 (master 多的,而 slaver 沒有)

首先用 pt-table-checksum 檢查,它會需要資料庫記錄檢查結果

前提它的驗證方式有 (查看)

METHOD       USES
===========  =============================================
processlist  SHOW PROCESSLIST
hosts        SHOW SLAVE HOSTS
cluster      SHOW STATUS LIKE 'wsrep\_incoming\_addresses'
dsn=DSN      DSNs from a table
none         Do not find slaves

但我試成功的只有 dsn=DSN 方式,底下就用這方式介紹

這方式要有一個資料庫使用者,它的權限如下
(SELECT 是要能讀 discuz 資料庫的,其它為 global)
(這個使用者要在 master 及 slaver 都建立)

GRANT SELECT, LOCK TABLES, PROCESS, SUPER, REPLICATION CLIENT  ON *.* TO 'replcheck'@'%' IDENTIFIED BY 'replpass';

dsn 也要有一個 table,記錄著讓 pt-table-checksum 指令連線資訊,所以在 master 端建立 (我是建在 percona 資料庫裡)

create database percona;
use percona;
# 在 master 端建立此 table
CREATE TABLE `dsns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `dsn` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);
# 並 insert 連線資料
INSERT INTO `dsns` (`id`, `parent_id`, `dsn`) VALUES (1, NULL, 'h=10.10.10.135,P=3306,u=replcheck,p=replpass');

master 端 下載、編譯 percona-toolkit

wget http://www.percona.com/redir/downloads/percona-toolkit/LATEST/percona-toolkit-2.2.11.tar.gz
tar zxvf percona-toolkit-2.2.11.tar.gz
cd percona-toolkit-2.2.11
perl Makefile.PL
make

我沒有 install,編譯完的工具會在 /root/percona-toolkit-2.2.11/bin 裡

master 端 開始檢查

/root/percona-toolkit-2.2.11/bin/pt-table-checksum --recursion-method=dsn='h=localhost,D=percona,t=dsns'  --nocheck-replication-filters --no-check-binlog-format --databases=discuz --replicate=percona.checksums --host=localhost --port=3306 --user=root --password=123456 
/root/percona-toolkit-2.2.11/bin/pt-table-checksum --recursion-method=dsn='h=localhost,D=percona,t=dsns'  --nocheck-replication-filters --no-check-binlog-format --databases=discuz --replicate=percona.checksums --host=localhost --port=3306 --user=root --password=123456 --replicate-check-only 
--recursion-method 使用 dsn,h=就是 master IP,我用 localhost,D= 就 pt 資料庫,t=  table dsns 資料表,這就是去撈 pt.dsns 裡的連線資訊 (文章上面提及)
--databases 指定 discuz 資料庫
--replicate= 將檢查結果記錄到 discuz 資料庫的 checksums 資料表裡 (它會自建)
--host= master IP
--port= master mysql port
--user= master mysql root
--password= master mysql password
--replicate-check-only 第一次執行檢查全部資料表,第二次執行不用這參數就只顯示有問題的 table

master 端 開始修復

/root/percona-toolkit-2.2.11/bin/pt-table-sync --replicate=percona.checksums --databases=discuz h=10.10.10.135,u=replcheck,p=replpass h=10.10.10.137,u=replcheck,p=replpass --charset=utf8 --print
--replicate= 指定 discuz.checksums 資料表
--databases= discuz 資料庫
第一個 h= 為 master 資訊
第二個 h= 為 slaver 資訊
--charset=utf8 確認資料庫是 utf8,dump 出來的也是 utf8,才不會有亂碼問題
--print 為顯示差異 (sql 語法),再手動貼上 slaver 上執行即可。如果要自動就把 --print 換成 --execute (但 replcheck 權限要足夠)

我這邊再測試一個情況,不小心在 slaver 寫入資料,這時後是沒什麼事情,等到 master 也寫入資料時,同步一定出錯, slaver 的 status 就會有 error

             Slave_IO_Running: Yes
            Slave_SQL_Running: No

               Last_SQL_Error: Error 'Duplicate entry '14' for key 'PRIMARY'' on query. Default database: 'discuz'. Query: 'INSERT INTO pre_forum_thread SET `fid`='2' , `posttableid`='0' , `readperm`='0' , `price`='0' , `typeid`='0' , `sortid`='0' , `author`='admin' , `authorid`='1' , `subject`='test17' , `dateline`='1413356062' , `lastpost`='1413356062' , `lastposter`='admin' , `displayorder`='0' , `digest`='0' , `special`='0' , `attachment`='0' , `moderated`='0' , `status`='' , `isgroup`='0' , `replycredit`='0' , `closed`='0''

此時一定無法 pt-table-checksum,所以要先處理狀態兩個都是 yes 的

(或者在 master 未寫入資料前,是可以 pt-table-checksum 及 pt-table-sync 的)

在 master 端,查看版號

 SHOW MASTER STATUS;

在 slaver 端設定重新同步

stop slave;
CHANGE MASTER TO  MASTER_HOST='10.10.10.137',  MASTER_USER='replslave',  MASTER_PASSWORD='replpass',  MASTER_LOG_FILE='mysqld-bin.000009',  MASTER_LOG_POS=2201973;
start slave;

就成功再讓同步正常運作,此時就可以用 pt-table-checksum 與 pt-table-sync 將 master 與 slaver 的差異寫回 slaver

假如一情況,當 master 的資料庫掛了連不到, slaver 是不是就變成主站了,所有資料也都寫到 slaver,那當 master 回復時, slaver 的資料要回到 master 是不是只有 all dump data and restore 一途? ,我嘗用 pt-table-sync 處理差異部份,似乎不行 ? 還是我試不出來 !!!

所以只能把 slaver 變成 master,舊 master 降級成 slaver

舊 master

flush logs;

新 master (舊 slaver)

slave stop;reset slave all;

然後再重作 mysql master-slaver replication 環境

Related posts 相關文章
使用連線控制插件提高 MySQL 安全性
More...
使用驗證插件提高 MySQL 密碼安全性
More...
用 pv 查看 mysql restore 還原資料進度
More...
使用 sysbench 壓力測試 mysql
More...

作者

留言

撰寫回覆或留言

發佈留言必須填寫的電子郵件地址不會公開。