mysql之主從複製
MySQL主從複製介紹
MySQL的主從複製是其自帶的功能,經過邏輯的binlog日誌複製到要同步的服務器本地,主服務器（Master），接收來自用戶的內容更新，而一個或多個其餘的服務器充當從服務器（Slave），接收來自主服務器binlog文件的日誌內容，解析出SQL，從新更新到從服務器，使得主從服務器數據達到一致。前端
MySQL主從複製都是異步的複製方式，既不是嚴格實時的數據同步，可是正常狀況下給用戶的體驗是真實的。mysql
複製原理介紹
- MySQL的主從複製是一個異步的複製過程（雖然通常狀況下感受是實時的），數據將從一個MySQL數據庫（咱們稱之爲Master）複製到另外一個MySQL數據庫（咱們稱之爲Slave），在Master與Slave之間實現整個主從複製的過程是由三個線程參與完成的。其中有兩個線程（SQL線程和I/O線程）在Slave端，另一個線程（I/O線程）在Master端。
- 要實現MySQL的主從複製，首先必須打開Master端的binlog記錄功能，不然就沒法實現。由於整個複製過程實際上就是Slave從Master端獲取binlog日誌，而後再在Slave上以相同順序執行獲取的binlog日誌中所記錄的各類SQL操做。
- 要打開MySQL的binlog記錄功能，可經過在MySQL的配置文件my.cnf中的mysqld模塊（[mysqld]標識後的參數部分）增長「log-bin」參數選項來實現，
MySQL主從複製原理過程詳細描述
1在slave服務器上執行start slave命令開始主從複製
2.此時slave服務器的io線程會請求連接主服務器,並從指定的binlog文件位置以後開始獲取日誌內容
3.master服務器接收到slave服務器io線程的請求後,負責複製的io線程會根據收到的信息讀取日誌文件指定位置以後的日誌內容,而後返回給slave端的io線程,
返回信息除了日誌內容外,還有新的binlog文件以及新的指定的位置
4.當slave服務器io線程收到master服務器上的日誌文件內容以及新的日誌文件名和新的位置後,會把日誌內容寫到slave服務器端自身的relay log(中繼日誌)的最末端,
並將新的日誌文件名和位置記錄到master-info文件中,以便下次讀取
5.slave服務器的sql線程會實時監測本地的relay log的內容,而後及時把relay log的內容解析成sql語句,按順序依次執行這些sql語句,
並在relay log info中記錄中繼日誌的文件名與位置sql
MySQL主從複製重點:
- 主從複製是異步的邏輯的SQL語句級的複製
- 複製時，主庫有一個I/O線程，從庫有兩個線程，即I/O和SQL線程
- 實現主從複製的必要條件是主庫要開啓記錄binlog功能
- 做爲複製的全部MySQL節點的server-id都不能相同。
- binlog文件只記錄對數據庫有更改的SQL語句（來自主數據庫內容的變動），不記錄任何查詢（如select，show）語句。
主從複製實戰
此處應用爲 一臺電腦 多實例mysql 主從複製 和單實例多服務器主從複製原理同樣數據庫
mysql主服務配置
設置server-id值並開啓binlog功能參數vim
vim my.cnf #修改主庫的配置文件 [mysqld] #參數要放在my.cnf中的[mysqld]模塊下，不然會出錯。 server-id = 1 #用於同步的每臺機器或實例server-id都不能相同 log-bin = /data/3306/mysql-bin #binlog日誌的位置
檢查配置參數後的結果緩存
egrep "server-id|log-bin" /data/3306/my.cnf log-bin = /data/3306/mysql-bin server-id = 1
重啓主庫MySQL服務服務器
/data/3306/mysql restart
登錄數據庫，檢查參數的更改狀況網絡
mysql -uroot -p123456 -S /data/3306/mysql.sock show variables like 'server_id'; #查看MySQL的系統變量（like相似於grep過濾） +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 1 | #配置的server_id爲1 +---------------+-------+ 1 row in set (0.00 sec) show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | #binlog功能已開啓 +---------------+-------+ 1 row in set (0.00 sec)
創建用於從庫複製的帳號多線程
grant replication slave on *.* to 'wk'@'192.168.50.%' identified by '123456';
#replication slave爲mysql同步的必須權限，此處不要受權all權限
flush privileges; #建立完帳號並受權後，須要刷新權限，使受權的權限生效
檢查主庫建立的wk複製帳號命令及結果以下：架構
select user,host from mysql.user; +------+--------------+ | user | host | +------+--------------+ | root | 127.0.0.1 | | wk | 192.168.50.% | | root | ::1 | | | localhost | | root | localhost | | root | www | +------+--------------+
show grants for wk@'192.168.50.%'; #查看受權情況 +--------------------------------------------------------------------------------------------------------------------------+ | Grants for wk@192.168.50.% | +--------------------------------------------------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO 'wk'@'192.168.50.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | +--------------------------------------------------------------------------------------------------------------------------+
#結果顯示受權正確
鎖表徹底備份主庫 (對於快速配置主從複製 不須要鎖表)
對主數據庫鎖表只讀（當前窗口不要關掉）的命令以下：
flush table with read lock; Query OK, 0 rows affected (0.00 sec)
在引擎不一樣的狀況下，這個鎖表命令的時間會受下面參數的控制。鎖表時，若是超過設置時間不操做會自動解鎖。
默認狀況下自動解鎖的時長參數值以下：
show variables like '%timeout%'; +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 120 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 28800 | #自動解鎖時間受本參數影響 | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | slave_net_timeout | 3600 | | wait_timeout | 28800 | #自動解鎖時間受本參數影響 +----------------------------+----------+
鎖表後查看主庫狀態
show master status; #根據Position偏移量 肯定是否在鎖表後的主從複製時 內容有所變更 +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 1193 | | | +------------------+----------+--------------+------------------+
鎖表後，必定要單開一個新的SSH窗口，導出數據庫的全部數據，若是數據量很大（50GB以上），而且容許停機，能夠停庫直接打包數據文件進行遷移，那樣更快。
分之一 快速主從複製 不須要上述鎖表mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show master status"
mysqldump -uroot -p123456 -S /data/3306/mysql.sock --events -x --master-data=1 -A -B | gzip >/server/backup/mysql_bak.$(date +%F).sql.gz #-A表示備份全部庫；-B表示增長use DB和 drop 等（導庫時會直接覆蓋原有的） --master-data=1 直接記錄日誌文件名和偏移量 不須要在change master
裏寫入日誌文件名和偏移量 -x 導出時鎖表
分支二
mysqldump -uroot -p123123 -S /data/3306/mysql.sock --events -A -B | gzip >/server/backup/mysql_bak.$(date +%F).sql.gz
#此種配置須要上述鎖表同時還須要在change master裏寫入日誌文件名和偏移量
#爲了確保導出數據期間，數據庫沒有數據插入，導庫完畢能夠再次檢查主庫狀態信息，結果以下：
mysql-bin.000001 | 1193 #確保遷移量沒有發生變化
在MySQL從庫的配置
設置server-id值並關閉binlog功能參數
vi my.cnf [mysqld] server-id = 2 #調整等號後的數值，和任何一個數據庫實例都不一樣 egrep "server-id|log-bin" /data/3307/my.cnf #檢查配置參數後的結果 server-id = 2
重啓從數據庫
/data/3307/mysql restart
把從主庫mysqldump導出的數據恢復到從庫
cd /server/backup/ gzip -d mysql_bak.2018-08-06.sql.gz mysql -uroot -S /data/3307/mysql.sock <mysql_bak.2018-08-06.sql #這是把數據還原到3307實例的命令
登錄3307從庫，配置複製參數
MySQL從庫鏈接主庫的配置信息以下：
分之一 快速配置 CHANGE MASTER TO MASTER_HOST='192.168.50.149', #這裏是主庫的IP MASTER_PORT=3306, #這裏是主庫的端口，從庫端口能夠和主庫不一樣 MASTER_USER='wk', #這裏是主庫上創建的用於複製的用戶wk MASTER_PASSWORD='123456'; #這裏是wk用戶的密碼 #因爲--master-data=1記錄了日誌文件名和偏移量因此此處不設置MASTER_LOG_FILE和MASTER_LOG_POS 分之二 CHANGE MASTER TO MASTER_HOST='192.168.50.149', #這裏是主庫的IP MASTER_PORT=3306, #這裏是主庫的端口，從庫端口能夠和主庫不一樣 MASTER_USER='wk', #這裏是主庫上創建的用於複製的用戶wk MASTER_PASSWORD='123456', #這裏是wk用戶的密碼 MASTER_LOG_FILE='mysql-bin.000001', #這裏是show master status時查看到的二進制日誌文件名稱，注意不能多空格 MASTER_LOG_POS=533; #這裏是show master status時查看到的二進制日誌偏移量，注意不能多空格
上述操做的原理其實是把用戶密碼等信息寫入從庫新的master.info文件中cat /data/3307/data/master.info
分之一 cat /data/3307/data/master.info 18 4 192.168.50.149 wk 123456 3306 60 0 分之二 cat /data/3307/data/master.info 18 mysql-bin.000001 1193 192.168.50.149 wk 123456 3306 60 0
啓動從庫同步開關，測試主從複製配置狀況
mysql -uroot -S /data/3307/mysql.sock -e "start slave" mysql -uroot -S /data/3308/mysql.sock -e "show slave status\G"
主從同步是否成功，最關鍵的爲下面的3項狀態參數：
- Slave_IO_Running: Yes,這個時I/O線程狀態，I/O線程負責從從庫到主庫讀取binlog日誌，並寫入從庫的中繼日誌，狀態爲Yes表示I/O線程工做正常。
- :Slave_SQL_Running: Yes,這個是SQL線程狀態，SQL線程負責讀取中繼日誌（relay-log）中的數據並轉換爲SQL語句應用到從數據庫中，狀態爲Yes表示I/O線程工做正常。
- :Seconds_Behind_Master:0,這個是複製過程當中從庫比主庫延遲的秒數，這個參數極度重要，但企業裏更準確地判斷主從延遲的方法爲：在主庫寫時間戳，而後從庫讀取時間戳，和當前數據庫時間進行比較，從而認定是否延遲。
此時主從複製配置成功
主從複製配置步驟小結
- 準備兩臺數據庫環境或單臺多實例環境，肯定能正常啓動和登錄
- 配置my.cnf文件：主庫配置log-bin和server-id參數；從庫配置server-id，該值不能和主庫及其餘從庫同樣，通常不開啓從庫log-bin功能。注意，配置參數後要重啓才能生效。
- 登錄主庫，增長從庫鏈接主庫同步的帳戶，例如：yunjisuan，並受權replication slave同步的權限。
- 登錄主庫，整庫鎖表flush table with read lock（窗口關閉後即失效，超時參數設置的時間到了，鎖表也失效），而後show master status查看binlog的位置狀態。
- 新開窗口，在Linux命令行備份導出原有的數據庫數據，並拷貝到從庫所在的服務器目錄。若是數據庫數據量很大，而且容許停機，能夠停機打包，而不用mysqldump。
- 導出主庫數據後，執行unlock tables解鎖主庫。
- 把主庫導出的數據恢復到從庫
- 根據主庫的show master status查看到的binlog的位置狀態，在從庫執行change master to....語句。
- 從庫開啓複製開關，即執行start slave；。
- 從庫show slave status\G,檢查同步狀態，並在主庫進行更新測試。
分之一和分之二的區別在於導出主備文件
在企業中增長從庫 只須要深夜掛定時任務執行
mysqldump -uroot -p123456 -S /data/3306/mysql.sock --events -x --master-data=1 -A -B | gzip >/server/backup/mysql_bak.$(date +%F).sql.gz
次日把該文件移植到從庫 開啓主從複製功能便可
MySQL主從複製主庫I/O線程狀態說明
登錄主數據庫查看MySQL線程的同步狀態
show processlist\G
#紅色內容表示上述狀態的意思是線程已經從binlog日誌讀取全部更新，並已經發送到了從數據庫服務器。線程目前爲空閒狀態，等待由主服務器上二進制日誌中的新事件更新 *************************** 1. row *************************** #從庫1IO線程 Id: 5 User: wk Host: 192.168.50.149:51932 db: NULL Command: Binlog Dump Time: 4480 State: Master has sent all binlog to slave; waiting for binlog to be updated Info: NULL *************************** 2. row *************************** #從庫2IO線程 Id: 15 User: wk Host: 192.168.50.149:51933 db: NULL Command: Binlog Dump Time: 557 State: Master has sent all binlog to slave; waiting for binlog to be updated Info: NULL *************************** 3. row *************************** Id: 17 User: root Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: show processlist 3 rows in set (0.00 sec)
下圖中列出了主服務器binlog Dump線程中State列的最多見狀態。若是你沒有在主服務器上看見任何binlog Dump線程，則說明覆制沒有運行，二進制binlog日誌由各類事件組成，事件一般會爲更新添加信息。
登錄從數據庫查看MySQL線程工做狀態
show processlist\G
紅1表示線程已鏈接到服務器,等待二進制日誌到達
紅2表示已處理完全部日誌等待新日誌到來 *************************** 1. row *************************** #IO線程 Id: 5 User: system user Host: db: NULL Command: Connect Time: 621 State: Waiting for master to send event Info: NULL *************************** 2. row *************************** #SQL線程 Id: 6 User: system user Host: db: NULL Command: Connect Time: 385 State: Slave has read all relay log; waiting for the slave I/O thread to update it Info: NULL *************************** 3. row *************************** Id: 9 User: root Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: show processlist 3 rows in set (0.00 sec)
- 經過MySQL線程同步狀態能夠看到同步是否正常進行，故障的位置是什麼，另外還可查看數據庫同步是否完成，可用於主庫宕機切換數據庫或人工數據庫主從切換遷移等。
- 例如：主庫宕機，要選擇最快的從庫將其提高爲主庫，就須要查看主從庫的線程狀態，若是主從複製在正常狀況下進行角色切換，也須要查看主從庫的線程狀態，根據複製狀態肯定更新是否完成。
mysql主從複製出現問題
複製故障
Slave_IO_Running: Yes
Slave_SQL_Running: No 表示複製故障
Master_Server_Id: 1 主從同步狀態:關閉
stop slave; #關閉主從同步 Query OK, 0 rows affected, 1 warning (0.00 sec)
set global sql_slave_skip_counter=1; #將sql線程同步指針向下移動一個，若是屢次不一樣步，能夠重複操做 Query OK, 0 rows affected (0.00 sec)
start slave; #開啓主從同步 Query OK, 0 rows affected (0.00 sec)
解決方法2
根據能夠忽略的錯誤號事先在配置文件中配置，跳過指定的不影響業務數據的錯誤，例如：
vim my.cnf
[mysqld] slave-skip-errors = 1032,1062,1007
讓MySQL從庫記錄binlog日誌的方法
若是從庫下邊還有從庫須要開啓從庫1的binlog功能
[mysqld]
log-slave-updates #必需要有這個參數 log-bin = /data/3307/mysql-bin expire_logs_days = 7 #至關於find /data/3307/ -type f -name "mysql-bin.000*" -mtime +7 | xargs rm -f 刪除7天以上的日誌文件
MySQL主從複製延遲問題的緣由及解決方案
問題一：主庫的從庫太多，致使複製延遲
從庫數量以3~5個爲宜，要複製的從節點數量過多，會致使複製延遲。
問題二：從庫硬件比主庫差，致使複製延遲。
查看Master和Slave的系統配置，可能會由於機器配置不當，包括磁盤I/O，CPU，內存等各方面因素形成複製的延遲。這通常發生在高併發大數據量寫入場景中。
問題三：慢SQL語句太多
假如一條SQL語句執行時間是20秒，那麼從執行完畢到從庫上能查到數據至少須要20秒，這樣就延遲20秒了。
通常要把SQL語句的優化做爲常規工做，不斷的進行監控和優化，若是單個SQL的寫入時間長，能夠修改後分屢次寫入。經過查看慢查詢日誌或show full processlist命令，找出執行時間長的查詢語句或大的事務。
問題四：主從複製的設計問題
例如，主從複製單線程，若是主庫寫併發太大，來不及傳送到從庫，就會致使延遲。
更高版本的MySQL能夠支持多線程複製，門戶網站則會本身開發多線程同步功能。
問題五：主從庫之間的網絡延遲
主從庫的網卡，網線，鏈接的交換機等網絡設備均可能成爲複製的瓶頸，致使複製延遲，另外，跨公網主從複製很容易致使主從複製延遲。
問題六：主庫讀寫壓力大，致使複製延遲。
主庫硬件要搞好一點，架構的前端要加buffer及緩存層。
經過read-only參數讓從庫只讀訪問
read-only參數選項可讓從服務器只容許來自從服務器線程或具備SUPER權限的數據庫用戶進行更新，確保從服務器不接受來自用戶端的非法用戶更新。
read-only參數容許數據庫更新的條件爲：
- 具備SUPER權限的用戶能夠更新，不受read-only參數影響，例如：管理員root。
- 來自從服務器線程能夠更新，不受read-only參數影響，例如：前文的yunjisuan用戶。
- 再生產環境中，能夠在從庫Slave中使用read-only參數，確保從庫數據不被非法更新。
在my.cnf裏[mysqld]模塊下加read-only參數重啓數據庫，配置以下：
[mysqld]
read-only
主從同步時設置單個或多個庫不作同步
生產環境中通常會採起忽略受權表方式的同步， 而後對從服務器(slave)上的用戶僅受權select 讀權限。不一樣步mysql庫，這樣咱們就保證主庫和從庫相同的用戶能夠受權不一樣的權限。。
忽略mysql庫和information_ schema 庫的主從同步。
[mysqld]
replicate- ignore -db=mysql4 binlog-do-db = testdh. binlog- ignore-db = mysql↔ binlog-ignore-db = per formance_ schemar binlog-ignore-db = informat ion_ schemat 提示:忽略記錄binlog日誌的參數binlog-ignore-db通常用於系統的庫和表。。