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

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

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

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

手動同步數(shù)據(jù)

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

配置master庫

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

配置slave庫

[mysqld]
server-id?=?171?????#唯一
log-bin?=?/var/log/mysql/mysql-bin??
log_slave_updates?=?1?????#將復(fù)制事件寫進(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對應(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ī)器上連接主庫并啟動線程：

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;

測試 slave:

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

如果都是Yes，說明slave的I/O和SQL線程都已經(jīng)開始運(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的線程正在運(yùn)行。如果我們在數(shù)據(jù)庫中新建表和添加數(shù)據(jù)，在slave中也會同步。

踩過的坑

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

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

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

可能原因：my.cnf配置文件沒指定relay_log或者指定路徑錯誤，注意空格。解決方法：?
停止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;啟動slave?
mysql>START SLAVE;

無法同步，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沒有設(shè)置server-id，這里要注意，server-id一定要寫在[mysqld]下面，如果裝的是wamp集成環(huán)境的話這個server-id默認(rèn)在[wampmysqld]下面了，這是不行的。解決辦法：添加配置?
[mysqld]?
server-id = 1無法同步，[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庫中有userdb數(shù)據(jù)庫和table表而slave中沒有，而且同步之前沒有把master庫中已有的數(shù)據(jù)傳到slave中。忘了哪里看到的好像數(shù)據(jù)庫引擎不一樣也可能會出現(xiàn)這個問題…解決辦法：手動將userdb數(shù)據(jù)庫傳到slave中并重新設(shè)置同步。無法同步，[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文件名指定錯誤。解決辦法：在master上使用mysql> show master statusG?查看File并在slave上重新連接master。配置項(xiàng)下劃線和中杠要分清，比如：log-bin和log_binslave跟master的MySQL版本一致，是官方推薦的方式；至少版本前兩個號相同，可高于master；版本不一致，可能出現(xiàn)的問題就是同步的不穩(wěn)定（兼容性的老問題），因?yàn)闀谀承┖瘮?shù)處理、日志讀取、日志的解析重演等上發(fā)生異常，導(dǎo)致同步報(bào)錯而需手工處理。注意檢查防火墻

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