mysql的主從復(fù)制如何配置-創(chuàng)新互聯(lián)

這篇文章給大家分享的是有關(guān)mysql的主從復(fù)制如何配置的內(nèi)容。小編覺(jué)得挺實(shí)用的，因此分享給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧。

吉安網(wǎng)站建設(shè)公司成都創(chuàng)新互聯(lián),吉安網(wǎng)站設(shè)計(jì)制作，有大型網(wǎng)站制作公司豐富經(jīng)驗(yàn)。已為吉安上千多家提供企業(yè)網(wǎng)站建設(shè)服務(wù)。企業(yè)網(wǎng)站搭建\外貿(mào)營(yíng)銷(xiāo)網(wǎng)站建設(shè)要多少錢(qián)，請(qǐng)找那個(gè)售后服務(wù)好的吉安做網(wǎng)站的公司定做！

數(shù)據(jù)庫(kù)復(fù)制對(duì)于系統(tǒng)高可用、高性能的提升扮演者很重要的角色。

1 主庫(kù)配置

1.1 my.cnf配置：

在主庫(kù)配置文件my.cnf中進(jìn)行如下基本配置：

log-bin  =  mysql-bin　//二進(jìn)制日志文件名稱(chēng)主體
log-bin-index  =  mysql-bin.index　//二進(jìn)制日志文件索引文件
server-id  =  1　//的服務(wù)器ID，為了保持性，可以去ip的尾部
binlog-format  =  mixed　//控制復(fù)制基于的方式，有基于語(yǔ)句(statement)，基于行(row)，混合(mixed)，**主從庫(kù)需要保持一致**
#sync_binlog=1 //推薦配置，開(kāi)啟該選項(xiàng)，mysql每次在事務(wù)提交前會(huì)將二進(jìn)制日志同步到磁盤(pán)上，保證在服務(wù)器崩潰時(shí)不會(huì)丟失事件。

默認(rèn)復(fù)制全部數(shù)據(jù)庫(kù)，如果需要指定數(shù)據(jù)庫(kù)，請(qǐng)參照第7節(jié)（復(fù)制過(guò)濾）。

比如說(shuō)要指定db1和db2兩個(gè)數(shù)據(jù)庫(kù)進(jìn)行主從復(fù)制：
binlog-do-db = db1
binlog-do-db = db2

1.2 添加復(fù)制賬戶(hù)：

復(fù)制賬戶(hù)添加以及權(quán)限設(shè)置：

mysql> grant replication slave, replicatin client on \*.\* to repl@'172.16.226.192' identified by 'repl123456';　//其中repl是用戶(hù)名，repl123456為賬戶(hù)密碼，172.16.226.168為備庫(kù)的地址。
mysql> flush privileges;　//在不重啟mysql服務(wù)的情況下完成權(quán)限的更新

2 備庫(kù)配置

在備庫(kù)配置文件my.cnf中進(jìn)行如下基本配置：

relay-log  =  slave-relay-bin　//中繼日志文件名稱(chēng)主體
relay-log-index  =  slave-relay-bin.index　//中繼日志文件索引文件
server-id  =  2　//的服務(wù)器ID，必須要異于主庫(kù)
#read_only = 1　//限制備庫(kù)為只讀，可選
log_slave_updates = 1　//控制是否在中繼日志執(zhí)行之后，將同步過(guò)來(lái)的數(shù)據(jù)添加到自己的binlog中去，1代表是
skip_slave_start // 該選項(xiàng)能夠阻止備庫(kù)在崩潰后自動(dòng)啟動(dòng)復(fù)制，建議開(kāi)啟
即使開(kāi)啟了建議的選項(xiàng)，備庫(kù)仍然可能在崩潰后被中斷，因?yàn)閙aster.info和中級(jí)日志文件都不是崩潰安全的，所以建議開(kāi)啟一下選項(xiàng)：
sync_master_info     = 1
sync_relay_log       = 1
sync_relay_log_info  = 1

同樣可以過(guò)濾待同步的數(shù)據(jù)庫(kù)，或者表，參考復(fù)制過(guò)濾一節(jié)。

3 數(shù)據(jù)庫(kù)遠(yuǎn)程備份

數(shù)據(jù)庫(kù)遠(yuǎn)程備份可以選擇mysqldump（邏輯備份）進(jìn)行熱備，但對(duì)于數(shù)據(jù)量較大時(shí)會(huì)比較慢，Xtrabackup（物理備份）也可以對(duì)mysql數(shù)據(jù)庫(kù)進(jìn)行熱備（這里使用innobackupex-1.5.1），Xtrabackup可以實(shí)現(xiàn)innoDB等數(shù)據(jù)庫(kù)的在線備份，速度較快且不影響正常讀寫(xiě)。這里對(duì)全庫(kù)進(jìn)行備份。

3.1 創(chuàng)建備份賬戶(hù)

在主服務(wù)器創(chuàng)建用戶(hù)backup（使用最小權(quán)限），用于數(shù)據(jù)庫(kù)備份。

mysql> grant reload, lock tables, replication client on \*.\* to backup@'%' identified by 'backup123';
mysql> flush privileges;　//在不重啟mysql服務(wù)的情況下完成權(quán)限的更新

3.2 數(shù)據(jù)庫(kù)完全備份

完全備份和恢復(fù)準(zhǔn)備兩個(gè)步驟都是在主庫(kù)服務(wù)器完成。

innobackupex-1.5.1 --defaults-file=/etc/mysql/my.cnf --user=backup --password=backup123  /mysqlbackup
--defaults-file：選擇默認(rèn)的配置文件
--user和--password：分別為進(jìn)行備份的用戶(hù)名和密碼
/mysqlbackup：目標(biāo)目錄

3.3 恢復(fù)準(zhǔn)備

一般情況下，在備份完成后，數(shù)據(jù)尚且不能用于恢復(fù)操作，因?yàn)閭浞莸臄?shù)據(jù)中可能會(huì)包含尚未提交的事務(wù)或已經(jīng)提交但尚未同步至數(shù)據(jù)文件中的事務(wù)。因此，此時(shí)數(shù)據(jù)文件仍處理不一致?tīng)顟B(tài)?！皽?zhǔn)備”的主要作用正是通過(guò)回滾未提交的事務(wù)及同步已經(jīng)提交的事務(wù)至數(shù)據(jù)文件也使得數(shù)據(jù)文件處于一致性狀態(tài)。
innobakupex命令的--apply-log選項(xiàng)可用于實(shí)現(xiàn)上述功能。如下面的命令：

innobackupex-1.5.1 --apply-log --user=backup --password=backup123  /mysqlbackup/2017-01-11_21-20-57
如果執(zhí)行正確，其最后輸出的幾行信息通常如下：
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
120407  9:01:36  InnoDB: Starting shutdown...
120407  9:01:40  InnoDB: Shutdown completed; log sequence number 92036620
120407 09:01:40  innobackupex: completed OK!

在實(shí)現(xiàn)“準(zhǔn)備”的過(guò)程中，innobackupex通常還可以使用--use-memory選項(xiàng)來(lái)指定其可以使用的內(nèi)存的大小，默認(rèn)通常為100M。如果有足夠的內(nèi)存可用，可以多劃分一些內(nèi)存給prepare的過(guò)程，以提高其完成速度。

3.4 數(shù)據(jù)拷貝

將主服務(wù)器上準(zhǔn)備好的數(shù)據(jù)庫(kù)拷貝到從服務(wù)器。(當(dāng)然也可以打包后再拷貝)

scp -r /mysqlbackup/ copyer@192.168.1.192:/data/

3.5 數(shù)據(jù)恢復(fù)

在數(shù)據(jù)恢復(fù)之前首先關(guān)閉從服務(wù)器mysql服務(wù)，并從備份文件夾中的xtrabackup_binlog_info文件中獲取當(dāng)前正在使用的二進(jìn)制日志文件，以及備份這一刻為止二進(jìn)制日志事件的位置。如果datadir目錄不為空，還需要清空datadir目錄。
innobackupex命令的--copy-back選項(xiàng)用于執(zhí)行恢復(fù)操作，其通過(guò)復(fù)制所有數(shù)據(jù)相關(guān)的文件至mysql服務(wù)器datadir目錄中來(lái)執(zhí)行恢復(fù)過(guò)程。innobackupex通過(guò)backup-my.cnf來(lái)獲取datadir目錄的相關(guān)信息（也可以通過(guò)--defaults-file指定my.cnf目錄，還要確保datadir路徑為空）

innobackupex-1.5.1 --copy-back  /mysqlbackup
如果執(zhí)行正確，其輸出信息的最后幾行通常如下：
innobackupex: Starting to copy InnoDB log files
innobackupex: in '/backup/2012-04-07_08-17-03'
innobackupex: back to original InnoDB log directory '/mydata/data'
innobackupex: Finished copying back files.
120407 09:36:10  innobackupex: completed OK!

請(qǐng)確保如上信息的最后一行出現(xiàn)“innobackupex: completed OK!”。

當(dāng)數(shù)據(jù)恢復(fù)至datadir目錄以后，還需要確保所有數(shù)據(jù)文件的屬主和屬組均為正確的用戶(hù)，如mysql，否則，在啟動(dòng)mysqld之前還需要事先修改數(shù)據(jù)文件的屬主和屬組。如：

chown -R  mysql:mysql  /var/lib/mysql/

4 主從連接

4.1 開(kāi)啟從數(shù)據(jù)庫(kù)

service mysql start

如果開(kāi)啟mysql失敗，可以通過(guò)查看錯(cuò)誤日志尋找失敗原因。

4.2 建立主從連接

從庫(kù)通過(guò)復(fù)制賬戶(hù)連接到主庫(kù)：（slave必須處于stop狀態(tài)才能使以下連接生效）

mysql> change master to master_host='192.168.1.208'，master_user='repl'，
master_password='repl123456'，master_log_file='mysql-bin.000001'（備份時(shí)得到的活動(dòng)日志）,master_log_pos=0（備份時(shí)得到的活動(dòng)日志中事件的位置）;

注：如果這里在進(jìn)行主從連接的時(shí)候一直連不上master，有一個(gè)可能的原因是my.cnf配置文件中綁定了本機(jī)，即bind-address = 127.0.0.1，我們要做的就是將其注釋掉，否則外部機(jī)器是訪問(wèn)不了的。

開(kāi)啟slave：

mysql> start slave;

查看slave狀態(tài)，可以發(fā)現(xiàn)IO線程和SQL線程已處于開(kāi)啟狀態(tài)，有非常多表征從庫(kù)連接狀態(tài)的變量（這些變量同樣可以用于設(shè)置主從監(jiān)控），在這里不一一做介紹。

mysql> show slave status;

Slave_IO_Running: Yes  //表示IO線程運(yùn)行正常
Slave_SQL_Running: Yes  //表示SQL線程運(yùn)行正常
Seconds_Behind_Master: 0  //表示在網(wǎng)絡(luò)條件較好的情況下，從庫(kù)能夠及時(shí)同步上主庫(kù)

4.3 常用監(jiān)控命令

mysql> show processlist\G;　//查看數(shù)據(jù)庫(kù)服務(wù)線程情況
mysql> show master/slave status\G;　//查看主備庫(kù)狀態(tài)
mysql> flush logs;　//強(qiáng)制輪換(rotate)二進(jìn)制日志，從而得到一個(gè)完整的二進(jìn)制日志文件
mysql> show binlog events in '指定二進(jìn)制日志文件名稱(chēng)'  from （從指定位置開(kāi)始顯示） limit （需要顯示的事件數(shù)量）\G;　//查看binlog中事件
mysql> show binary logs;　//顯示所有的binlogs
mysql> reset master;　//刪除所有二進(jìn)制日志文件并清空索引文件
mysql> reset slave;　//刪除slave上復(fù)制用的所有文件重新開(kāi)始
mysql> show slave hosts;  //查看主庫(kù)所擁有的從庫(kù)信息

mysql的主從復(fù)制如何配置

5 從庫(kù)延遲較大

如果發(fā)現(xiàn)從庫(kù)延遲較大，就需要找到延遲大的原因。參數(shù) innodb_flush_log_at_trx_commit對(duì)mysql的寫(xiě)入效率影響較大，有三個(gè)取值：

0：每隔一秒，把事務(wù)日志緩存區(qū)的數(shù)據(jù)寫(xiě)到日志文件中，以及把日志文件的數(shù)據(jù)刷新到磁盤(pán)上；
1：每個(gè)事務(wù)提交時(shí)候，把事務(wù)日志從緩存區(qū)寫(xiě)到日志文件中，并且刷新日志文件的數(shù)據(jù)到磁盤(pán)上；
2：每事務(wù)提交的時(shí)候，把事務(wù)日志數(shù)據(jù)從緩存區(qū)寫(xiě)到日志文件中；每隔一秒，刷新一次日志文件，但不一定刷新到磁盤(pán)上，而是取決于操作系統(tǒng)的調(diào)度；

取1時(shí)的IO耗費(fèi)較大，雖然一致性和完整性方面效果好，但是寫(xiě)入效率最低，而這也是導(dǎo)致從庫(kù)延遲較大的原因（如果服務(wù)器配置較高或許會(huì)好些）。取0時(shí)mysql寫(xiě)入性能很好，但如果 mysqld 進(jìn)程崩潰，通常會(huì)導(dǎo)致最后 1s 的日志丟失。取2時(shí)的寫(xiě)入性能也很好，每次事務(wù)提交會(huì)寫(xiě)入日志文件，但并不會(huì)立即刷寫(xiě)到磁盤(pán)，日志文件會(huì)每秒刷寫(xiě)一次到磁盤(pán)。這時(shí)如果 mysqld 進(jìn)程崩潰，由于日志已經(jīng)寫(xiě)入到系統(tǒng)緩存，所以并不會(huì)丟失數(shù)據(jù)；在操作系統(tǒng)崩潰的情況下，通常會(huì)導(dǎo)致最后 1s 的日志丟失。

6 混合模式復(fù)制

正常情況下使用使用基于語(yǔ)句的復(fù)制，而對(duì)不安全的語(yǔ)句則切換到基于行的復(fù)制。主要有以下幾種情況：

該語(yǔ)句調(diào)用了：
- UUID函數(shù)
- 用戶(hù)自定義函數(shù)
- CURRENT_USER或USER函數(shù)
- LOAD_FILE函數(shù)
一個(gè)語(yǔ)句同時(shí)更新了兩個(gè)或者兩個(gè)以上含有AUTO_INCREMENT列的表
語(yǔ)句使用了服務(wù)器變量
存儲(chǔ)引擎不允許使用基于語(yǔ)句的復(fù)制，例如，mysql cluster引擎

7 復(fù)制過(guò)濾

有時(shí)候我們不需要對(duì)數(shù)據(jù)庫(kù)中所有的庫(kù)進(jìn)行復(fù)制，或者不想對(duì)指定庫(kù)中的某些表進(jìn)行復(fù)制操作，那么我們就需要對(duì)復(fù)制進(jìn)行一定的過(guò)濾配置，以達(dá)到更合理的復(fù)制效果。

1. 基于master

**binlog-do-db=mysql**：主庫(kù)只是將指定庫(kù)(mysql)發(fā)生的變化記錄到二進(jìn)制日志中。
**binlog-ignore-db=mysql**：主庫(kù)取消將指定庫(kù)(mysql)發(fā)生的變化記錄到二進(jìn)制日志中。

2. 基于slave

針對(duì)數(shù)據(jù)庫(kù)進(jìn)行的過(guò)濾：

**replicate-do-db=mysql**：從庫(kù)只是將指定庫(kù)(mysql)發(fā)生的變化進(jìn)行重現(xiàn)。
**replicate-ignore-db=mysql**：從庫(kù)取消將指定庫(kù)(mysql)發(fā)生的變化進(jìn)行重現(xiàn)。
針對(duì)表進(jìn)行的過(guò)濾：
**replicate-wild_do-table=mysql.learn**：從庫(kù)只是將指定庫(kù)(mysql)中指定表(learn)發(fā)生的變化進(jìn)行重現(xiàn)。
**replicate-wild_ignore-table=mysql.learn**：從庫(kù)取消將指定庫(kù)(mysql)中指定表(learn)發(fā)生的變化進(jìn)行重現(xiàn)。

以上復(fù)制過(guò)濾方式乍一看沒(méi)有問(wèn)題，其實(shí)還是有需要注意的地方。因?yàn)檫@些過(guò)濾方式的效果與復(fù)制方式有關(guān)系。如果是基于語(yǔ)句的復(fù)制，binlog-do-db、binlog-ignore-db、replicate-do-db、replicate-ignore-db與跨庫(kù)(如use庫(kù)內(nèi)和use外)有關(guān)系，這一點(diǎn)需要注意。

8 日志清理

暴力清理：（沒(méi)有主從復(fù)制的情況下）

1、重啟mysql服務(wù)器即可關(guān)閉bin日志的記錄
2、通過(guò)reset master命令進(jìn)行清理

條件清理

如果存在主從復(fù)制關(guān)系，則應(yīng)當(dāng)使用purge的方式來(lái)清理bin日志，語(yǔ)法如下：

purge {master|binary} logs to 'log_name'
purge {master|binary} logs before 'date'

用戶(hù)刪除列于在指定的日志或日期之前的日志索引中的所有二進(jìn)制日志，同時(shí)這些日志也會(huì)從日志索引文件的清單中刪除。

eg.
purge master logs to 'mysql-bin.000005';
purge master logs before '2014-08-30 00:00:00';//清除指定日期之前的日志
purge master logs before date_sub(now(),Interval 3 day);清除三天前的日志

定時(shí)清理

參數(shù)：expire_logs_days
說(shuō)明：二進(jìn)制日志自動(dòng)刪除/過(guò)期的天數(shù)。默認(rèn)值為'0'，即沒(méi)有過(guò)期的
示例：expire_logs_days = 5，代表日志的有效時(shí)間為5天
什么時(shí)候會(huì)刪除過(guò)期日志？

每次進(jìn)行l(wèi)og flush的時(shí)候會(huì)自動(dòng)刪除過(guò)期的日志

什么時(shí)候會(huì)觸發(fā)log flush？

1、重啟
2、binlog文件的大小達(dá)到了較大限制
3、手動(dòng)執(zhí)行flush logs命令

感謝各位的閱讀！關(guān)于mysql的主從復(fù)制如何配置就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，讓大家可以學(xué)到更多知識(shí)。如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到吧！

文章名稱(chēng)：mysql的主從復(fù)制如何配置-創(chuàng)新互聯(lián)
標(biāo)題鏈接：http://bm7419.com/article6/dsshig.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站導(dǎo)航、軟件開(kāi)發(fā)、網(wǎng)頁(yè)設(shè)計(jì)公司、網(wǎng)站維護(hù)、建站公司、網(wǎng)站排名

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容