共計 6426 個字符,預計需要花費 17 分鐘才能閱讀完成。
本篇內容介紹了“如何安裝配置 Mysql 主從”的有關知識,在實際案例的操作過程中,不少人都會遇到這樣的困境,接下來就讓丸趣 TV 小編帶領大家學習一下如何處理這些情況吧!希望大家仔細閱讀,能夠學有所成!
環境:
主從服務器上的 MySQL 版本同為 5.1.34
主機 IP:192.168.0.1
從機 IP:192.168.0.2
一. MySQL 主服務器配置
1. 編輯配置文件 /etc/my.cnf
# 確保有如下行
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 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 -h292.168.0.1 -uslave -ppassword
mysql show grants for ;
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 STATUSG;
其中 Slave_IO_Running 和 Slave_SQL_Running 兩列的值都為 Yes,表明 Slave 的 I/O 和 SQL 線程都在正常運行。
8、解鎖主庫表
mysql UNLOCK TABLES;
到此主從庫搭建成功。可以在主庫上插入數據測試同步是否正常。
常見錯誤及解決方法:
常見問題的處理:
1:在從庫上面 show slave statusG; 出現下列情況,
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 statusG;
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 主從”的內容就介紹到這里了,感謝大家的閱讀。如果想了解更多行業相關的知識可以關注丸趣 TV 網站,丸趣 TV 小編將為大家輸出更多高質量的實用文章!