mysql主從數(shù)據(jù)庫的配置說明文檔

一、主從配置的原理:MySQL的Replication是一個(gè)異步的復(fù)制過程，從一個(gè)MySQLinstace(我們稱之為Master)復(fù)制到另一個(gè)MySQLinstance(我們稱之Slave)。在Master與Slave之間的實(shí)現(xiàn)整個(gè)復(fù)制過程主要由三個(gè)線程來完成，其中兩個(gè)線程(Sql線程和IO線程)在Slave端，另外一個(gè)線程(IO線程)在Master端。要實(shí)現(xiàn)MySQL的Replication，首先必須打開Master端的BinaryLog(MySQL-bin.xxxxxx)功能，否則無法實(shí)現(xiàn)。因?yàn)檎麄€(gè)復(fù)制過程實(shí)際上就是Slave從Master端獲取該日志然后再在自己身上完全順序的執(zhí)行日志中所記錄的各種操作。打開MySQL的BinaryLog可以通過在啟動(dòng)MySQLServer的過程中使用“—log-bin”參數(shù)選項(xiàng)，或者在f配置文件中的MySQLd參數(shù)組([MySQLd]標(biāo)識后的參數(shù)部分)增加“l(fā)og-bin”參數(shù)項(xiàng)。MySQL復(fù)制的基本過程如下：1.Slave上面的IO線程連接上Master，并請求從指定日志文件的指定位置(或者從最開始的日志)之后的日志內(nèi)容;2.Master接收到來自Slave的IO線程的請求后，通過負(fù)責(zé)復(fù)制的IO線程根據(jù)請求信息讀取指定日志指定位置之后的日志信息，返回給Slave端的IO線程。返回信息中除了日志所包含的信息之外，還包括本次返回的信息在Master端的BinaryLog文件的名稱以及在BinaryLog中的位置;3.Slave的IO線程接收到信息后，將接收到的日志內(nèi)容依次寫入到Slave端的RelayLog文件(MySQL-relay-bin.xxxxxx)的最末端，并將讀取到的Master端的bin-log的文件名和位置記錄到master-info文件中，以便在下一次讀取的時(shí)候能夠清楚的高速M(fèi)aster“我需要從某個(gè)bin-log的哪個(gè)位置開始往后的日志內(nèi)容，請發(fā)給我”4.Slave的SQL線程檢測到RelayLog中新增加了內(nèi)容后，會(huì)馬上解析該Log文件中的內(nèi)容成為在Master端真實(shí)執(zhí)行時(shí)候的那些可執(zhí)行的Query語句，并在自身執(zhí)行這些Query。這樣，實(shí)際上就是在Master端和Slave端執(zhí)行了同樣的Query，所以兩端的數(shù)據(jù)是完全一樣的。二、設(shè)置MySQL主從配置的優(yōu)點(diǎn)1、解決web應(yīng)用系統(tǒng)，數(shù)據(jù)庫出現(xiàn)的性能瓶頸，采用數(shù)據(jù)庫集群的方式來實(shí)現(xiàn)查詢負(fù)載；一個(gè)系統(tǒng)中數(shù)據(jù)庫的查詢操作比更新操作要多得多，通過多臺查詢服務(wù)器將數(shù)據(jù)庫的查詢分擔(dān)到不同的查詢服務(wù)器上從而提高查詢效率。2、MySQL數(shù)據(jù)庫支持?jǐn)?shù)據(jù)庫的主從復(fù)制功能，使用主數(shù)據(jù)庫進(jìn)行數(shù)據(jù)的插入、刪除與更新操作，而從數(shù)據(jù)庫則專門用來進(jìn)行數(shù)據(jù)查詢操作，這樣可以將更新操作和查詢操作分擔(dān)到不同的數(shù)據(jù)庫上，從而提高了查詢效率。三、主從數(shù)據(jù)庫服務(wù)器的配置（可以設(shè)置一個(gè)或者多個(gè)從服務(wù)器）環(huán)境：數(shù)據(jù)庫版本號：5.5.32MySQL主庫電腦的IP：5叢庫電腦的IP：1首先查看兩臺機(jī)器之間的網(wǎng)絡(luò)是否是通的用其中的一臺ping另外一臺，如下用IP地址為1的主機(jī)ping5的機(jī)器，其結(jié)果如下：如果結(jié)果如上圖所示，則說明兩臺機(jī)器的網(wǎng)絡(luò)是通的，否則請先檢查網(wǎng)絡(luò)。2、打開主數(shù)據(jù)庫，在主數(shù)據(jù)庫里面建一個(gè)需要同步的數(shù)據(jù)庫，如下user數(shù)據(jù)庫配置主數(shù)據(jù)庫服務(wù)器(1)、修改MySQL的配置文件（my.ini），如下，在mysqlld里面添加：上面的這些配置的含義：-server-id 顧名思義就是服務(wù)器標(biāo)識id號了，每一臺服務(wù)器都有一個(gè)固定 的標(biāo)示id。-log-bin MySQL進(jìn)行主從復(fù)制是通過二進(jìn)制的日志文件來進(jìn)行的，此處是 開啟MySQL的日志功能-binlog-do-db 是你需要復(fù)制的數(shù)據(jù)庫名稱，-binlog-ignore-db 是不需要復(fù)制的數(shù)據(jù)庫名稱（2）、重新啟動(dòng)主數(shù)據(jù)庫，給主數(shù)據(jù)庫服務(wù)器授予一個(gè)可以進(jìn)行復(fù)制的用戶，如下的slave，這樣從服務(wù)器就能通過這個(gè)連接用戶有權(quán)限來訪問主數(shù)據(jù)庫服務(wù)器：GRANTREPLICATIONSLAVE,REPLICATIONCLIENT,RELOAD,SUPERON*.*TO'slave'@'1'IDENTIFIEDBY'123456'withgrantoption;如下： 密碼 用戶權(quán)限 用戶名從機(jī)IP設(shè)置完畢以后可以通過NAVCAT查看權(quán)限：(3)、重新啟動(dòng)主數(shù)據(jù)庫，通過mysql>showmasterstatus;監(jiān)控主服務(wù)器的狀態(tài)*記下file和possion的值，這里的file，position會(huì)在從庫的配置里用到。4、從數(shù)據(jù)庫服務(wù)器的配置(1)、修改MySQL的配置文件（my.ini），如下，在mysqlld里面添加：、#server-id=1####必須把server-id=1注釋掉，上面的這些配置的含義：-server-id=2從數(shù)據(jù)庫的唯一標(biāo)示id為2-master-host=5#主數(shù)據(jù)庫的ip地址-master-user=slave #主數(shù)據(jù)庫已經(jīng)授權(quán)的可以連接復(fù)制的用戶的用戶名-master-password=123456 #主數(shù)據(jù)庫已經(jīng)授權(quán)的可以連接復(fù)制的用戶的密碼-master-port=3306 #主數(shù)據(jù)庫的端口號、啟動(dòng)從數(shù)據(jù)庫我當(dāng)時(shí)遇到的情況是沒有成功啟動(dòng)，查看error日志發(fā)現(xiàn)然后把下面四行注釋掉master-host=5#主數(shù)據(jù)庫的ip地址master-user=slave #主數(shù)據(jù)庫已經(jīng)授權(quán)的可以連接復(fù)制的用戶的用戶名master-password=123456 #主數(shù)據(jù)庫已經(jīng)授權(quán)的可以連接復(fù)制的用戶的密碼master-port=3306 #主數(shù)據(jù)庫的端口號再執(zhí)行下面的命令。slavestop;changemastertomaster_host='5',master_user='slave',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=107;slavestart;如下：(3)、可以通過showslavestatus;來看看從庫的情況。不過信息太多。為了方便查看可以用下面這個(gè)命令showslavestatus\G;顯示結(jié)果如下：查看上面的Slave_IO_Running:Yes和Slave_SQL_Running:Yes2個(gè)都為yes則證明主從同步正常，如果有任一個(gè)顯示NO,則證明同步有問題。可以查看數(shù)據(jù)庫日志文件，里面基本上會(huì)顯示出錯(cuò)誤之處，根據(jù)錯(cuò)誤一步一步排查，基本上都可以解決的。在從數(shù)據(jù)庫中創(chuàng)建一個(gè)與要備份的主數(shù)據(jù)庫相同的數(shù)據(jù)庫名，則配置完成。從數(shù)據(jù)庫會(huì)隨著主數(shù)據(jù)庫的改變動(dòng)態(tài)的同步數(shù)據(jù)庫。如下，在主數(shù)據(jù)庫的user表中創(chuàng)建一個(gè)user表此時(shí)查看從數(shù)據(jù)庫，可以發(fā)現(xiàn)：則說明主從數(shù)據(jù)庫搭建成功。五、常見問題與解答1、現(xiàn)象：修改完配置后，重啟MySQL，提示MySQLmanagerorserverPIDfilecouldnotbefound解決：查找進(jìn)程中的MySQL，kill掉>serviceMySQLrestart>ps-aux|grepmyslq>kill進(jìn)程號#serviceMySQLstart注：MySQL.sock文件在MySQL啟動(dòng)后才會(huì)生成，位置為/tmp下或/var/lib/MySQL/下2、現(xiàn)象：從數(shù)據(jù)庫無法同步，showslavestatus顯示Slave_SQL_Running為No,Seconds_Behind_Master為null原因：a.程序可能在slave上進(jìn)行了寫操作b.也可能是slave機(jī)器重起后，事務(wù)回滾造成的.解決：方法一：MySQL>slavestop;MySQL>setGLOBALSQL_SLAVE_SKIP_COUNTER=1;MySQL>slavestart;解決：方法二：Slave庫，MySQL>slavestop;--停掉slave服務(wù)Master庫，MySQL>showmasterstatus;+----------------------+----------+--------------+------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|+----------------------+----------+--------------+------------------+|MySQL-bin.000040|324|||+----------------------+----------+--------------+------------------+然后到slave服務(wù)器上執(zhí)行手動(dòng)同步MySQL>changemasterto>master_host='',>master_user='repuser',>master_password='123456',>master_port=3306,>master_log_file='MySQL-bin.000040',>master_log_pos=324;啟動(dòng)slave服務(wù)，MySQL>slavestart;通過showslavestatus查看Slave_SQL_Running為Yes,Seconds_Behind_Master為0，即為正常。注：手動(dòng)同步需要停止master的寫操作！3、現(xiàn)象：從數(shù)據(jù)庫無法同步，showslavestatus顯示Slave_IO_Running為No,Seconds_Behind_Master為null解決：重啟主數(shù)據(jù)庫serviceMySQLrestartMySQL>showmasterstatus;+------------------+----------+--------------+------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|+------------------+----------+------