MySQL数据库运维与管理（微课版） 课件 项目7 MYSQL复制

数据库运维与管理项目7MySQL复制教学目标知识目标掌握MySQL复制的概念掌握MySQL复制的过程掌握MySQL复制的表现形式掌握MySQL复制的常用拓扑结构能力目标能在Windows系统下建立MySQL主从复制能在Linux系统下建立MySQL主从复制能配置MySQL半同步复制能配置MySQL并行复制素养目标加强安全防范意识了解系统稳定运行的重要性提升规范操作行为思政目标民族自豪感国产软件振兴的使命担当项目7

MySQL复制项目场景随着天天电器商场在线业务的开展，为了时刻给用户提供7×24小时的优质服务，该公司要求信息部门对现有的服务器软硬件进行升级，明确要求当数据库的主服务器宕机时，能够及时启动备用服务器以提供不间断服务，从而保证数据库内容的实时备份。在实际的生产环境中，为了解决MySQL的单点故障问题和提高MySQL的整体服务性能，一般会建立MySQL主从复制。项目知识导入一、MySQL复制概述1．概述

MySQL复制是指从一台MySQL主服务器将数据复制到另一台或多台MySQL备用服务器的过程（主服务器与备用服务器数据同步）2．复制方式（1）MySQL支持多种复制方式。基于主库二进制日志中的事件与基于全局事务标识事件，并且要求二进制日志文件及其位置在主库和从库之间同步；这种复制方式是事务性的，因此不需要使用二进制日志文件和这些文件的位置，从而大幅简化了复制任务。（2）MySQL复制格式有两种核心类型。基于语句的复制，它可以复制整个SQL语句；基于行的复制，它仅复制更改的行。项目知识导入3．MySQL复制可以解决以下常见问题(1）服务的高可用性。从库可以提升为主库，减少宕机时间，双主时承担高可用角色。（2）服务的高性能。从库可以用于查询、统计分析，分担主库负载，承担负载均衡角色。（3）数据安全性。使用延迟从库作为数据备份以应对误删除带来的数据不可恢复性。（4）分析。实时数据可以在主库上创建，而信息分析可以在从库上进行，从而不影响主库的性能。（5）远程数据分发。可以使用复制来创建数据的本地副本以供远程站点使用，而无须永久访问项目知识导入二、MySQL复制过程1.传统复制：基于二进制日志文件的传统复制过程分为以下3步。（1）主库将数据改变记录到二进制日志中（这些记录叫作二进制日志事件）；（2）从库将主库的二进制日志事件复制到它的中继日志（RelayLog）中；（3）从库重做中继日志中的事件（即数据重演），将改变反映到从库中。项目知识导入2、GTID复制（1）概述GTID是MySQL5.6版本开始在主从复制方面推出的重要特性。GTID是一个已提交事务的编号，并且是全局唯一编号，不仅是在主库上，在给定的复制设置中的所有数据库上它都是唯一的。GTID格式：GTID=server_uuid:transaction_id。GTID由server_uuid和transaction_id组成，其中server_uuid在数据库启动时自动生成，存放在数据库目录下的f文件中；transaction_id即事务id，是事务提交时由系统按顺序分配的序列号。在数据库事务提交时，二进制日志中会产生一个对应的GTID。主从复制时，从库会通过GTID来确定同步的位置，不用再去找二进制日志文件的复制位置，即不再使用master_log_file和master_log_pos开启复制，而使用master_auto_postion=1的方式自动匹配GTID断点进行复制。项目知识导入（2）GTID相关参数项目知识导入（3）GTID相关参数说明binlog_gtid_simple_recovery：设置MySQL服务启动时是否自动寻找GTID集合值。enforce_gtid_consistency：强制要求只复制事务安全的事务。gtid_executed：表示在当前实例上执行过的GTID集合；实际上包含了所有记录到二进制日志中的事务。执行RESETMASTER命令可以将该变量置空。gtid_executed_compression_period：启用GTID时，服务器会定期在mysql.gtid_executed表上执行压缩。此参数可以控制压缩表之前允许的事务数，从而控制压缩率。设置为0时，则不进行压缩。gtid_mode：是否开启GTID模式。项目知识导入gtid_next：会话级参数，指示如何产生下一个GTID。该参数可能的取值如下。（1）AUTOMATIC：自动生成下一个GTID。（2）ANONYMOUS：执行事务不会产生GTID。（3）显式指定GTID：可以指定任意形式合法的GTID值，但不能是当前gtid_executed中已经包含的GTID，

否则，下次执行事务时会报错。gtid_owned：表示正在执行的事务GTID及对应的线程ID。gtid_purged：由于二进制日志需要定期进行清理，gtid_purged用于记录已经被清除的二进制日志事务的GTID集合，清除的GTID集合会包括在gtid_executed中。执行RESETMASTER命令时会把gtid_purged置空，即始终保持gtid_purged是gtid_executed的子集。session_track_gtids：是否开启跟踪查询所有MySQL客户端的GTID执行情况。项目知识导入（4）GTID复制的工作过程（1）主库更新数据时，会在事务提交前产生GTID，GTID会被记录到二进制日志中。（2）从库的I/O线程将变更的二进制日志写入本地的中继日志中。（3）SQL线程从中继日志中获取GTID，然后查询从库的二进制日志中是否有相应记录。（4）如果有记录，则说明该GTID的事务已经执行，从库会忽略。（5）如果没有记录，从库就会从中继日志中执行该GTID的事务，并将其记录到二进制日志中。（6）从库在复制主库的二进制日志时，会校验主库的GTID的事务是否已经执行过（一个GTID的事务只能执行一次）。（7）为了保证主、从库中数据的一致性，多线程只能同时执行一个GTID的事务。项目知识导入三、复制的表现形式

（1）同步复制（SynchronousReplication）：事务必须在主库和从库同时提交成功。（2）异步复制（AsynchronousReplication）：MySQL默认采用异步复制，主库提交的事务不需要等从库接收到或者提交成功。（3）半同步复制（SemisynchronousReplication）：主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到其中继日志中后才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延迟的网络中使用。项目任务分解任务7-1在Windows系统下建立MySQL主从复制任务说明

任务开始前，需要准备好两个基于Windows系统的MySQL实例。由于MySQL主从复制基于二进制日志进行，所以这两个MySQL实例需要开启二进制日志。本任务要求在Windows系统下建立MySQL主从复制并进行数据同步测试。任务实施过程项目任务分解任务7-2在Linux系统下建立MySQL主从复制任务说明

企业搭建具有高可用性的数据库系统时，复制是一种常用的技术方法。本任务要求在Linux系统下，基于二进制日志事件，建立MySQL主从复制并测试其同步情况。任务实施过程项目任务分解任务7-3配置MySQL半同步复制任务说明

MySQL复制的表现形式默认为异步复制，这种方式对数据完整性的保护有限。MySQL复制的半同步机制是指主库节点只要确认有至少一个从库节点接收到事务，即可向发起请求的客户端返回操作成功的消息。本任务要求配置MySQL半同步复制。任务实施过程项目任务分解任务7-4配置MySQL并行复制任务说明

MySQL的复制是基于二进制日志的。MySQL复制包括两个部分，即I/O线程和SQL线程。I/O线程主要用于拉取、接收主库传递过来的二进制日志，并将其写入中继日志。SQL线程主要负责解析中继日志，并将其应用到从库中。I/O线程和SQL线程都是单线程的，但是SQL线程往往是复制的瓶颈，并行复制可以有效减少延迟。本任务要求配置MySQL并行复制。任务实施过程项目任务分解任务7-5基于GTID建立MySQL主从复制任务说明

GTID复制比传统复制的搭建过程更简单、复制过程更安全、故障转移更优。本任务要求基于GTID建立MySQL主从复制并对其进行测试。任务实施过程常见问题解决问题1：出现“Last_IO_Error:errorconnectingtomaster‘repl@7:33068’-retry-time:60retries:4message:Authenticationplugin‘caching_sha2_password’reportederror:Authenticationrequiressecureconnection.”错误提示。原因分析：

MySQL8.0默认使用caching_sha2_password身份验证插件，在从库连接主库时，如果使用了不被caching_sha2_password插件认可的RSA公钥，主库就会拒绝从库的连接请求。这时要从服务器请求RSA公钥。解决方案：配置从库端到主库端的连接时，添加get_master_public_key=1参数，命令如下。mysql>changemastertomaster_host='7',->master_user='repl',->master_port=33068,->master_password='repl',->master_log_file='binlog.000106',->master_log_pos=1269;->get_master_public_key=1;常见问题解决问题2：：执行showslavestatus命令时出现“Last_IO_Error:Fatalerror:TheslaveI/OthreadstopsbecausemasterandslavehaveequalMySQLserverUUIDs;theseUUIDsmustbedifferentforreplicationtowork.”错误提示。原因分析：

UUID相同导致出现该错误。解决方案：把UUID的配置文件删除，然后把该从库节点的server_id修改成与其他节点不同，再重新启动MySQL服务，自动生成UUID即可。UUID配置文件名为f，存放在MySQL的data目录下。常见问题解决问题3：登录时出现“Last_IO_Error:Gotfatalerror