Mysql主從同步備份策略分享

環境：
主從服務器上的MySQL數據庫版本同為5.1.34
主機IP：192.168.0.1
從機IP：192.168.0.2
一. MySQL主服務器配置
1.編輯配置文件/etc/my.cnf
# 確保有如下行
server-id = 1
log-bin=mysql-bin
binlog-do-db=mysql  #需要備份的數據庫名，如果備份多個數據庫，重復設置這個選項即可
binlog-ignore-db=mysql  #不需要備份的數據庫名，如果備份多個數據庫，重復設置這個選項即可
log-slave-updates #這個參數一定要加上，否則不會給更新的記錄些到二進制文件裡
slave-skip-errors #是跳過錯誤，繼續執行復制操作
2.建立用戶
mysql> grant replication slave on *.* to [email protected] identified by ‘111111′;
# grant replication slave on *.* to ‘用戶名'@'主機' identified by ‘密碼';
# 可在Slave上做連接測試: mysql -h 192.168.0.1 -u test -p
3.鎖主庫表
mysql> FLUSH TABLES WITH READ LOCK;
4.顯示主庫信息
記錄File和Position，從庫設置將會用到
=====================
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_do_db | Binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106      |              |                  |
+------------------+----------+--------------+------------------+
5.另開一個終端，打包主庫
cd /usr/local/mysql  #mysql庫目錄
tar zcvf var.tar.gz var
============================
二.MySQL從服務器配置
1、傳輸拿到主庫數據包、解包
# cd /usr/local/mysql
# scp 192.168.0.1:/usr/local/mysql/var.tar.gz .
# tar zxvf var.tar.gz
2、查看修改var文件夾權限
# 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  #需要備份的數據庫名
replicate-ignore-db=mysql #忽略的數據庫
master-connect-retry=60 #如果從服務器發現主服務器斷掉，重新連接的時間差(秒)
log-slave-updates #這個參數一定要加上，否則不會給更新的記錄些到二進制文件裡
slave-skip-errors #是跳過錯誤，繼續執行復制操作
4、驗證連接MASTER
# mysql -h192.168.0.1 -uslave -ppassword
mysql> show grants for [email protected];
5、在SLAVE上設置同步
設置連接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、啟動SLAVE服務
mysql> slave start;
7、查看SLAVE狀態
mysql> SHOW SLAVE STATUS\G;
其中 Slave_IO_Running 和 Slave_SQL_Running 兩列的值都為 "Yes"，表明 Slave 的 I/O 和 SQL 線程都在正常運行。
8、解鎖主庫表
mysql> UNLOCK TABLES;
到此主從庫搭建成功。可以在主庫上插入數據測試同步是否正常。
--------------------------
附：一些錯誤信息的處理，主從服務器上的命令，及狀態信息。
在從服務器上使用show slave status\G
Slave_IO_Running,為No,
則說明IO_THREAD沒有啟動，請執行start slave io_thread
Slave_SQL_Running為No
則復制出錯,查看Last_error字段排除錯誤後執行start slave sql_thread
查看Slave_IO_State字段空 //復制沒有啟動
Connecting to master//沒有連接上master
Waiting for master to send event//已經連上
主服務器上的相關命令：
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=
從服務器上的相關命令:
slave start
slave stop
SLAVE STOP IO_THREAD //此線程把master段的日志寫到本地
SLAVE start IO_THREAD
SLAVE STOP SQL_THREAD //此線程把寫到本地的日志應用於數據庫
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= //動態改變master信息
PURGE MASTER [before 'date'] 刪除master端已同步過的日志
6.3.1 Master 同步線程狀態
以下列出了master的 Binlog Dump 線程 State 字段中最常見的幾種狀態。如果在master上沒有 Binlog Dump 線程，那麼同步就沒有在運行。
也就是說，沒有slave連接上來。
Sending binlog event to slave
事件是由二進制日志構成，一個事件通常由更新語句加上其他信息。線程讀取到一個事件並正發送到slave上。
Finished reading one binlog; switching to next binlog
讀取完了一個二進制日志，正切換到下一個。
Has sent all binlog to slave; waiting for binlog to be updated
已經讀取完全部未完成更新日志，並且全部都發送到slave了。它處於空閒狀態，正等待在master上執行新的更新操作以在二進制日志中產生新
的事件，然後讀取它們。
Waiting to finalize termination
當前線程停止了，這個時間很短。
6.3.2 Slave的I/O線程狀態
以下列出了slave的I/O線程 State 字段中最常見的幾種狀態。從MySQL 4.1.1開始，這個狀態在執行 SHOW SLAVE STATUS 語句結果的
Slave_IO_State 字段也會出現。這意味著可以只執行 SHOW SLAVE STATUS 語句就能了解到更多的信息。
Connecting to master
該線程證嘗試連接到master上。
Checking master version
確定連接到master後出現的一個短暫的狀態。
Registering slave on master
確定連接到master後出現的一個短暫的狀態。
Requesting binlog dump
確定連接到master後出現的一個短暫的狀態。該線程向master發送一個請求，告訴它要請求的二進制文件以及開始位置。
Waiting to reconnect after a failed binlog dump request
如果二進制日志轉儲(binary log dump)請求失敗了(由於連接斷開)，該線程在休眠時進入這個狀態，並定期重連。重連的時間間隔由 --
master-connect-retry 選項來指定。
Reconnecting after a failed binlog dump request
該線程正嘗試重連到master。
Waiting for master to send event
已經連接到master，正等待它發送二進制日志。如果master閒置時，這個狀態可能會持續較長時間，如果它等待超過 slave_read_timeout 秒
，就會發生超時。這時，它就會考慮斷開連接，然後嘗試重連。
Queueing master event to the relay log
已經讀取到一個事件，正把它拷貝到中繼日志中以備SQL線程處理。
Waiting to reconnect after a failed master event read
讀日志時發生錯誤(由於連接斷開)。該線程在重連之前休眠 master-connect-retry 秒。
Reconnecting after a failed master event read
正嘗試重連到master。當連接確定後，狀態就變成 Waiting for master to send event。
Waiting for the slave SQL thread to free enough relay log space
relay_log_space_limit 的值非零，中繼日志的大小總和超過這個值了。I/O線程等待SQL線程先處理中繼日志然後刪除它們以釋放足夠的空間
。
Waiting for slave mutex on exit
當前線程停止了，這個時間很短。
6.3.3 Slave的SQL線程狀態
以下列出了slave的SQL線程 State 字段中最常見的幾種狀態：
Reading event from the relay log
從中繼日志裡讀到一個事件以備執行。
Has read all relay log; waiting for the slave I/O thread to update it
已經處理完中繼日志中的全部事件了，正等待I/O線程寫入更新的日志。
Waiting for slave mutex on exit
當前線程停止了，這個時間很短。
--------------
常見的一些問題：
一.從庫SLAVE啟動問題
由於一些錯誤操作導致 CHANGE MASTER 和 SLAVE 服務無法啟動，系統報錯如下：
*****************************************************************
Could not initialize master info structure; more error messages can be found in the MySQL error log.
*****************************************************************
無法初始化master info結構，MySQL錯誤日志記錄了更詳細的錯誤信息。
解決方法：
1、查看MySQL錯誤日志，如：同步的上一個Position是多少，很多情況下無法啟動服務是由於mysql識別的同步始終停留在上一個Position上。
2、查看master.info和relay-log.info，master.info 記錄MASTER相關信息，relay-log.info 記錄當前同步日志信息。
3、停止myslq服務，刪除master.info和relay-log.info。
4、啟動mysql服務。
5、重新CHANGE MASTER，重新啟動SLAVE服務。
二.主從不能同步
show slave status;報錯: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會和Master去同步 主要看:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master是否為0，0就是已經同步了
2,還需要做的一些優化與監視:
show full processlist; //查看mysql當前同步線程號
skip-name-resolve       //跳過dns名稱查詢，有助於加快連接及同步的速度
max_connections=1000    //增大Mysql的連接數目，(默認100)
max_connect_errors=100 //增大Mysql的錯誤連接數目,(默認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
如果出現Command: Binlog Dump,則說明配置成功.
stop slave    #停止同步
start slave    #開始同步，從日志終止的位置開始更新。
SET SQL_LOG_BIN=0|1  #主機端運行，需要super權限，用來開停日志，隨意開停，會造成主機從機數據不一致，造成錯誤
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n  # 客戶端運行，用來跳過幾個事件，只有當同步進程出現錯誤而停止的時候才可以執行。
RESET MASTER  #主機端運行,清除所有的日志，這條命令就是原來的FLUSH MASTER
RESET SLAVE   #從機運行，清除日志同步位置標志，並重新生成master.info
雖然重新生成了master.info,但是並不起用，最好，將從機的mysql進程重啟一下，
LOAD TABLE tblname FROM MASTER #從機運行，從主機端重讀指定的表的數據，每次只能讀取一個，受timeout時間限制，需要調整timeout時間。執行這個命令需要同步賬號有 reload和super權限。以及對相應的庫有select權限。如果表比較大，要增加net_read_timeout 和 net_write_timeout的值
LOAD DATA FROM MASTER  #從機執行，從主機端重新讀入所有的數據。執行這個命令需要同步賬號有reload和super權限。以及對相應的庫有select權限。如果表比較大，要增加net_read_timeout 和 net_write_timeout的值
CHANGE MASTER TO master_def_list  #在線改變一些主機設置，多個用逗號間隔,比如
CHANGE MASTER TO
MASTER_HOST='master2.mycompany.com',
MASTER_USER='replication',
MASTER_PASSWORD='bigs3cret'
MASTER_POS_WAIT() #從機運行
SHOW MASTER STATUS #主機運行，看日志導出信息
SHOW SLAVE HOSTS #主機運行，看連入的從機的情況。
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; #查看主庫二進制日志文件內容：
注意：
1.主輔庫同步主要是通過二進制日志來實現同步的。
2.在啟動輔庫的時候必須先把數據同步，並刪除日志目錄下的：master.info文件。因為master.info記錄了上次要連接主庫的信息，如果不刪除，即使my.cnf裡進行了修改，也不起作用。因為讀取的還是master.info文件裡的信息。