這篇文章帶大家了解MySQL中的主備、主從和讀寫分離，希望對大家有幫助！
在狀態1中，客戶端的讀寫都會直接存取節點A，而節點B是A的備庫，只是將A的更新都同步過來，到本地執行。這樣可以保持節點B和A的資料是相同的。當需要切換的時候，就切成狀態2。這時候客戶端讀寫存取的都是節點B，而節點A是B的備庫。 【相關推薦：mysql影片教學】
在狀態1中，雖然節點B沒有直接訪問，但建議把備庫節點B，設定成唯讀模式。有以下幾個原因：
1.有時候一些運營類別的查詢語句會被放到備庫上去查，設定為只讀可以防止誤操作
2.防止切換邏輯有bug
3.可以用readonly狀態，來判斷節點的角色
把備庫設定成唯讀，還怎麼跟主函式庫保持同步更新？
readonly設定對超級權限使用者是無效的，而用於同步更新的線程，就擁有超級權限
下圖是一個update語句在節點A執行，然後同步到節點B的完整流程圖：
備庫B和主庫A之間維持了一個長連結。主庫A內部有一個線程，專門用來服務備庫B的這個長連線。一個交易日誌同步的完整過程如下：
1.在備庫B上透過change master指令，設定主庫A的IP、連接埠、使用者名稱、密碼，以及要從哪個位置開始請求binlog，這個位置包含檔名與日誌偏移量
2.在備庫B上執行start slave指令，這時備庫會啟動兩個線程，就是圖中的io_thread和sql_thread。其中io_thread負責與主庫建立連線
3.主庫A校驗完使用者名稱、密碼後，開始依照備庫B傳過來的位置，從本機讀取binlog，發給B
4.備庫B拿到binlog後，寫到本機文件，稱為中轉日誌
5.sql_thread讀取中轉日誌，解析出日誌裡的指令，並執行
由於多執行緒複製方案的引入，sql_thread演化成了多個執行緒
# 節點A和節點B互為主備關係。這樣在切換的時候就不用再修改主備關係
2.之後傳給備庫B，備庫B接收完這個binlog的時刻記為T2#3.備庫B執行完這個事務，把這個時刻記為T3所謂主備延遲，就是同一個事務，在備庫執行完成的時間和主庫執行完成的時間之間的差值，也就是T3-T1可以在備庫上執行show slave status指令，它的回傳結果裡面會顯示seconds_behind_master，用來表示目前備庫延遲了多少秒seconds_behind_master的計算方法是這樣的：
1.每個事務的binlog裡面都有一個時間字段，用於記錄主庫上寫入的時間
#2.備庫取出當前正在執行的事務的時間字段的值，計算它與目前系統時間的差值，得到seconds_behind_master
如果主備庫機器的系統時間設定不一致，不會導致主備延遲的值不準。備庫連接到主庫的時候，會透過SELECTUNIX_TIMESTAMP()函數來取得目前主庫的系統時間。如果此時發現主庫的系統時間與自己不一致，備庫在執行seconds_behind_master計算的時候會自動扣掉這個差值
網路正常情況下，主備延遲的主要來源是備庫接收完binlog與執行完這個交易之間的時間差
主備延遲最直接的表現是，備庫消費中轉日誌的速度，比主庫生產binlog的速度慢
1.有些部署條件下，備庫所在機器的性能要比主庫所在的機器性能差
2.備庫的壓力大。主庫提供寫入能力，備庫提供一些讀能力。忽略了備庫的壓力控制，導致備庫上的查詢耗費了大量的CPU資源，影響了同步速度，造成主備延遲
可以做以下處理：
3.大事務。因為主庫上必須等事務執行完才會寫入binlog，再傳給備庫。所以，如果一個主庫上的語句執行10分鐘，那麼這個事務很可能會導致從庫延遲10分鐘
典型的大事務場景：一次性地用delete語句刪除太多資料和大表的DDL
雙M結構下，從狀態1到狀態2切換的詳細過程如下：
1.判斷備庫B現在的seconds_behind_master，如果小於某個值繼續下一步，否則持續重試這一步驟
2.把主庫A改成唯讀狀態，也就是把readonly設定為true
3.判斷備庫B的seconds_behind_master的值，直到這個值變成0為止
#4.把備庫B改成可讀寫狀態，也就是把readonly設定為false
5.把業務請求切到備庫B
## 這個切換流程中是有不可用的時間的。在步驟2之後，主庫A和備庫B都處於readonly狀態，也就是說這時系統處於不可寫狀態，直到步驟5完成後才能恢復。在這個不可用狀態中，比較耗時的是步驟3，可能需要耗費好幾秒鐘的時間。也是為什麼需要在步驟1先做判斷，確保seconds_behind_master的值足夠小
mysql> CREATE TABLE `t` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `c` int(11) unsigned DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;insert into t(c) values(1),(2),(3);
表t定義了一個自增主鍵id，初始化資料後，主函式庫和備庫上都是3行資料。繼續在表t上執行兩個插入語句的指令，依序是：
insert into t(c) values(4);insert into t(c) values(5);
假設，現在主函式庫上其他的資料表有大量的更新，導致主備延遲達到5秒。在插入一條c=4的語句後，發起了主備切換
下圖是可用性優先策略，且binlog_format=mixed時的切換流程和資料結果
1.步驟2中，主庫A執行完insert語句，插入了一行資料(4,4)，之後開始進行主備切換
2.步驟3中，由於主備之間有5秒的延遲，所以備庫B還來不及套用插入c=4這個中轉日誌，就開始接收客戶端插入c=5的指令
3.步驟4中，備庫B插入了一行資料(4, 5)，並且把這個binlog發給主庫A
4.步驟5中，備庫B執行插入c=4這個中轉日誌，插入了一行資料(5,4)。而直接在備庫B執行的插入c=5這個語句，傳到主庫A，就插入了一行新資料(5,5)
最後的結果就是，主庫A和備庫B上出現了兩行不一致的資料
可用性優先策略，設定binlog_format=row
因此row格式在記錄binlog的時候，會記錄新插入的行的所有欄位值，所以最後只會有一行不一致。而且，兩邊的主備同步的應用程式執行緒會報錯duplicate key error並停止。也就是說，在這種情況下，備庫B的(5,4)和主庫A的(5,5)這兩行資料都不會被對方執行
1.使用row格式的binlog時，資料不一致問題更容易被發現。而使用mixed或statement格式的binlog時，可能過了很久才發現資料不一致的問題
2.主備切換的可用性優先原則會導致資料不一致。因此，大多數情況下，建議採用可靠度優先策略
主備的並行複製能力，要注意的就是上圖黑色的兩個箭頭。一個代表客戶端寫入主庫，另一個代表備庫上sql_thread執行中轉日誌
在MySQL5.6版本之前，MySQL只支援單執行緒複製，由此在主函式庫並發高、TPS高時就會出現嚴重的主備延遲問題
多線程複製機制都是把只有一個線程的sql_thread拆成多個線程，都符合下面這個模型：
coordinator就是原來的sql_thread，不過現在它不再直接更新資料了，只負責讀取中轉日誌和分發事務。真正更新日誌的，變成了worker執行緒。而worker執行緒的數量就是由參數slave_parallel_workers決定的
coordinator在分發的時候，需要滿足以下兩個基本要求：
MySQL5.6版本支援了平行複製，只是支援的粒度是按程式庫並行。用來決定分發策略的hash表裡，key是資料庫名稱
這個策略的平行效果取決於壓力模型。如果在主函式庫上有多個DB，且各個DB的壓力均衡，使用這個策略的效果會很好
這個策略的兩個優點：
可以建立不同的DB，把相同熱度的表格均勻分到這些不同的DB中，強行使用這個策略
redo log群組提交優化，而MariaDB的平行複製策略利用的就是這個特性：
在實作上，MariaDB是這麼做的：
1.在一組裡面一起提交的事務，有一個相同的commit_id，下一組就是commit_id 1
2.commit_id直接寫到binlog裡面
3.傳到備庫應用的時候，相同commit_id的事務分發到多個worker執行
4.這一組全部執行完成後，coordinator再去取下一批
下圖中假設三組事務在主庫的執行情況，trx1、trx2和trx3提交的時候， trx4、trx5和trx6是在執行的。這樣，在第一組交易提交完成的時候，下一組交易很快就會進入commit狀態
#按照MariaDB的並行複製策略，備庫上的執行效果如下圖：
在備庫上執行的時候，要等第一組交易完全執行完成後，第二組交易才能開始執行，這樣系統的吞吐量就不夠
另外，這個方案容易被大事務拖後腿。假設trx2是一個超大事務，那麼在備庫應用的時候，trx1和trx3執行完成後，下一組才能開始執行。只有一個worker執行緒在工作，是對資源的浪費
MySQL5.7版本由參數slave-parallel- type來控制並行複製策略：
同時處於執行狀態的所有事務，是不是可以並行？
不可以，因为这里面可能有由于锁冲突而处于锁等待状态的事务。如果这些事务在备库上被分配到不同的worker，就会出现备库跟主库不一致的情况
而MariaDB这个策略的核心是所有处于commit状态的事务可以并行。事务处于commit状态表示已经通过了锁冲突的检验了
其实只要能够达到redo log prepare阶段就表示事务已经通过锁冲突的检验了
因此，MySQL5.7并行复制策略的思想是：
1.同时处于prepare状态的事务，在备库执行时是可以并行的
2.处于prepare状态的事务，与处于commit状态的事务之间，在备库执行时也是可以并行的
binlog组提交的时候有两个参数：
这两个参数是用于故意拉长binlog从write到fsync的时间，以此减少binlog的写盘次数。在MySQL5.7的并行复制策略里，它们可以用来制造更多的同时处于prepare阶段的事务。这样就增加了备库复制的并行度。也就是说，这两个参数既可以故意让主库提交得慢些，又可以让备库执行得快些
MySQL5.7.22增加了一个新的并行复制策略，基于WRITESET的并行复制，新增了一个参数binlog-transaction-dependency-tracking用来控制是否启用这个新策略。这个参数的可选值有以下三种：
为了唯一标识，hash值是通过库名+表名+索引名+值计算出来的。如果一个表上除了有主键索引外，还有其他唯一索引，那么对于每个唯一索引，insert语句对应的writeset就要多增加一个hash值
1.writeset是在主库生成后直接写入到binlog里面的，这样在备库执行的时候不需要解析binlog内容
2.不需要把整个事务的binlog都扫一遍才能决定分发到哪个worker，更省内存
3.由于备库的分发策略不依赖于binlog内容，索引binlog是statement格式也是可以的
对于表上没主键和外键约束的场景，WRITESET策略也是没法并行的，会暂时退化为单线程模型
下图是一个基本的一主多从结构
图中，虚线箭头表示的是主备关系，也就是A和A’互为主备，从库B、C、D指向的是主库A。一主多从的设置，一般用于读写分离，主库负责所有的写入和一部分读，其他的读请求则由从库分担
一主多从结构在切换完成后，A’会成为新的主库，从库B、C、D也要改接到A’
当我们把节点B设置成节点A’的从库的时候，需要执行一条change master命令：
CHANGE MASTER TO MASTER_HOST=$host_name MASTER_PORT=$port MASTER_USER=$user_name MASTER_PASSWORD=$password MASTER_LOG_FILE=$master_log_name MASTER_LOG_POS=$master_log_pos
找同步位点很难精确取到，只能取一个大概位置。一种去同步位点的方法是这样的：
1.等待新主库A’把中转日志全部同步完成
2.在A’上执行show master status命令，得到当前A’上最新的File和Position
3.取原主库A故障的时刻T
4.用mysqlbinlog工具解析A’的File，得到T时刻的位点，这个值就可以作为$master_log_pos
这个值并不精确，有这么一种情况，假设在T这个时刻，主库A已经执行完成了一个insert语句插入了一行数据R，并且已经将binlog传给了A’和B，然后在传完的瞬间主库A的主机就掉电了。那么，这时候系统的状态是这样的：
1.在从库B上，由于同步了binlog，R这一行已经存在
2.在新主库A’上，R这一行也已经存在，日志是写在master_log_pos这个位置之后的
3.在从库B上执行change master命令，指向A’的File文件的master_log_pos位置，就会把插入R这一行数据的binlog又同步到从库B去执行，造成主键冲突，然后停止tongue
通常情况下，切换任务的时候，要先主动跳过这些错误，有两种常用的方法
一种是，主动跳过一个事务
set global sql_slave_skip_counter=1;start slave;
另一种方式是，通过设置slave_skip_errors参数，直接设置跳过指定的错误。这个背景是，我们很清楚在主备切换过程中，直接跳过这些错误是无损的，所以才可以设置slave_skip_errors参数。等到主备间的同步关系建立完成，并稳定执行一段时间之后，还需要把这个参数设置为空，以免之后真的出现了主从数据不一致，也跳过了
MySQL5.6引入了GTID，是一个全局事务ID，是一个事务提交的时候生成的，是这个事务的唯一标识。它的格式是：
GTID=source_id:transaction_id
GTID模式的启动只需要在启动一个MySQL实例的时候，加上参数gtid_mode=on和enforce_gtid_consistency=on就可以了
在GTID模式下，每个事务都会跟一个GTID一一对应。这个GTID有两种生成方式，而使用哪种方式取决于session变量gtid_next的值
1.如果gtid_next=automatic，代表使用默认值。这时，MySQL就把GTID分配给这个事务。记录binlog的时候，先记录一行SET@@SESSION.GTID_NEXT=‘GTID’。把这个GTID加入本实例的GTID集合
2.如果gtid_next是一个指定的GTID的值，比如通过set gtid_next=‘current_gtid’，那么就有两种可能：
一个current_gtid只能给一个事务使用。这个事务提交后，如果要执行下一个事务，就要执行set命令，把gtid_next设置成另外一个gtid或者automatic
这样每个MySQL实例都维护了一个GTID集合，用来对应这个实例执行过的所有事务
在GTID模式下，备库B要设置为新主库A’的从库的语法如下：
CHANGE MASTER TO MASTER_HOST=$host_name MASTER_PORT=$port MASTER_USER=$user_name MASTER_PASSWORD=$password master_auto_position=1
其中master_auto_position=1就表示这个主备关系使用的是GTID协议
实例A’的GTID集合记为set_a，实例B的GTID集合记为set_b。我们在实例B上执行start slave命令，取binlog的逻辑是这样的：
1.实例B指定主库A’，基于主备协议建立连接
2.实例B把set_b发给主库A’
3.实例A’算出set_a与set_b的差集，也就是所有存在于set_a，但是不存在于set_b的GTID的集合，判断A’本地是否包含了这个差集需要的所有binlog事务
4.之后从这个事务开始，往后读文件，按顺序取binlog发给B去执行
如果是由于索引缺失引起的性能问题，可以在线加索引来解决。但是，考虑到要避免新增索引对主库性能造成的影响，可以先在备库加索引，然后再切换，在双M结构下，备库执行的DDL语句也会传给主库，为了避免传回后对主库造成影响，要通过set sql_log_bin=off关掉binlog，但是操作可能会导致数据和日志不一致
两个互为主备关系的库实例X和实例Y，且当前主库是X，并且都打开了GTID模式。这时的主备切换流程可以变成下面这样：
set GTID_NEXT="source_id_of_Y:transaction_id";begin;commit;set gtid_next=automatic;start slave;
这样做的目的在于，既可以让实例Y的更新有binlog记录，同时也可以确保不会在实例X上执行这条更新
读写分离的基本结构如下图：
读写分离的主要目的就是分摊主库的压力。上图中的结构是客户端主动做负载均衡，这种模式下一般会把数据库的连接信息放在客户端的连接层。由客户端来选择后端数据库进行查询
还有一种架构就是在MySQL和客户端之间有一个中间代理层proxy，客户端只连接proxy，由proxy根据请求类型和上下文决定请求的分发路由
1.客户端直连方案，因此少了一层proxy转发，所以查询性能稍微好一点，并且整体架构简单，排查问题更方便。但是这种方案，由于要了解后端部署细节，所以在出现主备切换、库迁移等操作的时候，客户端都会感知到，并且需要调整数据库连接信息。一般采用这样的架构，一定会伴随一个负责管理后端的组件，比如Zookeeper，尽量让业务端只专注于业务逻辑开发
2.带proxy的架构，对客户端比较友好。客户端不需要关注后端细节，连接维护、后端信息维护等工作，都是由proxy完成的。但这样的话，对后端维护团队的要求会更高，而且proxy也需要有高可用架构
在从库上会读到系统的一个过期状态的现象称为过期读
强制走主库方案其实就是将查询请求做分类。通常情况下，可以分为这么两类：
1.对于必须要拿到最新结果的请求，强制将其发到主库上
2.对于可以读到旧数据的请求，才将其发到从库上
这个方案最大的问题在于，有时候可能会遇到所有查询都不能是过期读的需求，比如一些金融类的业务。这样的话，就需要放弃读写分离，所有读写压力都在主库，等同于放弃了扩展性
主库更新后，读从库之前先sleep一下。具体的方案就是，类似于执行一条select sleep(1)命令。这个方案的假设是，大多数情况下主备延迟在1秒之内，做一个sleep可以很大概率拿到最新的数据
以买家发布商品为例，商品发布后，用Ajax直接把客户端输入的内容作为最新商品显示在页面上，而不是真正地去数据库做查询。这样，卖家就可以通过这个显示，来确认产品已经发布成功了。等到卖家再刷新页面，去查看商品的时候，其实已经过了一段时间，也就达到了sleep的目的，进而也就解决了过期读的问题
但这个方案并不精确：
1.如果这个查询请求本来0.5秒就可以在从库上拿到正确结果，也会等1秒
2.如果延迟超过1秒，还是会出现过期读
show slave status结果里的seconds_behind_master参数的值，可以用来衡量主备延迟时间的长短
1.第一种确保主备无延迟的方法是，每次从库执行查询请求前，先判断seconds_behind_master是否已经等于0。如果还不等于0，那就必须等到这个参数变为0才能执行查询请求
show slave status结果的部分截图如下：
2.第二种方法，对比位点确保主备无延迟：
如果Master_Log_File和Read_Master_Log_Pos和Relay_Master_Log_File和Exec_Master_Log_Pos这两组值完全相同，就表示接收到的日志已经同步完成
3.第三种方法，对比GTID集合确保主备无延迟：
如果这两个集合相同，也表示备库接收到的日志都已经同步完成
4.一个事务的binlog在主备库之间的状态：
1）主库执行完成，写入binlog，并反馈给客户端
2）binlog被从主库发送给备库，备库收到
3）在备库执行binlog完成
上面判断主备无延迟的逻辑是备库收到的日志都执行完成了。但是，从binlog在主备之间状态的分析中，有一部分日志，处于客户端已经收到提交确认，而备库还没收到日志的状态
这时，主库上执行完成了三个事务trx1、trx2和trx3，其中：
如果这时候在从库B上执行查询请求，按照上面的逻辑，从库认为已经没有同步延迟，但还是查不到trx3的
要解决上面的问题，就要引入半同步复制。semi-sync做了这样的设计：
1.事务提交的时候，主库把binlog发送给从库
2.从库收到binlog以后，发回给主库一个ack，表示收到了
3.主库收到这个ack以后，才能给客户端返回事务完成的确认
如果启用了semi-sync，就表示所有给客户端发送过确认的事务，都确保了备库已经收到了这个日志
semi-sync+位点判断的方案，只对一主一备的场景是成立的。在一主多从场景中，主库只要等到一个从库的ack，就开始给客户端返回确认。这时，在从库上执行查询请求，就有两种情况：
1.如果查询是落在这个响应了ack的从库上，是能够确保读到最新数据
2.但如果查询落到其他从库上，它们可能还没有收到最新的日志，就会产生过期读的问题
判断同步位点的方案还有另外一个潜在的问题，即：如果在业务更新的高峰期，主库的位点或者GTID集合更新很快，那么上面的两个位点等值判断就会一直不成立，很有可能出现从库上迟迟无法响应查询请求的情况
上图从状态1到状态4，一直处于延迟一个事务的状态。但是，其实客户端是在发完trx1更新后发起的select语句，我们只需要确保trx1已经执行完成就可以执行select语句了。也就是说，如果在状态3执行查询请求，得到的就是预期结果了
semi-sync配合主备无延迟的方案，存在两个问题：
1.一主多从的时候，在某些从库执行查询请求会存在过期读的现象
2.在持续延迟的情况下，可能出现过度等待的问题
select master_pos_wait(file, pos[, timeout]);
这条命令的逻辑如下：
1.它是在从库执行的
2.参数file和pos指的是主库上的文件名和位置
3.timeout可选，设置为正整数N表示这个函数最多等待N秒
这个命令正常返回的结果是一个正整数M，表示从命令开始执行，到应用完file和pos表示的binlog位置，执行了多少事务
1.如果执行期间，备库同步线程发生异常，则返回NULL
2.如果等待超过N秒，就返回-1
3.如果刚开始执行的时候，就发现已经执行过这个位置了，则返回0
对于上图中先执行trx1，再执行一个查询请求的逻辑，要保证能够查到正确的数据，可以使用这个逻辑：
1.trx1事务更新完成后，马上执行show master status得到当前主库执行到的File和Position
2.选定一个从库执行查询语句
3.在从库上执行select master_pos_wait(file, pos, 1)
4.如果返回值是>=0的正整数，则在这个从库执行查询语句
5.否则，到主库执行查询语句
流程如下：
select wait_for_executed_gtid_set(gtid_set, 1);
这条命令的逻辑如下：
1.等待，直到这个库执行的事务中包含传入的gtid_set，返回0
2.超时返回1
等主库位点方案中，执行完事务后，还要主动去主库执行show master status。而MySQL5.7.6版本开始，允许在执行完更新类事务后，把这个事务的GTID返回给客户端，这样等GTID的方案可以减少一次查询
等GTID的流程如下：
1.trx1事务更新完成后，从返回包直接获取这个事务的GTID，记为gtid1
2.选定一个从库执行查询语句
3.在從庫上執行select wait_for_executed_gtid_set(gtid1, 1);
4.如果傳回值是0，則在這個從庫執行查詢語句
5.否則，到主庫執行查詢語句
更多程式設計相關知識，請造訪：程式設計入門！ ！
以上是詳細了解MySQL中的主備、主從和讀寫分離的詳細內容。更多資訊請關注PHP中文網其他相關文章！
