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

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