品牌网站建设目标,网上怎么自己审核营业执照,黄骅市旅游景点,深圳网站建设营销服务平台目录 前言概述提出问题主从复制的用途工作流程 主从复制的配置创建复制账号配置主库和从库启动主从复制从另一个服务器开始主从复制主从复制时推荐的配置sync_binloginnodb_flush_logs_at_trx_commitinnodb_support_xa1innodb_safe_binlog 主从复制的原理基于语句复制优点… 目录 前言概述提出问题主从复制的用途工作流程 主从复制的配置创建复制账号配置主库和从库启动主从复制从另一个服务器开始主从复制主从复制时推荐的配置sync_binloginnodb_flush_logs_at_trx_commitinnodb_support_xa1innodb_safe_binlog 主从复制的原理基于语句复制优点缺点 基于行复制优点缺点 主从复制用到的文件mysql-bin.indexmysql-relay-bin-indexmaster.inforelay-log.info 发送复制事件到其他的从库 主从复制拓扑一主库多从库用途 主动-主动模式下的主-主复制主动-被动模式下的主-主复制拥有从库的主-主结构主库分发主库以及从库树或金字塔型定制的复制方案选择性复制分离功能数据归档只读从库 复制和容量规划如何测量从库延迟检查主从是否一致从主库重新同步从库 前言
MySQL的内建功能是构建基于MySQL的大规模高性能应用的基础这类应用使用所谓的“水平扩展”的架构。我们可以通过为服务器配置一个或多个从库的方式来进行数据同步复制功能不仅有利于构建高性能的应用同时也是高可用可扩展性灾难恢复备份以及数据仓库等工作的基础。
概述
复制解决的基本问题是让一台服务器的数据与其他服务器保持同步。一台主库的数据可以同步到多台设备上从库本身也可以被配置到另外一台服务器的书库。主库和从库可以有多种不同的组合方式。
MySQL支持两种复制方式
基于行的复制基于语句的复制
这两种方式都是通过在主库上记录二进制日志,在从库重放日志的方式来实现异步的数据复制。这意味着在同一时间点从库上的数据可能与主库存在不一致并且无法保证主从之间的延迟。一些大的语句可能导致从库产生几秒几分钟甚至几个小时的延迟。
MySQL通常是向后兼容的新版本的服务器可以作为老版本服务器的从库但反过来将老版本作为新版本服务器的从库通常是不行的因为它可能无法解析新版本所采用的的新的特性或语法另外所使用的的二进制文件的格式也可能不太相同。
主从复制通常不会增加主库的开销主要是启用二进制日志带来的开销但是出于备份或及时从崩溃中恢复的目的这点开销也是必要的。除此之外每个从库也会对主库增加一些负载例如I/O开销尤其当从库请求从主库读取旧的二进制日志文件时可能会造成更高的I/O开销。另外锁竞争也可能阻碍事务的提交。最后如果是从一个高吞吐量例如5000或者更高的TPS的主库上复制到多个从库唤醒多个线程发送事件的开销将会累加。
通过主从复制可以将读操作指向从库来获得更好的读扩展但是对于写操作除非设计得当否则并不适合通过主从复制来扩展写操作。在一主库多从库的架构中写操作会被执行多次这个时候整个系统的性能取决于写入最慢的那部分
提出问题
此时我们想一想当使用一主库多从库的时候可能会造成一些浪费因为本质上它会复制大量不必要的重复数据。例如对于一台主库和十台从库的时候会有十一份数据拷贝并且这十一台服务器的缓存中储存了大部分相同的数据。这和服务器上有十一路RAID 1类似。这不是一种经济的硬件使用方式。但这种复制架构却很常见稍后我们会讨论解决这个问题的方法。
主从复制的用途
数据分布 MySQL主从复制通常不会对贷款造成很大压力但是在5.1版本引入的基于行的复制会比传统的基于语句的复制模式带来的贷款压力更大。你可以随意的停止或者开始复制并在不同的地理位置来分布数据备份。但如果为了保持很低的复制延迟最好有一个稳定的低延迟的连接负载均衡 通过主从复制可以将读操作分布到多个服务器上实现对密集型应用的优化并且实现很方便通过简单的代码修改就能实现基本的负载均衡。对于小规模的应用可以简单的对机器名做硬编码或使用DNS轮询将一个机器名指向多个IP地址。当然也可以使用更复杂的方法例如网络负载均衡这一类的标准负载均衡解决方案能够很好的将负载分配到不同的MySQL服务器上。备份 对于备份来说复制是一项很有意义的技术补充但复制既不是备份也不能够取代备份高可用和故障切换 复制能够帮助应用避免MySQL单点失败一个包含复制的设计良好的故障切换系统能够明显得缩短宕机时间MySQL升级测试 这种做法比较普遍使用一个更高版本的MySQL作为从库保证在升级全部实例前查询能够在从库按照预期执行。
工作流程
MySQL实际上是如何复制数据的。总的来说分为如下三个步骤
在主库上记录二进制日志Binary Log。在每次准备提交事务完成数据更新前主库将数据更新的事件记录到二进制日志Binary Log中MySQL会按照事务提交的顺序而非每条语句的执行顺序来记录二进制日志。在记录二进制日志后主库会告诉存储引擎可以提交事务了。从库将主库的二进制日志Binary Log复制到其本地的中继日志Relay Log当中。首先从库会启动一个工作线程成为I/O线程I/O线程跟主库建立一个普通的客服端连接然后在主库上启动一个特殊的二进制转储binlog dump线程该线程没有对应的SQL命令这个二进制转储线程会读取主库上二进制日志Binary Log中的事件。他不会对事件进行轮询。如果该线程追赶上了主库它将进入睡眠状态直到主库发送信号量通知其有的新的事件产生时才会被唤醒备库I/O线程会将接收到的事件记录到中继日志中。从库的SQL线程会执行最后一步。该线程从中继日志Relay Log中读取事件并在从库中执行从而实现从库的数据更新。当SQL线程追赶上I/O线程时中继日志Relay Log通常已经在系统缓存中所以中继日志Relay Log的开销很低。SQL线程执行的事件也可以通过配置选项来决定是否写入其自己的二进制日志Binary Log中它对我们稍后提到的场景非常有用 上图中显示了在从库中有两个运行的线程在主库中也有一个运行的线程这种复制架构实现了获取事件和重放时间的解耦允许这两个过程异步进行。也是就说I/O线程能工独立于SQL线程之外工作。这种架构也限制了复制的过程其中很重要的一点是在主库上并发运行的查询在从库只能串行化执行因为只有一个SQL线程来重放中继日志中的事件这个会是很多工作负载的性能瓶颈所在。
主从复制的配置
为MySQL服务器配置非常简单。但由于场景不同。基本的步骤有所差异。最基本的场景是新安装的主库和从库总的来说分为以下几步
在每台服务器上注册新的账号配置主库和从库通知从库连接到主库并从主库上复制数据
这里我们假定大部分配置采用默认值即可在主库和从库都是全新安装并且拥有同样的数据时这样的假设是合理的。
创建复制账号
MySQL会赋予一些特殊的权限给复制线程。在从库运行的I/O线程会建立一个到主库的TCP/IP连接这意味着必须在主库创建一个用户并赋予权限。从库I/O线程以该用户名连接到主库并读取其二进制日志
mysql CREATE USER repl% IDENTIFIED BY Dxl199522;
mysql GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl%;
mysql flush privileges;REPLICATION SLAVE: 这是一个权限选项表示授予用户主从复制的权限。主从复制是 MySQL 数据库中的一种特性它允许一个 MySQL 服务器从服务器从另一个 MySQL 服务器主服务器复制数据。 REPLICATION CLIENT: 这是另一个权限选项表示授予用户复制客户端的权限。复制客户端权限允许用户查看复制相关的状态和信息。 我们在主库和从库都创建该账号 复制账户事实上只需要有主库上的REPLICATION SLAVE权限并不一定需要每一端服务器都有REPLICATION CLIENT权限那为什么我们要把这两种权限给主/从库都赋予呢?这有两个原因: 1.用来监控和管理复制的账号需要 REPLICATION CLIENT权限并且针对这两种目的使用同一个账号更加容易 (而不是为某个目的单独创建一个账号)。 2.如果在主库上建立了账号然后从主库将数据克隆到备库上时备库也就设置好了一一变成主库所需要的配置。这样后续有需要可以方便地交换主备库的角色。 配置主库和从库
首先在主库上开启一些设置需要打开二进制日志并指定一个独一无二服务器ID在主库的my.cnf文件中增加或者修改如下内容并重启
log_binmysql-bin
server_id10必须明确的指定一个唯一的服务器ID默认服务器ID通常为1。使用默认值可能会导致和其他服务器的ID冲突因此这里我们选择10来作为服务器ID。一种通用的做法是使用服务器IP地址的末8位但要保证它是唯一不变的。最好选择一些有意义的约定并遵循。
此时使用命令
mysql show master status;从库上也需要在my.cnf中增加类似的配置并且同样需要重启服务器
log_binmysql-bin
server_id2
relay_log/var/lib/mysql/mysql-relay-bin
log_slave_updates1
read_only1从技术上这些选项只有server_id是必须的。这里我们也使用了log_bin,并赋予了一个明确的名字。默认情况下它是根据机器名来命名的但如果机器名变化了可能会导致问题。为了简便起见我们将主库和从库上的log_bin设置为相同的值。
除此之外我们还增加了两个配置选项
relay_log:指定中继日志的位置和命名log_updates:允许从库将其重放的事件也记录到自身的二进制日志中此选项会给从库增加额外的工作但是在文章会提到为什么要添加改配置
启动主从复制
告诉从库如何连接到主库并重放进其二进制日志。
mysql CHANGE MASTER TO MASTER_HOST192.168.99.145,
MASTER_USERrepl,
MASTER_PASSWORDDxl199522,
MASTER_LOG_FILEmysql-bin.000001,
MASTER_LOG_POS0;MASTER_LOG_POS参数被设置成0因为要从日志的开头读起。当在从库中执行完这条语句之后可以通过
mysql show slave status\G图中参数显示当前从库复制尚未运行
运行命令开始复制
mysql start slave;执行该命令后没有显示错误然后使用show slave status查看状态显示 从输出上看出I/O和SQL线程都开始运行,此时已经完成最基本场景的主从复制了。
从另一个服务器开始主从复制
上述的设置主库和从库都是刚刚安装切都是默认的数据也就是说两台服务器上的数据已经相同并且知道当前主库的二进制文件。但是现实的场景往往是已经运行了一段时间的主库然后用一台新安装的从库与之同步此时这台设备还没有数据。那么如何初始化从库呢
需要有三个条件来让主库和从库保持数据同步
在某个时间点的主库的数据快照主库当前的二进制日志文件和获得数据快照时在该二进制日志文件中的偏移量我们把这两个值称为日志文件坐标log file coordinates。通过这两个值可以确定二进制日志的位置。可以通过show master status命令来获得从快照时间到现在的二进制日志
例举初始化从库的方法
使用冷备份 最基本的方式就是关闭主库把数据复制到主库。重启主库后会使用一个新的二进制日志文件我们在从库通过执行CHANGE MASTER TO指向这个文件的起始处。但这个方式缺点很明显在复制数据的时候需要关闭主库。使用mysqldump 如果只包含InnoDB表那么可以使用以下命令来转储主数据库数据并将其加载到从库然后设置相应的二进制日志坐标
mysqldump --single-transaction --all-databases --master-data1 --hostserver1 | mysql --hostserver2- single-transaction这个选项告诉 mysqldump 使用单个事务来获取数据以确保备份的数据是一致的。这对于生产环境中的备份非常有用因为它避免了对数据库的读锁使得数据库在备份期间可以正常提供读取服务。- all-databases这个选项指示 mysqldump 备份所有数据库而不仅仅是一个单独的数据库。- master-data1这个选项告诉 mysqldump 在备份文件中添加二进制日志文件和位置的信息这将用于初始化从库。使用快照或备份 只要知道对应的二进制日志标,就可以使用主库的快照或者备份来初始化从库 (如果使用备份需要确保从备份的时间点开始的主库二进制日志都要存在)。只需要把备份或快照恢复到从库然后使用CHANGE MASTER TO指定二进制日志的坐标使用另外的从库 可以使用任何一种提及的克隆或者拷贝技术来从任意一台从库上将数据克隆到另外一台服务器。但是如果使用的是 mysgldump–master-data 选项就会不起作用。此外不能使用SHOW MASTER STATUS来获得主库的二进制日志标而是在获取快照时使用SHOW SLAVE STATUS来获取从库在主库上的执行位置。使用另外的从库进行数据克隆最大的缺点是如果这台从库的数据已经和主库不同步克隆得到的就是脏数据。
主从复制时推荐的配置
sync_binlog
该配置是主库上二进制日志最重要的选项
sync_binlog0表示不同步写入二进制日志文件。这意味着 MySQL 不会等待操作在二进制日志文件中写入磁盘后才返回而是立即返回。这样可能会提高性能但也会增加风险因为未同步的数据可能在宕机等情况下丢失。sync_binlog1表示每次写入二进制日志都会等待操作在日志文件中同步到磁盘后才返回。这样可以确保数据的持久性但也会对性能产生一些影响。sync_binlogN表示每 N 次写入操作后才进行同步。这可以在一定程度上权衡性能和数据持久性。
需要注意的是选择适当的 sync_binlog 设置取决于你的应用需求以及对性能和数据安全性的权衡考虑。如果应用对数据的持久性要求较高可以选择较小的值甚至是 sync_binlog1。如果对性能要求较高可以选择较大的值或者关闭同步。
如果使用InnoDB,强烈推荐设置如下选项:
innodb_flush_logs_at_trx_commit
这个参数用于控制事务的日志刷新行为。它有三个可能的值
0表示事务提交时不会将日志刷新到磁盘而是定期刷新。这可能会在故障时丢失一些事务。1表示事务提交时会立即将日志刷新到磁盘。这可以保证事务的持久性但会对性能产生一定影响。2表示事务提交时将日志写入操作系统的缓存但不会立即刷新到磁盘。这在某些情况下可以提高性能但在操作系统崩溃的情况下可能会丢失一些事务。
innodb_support_xa1
这个参数用于控制是否支持分布式事务XA 事务。如果设置为 1表示 InnoDB 存储引擎支持 XA 事务可以与其他数据库或应用协调执行分布式事务。
innodb_safe_binlog
这个参数用于控制是否在事务提交时将日志写入二进制日志。如果设置为 1表示在每个事务提交时将操作写入二进制日志以确保日志和数据的一致性。如果设置为 0表示只有在事务完成时才将操作写入二进制日志
我们推荐明确指定二进制日志的名字以保证二进制日志名在所有服务器上是一致的避免因为服务器名的变化导致的日志文件名变化。你可能认为以服务器名来命名二进制日志无关紧要但经验表明当在服务器间转移文件、克隆新的从库、转储备份或者其他一些你想象不到的场景下可能会导致很多问题。为了避免这些问题需要给 log_bin 选项指定一个参数。可以随意地给一个绝对路径但必须明确地指定基本的命名(正如之前讨论的)。
log_bin/var/lib/mysq1/mysql-bin在从库上我们同样推荐开启如下配置选项为中继日志指定绝对路径
relay_log/path/to/logs/relay-bin
skip_slave_start
read_only通过设置 relay_log 可以避免中继日志文件基于机器名来命名防止之前提到的可能在主库发生的问题。指定绝对路径可以避免多个 MySQL版本中存在的 Bug这些 Bug可能会导致中继日志在一个意料外的位置创建。skip _slave_start 选项能够阻止从库在崩溃后自动启动复制。这可以给你一些机会来修复可能发生的问题。如果从库在崩溃后自动启动并且处于不一致的状态就可能会导致更多的损坏最后将不得不把所有数据丢弃并重新开始配置从库 read_only 选项可以阻止大部分用户更改非临时表除了复制SOL 线程和其他拥有超级权限的用户之外这也是要尽量避免给正常账号授予超级权限的原因之一。
主从复制的原理
主从复制有两种方式
基于语句复制基于行复制
基于语句复制
在基于语句复制中主库执行的每个 SQL 语句都会被记录到二进制日志并且从库会解析并在从库上执行相同的 SQL 语句来复制数据。这意味着从库上的数据会与主库上的数据在逻辑上保持一致。
优点
简单明了复制的是 SQL 语句不需要复制整个行的数据。节省网络带宽当一条 SQL 修改多行数据时只需要复制一条语句节省了网络传输的带宽。
缺点
不适用于非确定性函数如果主库上使用了随机函数或时间函数从库上的执行结果可能与主库不一致。不适用于不可重复读问题在从库上执行与主库不同的查询顺序时可能导致结果不一致。主库版本限制一些特定版本的 MySQL 主库可能不支持基于语句的复制。
基于行复制
在基于行复制中主库上每次修改数据的操作都会被记录为修改行的详细信息包括修改前和修改后的数据。从库会接收这些行级别的变更并在从库上应用相同的修改操作。
优点
更准确的复制避免了基于语句复制的一些问题如非确定性函数和不可重复读问题。可用于更广泛的情况适用于所有 SQL 语句包括复杂的更新操作。
缺点
增加了网络传输和存储开销需要传输和存储更多的数据可能增加网络带宽和磁盘空间的占用。
主从复制用到的文件
mysql-bin.index
当在服务器上开启二进制日志时同时会生成一个和二进制日志同名的但以index作为后缀的文件该文件用于记录磁盘上的二进制日志文件。这里的“index”并不是指表的索引而是说这个文件的每一行包含了二进制文件的文件名。你可能认为这个文件是多余的可以被删除 (毕竟 MySQL可以在磁盘上找到它要的文件)。事实上并非如此MySQL 依赖于这个文件除非在这个文件里有记录否则MySOL识别不了二进制日志文件。
mysql-relay-bin-index
这个文件是中继日志的索引文件和 mysgl-bin.index 的作用类似
master.info
这个文件用于保存从库连接到主库所需要的信息格式为纯文本(每行一个值)不同的 MySQL 版本其记录的信息也可能不同。此文件不能删除否则从库在重启后无法连接到主库。这个文件以文本的方式记录了复制用户的密码所以要注意此文件的权限控制。
relay-log.info
这个文件包含了当前从库复制的二进制日志和中继日志坐标(例如从库复制在主库上的位置)同样也不要删除这个文件否则在从库重启后将无法获知从哪个位置开始复制可能会导致重放已经执行过的语句。
发送复制事件到其他的从库
log_slave_updates 选项可以让从库变成其他服务器的主库。在设置该选项后MySQL会将其执行过的事件记录到它自己的二进制日志中。这样它的从库就可以从其日志中检索并执行事件。 在这种场景下主库将数据更新事件写入二进制日志第一个从库提取并执行这个事件这时候一个事件的生命周期应该已经结束了但由于设置了 log_slave_updates从库会将这个事件写到它自己的二进制日志中。这样第二个从库就可以将事件提取到它的中继日志中并执行。这意味着作为源服务器的主库可以将其数据变化传递给没有与其直接相连的从库上。默认情况下这个选项是被打开的这样在连接到从库时就不需要重启服务器。
当第一个从库将从主库获得的事件写入到其二进制日志中时这个事件在从库二进制日志中的位置与其在主库二进制日志中的位置肯定是不相同的可能在不同的日志文件或文件内不同的位置。这意味着你不能假定所有拥有同一逻辑复制点的服务器拥有相同的日志坐标。稍后我们会提到这种情况会使某些任务更加复杂例如修改一个从库的主库或将从库提升为主库。
除非你已经注意到要给每个服务器分配一个唯一的服务器 ID否则按照这种方式配置从库会导致一些奇怪的错误甚至还会导致复制停止。一个更常见的问题是 :为什么要指定服务器ID难道MySQL 在不知道复制命来源的情况下不能执行吗?为什么MySQL 要在意服务器ID是全局唯一的。问题的答案在于 MySOL 在复制过中如何防止无限循环。当复制SQL 线程读中继日志时会丢弃事件中记录的服务器ID 和该服务器本身ID相同的事件从而打破了复制过程中的无限循环。在某些复制拓扑结构下打破无限循环非常重要例如主 -主复制结构
主从复制拓扑
可以在任意个主库和从库之间建立复制只有一个限制每一个从库只能有一个主库。有很多复杂的拓扑结构但即使是最简单的也可能会非常灵活。一种拓扑可以有多种用途。
我们已经讨论了如何为主库设置一个从库接下来讨论其他比较普遍的拓扑结构以及它们的优缺点。记住下面的基本原则:
一个MySQL从库实例只能有一个主库每个从库必须有一个唯一的服务器 ID一个主库可以有多个从库 (或者相应的一个从库可以有多个兄弟从库)。如果打开了 log_slave_updates 选项一个从库可以把其主库上的数据变化传播到其他从库。
一主库多从库
这是最简单的拓扑结构。在有少量写和大量读时这种配置是非常有用的。可以把读分摊到多个从库上直到从库给主库造成了太大的负担或者主从之间的带宽成为瓶颈为止。你可以按照之前介绍的方法一次性设置多个从库或者根据需要增加从库
用途
为不同的角色使用不同的从库 (例如添加不同的索或使用不同的存储)把一台从库当作待用的主库除了复制没有其他数据传输。将一台从库放到远程数据中心用作灾难恢复。延迟一个或多个从库以备灾难恢复。使用其中一个从库作为备份、培训、开发或者测试使用服务器
主动-主动模式下的主-主复制
主-主复制也叫双主复制或者双向复制包含两台服务器每一个都被配置成对方的主库和从库换句话说他们是一对主库 主动-主动模式下主- 主复制有一些应用场景但通常用于特殊的目的。一个可能的应用场景是两个处于不同地理位置的办公室并且都需要一份可写的数据拷贝。
这种配置最大的问题是如何解决冲突两个可写的互主服务器导致的问题非常多。这通常发生在两台服务器同时修改一行记录或同时在两台服务器上向一个包含 AUTO_INCREMENT列的表里插入数据
主动-被动模式下的主-主复制
这是前面描述的主-主结构的变体它能够避免我们之前讨论的问题。这也是构建容错性和高可用性系统的非常强大的方式主要区别在于其中的一台服务器是只读的被动服务器 这种方式使得反复切换主动和被动服务器非常方便因为服务器的配置是对称的。这使得故障转移和故障恢复很容易。它也可以让你在不关闭服务器的情况下执行维护、优化表、升级操作系统(或者应用程序、硬等) 或其他任务。
例如执行 ALTER TABLE操作可能会锁住整个表阻塞对表的读和写这可能会花费很长时间并导致服务中断。然而在主-主配置下可以先停止主动服务器上的从库复制线程(这样就不会在被动服务器上执行任何更新)然后在被动服务器上执行ALTER操作交换角色最后在先前的主动服务器上启动复制线程。这个服务器将会读取中继日志并执行相同的 ALTER语句。这可能花费很长时间但不要紧因为该服务器没有为任何活跃查询提供服务。
主动-被动模式的主-主结构能够帮助回避许多 MySOL的问题和限制
让我们看看如何配置主-主服务器对在两台服务器上执行如下设置后会使其拥有对称的设置:
确保两台服务器上有相同的数据启用二进制日志选择唯一的服务器 ID并创建复制账号启用从库更新的日志记录后面将会看到这是故障转移和故障恢复的关键把被动服务器配置成只读防止可能与主动服务器上的更新产生冲突这一点是可选的。启动每个服务器的MySQL实例将每个主库设置为对方的从库使用新创建的二进制日志开始工作
设置主动- 被动的主-主拓扑结构在某种意义上类似于创建一个热备份但是可以使用这个“备份”来提高性能例如用它来执行读操作、备份、“离线”维护以及升级等。真正的热备份做不了这些事情。然而,你不会获得比单台服务器更好的写性能。
拥有从库的主-主结构
另外一种相关的配置是为每个主库增加一个从库 这种配置的优点是增加了冗余对于不同地理位置的复制拓扑能够消除站点单点失效的问题。你也可以像平常一样将读查询分配到从库上。 如果在本地为了故障转移使用主 - 主结构这种配置同样有用。当主库失效时用从库来代替主库还是可行的虽然这有点复杂。同样也可以把从库指向一个不同的主库但需要考虑增加的复杂度
主库分发主库以及从库
我们之前提到当从库足够多时会对主库造成很大的负载。每个从库会在主库上创建一个线程并执行 binlog dump 命令。该命令会读取二进制日志文件中的数据并将其发送给从库。每个从库都会重复这样的工作它们不会共享 binlog dump 的资源
如果有很多从库并且有大的事件时例如一次很大的 LOAD DATA INFILE操作主库上的负载会显著上升甚至可能由于从库同时请求同样的事件而耗尽内存并崩溃。另一方面如果从库请求的数据不在文件系统的缓存中可能会导致大量的磁盘检索这同样会影响主库的性能并增加锁的竞争。
因此如果需要多个从库一个好办法是从主库移除负载并使用分发主库。分发主库事实上也是一个从库它的唯一目的就是提取和提供主库的二进制日志。多个从库连接到分发主库这使原来的主库摆脱了负担。为了避免在分发主库上做实际的查询可以将它的表修改为 blackhole 存储引擎 很难说当从库数据达到多少时需要一个分发主库。按照通用准则如果主库接近满负载不应该为其建立 10 个以上的从库。如果有少量的写操作或者只复制其中一部分表主库就可以提供更多的复制。另外也不一定只使用一个分发主库。如果需要的话可以使用多个分发主库向大量的从库进行复制或者使用金字塔状的分发主库。在某些情况下可以通过设置slave_compressed_protocol来节约一些主库带宽。这对跨数据中心复制很有好处。
还可以通过分发主库实现其他目的例如对二进制日志事件执行过滤和重写规则。这比在每个从库上重复进行日志记录、重写和过滤要高效得多。
如果在分发主库上使用 blackhole 表,可以支持更多的从库。虽然会在分发主库执行查询但其代价非常小因为 blackhole 表中没有任何数据。blockhole 表的缺点是其存在 Bug例如在某些情况下会忘记将自增ID 写入到二进制日志中。所以要小心使用 blackhole表
一个比较常见的问题是如何确保分发服务器上的每个表都是 blackhole 存储引警。如果有人在主库创建了一个表并指定了不同的存储引擎呢?确实不管什么时候在从库上使用不同的存储引擎总会导致同样的问题。常见的解决方案是设置服务器的 storage_engine选项:
storage_engine blackhole这只会影响那些没有指定存储引警的CREATE TABLE的语句如果有一个无法控制的应用这种拓扑结构可能会非常脆弱。可以通过 skip_innodb 选项禁止InnoDB将表退化为MyISAM。但你无法禁止 MyISAM 或者Memory 引擎。
使用分发主库另外一个主要的缺点是无法使用一个从库来代替主库。因为由于分发主库的存在导致各个从库与原始主库的二进制日志坐标已经不相同
树或金字塔型
如果正在将主库复制到大量的从库中。不管是把数据分发到不同的地方还是提供更高的读性能使用金字塔结构都能够更好地管理
这种设计的好处是减轻了主库的负担就像前一节提到的分发主库一样。它的缺点是中间层出现的任何错误都会影响到多个服务器。如果每个从库和主库直接相连就不会存在这样的问题。同样中间层次越多处理故障会更困难、更复杂。
定制的复制方案
MySQL的复制非常灵活可以根据需要定制解决方案。典型的定制方案包括 组合过滤分发和向不同的存储引擎复制。也可以使用“黑客手段”例如从一个使用 blackhole存储引擎的服务器上复制或复制到这样的服务器上。可以根据需要任意设计。这其中最大的限制是合理的监控和管理以及所拥有资源约束网络带宽CPU能力等
选择性复制
为了利用访问局部性原理 (locality of reference)并将需要读的工作集驻留在内存中可以复制少量数据到从库中。如果每个从库只拥有主库的一部分数据并且将读分配给从库就可以更好地利用从库的内存。并且每个从库也只有主库一部分的写入负载这样主库的能力更强并能保证从库延迟
它的优势在于主库包含了所有的数据集这意味着无须为了一条写入查询去访问多个服务器。如果读操作无法在从库上找到数据还可以通过主库来查询。即使不能从从库上读取所有数据可以移除大量的主库读负担。
最简单的方法是在主库上将数据划分到不同的数据库里。然后将每个数据库复制到不同的从库上。例如若需要将公司的每一个部门的数据复制到不同的从库可以创建名为sales、marketing、procurement 等的数据库每个从库通过选项 replicate_wild_dotable选项来限制给定数据库的数据。下面是 sales 数据库的配置:
replicate_wild_do_table sales.%
#replicate_wild_do_table 是 MySQL 复制中的一个配置选项
#用于指定要复制的表。sales.% 表示要复制 sales 数据库下的所有表以 % 通配符表示到从库。也可以通过一台分发主库进行分发。举个例子如果想通过一个很慢或者非常昂贵的网络从一台负载很高的数据库上复制一部分数据就可以使用一个包含 blackhole 表和过滤规则的本地分发主库分发主库可以通过复制过滤移除不需要的日志。这可以避免在主库上进行不安全的日志选项设定并且无须传输所有的数据到远程从库
分离功能
许多应用都混合了在线事务处理 (OLTP)和在线数据分析(OLAP)的查询。OLTP查询比较短并且是事务型的OLAP 查询则通常很大也很慢并且不要求绝对最新的数据这两种查询给服务器带来的负担完全不同因此它们需要不同的配置其至可能使用不同的存储引擎或者硬件。
一个常见的办法是将 OLTP 服务器的数据复制到专门为 OLAP 工作负载准备的备库上这些备库可以有不同的硬件、配置、索引或者不同的存储引擎。如果决定在备库上执行OLAP 查询就可能需要忍受更大的复制延迟或降低备库的服务质量。这意味着在一个非专用的备库上执行一些任务时可能会导致不可接受的性能例如执行一条长时间运行的查询。
数据归档
可以在从库上实现数据归档也就是说可以在从库上保留主库上删除过的数据在主库上通过 delete 语句删除数据是确保 delete 语句不传递到从库就可以实现。有两种通常的办法:一种是在主库上选择性地禁止二进制日志另一种是在备库上使用 replicate_ignore_db规则(是的两种方法都很危险)。
第一种方法需要先将SQL_LOG_BIN设置为0然后再进行数据清理。这种方法的好处是不需要在从库进行任何配置由于SQL语根本没有记录到二进制日志中效率会稍微有所提升。最大缺点也正因为没有将在主库的修改记录下来因此无法使用二进制日志来进行审计或者做按时间点的数据恢复。另外还需要 SUPER 权限
第二种方法是在清理数据之前对主库上特定的数据库使用USE语句。例如可以创建一个名为 purge的数据库然后在从库的 my.cnf文件里设置 replicate_ignore_dbpurge并重启服务器。从库将会忽略使用了 USE语句指定的数据库。这种方法没有第一种方法的缺点但有另一个小小的缺点:从库需要去读取它不需要的事件。另外也可能有人在 purge数据库上执行非清理查询从而导致从库无法重放该事件。
只读从库
将从库设置为只读以防止在从库进行的无意识修改导致复制中断。可以通过设置 read_only 选项来实现。它会禁止大部分写操作除了复制线程和拥有超级权限的用户以及临时表操作。只要不给也不应该给普通用户超级权限这应该就是很完美的方法
复制和容量规划
写操作通常是复制的瓶颈并且很难使用复制来扩展写操作。当计划为系统增加复制容量时需要确保进行了正确的计算否则很容易犯一些复制相关的错误。
例如假设工作负载为 20% 的写以及80% 的读 。为了计算简单假设有以下前提 :
读和写查询包含同样的工作量。所有的服务器是等同的每秒能进行 1000次查询。从库和主库有同样的性能特征。可以把所有的读操作转移到从库。
如果当前有一个服务器能支持每秒 1 000 次查询那么应该增加多少从库才能处理当前两倍的负载并将所有的读查询分配给从库?
看上去应该增加两个从库并将1600的读操作平分给他们。但是不要忘记写入负载同样增加到了400次每秒并且无法在主从服务器之间进行分摊。每个从库每秒必须处理400次写入这意味着每个从库写入占了40%只能每秒为600次查询提供服务。因此需要三台而不是两台从库来处理双倍负载。
如何测量从库延迟
主从同步一个普遍的问题是如何监控从库落后主库的延迟有多大虽然SHOW_SLAVE _STATUS输出的Seconds_behind _master列理论上显示了从库的延时但是由于各种原因并不是精确的
从库Seconds_behind_master值是通过将服务器当前的时间戳与二进制日志中的事件的时间戳相对比得到的所以只有在执行事件时才能报告延迟。如果从库复制线程没有运行就会报延迟为 NULL。一些错误(例如主从的 max_allowed_packet 不匹配或者网络不稳定)可能中断复制并且/或者停止复制线程,但 Seconds_behind_master将显示为0而不是显示错误即使从库线程正在运行从库有时候可能无法计算延时。如果发生这种情况从库会报0或者NULL。一个大事务可能会导致延迟波动例如有一个事务更新数据长达一个小时最后提交。这条更新将比它实际发生时间要晚一个小时才记录到二进制日志中。当从库执行这条语句时会临时地报告从库延迟为一个小时然后又很快变成0。如果分发主库落后了并且其本身也有已经追赶上它的从库从库的延迟将显示为 0,而事实上和源主库之间是有延迟的
解决这些问题的办法是忽略 Seconds_behind_master的值并使用一些可以直接观察和衡量的方式来监控从库延迟。最好的解决办法是使用heartbeat record 这是一个在主库上会每秒更新一次的时间戳。为了计算延时可以直接用从库当前的时间戳减去心跳记录的值。这个方法能够解决刚刚我们提到的所有问题另外一个额外的好处是我们还可以通过时间戳知道从库当前的复制状况。包含在 Percona Toolkit 里的 pt-heartbeat 脚本是“复制心跳”最流行的一种实现 心跳还有其他好处记录在二进制日志中的心跳记录拥有许多用途例如在一些很难解决的场景下可以用于灾难恢复。
检查主从是否一致
使用Percona Toolkit 里的 pt-table-checksum能够验证主从数据是否一致。其主要特性是用于确认从库与主库的数据是否一致。工作方式是通过在主库上执行 INSERT…SELECT查询
这些查询对数据进行校验并将结果插入到一个表中。这些语句通过复制传递到从库并在从库执行一遍然后可以比较主从上的结果是否一样。由于该方法是通过复制工作的它能够给出一致的结果而 无须同时把主从上的表都锁上。
通常情况下可以在主库上运行该工具参数如下 :
$ pt-table-checksum --replicatetest.checksum master host该命令将检查所有的表并将结果插入到 test.checksum 表中。当查询在从库执行完后就可以简单地比较主从之间的不同了。pt-table-checksum 能够发现服务器所有的从库在每台从库上运行查询并自动地输出结果
从主库重新同步从库
当使用校验工具发现了数据不一致或者是因为已经知道是从库忽略了某条查询或者有人在从库上修改了数据此时就需要出路未被同步的从库。
关闭从库重新从主库上复制一份数据。该方式缺点是不太方便特别是数据量很大的时候。使用mysqldump转储受影响的数据并重新导入。可以在主库上简单的锁住表然后进行转储再等待从库赶上主库。然后将数据导入到从库中。需要等待从库赶上主库这样就不会为其他的表引入新的不一致。此种方式在表很大或者网络带宽受限的情况下转储和重载数据的代价依然很高pt-table-sync 是 Percona Toolkit 中的另外一个工具可以解决该问题。该工具能够高效地查找并解决表之间的不同。它同样通过复制工作在主库上执行查询在从库上重新同步这样就没有竞争条件。它是结合 pt-table-checksum 生成的 checksum 表来工作的所以只能操作那些已知不同步的表的数据块。但该工具不是在所有场景下都有效。为了正确地同步主库和从库该工具要求复制是正常的否则就无法工作。pt-table-sync设计得很高效但当数据量非常大时效率还是会很低。比较主库和从库上 1TB 的数据不可避免地会带来额外的工作。尽管如此在那些合适的场景中该工具依然能节约大量的时间和工作。
