= MySQL Replication =
== How to Set Up Replication ==
目的:
我們假定A中心有一台Server Master1 ,而B中心有一台Server Master2. 為了方便我們在更新 Master1 的資料庫時, B中心的 Master2 資料庫也能同時更新而做到資料同步.[[BR]]

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

=== Part1: 安裝MySQL 5.0 ===

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

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

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

使用指令[[BR]]
''rider@ocean1:~$ sudo vim /etc/mysql/my.cnf''[[BR]]
{{{
# 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

使用指令[[BR]]
''rider@ocean1:~$ sudo su''[[BR]]
''root@ocean1:~$ /etc/init.d/mysql restart''[[BR]]

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

使用指令[[BR]]
''root@ocean1:~$ netstat -tap | grep mysql''[[BR]]
{{{
tcp        0      0 *:mysql                 *:*                     LISTEN      21678/mysqld
}}}

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

使用指令[[BR]]
''root@ocean1:~$ mysqladmin -u root password yourrootsqlpassword''[[BR]]

'''/* @Master1 */''' [[BR]]
'''步驟六 :''' 設定 root@master1.nchc.org.tw的MySQL使用者密碼

使用指令[[BR]]
''root@ocean1:~$ mysqladmin -h master1.nchc.org.tw -u root password yourrootsqlpassword''[[BR]]

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

使用指令[[BR]]
''root@ocean1:~$ mysql -u root -p''[[BR]] -> 登入MySQL

''Enter password: rootmysqlpasswd''[[BR]] -> 輸入root 的 MySQL 登入密碼

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

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

使用指令[[BR]]

''root@ocean2:~$ mysqladmin -h master1.nchc.org.tw -u root password yourrootsqlpassword''[[BR]]

''root@ocean2:~$ mysql -u root -p''[[BR]] -> 登入MySQL

''Enter password: rootmysqlpasswd''[[BR]] -> 輸入root 的 MySQL 登入密碼

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

=== 注意事項 ===

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

'''/* @Master2 */''' [[BR]]
使用指令[[BR]]

''root@ocean2:~$ mysql -u root -p''[[BR]] -> 登入MySQL

''mysql>''[[BR]]
''CREATE DATABASE exampledb;[[BR]]quit;[[BR]]''

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

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

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

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

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

使用指令[[BR]]

''root@ocean1:~$ vim /etc/mysql/my.cnf''[[BR]]

{{{
[...]
[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
使用指令[[BR]]

''root@ocean1:~$ /etc/init.d/mysql restart''[[BR]]


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

使用指令[[BR]]

''root@ocean2:~$ vim /etc/mysql/my.cnf''[[BR]]

{{{
[...]
[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@ocean2:~$ /etc/init.d/mysql restart''[[BR]]

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

使用指令[[BR]]

''root@ocean1:~$ mysql -u root -p''[[BR]]

''mysql>''[[BR]]
''USE exampledb;[[BR]]FLUSH TABLES WITH READ LOCK;[[BR]]SHOW MASTER STATUS;[[BR]]''

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

{{{
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>
}}}

說明:[[BR]]
這時暫時還請勿離開 MySQL命令列(MySQL shell). 這時如果下exit指令而離開的話資料庫鎖定(database lock)將會被移除,因為我們必須先做完 MySQL dump(資料庫傾倒). 由於我們還有一各MySQL shell在開啟狀態,所以我們得藉由開啟第二各指令對話窗(command line window)來做 MySQL dump 而產生snapshot.sql並且透過 SCP(Secure Copy) 來傳送至Master2.[[BR]]

'''/* @Master1 */''' [[BR]]
'''步驟五 :''' 在透過第二各指令對話窗(command line window)將資料庫給傾倒出來並SCP過去到遠端的 Master2的 /tmp資料夾暫存後便可關閉視窗二. 亦可離開並關閉第一視窗的 MySQL shell 藉以將資料庫解鎖

使用指令[[BR]]

''root@ocean1:~$ cd /tmp''[[BR]]

''root@ocean1:~$ mysqldump -u root -pyourrootsqlpassword --opt exampledb > snapshot.sql''[[BR]]

''root@ocean1:~$ scp snapshot.sql root@192.168.0.101:/tmp'' (Master2 IP: 192.168.0.101) [[BR]]

'''步驟六 :''' 可以離開並關閉第一各指令對話窗(command line window)的 MySQL shell 藉以將資料庫解鎖

使用指令[[BR]]

''mysql> UNLOCK TABLES;''[[BR]]

''mysql> quit;''[[BR]]

'''/* @Master2 */''' [[BR]]
'''步驟七 :''' 我們可以匯入由 Master1 所 SQL dump 出來的 snapshot.sql 到 Master2 的 MySQL 資料庫

使用指令[[BR]]

''root@ocean2:~$ /usr/bin/mysqladmin --user=root --password=yourrootsqlpassword stop-slave''[[BR]]

''root@ocean2:~$ cd /tmp''[[BR]]

''root@ocean2:~$ mysql -u root -pyourrootsqlpassword exampledb < snapshot.sql''[[BR]]

'''/* @Master2 */''' [[BR]]
'''步驟八 :''' 接著我們去查詢 Master2 的主機狀態並將他紀錄下來待會會用到

使用指令[[BR]]

''root@ocean2:~$ mysql -u root -p''[[BR]]

''mysql> USE exampledb;''[[BR]]

''mysql> FLUSH TABLES WITH READ LOCK;''[[BR]]

''mysql> SHOW MASTER STATUS;''[[BR]]

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

'''/* @Master2 */''' [[BR]]
'''步驟九 :''' 然後我們解除該狀態表格的鎖定,並且透過以下指令來使得 Master2 成為 Master1的從屬(slave)之一

使用指令[[BR]]

''mysql> UNLOCK TABLES;''[[BR]]

''mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave2_user', MASTER_PASSWORD='slave2_password', MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=98;''[[BR]]

'''/* @Master2 */''' [[BR]]
'''步驟十 :''' 最後啟動該從屬(slave)關係

使用指令[[BR]]

''mysql> START SLAVE;''[[BR]]

'''/* @Master2 */''' [[BR]]
'''步驟十一 :''' 檢查該從屬(slave)狀態

使用指令[[BR]]

''mysql> SHOW SLAVE STATUS;''[[BR]]

{{{
mysql> SHOW SLAVE STATUS;
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Slave_IO_State                   | Master_Host   | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File     | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Waiting for master to send event | 192.168.0.100 | slave2_user |        3306 |            60 | mysql-bin.000009 |                  98 | slave-relay.000002 |           235 | mysql-bin.000009      | Yes              | Yes               | exampledb       |                     |                    |                        |                         |                             |          0 |            |            0 |                  98 |             235 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set (0.00 sec)

mysql>
}}}

'''/* @Master2 */''' [[BR]]
'''步驟十二 :''' 最後你可以離開 Master2 的 MySQL shell了. 而此時 Master1 複製到 Master2 的 MySQL replication 也已經設定完成

使用指令[[BR]]

''mysql> quit''[[BR]]

'''/* @Master1 */''' [[BR]]
'''步驟十三 :''' 在 Master1 複製到 Master2 的 MySQL replication 設定完成後,接著便是要設定 Master2 複製到 Master1 的 MySQL replication. 而在這之前我們得先停止在 Master1上的從屬關係,並且反過來讓 Master1 成為 Master2 的從屬.

使用指令[[BR]]

''root@ocean1:~$ mysql -u root -p''[[BR]]

''mysql> STOP SLAVE;''[[BR]]

'''/* @Master1 */''' [[BR]]
'''步驟十四 :''' 請確認你目前在在使用 SHOW MASTER STATUS 的值,並且在 Master2 上執行如下的指令

使用指令[[BR]]

''mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.101', MASTER_USER='slave1_user', MASTER_PASSWORD='slave1_password', MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=783;'' (Master2 IP: 192.168.0.101)[[BR]]

'''/* @Master1 */''' [[BR]]
'''步驟十五 :''' 接著便可以啟動 Master1 上的從屬關係

使用指令[[BR]]

''mysql> START SLAVE;''[[BR]]

'''/* @Master1 */''' [[BR]]
'''步驟十六 :''' 最後便要檢查該從屬狀態

使用指令[[BR]]

''mysql> SHOW SLAVE STATUS;''[[BR]]

{{{
mysql> SHOW SLAVE STATUS;
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Slave_IO_State                   | Master_Host   | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File     | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Waiting for master to send event | 192.168.0.101 | slave1_user |        3306 |            60 | mysql-bin.000009 |                 783 | slave-relay.000002 |           235 | mysql-bin.000009      | Yes              | Yes               | exampledb       |                     |                    |                        |                         |                             |          0 |            |            0 |                 783 |             235 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set (0.00 sec)

mysql>
}}}

'''/* @Master1 */''' [[BR]]
'''步驟十七 :''' 最後便可以離開 MySQL shell

使用指令[[BR]]

''mysql> quit''[[BR]]



Reference:

(1) MySQL Replication: http://dev.mysql.com/doc/refman/5.0/en/replication.html

(2) Setting Up Master-Master Replication With MySQL 5 On Debian Etch: http://www.howtoforge.com/mysql5_master_master_replication_debian_etch

PS: 以上安裝與設定內容係參照reference(2)的操作程序並予以中文化以利後續文件撰寫之用.