顯示廣告
隱藏 ✕
回列表(←)
Disp BBS guest 註冊 登入(i) 線上人數: 654
首頁(home) 上頁(↑) 下頁(↓) 末頁(end)
看板 KnucklesNote
作者 Knuckles (站長 那克斯)
標題 [MySQL] 設定Master/Slave資料庫備援(Replication)
時間 2018-12-25 Tue. 02:27:37


本文使用的作業系統: CentOS 7，
資料庫使用 MariaDB 5.5 (與 MySQL 5.5 相容)

資料庫可以使用 Master/Slave 架構，
讓主資料庫 Master 主要負責資料寫入的動作，
然後自動將所有更新同步到備援資料庫 Slave
讓備援資料庫 Slave 用來分擔大部份的資料讀取動作

另外也可以當作資料庫的即時備份


主資料庫 Master 設定

修改設定檔 my.cnf
$ sudo vim /etc/my.cnf

# 不同的資料庫要用不用的 id 數字，通常主資料庫就設定為 1
server-id = 1

# 將資料庫的每個寫入指令記錄在 binlog 檔，用來讓備援資料庫同步用
log-bin=mysql-bin

# 設定 binlog 一天就到期自動刪除，免得占用過多硬碟空間
expire-logs-days=1

# 要忽略某些資料庫不要記錄的話可以設定
binlog_ignore_db=test1
binlog_ignore_db=test2

# 每次的寫入指令都會記錄在 binlog，確保資料即時被同步到備援資料庫，會影響效能
sync_binlog = 1

# 有使用 innodb 的話，要設定這個確保資料會寫入硬碟，會影響效能
innodb_flush_log_at_trx_commit = 1


重啟讓設定生效
$ sudo systemctl restart mariadb


在主資料庫新增一個使用者 replication
用來給備援資料庫登入用

先登入mysql指令模式
$ mysql -u root -p

新增使用者，將 rep_password 改為自訂的密碼
MariaDB > CREATE USER 'replication'@'%' IDENTIFIED BY 'rep_password';
MariaDB > GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
MariaDB > FLUSH PRIVILEGES;


備援資料庫 Slave 設定

修改設定檔 my.cnf
$ sudo vim /etc/my.cnf

# 備援資料庫的 id 設為 2
server-id = 2

# 設為唯讀避免非管理者帳號寫入資料
read_only

# 設定 Slave 的IP，讓 Master 可以使用 show slave hosts 顯示有在連線的 Slave
report-host=db2

# 如果要忽略某些資料庫，不同步過來的話可以設定這個
# 多個資料庫的話要分行設定，不能用逗號分隔
replicate_ignore_db=test1
replicate_ignore_db=test2

# 如果要忽略某些資料表，可以設定這個
# 多個talbe要分行設定，不能用逗號分隔
replicate_ignore_table=test1.table1
replicate_ignore_table=test1.table2


重啟讓設定生效
$ sudo systemctl restart mariadb


在 Master 使用 mysqldump 備份整個資料庫

要先將主資料庫完整的複製到備援資料庫後，才能開始同步
在使用 mysqldump 前，要停止資料庫所有的寫入動作，避免之後同步失敗

先將網頁讀取資料庫的帳號設為唯讀 (只有 SELECT 權限)
然後開一個新的ssh連線登入 mysql 指令模式執行
MariaDB > FLUSH TABLES WITH READ LOCK;
將還在記憶體的資料寫進binlog，並鎖定所有資料表

取得目前 binlog 的記錄位置
MariaDB > SHOW MASTER STATUS\G;
*************************** 1. row ***************************
            File: mysql-bin.000059
        Position: 3847499
    Binlog_Do_DB:
Binlog_Ignore_DB:

記下 File 和 Position，之後在 Slave 要設定從這邊開始同步

注意只要離開 mysql 指令模式後，鎖定就會解開了
所以要用另一個連線來執行 mysqldump

在另一個ssh連線執行 mysqldump 備份整個資料庫
$ mysqldump -u root -p --all-databases  --single-transaction --flush-logs > dump_master.sql

或是只備份指定的資料庫 my_db
$ mysqldump -u root -p mh_db --single-transaction --flush-logs > dump_master.sql


備份完成後就可以解除鎖定了，回到 mysql 指令模式的連線執行
MariaDB > UNLOCK TABLES;
或是用 exit; 離開也可以

將網頁讀取資料庫的帳號解除唯讀

使用 scp 將 dump_master.sql 傳給 Slave 主機
$ sudo scp dump_master.sql root@db2:/root/

以上步驟也可以寫成 Shell script
#!/bin/bash

myUser="your_username"
myPass="your_password"
myDB="your_db"

backup_dir="/root/"
dump_file=$backup_dir"dump_master.sql"

echo "使用 mysqldump 備份資料庫 存成 dump_master.sql 檔"
mysqldump -u $myUser -p$myPass $myDB  --single-transaction --flush-logs > $dump_file

echo "使用 scp 將 dump_master.sql 傳送給 Slave 主機"
scp $dump_file root@db2:$backup_dir

# 執行 SHOW MASTER STATUS 並取出 File 與 Position 的值
QUERY="SHOW MASTER STATUS\G;"
MYSQL_RESULT=$(mysql -u$myUser -p$myPass -e "$QUERY")
FILE=$(echo "$MYSQL_RESULT" | awk '/File/ {print $2}')
POSITION=$(echo "$MYSQL_RESULT" | awk '/Position/ {print $2}')

# 產生 SQL 指令檔傳給 SLAVE 主機
SQL_FILE="change_master.sql"
REP_PASS="rep_password"
SQL="STOP SLAVE; CHANGE MASTER TO MASTER_HOST='db3',
 MASTER_USER='replication', MASTER_PASSWORD='"$REP_PASS"',
 MASTER_LOG_FILE='$FILE', MASTER_LOG_POS=$POSITION; START SLAVE;"
echo $SQL > $SQL_FILE

scp $SQL_FILE root@db2:$backup_dir
其中 awk '/File/ {print $2}' 代表取出含有 File 那行的第二欄


在 Slave 啟動同步功能

將 Master 傳來的資料庫備份檔 dump_master.sql 還原進資料庫
$ mysql -u root -p < dump_master.sql


在 Slave 主機登入 mysql 指令模式，執行
MariaDB > STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='db1',
  MASTER_USER='replication',
  MASTER_PASSWORD='rep_password',
  MASTER_LOG_FILE='mysql-bin.000059',
  MASTER_LOG_POS=3847499;
START SLAVE;

將上面的 db1 改為主資料庫的 hostname 或是 IP 位址
rep_password 改為之前新增的使用者 replication 的密碼
mysql-bin.000059 和 3847499 改為前面記下的 File 和 Position

或是使用 Master 傳來的 SQL 指令檔
$ mysql -u root -p < change_master.sql


看看 Slave 主機有沒有正確在執行同步
MariaDB > SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: db1
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000060
          Read_Master_Log_Pos: 16172358
               Relay_Log_File: db2-relay-bin.000004
                Relay_Log_Pos: 16172642
        Relay_Master_Log_File: mysql-bin.000060
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
.....

若上面的 Slave_IO_Running 和 Slave_SQL_Running 都是 Yes 的話就是成功了


有使用 phpMyAdmin 的話可以在 Master 主機的「伺服器/備援」看到
[圖]

前面兩個指令就是前面提過的
MariaDB > SHOW MASTER STATUS;
MariaDB > SHOW SLAVE HOSTS;

在 Slave 主機的「伺服器/備援」看到
[圖]

其中查看次要伺服器狀態就是前面提過的
MariaDB > SHOW SLAVE STATUS\G;
[圖]

可以過段時間看一下Running是不是 Yes，以及 Errno 是否為0

Slave主機可以關機一下再打開，一樣可以同步到最新的資料

若確認資料庫同步都不會出現錯誤後，就可以改寫 PHP 存取資料庫的程式，
將 SELECT 的 SQL 改為使用備援資料庫

例如是使用 mysqli 來存取資料庫時，原本取得連線的程式為
$mysqli = new mysqli("db1", $user, $pswd, $db_name);
再另外新增一個
$mysqli_slave = new mysqli("db2", $user, $pswd, $db_name);
if($mysqli_slave->connect_errno){ //備援DB連不上時，使用主DB
unset($mysqli_slave);
$mysqli_slave = $mysqli;
}
然後將使用 SELECT 的 SQL 改為使用 $mysqli_slave 這個連線即可


另外要注意讀取型態為 MEMORY 的資料表不要使用 Slave
因為 Slave 主機只要重開機後資料表被清空，就會與主資料庫的不相同了


自動檢查 Slave 主機狀態

寫一個 shell 檔 check_slave.sh 每10分鐘自動檢查 Slave 主機是否有在同步，
若沒有的話產生一個 slave_error 的檔案，讓 PHP 可以知道 Slave 掛了不要使用
#!/bin/bash

myUser="your_username"
myPswd="your_password"

myStatus=(`mysql -u$myUser -p$myPswd -e "SHOW SLAVE STATUS\G;" |egrep "Slave_IO_Running|Slave_SQL_Running" |awk '{print $NF}'`)
if [ "${myStatus[0]}" == "No" -o "${myStatus[1]}" == "No" ]; then
    touch slave_error
    scp slave_error your_admin_id@your_web_host:/var/www/your_path/
fi
其中使用 |egrep 將 SLAVE STAUS 中的 Slave_IO_Running 與 Slave_SQL_Running 這兩行抓出來
使用 |awk '{print $NF}' 可以將那兩行的最後一個字串抓出來
若 Slave 主機正常的話，抓出來的結果為兩行都是「Yes」，有錯的話其中一行會是「No」
存到 myStatus 後可使用 ${myStatus[0]} 與 ${myStatus[1]} 來判斷是否有「No」
有「No」的話用 touch 產生一個 slave_error 檔
用 scp 將 slave_error 檔傳到 web 主機的網頁目錄

在 /etc/crontab 每十分鐘執行一次檢查
*/10 * * * *  your_admin_id    /home/your_admin_id/check_slave.sh


在 PHP 連線資料庫時多加個判斷
$slave_error = false;
if(file_exists(slave_error) && time()-filemtime(slave_error)<600){
$slave_error = true;
}


參考:
MySQL Replication 主從式架構設定教學

--
※ 作者: Knuckles 時間: 2018-12-25 02:27:37
※ 編輯: Knuckles 時間: 2023-11-19 19:05:50 (台灣)
※ 看板: KnucklesNote　文章推薦值: 1 目前人氣: 0 累積人氣: 3189　
分享網址: 複製 已複製
(￣︶￣)ｂ Leon 說讚！
r)回覆 e)編輯 d)刪除 M)收藏 ^x)轉錄 同主題: =)首篇 [)上篇 ])下篇