MySQL复制的注意事项
本文是看了《高性能MySQL》书中复制章节的内容,并结合我个人的经验而写的。书中关于复制的内容非常多,本文不可能包括所有的内容,本文只是列举并摘录一些我认为重要或者我以前忽略的内容。如有需要,请阅读原书相关内容。
复制的配置
配置复制时需要为主库,备库创建一个复制账户,并且需要将REPLICATION SLAVE,REPLICATION CLIENT权限都授予此账户。如果只是配置主备库的复制,只需要在主库上创建复制账户,并将REPLICATION SLAVE权限赋予此主库上的复制账户即可。那么为什么在主库,备库上都创建复制账户,并授予REPLICATION SLAVE,REPLICATION CLIENT权限呢?一是为了方便主库发生故障时,主备库的切换,二是利于数据库管理员对复制进行监控与管理。 另外还有几个对复制很重要的配置项,像server_id,二进制日志的命名,这些配置项都需要在主库,备库上指定。另外备库上还需要配置中继日志的路径和命名。对于主库故障时需要切换为主库的备库,还需要配置log_slave_updates(备库将重放的事件记录到二进制日志),一是当故障切换时,有二进制日志可以用,二是当次备库也作为其他备库的主库时,需要打开此选项,这样才能够将事件复制到他的备库。
备库的用途
备库的用途非常多,但是看了书中列举的用途项后,还是超出我的想象。
1、为不同的角色使用不同的备库(例如添加不同的索引或使用不同的存储引擎)。 2、把一台备库当作待用的主库,除了复制没有其他数据传输。 3、将一台备库放到远程数据中心,用作灾难恢复。 4、延迟一个或多个备库,以备灾难恢复。 5、使用其中一个备库,作为备份、培训、开发或者测试使用服务器。
对于其中的第4点,我还有一个疑问,如何配置复制延迟,这还得去看看资料。
复制拓扑
关于复制拓扑的选项,我想大部分人还是会选择一主库多备库,或者主动-被动模式下的主-主复制。这两种模式都足够简单,且能够覆盖大多数应用场景。对于一主库多备库这样情况,大家都非常熟悉,在一个规模不大的应用中,一般都会采用这种模式,虽然备库可以扩展读负载,但并不是可以无限制扩展,随着备库越来越多,资源的利用也将越来越低,因为备库除了处理读负载之外,也需要处理写的负载。当语句或数据(取决复制模式)复制到备库时,备库照样需要去执行这些语句或数据,才能将更新作用备库的数据集上。 对于备库为什么不能无限制的扩展读负载,书中也做了详细的说明。
如果当前有一个服务器能支持每秒1000次查询,那么应该增加多少备库才能处理当前两倍的负载,并将所有的读查询分配给备库?
看上去应该增加两个备库并将1600次读操作平分给它们。但是不要忘记,写入负载同样增加到了400次每秒,并且无法在主备服务器之间进行分摊。每个备库每秒必须处理400次写入,这意味着每个备库写入占了40%,只能每秒为600次查询提供服务。因此,需要三台而不是两台备库来处理双倍负载。
对于主动-被动模式下的主-主复制,这种模式并不能扩展写负载,但是有非常多的好处,如下:
这种方式使得反复切换主动和被动服务器非常方便,因为服务器的配置是对称的。这使得故障转移和故障恢复很容易。它也可以让你在不关闭服务器的情况下执行维护、优化表、升级操作系统(或者应用程序、硬件等)或其他任务。
设置主动-被动的主-主拓扑结构在某种意义上类似于创建一个热备份,但是可以使用这个“备份”来提高性能,例如,用它来执行读操作、备份、“离线”维护以及升级等。真正的热备份做不了这些事情。然而,你不会获得比单台服务器更好的写性能。
复制的延迟
如何评估备库的复制容量呢?一是监控你的数据库服务器,并观察备库的复制延迟曲线,二是人为的制造延迟,看看备库多久可以追上主库。
首先应该观察复制延迟的尖刺。如果有复制延迟的曲线图,需要注意到图上的一些短暂的延迟骤升,这时候可能负载加大,备库短时间内无法跟上主库。当负载接近耗尽备库的容量时,会发现曲线上的凸起会更高更宽。前面曲线的上升角度不变,但随后当备库在产生延迟后开始追赶主库时,将会产生一个平缓的斜坡。这些突起的出现和增长是一个警告信息,意味着已经接近容量容量限制。
为了预测在将来的某个时间点会发生什么,可以人为地制造延迟,然后看多久备库能赶上主库。目的是为了明确地说明曲线上的斜坡的陡度。如果将备库停止一个小时,然后开启并在1小时内追赶上,说明正常情况下只消耗了一半的容量。也就是说,如果中午12:00停止备库复制,在1:00开启,并且在2:00追赶上,备库在一小时内完成了两个小时内所有的变更,说明复制可以在双倍速度下运行。
那么如何判断备库已经追上主库了呢,或者说备库延后主库多长时间呢?一个简单的办法,你可以创建一个这样的表,这个表只有两个列,id,创建时间(最好精确毫秒,所以得使用字符串类型来存储),然后你就可以通过存储过程或者客户端程序往此表中插入数据,这样你就可以对比主备库中此表的最后插入数据来判断延迟的时间。另外程序无需一直运行,只需要在要获取主备库的延迟时间时运行,这样就不会占用太多的服务器资源。
如果备库出现过大的延迟时,我们该如何做呢?书中提供一些方法,你可以在备库打开慢查询日志,将复制过来的语句也将记入其中,然后分析慢查询日志;你也可以选择提升硬件;你还可以修改备库上的配置选项,比如关闭二进制日志转储等。
最好的分析办法是暂时在备库上打开慢查询日志记录,然后使用pt-query-digest工具来分析。如果打开了log_slow_slave_statements选项,在标准的MySQL慢查询日志能够记录MySQL5.1及更新的版本中复制线程执行的语句,这样就可以找到在复制时哪些语句执行慢了。
除了购买更快的磁盘和CPU(固态硬盘能够提供极大的帮助),备库没有太多的调优空间。大部分选项都是禁止某些额外的工作以减少备库的负载。一个简单的办法是配置InnoDB,使其不要那么频繁地刷新磁盘,这样事务会提交得更快些。可以通过设置innodb_flush_log_at_trx_commit的值为2来实现。还可以在备库上禁止二进制日志记录,把innodb_locks_unsafe_for_binlog设置为1,并把MyISAM的delay_key_write设置为ALL。但是这些设置以牺牲安全换取速度。如果需要将备库提升为主库,记得把这些选项设置回安全的值。
复制的问题和解决方案
关于复制的问题和解决方案,书中列举十多种,在此我就不一一摘录,下面只列举我认为重要的,或者说我以前没有意识到的,如果想要了解所有的问题及解决方案,请去看复制相关章节。
数据损坏或丢失的错误
如果没有设置主库的sync_binlog选项,就可能在崩溃前没有将最后的几个二进制日志事件刷新到磁盘中。备库I/O线程因此也可一直处于读不到尚未写入磁盘的事件的状态中。当主库重新启动时,备库将重连到主库并再次尝试去读该事件,但主库会告诉备库没有这个二进制日志偏移量。二进制日志转储线程通常很快,因此这种情况并不经常发生。
解决这个问题的方法是指定备库从下一个二进制日志的开头读日志。但是一些日志事件将永久地丢失,建议使用PerconaToolkit中的pt-table-checksum工具来检查主备一致性,以便于修复。可以通过在主库开启sync_binlog来避免事件丢失。
不确定语句
当使用基于语句的复制模式时,如果通过不确定的方式更改数据可能会导致主备不一致。例如,一条带LIMIT的UPDATE语句更改的数据取决于查找行的顺序,除非能保证主库和备库上的顺序相同。例如,若行根据主键排序,一条查询可能在主库和备库上更新不同的行,这些问题非常微妙并且很难注意到。所以一些人禁止对那些会更新数据的语句使用LIMIT。另外一种不确定的行为是在一个拥有多个唯一索引的表上使用REPLACE或者INSERT IGNORE语句——MySQL在主库和备库上可能会选择不同的索引。
丢失的临时表
临时表在某些时候比较有用,但不幸的是,它与基于语句的复制方式是不相容的。如果备库崩溃或者正常关闭,任何复制线程拥有的临时表都会丢失。重启备库后,所有依赖于该临时表的语句都会失败。
如果备库重启后复制因找不到临时表而停止,可能需要做以下一些事情:可以直接跳过错误,或者手动地创建一个名字和结构相同的表来代替消失的临时表。不管用什么办法,如果写入查询依赖于临时表,都可能造成数据不一致。
创建临时表的语句为:CREATET EMPORARY TABLE top_users(…)。
使用非事务型表 这个我也认为相当重要。一般情况下,应该杜绝混合使用InnoDB,MyISAM两种数据库引擎,这样将导致数据库性能优化,服务器参数配置等都更加困难,同时也带来了更多的问题。
例如,假设更新一个MyISAM表的100行数据,若查询更新到了其中50条时有人kill该查询,会发生什么呢?一半的数据改变了,而另一半则没有,结果是复制必然不同步,因为该查询会在备库重放并更新完100行数据(MySQL随后会在主库上发现查询引起的错误,而备库上则没有报错,此后复制将会发生错误并中断)。
如果使用的是MyISAM表,在关闭MySQL之前需要确保已经运行了STOP SLAVE,否则服务器在关闭时会kill所有正在运行的查询(包括没有完成的更新)。事务型存储引擎则没有这个问题。如果使用的是事务型表,失败的更新会在主库上回滚并且不会记录到二进制日志中。