前幾天剛剛註冊了博客園，我想寫一些技巧性的教程，今天給大家分享一個MySQL同步常見問題解答。 Q：如果主伺服器正在運行並且不想停止主伺服器，怎樣配置一個從伺服器？ A：有多種方法。如果你在某時間點做過主伺服器備份並且記錄了相應快照的二進位日誌名和偏移量(通過SHOW MASTER STATUS命令 ...
前幾天剛剛註冊了博客園，我想寫一些技巧性的教程，今天給大家分享一個MySQL同步常見問題解答。
Q：如果主伺服器正在運行並且不想停止主伺服器，怎樣配置一個從伺服器？
A：有多種方法。如果你在某時間點做過主伺服器備份並且記錄了相應快照的二進位日誌名和偏移量(通過SHOW MASTER STATUS命令的輸出)，採用下麵的步驟：
1.確保從伺服器分配了一個唯一的伺服器ID號。
2.在從伺服器上執行下麵的語句，為每個選項填入適當的值：
mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host_name',
-> MASTER_USER='master_user_name',
-> MASTER_PASSWORD='master_pass',
-> MASTER_LOG_FILE='recorded_log_file_name',
-> MASTER_LOG_POS=recorded_log_position;
3.在從伺服器上執行START SLAVE語句。
如果你沒有備份主伺服器，這裡是一個創建備份的快速程式。所有步驟都應該在主伺服器主機上執行。
1.發出該語句：
mysql> FLUSH TABLES WITH READ LOCK；
2.仍然加鎖時，執行該命令（或它的變體）：
shell> tar zcf /tmp/backup.tar.gz /var/lib/mysql
3.發出該語句並且確保記錄了以後用到的輸出：
mysql>SHOW MASTER STATUS；
4.釋放鎖：
mysql> UNLOCK TABLES；
一個可選擇的方法是，轉儲主伺服器的SQL來代替前面步驟中的二進位複製。要這樣做，你可以在主伺服器上使用mysqldump --master-data，以後裝載SQL轉儲到到你的從伺服器。然而，這比進行二進位複製速度慢。
不管你使用這兩種方法中的那一個，當你有一個快照和記錄了日誌名與偏移量時，後來根據說明操作。你可以使用相同的快照建立多個從伺服器。一旦你擁有主伺服器的一個快照，可以等待創建一個從伺服器，只要主伺服器的二進位日誌完整。兩個能夠等待的時間實際的限制是指在主伺服器上保存二進位日誌的可用硬碟空間和從伺服器同步所用的時間。
你也可以使用LOAD DATA FROM MASTER。這是一個方便的語句，它傳輸一個快照到從伺服器並且立即調整日誌名和偏移量。將來，LOAD DATA FROM MASTER將成為創建從伺服器的推薦方法。然而需要註意，它只工作在MyISAM表上並且可能長時間持有讀鎖定。它並不象我們希望的那樣高效率地執行。如果你有大表，執行FLUSH TABLES WITH READ LOCK語句後，這時首選方法仍然是在主伺服器上製作二進位快照。
Q：從伺服器需要始終連接到主伺服器嗎？
A：不，不需要。從伺服器可以宕機或斷開連接幾個小時甚至幾天，重新連接後獲得更新信息。例如，你可以在通過撥號的鏈接上設置主伺服器/從伺服器關係，其中只是偶爾短時間內進行連接。這意味著，在任何給定時間，從伺服器不能保證與主伺服器同步除非你執行某些特殊的方法。將來，我們將使用選項來阻塞主伺服器直到有一個從伺服器同步。
Q：我怎樣知道從伺服器與主伺服器的最新比較? 換句話說，我怎樣知道從伺服器複製的最後一個查詢的日期？
A：你可以查看SHOW SLAVE STATUS語句的Seconds_Behind_Master列的結果。當從伺服器SQL線程執行從主伺服器讀取的事件時，它根據事件時間戳修改自己的時間（這是TIMESTAMP能夠很好複製的原因）。在SHOW PROCESSLIST語句輸出的Time列內，為從伺服器SQL線程顯示的秒數是最後一個複製事件的時間戳和從伺服器主機的實際時間之間相差的秒數。你可以使用它來確定最後一個複製事件的日期。註意，如果你的從伺服器與主伺服器連接斷開一個小時，然後重新連接，在SHOW PROCESSLIST結果中，你可以立即看到從伺服器SQL線程的Time值為3600。這可能是因為從伺服器執行的語句是一個一小時之前的。
Q：我怎樣強制主伺服器阻塞更新直到從伺服器同步？
A：使用下麵的步驟：
1.在主伺服器上，執行這些語句：
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
記錄SHOW語句的輸出的日誌名和偏移量。這些是複製坐標。
2.在從伺服器上，發出下麵的語句，其中Master_POS_WAIT()函數的參量是前面步驟中的得到的複製坐標值：
mysql> SELECT MASTER_POS_WAIT('log_name', log_offset);
SELECT語句阻塞直到從伺服器達到指定的日誌文件和偏移量。此時，從伺服器與主伺服器同步，語句返回。
3.在主伺服器上，發出下麵的語句允許主伺服器重新開始處理更新：
mysql> UNLOCK TABLES；
Q：當設置雙向複製時我應該知道發出那些語句？
A：MySQL複製目前不支持主伺服器和從伺服器之間的任何鎖定協議來保證分散式(跨伺服器)更新的原子性。換句話說，這樣做是可能的：客戶A根據協作-主伺服器1更新，同時，在它傳給協作-主伺服器2之前，客戶B能夠根據協作-主伺服器2更新，這樣客戶A的更新與它在協作-主伺服器1的更新不同。這樣，當客戶A根據協作-主伺服器2更新時，它產生的表與在協作-主伺服器1上的不同，即使所有根據協作-主伺服器2的更新已經傳過來。這意味著，在雙向複製關係中，你不應該把兩個伺服器串連在一起，除非你確信任何順序的更新是安全的，或者除非你在客戶端代碼中註意怎樣避免更新順序錯誤。
你還必須認識到從更新角度，雙向複製實際上並不能顯著地提高性能（或者根本不能提高性能）。兩個伺服器都需要做相同數量的更新，如同在一個伺服器做的那樣。唯一的差別是鎖競爭要少，這因為源於另一個伺服器的更新在一個從線程中序列化。即使這個益處可能被網路延遲抵消。
Q：怎樣通過複製來提高系統的性能？
A：你應將一個伺服器設置為主伺服器並且將所有寫指向該伺服器。然後根據預算配置儘可能多的從伺服器以及棧空間，並且在主伺服器和從伺服器之間分發讀取操作。你也可以用--skip-innodb、--skip-bdb、--low-priority-updates以及--delay-key-write=ALL選項啟動從伺服器，以便在從伺服器端提高速度。在這種情況下，為了提高速度，從伺服器使用非事務MyISAM表來代替InnoDB和BDB表。
Q：為了使用高性能的複製，我應該在自己的應用程式中怎樣準備客戶端代碼？
A：如果你的代碼中資料庫訪問部分已經正確地模塊化，應該能夠平滑和容易地轉換為在複製步驟中運行的代碼。僅需要更改資料庫訪問執行部分，以便發送所有的寫操作到主伺服器，以及發送讀操作到主伺服器或某個從伺服器。如果你的代碼沒有這個級別，設置一個複製系統以便清除。應先通過下麵的函數創建一個包裝庫或模塊：
·safe_writer_connect()
·safe_reader_connect()
·safe_reader_statement()
·safe_writer_statement()
每個函數名的safe_意味著函數比較小心地處理所有錯誤。你可以使用不同名的函數。重要是對於讀連接、寫連接、讀和寫有一個統一的介面。
然後，你應該轉換客戶端代碼使用包裝庫。剛開始這可能是痛苦和恐慌的過程，但從長遠來看是值得的。使用剛纔討論的方法的所有應用程式都能夠利用主伺服器/從伺服器配置的優越性，即使是含有多個從伺服器的配置。代碼非常容易維護，並且添加排錯選項也很容易。你僅需要修改一兩個函數；例如，記錄每個語句執行的時間，或者你的上千個語句中哪個語句發生了錯誤。
如果你已經編寫了許多代碼，你可能想使用replace工具自動進行轉換，該工具隨標準MySQL一起發佈，或可以自己編寫轉換腳本。理想情況，你的代碼使用一致的程式轉換風格。否則，可能最好重新編寫代碼，或者至少手工對其進行規則化以使用一致的風格。
Q：MySQL複製能夠何時和多大程度提高系統性能？
A：MySQL複製對於頻繁讀和頻繁寫的系統具有最大好處。理論上，通過使用單個主伺服器/多從伺服器設置，可以通過添加更多的從伺服器來擴充系統，直到用完網路帶寬，或者你的更新負載已經增長到主伺服器不能處理的點。
在獲得的收益開始吃平之前，為了確定可以有多少從伺服器，以及可以將你的站點的性能提高多少，需要知道查詢模式，並且要通過基準測試並根據經驗確定一個典型的主伺服器和從伺服器中的讀取（每秒鐘讀取量，或者max_reads）吞吐量和寫（max_writes）吞吐量的關係。通過一個假設的帶有複製的系統，本例給出了一個非常簡單的計算結果。
假設系統負載包括10%的寫和90%的讀取，並且我們通過基準測試確定max_reads是1200 –2 × max_writes。換句話說，如果沒有寫操作，系統每秒可以進行1,200次讀取操作，平均寫操作是平均讀操作所用時間的兩倍，並且關係是線性的。我們假定主伺服器和每個從伺服器具有相同的性能，並且我們有一個主伺服器和N個從伺服器。那麼，對於每個伺服器（主伺服器或從伺服器），我們有：
reads = 1200 – 2 × writes
reads = 9 × writes / (N + 1) (讀取是分離的, 但是寫入所有伺服器)
9 × writes / (N + 1) + 2 × writes = 1200
writes = 1200 / (2 + 9/(N+1))
最後的等式表明瞭N個從伺服器的最大寫操作數，假設最大可能的讀取速率是每分鐘1,200次，讀操作與寫操作的比率是9。
如上分析可以得到下麵的結論：
·如果N = 0（這表明沒有複製），系統每秒可以處理大約1200/11 = 109個寫操作。
·如果N = 1，每秒得到184個寫操作。
·如果N = 8，每秒得到400個寫操作。
·如果N = 17，每秒得到480個寫操作。
·最後，當 N趨於無窮大（以及我們預算的負無窮大）時，可以得到非常接近每秒600個寫操作，系統吞吐量增加將近5.5倍。然而，如果只用8個伺服器，增加接近4倍。
請註意，這些計算假設網路帶寬無窮大並忽略掉了其它一些因素，那些因素可能對系統產生重要的影響。在許多情況下，不能執行與剛纔類似的計算，即如果添加N台複製從伺服器，應該準確預報系統將發生哪些影響。回答下麵的問題應能夠幫助你確定複製是否和在多大程度上能夠提高系統的性能：
·系統上的讀取/寫比例是什麼?
·如果減少讀取操作，一個伺服器可以多處理多少寫負載？
·網路帶寬可滿足多少從伺服器的需求?
Q：如何使用複製來提供冗餘/高可用性?
A：利用目前的可用特性，必須設置一個主伺服器和一個從伺服器（或多個從伺服器），以及寫一個腳本來監視主伺服器是否啟動。如果主伺服器失敗，通知應用程式和從伺服器切換主伺服器。下麵是一些建議：
·告知從伺服器更改其主伺服器，使用CHANGE MASTER TO語句。
·通知應用程式主伺服器位置的一個很好的方法是對主伺服器提供動態DNS入口。用bind可以使用nsupdate動態更新DNS。
·應該用--logs-bin選項而不用 --logs-slave-updates選項運行從伺服器。這樣，一旦你在其它從伺服器上發出STOP SLAVE; RESET MASTER, 以及CHANGE MASTER TO語句，該從伺服器可以切換為主伺服器。例如，假設有下麵的設置：
WC
\
v
WC----> M
/ | \
/ | \
v v v
S1 S2 S3
M代表主伺服器，S代表從伺服器，WC代表發出資料庫寫和讀取操作的客戶；只發出資料庫讀取操作的客戶沒有給出，因為它們不需要切換。S1、S2以及S3是從伺服器，用--logs-bin選項而沒有用--logs-slave-updates運行。因為從伺服器收到的主伺服器的更新沒有記錄在二進位日誌中，除非指定 --logs-slave-updates選項，每個從伺服器上的二進位日誌是空的。如果因為某些原因M 變得不可用，你可以選取一個從伺服器變為新的主伺服器。例如，如果你選取了S1，所有WC應該重新指向S1和S2，並且S3然後應從S1複製。
確保所有從伺服器已經處理了中繼日誌中的所有語句。金絲閣在每個從伺服器上，發出STOP SLAVE IO_THREAD語句，然後檢查SHOW PROCESSLIST語句的輸出，直到你看到Has read all relay log。當所有從伺服器都執行完這些，它們可以被重新配置為一個新的設置。在被提升為主伺服器的從伺服器S1上，發出STOP SLAVE和RESET MASTER語句。
在其它從伺服器S2和S3上，使用STOP SLAVE和CHANGE MASTER TO MASTER_HOST='S1'（其中'S1'表示S1實際的主機名）。為CHANGE MASTER添加關於從S2或S3如何連接到S1的所有信息（user、password、port）。在CHANGE MASTER命令中，不需要指定從其讀取的S1的二進位日誌名或二進位日誌位置：我們知道它是第1個二進位日誌，位置是4，這是CHANGE MASTER命令的預設值。最後，在S2和S3上使用START SLAVE 命令。
然後，指示所有WC把它們的語句指向S1。此後，WC發出的所有發送到S1的更新語句被寫入S1的二進位日誌，S1則包含M死掉之後的發送到 S1的每一個更新語句。
結果是下麵的配置：
WC
/
|
WC | M(unavailable)
\ |
\ |
v v
S1<--S2 S3
^ |
+-------+
當M重新啟動後，你必須在M上發出相同的CHANGE MASTER語句，與在S2和S3上發出的語句一樣，以便M變為S1的從伺服器並且恢覆在它宕機後丟失的所有WC寫操作。要把 M 再次作為主伺服器（例如，因為它是功能最強的機器），使用前面的步驟，好像S1不可用並且M變為一個新的主伺服器一樣。在這個過程中，在S1、S2以及S3作為M的從伺服器之前，不要忘記在M上運行RESET MASTER。否則，它們可能拾取M變得不可用之前的舊WC寫操作。
我們目前正在MySQL集成自動主伺服器選擇系統，但在準備好之前，你必須創建自己的監控工具。
