MySQL Master-Slaver Replication 讓資料庫資料有備援

重新複習一下 資料庫同步 – MySQL + replication

Master / Slaver 架構就是要讓 MySQL 資料庫系統有著備援的保障

基本運作方式就是，MySQL  Master 這台上只要有新增刪除修改，就會記錄在 binlog 檔裡，這時 Slaver 就可以透過 Master 授權的帳號去同步資料 ( Replication )，這是單向的。雙向的話可以靠 MySQL-MMM (但架構上主機要求的台數比較多)。

其它應用則可以使用 MySQL-proxy ，讓 Master 作寫入工作，而 Slaver 就只作讀取，提高效能

實作的環境 : 這次我拿 discuz 這個 opensource 的 論壇套件 作測試

Master                                  Slaver
--------------------------------------------------------
CentOS 6.5                              CentOS 6.4
PHP 5.4.27                              PHP 5.4.33
MySQL 5.5.37                            MySQL 5.5.40
Discuz X3.2                             Discuz X3.2
ip:10.10.10.137                         ip: 10.10.10.135

Master 端

編輯 /etc/my.cnf

# 在 [mysqld] 加入
[mysqld]
# 主機 ID，不可以重複，通常 master 為 1
server-id=1
log-bin=mysqld-bin
# Log 的格式 ROW,STATEMENT,MIXED
binlog_format=MIXED
# 只 binlog 某個資料庫
binlog-do-db=discuz

其它

# binlog 多個資料庫
binlog-do-db=discuz,test2
# 或
binlog-do-db=discuz
binlog-do-db=test2
# binlog 全部資料庫，但不要 test
binlog-ignore-db=test
# 假如有 InnoDB，且有用 transaction 的話，就設定如下
innodb_flush_log_at_trx_commit=1
sync_binlog=1
# 引用 http://xyz.cinc.biz/2014/10/mysql-replication.html
# sync_binlog,設定大於等於0的數字,控制每幾次將binary log寫入硬碟,0:系統自己控制,1:最安全但效能較低
sync_binlog = 1
# innodb_flush_log_at_trx_commit,可設定0、1、2,
# 0:每秒將 log buffer 寫入 log file,並flush to disk,但在 transaction commit 時不做任何事.
# 1:預設值,transaction commit 時將 log buffer 寫入 log file，,並flush to disk.
# 2:transaction commit 時將 log buffer 寫入 log file,不過flush to disk是每秒執行一次.
innodb_flush_log_at_trx_commit=1

重啟資料庫

service mysqld restart

登入 mysql 設定授權給 slaver 的帳號

GRANT REPLICATION SLAVE ON *.* TO 'replslave'@'%' IDENTIFIED BY 'replpass';
FLUSH PRIVILEGES;

暫時鎖定，無法寫入

FLUSH TABLES WITH READ LOCK;

查看狀態

SHOW MASTER STATUS;

mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysqld-bin.000005 | 8536     |      discuz  |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

記住 bin 檔版號，及 Position 號，這是要給 Slaver 用的，讓 Slaver 知道從那裡開始同步

再來 dump 出資料庫，這是要 copy 到 slaver 還原的

mysqldump --opt -u root -p discuz > discuz.sql

將 discuz.sql copy 到 slaver 去

Slaver 端

編輯 /etc/my.cnf

[mysqld]
# 通常就 2 以上的數字
server-id=2
# 只同步某個資料庫
replicate-do-db=discuz
# slaver ip，可以讓 master 使用 show slave hosts 得知 slaver IP 是多少
report-host=10.10.10.135

其它

# 忽略某個資料庫
replicate-ignore-db=test
# 只同步某個 table
replicate-do-table=test.test
# 也可以用 wildcard，% 就是所有資料表
replicate_wild_do_table=test.%
# 與 Master 斷線後，重新嘗試連接的時間(sec)(預設 60)
master-connect-retry=60

重啟 MySQL

service mysqld restart

登入 slaver 的 mysql

假如有啟用 slave 就先停止，及清除之前的連線

stop slave; reset slave all;

再匯入從 master copy 過來的 all.sql

mysql -u root -p discuz < discuz.sql

設定 slaver 與 master 溝通

設定 master 的 ip、帳號、密碼、binlog 序號、及 position

CHANGE MASTER TO
 MASTER_HOST='10.10.10.137',
 MASTER_USER='replslave',
 MASTER_PASSWORD='replpass',
 MASTER_LOG_FILE='mysqld-bin.000005',
 MASTER_LOG_POS=8536;

啟動 slave

start slave;

查看狀態

show slave status\G;

在  slaver 看到這兩個參數都是 Yes 就代表成功設定了

Slave_IO_Running：Yes
Slave_SQL_Running：Yes

回到 Master 端，將鎖定解開

unlock tables;

測試對 Master 寫入，看 Slaver 會不會同步

QA : 如果 master restart mysql 時，slaver 會出現如下，不過同步還是運作正常的，slaver 也 restart 亦同

141014 11:01:12 [Note] Slave: received end packet from server, apparent master shutdown:
141014 11:01:12 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysqld-bin.000004' at position 312099
141014 11:01:12 [ERROR] Slave I/O: error reconnecting to master '[email protected]:3306' - retry-time: 60  retries: 86400, Error_code: 2003
141014 11:02:12 [Note] Slave: connected to master '[email protected]:3306',replication resumed in log 'mysqld-bin.000004' at position 312099