生产问题（九）Mysql8.0 ddl问题

一、引言

        最近线上有个表要加几个字段，提交db单之后执行了一天都没有结束，这个表的容量不大不应该这样。因此作者与DBA进行了深入沟通，了解了集团使用gh-ost更新表结构的过程，问题出在交换表名的过程中。

二、分析

        看的ddl单据卡住了之后，作者看了一下日志，循环播放取消gh-ost的限速设置成功，怎么看都是代码走到死循环里面去了，毕竟我们这边执行ddl的系统是自研的。

         因此作者只能和dba沟通一下了，因为不了解集团这个db系统后台是怎么玩的。

        沟通之后了解到这个系统拿到ddl语句之后会使用gh-ost进行表结构修改，gh-ost是一个开源工具，用于执行DDL（数据定义语言）操作。它可以在不中断服务的情况下执行表结构的更改，例如添加、修改或删除列、索引等。gh-ost使用一种称为"online schema change"的方法，通过复制表并在副本上执行DDL操作，然后将更改应用到主表上，以减少对生产环境的影响。

        首先分析一下这个表结构更新的过程，接下来才能分析问题出在什么地方

1、表结构更新过程

        1、改表结构对于大表来说是非常长的过程，锁表时间很久，所以gh-ost会在主节点建立一个临时副本，把a表里面所有的数据复制过去并且在这个过程中追加a更新的数据，gh-ost会使用MySQL的复制机制将主库上的binlog事件复制到副本上，以确保临时表中的数据与主表中的数据保持一致。

        从库不用管，主库的变化都会从binlog被感知。

        这个过程不是绝对安全的，gh-ost是通过MySQL的复制机制来实现数据的复制和追加的，它无法实时感知到原表的更新操作，为了解决这个问题，gh-ost在复制数据之前会记录原表的binlog位置，并在复制完成后再次检查这个位置。如果原表在复制过程中被更新了，gh-ost会重新执行复制过程，确保副本上的临时表与原表的数据保持一致。

        所以还是要在业务低峰期操作表结构变更。

         2、副本数据一致的时候加锁，Ghost lock是一种特殊的表级别锁，它会锁定主表，基于MySQL的表级别锁机制实现。在执行DDL操作之前，会使用ALTER TABLE语句对主表进行加锁。这个ALTER TABLE语句中使用的锁模式是`LOCK=EXCLUSIVE`，它会锁定整个表，阻止其他会话对表进行写入或修改。

        3、这一步是关键的，交换表名，可以按照下面这个sql理解下，由于已经被锁不会有数据影响

ALTER TABLE `temp_a` RENAME TO `temp_b`, 
`a` RENAME TO `temp_a`, 
`temp_b` RENAME TO `a`;

        最后在把副本删除就可以了

2、MYSQL Error: this authentication plugin is not supported

        其实真正报的错是这个，这个错是当连接到MySQL Docker时，应用程序一直报告错误：这个身份验证插件不受支持。

        查到是default_authentication_plugin配置的身份插件有问题，又碰到这个db实例在阿里云上面，而新版本的MySQL（8.0或以上）将root用户使用的插件更新为caching_sha2_password。种种巧合才触发了这个问题。这个问题是在交换表名的阶段发生的。

解决方案如下：
（1）降级并使用较旧版本的MySQL。
（2）将插件更改为mysql_native_password。

3、为什么交换表名的时候会因为身份验证插件是`caching_sha2_password`，导致一直ddl失败，gh-ost创建副本同步数据就不会发生这个问题？

        爱思考的同学一定想到了，为什么是在交换表名的时候因为身份验证失败，再次之前gh-ost又是创建副本又同步数据又查log的，为什么这些操作没有导致身份验证失败？

        gh-ost创建副本同步数据时不会出现这个问题，是因为在创建副本时，MySQL会将副本的身份验证插件设置为与主库相同的插件。因此，在副本上执行DDL操作时，身份验证插件与主库相同，不会出现身份验证失败的情况。

        而在交换表名时，由于gh-ost需要在主库上执行DDL操作，因此需要使用主库的身份验证插件进行身份验证。如果主库的身份验证插件是`caching_sha2_password`，而gh-ost使用的是旧版本的MySQL客户端，那么就可能会出现身份验证失败的情况。

三、总结

        在使用mysql8.0的时候要注意default_authentication_plugin插件的设置。以上是作者交流和阅读相关资料的总结，有不同想法欢迎讨论。