快捷導(dǎo)航

Mysql主從同步備份策略分享

更新時(shí)間：2011年05月25日 17:52:16 作者：

主從服務(wù)器上的MySQL數(shù)據(jù)庫版本同為5.1.34

環(huán)境：
主從服務(wù)器上的MySQL數(shù)據(jù)庫版本同為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ù)庫名，如果備份多個(gè)數(shù)據(jù)庫，重復(fù)設(shè)置這個(gè)選項(xiàng)即可
binlog-ignore-db=mysql  #不需要備份的數(shù)據(jù)庫名，如果備份多個(gè)數(shù)據(jù)庫，重復(fù)設(shè)置這個(gè)選項(xiàng)即可
log-slave-updates #這個(gè)參數(shù)一定要加上，否則不會(huì)給更新的記錄些到二進(jìn)制文件里
slave-skip-errors #是跳過錯(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上做連接測試: mysql -h 192.168.0.1 -u test -p
3.鎖主庫表
mysql> FLUSH TABLES WITH READ LOCK;
4.顯示主庫信息
記錄File和Position，從庫設(shè)置將會(huì)用到
=====================
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_do_db | Binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106      |              |                  |
+------------------+----------+--------------+------------------+
5.另開一個(gè)終端，打包主庫
cd /usr/local/mysql  #mysql庫目錄
tar zcvf var.tar.gz var
============================
二.MySQL從服務(wù)器配置
1、傳輸拿到主庫數(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ù)庫名
replicate-ignore-db=mysql #忽略的數(shù)據(jù)庫
master-connect-retry=60 #如果從服務(wù)器發(fā)現(xiàn)主服務(wù)器斷掉，重新連接的時(shí)間差(秒)
log-slave-updates #這個(gè)參數(shù)一定要加上，否則不會(huì)給更新的記錄些到二進(jìn)制文件里
slave-skip-errors #是跳過錯(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為主庫的File，MASTER_LOG_POS為主庫的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、解鎖主庫表
mysql> UNLOCK TABLES;
到此主從庫搭建成功。可以在主庫上插入數(shù)據(jù)測試同步是否正常。
--------------------------
附：一些錯(cuò)誤信息的處理，主從服務(wù)器上的命令，及狀態(tài)信息。
在從服務(wù)器上使用show slave status\G
Slave_IO_Running,為No,
則說明IO_THREAD沒有啟動(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ù)制沒有啟動(dòng)
Connecting to master//沒有連接上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段的日志寫到本地
SLAVE start IO_THREAD
SLAVE STOP SQL_THREAD //此線程把寫到本地的日志應(yīng)用于數(shù)據(jù)庫
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端已同步過的日志
6.3.1 Master 同步線程狀態(tài)
以下列出了master的 Binlog Dump 線程 State 字段中最常見的幾種狀態(tài)。如果在master上沒有 Binlog Dump 線程，那么同步就沒有在運(yùn)行。
也就是說，沒有slave連接上來。
Sending binlog event to slave
事件是由二進(jìn)制日志構(gòu)成，一個(gè)事件通常由更新語句加上其他信息。線程讀取到一個(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 字段中最常見的幾種狀態(tài)。從MySQL 4.1.1開始，這個(gè)狀態(tài)在執(zhí)行 SHOW SLAVE STATUS 語句結(jié)果的
Slave_IO_State 字段也會(huì)出現(xiàn)。這意味著可以只執(zhí)行 SHOW SLAVE STATUS 語句就能了解到更多的信息。
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)制文件以及開始位置。
Waiting to reconnect after a failed binlog dump request
如果二進(jìn)制日志轉(zhuǎn)儲(chǔ)(binary log dump)請(qǐng)求失敗了(由于連接斷開)，該線程在休眠時(shí)進(jìn)入這個(gè)狀態(tài)，并定期重連。重連的時(shí)間間隔由 --
master-connect-retry 選項(xiàng)來指定。
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ù)較長時(shí)間，如果它等待超過 slave_read_timeout 秒
，就會(huì)發(fā)生超時(shí)。這時(shí)，它就會(huì)考慮斷開連接，然后嘗試重連。
Queueing master event to the relay log
已經(jīng)讀取到一個(gè)事件，正把它拷貝到中繼日志中以備SQL線程處理。
Waiting to reconnect after a failed master event read
讀日志時(shí)發(fā)生錯(cuò)誤(由于連接斷開)。該線程在重連之前休眠 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 的值非零，中繼日志的大小總和超過這個(gè)值了。I/O線程等待SQL線程先處理中繼日志然后刪除它們以釋放足夠的空間
。
Waiting for slave mutex on exit
當(dāng)前線程停止了，這個(gè)時(shí)間很短。
6.3.3 Slave的SQL線程狀態(tài)
以下列出了slave的SQL線程 State 字段中最常見的幾種狀態(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線程寫入更新的日志。
Waiting for slave mutex on exit
當(dāng)前線程停止了，這個(gè)時(shí)間很短。
--------------
常見的一些問題：
一.從庫SLAVE啟動(dòng)問題
由于一些錯(cuò)誤操作導(dǎo)致 CHANGE MASTER 和 SLAVE 服務(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.
*****************************************************************
無法初始化master info結(jié)構(gòu)，MySQL錯(cuò)誤日志記錄了更詳細(xì)的錯(cuò)誤信息。
解決方法：
1、查看MySQL錯(cuò)誤日志，如：同步的上一個(gè)Position是多少，很多情況下無法啟動(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       //跳過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,則說明配置成功.
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,則說明配置成功.
stop slave    #停止同步
start slave    #開始同步，從日志終止的位置開始更新。
SET SQL_LOG_BIN=0|1  #主機(jī)端運(yùn)行，需要super權(quán)限，用來開停日志，隨意開停，會(huì)造成主機(jī)從機(jī)數(shù)據(jù)不一致，造成錯(cuò)誤
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n  # 客戶端運(yùn)行，用來跳過幾個(gè)事件，只有當(dāng)同步進(jìn)程出現(xiàn)錯(cuò)誤而停止的時(shí)候才可以執(zhí)行。
RESET MASTER  #主機(jī)端運(yùn)行,清除所有的日志，這條命令就是原來的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)的庫有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)的庫有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; #查看主庫二進(jìn)制日志文件內(nèi)容：
注意：
1.主輔庫同步主要是通過二進(jìn)制日志來實(shí)現(xiàn)同步的。
2.在啟動(dòng)輔庫的時(shí)候必須先把數(shù)據(jù)同步，并刪除日志目錄下的：master.info文件。因?yàn)?/FONT>master.info記錄了上次要連接主庫的信息，如果不刪除，即使my.cnf里進(jìn)行了修改，也不起作用。因?yàn)樽x取的還是master.info文件里的信息。

您可能感興趣的文章: