下文內(nèi)容主要給大家?guī)?lái)mysql主從配置定義及流程講析，這里所講到的知識(shí)，與書(shū)籍略有不同，都是創(chuàng)新互聯(lián)專業(yè)技術(shù)人員在與用戶接觸過(guò)程中，總結(jié)出來(lái)的，具有一定的經(jīng)驗(yàn)分享價(jià)值，希望給廣大讀者帶來(lái)幫助。
Mysql主從同步的配置
主庫(kù)IP：192.168.1.2
從庫(kù)IP：192.168.1.3
添加一個(gè)用于主從同步的用戶：
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY ‘1q2w3e4r’;
如果監(jiān)控mysql主從的話，請(qǐng)加上一個(gè)super權(quán)限：
GRANT SUPER, REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY '1q2w3e4r';
1、主庫(kù)的配置
1.1．mysql5.0以下版本的配置
修改主庫(kù)mysql配置配置文件，在[mysqld]段添加以下內(nèi)容：
server-id = 1
log-bin=/home/mysql/logs/binlog/bin-log
max_binlog_size = 500M
binlog_cache_size = 128K
binlog-do-db = adb
binlog-ignore-db = mysql
log-slave-updates
1.2. mysql5.0以上版本的配置
修改主庫(kù)mysql配置配置文件，在[mysqld]段添加以下內(nèi)容：
server-id = 1
log-bin=/home/mysql/logs/binlog/bin-log
max_binlog_size = 500M
binlog_cache_size = 128K
binlog-do-db = adb
binlog-ignore-db = mysql
log-slave-updates
expire_logs_day=2
binlog_format="MIXED"
1.3.各個(gè)參數(shù)的含義和相關(guān)注意項(xiàng)：
server-id = 1 #云服務(wù)器標(biāo)志號(hào)，注意在配置文件中不能出現(xiàn)多個(gè)這樣的標(biāo)識(shí)，如果出現(xiàn)多個(gè)的話mysql以第一個(gè)為準(zhǔn)，一組主從中此標(biāo)識(shí)號(hào)不能重復(fù)。
log-bin=/home/mysql/logs/binlog/bin-log #開(kāi)啟bin-log，并指定文件目錄和文件名前綴。
max_binlog_size = 500M #每個(gè)bin-log大大小，當(dāng)此大小等于500M時(shí)會(huì)自動(dòng)生成一個(gè)新的日志文件。一條記錄不會(huì)寫在2個(gè)日志文件中，所以有時(shí)日志文件會(huì)超過(guò)此大小。
binlog_cache_size = 128K #日志緩存大小
binlog-do-db = adb #需要同步的數(shù)據(jù)庫(kù)名字，如果是多個(gè)，就以此格式在寫一行即可。
binlog-ignore-db = mysql #不需要同步的數(shù)據(jù)庫(kù)名字，如果是多個(gè)，就以此格式在寫一行即可。
log-slave-updates #當(dāng)Slave從Master數(shù)據(jù)庫(kù)讀取日志時(shí)更新新寫入日志中，如果只啟動(dòng)log-bin 而沒(méi)有啟動(dòng)log-slave-updates則Slave只記錄針對(duì)自己數(shù)據(jù)庫(kù)操作的更新。
expire_logs_day=2 #設(shè)置bin-log日志文件保存的天數(shù)，此參數(shù)mysql5.0以下版本不支持。
binlog_format="MIXED" #設(shè)置bin-log日志文件格式為：MIXED，可以防止主鍵重復(fù)。
2、從庫(kù)的配置
2.1.mysql5.1.7以前版本
修改從庫(kù)mysql配置配置文件，在[mysqld]段添加以下內(nèi)容：
server-id=2
master-host=192.168.1.2
master-user=repl
master-password=1q2w3e4r
master-port=3306
master-connect-retry=30
slave-skip-errors=1062
replicate-do-db = adb
replicate-ignore-db = mysql
slave-skip-errors=1007,1008,1053,1062,1213,1158,1159
master-info-file = /home/mysql/logs/master.info
relay-log = /home/mysql/logs/relay-bin
relay-log-index = /home/mysql/logs/relay-bin.index
relay-log-info-file = /home/mysql/logs/relay-log.info
如果修改了連接主庫(kù)相關(guān)信息，重啟之前一定要?jiǎng)h除master.info文件，否則重啟之后由于連接信息改變從庫(kù)而不會(huì)自動(dòng)連接主庫(kù)，造成同步失敗。此文件是保存連接主庫(kù)信息的。
2.2.mysql5.1.7以后版本
Mysql5.1.7版本在叢庫(kù)上面的配置很少，主要是采用了新的同步信息記錄方式，他不在支持在配置文件中配置連接主庫(kù)的相關(guān)信息，而是把連接等相關(guān)信息記錄在master-info-file = /home/mysql/logs/master.info文件中，如果入庫(kù)變了，直接在mysql命令行執(zhí)行連接信息的改變即可生效，比較靈活了，而不用去重啟mysql。修改從庫(kù)mysql配置配置文件，在[mysqld]段添加以下內(nèi)容：
slave-skip-errors=1007,1008,1053,1062,1213,1158,1159
2.3. 各個(gè)參數(shù)的含義和相關(guān)注意項(xiàng)
這里只講一下2個(gè)參數(shù)，其他全部是從庫(kù)連接主庫(kù)的信息和中間日志relay-log的設(shè)置。
master-connect-retry=30 #這個(gè)選項(xiàng)控制重試間隔，默認(rèn)為60秒。
slave-skip-errors=1007,1008,1053,1062,1213,1158,1159 #這個(gè)是在同步過(guò)程中忽略掉的錯(cuò)誤，這些錯(cuò)誤不會(huì)影響數(shù)據(jù)的完整性，有事經(jīng)常出現(xiàn)的錯(cuò)誤，一般設(shè)置忽略。其中1062為主鍵重復(fù)錯(cuò)誤。
3、實(shí)現(xiàn)主從同步
3.1.實(shí)現(xiàn)數(shù)據(jù)庫(kù)的統(tǒng)一
檢查主從數(shù)據(jù)庫(kù)的配置文件，查看是否已正確配置。首次實(shí)現(xiàn) 同步要備份主庫(kù)上需要同步的數(shù)據(jù)庫(kù)，然后完整的導(dǎo)入到從庫(kù)中。注：mysql5.0之前的版本涉及到mysql本身復(fù)制過(guò)濾存在問(wèn)題，需要把所有的數(shù)據(jù)庫(kù)都備份導(dǎo)入到叢庫(kù)，保持。
3.2.查看并記錄主庫(kù)bin-log信息
進(jìn)入主庫(kù)mysql中，執(zhí)行：show master status;顯示信息如下：
mysql> show master status;
+-------------+----------+--------------+------------------+
| File | Position | Binlog_do_db | Binlog_ignore_db |
+-------------+----------+--------------+------------------+
| bin-log.003 | 4 | adb | mysql |
+-------------+----------+--------------+------------------+
1 row in set (0.00 sec)
記錄File 和Position信息；
3.3.在從庫(kù)上執(zhí)行同步語(yǔ)句
進(jìn)入mysql，執(zhí)行以下語(yǔ)句：
slave stop;
change master to
master_host='192.168.1.2',
master_user='repl',
master_password='1q2w3e4r',
master_port=3306,
master_log_file='bin-log.003',
master_log_pos=4;
slave start;
3.4.查看主從同步狀態(tài)
進(jìn)入mysql，執(zhí)行show slave status\G;顯示如下（mysql版本不同查詢的結(jié)果不同，但是重要的指標(biāo)還是一樣的）：
Mysql5.0之前的版本如下：
Mysql5.5之前的版本如下：
Mysql5.5的版本如下：
重要的指標(biāo)為：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_Log_File: bin-log.003
Relay_Master_Log_File: bin-log.003
Read_Master_Log_Pos: 4
Exec_master_log_pos: 4
Seconds_Behind_Master: 0（5.0之前版本沒(méi)有這個(gè)選項(xiàng)）
以上選項(xiàng)是兩兩對(duì)應(yīng)的，只要結(jié)果是一致的，就說(shuō)明主從同步成功。
3.5.同步中的常見(jiàn)的錯(cuò)誤和處理
1、現(xiàn)象：在從庫(kù)上面show slave status\G;出現(xiàn)下列情況，
Slave_IO_Running: Yes
Slave_SQL_Running: No
Seconds_Behind_Master: NULL
原因：
a.程序可能在slave上進(jìn)行了寫操作；
b.也可能是slave機(jī)器重起后，事務(wù)回滾造成的；
c．有可能是在同步過(guò)程中遇到某種錯(cuò)誤，這個(gè)會(huì)在查看從庫(kù)中狀態(tài)時(shí)看到錯(cuò)誤提示，最少見(jiàn)的就是主鍵重復(fù)1062的錯(cuò)誤。
解決方法：
進(jìn)入master
mysql> show master status;
+----------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| mysql-bin.000040 | 324 |adb | mysql|
+----------------------+----------+--------------+------------------+
然后到slave服務(wù)器上執(zhí)行手動(dòng)同步
slave stop;
change master to
master_host='10.14.0.140',
master_user='repl',
master_password='1q2w3e4r',
master_port=3306,
master_log_file='mysql-bin.000040',
master_log_pos=324;
slave start;
show slave status\G;
2、現(xiàn)象：從數(shù)據(jù)庫(kù)無(wú)法同步，show slave status顯示:
Slave_IO_Running: No
Slave_SQL_Running: Yes
Seconds_Behind_Master: NULL
解決：首先查看數(shù)據(jù)庫(kù)的err日志，查看是什么錯(cuò)誤提示，看從庫(kù)連接主庫(kù)的IP、用戶、密碼等相關(guān)信息是否有誤，如果有誤，重新執(zhí)行同步；如果確認(rèn)無(wú)誤，重啟主數(shù)據(jù)庫(kù)。
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 98 | adb| mysql|
+------------------+----------+--------------+------------------+
進(jìn)入從庫(kù)mysql，執(zhí)行：
slave stop;
change master to Master_Log_File='mysql-bin.000001',Master_Log_Pos=98;
slave start;
或是這樣：
stop slave;
set global sql_slave_skip_counter =1;
start slave;
這個(gè)現(xiàn)象主要是master數(shù)據(jù)庫(kù)存在問(wèn)題，由于連接主庫(kù)信息錯(cuò)誤、主庫(kù)數(shù)據(jù)庫(kù)掛掉如果說(shuō)常見(jiàn)錯(cuò)等原因引起的，我在實(shí)際的操作中先重啟master后重啟slave即可解決這問(wèn)題，出現(xiàn)此問(wèn)題，必須要要重啟master數(shù)據(jù)庫(kù)。
四、mysql主主和主主集群
1、mysql主主的實(shí)現(xiàn)
在實(shí)際的生產(chǎn)應(yīng)用中，為了在主庫(kù)出現(xiàn)崩潰或是主服務(wù)器出現(xiàn)嚴(yán)重故障時(shí)快速的恢復(fù)業(yè)務(wù)，會(huì)直接切換到從庫(kù)上，當(dāng)主庫(kù)故障處理完成后讓他直接作為叢庫(kù)來(lái)運(yùn)行，此時(shí)主主就是一個(gè)不錯(cuò)的選擇。
五、mysql主從的監(jiān)控
在mysql主從的應(yīng)用中，只要進(jìn)行了合理設(shè)置，基本上不會(huì)出現(xiàn)問(wèn)題，但是對(duì)他的監(jiān)控是必不可少的，以免由于真的出現(xiàn)問(wèn)題又不知道而造成不必要的數(shù)據(jù)損失。
1、mysql主從監(jiān)控的主要思路
Mysql主從的監(jiān)控，其主要是監(jiān)控從庫(kù)上的一些重要參數(shù)：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_Log_File: bin-log.003
Relay_Master_Log_File: bin-log.003
Read_Master_Log_Pos: 4
Exec_master_log_pos: 4
Seconds_Behind_Master: 0（5.0之前版本沒(méi)有這個(gè)選項(xiàng)）
通過(guò)以上的參數(shù)可以反映出主庫(kù)和從庫(kù)狀態(tài)是否正常，從庫(kù)是否落后于主庫(kù)等。值得一提的是在mysql5.0以前的版本，Slave_IO_Running這個(gè)狀態(tài)指標(biāo)不可靠，會(huì)在主庫(kù)直接掛掉的情況下不會(huì)變成NO，Seconds_Behind_Master參數(shù)也不存在。監(jiān)控以上參數(shù)即可監(jiān)控mysql主從。
2、mysql主從監(jiān)控的實(shí)現(xiàn)
不管mysql是那個(gè)版本，其中的從庫(kù)上的Exec_master_log_pos、Exec_master_log_pos；主庫(kù)上的 Master上的Log_File， Position，這四個(gè)參數(shù)可以判斷出當(dāng)前主從的狀態(tài)。以下是適用于mysql所有版本的主從監(jiān)控shell腳本：
#/bin/sh
user=repl
passwd=123415
master_ip="192.168.1.2"
log="/data3/check_repl.log"
value()
{
master=`/usr/local/mysql/bin/mysql -u$user -p$passwd -h$master_ip -e "show master status\G;"|egrep "File|Position"`
#mysql 4.0
slave=`/usr/local/mysql/bin/mysql -u$user -p$passwd -h227.0.0.1 -e "show slave status\G;"|egrep "Relay_Master_Log_File|Exec_master_log_pos"`
#mysql 5.0
#slave=`mysql -u$user -p$passwd -e "show slave status\G;"|egrep "Relay_Master_Log_File|Exec_Master_Log_Pos"`
#取主庫(kù)上的bin-log號(hào)及寫入的當(dāng)前日志位置
Master_Log=`echo $master |awk '{print $2}'|awk -F "." '{print $2}'`
Master_Log_Pos=`echo $master |awk '{print $4}'`
#取從庫(kù)上當(dāng)前同步主庫(kù)的位置
Relay_Master_Log_File=`echo $slave |awk '{print $2}'|awk -F "." '{print $2}'`
Exec_Master_Log_Pos=`echo $slave |awk '{print $4}'`
echo "Master_Log:"$Master_Log>>$log
echo "Master_Log_Pos:"$Master_Log_Pos>>$log
echo "Relay_Master_Log_File:"$Relay_Master_Log_File>>$log
echo "Exec_Master_Log_Pos:"$Exec_Master_Log_Pos>>$log
}
for((i=1;i<=10;i++));
do
echo "#################################">>$log
value
time=`date +"%Y-%m-%d %H:%M:%S"`
if [ $Master_Log -eq $Relay_Master_Log_File ];then
A=`expr $Master_Log_Pos - $Exec_Master_Log_Pos`
if [ $A -lt 0 ];then
A=`expr 0 - $A`
fi
echo $A>>$log
if [ $A -lt 10000 ];then
echo "$time Master-Slave is OK.">>$log
#echo "$i"
break
else
if [ $i ge 3 ];then
echo "$time Warning:Slave-Master lag $A " >>$log
echo "$i"
fi
sleep 30
continue
fi
else
sleep 60
fi
if [ $i -eq 10 ];then
echo "$i"
echo "$time Error:Slave-Master must be check !" >>$log
fi
done
在mysql5.0以后的版本，mysql主從已經(jīng)相當(dāng)?shù)某墒炝?，可以只監(jiān)控Slave_IO_Running，Slave_SQL_Running，Seconds_Behind_Master狀態(tài)就可以了，這里不再做說(shuō)明。
對(duì)于以上關(guān)于mysql主從配置定義及流程講析，如果大家還有更多需要了解的可以持續(xù)關(guān)注我們創(chuàng)新互聯(lián)的行業(yè)推新，如需獲取專業(yè)解答，可在官網(wǎng)聯(lián)系售前售后的，希望該文章可給大家?guī)?lái)一定的知識(shí)更新。
另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無(wú)理由+7*72小時(shí)售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國(guó)服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡(jiǎn)單易用、服務(wù)可用性高、性價(jià)比高”等特點(diǎn)與優(yōu)勢(shì)，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場(chǎng)景需求。
重慶分公司
重慶分公司
