mysql多台服務器機制（有兩個mysql服務）

本文目錄一覽：

• 1、mysql的事務處理機制可以做到多服務器時時並發同步嗎
• 2、mysql 多主一從如何實現
• 3、多線程寫mysql的話，mysql有自己的上鎖機制么
• 4、Mysql分布式 Dual Master具體是？
• 5、查看mysql是否為雙機

mysql的事務處理機制可以做到多服務器時時並發同步嗎

MySQL提供兩個級別的並發控制：服務器級(the server level)和存儲引擎級(the storage engine level)。加鎖是實現並發控制的基本方法，MySQL中鎖的粒度：

(1) 表級鎖：MySQL獨立於存儲引擎提供表鎖，例如，對於ALTER TABLE語句，服務器提供表鎖(table-level lock)。

(2) 行級鎖：InnoDB和Falcon存儲引擎提供行級鎖，此外，BDB支持頁級鎖。InnoDB的並發控制機制，下節詳細討論。

另外，值得一提的是，MySQL的一些存儲引擎（如InnoDB、BDB）除了使用封鎖機制外，還同時結合MVCC機制，即多版本兩階段封鎖協議MVCC(Multiversion two-phrase locking protocal)，來實現事務的並發控制，從而使得只讀事務不用等待鎖，提高了事務的並發性。

mysql 多主一從如何實現

原則上MySQL是不支持多主一從的複製架構的，有一個思路可以考慮，就是採用在從機上用多個MySQL實例來實現，不過，也要看你的各個主數據庫數據量的大小，對從機服務器IO的影響等等因素。總體來說，還是要看你的應用想解決什麼問題，只有仔細分析你的應用系統的實際需求，才能設計出適合的方案，國內MySQL的技術研究非常深入的公司，可以聯繫一下上海愛可生諮詢。

多線程寫mysql的話，mysql有自己的上鎖機制么

數據庫有自己的連接鎖機制，如果是針對同一台機器使用同一個接口進行插入的話多線程和單線程是一樣的。除非你有好幾台數據庫服務器，這樣再使用多線程來進行上面的工作的話效率才會明顯提高。

Mysql分布式 Dual Master具體是？

所謂的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是否為雙機

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的值被忽略。例如：表內已有一些數據，就會用現在已有的最大自增值做為初始值。