AWS 筆記 - 透過 Script 半自動化處理 MySQL Replication Error

用了 AWS RDS 一陣子了，之前一直沒搞懂為何 call mysql.rds_skip_repl_error; 一次只能清一條 Error ，直到另一台 MySQL Slave Server 出錯，變成也要手動清除錯誤訊息時，才搞懂原理就是這樣，一次只能清一條。

所以，對於 MySQL Replication Error 不知不覺就生了兩種 script 來應付，一種是 RDS 當 MySQL Replication Slave 用的，另一個則是一般 MySQL Replication Slave Server 用的：

處理 AWS RDS MySQL Replication Slave Servers:

#!/bin/sh
a=0
while [ $a -lt 1000 ]
do
        check=`echo "SHOW SLAVE STATUS \G" | mysql -h YOUR_AWS_RDS_SERVER -u root -ppassword | grep "Last_SQL_Error:" | grep -c "Could not execute"`
        if [ $check -eq 0 ] ; then
                break
        fi
        echo "CALL mysql.rds_skip_repl_error;" | mysql -h YOUR_AWS_RDS_SERVER -u root -ppassword
        a=$(( $a + 1 ))
done

處理 MySQL Replication Slave Servers:

#!/bin/sh

a=0
while [ $a -lt 2000 ]
do
        check=`echo "SHOW SLAVE STATUS \G" | mysql -h YOUR_MYSQL_SLAVE_SERVER -u root -ppassword | grep "Last_SQL_Error:" | grep -c "Error "`
        if [ $check -eq 0 ] ; then
                break
        fi
        check=`echo "STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;" | mysql -h localhost -u root -ppassword`
        a=$(( $a + 1 ))
        sleep 3
done

以上是用在 MySQL Server 彼此算異質系統，只能先透過這招頂替了。

[Linux] 透過 MySQL Replication 之 Master Slave 切換進行 DB server 移機 @ Ubuntu 12.04

事情的緣由是想要進行 DB server 的升級，做法有不少種，而最重要的是要將 down time 降低。由於未來有考慮把 DB server 移到 AWS RDB 上頭，參考 AWS RDB 的 import/export 方式，故選擇從 replication slave 來摸摸。

建立 Replication Slave 的流程：

1. 將原先的 db1 server 調整成可以成為 master db 的功能
   
   • $ vim /etc/mysql/my.cnf
     
     • 拿掉 bind-address 127.0.0.1
     • 指定 server-id = 1
     • 指定 log_bin = /var/log/mysql/mysql-bin.log
     • innodb_flush_log_at_trx_commit=1
     • sync_binlog=1
   • $ sudo service mysql restart
2. 在 master db 上建立 replication slave 存取權限
   
   • master:mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl_password';
3. 在 master db 上匯出資料
   
   • master:mysql> FLUSH TABLES WITH READ LOCK;
   • master:mysql> SHOW MASTER STATUS;
   • $ mysqldump -u root -p --all-databases --lock-all-tables > all.sql
   • master:mysql> UNLOCK TABLES;
   • 想辦法把 all.sql 弄到 slave db server
4. 在 db2 server 設定可以成為 slave db 功能
   
   • $ vim /etc/mysql/my.cnf
     
     • 拿掉 bind-address 127.0.0.1
     • 指定 server-id = 2
     • 指定 log_bin = /var/log/mysql/mysql-bin.log
   • $ sudo service mysql restart
5. 在 db server 匯入 master db 的資料
   
   • 從 master db server 取得 all.sql
   • $ mysql -u root -p < all.sql
6. 在 slave db 設定 master db 資訊
   
   • slave:mysql> CHANGE MASTER TO
     MASTER_HOST='MasterDBIP',
     MASTER_USER='repl',
     MASTER_PASSWORD='repl_password',
     MASTER_LOG_FILE='File',
     MASTER_LOG_POS=Position;
   • slave:mysql> START SLAVE;
   • slave:mysql> SHOW MASTER STATUS;
   • slave:mysql> SHOW SLAVE STATUS;

如此一來，就可以看到 Slave 跟 Master 一直保持同步了，可以用一些指令，如 SELECT count(*) FROM TABLE; 來比較看看。

以上就是架設 MySQL Replication Slave 的流程，接下來是進行 DB Server 移機的過程，目的就是將上述的 db1 server 下線，並改用 db2 server 當 master，有點像是把 switch slave server to master 的味道，過程：

1. 鎖住 db1 server
   
   • db1:mysql> FLUSH TABLES WITH READ LOCK;
2. 確認 db2 server 資料有 sync
3. 在 db2 server 取消 slave 行為
   
   • db2:mysql> STOP SLAVE;
   • db2:mysql> RESET SLAVE;
4. 將所有相關的 application 的 db 連線導到 db2 server

透過上述 MySQL Replication 移機的方式，所造成的 down time 會小很多。另外，更好的解法是先處理好 primary key （例如 2N 與 2N+1）的問題，然後先把 application db 切到 db2，過一陣子後再把 db1 shutdown ，就可以避免資料遺失問題。

最後補充一下，在 Master DB Server 新增帳號時，在 Slave DB server 並沒有 sync ，解法除了一開始在設定 Slave 前就把帳號建立好外，另一種解法就是讓 Slave DB 先暫停 Slave 角色，接著新增完帳號再重回 Slave 角色，而新資料又可以從 Master sync 回來。

1. slave:mysql> STOP SLAVE;
2. slave:mysql> 新增使用者;
3. slave:mysql> START SLAVE;

關於新增帳號的問題是在於要把 db2(Slave) 轉成 Master 時，需要更新帳號能夠存取 db2 的權限，然而透過上述 mysqldump -u root -p --all-databases --lock-all-tables 更新方式，db2 裡的資料都是以 db1 的環境去設定的。