1.1 主從複製基礎概念 在瞭解主從複製之前必須要瞭解的就是資料庫的二進位日誌(binlog),主從複製架構大多基於二進位日誌進行,二進位日誌相關信息參考:http://www.cnblogs.com/clsn/p/8087678.html#_label6 1.1.1 二進位日誌管理說明 二進位日誌 ...
1.1 主從複製基礎概念
在瞭解主從複製之前必須要瞭解的就是資料庫的二進位日誌(binlog),主從複製架構大多基於二進位日誌進行,二進位日誌相關信息參考:http://www.cnblogs.com/clsn/p/8087678.html#_label6
1.1.1 二進位日誌管理說明
二進位日誌在哪?如何設置位置和命名?
在my.cnf文件中使用 log-bin = 指定;命名規則為 mysql-bin.000000 (後為6位數字)
二進位日誌位置
mysql> show variables like '%log_bin%' ; +---------------------------------+-----------------------------------------+ | Variable_name | Value | +---------------------------------+-----------------------------------------+ | log_bin | ON | | log_bin_basename | /application/mysql/data/mysql-bin | | log_bin_index | /application/mysql/data/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+-----------------------------------------+ 6 rows in set (0.06 sec)
日誌命名
mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 2979 | | mysql-bin.000002 | 120 | +------------------+-----------+ 2 rows in set (0.00 sec)
二進位日誌記錄什麼?
二進位日誌中記錄的是一個個完成的事件
二進位日誌格式是怎樣的?
推薦使用row格式
查看當前使用的日誌 格式。
mysql> show variables like '%format%'; +--------------------------+-------------------+ | Variable_name | Value | +--------------------------+-------------------+ | binlog_format | ROW | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s | | default_week_format | 0 | | innodb_file_format | Antelope | | innodb_file_format_check | ON | | innodb_file_format_max | Antelope | | time_format | %H:%i:%s | +--------------------------+-------------------+ 8 rows in set (0.00 sec)
二進位日誌如何滾動?
每次重啟都會刷新日誌,也可以通過命令進行刷新 reset master;
二進位日誌用來幹嘛?
備份恢復
起始點的備份恢復
二進位日誌的操作命令?
查看都有哪些二進位日誌
mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 2979 | | mysql-bin.000002 | 167 | | mysql-bin.000003 | 120 | +------------------+-----------+ 3 rows in set (0.00 sec)
查看當前使用的二進位日誌文件
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 120 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
binlog相關詳情參照:http://www.cnblogs.com/clsn/p/8087678.html#_label6
1.1.2 mysql傳統備份方式和缺陷
1、二進位日誌備份
2、mysqldump
a)必須有資料庫伺服器完成邏輯工作,需要更多地cpu周期
b)邏輯備份還原速度慢:需要MySQL載入和解釋語句、轉化存儲格式、重建引擎
3、xtrabackup
a)文件大
b)不總是可以跨平臺、操作系統和MySQL版本
1.1.3 MySQL主從複製能為我們做什麼
高可用、輔助備份、分擔負載
1.2 MySQL主從複製介紹
1.2.1 複製技術
作用
1.保證數據安全(異機實時備份)
2.保證服務持續運行(宕機接管)
主從複製實現基本原理
1.自帶功能,複製是 MySQL 的一項功能,允許伺服器將更改從一個實例複製到另一個實例。
2.主伺服器將所有數據和結構更改記錄到二進位日誌中。
3.從屬伺服器從主伺服器請求該二進位日誌併在本地應用其內容。即通過把主庫的binlog傳送到從庫,從新解析應用到從庫。
1.2.2 複製架構
mysql複製的應用常見場景:
應用場景1:從伺服器作為主伺服器的實時數據備份
應用場景2:主從伺服器實現讀寫分離,從伺服器實現負載均衡
應用場景3:把多個從伺服器根據業務重要性進行拆分訪問
1.2.2.1 主–從複製
傳統的 MySQL 複製提供了一種簡單的主–從複製方法。 有一個主,以及一個或多個從。 主節點執行和提交事務,然後將它們(非同步地)發送到從節點,以重新執行(在基於語句的複製中)或應用(在基於行的複製中)。 這是一個 shared-nothing 的系統,預設情況下所有 server 成員都有一個完整的數據副本。
(圖)MySQL 非同步複製
還有一個半同步複製,它在協議中添加了一個同步步驟。 這意味著主節點在提交時需要等待從節點確認它已經接收到事務。只有這樣,主節點才能繼續提交操作。
(圖)MySQL 非同步複製
在上面的兩個圖片中,可以看到傳統非同步 MySQL 複製協議(以及半同步)的圖形展示。 藍色箭頭表示在不同 server 之間或者 server 與 client 應用之間的信息交互。
1.2.3 MySQL主從複製原理介紹
複製過程:
1、開啟binlog日誌,通過把主庫的binlog傳送到從庫,從新解析應用到從庫。
2、複製需要3個線程(dump、io、sql)完成,5.6從庫多個sql。
3、複製是非同步的過程。主從複製是非同步的邏輯的SQL語句級的複製。
複製前提:
1、主服務期一定要打開二進位日誌
2、必須兩台伺服器(或者是多個實例)
3、從伺服器需要一次數據初始化
3.1如果主從伺服器都是新搭建的話,可以不做初始化
3.2如果主伺服器已經運行了很長時間了,可以通過備份將主庫數據恢復到從庫。
4、主庫必須要有對從庫複製請求的用戶。
5、從庫需要有relay-log設置,存放從主庫傳送過來的二進位日誌 show variables like '%relay%';
6、在第一次的時候,從庫需要change master to 去連接主庫。
7、change master信息需要存放到master.info中 show variables like '%master_info%';
8、從庫怎麼知道,主庫發生了新的變化?通過relay-log.info記錄的已經應用過的relay-log信息。
9、在複製過程中涉及到的線程
從庫會開啟一個IO thread(線程),負責連接主庫,請求binlog,接收binlog並寫入relay-log。
從庫會開啟一個SQL thread(線程),負責執行relay-log中的事件。
主庫會開啟一個dump thrad(線程),負責響應從IO thread的請求。
主從怎麼實現的?
1、通過二進位日誌
2、至少兩台(主、從)
3、主伺服器的二進位日誌“拿”到從伺服器上再運行一遍。
4、通過網路連接兩台機器,一般都會出現延遲的狀態。也可以說是非同步的。
1.2.4 執行原理--第一次開啟主從過程
1、 從庫通過手工執行change master to 語句連接主庫,提供了連接的用戶一切條件
(user、password、port、ip)
並且讓從庫知道,二進位日誌的起點位置(file名 position號)
start slave
2、從庫的IO和主庫的dump線程建立連接
3、從庫根據change master to 語句提供的file名和position號,IO線程向主庫發起binlog的請求
4、主庫dump線程根據從庫的請求,將本地binlog以events的方式發給從庫IO線程
5、從庫IO線程接收binlog evnets,並存放到本地relay-log中,傳送過來的信息,會記錄到master.info中。
6、從庫SQL線程應用relay-log,並且把應用過的記錄到relay-log.info,預設情況下,已經應用過的relay會自動被清理purge。
到此位置,一次主從複製就完成
一旦主從運行起來:
就不需要手工執行change master to,
因為信息都會被存放到master.info
(user、password、port、ip,上次獲取過的binlog信息file和position)中
其他的過程都是一樣的
1.2.4.1 詳細的mysql replication 過程
1.3 主從搭建配置
本次主從搭建使用mysql多實例進行實驗。多實例配置參考文檔進行配置:http://www.cnblogs.com/clsn/p/8038964.html#_label8
1.3.1 多實例資料庫slave配置
系統環境說明:
[root@db02 ~]# cat /etc/redhat-release CentOS release 6.9 (Final) [root@db02 ~]# uname -r 2.6.32-696.el6.x86_64 [root@db02 ~]# /etc/init.d/iptables status iptables: Firewall is not running. # 註意:務必關閉防火牆(iptables selinux) [root@db02 ~]# getenforce Disabled [root@db02 ~]# mysql --version mysql Ver 14.14 Distrib 5.6.36, for Linux (x86_64) using EditLine wrapper
1、啟動多實例資料庫
[root@db02 ~]# /data/3306/mysql start Starting MySQL... [root@db02 ~]# /data/3307/mysql start Starting MySQL...
2、配置文件說明:
master 配置文件說明:
[root@db02 ~]# cat /data/3306/my.cnf [client] port = 3306 socket = /data/3306/mysql.sock [mysqld] user = mysql port = 3306 socket = /data/3306/mysql.sock basedir = /application/mysql datadir = /data/3306/data log-bin = /data/3306/mysql-bin server-id = 6 # server id 不能相同 skip_name_resolve = 0 # 跳過功能變數名稱解析參數 [mysqld_safe] log-error=/data/3306/mysql_3306.err pid-file=/data/3306/mysqld.pid
slave 配置文件說明:
[root@db02 ~]# cat /data/3307/my.cnf [client] port = 3307 socket = /data/3307/mysql.sock [mysqld] user = mysql port = 3307 socket = /data/3307/mysql.sock basedir = /application/mysql datadir = /data/3307/data log-bin = /data/3307/mysql-bin server-id = 7 # server id 不能相同 skip_name_resolve = 0 # 跳過功能變數名稱解析參數 read_only = 1 # 從庫只讀 (非root用戶 ) [mysqld_safe] log-error=/data/3307/mysql_3307.err pid-file=/data/3307/mysqld.pid
3、在主庫創建複製用戶
登陸到主資料庫中:
mysql -uroot -p123 -S /data/3306/mysql.sock
創建授權用戶,註意是slave用戶。
grant replication slave on *.* to repl@'10.0.0.%' identified by '123';
4、初始化從庫數據
備份主庫當前數據
mysqldump -uroot -p123 -A -B -F --master-data=2 -S /data/3306/mysql.sock >/tmp/full.sql
部分參數說明:(詳細參照http://www.cnblogs.com/clsn/p/8138015.html#_label2)
-F 刷新二進位日誌
--master-data [=#]這會導致二進位日誌的位置和文件名被追加到輸出中。
如果等於1,則將其列印為CHANGE MASTER命令; 如果等於2,那麼該命令將以註釋符號為首碼。
到從庫進行恢復
mysql -uroot -p123 -S /data/3307/mysql.sock
# 恢復備份的數據
set sql_log_bin=0; source /tmp/full.sql
5、開啟從庫複製
查看備份的當前使用的文件及POS號
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000012 | 120 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
登入資料庫,進行slave配置。
mysql -uroot -p123 -S /data/3307/mysql.sock CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_USER='repl', MASTER_PASSWORD='123', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=120; start slave; # 啟動從庫複製
該配置想關說明可以通過 help 獲得。
mysql> help CHANGE MASTER TO CHANGE MASTER TO MASTER_HOST='master2.mycompany.com', MASTER_USER='replication', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='master2-bin.001', MASTER_LOG_POS=4, MASTER_CONNECT_RETRY=10;
1.3.2 測試主從同步
查看slave庫的狀態
主要查看 Slave_IO_Running 與 Slave_SQL_Running 是否都為Yes
1 mysql> show slave status\G 2 *************************** 1. row *************************** 3 Slave_IO_State: Waiting for master to send event 4 Master_Host: 10.0.0.52 5 Master_User: repl 6 Master_Port: 3306 7 Connect_Retry: 60 8 Master_Log_File: mysql-bin.000010 9 Read_Master_Log_Pos: 842 10 Relay_Log_File: 3307-relay-bin.000018 11 Relay_Log_Pos: 283 12 Relay_Master_Log_File: mysql-bin.000010 13 Slave_IO_Running: Yes 14 Slave_SQL_Running: Yes 15 Replicate_Do_DB: 16 Replicate_Ignore_DB: 17 Replicate_Do_Table: 18 Replicate_Ignore_Table: 19 Replicate_Wild_Do_Table: 20 Replicate_Wild_Ignore_Table: 21 Last_Errno: 0 22 Last_Error: 23 Skip_Counter: 0 24 Exec_Master_Log_Pos: 842 25 Relay_Log_Space: 455 26 Until_Condition: None 27 Until_Log_File: 28 Until_Log_Pos: 0 29 Master_SSL_Allowed: No 30 Master_SSL_CA_File: 31 Master_SSL_CA_Path: 32 Master_SSL_Cert: 33 Master_SSL_Cipher: 34 Master_SSL_Key: 35 Seconds_Behind_Master: 0 36 Master_SSL_Verify_Server_Cert: No 37 Last_IO_Errno: 0 38 Last_IO_Error: 39 Last_SQL_Errno: 0 40 Last_SQL_Error: 41 Replicate_Ignore_Server_Ids: 42 Master_Server_Id: 6 43 Master_UUID: 4f344556-e0ab-11e7-9138-000c29d60ab3 44 Master_Info_File: /data/3307/data/master.info 45 SQL_Delay: 0 46 SQL_Remaining_Delay: NULL 47 Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it 48 Master_Retry_Count: 86400 49 Master_Bind: 50 Last_IO_Error_Timestamp: 51 Last_SQL_Error_Timestamp: 52 Master_SSL_Crl: 53 Master_SSL_Crlpath: 54 Retrieved_Gtid_Set: 55 Executed_Gtid_Set: 56 Auto_Position: 0 57 1 row in set (0.00 sec)View Code 從庫狀態信息
在主庫進行操作,在從庫驗證
[root@db02 ~]# mysql -uroot -p123 -S /data/3306/mysql.sock mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> create database clsn; Query OK, 1 row affected (0.00 sec)
在從庫上可以看到該資料庫已創建
[root@db02 ~]# mysql -uroot -p123 -S /data/3307/mysql.sock mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | clsn | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
至此mysql主從複製就搭建完成
1.3.3 忘記資料庫密碼?
shell> /application/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-tables --skip-networking & mysql> update user set password=password('123') where user='root' and host='localhost'; mysql> flush privileges;
1.3.4 主從複製狀態失敗的原因?
Last_IO_Error: error reconnecting to master '[email protected]:3306' - retry-time: 60 retries: 1
原因:
1、主機沒啟動,或者宕機,檢查主庫的狀態。
2、網路通信問題,使用ping命令進行檢查;或使用mysql命令進行shell端登陸測試
3、防火牆,selinux(務必檢查)。
4、複製用戶和密碼、埠號、地址有問題,使用mysql命令進行shell端登陸測試。
5、mysql自動解析,會將連接的IP解析成主機名(skip-name-resolve = 0)寫入my.cnf文件即可。
6、從庫IO異常關閉,通過show slave status \G 進行查看。
1.4 MySQL主從複製常見問題
1.4.1 從庫binlog落後主庫binlog?
從庫記錄的已經主庫已經給我傳送的binlog事件的坐標,一般在繁忙的生產環境下會落後於主庫
show master status\G --- 主 show slave status \G --- 從 Master_Log_File: mysql-bin.000007 Read_Master_Log_Pos: 729
落後太遠的原因:
硬體條件有關的,機器磁碟IO性能不足。
主要還是網路問題,網路傳輸的性能。
主庫存放二進位日誌的存儲性能太低,建議binlog日誌存咋SSD中。
主庫DUMP線程太繁忙,主要發生在一主多從的環境下。
從庫IO線程太忙
人為控制(delay節點、延時節點 )
1.4.2 主庫update,從庫遲遲的沒有更新。
特殊情況:日誌已經傳過來了,數據並沒有同步
一般情況:
1、沒開啟SQL線程
2、傳的東西有問題(你要做的事情,我提前已經做了,不想重覆做了,然後他就死了)
3、SQL線程忙。
4、人為控制了【delay(從庫)節點、延時節點,一般生產設置為3-6小時之間,可以保證過去3-6小時之間的誤操作,可以避免】。
1.4.3 主從複製延時配置(從庫配置)
停止從庫複製
mysql>stop slave; Query OK, 0 rows affected (0.01 sec)
修改延時參數,MASTER_DELAY,單位位S (秒)。
mysql>CHANGE MASTER TO MASTER_DELAY = 30; Query OK, 0 rows affected (0.07 sec)
啟動從庫複製
mysql>start slave; Query OK, 0 rows affected (0.07 sec)
查看配置是否生效
mysql> show slave status \G …… SQL_Delay: 30
1.4.4 從庫安全配置(其他用戶只讀)
修改my.cnf配置文件,添加只讀參數
read_only = 1 ====> 控制普通用戶 innodb_read_only = 1 ====> 控制root用戶,正常情況不要加
添加完成後重啟資料庫
mysql> show variables like '%read_only%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | innodb_read_only | OFF | | read_only | ON | | tx_read_only | OFF | +------------------+-------+ 3 rows in set (0.00 sec)
延時從庫: delay節點、延時節點
1.4.5 主從複製故障及解決(跳過錯誤)
命令行設置
stop slave; #<==臨時停止同步開關。 set global sql_slave_skip_counter = 1 ; #<==將同步指針向下移動一個,如果多次不同步,可以重覆操作。 start slave;
在配置文件修改,設置要跳過的pos
/etc/my.cnf slave-skip-errors = 1032,1062,1007
在mysql中可以跳過某些錯誤,但是最好的解決辦法,重新搭建主從複製。
1.4.6 延時節點概念 --> SQL線程延時?
Last_SQL_Errno: 0 Last_SQL_Error:
原因:
1、主庫做操作的對象,在從庫不存在
2、主庫做操作的對象屬性不一致。
3、主庫做操作的對象,從庫已經存在
……
1.4.7 Slave_*_Running:?
1、Slave_IO_Running I/O 線程正在運行、未運行還是正在運行但尚未連接到主伺服器。可能值分別為Yes、No 或 Connecting。
2、Slave_SQL_Running SQL 線程當前正在運行、未運行,可能值分別為 Yes、No 主伺服器日誌坐標:
3、Master_Log_File 和 Read_Master_Log_Pos 標識主伺服器二進位日誌中 I/O 線程已經傳輸的最近事件的坐標。
4、如果Master_Log_File和Read_Master_Log_Pos 的值遠遠落後於主伺服器上的那些值,這表示主伺服器與從屬伺服器之間事件的網路傳輸可能存在延遲。
1.4.8 中繼日誌坐標
a) Relay_Log_File 和 Relay_Log_Pos 列標識從屬伺服器中繼日誌中 SQL 線程已經執行的最近事件的坐標。這些坐標對應於 Relay_Master_Log_File 和 Exec_Master_Log_Pos 列標識的主伺服器二進位日誌中的坐標。
b) 如果 Relay_Master_Log_File 和 Exec_Master_Log_Pos 列的輸出遠遠落後於 Master_Log_File 和Read_Master_Log_Pos 列(表示 I/O 線程的坐標),這表示 SQL 線程(而不是 I/O 線程)中存在延遲。即,它表示覆制日誌事件快於執行這些事件。
1.4.9 單一主從需要改變的地方
從庫的作用
1、相當於實時備份
2、使用從庫備份
3、一主多從應對讀多的業務需求
如果,從庫只做備份伺服器用,那麼主庫的壓力會不減反增。因為,所有的業務都在主庫實現,讀和寫,dump線程讀取並投遞binlog
解決方案:
(1)可不可以挪走一部分讀業務到從庫,讀寫分離
(2) 一主多從應對讀多的業務需求,一旦發展成這個架構,dump線程投遞binlog的壓力更大
(3) 多級主從,採用中間庫緩解主庫dump的壓力,會出現中間庫瓶頸的問題,選擇blackhole引擎,看性能與安全的權衡
(4)雙主模型:緩解,數據一致性難保證
(5)環裝複製
1.5 【生產案例】主從複製事故
1.5.1 發生背景
1、有一臺已經工作很久的單機mysql數據。在2017年12月24日的平安夜,我司購物網站宕機了。機器物損壞,系統硬碟報廢。
2、在接到一條簡訊告知伺服器宕機,資料庫連不上。當時的我一臉懵逼的還在開party,誰能想到在這樣一個闔家歡樂的時刻發生這樣的事情。
3、隨之我火速趕回公司處理事故。首先更換硬碟,從備份伺服器上拉取備份數據,用備份恢復宕機的時刻數據,經歷40分鐘後所有應用恢復正常。
4、經歷這次事故,決心修改資料庫架構,我跟領導承諾,保證改完之後,出現類似故障能在5-10分鐘恢復業務。把原來的停機時間縮短4-8倍。
1.5.2 搭建流程
1.5.2.1 架構設計
修改架構採用資料庫主從同步,能保證數據的安全,提高事故發生的恢復速度。
1.5.2.2 架構實施
(1)準備一臺新機器,配置、系統、環境等與原資料庫保持一致。
(2)在主庫檢查binlog開關,沒有開啟將其開啟 ,檢查server_id 與 auto.cnf文件中的uuid 是否唯一。
(3)主庫創建授權複製的用戶,授權 replication slave。
(4)備份主庫上現有數據,恢復到從庫中,推薦使用mysqldump,在訪問低谷的時候做。
(5)在從庫上開啟binlog和relaylog,server_id。
(6)在從庫配置change master to 信息:在第一次開啟主從的時候,告訴從庫user password host port,複製binlog的起點file、position。
(7)start slave 開啟主從複製。
到此歷經千辛萬苦主從複製搭建完成。
1.5.3 測試主從切換
(1) 主從的可用性測試:在主庫中插入數據,在從庫查看有沒有。
(2) 主從快速恢復演練
a) 在一個月黑風高夜