Mysql主從安裝配置方法

本篇內容介紹了“Mysql主從安裝配置方法”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

 
環境：
主從服務器上的MySQL版本同為5.1.34
主機IP：192.168.0.1
從機IP：192.168.0.2
 
一. MySQL主服務器配置
1.編輯配置文件/etc/my.cnf
# 確保有如下行  www.2cto.com  
server-id = 1
log-bin=-bin
binlog-do-db=mysql  #需要備份的數據庫名，如果備份多個數據庫，重復設置這個選項即可
binlog-ignore-db=mysql  #不需要備份的數據庫名，如果備份多個數據庫，重復設置這個選項即可
log-slave-updates #這個參數一定要加上，否則不會給更新的記錄些到二進制文件里
slave-skip-errors #是跳過錯誤，繼續執行復制操作  
 
2.建立用戶
mysql> grant replication slave on *.* to slave@192.168.0.2 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  www.2cto.com  
============================
 
二.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 -h292.168.0.1 -uslave -ppassword
mysql> show grants for slave@192.168.0.2;
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;  www.2cto.com  
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;
到此主從庫搭建成功?？梢栽谥鲙焐喜迦霐祿y試同步是否正常。
 
常見錯誤及解決方法：
常見問題的處理：
  www.2cto.com  
1：在從庫上面show slave status\G;出現下列情況，
          Slave_IO_Running: Yes
          Slave_SQL_Running: No
          Seconds_Behind_Master: NULL
 
原因：
a.程序可能在slave上進行了寫操作
b.也可能是slave機器重起后，事務回滾造成的.
 
解決方法：
 
進入master
 
mysql> show master status;
+----------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| mysql-bin.000040 | 324 | | |
+----------------------+----------+--------------+------------------+
然后到slave服務器上執行手動同步
 
slave stop;
change master to 
master_host='10.14.0.140',
master_user='repl',
master_password='111111',
master_port=3306,
master_log_file='mysql-bin.000040',
master_log_pos=324;
slave start;
show slave status\G;
 
2、現象：從數據庫無法同步，show slave status顯示Slave_IO_Running為No,Seconds_Behind_Master
為null
 
       解決：重啟主數據庫
 
             service mysql restart
 
             mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 98 | | |
+------------------+----------+--------------+------------------+
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;
 
這個現象主要是master數據庫存在問題，我在實際的操作中先重啟master后重啟slave即可解決這問題，
出現此問題，必須要要重啟master數據庫。
 
1.主輔庫同步主要是通過二進制日志來實現同步的。
2.在啟動輔庫的時候必須先把數據同步，并刪除日志目錄下的：master.info文件。因為master.info記錄
了上次要連接主庫的信息，如果不刪除，即使my.cnf里進行了修改，也不起作用。因為讀取的還是
master.info文件里的信息。
 
在mysql復制環境中,有8個參數可以讓我們控制,需要復制或需要忽略不進行復制的DB或table分別為: 
下面二項需要在Master上設置： 
Binlog_Do_DB:設定哪些數據庫需要記錄Binlog 
Binlog_Ignore_DB:設定哪里數據庫不需要記錄Binlog 
  
優點是Master端的Binlog記錄所帶來的Io量減少，網絡IO減少，還會讓slave端的IO線程,SQL線程減少，
從而大幅提高復制性能, 
缺點是mysql判斷是否需要復制某個事件不是根據產生該事件的查詢所在的DB,而是根據執行查詢時刻所在
的默認數據庫（也就是登錄時指定的庫名或運行"use database"中指定的DB）,只有當前默認DB和配置中
所設定的DB完全吻合時IO線程才會將該事件讀取給slave的IO線程.所以,如果在默認 DB和設定須要復制的
DB不一樣的情況下改變了須要復制的DB中某個Table中的數據,該事件是不會被復制到Slave中去的,這樣就
會造成Slave端的數據和Master的數據不一致.同樣,在默認的數據庫下更改了不須要復制的數據庫中的數據,
則會被復制到slave端,當slave端并沒有該數據庫時,則會造成復制出錯而停止. 
  
下面六項需要在slave上設置： 
Replicate_Do_DB:設定需要復制的數據庫,多個DB用逗號分隔 
Replicate_Ignore_DB:設定可以忽略的數據庫. 
Replicate_Do_Table:設定需要復制的Table 
Replicate_Ignore_Table:設定可以忽略的Table 
Replicate_Wild_Do_Table:功能同Replicate_Do_Table,但可以帶通配符來進行設置。 
Replicate_Wild_Ignore_Table:功能同Replicate_Do_Table,功能同Replicate_Ignore_Table,可以帶通配符。  
  
優點是在slave端設置復制過濾機制,可以保證不會出現因為默認的數據庫問題而造成Slave和Master數據
不一致或復制出錯的問題. 
缺點是性能方面比在Master端差一些.原因在于:不管是否須要復制,事件都會被IO線程讀取到Slave端,
這樣不僅增加了網絡IO量,也給Slave端的IO線程增加了Relay Log的寫入量.  
同步原理說明 
MySQL的Replication基于主服務器在二進制日志中跟蹤所有對數據庫的更改(更新、刪除等)。
MySQL使用3個線程來完成Replication工作，具體分布是主上1個相關線程、從上2個相關線程；
主的相關線程可以理解為主服務器上SHOW PROCESSLIST的輸出中的Binlog Dump線程、從服務器分別為IO和
SQL線程;
主服務器創建將binlog中的內容發送到從服務器。從服務器I/O線程讀取主服務器Binlog Dump線程發送的
內容并將該數據拷貝到從服務器數據目錄中的中繼日志文件（relay-log）里，SQL線程用于讀取中繼日志
并執行日志中包含的更新。 
MySQL的Replication是單向，異步同步 
MySQL同步機制基于master把所有對數據庫的更新、刪除等)都記錄在二進制日志里。因此，想要啟用同步
機制，在master就必須啟用二進制日志。每個slave接受來自master上在二進制日志中記錄的更新操作，
因此在slave上執行了這個操作的一個拷貝。應該非常重要地意識到，二進制日志只是從啟用二進制日志開
始的時刻才記錄更新操作的。所有的 slave必須在啟用二進制日志時把master上已經存在的數據拷貝過來。
如果運行同步時slave上的數據和master上啟用二進制日志時的數據不一致的話，那么slave同步就會失敗。
把master上的數據拷貝過來的方法之一實在slave上執行 LOAD DATA FROM MASTER 語句。不過要注意，LOAD DATA FROM MASTER是從MySQL 4.0.0之后才開始可以用的，而且只支持master上的 MyISAM 類型表。同樣地，
這個操作需要一個全局的讀鎖，這樣的話傳送日志到slave的時候在master上就不會有更新操作了。當實現了
自由鎖表熱備份時(在 MySQL 5.0中)，全局讀鎖就沒必要了。由于有這些限制，因此我們建議只在master上
相關數據比較小的時候才執行 LOAD DATA FROM MASTER 語句，或者在master上允許一個長時間的讀鎖。
由于每個系統之間 LOAD DATA FROM MASTER 的速度各不一樣，一個比較好的衡量規則是每秒能拷貝1MB數據。
這只是的粗略的估計，不過master和slave都是奔騰700MHz的機器且用 100MBit/s網絡連接時就能達到這個
速度了。slave上已經完整拷貝master數據后，就可以連接到master上然后等待處理更新了。如果 master
當機或者slave連接斷開，slave會定期嘗試連接到master上直到能重連并且等待更新。重試的時間間隔由 –master-connect-retry 選項來控制，它的默認值是60秒。每個slave都記錄了它關閉時的日志位置。
master是不知道有多少個slave連接上來或者哪個slave從什么時候開始更新。 
 
MySQL同步功能由3個線程(master上1個，slave上2個)來實現。執行 START SLAVE 語句后，slave就創建
一個I/O線程。I/O線程連接到master上，并請求master發送二進制日志中的語句。master創建一個線程來把
日志的內容發送到slave上。這個線程在master上執行 SHOW PROCESSLIST 語句后的結果中的 Binlog Dump
線程便是。slave上的I/O線程讀取master的 Binlog Dump 線程發送的語句，并且把它們拷貝到其數據目錄
下的中繼日志(relay logs)中。第三個是SQL線程，salve用它來讀取中繼日志，然后執行它們來更新數據。
如上所述，每個mster/slave上都有3個線程。每個 master上有多個線程，它為每個slave連接都創建一個線程，每個slave只有I/O和SQL線程。在MySQL 4.0.2以前，同步只需2個線程(master和slave各一個)。slave上的I/O
和SQL線程合并成一個了，它不使用中繼日志。slave上使用2個線程的優點是，把讀日志和執行分開成2個
獨立的任務。執行任務如果慢的話，讀日志任務不會跟著慢下來。例如，如果slave停止了一段時間，那么
I/O線程可以在slave啟動后很快地從master上讀取全部日志，盡管SQL線程可能落后I/O線程好幾的小時。
如果slave在SQL線程沒全部執行完就停止了，但I/O線程卻已經把所有的更新日志都讀取并且保存在本地的中
繼日志（relay-log）中了，因此在slave再次啟動后就會繼續執行它們了。這就允許在 master上清除二進制
日志，因為slave已經無需去master讀取更新日志了。執行 SHOW PROCESSLIST 語句就會告訴我們所關心的master和slave上發生的情況。

“Mysql主從安裝配置方法”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！