Ⅰ屉佳、復(fù)制類型
1.1 邏輯復(fù)制
- 記錄每次邏輯操作
- 主從數(shù)據(jù)庫可以不一致
1.2 物理邏輯復(fù)制
- 記錄每次對于數(shù)據(jù)頁的操作
- 主從數(shù)據(jù)物理嚴(yán)格一致
- 基于重做日志
說明：
如果一個塊(頁)修改了绪钥，就把這個修改發(fā)到遠(yuǎn)端
主從兩端不僅僅是數(shù)據(jù)一致淹魄，而是物理上的一致纪他，頁都是一樣的
1.3 復(fù)制選型與對比
簡單的主從環(huán)境趣苏，兩邊同樣的表扰楼，space_id不用一樣，只要保證數(shù)據(jù)在邏輯內(nèi)容上一致划鸽，物理上不用一樣输莺。也就是說戚哎，一張表的數(shù)據(jù)一致就行，不要求這些數(shù)據(jù)對應(yīng)的表空間里面內(nèi)容也一致
Oracle的優(yōu)勢
Oracle中commit時間是平均的嫂用，MySQL卻不是
MySQL中事務(wù)越大commit越慢型凳，binlog是事務(wù)執(zhí)行完commit之后才寫，從而產(chǎn)生延時的問題嘱函，Oracle中是物理邏輯復(fù)制甘畅，塊的變化實時同步到從上
MySQL的優(yōu)勢
做大數(shù)據(jù)，把MySQL里面的數(shù)據(jù)變化傳到數(shù)據(jù)倉庫平臺往弓，MySQL做起來就很方便疏唾，Oralce做起來麻煩，物理日志要去解析是解析不出來行的變化的函似，但是有ogg工具可以同步到hive槐脏，不過偏商業(yè)
Ⅱ、典型關(guān)系型數(shù)據(jù)庫復(fù)制對比
| - | MySQL | Oracle Data Gurad | SQL Server Mirroring |
|---|---|---|---|
| 類型 | 邏輯復(fù)制 | 物理邏輯復(fù)制 | 物理邏輯復(fù)制 |
| 優(yōu)點 | 靈活 | 復(fù)制速度快 | 復(fù)制速度快 |
| 缺點 | 配置不當(dāng)易出錯 | 要求物理數(shù)據(jù)嚴(yán)格一致 | 要求物理數(shù)據(jù)嚴(yán)格一致 |
問：MySQL的innodb也有redo撇寞，那為什么不支持redo級別的復(fù)制呢顿天？
答：因為MySQL有很多種引擎，需要有一個統(tǒng)一的復(fù)制層蔑担，而不是在引擎層做復(fù)制牌废，所以在server層實現(xiàn)復(fù)制，每個引擎做一個復(fù)制就做不到一致性了
一般業(yè)務(wù)架構(gòu)钟沛，一主兩從足夠畔规，金融行業(yè)可能會做一主四從
Ⅲ、復(fù)制搭建
小常識
MySQL的事務(wù)一次commit分成三個階段
第一階段的prepare和第三階段的commit都在innodb層
第二階段的write binlog在server層
三個步驟都是group commit的恨统，所以性能不會差
3.1 基本原理
+-----+ +-----+
| M | | S |
+--+--+ +--^--+
| change | apply
| +-----+ +---+--+
| +---->|io td| |sql td|
| | +--+--+ +---^--+
| | | |
| | |in_put | out_put
+--v-----+ | +----v----+ |
| binlog +--^ |relay log+-----^
+--------+ +---------+
當(dāng)commit寫到二進(jìn)制文件之后，如果搭建了復(fù)制三妈，就會有一個dump thread將binlog傳送到遠(yuǎn)程服務(wù)器
遠(yuǎn)端服務(wù)器上有一個IO thread來接收這些event放到relay log中畜埋，還有一個SQL thread來回放relay log中的event
5.6版本開始，可以多個線程并行回放畴蒲，所以速度相對來說可能快一些
tips：
①關(guān)于binlog悠鞍，詳見binlog——邏輯復(fù)制的基礎(chǔ)
②master發(fā)送給slave的是什么東西？具體怎么發(fā)送的模燥？ 答：以event為最小單位發(fā)送咖祭，千萬不要說sql語句或者記錄
③關(guān)于relay log
- Relay_Log_File和Relay_Log_Pos 是中繼日志（relay log）信息
- 由于IO線程拉取數(shù)據(jù)的速度快于SQL線程回放數(shù)據(jù)的速度，所以relay log可在兩者之間起到一個緩沖的作用
- relay log的格式和binlog的格式是一樣的蔫骂，但是兩者的內(nèi)容是不一樣的（不是和binlog一一對應(yīng)的）
- relay log在SQL線程回放完成后就會被刪除（默認(rèn)）么翰，而 binlog 不會（由expire_logs_days控制）
- relay log 可以通過設(shè)置 relay_log_purge=0，使得relay Log不被刪除（MHA中不希望被purge）辽旋，需要通過外部的腳本進(jìn)行刪除
3.2 動手搭一個
炒雞簡單浩嫌，雖然沒mongodb簡單檐迟，但比oracle簡單太多了(redis的cluster比mongodb都簡單)，搞起來Ｂ肽汀Ｉ取廓块！
| 步驟 | 操作 |
|---|---|
| step1 | 主機(jī)備份數(shù)據(jù) |
| step2 | 從機(jī)恢復(fù)數(shù)據(jù) |
| step3 | 主機(jī)授權(quán)一個復(fù)制用戶權(quán)限 |
| step4 | 從機(jī)change master to(filename,pos) |
- pos之前是導(dǎo)過來的數(shù)據(jù)浓利，pos之后通過binlog同步，這是一個最終一致性的模型苹威，master不斷將binlog發(fā)到slave
- binlog的恢復(fù)是手工的，MySQL的復(fù)制是準(zhǔn)實時的
實操
本人是窮人，這里用單機(jī)多實例模擬：
主用3306端口 server-id：3306
從用3307端口 server-id：3307
重點：
主從之間的server-id千萬不能一樣，否則可能導(dǎo)致宕機(jī)
說明：我這里3306跑的是mysql5.5,3307跑的mysql5.6，所以bashrc和my.cnf中配置的東西55就是3306，56就是3307的意思
step1：
主上做備份
[root@VM_0_5_centos ~]# mysqldump --single-transaction --master-data=1 -A -S /tmp/mysql.sock55 > /tmp/fullbackup.sql
step2：
從上導(dǎo)入備份
[root@VM_0_5_centos ~]# mysql56 < /tmp/fullbackup.sql
step3：
主上授權(quán)復(fù)制用戶權(quán)限
(root@172.16.0.10) [test]> create user 'rpl'@'%' identified by '123';
Query OK, 0 rows affected (0.01 sec)
(root@172.16.0.10) [test]> grant replication slave on *.* to 'rpl'@'%'; --需要replication和slave的權(quán)限筑煮，線上建議 限制成內(nèi)網(wǎng)的網(wǎng)段
Query OK, 0 rows affected (0.01 sec)
打開備份文件初澎，找到二進(jìn)制文件位置點
[root@VM_0_5_centos ~]# head -n 30 /tmp/fullbackup.sql
CHANGE MASTER TO MASTER_LOG_FILE='bin.000004', MASTER_LOG_POS=107;
找到上面這句
step4：
登陸到從實例執(zhí)行如下語句：
(root@172.16.0.10) [test]> CHANGE MASTER TO MASTER_LOG_FILE='bin.000004', MASTER_LOG_POS=107, MASTER_HOST='127.0.0.1' ,MASTER_PORT=3306 ,MASTER_USER='rpl' ,MASTER_PASSWORD='123';
Query OK, 0 rows affected, 2 warnings (0.12 sec) -- 有warning是因為密碼寫在了里面
檢查：
從上執(zhí)行show slave status\G --此處只貼部分關(guān)鍵內(nèi)容
(root@172.16.0.10) [test]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 127.0.0.1
Master_User: rpl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin.000004 -- change master中的filename
Read_Master_Log_Pos: 107 -- change master中的pos
Relay_Log_File: relay.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: bin.000004
Slave_IO_Running: No
Slave_SQL_Running: No
到這一步延柠，我們的主從復(fù)制就搭建好了贿条！但是我們看到io線程和sql線程都是no峻仇，我們需要在從機(jī)上啟動復(fù)制
(root@172.16.0.10) [test]>start slave;
Query OK, 0 rows affected (0.00 se
再看兩個線程就都是Yes了
Slave_IO_Running和Slave_SQL_Running這兩個指標(biāo)都為YES,表示目前的復(fù)制的狀態(tài)是正常的
3.3 細(xì)說show slave status\G
(root@172.16.0.10) [test]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event IO線程狀態(tài)
Master_Host: 127.0.0.1 主機(jī)IP
Master_User: rpl 復(fù)制用戶名
Master_Port: 3306 主機(jī)端口號
Connect_Retry: 60 連不上凡蚜，重連次數(shù)，默認(rèn)60次
Master_Log_File: bin.000004 當(dāng)前同步到主機(jī)的logfile
Read_Master_Log_Pos: 15451622 當(dāng)前同步到的位置點
Relay_Log_File: cloud-relay-bin.000002 slave上的中繼日志
Relay_Log_Pos: 15451775 中繼日志的位置點
Relay_Master_Log_File: bin.000004 當(dāng)前Relay文件對應(yīng)的Master文件
Slave_IO_Running: Yes IO線程狀態(tài)
Slave_SQL_Running: Yes SQL線程狀態(tài)
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0 最近一次的錯誤號
Last_Error: 最近一次錯誤信息
Skip_Counter: 0 跳過錯誤次數(shù)
Exec_Master_Log_Pos: 15451622 現(xiàn)在回放到的位置
Relay_Log_Space: 15451944 當(dāng)前中繼日志的大小
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0 sql線程落后io線程的時間
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3306
Master_UUID:
Master_Info_File: /data/mysql_test_data56/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
最終來看，io線程和sql線程執(zhí)行的位置應(yīng)該一樣轿秧，但是通常sql線程會比io線程慢一點
tips:
①Seconds_Behind_Master: The number of seconds that the slave SQL thread is behind processing the master binary log
relay log中event記錄的時間戳是主庫上的時間戳咨堤，而SQL thread的時間戳是從庫上的菇篡，只有主從時間完全一致，該指標(biāo)才有意義
②重連機(jī)制
slave-net-timeout 從獲取不到數(shù)據(jù)后等待時間
master-connect-retry 重連等待時間
master-retry-count 重連次數(shù)
當(dāng)從庫發(fā)現(xiàn)從主庫上無法獲得更多的數(shù)據(jù)了一喘，就會等待slave-net-timeout時間驱还，然后將IO thread置為no狀態(tài)，接著開始嘗試重建建立連接凸克，每次建立失敗之后等待master-connect-retry時間议蟆，一直重試master-retry-count次
Ⅳ、主從架構(gòu)中常用的操作
4.1 主
查看從服務(wù)器列表
(root@172.16.0.10) [test]> show slave hosts;
+-----------+------+-------+-----------+
| Server_id | Host | Port | Master_id |
+-----------+------+-------+-----------+
| 3307 | | 3307 | 3306 |
+-----------+------+-------+-----------+
1 row in set (0.00 sec)
看binlog到了什么位置
(root@172.16.0.10) [test]> show master status;
+----------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| mysql-bin.003 | 73 | | |
+-------------------------------------------------------------+
1 row in set (0.00 sec)
4.2 從
show slave status;
change master to
start/stop slave
4.3 報錯處理
模擬報錯：
從：
(root@172.16.0.10) [test]> create database sb;
Query OK, 1 row affected (0.00 sec)
主：
(root@172.16.0.10) [test]> create database sb;
Query OK, 1 row affected (0.00 sec)
從：
(root@172.16.0.10) [test]> show slave status\G
Last_SQL_Errno: 1007
Last_SQL_Error: Error 'Can't create database 'sb'; database exists' on query. Default database: 'sb'. Query: 'create database sb'
報錯沖突了萎战！
怎么辦咐容？
從：
執(zhí)行如下sql，跳過當(dāng)前報錯,不執(zhí)行這個event
(root@172.16.0.10) [test]> set global sql_slave_skip_counter=1;
Query OK, 0 row affected (0.00 sec)
重新把主從復(fù)制拉起來
start slave;
Query OK, 0 row affected (0.00 sec)
關(guān)于這個跳過當(dāng)前報錯后面會專門具體展開分析蚂维，暫時了解這么多即可
Ⅴ戳粒、read_only與super_read_only
如果在slave機(jī)器上對數(shù)據(jù)庫進(jìn)行修改或者刪除，會導(dǎo)致主從的不一致鸟雏，需要對Slave機(jī)器設(shè)置為read_only = 1 享郊，讓slave提供只讀操作。
tips：
read_only僅僅對沒有SUPER權(quán)限的用戶有效（即 mysql.user表的Super_priv字段為Y）孝鹊，一般給APP的權(quán)限是不需要SUPER權(quán)限的
MySQL 5.7中參數(shù)super_read_only可以將有SUPER權(quán)限的用戶也設(shè)置為只讀炊琉，且該參數(shù)設(shè)置為ON后，read_only也跟著自動設(shè)置為ON
當(dāng)前MySQL中又活，在/etc/my.cnf中將super_read_only=1配置好后重啟苔咪，還是可以插入或修改數(shù)據(jù)。需要在命令行中執(zhí)行set global super_read_only=1; 才能真正修改為只讀柳骄，具體何時修復(fù)還沒注意
