MySQL主從服務(wù)器配置.doc

MySQL 主從服務(wù)器配置一、同步原理說明Mysql的 Replication 是一個(gè)異步的復(fù)制過程，從一個(gè) Mysql instace(我們稱之為 Master)復(fù)制到另一個(gè)Mysql instance(我們稱之 Slave)。在 Master 與 Slave之間的實(shí)現(xiàn)整個(gè)復(fù)制過程主要由三個(gè)線程來完成，其中兩個(gè)線程(Sql線程和IO線程)在 Slave 端，另外一個(gè)線程(IO線程)在 Master端。要實(shí)現(xiàn) MySQL 的 Replication ，首先必須打開 Master 端的Binary Log(mysql-bin.xxxxxx)功能，否則無法實(shí)現(xiàn)。因?yàn)檎麄€(gè)復(fù)制過程實(shí)際上就是Slave從Master端獲取該日志然后再在自己身上完全順序的執(zhí)行日志中所記錄的各種操作。打開 MySQL 的 Binary Log 可以通過在啟動(dòng) MySQL Server 的過程中使用“l(fā)og-bin” 參數(shù)選項(xiàng)，或者在 f 配置文件中的 mysqld 參數(shù)組(mysqld標(biāo)識(shí)后的參數(shù)部分)增加“l(fā)og-bin” 參數(shù)項(xiàng)。MySQL 復(fù)制的基本過程如下：1. Slave 上面的IO線程連接上 Master，并請(qǐng)求從指定日志文件的指定位置(或者從最開始的日志)之后的日志內(nèi)容;2. Master 接收到來自 Slave 的 IO 線程的請(qǐng)求后，通過負(fù)責(zé)復(fù)制的 IO線程根據(jù)請(qǐng)求信息讀取指定日志指定位置之后的日志信息，返回給 Slave 端的 IO線程。返回信息中除了日志所包含的信息之外，還包括本次返回的信息在 Master 端的 Binary Log 文件的名稱以及在 Binary Log 中的位置;3. Slave 的 IO 線程接收到信息后，將接收到的日志內(nèi)容依次寫入到 Slave 端的Relay Log文件(mysql-relay-bin.xxxxxx)的最末端，并將讀取到的Master端的bin-log的文件名和位置記錄到master-info文件中，以便在下一次讀取的時(shí)候能夠清楚的高速M(fèi)aster“我需要從某個(gè)bin-log的哪個(gè)位置開始往后的日志內(nèi)容，請(qǐng)發(fā)給我”4. Slave 的 SQL 線程檢測(cè)到 Relay Log 中新增加了內(nèi)容后，會(huì)馬上解析該 Log 文件中的內(nèi)容成為在 Master端真實(shí)執(zhí)行時(shí)候的那些可執(zhí)行的 Query 語(yǔ)句，并在自身執(zhí)行這些 Query。這樣，實(shí)際上就是在 Master 端和 Slave端執(zhí)行了同樣的 Query，所以兩端的數(shù)據(jù)是完全一樣的。二、設(shè)置mysql主從配置的優(yōu)點(diǎn)：1.解決web應(yīng)用系統(tǒng)，數(shù)據(jù)庫(kù)出現(xiàn)的性能瓶頸，采用數(shù)據(jù)庫(kù)集群的方式來實(shí)現(xiàn)查詢負(fù)載；一個(gè)系統(tǒng)中數(shù)據(jù)庫(kù)的查詢操作比更新操作要多得多，通過多臺(tái)查詢服務(wù)器將數(shù)據(jù)庫(kù)的查詢分擔(dān)到不同的查詢服務(wù)器上從而提高查詢效率。2. Mysql數(shù)據(jù)庫(kù)支持?jǐn)?shù)據(jù)庫(kù)的主從復(fù)制功能，使用主數(shù)據(jù)庫(kù)進(jìn)行數(shù)據(jù)的插入、刪除與更新操作，而從數(shù)據(jù)庫(kù)則專門用來進(jìn)行數(shù)據(jù)查詢操作，這樣可以將更新操作和查詢操作分擔(dān)到不同的數(shù)據(jù)庫(kù)上，從而提高了查詢效率。三、同步設(shè)置基本步驟(以下設(shè)置均涉及f文件的修改)3.1 設(shè)置主服務(wù)器:啟用bin-log日志、同步帳號(hào)server-id = n #設(shè)置IDlog-bin #啟用bin-log記錄，如需要指定binlog文件名，可使用如下語(yǔ)法#log-bin=binlog #binlog文件名為binlog.0，binlog.1set-variable=binlog-ignore-db=mysql #不記錄數(shù)據(jù)庫(kù)mysql的更新日志binlog-do-db = #記錄指定的數(shù)據(jù)庫(kù)的更新日志，還可以單獨(dú)處理只更新表內(nèi)容此外，需要注意兩個(gè)環(huán)境變量：innodb_flush_log_at_trx_commit=1 #Default: 1。在使用了事務(wù)的數(shù)據(jù)庫(kù)中，強(qiáng)烈建議該值顯式的設(shè)置為1，以確保在每個(gè)事務(wù)提交時(shí)，日志緩沖被寫到日志文件，并將該寫操作同步到磁盤上sync_binlog=1 #在每進(jìn)行1次binlog寫操作后就將該操作同步到硬盤上。設(shè)為更高的值可以有效降低io并獲得更好的性能，但會(huì)增加bilog丟失的風(fēng)險(xiǎn)為從服務(wù)器上的同步帳號(hào)授權(quán)：mysqlGRANT REPLICATION SLAVE ON *.* TO 同步帳號(hào)的用戶名從服務(wù)器ip地址 IDENTIFIED BY 同步帳號(hào)的密碼;如果想要從服務(wù)器上有權(quán)限執(zhí)行”LOAD TABLE FROM MASTER”和”LOAD DATA FROM MASTER”還需要增加”FILE”和”SELECT”權(quán)限;3.2 設(shè)置從服務(wù)器:設(shè)置需要同步的內(nèi)容server-id = n #設(shè)置IDmaster-host = #設(shè)置主服務(wù)器的ipmaster-user = #設(shè)置同步帳戶的用戶名master-password = #設(shè)置同步帳戶的密碼master-port = #設(shè)置TCPIP端口，默認(rèn)為3306set-variable=replicate-ignore-db=mysql #設(shè)置忽略的同步數(shù)據(jù)庫(kù)名set-variable=replicate-db-db= #設(shè)置同pass_adm步的數(shù)據(jù)庫(kù)名若需要單獨(dú)設(shè)置可以針對(duì)表級(jí)設(shè)置的數(shù)據(jù)多個(gè)，請(qǐng)?jiān)O(shè)置多次這里的設(shè)置可以在后面登陸進(jìn)從數(shù)據(jù)庫(kù)之后用CHANGE MASTER TO來修改。3.3 拷貝主服務(wù)器數(shù)據(jù)到從服務(wù)器3.3.1 方式1:停止主服務(wù)器或其中一臺(tái)從服務(wù)器并拷貝數(shù)據(jù)在需要拷貝數(shù)據(jù)的服務(wù)器上停止mysqld，完全停止服務(wù)。如果是從服務(wù)器，則需要在停止mysqld之前，停止同步進(jìn)程。mysqlstop slave;$mysqladmin -uroot -p shutdown確認(rèn)mysqld停止之后，將其中的數(shù)據(jù)文件ib*，需要同步的數(shù)據(jù)庫(kù)目錄和其他相關(guān)文件拷貝到新機(jī)器上。binlog和relay-log不用拷貝3.3.2 方式2：在主服務(wù)器或其中一臺(tái)從服務(wù)器上設(shè)置鎖，禁止一切讀寫操作并拷貝數(shù)據(jù)在主服務(wù)器上執(zhí)行:mysqlFLUSH TABLES WITH READ LOCK;導(dǎo)出數(shù)據(jù)到從數(shù)據(jù)庫(kù)(可以使用tar、scp、mysqldump等方式，此處實(shí)現(xiàn)比較靈活):$cd /mysql/$tar zcvf var.tar.gz var/ #數(shù)據(jù)目錄可能不為var目錄然后是拷貝此var.tar.gz包到從服務(wù)器并解壓縮到相應(yīng)目錄在主服務(wù)器上執(zhí)行:mysqlUNLOCK TABLES;說明： 拷貝主服務(wù)器的數(shù)據(jù)到從服務(wù)器的實(shí)現(xiàn)方式太多：可以直接停止mysqld程序拷貝數(shù)據(jù)、可以直接tar方式打包處理、可以直接scp方式處理、可以直接導(dǎo)出相應(yīng)表結(jié)構(gòu)等，具體實(shí)現(xiàn)方式看具體應(yīng)用，適而擇之，主要保證在拷貝數(shù)據(jù)的時(shí)候沒有數(shù)據(jù)寫入和更改即可3.4 啟動(dòng)同步啟動(dòng)從數(shù)據(jù)庫(kù)的mysqld,推薦使用-skip-slave-start啟動(dòng)，進(jìn)入mysql后再start slave啟動(dòng)同步。說明： 通常使用的mysqld_safe會(huì)記住啟動(dòng)時(shí)的參數(shù)，例如-skip-slva-start。如果從庫(kù)因?yàn)橐馔庠蛑貑ⅲ以摂?shù)據(jù)庫(kù)啟動(dòng)的時(shí)候使用了mysqld_safe -skip-slave-start，則需要手工啟動(dòng)同步進(jìn)程，否則同步進(jìn)程會(huì)始終處于停止?fàn)顟B(tài)從服務(wù)器上執(zhí)行：mysqlSHOW SLAVE STATUS若Slave_IO_Running 和 Slave_SQL_Running 兩行的值都為 Yes，這表明 Slave 的 I/O 和 SQL 線程都在正常運(yùn)行。說明： 推薦使用CHANGE MASTER TO 語(yǔ)句進(jìn)行同步參數(shù)設(shè)置; 具體參照help change master to,語(yǔ)法如下：master_def:aster_def , master_def .master_def:MASTER_HOST = host_name MASTER_USER = user_name MASTER_PASSWORD = password MASTER_PORT = port_num MASTER_CONNECT_RETRY = count MASTER_LOG_FILE = master_log_name MASTER_LOG_POS = master_log_pos RELAY_LOG_FILE = relay_log_name RELAY_LOG_POS = relay_log_pos MASTER_SSL = 0|1 MASTER_SSL_CA = ca_file_name MASTER_SSL_CAPATH = ca_directory_name MASTER_SSL_CERT = cert_file_name MASTER_SSL_KEY = key_file_name MASTER_SSL_CIPHER = cipher_list 四、 實(shí)例參考:4.1主數(shù)據(jù)庫(kù)A的f配置：server-id = 1 #設(shè)置IDlog-bin #啟用bin-log記錄binlog-do-db = iknow #記錄指定庫(kù)的binlog更新記錄，每個(gè)數(shù)據(jù)庫(kù)一行binlog-do-db = iknow_admbinlog-do-db = .現(xiàn)在，新搭建一個(gè)從數(shù)據(jù)庫(kù)B。需要進(jìn)行如下步驟4.2.修改配置文件: 修改f，加入如下項(xiàng)：server-id = 2 #指定server id，每臺(tái)機(jī)器的id必須保證唯一log-bin #啟用binlog記錄master-connect-retry = 60 #連接主庫(kù)的重試間隔master-host = xxx.xxx.xxx.xxx #指定主庫(kù)A的IP，不能用虛IPmaster-port = 3306 #指定主庫(kù)端口master-user = rep #指定同步用戶的用戶名為replicationmaster-password = rep #指定同步用戶的密碼為PassWORdreplicate-do-db = iknow #指定需要同步的庫(kù)，每個(gè)庫(kù)獨(dú)占一行replicate-do-db = iknow_admreplicate-do-db = .log-slave-updates #在從庫(kù)上對(duì)同步進(jìn)行日志記錄，磁盤占用量較大，建議從庫(kù)運(yùn)行穩(wěn)定后將其去除，如要作為第二層主數(shù)據(jù)庫(kù)，則此項(xiàng)必須這里的設(shè)置可以在后面登陸進(jìn)從數(shù)據(jù)庫(kù)之后用CHANGE MASTER TO來修改。4.3.拷貝數(shù)據(jù):從一臺(tái)從數(shù)據(jù)庫(kù)C拷貝數(shù)據(jù)到B 從數(shù)據(jù)庫(kù)沒有對(duì)外提供服務(wù)，因此可以直接停掉從數(shù)據(jù)庫(kù)拷貝數(shù)據(jù)。注意：停從數(shù)據(jù)庫(kù)的時(shí)候需要顯式的執(zhí)行stop slave，并執(zhí)行show slave status，確認(rèn)Read_Master_Log_Pos和Exec_Master_Log_Pos一致，再執(zhí)行mysqladmin -uroot -p shutdown停掉mysqld。mysqlstop slave;mysqlshow slave status確認(rèn)Read_Master_Log_Pos和Exec_Master_Log_Pos一致$ mysqladmin -uroot -p shutdown待mysqld完全退出后，將var目錄下的ib*，，以及需要同步的庫(kù)cp到本地目錄var.$DATE下（主要是為了縮短C上mysqld的服務(wù)停止時(shí)間）$ cd $MYSQL/var; mkdir ./var.$DATE$ cp -r ib* iknow iknow_adm ./var.$DATE/$ scp $MYSQL/var.$DATE B:/mysql4.4.在主數(shù)據(jù)庫(kù)上為一臺(tái)從庫(kù)授權(quán)同步mysqlGRANT REPLICATION SLAVE ON *.* TO replicationB機(jī)器的ip地址 IDENTIFIED BY Password;4.5.在B上啟動(dòng)mysqld$ mysqld_safe -skip-slave-start &4.6.進(jìn)入mysql觀察slave statusmysql show slave status4.7.觀察和$ cat $MYSQL/$ cat $MYSQL/在這里不需要change master一步是因?yàn)橹幸延涗浟薽aster_log_file和master_log_pos等相關(guān)信息。這是一種比較保險(xiǎn)而且簡(jiǎn)單的辦法4.8.復(fù)查確認(rèn)f中replication slave配置正確如果文件存在的話，mysql是優(yōu)先讀取它的，但在某些情況下，比如mysqld異常重啟等，有可能會(huì)讀取f中配置，因此需要保證f中的replication slave配置正確 由于mysqld在正常情況下優(yōu)先讀取，因此，在主數(shù)據(jù)庫(kù)上一定要保證是不存在的，否則可能出現(xiàn)不可預(yù)知的后果。4.9. CHANGE MASTER TO語(yǔ)句這是用于在mysql命令行中顯式的更改主庫(kù)的語(yǔ)句。在mysql命令行中執(zhí)行mysql help change master to可獲得關(guān)于這條語(yǔ)句的詳細(xì)信息。如果在進(jìn)行主從數(shù)據(jù)庫(kù)切換后，或需要臨時(shí)更改主庫(kù)，而又不想重啟數(shù)據(jù)庫(kù)的時(shí)候，可以使用如下語(yǔ)句動(dòng)態(tài)的更改主庫(kù)：mysql CHANGE MASTER TO MASTER_HOST=xxx.xxx.xxx.xxx, MASTER_USER=replication, MASTER_PASSWORD=Password, MASTER_LOG_FILE=master-binlog.00001, MASTER_LOG_POS=123456;以上語(yǔ)句各項(xiàng)含義如下：MASTER_HOST主庫(kù)的機(jī)器名或IP地址MASTER_USER主庫(kù)為從庫(kù)授權(quán)的同步帳號(hào)MASTER_PASSWORD主庫(kù)為從庫(kù)授權(quán)的同步帳號(hào)密碼MASTER_LOG_FILE主庫(kù)的binlog文件名MASTER_LOG_POS主庫(kù)的binlog文件偏移MASTER_LOG_FILE和MASTER_LOG_POS兩項(xiàng)需要在啟動(dòng)主庫(kù)之前執(zhí)行show master status記錄下來，并且必須保證準(zhǔn)確無誤，否則會(huì)出現(xiàn)同步失敗的情況。五、監(jiān)控服務(wù)器的狀態(tài)5.1.監(jiān)控主服務(wù)器的狀態(tài)可通過show master status來監(jiān)控主服務(wù)器的狀態(tài)，內(nèi)容如下：mysql show master status;+-+-+-+-+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-+-+-+-+| redhat01-bin.000001 | 625 | mydb | | +-+-+-+-+1 row in set (0.00 sec)+-+-+-+-+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-+-+-+-+| mysql-bin.000003 | 1164 | | |+-+-+-+-+#其中File表示日志文件記錄，Position表示日志文件的位置，這個(gè)也是數(shù)據(jù)庫(kù)執(zhí)行復(fù)制操作的必須標(biāo)識(shí)，后面兩字段表示復(fù)制的數(shù)據(jù)庫(kù)名和不復(fù)制的數(shù)據(jù)庫(kù)名，也可以在配置文件中你進(jìn)行配置。5.2.監(jiān)控從服務(wù)器的狀態(tài)可以通過：show slave statusG來查看，另外如果從數(shù)據(jù)庫(kù)在復(fù)制的過程中出現(xiàn)問題，可以通過命令reset slave從數(shù)據(jù)庫(kù)服務(wù)器復(fù)制的線程，從數(shù)據(jù)庫(kù)服務(wù)器的通常操作命令有：start slave；#啟動(dòng)復(fù)制線程stop slave；#停止復(fù)制線程reset slave；#重置復(fù)制線程change master to； #動(dòng)態(tài)改變到主服務(wù)器的配置mysql show slave statusG* 1. row * Slave_IO_State: Waiting for master to send event Master_Host: 01 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: redhat01-bin.000001 Read_Master_Log_Pos: 625 Relay_Log_File: redhat02-relay-bin.000002 Relay_Log_Pos: 765 Relay_Master_Log_File: redhat01-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: mydb Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 625 Relay_Log_Space: 765 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 01 row in set (0.00 sec)六、手工指定同步mysql CHANGE MASTER TO- MASTER_HOST=master_host_name, /主服務(wù)器的IP地址- MASTER_USER=replication_user_name, /同步數(shù)據(jù)庫(kù)的用戶- MASTER_PASSWORD=replication_password, /同步數(shù)據(jù)庫(kù)的密碼- MASTER_LOG_FILE=recorded_log_file_name, /主服務(wù)器二進(jìn)制日志的文件名，前面要求記住的參數(shù)- MASTER_LOG_POS=recorded_log_position; /日志文件的開始位置(前面要求記住的參數(shù))備注：不用把主服務(wù)器上的初始數(shù)據(jù)導(dǎo)入從數(shù)據(jù)庫(kù)，從數(shù)據(jù)庫(kù)會(huì)自動(dòng)的同步你設(shè)置同步的數(shù)據(jù)庫(kù)的全部?jī)?nèi)容！七、FAQ7.1. scp了整個(gè)var目錄到新的從庫(kù)，start slave后，發(fā)現(xiàn)同步無法正常進(jìn)行，Slave_IO_Running正常，但Slave_SQL_Running的狀態(tài)為No，并且mysql錯(cuò)誤日志中報(bào)找不到一個(gè)releylog文件答：這是由于文件的存在造成的，它記錄了數(shù)據(jù)庫(kù)當(dāng)前正在使用的relaylog，而mysql默認(rèn)的relaylog命名是以機(jī)器名命名的，因此如果從一臺(tái)機(jī)器拷貝到另一臺(tái)機(jī)器，它是找不到這個(gè)文件的。如果可以確認(rèn)Read_Master_Log_Pos和Exec_Master_Log_Pos一致，那么可以直接將刪除，重啟mysqld，再start slave就可以解決。7.2. start slave后， Slave_IO_