数据库

解决MySQL主从复制错误1595:Relay log write failure…

先交代下背景:我这边正在研究基于Docker的MariaDB Galera Cluster的自动化集群部署方案【以下简称MGC,后续有时间可以考虑分享下这个方案】,已经完成所有调试,想在生产环境部署一个MGC集群作为从先灰度测试。 生产环境主DB版本为MySQL5.5,新的MGC采用Mariadb最新10.3.12 stable版本,做好MGC集群,并导入一份从主DBdump出来的完整SQL之后,change master 开始创建主从,结果如下报错: 查了下资料,都是是磁盘满导致的,但是我这边磁盘空间、权限都没问题,于是继续检查了下MGC节点日志如下: 里面有一个关键信息:binlog_checksum failed,看来报错和这个有关系了。查了下资料,应该是Mariadb默认打开了 slave_sql_verify_checksum(MySQL版本默认关闭),而主从版本又不一致,导致checksum失败。 解决办法也很简单,在MGC各节点的配置中加上:slave_sql_verify_checksum=0 ,关闭这个特性即可,具体有没有负面影响暂未深入研究。
阅读全文
数据库

MySQL主从报错解决:Table ‘mysql.gtid_slave_pos’ doesn’t exist

给内部一个数据库做异地热备,热备部分采用了MariaDB的galera集群模式。然后挑选其中一台作为Slave和深圳主集群做主从同步。 主集群是老环境,用的版本还是是MySQL 5.5.13。用常规办法创建主从同步 结果有如下报错: 错误信息为:Last_SQL_Error: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist 搜了下资料,大部分说是没有执行mysql_upgrade导致的,不过我们这边的MariaDB是Docker跑的,而且用了很长时间了,理论上应该是没问题的才对。 既然提示没有这个表:Table 'mysql.gtid_slave_pos' doesn't exist,那我就创建一个吧! 从网上找到这个建表语句: 在作为Slave的MariaDB上执行,然后重启slave后问题解决,过程如下:
阅读全文
数据库

MySQL在线DDL修改表结构的简单经验分享

在线DDL修改生产环境的大表一直是运维、DBA一个很头痛的问题,本文分享一些相关经验,希望对还在头痛的同学能有所帮助,当然更希望路过的大神,如果有更靠谱的方案能够指点一二,不吝赐教。
阅读全文
数据库

gh-ost:在线DDL修改MySQL表结构工具

在之前,我分享过一次pt-online-schema-change在线DDL的工具实践记录,在实际使用过程中,发现部门的很多老系统大量使用了触发器,从而无法使用这个工具,非常遗憾!导致很多DDL变更都必须压到空闲时候做,比如凌晨,非常苦逼。 咨询了做DBA的老同事,他将gh-ost推荐给我,基于golang语言,是github开源的一个DDL工具,gh-ost是gitHub,s Online Schema Transmogrifier/Transfigurator/Transformer/Thingy的缩写,意思是GitHub的在线表定义转换器。============  分割线之间内容摘自网络  ============ gh-ost有如下特点: 无触发器 轻量级 可暂停 动态可控 可审计 可测试 可靠 无触发器gh-ost不使用触发器,它跟踪二进制日志文件,在对原始表的修改提交之后,用异步方式把这修改内容应用到临时表中去。 gh-ost希望二进制文件使用基于行的日志格式,但这并不表示如果主库上使用的是基于语句的日志格式,就不能用它来在线修改表定义了。事实上,我们常用的方式是用一个从库把日志的语句模式转成行模式,再从这个从库上去读日志。搭一个这样的从库并不复杂。 轻量级 因为不需要使用触发器,gh-ost把修改表定义的负载和正常的业务负载解耦开了。它不需要考虑被修改的表上的并发操作和竞争等,这些在二进制日志中都被序列化了,gh-ost只操作临时表,完全与原始表不相干。事实上,gh-ost也把行拷贝的写操作与二进制日志的写操作序列化了,这样,对主库来说只是有一条连接在顺序的向临时表中不断写入数据,这样的行为与常见的ETL相当不同。 可暂停 因为所有写操作都是gh-ost生成的,而读取二进制文件本身就是一个异步操作,所以在暂停时,gh-ost是完全可以把所有对主库的写操作全都暂停的。暂停就意味着对主库没有写入和更新。不过gh-ost也有一张内部状态跟踪表,即使在暂停状态下也会向那张表中不断写入心跳信息,写入量可以忽略不计。 gh-ost提供了比简单的暂停更多的功能,除了暂停之外还可以做: 负载:与pt-online-schema-change相近的一个功能,用户可以设置MySQL指标的阈值,比如设置Threads_running=30。 复制延迟:gh-ost内置了心跳功能来检查复制延迟。用户可以指定查看哪个从库的延迟,gh-ost默认是直接查看它连上的那个从库。 命令:用户可以写一些命令,根据输出结果来决定要不要开始操作。比如:SELECT HOUR(NOW()) BETWEEN 8 and 17. 上述所有指标即使在修改表定义的过程中也可以动态修改。 标志位文件:生成一个标志位文件,gh-ost就会立刻暂停。删除文件,gh-ost又会恢复工作。 用户命令:通过网络连上gh-ost,通过命令让它暂停。 动态可控如果别的工具在修改过程中产生了比较高的负载,DBA只好把它停掉再修改配置,比如把一次拷贝的数据量改小些,然后再从头开始修改过程。这样的反复操作代价非常大。 gh-ost通过监听TCP或者unix socket文件来获取命令。即使有正在进行中的修改工作,用户也可以向gh-ost发出命令修改配置,比如可以这样做: echo throttle | socat - /tmp/gh-ost.sock:这是暂停命令。也可以输入no-throttle 修改运行参数,gh-ost可以接受这样的修改方式来改变它的行为:chunk-size=1500, max-lag-millis=2000, max-load=Thread_running=30 可审计用上面所说的相同接口也可以查看gh-ost的状态,查看当前任务进度、主要配置参数、相关MySQL实例的情况等。这些信息通过网络发送命令就可以得到,因此就给了运维人员极大的灵活性,如果是使用别的工具的话一般只能是通过共享屏幕或者不断跟踪日志文件最新内容。 可测试 读取二进制文件内容的操作完全不会增加主库的负载,在从库上做修改表结构的操作也和在主库上做是非常相象的(当然并不完全一样,但主要来说还是差不多的)。 gh-ost自带了--test-on-replica选项来支持测试功能,它允许你在从库上运行起修改表结构操作,在操作结束时会暂停主从复制,让两张表都处于同步、就绪状态,然后切换表、再切换回来。这样就可以让用户从容不迫地对两张表进行检查和对比。 我们在GitHub是这样在生产环境测试gh-ost的:我们有许多个指定的生产从库,在上面不提供服务,只是周而复始地不断地把所有表定义都改来改去。对于我们生产环境地每一张表,小到空表,大到几百GB,都会通过修改存储引擎的方式来进行修改(engine=innodb),这样并不会真正修改表结构。在每一次这样的修改操作最后我们都会停掉主从复制,再把原始表和临时表的全量数据都各做一次校验和,然后比较两个校验和,要求它们是一致的。然后我们恢复主从复制,再继续测试下一张表。我们生产环境的每一张表都这样用gh-ost在从库上做过好多次修改测试。 可靠的 所有上述讲到的和没讲到的内容,都是为了让你对gh-ost的能力建立信任。毕竟,大家在做这件事的时候已经使用类似工具做了好多年,而gh-ost只是一个新工具。 我们在从库上对gh-ost进行测试,在去主库上做第一次真正改动之前我们在从库上成功地试了几千次。所以,请你也在从库上开始测试,验证数据是完好无损的,然后再把它用到生产环境。我们希望你可以放手去试。 当你执行了gh-ost之后,也许你会看见主库的负载变高了,那你可以发出暂停命令。用echo throttle命令生成一个文件,看看主库的负载会不会又变得正常。试一下这些命令,你就可以知道你可以怎样控制它的行为,你的心里就会安定许多。 你发起了一次修改操作,然后估计完成时间是凌晨2点钟,可是你又非常关心最后的切换操作,非常想看着它切换,这可怎么办?只需要一个标志位文件就可以告诉gh-ost推迟切换了,这样gh-ost会只做完拷贝数据的操作,但不会切换表。它还会仍然继续同步数据,保持临时表的数据处于同步状态。等第二天早上你回到办公室之后,删除标志位文件或者向gh-ost发送命令echo unpostpone,它就会做切换了。我们不希望软件强迫我们看着它做事情,它应该把我们解放出来,让人去做人该做的事。 谈到估计完成时间,--exact-rowcount选项非常有用。在最开始时要在目标表上做个代价比较大的SELECT COUNT(*)操作查出具体要拷多少行数据,gh-ost就会对它要做多少工作有了一个比较准确的估计。接下来在拷贝的过程中,它会不断地尝试更新这个估计值。因为预计完成的时间点总是会不断变化,所以已经完成的百分比就反而比较精确。如果你也曾经有过非常痛苦的经历,看着已经完成99%了可是剩下的一点操作却继续了一个小时也没完,你就会非常喜欢我们提供的这个功能。 gh-ost工作模式【要着重看】gh-ost工作时可以连上多个MySQL实例,同时也把自己以从库的方式连上其中一个实例来获取二进制日志事件。根据你的配置、数据库集群架构和你想在哪里执行修改操作,可以有许多种不同的工作模式。 模式一、连上从库,在主库上修改 这是gh-ost默认的工作模式,它会查看从库情况,找到集群的主库并且连接上去。修改操作的具体步骤是: 在主库上读写行数据; 在从库上读取二进制日志事件,将变更应用到主库上; 在从库上查看表格式、字段、主键、总行数等; 在从库上读取gh-ost内部事件日志(比如心跳); 在主库上完成表切换; 如果主库的二进制日志格式是Statement,就可以使用这种模式。但从库就必须配成启用二进制日志(log_bin, log_slave_updates),还要设成Row格式(binlog_format=ROW),实际上gh-ost会在从库上帮你做这些设置。 事实上,即使把从库改成Row格式,这仍然是对主库侵入最少的工作模式。 模式二、直接在主库上修改 如果没有从库,或者不想在从库上操作,那直接用主库也是可以的。gh-ost就会在主库上直接做所有的操作。仍然可以在上面查看主从复制延迟。 主库必须产生Row格式的二进制日志; 启动gh-ost时必须用--allow-on-master选项来开启这种模式; 模式三、在从库上修改和测试 这种模式会在从库上做修改。gh-ost仍然会连上主库,但所有操作都是在从库上做的,不会对主库产生任何影响。在操作过程中,gh-ost也会不时地暂停,以便从库的数据可以保持最新。 --migrate-on-replica选项让gh-ost直接在从库上修改表。最终的切换过程也是在从库正常复制的状态下完成的。 --test-on-replica表明操作只是为了测试目的。在进行最终的切换操作之前,复制会被停止。原始表和临时表会相互切换,再切换回来,最终相当于原始表没被动过。主从复制暂停的状态下,你可以检查和对比这两张表中的数据。 三种模式各有优缺点,但我只关心缺点:先说模式一的缺点,模式一会在从DB上面读取binlog,可能造成数据库主从数据不一致,原因因为是主库的binlog没有完全在从库执行。所以个人感觉模式一有丢失数据的风险。 模式二任何操作都会在主库操作,或多或少会对主库负载造成影响,但是可以通过调整一些参数降低和时刻关注这些影响,所以个人推荐使用模式二。 至于模式三是偏向测试用的,这里不做过多介绍,但是模式三里有一个细节,cut-over阶段有会stop slave一个操作,其实这个操作风险特别高,有时stop slave 时间会很长,务必会对线上数据库使用造成影响,所以如果使用模式三做测试也要在线下数据库。 gh-ost在GitHub的应用现在GitHub生产环境的表修改操作全都是用gh-ost完成的。每天只要有需求来了,就将它运行起来,有时候一天会做好多次。因为它有审计和控制功能,所以我们还可以把它和我们的Chatops系统整合起来。技术人员可以对它的工作进度有非常清晰的了解,因此可以控制它的行为。在生产环境中各种指标和事件都被收集起来,让大家可以以图形化的方式看到操作情况。 ============  分割线之间内容摘自网络  ============ 下面分享gh-ost的测试使用: 1、下载程序 一直都非常喜欢go语言编写的程序,原因很单纯,大部分情况都是一个二进制就能解决问题了,无任何依赖,简直是IT界的一股清流! 从github发布地址下载最新的binary包:https://github.com/github/gh-ost/releases 解压后就一个 gh-ost 二进制文件,再次献上我坚实的膝盖。 2、常用参数 --max-load 执行过程中,gh-ost会时刻关注负载情况,负载阀值是使用者自己定义,比如数据库的最大连接数,如果超过阀值,gh-ost不会退出,会等待到负载在阀值以下继续执行。 --critical-load 这个指的是gh-ost退出阀值,当负载超过这个阀值,gh-ost会停止并退出 --chunk-size 迁移过程是一步步分批次完成的,这个参数是指事务每次提交的行数,默认是1000。 --max-lag-millis 会监控从库的主从延迟情况,如果延迟秒数超过这个阀值,迁移不会退出,等待延迟秒数低于这个阀值继续迁移。...
阅读全文
数据库

MySQL命令行工具:percona-toolkit安装使用初探

导读:percona-toolkit源自Maatkit 和Aspersa工具,这两个工具是管理mysql的最有名的工具,现在Maatkit工具已经不维护了,请大家还是使用percona-toolkit吧!这些工具主要包括开发、性能、配置、监控、复制、系统、实用六大类,作为一个优秀的DBA,里面有的工具非常有用,如果能掌握并加以灵活应用,将能极大的提高工作效率。 一、 工具简介 percona-toolkit是一组高级命令行工具的集合,用来执行各种通过手工执行非常复杂和麻烦的mysql任务和系统任务,这些任务包括: 检查master和slave数据的一致性 有效地对记录进行归档 查找重复的索引 对服务器信息进行汇总 分析来自日志和tcpdump的查询 当系统出问题的时候收集重要的系统信息 二、工具安装 ①、下载安装包 Percona-toolkit安装包: 最新rpm包:http://percona.com/get/percona-toolkit.rpm 最新编译包:http://percona.com/get/percona-toolkit.tar.gz (本文略)   Perl-TermReadKey安装包: 访问:http://pkgs.repoforge.org/perl-TermReadKey/ 下载最新rpm安装包 ②、安装记录 若发现缺失组件,则使用yum在线安装即可:  yum install -y perl-IO-Socket-SSL   成功安装后,系统会多出如下pt命令: 三、使用初探 pt有很多实用功能,但我就是奔着alter不锁表去的,所以率先测试 pt-online-schema-change 这个字段属性修改工具。 pt-online-schema-change ①、功能介绍 如果没有这个工具,直接使用alter修改字段,过程如下: 按照原始表(original_table)的表结构和DDL语句,新建一张不可见的临时表; 在原表上加write lock,此时对原表的所有U D I DDL 都是阻塞的; 执行insert into tmp_table select * from oldtable; rename oldtable 和 tmp_table,再drop oldtable; 释放 write lock。 为了避免锁表,该换 pt-online-schema-change 出马了! pt-online-schema-change 的 工作过程解析如下: 如果存在外键,根据alter-foreign-keys-method参数的值,检测外键相关的表,做相应设置的处理。 创建一个和你要执行 alter 操作的表一样的新的空表结构(是alter之前的结构)。 在新表执行alter table 语句, 在原表中创建触发器(3个)三个触发器分别对应insert,update,delete操作 从原表拷贝数据到临时表,拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表。 Rename 原表到old表中,在把临时表Rename为原表,默认最后将原表删除,将原表上所创建的触发器删除。 因此,这个工具有如下限制: 表必须是单一列的主键或者单一唯一键。 若表存在外键,则需要指定参数 --alter-foreign-keys-method=auto|rebuild_constraints|drop_swap|none,而且外键不能有错误 表上不能有触发器,否则修改失败。如果有触发器,需要先删除触发器再执行PT,最后再增加触发器。 ②、demo ③、测试记录 在测试数据库上随便导入了一份现网的数据,居然就让我碰到了触发器和外键,人品不错! 测试数据,本机无密码,所以直接执行: 结果如下报错: 对象表存在触发器!于是,将其中某个表的触发器全部删除,继续执行,又发现如下错误: 对象表存在外键! 于是加入 –alter-foreign-keys-method=drop_swap 参数成功执行: 新增字段成功: 四、教程分享 除此之外,percona-toolkit 还有很多实用功能,比如: pt-config-diff : 比较本地与远程MySQL配置文件差异 pt-heartbeat : MySQL主从复制延迟监控 pt-slave-find : 查找和打印所有从服务器复制层级关系 pt-table-checksum : 主从复制一致性检查...
阅读全文
数据库

MySQL主从、字典死锁、连接数的Python监控脚本

最近,公司的系统在天津增加了不少异地容灾,其中就有异地双主MySQL。由于容灾环境一般只会在出现较大的网络故障或故障演习的时候用到,所以,容灾系统的可用性就只能靠监控了。 对于互联网这种赶鸭式的发展模式,很多公司都没有完善的自动化运维系统。所以,很多时候还得靠自己写脚本。 脚本越写越多,就容易出现混乱、分散难管理的窘状。所以,这次就写了一个MySQL的集中监控脚本,后续有新的监控加入也可以非常方便的拓展。 Python 脚本: 用法很简单,只要在脚本同目录新增一个文本文件hosts.list,一行一个IP,然后需要在IP对应的MySQL身上新增一个 db_monitor账号,用于监控机远程登录MySQL(见脚本注释部分语句): 其他特性: ①、脚本支持 5.0 和 5.5+ MySQL版本的 Second behid master 监控,因为新老版本这个参数的位置不一样(鹅厂很多开源软件是非常陈旧的。。。); ②、支持MySQL连接失败的监控; ③、支持MySQL主从复制的延迟和错误监控 ④、支持MySQL的连接数和字典锁数监控 ⑤、更多监控可以参考继续添加..... 好了,就这么多了,算是给自己一个备忘。
阅读全文