Mysql主從同步備份策略

pkman110

Mysql主從同步備份策略五月 18th, 2009 at 17:30 - 3,803 views
mysql主從同步備份策略
環(huán)境：
主從服務(wù)器上的MySQL數(shù)據(jù)庫(kù)版本同為5.1.34
主機(jī)IP：192.168.0.1
從機(jī)IP：192.168.0.2
一. MySQL主服務(wù)器配置
1.編輯配置文件/etc/my.cnf
# 確保有如下行
server-id = 1
log-bin=mysql-bin
binlog-do-db=mysql  #需要備份的數(shù)據(jù)庫(kù)名，如果備份多個(gè)數(shù)據(jù)庫(kù)，重復(fù)設(shè)置這個(gè)選項(xiàng)即可
binlog-ignore-db=mysql  #不需要備份的數(shù)據(jù)庫(kù)名，如果備份多個(gè)數(shù)據(jù)庫(kù)，重復(fù)設(shè)置這個(gè)選項(xiàng)即可
log-slave-updates #這個(gè)參數(shù)一定要加上，否則不會(huì)給更新的記錄些到二進(jìn)制文件里
slave-skip-errors #是跳過(guò)錯(cuò)誤，繼續(xù)執(zhí)行復(fù)制操作
2.建立用戶
mysql> grant replication slave on *.* to slave@192.168.0.2 identified by ‘111111′;
# grant replication slave on *.* to ‘用戶名’@'主機(jī)’ identified by ‘密碼’;
# 可在Slave上做連接測(cè)試: mysql -h 192.168.0.1 -u test -p
3.鎖主庫(kù)表
mysql> FLUSH TABLES WITH READ LOCK;
4.顯示主庫(kù)信息
記錄File和Position，從庫(kù)設(shè)置將會(huì)用到
=====================
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_do_db | Binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106      |              |                  |
+------------------+----------+--------------+------------------+
5.另開(kāi)一個(gè)終端，打包主庫(kù)
cd /usr/local/mysql  #mysql庫(kù)目錄
tar zcvf var.tar.gz var
============================
二.MySQL從服務(wù)器配置
1、傳輸拿到主庫(kù)數(shù)據(jù)包、解包
# cd /usr/local/mysql
# scp 192.168.0.1:/usr/local/mysql/var.tar.gz .
# tar zxvf var.tar.gz
2、查看修改var文件夾權(quán)限
# chown -R mysql:mysql var
3.編輯 /etc/my.cnf
server-id=2
log-bin=mysql-bin
master-host=192.168.0.1
master-user=slave
master-password=111111
master-port=3306
replicate-do-db=test  #需要備份的數(shù)據(jù)庫(kù)名
replicate-ignore-db=mysql #忽略的數(shù)據(jù)庫(kù)
master-connect-retry=60 #如果從服務(wù)器發(fā)現(xiàn)主服務(wù)器斷掉，重新連接的時(shí)間差(秒)
log-slave-updates #這個(gè)參數(shù)一定要加上，否則不會(huì)給更新的記錄些到二進(jìn)制文件里
slave-skip-errors #是跳過(guò)錯(cuò)誤，繼續(xù)執(zhí)行復(fù)制操作
4、驗(yàn)證連接MASTER
# mysql -h192.168.0.1 -uslave -ppassword
mysql> show grants for slave@192.168.0.2;
5、在SLAVE上設(shè)置同步
設(shè)置連接MASTER MASTER_LOG_FILE為主庫(kù)的File，MASTER_LOG_POS為主庫(kù)的Position
============================
mysql> slave stop;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.1',MASTER_USER='slave',MASTER_PASSWORD='111111',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=106;
6、啟動(dòng)SLAVE服務(wù)
mysql> slave start;
7、查看SLAVE狀態(tài)
mysql> SHOW SLAVE STATUS\G;
其中 Slave_IO_Running 和 Slave_SQL_Running 兩列的值都為 "Yes"，表明 Slave 的 I/O 和 SQL 線程都在正常運(yùn)行。
8、解鎖主庫(kù)表
mysql> UNLOCK TABLES;
到此主從庫(kù)搭建成功�？梢栽谥鲙�(kù)上插入數(shù)據(jù)測(cè)試同步是否正常。
--------------------------
附：一些錯(cuò)誤信息的處理，主從服務(wù)器上的命令，及狀態(tài)信息。
在從服務(wù)器上使用show slave status\G
Slave_IO_Running,為No,
則說(shuō)明IO_THREAD沒(méi)有啟動(dòng)，請(qǐng)執(zhí)行start slave io_thread
Slave_SQL_Running為No
則復(fù)制出錯(cuò),查看Last_error字段排除錯(cuò)誤后執(zhí)行start slave sql_thread
查看Slave_IO_State字段空 //復(fù)制沒(méi)有啟動(dòng)
Connecting to master//沒(méi)有連接上master
Waiting for master to send event//已經(jīng)連上
主服務(wù)器上的相關(guān)命令：
show master status
show slave hosts
show logs
show binlog events
purge logs to 'log_name'
purge logs before 'date'
reset master(老版本flush master)
set sql_log_bin=
從服務(wù)器上的相關(guān)命令:
slave start
slave stop
SLAVE STOP IO_THREAD //此線程把master段的日志寫(xiě)到本地
SLAVE start IO_THREAD
SLAVE STOP SQL_THREAD //此線程把寫(xiě)到本地的日志應(yīng)用于數(shù)據(jù)庫(kù)
SLAVE start SQL_THREAD
reset slave
SET GLOBAL SQL_SLAVE_SKIP_COUNTER
load data from master
show slave status(SUPER,REPLICATION CLIENT)
CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= //動(dòng)態(tài)改變master信息
PURGE MASTER [before 'date'] 刪除master端已同步過(guò)的日志
6.3.1 Master 同步線程狀態(tài)
以下列出了master的 Binlog Dump 線程 State 字段中最常見(jiàn)的幾種狀態(tài)。如果在master上沒(méi)有 Binlog Dump 線程，那么同步就沒(méi)有在運(yùn)行。
也就是說(shuō)，沒(méi)有slave連接上來(lái)。
Sending binlog event to slave
事件是由二進(jìn)制日志構(gòu)成，一個(gè)事件通常由更新語(yǔ)句加上其他信息。線程讀取到一個(gè)事件并正發(fā)送到slave上。
Finished reading one binlog; switching to next binlog
讀取完了一個(gè)二進(jìn)制日志，正切換到下一個(gè)。
Has sent all binlog to slave; waiting for binlog to be updated
已經(jīng)讀取完全部未完成更新日志，并且全部都發(fā)送到slave了。它處于空閑狀態(tài)，正等待在master上執(zhí)行新的更新操作以在二進(jìn)制日志中產(chǎn)生新
的事件，然后讀取它們。
Waiting to finalize termination
當(dāng)前線程停止了，這個(gè)時(shí)間很短。
6.3.2 Slave的I/O線程狀態(tài)
以下列出了slave的I/O線程 State 字段中最常見(jiàn)的幾種狀態(tài)。從MySQL 4.1.1開(kāi)始，這個(gè)狀態(tài)在執(zhí)行 SHOW SLAVE STATUS 語(yǔ)句結(jié)果的
Slave_IO_State 字段也會(huì)出現(xiàn)。這意味著可以只執(zhí)行 SHOW SLAVE STATUS 語(yǔ)句就能了解到更多的信息。
Connecting to master
該線程證嘗試連接到master上。
Checking master version
確定連接到master后出現(xiàn)的一個(gè)短暫的狀態(tài)。
Registering slave on master
確定連接到master后出現(xiàn)的一個(gè)短暫的狀態(tài)。
Requesting binlog dump
確定連接到master后出現(xiàn)的一個(gè)短暫的狀態(tài)。該線程向master發(fā)送一個(gè)請(qǐng)求，告訴它要請(qǐng)求的二進(jìn)制文件以及開(kāi)始位置。
Waiting to reconnect after a failed binlog dump request
如果二進(jìn)制日志轉(zhuǎn)儲(chǔ)(binary log dump)請(qǐng)求失敗了(由于連接斷開(kāi))，該線程在休眠時(shí)進(jìn)入這個(gè)狀態(tài)，并定期重連。重連的時(shí)間間隔由 --
master-connect-retry 選項(xiàng)來(lái)指定。
Reconnecting after a failed binlog dump request
該線程正嘗試重連到master。
Waiting for master to send event
已經(jīng)連接到master，正等待它發(fā)送二進(jìn)制日志。如果master閑置時(shí)，這個(gè)狀態(tài)可能會(huì)持續(xù)較長(zhǎng)時(shí)間，如果它等待超過(guò) slave_read_timeout 秒
，就會(huì)發(fā)生超時(shí)。這時(shí)，它就會(huì)考慮斷開(kāi)連接，然后嘗試重連。
Queueing master event to the relay log
已經(jīng)讀取到一個(gè)事件，正把它拷貝到中繼日志中以備SQL線程處理。
Waiting to reconnect after a failed master event read
讀日志時(shí)發(fā)生錯(cuò)誤(由于連接斷開(kāi))。該線程在重連之前休眠 master-connect-retry 秒。
Reconnecting after a failed master event read
正嘗試重連到master。當(dāng)連接確定后，狀態(tài)就變成 Waiting for master to send event。
Waiting for the slave SQL thread to free enough relay log space
relay_log_space_limit 的值非零，中繼日志的大小總和超過(guò)這個(gè)值了。I/O線程等待SQL線程先處理中繼日志然后刪除它們以釋放足夠的空間
。
Waiting for slave mutex on exit
當(dāng)前線程停止了，這個(gè)時(shí)間很短。
6.3.3 Slave的SQL線程狀態(tài)
以下列出了slave的SQL線程 State 字段中最常見(jiàn)的幾種狀態(tài)：
Reading event from the relay log
從中繼日志里讀到一個(gè)事件以備執(zhí)行。
Has read all relay log; waiting for the slave I/O thread to update it
已經(jīng)處理完中繼日志中的全部事件了，正等待I/O線程寫(xiě)入更新的日志。
Waiting for slave mutex on exit
當(dāng)前線程停止了，這個(gè)時(shí)間很短。
--------------
常見(jiàn)的一些問(wèn)題：
一.從庫(kù)SLAVE啟動(dòng)問(wèn)題
由于一些錯(cuò)誤操作導(dǎo)致 CHANGE MASTER 和 SLAVE 服務(wù)無(wú)法啟動(dòng)，系統(tǒng)報(bào)錯(cuò)如下：
*****************************************************************
Could not initialize master info structure; more error messages can be found in the MySQL error log.
*****************************************************************
無(wú)法初始化master info結(jié)構(gòu)，MySQL錯(cuò)誤日志記錄了更詳細(xì)的錯(cuò)誤信息。
解決方法：
1、查看MySQL錯(cuò)誤日志，如：同步的上一個(gè)Position是多少，很多情況下無(wú)法啟動(dòng)服務(wù)是由于mysql識(shí)別的同步始終停留在上一個(gè)Position上。
2、查看master.info和relay-log.info，master.info 記錄MASTER相關(guān)信息，relay-log.info 記錄當(dāng)前同步日志信息。
3、停止myslq服務(wù)，刪除master.info和relay-log.info。
4、啟動(dòng)mysql服務(wù)。
5、重新CHANGE MASTER，重新啟動(dòng)SLAVE服務(wù)。
二.主從不能同步
show slave status;報(bào)錯(cuò):Error xxx dosn't exist
且show slave status\G:
Slave_SQL_Running: NO
Seconds_Behind_Master: NULL
解決方法:
stop slave;
set global sql_slave_skip_counter =1 ;
start slave;
之后Slave會(huì)和Master去同步 主要看:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master是否為0，0就是已經(jīng)同步了
2,還需要做的一些優(yōu)化與監(jiān)視:
show full processlist; //查看mysql當(dāng)前同步線程號(hào)
skip-name-resolve       //跳過(guò)dns名稱查詢，有助于加快連接及同步的速度
max_connections=1000    //增大Mysql的連接數(shù)目，(默認(rèn)100)
max_connect_errors=100 //增大Mysql的錯(cuò)誤連接數(shù)目,(默認(rèn)10)
查看日志一些命令
1,  show master status\G;
在這里主要是看log-bin的文件是否相同。
show slave status\G;
在這里主要是看:
Slave_IO_Running=Yes
Slave_SQL_Running=Yes
如果都是Yes,則說(shuō)明配置成功.
2,在master上輸入show processlist\G;
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 2
User: root
Host: localhost:32931
db: NULL
Command: Binlog Dump
Time: 94
State: Has sent all binlog to slave; waiting for binlog to
be updated
Info: NULL
如果出現(xiàn)Command: Binlog Dump,則說(shuō)明配置成功.
stop slave    #停止同步
start slave    #開(kāi)始同步，從日志終止的位置開(kāi)始更新。
SET SQL_LOG_BIN=0|1  #主機(jī)端運(yùn)行，需要super權(quán)限，用來(lái)開(kāi)停日志，隨意開(kāi)停，會(huì)造成主機(jī)從機(jī)數(shù)據(jù)不一致，造成錯(cuò)誤
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n  # 客戶端運(yùn)行，用來(lái)跳過(guò)幾個(gè)事件，只有當(dāng)同步進(jìn)程出現(xiàn)錯(cuò)誤而停止的時(shí)候才可以執(zhí)行。
RESET MASTER  #主機(jī)端運(yùn)行,清除所有的日志，這條命令就是原來(lái)的FLUSH MASTER
RESET SLAVE   #從機(jī)運(yùn)行，清除日志同步位置標(biāo)志，并重新生成master.info
雖然重新生成了master.info,但是并不起用，最好，將從機(jī)的mysql進(jìn)程重啟一下，
LOAD TABLE tblname FROM MASTER #從機(jī)運(yùn)行，從主機(jī)端重讀指定的表的數(shù)據(jù)，每次只能讀取一個(gè)，受timeout時(shí)間限制，需要調(diào)整timeout時(shí)間。執(zhí)行這個(gè)命令需要同步賬號(hào)有 reload和super權(quán)限。以及對(duì)相應(yīng)的庫(kù)有select權(quán)限。如果表比較大，要增加net_read_timeout 和 net_write_timeout的值
LOAD DATA FROM MASTER  #從機(jī)執(zhí)行，從主機(jī)端重新讀入所有的數(shù)據(jù)。執(zhí)行這個(gè)命令需要同步賬號(hào)有reload和super權(quán)限。以及對(duì)相應(yīng)的庫(kù)有select權(quán)限。如果表比較大，要增加net_read_timeout 和 net_write_timeout的值
CHANGE MASTER TO master_def_list  #在線改變一些主機(jī)設(shè)置，多個(gè)用逗號(hào)間隔,比如
CHANGE MASTER TO
MASTER_HOST='master2.mycompany.com',
MASTER_USER='replication',
MASTER_PASSWORD='bigs3cret'
MASTER_POS_WAIT() #從機(jī)運(yùn)行
SHOW MASTER STATUS #主機(jī)運(yùn)行，看日志導(dǎo)出信息
SHOW SLAVE HOSTS #主機(jī)運(yùn)行，看連入的從機(jī)的情況。
SHOW SLAVE STATUS (slave)
SHOW MASTER LOGS (master)
SHOW BINLOG EVENTS [ IN 'logname' ] [ FROM pos ] [ LIMIT [offset,] rows ]
PURGE [MASTER] LOGS TO 'logname' ; PURGE [MASTER] LOGS BEFORE 'date'
show binlog events; #查看主庫(kù)二進(jìn)制日志文件內(nèi)容：
注意：
1.主輔庫(kù)同步主要是通過(guò)二進(jìn)制日志來(lái)實(shí)現(xiàn)同步的。
2.在啟動(dòng)輔庫(kù)的時(shí)候必須先把數(shù)據(jù)同步，并刪除日志目錄下的：master.info文件。因?yàn)閙aster.info記錄了上次要連接主庫(kù)的信息，如果不刪除，即使my.cnf里進(jìn)行了修改，也不起作用。因?yàn)樽x取的還是master.info文件里的信息。


本文來(lái)自ChinaUnix博客，如果查看原文請(qǐng)點(diǎn)：http://blog.chinaunix.net/u3/111930/showart_2186212.html

shushenglin

很好的文檔，呵呵

Lucky_f_xh

學(xué)習(xí)了