主从复制
1. 主从复制概述
1.1 如何提升数据库的并发能力
在实际工作中，我们常常将 redis 作为缓冲与 MySQL 配合来使用，当有请求的时候，首先会从缓存中进行查找，如果存在就直接取出。如果不存在再访问数据库，这样就 提升了读取的效率，也减少了堆后端数据库的 访问压力。redis 的缓存架构是 高并发架构 中非常重要的一环。
此外，一般应用对数据库而言都是 "读多写少"，也就是说对数据库读取数据的压力比较大，有一个思路就是采用数据库集群的方案，做 主从架构，进行 读写分离，这样同样可以可以提升数据库的并发处理能力。但并不是所有的应用都需要对数据库进行主从架构的设置，毕竟设置架构本身是有成本的。
如果我们的目的在于 升数据库高并发访问的效率，那么首先考虑的是如何 优化SQL和索引，这种方式简单有效，其次才是采用 缓存的策略，比如使用 redis 将热点数据保存在内存数据库中，提升读取的效率，最后才是对数据库采用 主从架构，进行 读写分离。
按照上面的方式进行优化，使用和维护的成本是由低到高的。
1.2 主从复制的作用
主从同步/复制 不仅可以提高数据库的吞吐量（提高并发能力），还有以下 3 个方面的作用。
- 第 1 个作用：读写分离。我们可以通过 主从复制 的方式来 同步数据，然后通过 读写分离 提高数据库并发处理能力。
其中一个是 master 主库，负责写入数据，我们称之为：写库。
其他都是 slave 从库，负责读取数据，我们称之为：读库。
当主（master ）库进行更新的时候，会自动将数据复制到从（slave）库中，而我们在客户端读取数据的时候，会从从（slave）库中进行读取。
面对 "读多写少" 的需求，采用 读写分离 的方式，可以实现 更高的并发访问。同时，我们还能对从（slave）服务器进行 负载均衡，让不同的读请求按照策略均匀的分发到不同的从（slave）服务器上，让 读取更加顺畅。读取顺畅的另一个原因，就是 减少了锁 的影响，当主（master ）库出现写锁（X锁-排他锁）的时候，不会影响到从（slave）库进行 select 的读取。
- 第 2 个作用：数据备份 。我们通过 主从复制 将主（master）库上的数据复制到了从（slave）库上，相当于是一种 热备份机制，也就是在主（master）库正常运行的情况下进行的备份，不会影响到服务。
- 第 3 个作用：高可用性 。数据备份实际上是一种冗余的机制，通过这种冗余的方式可以换取数据库的 高可用性，也就是当服务器出现 故障 或 宕机 的情况下，可以 切换 到从（slave）服务器上，保证服务的正常运行。
关于 高可用性 的程度，我们可以用一个指标衡量（ 正常时间 / 全年时间）。
例如：要达到全年 99.999% 的时间都可用，那就意味着系统在一年中不可用的时间不得超过 （365 * 24 * 60 * (1-99.999%) = 5.256）分钟（含系统崩溃的时间，日常维护操作导致的停机时间等..），其他时间都必须保持可用的状态。
实际上，越高的 高可用性，也就意味着需要付出更高的成本代价（我们需要结合业务需求和成本来进行选择）。
2 . 主从复制的原理
从（slave）服务器 读取 主（master）服务器 的 bin log 文件来进行数据同步！
2.1 原理剖析 - 三个线程
主要的三个线程
实际上 主从同步 的原理就是基于 bin log 进行数据同步的。在主从复制的过程中，会基于 三个线程 来操纵（一个主（master）库线程，两个从（slave）库线程）。
- bin log（二进制日志）转储线程：是一个主库线程。当从库线程连接的时候，主库可以将 bin log（二进制日志）的更新部分发送给从库。注意的是：当主库读取事件（event）的时候，会对 bin log 上 加锁，读取完成之后，再将锁释放掉。
- 从库 I/O 线程：会连接到主库，向主库发送更新 bin log （二进制日志）的请求。这时 从库的 I/O 线程 就可以读取到 主库的 bin log（二进制日志）转储线程 发送的 bin log（二进制日志），并且拷贝到本地（从库服务器）的 中继日志（relay log）中。
- 从库 SQL 线程：会读取从库中的 中继日志（relay log），并且执行日志（中继日志）中的事件（event），实现从库与主库之间数据同步。
注意：
不是所有版本的 MySQL 都会默认开启 bin log（二进制日志）。在进行 主从复制 的时候，必须要先检查主库服务器是否已经开启了 bin log（二进制日志）。
除非特殊指定，默认情况下从库服务器会执行主库服务器中所有保存的事件。也可以通过配置，使从库服务器执行特定的事件。
复制三步骤
步骤1：主库将写操作记录到 bin log（二进制日志）中、这些记录叫做 二进制日志事件。
步骤2：从库将主库的 二进制日志事件 拷贝到它的 中继日志（relay log）。
步骤3：从库读取 中继日志（relay log） 中的事件，将改变写入到自己的数据库中。MySQL 复制是 异步的且串行化 的，而且重启后会从 接入点 开始复制。
复制的最大问题：延迟。
2.2 复制的基本原则
- 每个 从（slave）库 只有一个 主（master）库
- 每个 从（slave）库 只能有一个唯一的服务器 id
- 每个 主（master）库 可以有多个 从（slave）库
3. 一主一从架构的搭建
一台 主库服务器 用于处理所有的 写请求，一台 从库服务器 负责所有的 读请求。
3.1 准备工作
- 准备 2台 数据库服务器。
- 每台服务器上的 数据库的版本必须要是大版本相同（要是 8.0.xx 都是 8.0.xx，不能是 5.7.xx）。
注意：如果使用的是克隆虚拟机的方式的话，必须修改克隆后服务器的以下配置：
- MAC 地址
- 主机名（hostname）
- ip 地址
- 服务器的 UUID
- MySQL Server 的 UUID
说明：
此外，克隆的方式生成的虚拟机（包括 MySQL Server），则克隆的虚拟机 MySQL Server 的 UUID 和 主机 相同，必须修改，否则在有些场景下会报错。
比如：执行以下语句时
show slave status;
修改 MySQL Server 的 UUID 的方式：
- 打开文件修改（auto.cnf 默认在 mysql 的数据目录下）
vim /var/lib/mysql/auto.cnf
- 重启 mysql
systemctl restart mysqld # 或者 service mysqld restart
3.2 主库的配置文件
建议 mysql 版本一致且后台以服务运行，主（master）从（slave）所有配置项都配置在 my.cnf / my.ini 的 [mysqld] 节点下，且都是小写字母。
具体参数配置如下：
- 必选
# [必须] 主服务器唯一 id
server-id=1
# [必须] 启用二进制日志，表示使用 mysql-bin 替换默认的 bin log 文件名称。也可以指明路径（比如：/log/mysqlbin/mysql-bin）。不指定默认在数据目录下
log-bin=mysql-bin
- 可选
# [可选]（默认：0 表示读写-主库），1 表示只读-从库
read-only=0
# 设置 bin log 文件保留的时长，单位：秒
#binlog_expire_logs_seconds=6000000
# 控制单个 bin log 文件日志的空间大小，默认：1GB
#max_binlog_size=1024M
# [可选] 设置不要复制的数据库（可设置多个）
#binlog-ignore-db=test
#binlog-ignore-db=test1
#....
# [可选] 设置需要复制的数据库，默认:读取全部记录
#binlog-do-db=xld（需要复制的主数据库名称）（可设置多个）
#....
# [可选] 设置 bin log 的格式，默认：row 格式
#binlog_format=STATEMENT
重启后台 mysql 服务，使配置生效。
注意：
- 先搭建完 "主从复制"，再创建数据库。
- MySQL "主从复制" 起始时，从机不继承主机数据。
3.2.1 bin log（二进制日志）格式设置 - 重点
格式1：STATEMENT（基于 SQL 语句的复制 - statement-based replication，SBR）
binlog_format=STATEMENT
每一条会修改数据的 sql 语句都会记录到 bin log 中（bin log 中记录的是一条条的 SQL 语句）。
- SBR 的优点：
- 历史悠久，技术成熟。
- 不需要记录每一行的变化，减少了 bin log 的日志量，文件较小。
- bin log 中包含了所有数据库更改信息，可以据此来审核数据库的安全等情况。
- bin log 可以用于实时的还原，而不仅仅用于复制。
- 主从版本可以不一样，从（slave）库的版本可以比主（master）库的版本高。
- SBR 的缺点：
- 不是所有的 update 语句都能被复制，尤其是包含不确定的操作的时候。
- 使用以下函数的语句也无法被复制：load_file(filename)，uuid()，user()，found_rows()，sysdate()，now()（除非启动时启用了 --sysdate-is-now 选项），@@hostname
- insert ... select 会产生比 RBR 更多的 行级锁。
- 复制需要进行 全表扫描（where 语句中没有使用到索引）的 update 时，需要比 RBR 请求/使用更多的 行级锁。
- 对于有 auto_increment 字段的 InnoDB 表而言，insert 语句会阻塞其他 insert 语句。
- 对于一些复杂的语句，在从（slave）库服务器上的资源消耗情况会更加严重，而 RBR 模式下，只会对发生变化的记录产生影响。
- 执行复制语句如果出错的话，会消耗更多的资源。
- 从（slave）库的数据表必须和主（master）库的数据表完全保持一致 才行，否则就会导致复制出错。
格式2：ROW（基于行的复制 - row-based replication，RBR）
binlog_format=ROW
5.1.5 之后的 MySQL 才开始支持，不记录每条 sql 语句的上下文信息，仅记录哪条数据被修改了，最终修改成的什么了。
- RBR 的优点：
- 任何情况都可以被复制，这对复制来说是最 安全可靠 的（比如：不会出现某些特定情况下的 存储过程，存储函数以及触发器 的调用和触发无法被正确复制的问题）。
- 多数情况下，从（slave）库上的表如果有主键的话，复制就会快很多。
- 复制以下几种语句时行级锁较少：insert ... select，包含 auto_increment 字段的 insert，没有条件或者并没有修改很多记录的 update 和 delete 语句。（对于复制批量更新数据的时，行级锁会很少）
- 执行 insert，update，delete 语句时相比 SBR 锁更少。
- 可以在从（slave）库服务器上采用 多线程 来执行复制。
- RBR 的缺点：
- 记录 bin log 太多了，日志文件太大 了。
- 在 执行复杂的回滚时 bin log 中会包含大量的记录。
- 主（master）库上执行 update 语句时，所有发生变化的记录都会写到 bin log 中，而 SBR 只会写一次，这会导致频繁发生 bin log 的 并发写问题。
- 无法从 bin log 中看到具体的操作（看不到具体的 SQL 语句）。
格式3：MIXED（混合模式复制 mixed-based replication，MBR）
binlog_format=MIXED
从 5.1.8 版本开始，MySQL 提供了 Mixed 格式，实际上就是 Statement 与 Row 的 结合模式。
在 Mixed 格式下，一般的语句修改使用 Statement 格式保存 bin log。如一些函数，Statement 无法完成复制的操作，则采用 Row 格式保存 bin log。
MySQL 会根据执行的每一条具体的 sql 语句来区分对待记录的日志形式，也就是在 Statement 和 Row 之间选择一种。
3.3 从库的配置文件
要求主（master）从（slave）所有配置项都配置在 my.cnf / my.ini 的 [mysqld] 节点下，且都是小写字母。
- 必选
# [必须] 从服务器唯一id
server-id=2
- 可选
# [可选] 启用中继日志
relay-log=mysql-relay
重启后台 mysql 服务，使配置生效。
注意：主从机都关闭防火墙
- CentOS 6：service iptables stop
- CentOS 7：systemctl stop firewalld.service
3.4 主库：建立账户并授权
- mysql 5.xx 版本：主库授权主从复制的命令（同时创建了用户）
GRANT replication SLAVE ON *.* TO 'slave1'@'从库的ip地址' IDENTIFIED by 'xld123';
- msyql 8.xx 版本：主库授权主从复制的命令
# 创建用户
CREATE USER 'slave1'@'%' IDENTIFIED BY 'xld123';
# 为 slave1 用户授权
GRANT replication SLAVE ON *.* TO 'slave1'@'%';
# 1
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave1'@'%';
# 刷新权限
FLUSH PRIVILEGES;
注意：由于 mysql 8.x 使用新的密码安全机制，可能出现以下问题。
解决办法：
# 修改密码 ALTER USER 'slave1'@'%' IDENTIFIED BY WITH mysql_native_password BY 'xld123';# 刷新权限 FLUSH PRIVILEGES;
查询 master 的状态，并记录下 file 和 position 值
show master status
- file：mysql-bin.000001
- position：831
注意：执行完此步骤后 不要再操作主库 MySQL，防止主库状态值发生变化。
3.5 从库：配置需要复制的主库
步骤1：从库上复制主库的命令
CHANGE MASTER TO
master_host = '主库的ip地址',
master_user = '主库用户名',
master_port = 主库的端口号,
master_password = '主库用户的密码',
master_log_file = '主库master状态中的 file 文件',
master_log_pos = 主库master状态中的 position 值,
master_connect_trt='尝试重连的时间间隔（秒）';
例如：
CHANGE MASTER TO master_host = '172.16.2.215',
master_port = 3306,
master_user = 'slave1',
master_password = 'xld123',
master_log_file = 'mysql-bin.000001',
master_log_pos = 831;
可能出现的问题：
问题原因：主从复制正在启动中，无法重新设置从库复制主库的命令。
解决方式：停止主从复制，然后再重新配置。
stop slave;
步骤2：启动 主从复制/同步
start slave;
可能出现的问题：
问题原因：之前曾经开启过 主从复制，从库已经存在 中继日志。
解决方式：删除之前的 中继日志（relay log），然后重新执行 从库复制主库（change master to ...） 的语句即可。
# 删除 从（slave）库中的 中继日志（relay log）文件，并重新启用新的 relay log 文件 reset slave;
步骤3：查看从库的同步状态
show slave status;
上面两个参数的值都是 yes，则表示主从复制配置成功！
- Slave_IO_Running：Yes
- Slave_SQL_Running：Yes
个人推测：这个两个字段应该就代表这从库上的两个线程（IO 线程 和 SQL 线程）。
- 如果显示以下情况，就是不正确的。可能错误的原因有：
- 网络不通
- 账户密码错误
- 防火墙
- mysql 配置文件问题
- 连接服务器时语法不对
- 主库 mysql 权限问题
- 扩展 - 参数详解：
Slave_IO_State: Waiting for source to send event # 当前slave I/O线程的状态(slave连接到master的状态) Master_Host: 10.6.8.238 Master_User: root Master_Port: 13306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 346 Relay_Log_File: e7d8eacb8d39-relay-bin.000003 Relay_Log_Pos: 324 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes 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 Exec_Master_Log_Pos: 346 Relay_Log_Space: 898 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 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: 1 Master_UUID: 1b3f1e77-4ebe-11ed-87d9-0242ac110003 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica 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: Master_public_key_path: Get_master_public_key: 0 Network_Namespace:
- Slave_IO_State：当前slave I/O线程的状态(slave连接到master的状态)。
- waiting for master update：这是 connecting to master 状态之前的状态。
- connecting to master：I/O 线程正尝试连接到 master。
- checking master version：在与master建立连接后，会出现该状态。该状态出现的时间非常短暂。
- registering slave on master：在与master建立连接后，会出现该状态。该状态出现的时间非常短暂。
- requesting binlog dump：在与master建立连接后，会出现该状态。该状态出现的时间非常短暂。在这个状态下，I/O线程向master发送请求，请求binlog，位置从指定的binglog 名字和binglog的position位置开始。
- waiting to reconnect after a failed binlog dump request：如果因为连接断开，导致binglog的请求失败，I/O线程会进入睡眠状态。然后定期尝试重连。尝试重连的时间间隔，可以使用命令 "" 改变。
- reconnecting after a failed binglog dump request：I/O进程正在尝试连接master。
- waiting for master to send event：说明，已经成功连接到master，正等待二进制日志时间的到达。如果master 空闲，这个状态会持续很长时间。如果等待的时间超过了slave_net_timeout(单位是秒)的值，会出现连接超时。在这种状态下，I/O线程会人为连接失败，并开始尝试重连。
- queueing master event to the relay log：此时，I/O线程已经读取了一个event，并复制到了relay log 中。这样SQL 线程可以执行此event
- waiting to reconnect after a failed master event read：读取时出现的错误(因为连接断开)。在尝试重连之前，I/O线程进入sleep状态，sleep的时间是master_connect_try的值(默认是60秒)
- reconnecting after a failed master event read：I/O线程正尝试重连master。如果连接建立，状态会变成"waiting for master to send event"
- waiting for the slave sql thread to free enough relay log space：这是因为设置了relay_log_space_limit，并且relay log的大小已经整张到了最大值。I/O线程正在等待SQL线程通过删除一些relay log，来释放relay log的空间。
- waiting for slave mutex on exit：I/O线程停止时会出现的状态，出现的时间非常短。
状态信息和使用 show processlist | grep "system user" (会显示两条信息，一条slave I/O线程的，一条是 slave SQL线程的)显示的内容一样!
- Master_Host：主库的 ip 地址。
- Master_Port：主库的用户，用来负责【主从复制】的用户，创建主从复制的时候建立的（具有 reolication slave权限）。
- Connect_Retry：连接中断后，重新尝试连接的时间间隔（默认值是60秒）。
- Master_Log_File：【从库】当前 I/O 线程正在读取的【主库】二进制日志文件的名称。
- Read_Master_Log_Pos：【从库】当前I/O线程正在读取的【主库】二进制日志的位置。
- Relay_Log_File: 当前【从库】SQL线程正在读取并执行的 relay log 的文件名。
- Relay_Log_Pos 当前【从库】SQL线程正在读取并执行的 relay log 文件中的位置。
- Relay_Master_Log_File: 当前【从库】SQL线程读取并执行的 relay log 的文件中多数近期事件，对应【主库】的二进制日志文件的名称。
- Slave_IO_Running: 【从库】I/O线程是否被启动并成功地连接到【主库】上。
- Slave_SQL_Running: 【从库】SQL线程是否被启动。
- Replicate_Do_DB：复制的数据库。
- Replicate_Ignore_DB：复制时忽略的数据库。
- Replicate_Do_Table：复制的表。
- Replicate_Ignore_Table：复制时忽略的表。
- Replicate_Wild_Do_Table：复制的表。
- Replicate_Wild_Ignore_Table：复制时忽略的表。
这些参数都是为了用来指明哪些库或表在复制的时候不要同步到【从库】，但是这些参数用的时候要小心，因为当跨库使用的时候可能会出现问题。一般情况下 ，限制的时候都用 Replicate_Wild_Ignore_Table 这个参数。
- Last_Errno：【从库】的SQL线程读取日志参数的的 "错误数量"。
- Last_Error：【从库】的SQL线程读取日志参数的的 "错误消息"。错误数量为0并且消息为空字符串表示没有错误！
- Skip_Counter：SQL_SLAVE_SKIP_COUNTER 的值，用于设置跳过 sql 执行步数。
- Exec_Master_Log_Pos: 【从库】SQL线程当前执行的事件，对应在master相应的二进制日志中的position。（在 Relay_Master_Log_File 这个值等于 Master_Log_File 值的时候，Exec_Master_Log_Pos 是不可能超过 Read_Master_Log_Pos的）
- Relay_Log_Space：所有原有的中继日志结合起来的总大小。
- Until_Condition：None
- 如果没有指定 Until 子句，则没有值。
- 如果从属服务器正在读取，直到达到主服务器的二进制日志的给定位置为止，则值为 Master。
- 如果从属服务器正在读取，直到达到其中继日志的给定位置为止，则值为 Relay。
- Until_Log_File：用于指示日志文件名。
- Until_Log_Pos：用于指示日志位置值。日志文件名和位置值定义了SQL线程在哪个点中止执行！
在 start slave 语句的 Until 子句中指定的值。
- Master_SSL_CA_File：
- Master_SSL_CA_Path：
- Master_SSL_Cert：
- Master_SSL_Cipher：
- Master_SSL_Key：
- Master_SSL_Verify_Server_Cert：No
- Master_SSL_Crl：
- Master_SSL_Crlpath：
这些字段显示了被从属服务器使用加密相关的参数，这些参数用于连接主服务器。
- Master_SSL_Allowed具有以下值：
- 如果允许对主服务器进行SSL连接，则值为Yes
- 如果不允许对主服务器进行SSL连接，则值为No
- 如果允许SSL连接，但是从属服务器没有让SSL支持被启用，则值为Ignored。
- Seconds_Behind_Master：是【从库】当前的时间戳和【主库】记录该事件时的时间戳的差值。
- Last_IO_Errno：最后一次 I/O线程的错误号。
- Last_IO_Error：最后一次 I/O线程错误消息。
- Last_SQL_Errno：最后一次 SQL线程的错误号。
- Last_SQL_Error：最后一次 SQL线程的错误消息。
- Replicate_Ignore_Server_Ids：主从复制，【从库】忽略的【主库】服务器Id号（就是不以这些服务器Id为主库）。
- Master_Server_Id：【主库】服务器 id 号。
- Master_UUID：【主库】服务器 uuid 号。
- Master_Info_File：【从库】中保存【主库】服务器相关的目录位置。
- SQL_Delay：【从库】滞后多少秒于【主库】。
- SQL_Remaining_Delay：当 Slave_SQL_Running_State 等待，直到 SQL_Delay 秒后，Master 执行的事件，此字段包含一个整数，表示有多少秒左右的延迟。在其他时候，这个字段是NULL。
- Slave_SQL_Running_State：SQL线程运行状态。
- Reading event from the relay log：线程已经从中继日志读取一个事件，可以对事件进行处理了。
- Has read all relay log; waiting for the slave I/O thread to update it：线程已经处理了中继日志文件中的所有事件，现在正等待I/O线程将新事件写入中继日志。
- Waiting for slave mutex on exit：线程停止时发生的一个很简单的状态。
- Master_Retry_Count：连接主库失败最多的重试次数。
- Master_Bind：【从库】在多网络接口的情况下使用，以确定用哪一个【从库】网络接口连接到【主库】。
- Last_IO_Error_Timestamp：最后一次 I/O线程错误时的时间戳。
- Last_SQL_Error_Timestamp：最后一次 SQL线程错误时的时间戳。
- Retrieved_Gtid_Set：获取到的GTID<IO线程>。
- Executed_Gtid_Set：执行过的GTID<SQL线程>。
- Auto_Position：
- 为什么执行stop slave; 再start slave;可以继续主从关系呢？
- 其实执行stop slave;就是分别关闭了I/O线程（stop slave IO_THREAD;）和SQL线程（stop slave SQL_THREAD;），I/O线程会维护 master.info 信息的更新，SQL 线程会维护 relay-log.info 信息的更新，在执行 start slave; 时候，会依照 master.info 和 relay-log.info 信息，继续执行 I/O线程和 SQL线程。
- master_log_file 对应 Master_Log_File 值，master_log_pos 对应 Read_Master_Log_Pos 值！
3.6 测试
- 在主（master）库中创建一个数据库进行测试
# 创建一个数据库 xld
create database if not exists xld default charset=utf8mb4 COLLATE utf8mb4_general_ci;
# 切换到 xld 中
use xld;
# 查看当前所在的库
select database();
# 查看数据库中的表
show tables;
# 创建一张表
create table if not exists xld_master_slave_text(
id int PRIMARY KEY comment '主键id',
age tinyint default 0 comment '年龄',
xld_name varchar(25) not null comment '名称',
index idx_age(age)
)ENGINE= INNODB default charset=utf8mb4;
# 给表添加三条数据
insert into xld_master_slave_text values (1,0,'xld1');
insert into xld_master_slave_text values (2,2,'xld2');
insert into xld_master_slave_text values (3,3,'xld3');
- 连接从（slave）库进行查看数据是否同步成功
# 查看所有的库
show databases;
# 切换数据库
use xld;
# 查看当前所在的库
select database();
# 查看该库所有的表
show tables;
# 查询表（xld_master_slave_text）中的数据
select * from xld_master_slave_text;
至此，主库与从库之间的数据同步成功！
3.7 停止 - 主从复制/同步
- 停止 - 主从复制/同步：
stop slave;
- 如何重新配置主从关系
如果停止从库的复制功能，再启动使用时（主从复制），需要重新配置主从关系。否则会报以下错误：
重新配置主从，需要在从库上执行（以下操作过去暴力不得已不的使用）：
# 先停止主从复制
stop slave;
# 删除 主（master）库中所有的 bin log 文件，并将日志索引文件清空，重新开始所有新的日志文件（慎用！慎用！慎用！）
reset master;
功能说明：删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件。用于第一次进行搭建主从库时，进行主库binlog初始化工作（清空/重置主机环境）；
注意：reset master 不同于 purge binary log 的两处地方
- reset master 将删除日志索引文件中记录的所有binlog文件，创建一个新的日志文件 起始值从000001 开始，然而 purge binary log 命令并不会修改记录binlog的顺序的数值
- reset master 不能用于有任何 slave 正在运行的主从关系的主库。因为在 slave 运行时刻 reset master 命令不被支持，reset master 将 master 的binlog从000001 开始记录, slave 记录的 master log 则是 reset master 时主库的最新的 binlog,从库会报错无法找的指定的binlog文件。
扩展：删除从库的中继日志，并生成新的中继日志
# 删除 从（slave）库中的 中继日志（relay log）文件，并重新启用新的 relay log 文件 reset slave;功能说明：用于删除 SLAVE 数据库的 relay log 日志文件，并重新启用新的 rela ylog 文件（清空/重置从机环境）；
reset slave 将使 slave 忘记主从复制关系的位置信息。该语句将被用于干净的启动, 它删除 master.info 文件和 relay-log.info 文件以及所有的 relay log 文件并重新启用一个新的relay log文件。
使用 reset slave 之前必须使用 stop slave 命令将复制进程停止。
3.8 后续
搭建主从复制：双主双从
一个主机 m1 用于处理所有写请求，它的从机 s1 和另一台主机 m2 还有它的从机 s2 负责所有读请求。当 m1 主机宕机后，m2 主机负责写请求，m1，m2互为备机。如图：
这真的没有后续 ....
看个人灵性了 ...
4. 同步数据一致性问题
主从同步的要求：
- 读库和写库的数据一致（最终一致）。
- 写数据必须写到写库。
- 读数据必须到读库
4.1 理解主从延迟问题
进行主从同步的内容是 bin log（二进制日志），它是一个文件，在进行 网络传输 的过程中就一定会 存在主从延迟，这样就可能造成用户在从（slave）库上读取的数据不是最新的数据，也就是主从同步中的 数据不一致 问题。
举例：导致主从延迟的时间点主要包括以下三个
- 主库A 执行完成一个事务，写入 bin log，我们把这个时刻记为 T1（主库写入 bin log）；
- 之后传给从库B，我们把从库B 接收完这个 bin log 的时刻记为 T2（从库接收完 bin log）；
- 从库B 执行完成这个事务，我们把这个时刻记为 T3（从库执行 bin log 中的事务）；
4.2 主从延迟问题原因
在网络正常的时候，日志从主库传给从库所需的时间是很短的（即 T2 ~ T1 的值是非常少的）。在网络正常的情况下，主备延迟的主原因是：从库接收完 bin log（二进制日志） 和执行完这个些事件之间的时间差。
主备/主从最直接的表现是，从库消费中继日志（relay log）的速度，比主库生产 bin log 的速度要慢。可能造成这种情况的原因：
- 从库的机器性能比主库要差
- 从库的压力大
- 大事务的执行
- 举例1：一次性用 delete 语句删除太多数据
结论：后续再删除数据的时候，要控制每个事务删除的数据量，分成多次删除。
- 举例2：一次性用 insert ... select 插入太多数据
- 举例3：大表的DDL（数据库定义语言）
比如在主库对一张 500w 的表添加一个字段耗费了 10 分组，那么在从库上也会消耗10分钟。
- 总结一句话就是：尽量减少批量更新数据的条数（多次少量）。
4.3 如何减少主从延迟
若想要减少主从延迟的时间，可以采取下面的办法：
- 降低多线程大事务并发的概率，优化业务逻辑（减少大事务）。
- 优化 SQL ，避免慢 SQL，减少批量操作，建议写脚本以 update-sleep 这样的形式完成。
- 提高从库机器的配置，减少主库写 bin log 和从库 bin log 的效率差。
- 尽量采用 短的链路，也就是主库和从库服务器的距离尽量要短，提升端口带宽，减少 bin log 传输的网络延迟。
- 实时性要求的高业务读强制走主库，从库只做灾备，备份。
4.4 如何解决一致性问题
如果操作的数据存储在同一个数据库中，那么对数据进行更新的时候，可以对记录加写锁，这样在读取的时候就不会发生数据不一致的情况。但如果是这样的话，从库的作用仅仅是 备份，并没有起到 读写分离，分担主库 读压力 的作用。
那么在 读写分离 情况下，解决主从同步中数据不一致的问题，就是解决主从之间 数据复制方式 的问题，如果按照数据一致性 从弱到强 来进行划分，有以下 3 种复制方式（一致性越好，并发性就越低）。
方式1：异步复制 - 默认方式（效率最高，数据一致性最差）
异步模式：就是客户端 commit（提交）之后不需要等从库返回任何结果，直接将结果返回给客户端。这样做的好处是不会影响主库写的效率，但可能会存在主库宕机。而 bin log 还没有同步到从库的情况，也就是说此时的主库和从库数据不一致。这时候将从库升级为主库，那么就可能缺少原来主库中已提交的事务数据。所以，在这种复制模式（异步）下的数据一致性是最弱的。
方式2：半同步复制 （需要安装对应插件才能使用）
MySQL 5.5 版本之后开始支持 半同步复制 的方式。原理是：客户端 commit（提交）之后不直接将结果返回给客户端，而是等到至少有一个从库接收到了 bin log。并且写入到 中继日志（relay log）中，再返回给客户端。
这样做的好处就是提高了数据的一致性，但相比 异步复制 来说，至少多增加了一个网络连接的延迟，降低了主库写的效率。
在 MySQL 5.7 版本中还增加了一个 rpl_semi_sync_master_wait_for_slave_count 参数，用于设置应答的从库的数量（默认：1）。也就是说只要有 1 个从库进行了响应，就可以返回客户端。如果将这个参数调大，就可以提升数据一致性的强度，但也会增加主库等待响应的时间。
安装半同步复制：
半同步复制是通过插件来实现的，因此，安装半同步复制环境，需先在主（master）从（slave）中 安装半同步复制，插件安装后，便有了相关的系统变量，状态变量对半同步环境进行配置和监控。要使用半同步复制，必须满足如下要求：
- MySQL 服务器支持动态加载插件（即变量 have_dynamic_loading 为 YES），默认支持。
- 主从复制环境已经运行，即 在已经运行的复制环境安装半同步复制插件并进行相应的配置。
- 半同步复制 只支持默认的复制通道，不能同时配置多个复制通道。
安装半同步复制插件：
- 查看插件所在位置 - 查看主库
show variables like 'plugin_dir'; # 查询结果 Variable_name | Value ±-------------±-----------+ plugin_dir | E:\mysql\mysql-8.0.20-winx64\lib\plugin\
- 查看从库
show variables like 'plugin_dir'; # 查询结果 Variable_name | Value ±-------------±-----------+ plugin_dir | /usr/lib64/mysql/plugin/
- 主库安装插件
# 8.0 以前 install plugin rpl_semi_sync_source soname 'semisync_source.dll'; # 8.0 以后 反正就是两个版本 install plugin rpl_semi_sync_master soname 'semisync_master.dll';
- 从库安装插件
# 8.0 以前 install plugin rpl_semi_sync_replica soname 'semisync_replica.so'; # 8.0 以后 反正就是两个版本 install plugin rpl_semi_sync_slave soname 'semisync_slave.so';注意：
- 主库和从库安装的插件是有区别的哦！主库：rpl_semi_sync_source / rpl_semi_sync_master，从库：rpl_semi_sync_replica / rpl_semi_sync_slave。
- 在 windows下安装半同步复制插件时不能使用 .so 要使用 .dll。
- 检查插件安装 - 主从都要检查一下哦！
show plugins;说明：半同步复制插件安装完成之后，默认是没有激活半同步复制，需同时在主库和从库进行激活。
临时 - 开启半同步复制：
- 主库激活半同步复制
# 主库开启半同步复制 set global rpl_semi_sync_source_enabled=1; # 8.0 使用这个系统变量 set global rpl_semi_sync_master_enabled=1; # 查看状态 show variables like '%rpl_semi_sync%';
- 从库激活半同步复制
# 从库开启半同步复制 set global rpl_semi_sync_replica_enabled=1; # 8.0 使用这个系统变量 set global rpl_semi_sync_slave_enabled=1; # 查看状态 show variables like '%rpl_semi_sync%';
- 从库重新启动 I/O 线程
# 关闭 I/O 线程 stop replica io_thread; # 或者 mysql 8.0 stop slave io_thread; # 启动 I/O 线程 start replica io_thread; # 或者 mysql 8.0 start slave io_thread;
- 在主库中插入一条记录进行测试验证
INSERT INTO `xld`.`xld_master_slave_text` (`id`, `age`, `xld_name`) VALUES ('5', '5', 'xld5');
- 查看主库中半同步复制的状态
show status like '%rpl_semi_sync%'; # 查询结果略过..........说明 rpl_semi_sync_source_yes_tx：该参数表示主从库之间同步成功的事件数量。
永久 - 开启半同步复制：
在主，从库安装完半同步复制插件后，就可以使用相关的系统变量在配置文件（my.ini / my.cnf）对半同步复制进行配置，变量的查看可以使用 show variables like 命令进行查看。
- 在主库的 my.ini 文件的 [mysqld] 节点下添加如下配置，然后重启服务器即刻生效。
[mysqld] # 开启半同步复制 rpl_semi_sync_master_enabled=1
- 在主库的 my.cnf 文件的 [mysqld] 节点下添加如下配置，然后重启服务器即刻生效。
[mysqld] # 开启半同步复制 rpl_semi_sync_slave_enabled=1
- 查看主库的半同步复制变量
show variables like '%rpl_semi_sync%'; # 查询结果 Variable_name | Value ±---------------------------------------------------±-----------+ rpl_semi_sync_master_enabled | ON rpl_semi_sync_master_timeout | 10000 rpl_semi_sync_master_trace_level | 32 rpl_semi_sync_master_wait_for_replica_count | 1 rpl_semi_sync_master_wait_no_replica | ON rpl_semi_sync_master_wait_point | AFTER_SYNC变量说明：
rpl_semi_sync_master_enabled：（默认 OFF）用于控制主库是否启用半同步复制。设置为 ON，表示主库开启半同步复制。
rpl_semi_sync_master_timeout：（默认 10秒）用于控制主库等待从库返回确认提交的超时时间，若超过对应的时间未收到从库的确认，则半同步复制将退化为异步复制。
rpl_semi_sync_master_trace_level：（默认 32）用于控制主库半同步复制调试跟踪级别，定义的级别共有4个，分别是1，16，32和64。
rpl_semi_sync_master_wait_for_slave_count：（默认 1）用于控制主库在返回客户端会话前每个事务必须被多数个从库接收。默认是 1，即主库收到一个从库接收到事务事件的确认信息后，即可进行提交事务并返回给客户端会话，该值越小，性能越好。
rpl_semi_sync_master_wait_no_replica：（默认 ON）允许在超时时间内从库的数量小于 rpl_semi_sync_master_wait_for_replica_count 配置的值，只要在超时时间到期前有足够的从库确认事务，半同步复制就会继续。
rpl_semi_sync_master_wait_point：（默认 AFTER_SYNC）用于控制主库在向提交事务的客户端返回状态前等待接收事务的从库确认的时间点，该参数有两个值，分别是 AFTER_SYNC 和 AFTER_COMMIT，不同的值，产生的影响不同：
AFTER_COMMIT：
使用该模式下，事务在 commit（提交）的时，是先在存储引擎层进行提交，然后等待从库的确认，如果在从库确认的过程中，主库宕机了，此时，可能的情况有两种：
事务还没发送到从库上：此时，客户端会收到事务提交失败的信息，客户端会重新提交该事务到新的主库上，当宕机的主库重新启动后，以从库的身份重新加入到该主从架构中，会发现该事务在从库中被提交了两次，一次是之前作为主库的时候，一次是被新主库同步过来的。
事务已经发送到从库上：此时，从库已经收到并应用了该事务，但是客户端仍然会收到事务提交失败的信息，重新提交该事务到新的主库上。
使用 AFTER_COMMIT 时，事务在存储引擎层提交后，执行事务的客户端便收到返回信息，在提交到存储引擎之后和从库发出应答确认之前的这段时间，其他客户端可以看到已经提交的事务，如果此时发生错误（主库宕机），主从库切换后，之前可能看到数据的客户端发现在新主库看不到对应的数据了（可能会导致数据丢失）。
- AFTER_SYNC：
使用该模式下，事务在 commit（提交）的时，是先等待从库的应答确认，然后再在存储引擎层进行事务的提交。
使用 AFTER_SYNC 时，由于先收到从库的应答确认，然后再在存储引擎层提交事务，并返回给客户端，这样，在同一的时间，所有的客户端都可以看到已提交的数据，及时主库发生故障进行主从切换，所有在主库提交的事务已经复制到从库，因此，从库的数据也是最新的，这种半同步方案也称为 无损半同步复制。
- 查看从库的半同步复制变量
show variables like '%rpl_semi_sync%'; # 查询结果 Variable_name | Value ±-------------------------------------------------------±-----------+ rpl_semi_sync_slave_enabled | ON rpl_semi_sync_slave_trace_level | 32变量说明：
- rpl_semi_sync_slave_enabled：（默认 OFF）用于控制从库是否启用半同步复制，设置为 ON，表示从库开启半同步复制
- rpl_semi_sync_slave_trace_level：（默认 32）用于控制从库半同步复制调试跟踪级别，定义的级别共有4个，分别是 1，16，32和64。
监控半同步复制：
半同步复制提供了很多状态变量用于查看当前复制的状态，可使用 show status 进行查看。
- 查看主库的半同步复制状态
show status like '%rpl_semi_sync%';变量说明：
- rpl_semi_sync_master_clients：连接到主库进行半同步从库的数量（从库的个数）。
- rpl_semi_sync_master_net_avg_wait_time：主库等待从库应答的平均时间（单位 微秒），值一直是0，已被弃用。
- rpl_semi_sync_master_net_wait_time：主库等待从库应答的总时间（单位 微秒），值一直是0，已被弃用。
- rpl_semi_sync_master_net_waits：主库等待从库应答的总次数。
- rpl_semi_sync_master_no_times：主库关闭半同步复制的次数，即当主库在指定时间内未收到到从库的应答确认，就会退化为异步辅助。
- rpl_semi_sync_master_no_tx：从库未成功确认的提交的次数。
- rpl_semi_sync_master_status：主库当前是否处于半同步复制状态（ON：是，OFF：否）。
- rpl_semi_sync_master_timefunc_failures：当调用时间函数时（如：now()，sysdate() ...），主库失败的次数。
- rpl_semi_sync_master_tx_avg_wait_time：主库等待每个事务的平均时间（单位 微秒）。
- rpl_semi_sync_master_tx_wait_time：主库等待事务的总时间（单位 微秒）。
- rpl_semi_sync_master_tx_waits：主库等待事务的总次数。
- rpl_semi_sync_master_wait_pos_backtraverse：
- rpl_semi_sync_master_wait_sessions：正在等待从库应答的会话数（客户端）。
- rpl_semi_sync_master_yes_tx：从库已经确认提交的次数。
- 查看从库的半同步复制的状态
show status like '%rpl_semi_sync%';变量说明：
rpl_semi_sync_slave_status：显示当前从库是否运行半同步复制，如果插件已启用，并且 复制 I/O 线程 正在运行，则为ON，否则为 OFF。
方式3：组复制
异步复制 和 半同步复制 都无法最终保证数据的一致性问题，半同步复制 是通过判断从库响应的个数来决定是否返回给客户端，虽然数据一致性相比于 异步复制 有提升，但仍然无法满足对数据一致性要求高的场景，比如金融项目。而 MGR 很好的弥补了这两种复制模式的不足。
组复制技术，简称 MGR（MySQL Group Replication）。是 MySQL 在 5.7.17 版本中推出的一种新的数据库复制技术，这种复制技术是 基于 Paxos 协议 的状态机复制。
MGR 是如何工作的
首先我们将多个节点共同组成一个复制组，在 执行读写事务 的时候，需要通过一致性协议层的同意，也就是说读写事务想要进行提交时，必须要经过组里的 "大多数"（对应的 Node 节点）的同意，大多数指的是同意的节点数据量需要大于（N/2+1），这样才可以进行提交，而不是原发起方一个说了算。针对 只读事务 则不需要经过组内同意，直接 commit（提交）即可。
在一个复制组内有多个节点组成，它们各自维护了自己的数据副本，并且在一致性协议层实现了原子消息和全局有序消息，从而保证组内数据的一致性。
必须介绍：
MGR 将 MySQL 带入了数据强一致性的时代，是一个划时代的创新，其中一个重要的原因就是 MGR 是基于 Paxos 协议的。Paxos 算法是由 2013 年的图灵奖获得者 Leslie Lamport 于 1990 年提出的，有关这个算法的决策机制可以搜一下。事实上，Paxos 算法提出来之后就作为 分布式一致性算法 被广泛应用，比如：Apache 的 ZooKeeper 也是基于 Paxos 实现的。
5. 知识延伸 - 专业的DBA
在主从架构的配置中，如果想要采取读写分离的策略，我们可以 自己编写程序，也可以通过 第三方的中间件 来实现。
- 自己编写程序 的好处就在于比较自主，我们可以自己判断哪些查询在从库上来执行，针对实时性要求高的需求，我还可以考虑某些查询可以在主库上执行。同时，程序直接连接数据库，减少中间件层，相当于减少了性能损耗。
- 采用中间件 的方法有很明显的优势，功能强大，使用简单。但因为在客户端和数据库之间增加了中间件层 会有一些性能损耗，同时商业中间件也是有使用成本的。我们也可以考虑采取一些优秀的开源工具。
- Cober：属于阿里 B2B 事业群，始于2008年，在阿里服役3年多，接管3000+个MySQL数据库的 schema 集群日处理在线SQL请求50亿次以上。由于Cobar发起人的离职，Cobar停止维护。
- Mycat：是开源社区在阿里cobar基础上进行第二次开发，解决了cobar存在的问题，并且加入了许多新的功能在其中。青出于蓝而胜于蓝。
- OneProxy：基于MySQL官方的proxy思想利用c语言进行开发的，OneProxy是一款商业 收费 的中间件。舍弃了 一些功能，专注在 性能和稳定性上。
- kingshard：由小团队用go语言开发，还需要发展，需要不断完善。
- Vitess：是Youtube生产在使用，架构很复杂。不支持MySQL源生协议。
- Altas：是360团队基于mysql proxy改写，功能还需完善，高并发下不稳定。
- MaxScale：是mariadb（MySQL原作者维护的一个版本）研发的中间件。
- MySQLRoute：是MySQL官方Oracle公司发布的中间件。
主备切换：
- 主动切换
- 被动切换
- 如何判断主库出问题了？如何解决过程中的数据不一致问题？