mysql主从模式原理与配置
主从模式原理
1.1 概念：MySQL主从复制是指数据可以从⼀个MySQL数据库服务器主节点复制到⼀个或多个从节点。MySQL 默认采⽤异步复制⽅式，这样从节点不⽤⼀直访问主服务器来更新⾃⼰的数据，数据的更新可以在远程连接上进⾏，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。
1.2 主要⽤途：
1.在开发⼯作中，有时候会遇见某个sql 语句需要锁表，导致暂时不能使⽤读的服务，这样就会影响现有业务，使⽤主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
2.数据实时备份，当系统中某个节点发⽣故障时，可以⽅便的故障切换(主从切换)
3.⾼可⽤（HA）
4.随着系统中业务访问量的增⼤，如果是单机部署数据库，就会导致I/O访问频率过⾼。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘I/O访问的频率，提⾼单个机器的I/O性能。
1.3 MySQL主从形式
1.⼀主多从（实现HA，读写分离，提供集的并发能⼒）
2.多主⼀从（从库主要⽤于数据库备份作⽤）
3.双主复制
4.级联复制（级联复制模式下，部分slave的数据同步不连接主节点，⽽是连接从节点。因为如果主节点有太多的从节点，就会损耗⼀部分性能⽤于replication（复制），那么我们可以让3~5个从节点连接主节点，其它从节点作为⼆级或者三级与从节点连接，这样不仅可以缓解主节点的压⼒，并且对数据⼀致性没有负⾯影响。级联复制下从节点也要开启binary log（bin-log）功能）
1.4 MySQL主从复制的原理（重点）
MySQL主从复制涉及到三个线程，⼀个运⾏在主节点（log dump thread），其余两个（I/O thread, SQL thread）运⾏在从节点，如下图所⽰：
1. 主节点log dump线程
当从节点连接主节点时，主节点会为其创建⼀个log dump 线程，⽤于发送和读取bin-log的内容。在读取bin-log中的操作时，log dump线程会对主节点上的bin-log加锁，当读取完成，在发送给从节点之前，锁会被释放。主节点会为⾃⼰的每⼀个从节点创建⼀个log dump 线程。
2.从节点I/O线程
当从节点上执⾏start slave命令之后，从节点会创建⼀个I/O线程⽤来连接主节点，请求主库中更新的bin-log。I/O线程接收到主节点的blog dump进程发来的更新之后，保存在本地relay-log（中继⽇志）中。
3.从节点SQL线程
SQL线程负责读取relay-log中的内容，解析成具体的操作并执⾏，最终保证主从数据的⼀致性。
对于每⼀个主从连接，都需要这三个进程来完成。当主节点有多个从节点时，主节点会为每⼀个当前连接的从节点建⼀个log dump 进程，⽽每个从节点都有⾃⼰的I/O进程，SQL进程。从节点⽤两个线程将从主库拉取更新和执⾏分成独⽴的任务，这样在执⾏同步数据任务的时候，不会降低读操作的性能。⽐如，如果从节点没有运⾏，此时I/O进程可以很快从主节点获取更新，尽管SQL进程还没有执⾏。
如果在SQL进程执⾏之前从节点服务停⽌，⾄少I/O进程已经从主节点拉取
到了最新的变更并且保存在本地relay⽇志中，当服务再次起来之后，就可以完成数据的同步。
要实施复制，⾸先必须打开Master 端的binary log（bin-log）功能，否则⽆法实现。
因为整个复制过程实际上就是Slave 从Master 端获取该⽇志然后再在⾃⼰⾝上完全顺序的执⾏⽇志中所记录的各种操作。如下图所⽰：
1.5 MySQL主从复制的模式（理解）
MySQL 主从复制默认是异步的模式。MySQL增删改操作会全部记录在bin-log（binary log）中，当slave节点连接master时，会主动从master处获取最新的bin-log⽂件。并把bin-log存储到本地的relay-log中，然后去执⾏relay-log的更新内容。
主要有三种模式：
1. 异步模式
异步模式如下图所⽰，这种模式下，主节点不会主动推送bin-log到从节点，主库在执⾏完客户端提交的事务后会⽴即将结果返给给客户端，并不关⼼从库是否已经接收并处理，这样就会有⼀个问题，主节点如果崩溃掉了，此时主节点上已经提交的事务可能并没有传到从节点上，如果此时，强⾏将从提升为主，可能导致新主节点上的数据不完整。
2.半同步模式(mysql semi-sync)
介于异步复制和全同步复制之间，主库在执⾏完客户端提交的事务后不是⽴刻返回给客户端，⽽是等待⾄少⼀个从库接收到并写到relay-log中才返回成功信息给客户端（只能保证主库的bin-log⾄少传输到了⼀个从节点上，但并不能保证从节点将此事务执⾏更新到db中），否则需要等待直到超时时间然后切换成异步模式再提交。相对于异步复制，半同步复制提⾼了数据的安全性，⼀定程度的保证了数据能成功备份到从库，同时它也造成了⼀定程度的延迟，但是⽐全同步模式延迟要低，这个延迟最少是⼀个TCP/IP往返的时间。所以，半同步复制最好在低延时的⽹络中使⽤。如下图所⽰：
3.全同步模式
指当主库执⾏完⼀个事务，然后所有的从库都复制了该事务并成功执⾏完才返回成功信息给客户端。因为需要等待所有从库执⾏完该事务才能返回成功信息，所以全同步复制的性能必然会收到严重的影响。
1.6 GTID复制模式
在传统的复制⾥⾯，当发⽣故障，需要主从切换，需要到bin-log和pos点（指从库更新到了主库bin-log的哪个位置，这个位置之前都已经更显完毕，这个位置之后未更新），然后将主节点指向新的主节点，相对来说⽐较⿇烦，也容易出错。在MySQL 5.6⾥⾯，不⽤再bin-log和pos点，我们只需要知道主节点的ip，端⼝，以及账号密码就⾏，因为复制是⾃动的，MySQL会通过内部机制GTID⾃动点同步。
基于GTID的复制是MySQL 5.6后新增的复制⽅式.
GTID (global transaction identifier) 即全局事务ID, 保证了在每个在主库上提交的事务在集中有⼀个唯⼀的ID.
1.7 MySQL主从复制的⽅式(重点)
MySQL主从复制有三种⽅式：基于SQL语句的复制(statement-based replication, SBR) , 基于⾏的复制(row-based replication, RBR) , 混合模式复制(mixed-based replication, MBR)。对应的bin-log⽂件的格式也有三种：STATEMENT, ROW , MIXED
Statement-base Replication (SBR)
就是记录sql语句在bin-log中，MySQL 5.1.4及之前的版本都是使⽤这种复制格式。优点就是只需要记录会修改数据的sql语句到bin-log中，减少了bin-log⽇质量，节约I/O，提⾼性能。缺点是在某些情况下，会导致主从节点中数据不⼀致(⽐如sleep(),now()或⾃定义函数存储过程没同步，导致执⾏失败等)
Row-based Relication(RBR)
mysql master将SQL语句分解为基于Row更改的语句并记录在bin-log中，也就是只记录哪条数据被修改了，修改成什么样。优点是不会出现某些特定情况下的存储过程、或者函数、或者trigger的调⽤或者触发⽆法被正确复制的问题。缺点是会产⽣⼤量的⽇志，尤其是修改table的时候会让⽇志暴增，同时增加bin-log同步时间。也不能通过bin-log解析获取执⾏过的sql语句，只能看到发⽣的data变更。
Mixed-format Replication(MBR)
MySQL NDB cluster 7.3和7.4使⽤的MBR。是以上两种模式的混合，对于⼀般的复制使⽤STATEMEN
T模式保存到bin-log，对于STATEMENT模式⽆法复制的操作则使⽤ROW模式来保存，MySQL会根据执⾏的SQL语句选择⽇志保存⽅式。
配置主从模式
2.1 docker下mysql创建
docker run -d name mysql_master -p  3306:3306 -e MYSQL_ROOT_PASSWORD=123456 --network bridge --ip 172.17.0.2 mysql:5.7
docker run -d name mysql_slave -p  3307:3306 -e MYSQL_ROOT_PASSWORD=123456 --network bridge --ip 172.17.0.3 mysql:5.7
这⾥如果第⼀步ip没有指定，docker容器每次启动ip会⾃动变化，可以另外添加另⼀个⽹络环境
docker network create --subnet 172.18.0.0/16 my-net
docker network connect --ip 172.18.0.2 my-net mysql_master
docker network connect --ip 172.18.0.3 my-net mysql_slave
如果两个mysql不是新创建的或者数据不⼀致，注意先⽤数据库同步⼯具先同步好两者数据结构、数据⼀致，再执⾏主从配置；
2.2 创建账号和开启binlog
// 进⼊master容器
docker exec -it mysql_master /bin/bash
// 登录mysql
mysl -u root -p
// 创建账号
GRANT REPLICATION SLAVE ON *.* to 'slave'@'172.18.0.%' identified by '123456';
flush privileges;
修改mysqld配置，注意docker 5.7下配置路径为：/etc/f.d/mysqldf
[mysqld]# 开启binlog
log-bin=mysql-bin
server-id=1
binlog_format=mixed
# 需要同步的数据库，如果不配置则同步全部数据库
binlog-do-db=db_test
# binlog⽇志保留的天数，清除超过10天的⽇志
# 防⽌⽇志⽂件过⼤，导致磁盘空间不⾜
expire-logs-days=10
# 事务提交过程中的数据同步模式控制
innodb_flush_log_at_trx_commit=1
配置完成后，重启mysql
docker restart mysql_master
重新进⼊mysql中，查看当前binlog⽂件状态(后⾯slave配置会⽤到File、Position参数)
mysql> flush logs;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |      154 |              |                  |                  |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
2.3 Slave配置binlog
修改mysqld配置，注意docker 5.7下配置路径为：/etc/f.d/mysqldf
[mysqld]
server-id=2
log-bin=mysql-bin
innodb_flush_log_at_trx_commit=1
sync_binlog=1
小程序java课程设计进⼊mysql，配置(注意步骤2.2中到File、Position参数)：
change master to master_host='172.18.0.2',master_user='slave',master_password='123456',master_port=3306,master_log_file='mysql-bin.000005',master_log_pos=154,master_connect_retry=30; // 启动slave服务
start slave;
// 查看slave状态：
show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.18.0.2
Master_User: slave
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 491
Relay_Log_File: 1e73c1ca86e3-relay-bin.000002
Relay_Log_Pos: 657
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:tcl中foreach跳出本次循环
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 491
Relay_Log_Space: 871
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
spark教程Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
mysql无法连接到服务器Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:mysql面试查询语句
Last_SQL_Errno: 0
Last_SQL_Error:
grilledReplicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 84868f03-4366-11ec-80e7-0242ac110007
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
Slave_IO_Running、Slave_SQL_Running为Yes，Last_IO_Error没有报错，说明主从配置成功了，之后可以主mysql实现创建⼀个表并插⼊⼀条数据，看看slave数据库是否同步了；
参考资料：【MySQL】

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。