Mysql 數據庫的同步(master slave) 詳解

waysnow

2008年12月10日 星期三 10:36

####原理######## mysql 的數據同步，在 mysql 官方網站文檔上，叫 replication 字面是重作的意思，意譯就是同步了。 mysql 為了實現 replication 必須打開 bin-log 項，也是打開二進制的 mysql 日志記錄選項。 mysql 的 bin log 二進制日志，可以記錄所有影響到數據庫表中存儲記錄內容的 sql 操作，如 insert / update / delete 操作，而不記錄 select 這樣的操作。因此，我們可以通過二進制日志把某一時間段內丟失的數據可以恢復到數據庫中，( 如果二進制日志中記錄的日志項，包涵數據庫表中所有數據，那么，就可以恢復本地數據庫的全部數據了 ) 而這個二進制日志，如果用作遠程數據庫恢復，那就是 replication 了。 這就是使用 replication 而不用 sync 的原因。 這也是為什么要設置 bin-log = 這個選項的原因。 在同步過程中，最重要的同步參照物，就是同步使用那一個二進制日志文件，從那一條記錄開始同步。 下面的官方文檔，說明如何安裝與設置 mysql 同步操作，當然，英文版 http://dev.mysql.com/doc/mysql/en/Replication_HOWTO.html 以下是中文翻譯： 　1. 首先，你應該有兩個或兩個以上的 mysql 數據庫服務器，版本最好是在 3.3 以上 ( 當然啦，兩個服務器不一定是兩臺機器，一臺機器上安裝兩個 mysql 服務是可以的，同時，如果你對 mysql replication 原理十分精通的話，你甚至可以在一個 mysql 服務的兩個不同數據庫 database 之間作同步，看有沒有需要了)　 說明： 這兩個服務器一般設置一個為 主服務器，或叫源服務器， master mysql server, 另一臺或其他多臺就是 replication slave 同步從服務器了。一臺 slave 與多臺 slave 設置方法是一樣的，這樣你就可以作類似數據庫集群了 2. 設置可訪問 mysql 帳號，操作以英文為準 說明： 2.1 mysql 帳號一般設置為限定 ip 訪問，以保障安全性 2.2 mysql 帳號一般在 master 與 slave 設置為相同帳號，同時是遠程可訪問 2.3 特別注意，如果你用 linux / unix 操作系統(tǒng)，那一定要注意一下防火墻 firewall 有沒有限制 mysql 遠程訪問，如果是，最好是打開遠程訪問端口，并作好訪問 ip 限制 2.4 由于 my.cnf 中要明文存儲 mysql 帳號密碼，請注意保護 my.cnf 不讓其他用戶訪問到( 看來要向 mysql 說明下以后用密碼存 passwd ) 3. 第三步，當然是設置兩個服務器要同步的數據庫為同樣的數據庫了 這里有一些技巧，包括 mysql 的 sql 指令說明下 3.1 方法之一，就是英文說明中說的，先鎖定數據庫讀寫功能 ( 其實最好是停止 mysqld 服務，再作備份) 然后用 tar 備份數據庫目錄，轉到 slave 服務器相同數據目錄中 　　 3.2 方法之二，使用 mysql studio 這樣的實用工具，直接使用 mysql studio 的 backup database 工具把數據庫同步 　　 3.3 以上兩種方法都是 master 數據庫中有不少數據記錄，按上兩 種方法得到 master 與 slave 有相同數據庫與數據記錄。 而第三種方法，則是適合于新建數據庫的情況，特別適合于 master 與 slave 在 my.cnf 已經設置好 replication 關系 （但 未指定 database 同步數據庫 ) 的情況: 這就是使用　mysql 的 sql 語句 load table from master 與 load data from master; load table from master 可以從 master 數據庫把表結構復制到 slave 數據庫中，這樣可以建立同步的表。 load data from master 是從 master 數據庫把數據導入到 slave 數據表中，條件是 master 從一開始安裝運行就使用了 bin-log參數而保存有二進制日志 　　 技巧： 如果你使用 win2k 的 mysql 那么，你可以下載 mysql.com 出的官方 mysql administrator 實用程序，直接在 mysql administrator 中即可配置 master 與 slave ，同時也可以配置 query-cache 喲 在 master 的 my.cnf ( 如果是 win32 那就是 my.ini ) 增加 [mysqld] log-bin=database_bin_log #記錄日志的數據庫 binlog-do-db=database #binlog-ignore-db=db_name //不記錄日志的數據庫 server-id=1 　　 注意，上面的 log-bin = 中的 等號 = 是不可少的 　 在 slave 的 my.cnf 修改 [mysqld] server-id=2 # 如果有多個 slave 就改為不重復的 id 就好，在mysql 4.1 中，這個已經取消了 master-host=10.10.10.22 master-user=backup #同步用戶帳號 master-password=1234 master-port=3306 master-connect-retry=60 #預設重試間隔60秒 replicate-do-db=test # 告訴slave只做 test 數據庫的更新 bin-log = database_bin_log 5. 檢查 master 與 slave 配置狀態(tài),使用 show master status; 與 show slave status; 這兩個 SQL 指令即可在 master 與 slave 查看配置狀態(tài)。這里有兩個狀態(tài)變量十分重要 　　mysql > SHOW MASTER STATUS; +—————+———-+————–+——————+ | File　　 | Position | Binlog_Do_DB | Binlog_Ignore_DB | +—————+———-+————–+——————+ | mysql-bin.003 | 73　　| test　　 | manual,mysql　 | +—————+———-+————–+——————+ 注意 File 是 mysql-bin.003 而 position 是 73 這個文件是對 test 這個 數據庫的二進制日志記錄，記錄數據變化的當前記錄條數是 73 前面我們說過，二進制日志記錄著某個數據庫所有數據記錄變化的 sql 操作語句，如 insert / update / delete 等，正是因為如此，在 slave 同步 mysql 數據庫操作時，其實是讀取master 中這個 mysql-bin.003 二進制日志中的 sql 操作，同在 slave 中執(zhí)行這些 sql 操作，所以，同步成功有以下幾個條件 1. master 與 slave 有相同的數據庫表結構，最好 database name 也一樣 ( 可以設置為不一樣的 database name) 2. master 與 slave 有相同的初始數據記錄，保證同步操作開始后兩者數據一致 3. master 必須使用 bin-log 二進制日志記錄 ( 推薦 slave 也使用 bin-log ) 4. slave 是從 master 的 bin-log 是讀取 sql 記錄來同步，所以，從哪一條 log 開始讀取很重要 ( 下面的第 7 條操作，就是保證 slave 能與 master 保持相同的記錄讀取，并讀取正確的 bin-log 日志文件 ) 7:在 slave 執(zhí)行下面 sql 操作 (使主從數據同步，slave從master的點開始同步) mysql> CHANGE MASTER TO ->　 MASTER_HOST=’master_host_name’, ->　 MASTER_USER=’replication_user_name’, ->　 MASTER_PASSWORD=’replication_password’, ->　 MASTER_LOG_FILE=’recorded_log_file_name’, ->　 MASTER_LOG_POS=recorded_log_position; 這里，把 上面的 redcorded_log_file_name 改為 mysql-bin.003 而 recorded_log_position 改為 73 （特別注意喲，這是關系關鍵） ################################## 配置文件： master: #日志文件的名稱，這里可以制定日志到別的目錄 如果沒有設置則默認主機名的一個日志名稱 log-bin=myfk_bin_log #記錄日志的數據庫 binlog-do-db=myfookoo #binlog-ignore-db=db_name //不記錄日志的數據庫 max_binlog_size = 104857600 #日志的大小，超出會自動生成一個新的 slave: log-bin=mysql-bin server-id=2 master-host=172.17.10.19 master-user=slave master-password=fookoo_MS master-connect-retry=60 #如果從服務器發(fā)現主服務器斷掉，重新連接的時間差 replicate-do-db=myfookoo #備份的數據庫