兩台mysql伺服器（安裝兩個mysql）

本文目錄一覽：

• 1、如何安裝配置基於2台伺服器的MySQL集群
• 2、Dual Master在mysql中具體意義？
• 3、怎麼實現兩台伺服器的mysql數據同步
• 4、如何在兩台伺服器之間安全遷移MySQL資料庫
• 5、查看mysql是否為雙機

如何安裝配置基於2台伺服器的MySQL集群

Servers1和Server2作為實際配置MySQL集群的伺服器。

對於作為管理節點的Server3則要求較低，只需對Server3的系統進行很小的調整並且無需安裝MySQL，Server3可以使用一台配置較低的計算機並且可以在Server3同時運行其他服務。

Dual Master在mysql中具體意義？

Dual Master實際上就是兩台MySQL伺服器互相將對方作為自己的 Master，自己作為對方的Slave，這樣任何一台伺服器上的數據變更，都會通過MySQL 的複製機制同步到另一台伺服器。當然，有的可能會擔心，這樣不會導致兩台互為Master 的 MySQL之間循環複製嗎?當然不會,這是由於MySQL在記錄Binary log日誌時,記錄了當前的server-id, server-id在我們配置MySQL複製時就已經設置好了。一旦有了server-id，MySQL就很容易判斷最初的寫入是在哪台伺服器上發生的，MySQL不會將複製所產生的變更記錄到Binary log，這樣就避免了伺服器間數據的循環複製。當然，我們搭建Dual-Master 架構，並不是為了讓兩個 Master 能夠同時提供寫入服務，這樣會導致很多問題。舉例來說，假如Master A 與Master B幾乎同時對一條數據進行了更新，對Master A的更新比對Master B的更新早，當對Master A的更新最終被同步到Master B時，老版本的數據將會把版本更新的數據覆蓋，並且不會拋出任何異常，從而導致數據不一致的現象發生。在通常情況下，我們僅開啟一台Master的寫入，另一台Master僅僅 stand by或者作為讀庫開放，這樣可以避免數據寫入的衝突，防止數據不一致的情況發生。在正常情況下，如需進行停機維護，可按如下步驟執行Master的切換操作:

1）停止當前Master 的所有寫入操作。

2）在 Master 上執行set global read_only=1，同時更新MySQL 配置文件中相應的配置，避免重啟時失效。

3）在 Master上執行show Master status，以記錄 Binary log 坐標。

4)使用Master上的Binary log坐標,在stand by的 Master上執行select Master_pos_wait(),等待stand by Master的 Binary log跟上 Master的 Binary log。

5）在stand by Master 開啟寫入時，設置read_only=O。

6）修改應用程序的配置，使其寫入到新的Master。

怎麼實現兩台伺服器的mysql數據同步

這種架構一般用在以下三類場景

1. 備份多台 Server 的數據到一台如果按照數據切分方向來講，那就是垂直切分。比如圖 2，業務 A、B、C、D 是之前拆分好的業務，現在需要把這些拆分好的業務匯總起來備份，那這種需求也很適用於多源複製架構。實現方法我大概描述下：業務 A、B、C、D 分別位於 4 台 Server，每台 Server 分別有一個資料庫來隔離前端的業務數據，那這樣，在從庫就能把四台業務的數據全部匯總起來，而不需要做額外的操作。那沒有多源複製之前，要實現這類需求，只能在匯總機器上搭建多個 MySQL 實例，那這樣勢必會涉及到跨庫關聯的問題，不但性能急劇下降，管理多個實例也沒有單台來的容易。

2. 用來聚合前端多個 Server 的分片數據。

同樣，按照數據切分方向來講，屬於水平切分。比如圖 3，按照年份拆分好的數據，要做一個匯總數據展現，那這種架構也非常合適。實現方法稍微複雜些：比如所有 Server 共享同一資料庫和表，一般為了開發極端透明，前端配置有分庫分表的中間件，比如愛可生的 DBLE。

3. 匯總併合並多個 Server 的數據

第三類和第一種場景類似。不一樣的是不僅僅是數據需要匯總到目標端，還得合併這些數據，這就比第一種來的相對複雜些。比如圖 4，那這樣的需求，是不是也適合多源複製呢？答案是 YES。那具體怎麼做呢？

如何在兩台伺服器之間安全遷移MySQL資料庫

   遷移MySQL資料庫通常只需要幾個簡單的步驟，但是由於您要轉移的數據量可能比較龐大，因此一般耗時也會比較長。

    下面的步驟將指導您如何從舊的伺服器上導出MySQL資料庫，對它進行安全加固；然後將其複製並導入到新的伺服器上，以保證數據的完整。

    將MySQL資料庫導出至轉儲文件（dump file）

    Oracle提供了一個名為mysqldump的工具，允許您輕鬆地將資料庫結構和其數據導出到一個SQL的轉儲文件。您可以使用如下的命令：

    1.mysqldump -u root -p –opt [database name] [database name].sql 

    不過，請注意如下幾點：

    我們可以使用–single-transaction的標誌，以避免資料庫在導出數據的過程中被鎖死。這樣能夠在將數據導出到轉儲文件的同時，您仍可繼續在舊的資料庫上更新數據。不過請注意，那些在導出進程已經開始之後被更新的數據，是不會被導入轉儲文件之中的。

    在運行該命令之前，請務必將[database name]替換成您的實際資料庫名稱。

    請輸入您自己的用戶名和相對應的密碼，並確保該用戶具有備份資料庫所需的許可權。

    安全加固備份文件

    在大多數情況下，數據是一家企業的最重要的資產。因此，我們不希望資料庫的各種備份被暴露在不受保護的伺服器上，因為這樣有可能會造成錯誤地泄露，甚至會出現被黑客竊取等更為糟糕的狀況。

    因此，通常您可以嘗試的做法是：壓縮、加密文件，然後刪除原文件。在Linux操作系統上，請使用以下的命令對已壓縮文件進行加密：

    1.zip –encrypt dump.zip db.sql 

    在壓縮開始之前，系統將提示您輸入密碼。

    傳輸備份文件

    至此，我們已經獲得了一個加密的轉儲文件。下面讓我們通過網路使用SCP命令，將其傳輸到新的伺服器上：

    1.scp /path/to/source-file user@host：/path/to/destination-folder/ 

    將MySQL轉儲導入新伺服器

    通過上面一步，我們已將備份文件傳到了新的伺服器上，下面讓我們來進行解密和提取：

    1.unzip -P your-password dump.zip 

    為了存儲空間和安全方面的原因，一旦文件導入成功，請記得刪除其對應的轉儲文件。

    您可以使用以下的命令來導入文件：

    1.mysql -u root -p newdatabase /path/to/newdatabase.sql 

    在新伺服器上驗證導入的數據

    現在我們在新伺服器上已經導入了資料庫，那麼我們就需要一種方法來驗證數據的真實存在，並確保沒有任何遺漏。

    我建議您同時在舊的和新的資料庫上運行如下查詢，並將獲得的結果進行對比。

    該查詢會在所有的表裡計算行數，以顯示出新、舊資料庫中的數據量。

    1.SELECT   

    2.TABLE_NAME,   

    3.TABLE_ROWS  

    4.FROM   

    `

    5.information_schema`.`tables`  

    6.WHERE   

    `

    7.table_schema` = ‘YOUR_DB_NAME’； 

    此外，我建議您檢查各個表中數字列的MIN和MAX記錄，以確保數據本身是有效的，而不僅僅是看數據的總量（雖然這是查詢所唯一能夠讀出的值）。另一種可供測試的選擇是將資料庫從新的伺服器導出為SQL轉儲文件，並將其與舊伺服器的SQL轉儲文件做比較。

    此外，在應用程序被遷移之前，我建議您先將一個應用程序的實例重定向到新的資料庫上，以確認一切運行正常。

    另一種導出和導入的選項

    我們之所以把該選項放在最後，是因為我們的確不建議您去使用它。

    該方法實現起來非常的容易，因為它僅使用一個命令，便能一次性將轉儲文件導出、傳輸、並將其數據導入到新的資料庫之中。

    而它的不足之處在於，一旦其網路鏈接斷掉，您就需要重新啟動它了。

    因此，我們認為它並不值得被推薦，尤其是在大型資料庫中，可能會非常不適用。

    當然，如果您非要嘗試一下的話，可以使用如下的命令：

    1.mysqldump -u root -pPassword –all-databases | ssh user@new_host.host.com ‘cat – | mysql -u root -pPassword’ 

    重要提示

    請確保在新舊兩處，安裝有相同官方發行版本的MySQL伺服器。否則，你需要按照MySQL網站上的升級說明來進行統一（請參見（https：//dev.mysql.com/doc/refman/5.7/en/upgrading.html）。

    請確保您在舊的伺服器上擁有足夠的空間來保存轉儲文件和壓縮文件（應該有db_size×2的空間）。

    請確保您在新的伺服器上擁有足夠的空間來保存加密的和解密的轉儲文件、並能導入資料庫（應該有db_size×3的空間）。

    如果您曾經考慮過只是將datadir從一個資料庫轉移到另一個的話，我建議您最好不要這樣做。否則，您會搞亂資料庫的內部結構，而且會給將來可能的問題埋下隱患。

    在新的伺服器配置中，請不要忘了配置諸如innodb_log_file_size這樣的重要標誌。因為如果忘記了根據新伺服器的規格而更新配置的話，很可能會導致嚴重的性能問題。

    在許多情況下，一般升級到新的資料庫伺服器的初衷是為了提高查詢性能。而如果此類升級沒有達到預期的改善，那麼您就應該考慮去優化SQL查詢，而不僅僅是升級硬體那麼簡單了

查看mysql是否為雙機

mysql雙機熱備實現原理分析，在本文經過深思熟慮和多次用不同的方式實測試後。最後在這篇文章中，用一個小例子來完成mysql雙機熱備的實現。

Mysql資料庫沒有增量備份的機制，當數據量太大的時候備份是一個很大的問題。還好mysql資料庫提供了一種主從備份的機制，其實就是把主資料庫的所有的數據同時寫到備份的資料庫中。實現mysql資料庫的熱備份。

要想實現雙機的熱備，首先要了解主從資料庫伺服器的版本的需求。要實現熱備mysql的版本都高於3.2。還有一個基本的原則就是作為從資料庫的數據版本可以高於主伺服器資料庫的版本，但是不可以低於主伺服器的資料庫版本。

當然要實現mysql雙機熱備，除了mysql本身自帶的REPLICATION功能可以實現外，也可以用Heartbeat這個開源軟體來實現。不過本文主要還是講如何用mysql自帶的REPLICATION來實現mysql雙機熱備的功能。

1. 準備伺服器

由於Mysql不同版本之間的（二進位日誌）binlog格式可能會不太一樣，因此最好的搭配組合是主（Master）伺服器的Mysql版本和從（Slave）伺服器版本相同或者更低，主伺服器的版本肯定不能高於從伺服器版本。

本次我用於測試的兩台伺服器版本都是Mysql-5.5.17。

2. Mysql 建立主－從伺服器雙機熱備配置步驟

2.1環境描述

A伺服器（主伺服器Master）：59.151.15.36

B伺服器（從伺服器Slave）：218.206.70.146

主從伺服器的Mysql版本皆為5.5.17

Linux環境下

將主伺服器需要同步的資料庫內容進行備份一份，上傳到從伺服器上，保證始初時兩伺服器中資料庫內容一致。

不過這裡說明下，由於我是利用Mysql在安裝後就有的資料庫test進行測試的，所以兩台伺服器裡面是沒有建立表的，只不分別在test裡面建立了同樣的一張空表tb_mobile;

Sql語句如下：

mysql create table tb_mobile( mobile VARCHAR(20) comment’手機號碼’, time timestamp DEFAULT now() comment’時間’ );

2.2 主伺服器Master配置

2.2.1 創建同步用戶

進入mysql操作界面，在主伺服器上為從伺服器建立一個連接帳戶，該帳戶必須授予REPLICATION SLAVE許可權。因為從mysql版本3.2以後就可以通過REPLICATION對其進行雙機熱備的功能操作。

操作指令如下：

mysql grant replication slave on *.* to ‘replicate’@’218.206.70.146’ identified by ‘123456’;

mysql flush privileges;

創建好同步連接帳戶後，我們可以通過在從伺服器（Slave）上用replicat帳戶對主伺服器（Master）資料庫進行訪問下，看下是否能連接成功。

在從伺服器（Slave）上輸入如下指令：

[root@YD146 ~]# mysql -h59.151.15.36 -ureplicate -p123456

如果出現下面的結果，則表示能登錄成功，說明可以對這兩台伺服器進行雙機熱備進行操作。

2.2.2 修改mysql配置文件

如果上面的準備工作做好，那邊我們就可以進行對mysql配置文件進行修改了，首先找到mysql配置所有在目錄，一般在安裝好mysql服務後，都會將配置文件複製一一份出來放到/ect目錄下面，並且配置文件命名為：my.cnf。即配置文件準確目錄為/etc/my.cnf

(Linux下用rpm包安裝的MySQL是不會安裝/etc/my.cnf文件的，

至於為什麼沒有這個文件而MySQL卻也能正常啟動和作用，在點有兩個說法，

第一種說法，my.cnf只是MySQL啟動時的一個參數文件，可以沒有它，這時MySQL會用內置的默認參數啟動，

第二種說法，MySQL在啟動時自動使用/usr/share/mysql目錄下的my-medium.cnf文件，這種說法僅限於rpm包安裝的MySQL，

解決方法，只需要複製一個/usr/share/mysql目錄下的my-medium.cnf文件到/etc目錄，並改名為my.cnf即可。)

找到配置文件my.cnf打開後，在[mysqld]下修改即可：

[mysqld]

server-id = 1

log-bin=mysql-bin //其中這兩行是本來就有的，可以不用動，添加下面兩行即可

binlog-do-db = test

binlog-ignore-db = mysql

2.2.3 重啟mysql服務

修改完配置文件後，保存後，重啟一下mysql服務，如果成功則沒問題。

2.2.4 查看主伺服器狀態

進入mysql服務後，可通過指令查看Master狀態，輸入如下指令：

注意看裡面的參數，特別前面兩個File和Position，在從伺服器（Slave）配置主從關係會有用到的。

註：這裡使用了鎖表，目的是為了產生環境中不讓進新的數據，好讓從伺服器定位同步位置，初次同步完成後，記得解鎖。

2.3 從伺服器Slave配置

2.3.1修改配置文件

因為這裡面是以主－從方式實現mysql雙機熱備的，所以在從伺服器就不用在建立同步帳戶了，直接打開配置文件my.cnf進行修改即可，道理還是同修改主伺服器上的一樣，只不過需要修改的參數不一樣而已。如下：

[mysqld]

server-id = 2

log-bin=mysql-bin

replicate-do-db = test

replicate-ignore-db = mysql,information_schema,performance_schema

2.3.2重啟mysql服務

修改完配置文件後，保存後，重啟一下mysql服務，如果成功則沒問題。

2.3.3用change mster 語句指定同步位置

這步是最關鍵的一步了，在進入mysql操作界面後，輸入如下指令：

mysqlstop slave; //先停步slave服務線程，這個是很重要的，如果不這樣做會造成以下操作不成功。

mysqlchange master to

master_host=’59.151.15.36′,master_user=’replicate’,master_password=’123456′,

master_log_file=’ mysql-bin.000016 ‘,master_log_pos=107;

註：master_log_file, master_log_pos由主伺服器（Master）查出的狀態值中確定。也就是剛剛叫注意的。master_log_file對應File, master_log_pos對應Position。Mysql 5.x以上版本已經不支持在配置文件中指定主伺服器相關選項。

遇到的問題，如果按上面步驟之後還出現如下情況：

則要重新設置slave。指令如下

mysqlstop slave;

mysqlreset slave;

之後停止slave線程重新開始。成功後，則可以開啟slave線程了。

mysqlstart slave;

2.3.4查看從伺服器（Slave）狀態

用如下指令進行查看

mysql show slave status\G;

查看下面兩項值均為Yes，即表示設置從伺服器成功。

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

2.4 測試同步

之前開始已經說過了在資料庫test只有一個表tb_mobile沒有數據，我們可以先查看下兩伺服器的資料庫是否有數據：

Master:59.151.15.36

Slave:218.206.70.146

好了，現在可以在Master伺服器中插入數據看下是否能同步。

Master:59.151.15.36

Slave:218.206.70.146

可以從上面兩個截圖上看出，在Master伺服器上進行插入的數據在Slave伺服器可以查到，這就表示雙機熱備配置成功了。

3. Mysql 建立主－主伺服器雙機熱備配置步驟

伺服器還是用回現在這兩台伺服器

3.1創建同步用戶

同時在主從伺服器建立一個連接帳戶，該帳戶必須授予REPLIATION SLAVE許可權。這裡因為伺服器A和伺服器B互為主從，所以都要分別建立一個同步用戶。

伺服器A：

mysql grant replication slave on *.* to ‘replicate’@’218.206.70.146’ identified by ‘123456’;

mysql flush privileges;

伺服器B：

mysql grant replication slave on *.* to ‘replicate’@’59.151.15.36’ identified by ‘123456’;

mysql flush privileges;

3.2修改配置文件my.cnf

伺服器A

[mysqld]

server-id = 1

log-bin=mysql-bin

binlog-do-db = test

binlog-ignore-db = mysql

#主－主形式需要多添加的部分

log-slave-updates

sync_binlog = 1

auto_increment_offset = 1

auto_increment_increment = 2

replicate-do-db = test

replicate-ignore-db = mysql,information_schema

伺服器B：

[mysqld]

server-id = 2

log-bin=mysql-bin

master-slave need

replicate-do-db = test

replicate-ignore-db = mysql,information_schema,performance_schema

#主－主形式需要多添加的部分

binlog-do-db = test

binlog-ignore-db = mysql

log-slave-updates

sync_binlog = 1

auto_increment_offset = 2

auto_increment_increment = 2

3.3分別重啟A伺服器和B伺服器上的mysql服務

重啟伺服器方式和上面的一樣，這裡就不做講解了。

3.4分別查A伺服器和B伺服器作為主伺服器的狀態

伺服器A：

伺服器B：

3.5分別在A伺服器和B伺服器上用change master to 指定同步位置

伺服器A：

mysqlchange master to

master_host=’218.206.70.146′,master_user=’replicate’,master_password=’123456′,

master_log_file=’ mysql-bin.000011 ‘,master_log_pos=497;

伺服器B：

mysqlchange master to

master_host=’59.151.15.36′,master_user=’replicate’,master_password=’123456′,

master_log_file=’ mysql-bin.000016 ‘,master_log_pos=107;

3.6 分別在A和B伺服器上重啟從服務線程

mysqlstart slave;

3.7 分別在A和B伺服器上查看從伺服器狀態

mysqlshow slave status\G;

查看下面兩項值均為Yes，即表示設置從伺服器成功。

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

3.8 測試主－主同步例子

測試伺服器A：

在伺服器A上插入一條語句如下圖所示：

之後在伺服器B上查看是否同步如下圖所示：

測試伺服器B：

在伺服器B上插入一條語句如下圖所示：

然後在從伺服器A上查看是否有同步數據如下圖所示：

最後從結果可以看出主－主形式的雙機熱備是能成功實現的。

4. 配置參數說明

Server-id

ID值唯一的標識了複製群集中的主從伺服器，因此它們必須各不相同。Master_id必須為1到232－1之間的一個正整數值，slave_id值必須為2到232－1之間的一個正整數值。

Log-bin

表示打開binlog，打開該選項才可以通過I/O寫到Slave的relay-log，也是可以進行replication的前提。

Binlog-do-db

表示需要記錄二進位日誌的資料庫。如果有多個數據可以用逗號分隔，或者使用多個binlog-do-dg選項。

Binglog-ingore-db

表示不需要記錄二進位日誌的資料庫，如果有多個資料庫可用逗號分隔，或者使用多binglog-ignore-db選項。

Replicate-do-db

表示需要同步的資料庫，如果有多個數據可用逗號分隔，或者使用多個replicate-do-db選項。

Replicate-ignore-db

表示不需要同步的資料庫，如果有多個資料庫可用逗號分隔，或者使用多個replicate-ignore-db選項。

Master-connect-retry

master-connect-retry=n表示從伺服器與主伺服器的連接沒有成功，則等待n秒（s）後再進行管理方式（默認設置是60s）。如果從伺服器存在mater.info文件，它將忽略些選項。

Log-slave-updates

配置從庫上的更新操作是否寫入二進位文件，如果這台從庫，還要做其他從庫的主庫，那麼就需要打這個參數，以便從庫的從庫能夠進行日誌同步。

Slave-skip-errors

在複製過程，由於各種原因導致binglo中的sql出錯，默認情況下，從庫會停止複製，要用戶介入。可以設置slave-skip-errors來定義錯誤號，如果複製過程中遇到的錯誤是定義的錯誤號，便可以路過。如果從庫是用來做備份，設置這個參數會存在數據不一致，不要使用。如果是分擔主庫的查詢壓力，可以考慮。

Sync_binlog=1 Or N

Sync_binlog的默認值是0，這種模式下，MySQL不會同步到磁碟中去。這樣的話，Mysql依賴操作系統來刷新二進位日誌binary log，就像操作系統刷新其他文件的機制一樣。因此如果操作系統或機器（不僅僅是Mysql伺服器）崩潰，有可能binlog中最後的語句丟失了。要想防止這種情況，可以使用sync_binlog全局變數，使binlog在每N次binlog寫入後與硬碟同步。當sync_binlog變數設置為1是最安全的，因為在crash崩潰的情況下，你的二進位日誌binary log只有可能丟失最多一個語句或者一個事務。但是，這也是最慢的一種方式（除非磁碟有使用帶蓄電池後備電源的緩存cache,使得同步到磁碟的操作非常快）。

即使sync_binlog設置為1，出現崩潰時，也有可能表內容和binlog內容之間存在不一致性。如果使用InnoDB表，Mysql伺服器處理COMMIT語句，它將整個事務寫入binlog並將事務提交到InnoDB中。如果在兩次操作之間出現崩潰，重啟時，事務被InnoDB回滾，但仍然存在binlog中。可以用-innodb-safe-binlog選項來增加InnoDB表內容和binlog之間的一致性。（注釋：在Mysql 5.1版本中不需要-innodb-safe-binlog；由於引入了XA事務支持，該選項作廢了），該選項可以提供更大程度的安全，使每個事務的binlog(sync_binlog=1)和（默認情況為真）InnoDB日誌與硬碟同步，該選項的效果是崩潰後重啟時，在滾回事務後，Mysql伺服器從binlog剪切回滾的InnoDB事務。這樣可以確保binlog反饋InnoDB表的確切數據等，並使從伺服器保持與主伺服器保持同步（不接收回滾的語句）。

Auto_increment_offset和Auto_increment_increment

Auto_increment_increment和auto_increment_offset用於主－主伺服器（master-to-master）複製，並可以用來控制AUTO_INCREMENT列的操作。兩個變數均可以設置為全局或局部變數，並且假定每個值都可以為1到65,535之間的整數值。將其中一個變數設置為0會使該變數為1。

這兩個變數影響AUTO_INCREMENT列的方式：auto_increment_increment控制列中的值的增量值，auto_increment_offset確定AUTO_INCREMENT列值的起點。

如果auto_increment_offset的值大於auto_increment_increment的值，則auto_increment_offset的值被忽略。例如：表內已有一些數據，就會用現在已有的最大自增值做為初始值。