MySQL Replication

How to Set Up Replication

目的: 我們假定A中心有一台Server Master1 ,而B中心有一台Server Master2. 為了方便我們在更新 Master1 的資料庫時, B中心的 Master2 資料庫也能同時更新而做到資料同步.

實驗環境: Debian GNU/Linux Etch 4.0r4 with kernel 2.6.18

Part1: 安裝MySQL 5.0

步驟一 : 安裝 MySQL (假定 Master1 & Master2 都還沒有安裝 MySQL database)

使用指令
rider@ocean:~$ sudo apt-get install mysql-server-5.0 mysql-client-5.0

步驟二 : 為了要確保同步設定有效,我們讓MySQL可以在該網路介面上(interface)任意開啟監聽的埠(port)並藉由編輯 /etc/mysql/my.cnf 將 bind-address註釋起來.

使用指令
rider@ocean:~$ sudo vim /etc/mysql/my.cnf

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address            = 127.0.0.1

步驟三 : 重新啟動 MySQL

使用指令
rider@ocean:~$ sudo su
root@ocean:~$ /etc/init.d/mysql restart

步驟四 : 檢查MySQL所開啟的連接埠

使用指令
root@ocean:~$ netstat -tap | grep mysql

tcp        0      0 *:mysql                 *:*                     LISTEN      21678/mysqld

步驟五 : 設定 root@localhost 的MySQL使用者密碼

使用指令
root@ocean:~$ mysqladmin -u root password yourrootsqlpassword

/* @Master1 */
步驟六 : 設定 root@…的MySQL使用者密碼

使用指令
root@ocean:~$ mysqladmin -h master1.nchc.org.tw -u root password yourrootsqlpassword

步驟七 : 設定一組同步使用者 slave2_user 給 Master2 能存取 Master1 的MySQL 資料庫的權限

使用指令
root@ocean:~$ mysql -u root -p
-> 登入MySQL

Enter password: rootmysqlpasswd
-> 輸入root 的 MySQL 登入密碼

GRANT REPLICATION SLAVE ON *.* TO 'slave2_user'@'%' IDENTIFIED BY 'slave2_password';
FLUSH PRIVILEGES;
quit;
-> 設定使用者: slave2_user 的同步權限並給予密碼

/* @Master2 */
步驟八 : 設定 root@…的MySQL使用者密碼,與設定一組同步使用者 slave1_user 給 Master1 能存取 Master2 的MySQL 資料庫的權限(重複步驟六&七: 設定 Master1 MySQL的方法)

使用指令

root@ocean:~$ mysqladmin -h master1.nchc.org.tw -u root password yourrootsqlpassword

root@ocean:~$ mysql -u root -p
-> 登入MySQL

Enter password: rootmysqlpasswd
-> 輸入root 的 MySQL 登入密碼

GRANT REPLICATION SLAVE ON *.* TO 'slave1_user'@'%' IDENTIFIED BY 'slave1_password';
FLUSH PRIVILEGES;
quit;
-> 設定使用者: slave1_user 的同步權限並給予密碼

注意事項

我們假設我們已經在 Master1 建夠好一個 exampledb 並且存有資料表與相關資料內容. 接著我們要做的就是設定 exampledb 的副本到 Master2 , 接著也可將 Master2 的 exampledb 複製喔份回去Master1. 在設定資料庫複製 (MySQL Replication) 的同時我們先在 Master2 上建一個空的資料庫:exampledb.

/* @Master2 */
使用指令

root@ocean:~$ mysql -u root -p
-> 登入MySQL

CREATE DATABASE exampledb;
quit;

Part2: 設定資料庫複製(Replication)

首先我們要透過編輯 /etc/mysql/my.cnf 來完成 master-master replication 的設定, 而 master-master replication 最重要的兩個設定選項就是 auto_increment_increment & auto_increment_offset

auto_increment_increment: 控制連續性增量的AUTO_INCREMENT值.
auto_increment_offset: 決定AUTO_INCREMENT欄位的起始值.

假設我們有 N 個 MySQL nodes (本範例是兩個nodes,所以 N=2),因此 "auto_increment_increment" 在每各node的值都是 2 , 而每各node都必須給 "value for auto_increment_offset" 一各不同的值(1, 2, ..., N). 而接下來我們便要開始設定本範例中的兩各 MySQL nodes.

/* @Master1 */
步驟一 : 編輯 /etc/mysql/my.cnf -> 從[mysqld]開始新增以下的選項,並且將有衝突的相關選項給註釋掉.

使用指令

root@ocean:~$ vim /etc/mysql/my.cnf

[...]
[mysqld]
#
# * Basic Settings
#

#
# * IMPORTANT
#   If you make changes to these settings and your system uses apparmor, you may
#   also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.
#
server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1

master-host = 192.168.0.101
master-user = slave1_user
master-password = slave1_password
master-connect-retry = 60
replicate-do-db = exampledb

log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db = exampledb

relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

expire_logs_days        = 10
max_binlog_size         = 500M
[...]

步驟二 : 重新啟動 MySQL 使用指令

root@ocean:~$ /etc/init.d/mysql restart

/* @Master2 */
步驟三 : 對 Master2 重複相同的設定步驟(步驟一 ~ 步驟二)-> 編輯 /etc/mysql/my.cnf -> 從[mysqld]開始新增以下的選項,並且將有衝突的相關選項給註釋掉.再重新啟動MySQL

使用指令

root@ocean:~$ vim /etc/mysql/my.cnf

[...]
[mysqld]
#
# * Basic Settings
#

#
# * IMPORTANT
#   If you make changes to these settings and your system uses apparmor, you may
#   also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.
#
server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2

master-host = 192.168.0.100
master-user = slave2_user
master-password = slave2_password
master-connect-retry = 60
replicate-do-db = exampledb

log-bin= /var/log/mysql/mysql-bin.log
binlog-do-db = exampledb

relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

expire_logs_days        = 10
max_binlog_size         = 500M
[...]

root@ocean:~$ /etc/init.d/mysql restart

/* @Master1 */
步驟四 : 我們將鎖定在 Master1 上的 exampledb 資料庫, 且查詢 Master1 的主機狀態資訊而將 exampledb 給 dump(傾倒)出來 (因為我們待會要將傾倒出來的 exampledb 匯入到 Master2去,這樣兩各nodes的資料庫都將會有相同的資料), 最後將 exampledb 資料庫給解鎖(unlock)以便後續使用.

使用指令

root@ocean:~$ mysql -u root -p

USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

接著應該會顯示如下的資訊 (請將他記下來之後會有用)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000009 |       98 | exampledb    |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>