MySQL主從架構(gòu)配置

有兩臺(tái)MySQL數(shù)據(jù)庫(kù)服務(wù)器master和slave，master為主服務(wù)器，slave為從服務(wù)器，初始狀態(tài)時(shí)，master和slave中的數(shù)據(jù)信息相同，當(dāng)master中的數(shù)據(jù)發(fā)生變化時(shí)，slave也跟著發(fā)生相應(yīng)的變化，使得master和slave的數(shù)據(jù)信息同步，達(dá)到備份的目的。

MySQL復(fù)制的基本過(guò)程 Slave上面的IO線(xiàn)程連接上Master，并請(qǐng)求從指定日志文件的指定位置（或者從?
最開(kāi)始的日志）之后的日志內(nèi)容；Master接收到來(lái)自Slave的IO線(xiàn)程的請(qǐng)求后，通過(guò)負(fù)責(zé)復(fù)制的IO線(xiàn)程根據(jù)請(qǐng)?
求信息讀取指定日志指定位置之后的日志信息，返回給Slave端的IO線(xiàn)程。返回信?
息中除了日志所包含的信息之外，還包括本次返回的信息在Master端的Binary Log?
文件的名稱(chēng)以及在Binary Log中的位置；Slave的IO線(xiàn)程接收到信息后，將接收到的日志內(nèi)容依次寫(xiě)入到Slave端的?
Relay Log文件(mysql-relay-bin.xxxxxx)的最末端，并將讀取到的Master端的binlog的文件名和位置記錄到master-info文件中，以便在下一次讀取的時(shí)候能夠清楚的?
告訴Master“我需要從某個(gè)bin-log的哪個(gè)位置開(kāi)始往后的日志內(nèi)容，請(qǐng)發(fā)給我”Slave的SQL線(xiàn)程檢測(cè)到Relay Log中新增加了內(nèi)容后，會(huì)馬上解析該Log文?
件中的內(nèi)容成為在Master端真實(shí)執(zhí)行時(shí)候的那些可執(zhí)行的Query語(yǔ)句，并在自身執(zhí)?
行這些Query。這樣，實(shí)際上就是在Master端和Slave端執(zhí)行了同樣的Query，所?
以?xún)啥说臄?shù)據(jù)是完全一樣的。 應(yīng)用場(chǎng)景 數(shù)據(jù)分布負(fù)載均衡備份高可用和容錯(cuò) 創(chuàng)建同步帳號(hào)

在master的數(shù)據(jù)庫(kù)中建立一個(gè)備份帳戶(hù)：每個(gè)slave使用標(biāo)準(zhǔn)的mysql用戶(hù)名和密碼連接master。進(jìn)行復(fù)制操作的用戶(hù)會(huì)授予REPLICATION SLAVE權(quán)限。?
命令如下：?
mysql > GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO 'slave'@'192.168.0.171' IDENTIFIED BY '123456';?
建立一個(gè)帳戶(hù)slave，并且只能允許從192.168.0.171這個(gè)地址上來(lái)登陸。添加用戶(hù)后，可在從服務(wù)器上用mysql -h192.168.0.151 -uslave -p123456;?來(lái)測(cè)試是否有權(quán)限訪問(wèn)主數(shù)據(jù)庫(kù)。

手動(dòng)同步數(shù)據(jù)

如果要為已經(jīng)有數(shù)據(jù)的主服務(wù)器添加從服務(wù)器，要先把主庫(kù)已經(jīng)存在的數(shù)據(jù)先手動(dòng)同步遷移到從庫(kù)上面去。搭建過(guò)程中，禁止在主庫(kù)從庫(kù)上進(jìn)行任何對(duì)數(shù)據(jù)庫(kù)的ddl、dml等數(shù)據(jù)操作。?
這里可以用mysqldump也可以用xtrabackup導(dǎo)出主庫(kù)上面的數(shù)據(jù)，還可以直接用可視化管理工具(如navicat)把數(shù)據(jù)傳輸?shù)綇膸?kù)上面。

配置master庫(kù)

[mysqld]
server-id?=?151?????#唯一??
log-bin?=?/data/mysql/binlog/mysql-bin

配置slave庫(kù)

[mysqld]
server-id?=?171?????#唯一
log-bin?=?/var/log/mysql/mysql-bin??
log_slave_updates?=?1?????#將復(fù)制事件寫(xiě)進(jìn)自己的二進(jìn)制日志
relay_log_index?=?/var/log/mysql/slave-relay-bin.index
relay_log?=?/var/log/mysql/slave-relay-bin
replicate-do-db?=?userdb
replicate-do-db?=?osadmin
replicate-ignore-db?=?mysql
replicate-ignore-db?=?information_schema
replicate-ignore-db?=?performance_schema

到主服務(wù)器上查看主機(jī)狀態(tài)，記錄File和Position對(duì)應(yīng)的值。

mysql>?SHOW?MASTER?STATUSG??
File:?mysql-bin.000001??
Position:?120??
Binlog_Do_DB:??
Binlog_Ignore_DB:??
Executed_Gtid_Set:

File在binlog.000001 Position是120?
在slave機(jī)器上連接主庫(kù)并啟動(dòng)線(xiàn)程：

mysql>?CHANGE?MASTER?TO
Master_Host='192.168.0.151',
Master_Port=3306,?
Master_User='slave',
Master_Password='123456',?
Master_Log_File='mysql-bin.000001',
Master_Log_Pos=120,
Master_Connect_Retry=60;
mysql>?START?SLAVE;

測(cè)試 slave:

mysql>?SHOW?SLAVE?STATUSG??
...
Slave_IO_Running:?Yes??
Slave_SQL_Running:?Yes
...

如果都是Yes，說(shuō)明slave的I/O和SQL線(xiàn)程都已經(jīng)開(kāi)始運(yùn)行，配置成功。

master:

mysql>?SHOW?PROCESSLISTG
...
Id:?6
User:?slave
Host:?192.168.0.171:52166
db:?NULL
Command:?Binlog?Dump
Time:?278
State:?Master?has?sent?all?binlog?to?slave;?waiting?for?binlog?to?be?updated
Info:?NULL

可以看到slave的線(xiàn)程正在運(yùn)行。如果我們?cè)跀?shù)據(jù)庫(kù)中新建表和添加數(shù)據(jù)，在slave中也會(huì)同步。

踩過(guò)的坑

MySQL無(wú)法關(guān)閉或重啟：?
Warning: World-writable config file '/etc/my.cnf' is ignored

原因是my.cnf配置文件權(quán)限全局可寫(xiě)。mysql擔(dān)心這種文件被其他用戶(hù)惡意修改，所以忽略掉這個(gè)配置文件。這樣mysql無(wú)法關(guān)閉。解決辦法：修改my.cnf權(quán)限?chmod 644 /etc/my.cnf

start slave出錯(cuò)：?
[Err] 1872 - Slave failed to initialize relay log info structure from the repository

可能原因：my.cnf配置文件沒(méi)指定relay_log或者指定路徑錯(cuò)誤，注意空格。解決方法：?
停止slave?
mysql>STOP SLAVE;在my.cnf中添加?
relay_log=/var/log/mysql/relay_log?
relay_log_index=/var/log/mysql/relay_log.index重置slave日志并連接master?
mysql>RESET SLAVE;?
mysql>CHANGE MASTER TO?
Master_Host='192.168.0.151',?
Master_Port=3306,?
Master_User='slave',?
Master_Password='123456',?
Master_Log_File='mysql-bin.000009',?
Master_Log_Pos=159;啟動(dòng)slave?
mysql>START SLAVE;

無(wú)法同步，error.log：?
[ERROR] Error reading packet from server: Misconfigured master - server_id was not set ( server_errno=1236)?
[ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Misconfigured master - server_id was not set', Error_code: 1236

原因很明顯，master沒(méi)有設(shè)置server-id，這里要注意，server-id一定要寫(xiě)在[mysqld]下面，如果裝的是wamp集成環(huán)境的話(huà)這個(gè)server-id默認(rèn)在[wampmysqld]下面了，這是不行的。解決辦法：添加配置?
[mysqld]?
server-id = 1無(wú)法同步，[SQL]SHOW SLAVE STATUS;?
Last_Error：Error 'Unknown database 'userdb'' on query. Default database: 'userdb'...?
或者?
Last_Error：Error 'Table 'userdb.table' doesn't exist' on query. Default database: 'userdb'...?
可能原因：master庫(kù)中有userdb數(shù)據(jù)庫(kù)和table表而slave中沒(méi)有，而且同步之前沒(méi)有把master庫(kù)中已有的數(shù)據(jù)傳到slave中。忘了哪里看到的好像數(shù)據(jù)庫(kù)引擎不一樣也可能會(huì)出現(xiàn)這個(gè)問(wèn)題…解決辦法：手動(dòng)將userdb數(shù)據(jù)庫(kù)傳到slave中并重新設(shè)置同步。無(wú)法同步，[SQL]SHOW SLAVE STATUS;?
Last_Error：Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
原因：master_log_file文件名指定錯(cuò)誤。解決辦法：在master上使用mysql> show master statusG?查看File并在slave上重新連接master。配置項(xiàng)下劃線(xiàn)和中杠要分清，比如：log-bin和log_binslave跟master的MySQL版本一致，是官方推薦的方式；至少版本前兩個(gè)號(hào)相同，可高于master；版本不一致，可能出現(xiàn)的問(wèn)題就是同步的不穩(wěn)定（兼容性的老問(wèn)題），因?yàn)闀?huì)在某些函數(shù)處理、日志讀取、日志的解析重演等上發(fā)生異常，導(dǎo)致同步報(bào)錯(cuò)而需手工處理。注意檢查防火墻

多說(shuō)幾句 官方文檔推薦的是，在master端不指定binlog-do-db，在slave端用replication-do-db來(lái)過(guò)濾。不要在my.cnf/my.ini中配制master_host等選項(xiàng)，而應(yīng)該使用CHANGE MASTER TO命令來(lái)動(dòng)態(tài)設(shè)置！Mysql版本從5.1.7以后開(kāi)始就不支持”master-host”類(lèi)似的參數(shù)。詳情可以查看http://dev.mysql.com/doc/refman/5.5/en/replication-howto.html