国产化改造之Mysql迁移方案：Mysql Galera Cluster

一、背景

因某业务系统OS国产化改造，现需将生成环境Mysql 主从迁移到新部署的BCLinux OS主机上；如果保障业务不断，平滑迁移并成功割接将是本次方案的重要方向，现场环境涉及需迁移数据780G左右，目标主机OS版本：BC-Linux for Euler 22.10 64位，官网显示兼容大部分应用，现场测已兼容：mysql、es、redis、nginx、flink、clickhouse、kafka等应用。回归正题，对于Mysql主从集群，采用新增从节点方式，实现平滑迁移。再通过Mysql Galera Cluster方案，实现数据同步过程中的数据强一致性和较好性能。

Galera Cluster是集成了Galera插件（支持多点写入的同步通信模块）的MySQL集群方案，是一个基于MySQL InnoDB同步复制，一种新型的，数据不共享的，高度冗余的高可用方案，目前Galera Cluster有两个版本，分别是Percona Xtradb Cluster及MariaDB Cluster，都是基于Galera的实现。Galera本身是具有多主特性的，即采用multi-master的集群架构，是一个既稳健，又在数据一致性、完整性及高性能方面有出色表现的高可用解决方案。一般Mysql Galera Cluster采用3节点部署，这三个节点与普通的主从架构不同，它们都可以作为主节点，三个节点是对等的，即 multi-master 架构，当有客户端要写入或者读取数据时，随便连接哪个实例都是一样的，读到的数据是相同的，写入某一个节点之后，集群自己会将新数据同步到其它节点上面，他们不共享任何数据，但能保证数据强一致性。架构示例如下：

Galera Cluster特点：

1、多主架构：真正的多点读写的集群，在任何时候读写数据，都是最新的同步复制：集群不同节点之间数据同步没有延迟，在数据库挂掉之后，数据不会丢失
2、并发复制：从节点在 APPLY 数据时支持并行执行，有更好的性能表现
3、故障切换：在出现数据库故障时，因为支持多点写入，切换非常容易
4、热插拔：在服务期间，如果数据库挂了，只要监控程序发现的够快，不可服务时间就会非常少，在节点故障期间，节点本身对集群的影响非常小
5、自动节点恢复：在新增节点或者停机维护时，增量数据或者基础数据不需要人工手动备份提供，Galera Cluster 会自动拉取在线节点数据，最终集群会变为一致

资源链接：galeracluster官网、

二、Mysql Galera Cluster部署配置

1） 原理：

使用 Galera Cluster 时，应用程序可以直接读、写某个节点的最新数据，并且可以在不影响应用程序读写的情况下，下线某个节点，因为支持多点写入，使得 Failover 变得非常简单。所有的 Galera Cluster 都是对 Galera 所提供的接口 API 做了封装，这些 API 为上层提供了丰富的状态信息及回调函数，通过这些回调函数，做到了真正的多主集群，多点写入及同步复制，这些 API 被称作是 Write-Set Replication API，简称为 wsrep API。通过这些 API，Galera Cluster 提供了基于验证的复制，是一种乐观的同步复制机制，一个将要被复制的事务（称为写集），不仅包括被修改的数据库行，还包括了这个事务产生的所有 Binlog，每一个节点在复制事务时，都会拿这些写集与正在 APPLY 队列的写集做比对，如果没有冲突的话，这个事务就可以继续提交或者是 APPLY，此时这个事务就被认为是提交了，然后在数据库层面还需要继续做事务上的提交操作。这种方式的复制也被称为是虚拟同步复制，实际上是一种逻辑上的同步，因为每个节点的写入和提交操作还是独立的，更准确的说是异步的，Galera Cluster 是建立在一种乐观复制的基础上的。假设集群中的每个节点都是同步的，那么在写入时都会做验证，理论上是不会出现不一致的，当然也不能这么乐观，如果出现不一致了，比如主库（相对）插入成功，而从库则出现主键冲突，那说明此时数据库已经不一致，这种时候 Galera Cluster 采取的方式是将出现不一致数据的节点踢出集群，其实是自己 shutdown 了。而使用 Galera 通过判断键值的冲突方式实现了真正意义上的 multi-master。

Galera Cluster依靠一个全局的事务id, 整个集群环境, 每个事务的事务id都是唯一的. 客户端连接到一台主服务器上做更改, 提交请求后会在当前连接的主服务器上做检查, 如果失败, 直接返回更新失败, 如果成功, 会把修改操作发给其他主节点, 如果发生冲突, 那么操作就会取消, 返回失败信息. 这样确保所有的服务器都会执行正确的指令, 不会出现冲突因此, 所有节点都要做检查就会造成效率问题, 延迟问题. 因此, galera cluster的集群有数量限制, 主节点过多或造成效率过低.

galera cluster有多种实现方案, 其底层都是基于两个组件

Galera replication library
WSREP: MySQL extended with the Write Set Replication

WSREP复制实现的不同方案

PXC: Percona XtraDB Cluster, 是Percona 对 Galera的实现
MariaDB Galera Cluster, 是MariaDB 对 Galera的实现

上述两者都需要至少三个节点, 不能安装mysql server或者mariadb server, 因为Galera Cluster是独立专用的数据库版本

2）Galera Cluster 每一个阶段以及其并发控制行为过程

1、本地执行：这个阶段是事务执行的最初阶段，可以说这个阶段的执行过程与单点 MySQL 执行没什么区别，并发控制就是数据库的并发控制，而不是 Galera Cluster 的并发控制
2、写集发送：在执行完之后就到了提交阶段，提交之前首先将产生的写集广播出去，为了保证全局数据的一致性，在写集发送时需要串行，这就属于 Galera Cluster 并发控制的一部分了
3、写集验证：这个阶段就是 Galera Cluster 的验证，验证是将当前的事务与本地写集验证缓存集来做验证，通过比对写集中被影响的数据库 KEYS来发现有没有相同的来确定是不是可以验证通过，这个过程也是串行的
4、写集提交：这个阶段是一个事务执行时的最后一个阶段，验证完成之后就可以进入提交阶段，因为此时已经执行完了而提交操作的并发控制是可以通过参数来控制其行为的，即参数 repl.commit_order。如果设置为3，表示提交是串行的，而这也是推荐的（默认值）的一种设置，因为这样的结果是集群中不同节点产生的 Binlog 是完全一样的，给运维带来了不少好处和方便
5、写集 APPLY：这个阶段与上面几个阶段在流程上不太一样，这个阶段是从节点做的事情，从节点只包括两个阶段，即写集验证和写集 APPLY，写集 APPLY 的并发控制与参数 wsrep_slave_threads 有关系，本身在验证之后确定相互依赖关系，如果确定没有关系就可以并行，而并行就是参数 wsrep_slave_threads 的事情，可以参照参数 wsrep_cert_deps_distance 来设置。

三、新增从节点加入上述集群

四、主从同步配置

五、主从切换

六、业务验证及割接

七、Galera Cluster的坑

1）大表DDL操作会导致整个集群不可用。

在DDL操作完成前集群都不可写入任何事务，导致服务不可用。

解决方案：可直接使用pt-online-schema工具进行操作，以避开这个问题。

2）由于Galera Cluster在执行DDL时，是Total Ordered Isolation(wsrep_OSU_method=TOI)的，所以必须要保证每个节点都是同时执行的，当然对于不是DDL的，也是Total Order的，因为每一个事务都具有同一个GTID值，DDL也不例外，而DDL涉及到的是表锁，MDL锁(Meta Data Lock)，只要在执行过程中，遇到了MDL锁的冲突，所有情况下，都是DDL优先，将所有使用到这个对象的事务，统统杀死，不管是读事务，还是写事务，被杀的事务都会报出死锁的异常，这也是一个Galera Cluster中关于DDL的闻名遐迩的坑。

解决方案：可以使用滚动升级方式在每个节点上分别执行DDL操作，这样就可以避开上面的问题。操作如下：

SET wsrep_OSU_method=‘RSU’;
ALTER TABLE test ADD COLUMN user_age tinyint;
SET wsrep_OSU_method=‘TOI’;

即先在节点上修改实例升级方式为滚动升级(Rolling Schema Upgrade)，然后再执行DDL语句，最后再将实例升级方式修改回去。

3）相关研究表明：新的Mysql高可用解决方案MySQL Group Replication GA（ MGR） 比Galera性能更好，问题也会更少

MGR是MySQL官方推出的高可用解决方案，基于原生复制技术，并以插件的方式提供。其包含下面的特性：

复制的管理操作变得更为自动化，还在Backup + CHANGE MASTER建复制你就out了；

通过Paxos协议提供数据库集群节点数据强一致保证，扫清了MySQL进入金融行业最后的障碍。打脸了淘宝阳振坤老师对于MySQL无法支持强一致的论调；

集群间所有节点可写入，解决了单个集群的写入性能，所有节点都能读写，不过现实还是有些残酷；

八、生产案例

8.1 在移动的应用

相关案例表明，移动生产上使用了MySQL Galera集群，其中，MySQL采用的是Percona的一个MySQL分支版本，PerconaXtradb Cluster，简称PXC，这是一个可以实时同步的MySQL集群，基于广播write set和事务验证来实现多节点同时commit，冲突事务回滚的功能。强数据一致性保证。Galera是同步复制（虚拟同步）方案，事务在本地节点（客户端提交事务的节点）上提交成功时，其它节点保证执行该事务。在提交事务时，本地节点把事务复制到所有节点，之后各个节点独立异步地进行certification test、事务插入待执行队列、执行事务。然而，由于不同节点之间执行事务的速度不一样，长时间运行后，慢节点的待执行队列可能会越积越长，最终可能导致事务丢失。Galera内部的flow control实现，作用就是协调各个节点，保证所有节点执行事务的速度大于队列增长速度，从而避免丢失事务。整个Galera Cluster中，同时只有一个节点可以广播消息（数据），每个节点都会获得广播消息的机会（获得机会后也可以不广播），当慢节点的待执行队列超过一定长度后，它会广播一个FC_PAUSE消息，所以节点收到消息后都会暂缓广播消息，直到该慢节点的待执行队列长度减小到一定长度后，Galera Cluster数据同步又开始恢复。

Galera Replication原理总结：

1. 事务在本地节点执行时采取乐观策略，成功广播到所有节点后再做冲突检测；
   \
2. 检测出冲突时，本地事务优先被回滚；
   \
3. 每个节点独立、异步执行队列中的WS；
   \
4. 事务T在A节点执行成功返回客户端后，其他节点保证T一定会被执行，因此有可能存在延迟，即虚拟同步。

他们之前出过一个node宕机的故障，执行show global status like ‘Threads_running’情况发现Threads_running线程不断增高；show processlist发现存在很多线程停在wsrep in pre-commit stage状态(这意味着很多线程已经在节点发出commit，但将该SQL发送到其他节点时处于独立异步地进行certification test、事务插入待执行队列的状态），线程都停留在wsrep in pre-commit stage状态；执行show global status like ‘%wsrep%’，查看状态情况，未发现其余2哥节点接受队列并无阻塞，也没有发出流控。但wsrep_evs_delayed报节点3的4567端口连接延迟。查看节点1、2错误日志发现报“WSREP: (40a252ac, ‘tcp://节点2:4567’) reconnecting to 67f667d2 (tcp://节点3:4567), attempt 0”。查看节点3的错误日志与wsrep_evs_delayed运行参数则报相反信息，连接节点1和节点2的4567端口延迟。其中，4567端口的作用(wsrep_provider_options中的gmcast.listen_addr项）：主要作用是集群内监听组员状态，组员之间的通信(握手，鉴权，广播，写入集的复制)。综上，本次故障就是因为节点3与节点1、2的4567端口一直连接有延迟，所以在节点1、2执行的请求无法及时的复制给节点3执行，导致节点1、2的活跃线程一直执行不完。而延迟原因大概率是网络原因；排查网络传输过程的问题即可。修复期间，因2个节点会导致脑裂，每个节点执行mysqld_safe –wsrep-recover命令找出最新事务号节点作为主节点启动，并在故障期间保持单节点运行。待网络故障消除后，逐一启动节点2、3，系统数据库集群恢复正常。注意：当3节点宕掉其中一个时，应该及时地关闭剩余2个节点中的一个节点，让业务只跑在单节点上，还能避免出现脑裂的情况，导致整体mysql不可用。另外，可将wsrep_evs_delayed作为一个监控项进行监控，及时关注集群检通信问题。

九、迁移工具及案例

1）ium是一款强大的数据库管理工具，支持多种数据库管理，包括MySQL、Oracle、SQL Server、PostgreSQL等。如数据可视化、数据同步、数据备份、数据传输等，chdowsux和Mac OS X等。如数据库设计、数据建模、数据管理、SQL开发等，

2）dbForge Studio for MySQL

dbForge Studio for MySQL是一款专业的MySQL数据库管理工具，支持多种功能，如数据库设计、数据建模、数据管理、数据同步、数据备份等。它

3）Toolkit

Toolkit是MySQL官方推出的一款数据库迁移工具，支持多种数据库之间的转换，包括MySQL、Oracle、SQL Server、PostgreSQL等。如数据传输、数据转换、数据同步等

4）Navicat Premium
Navicat Premium 是一套多连接数据库开发工具，让你在单一应用程序中同时连接多种类型的数据库：MySQL、MariaDB、MongoDB、SQL Server、SQLite、Oracle 和 PostgreSQL，可一次快速方便地访问所有数据库。它也自带里数据库迁移能力，打开【工具】->【传输工具】如下所示：

上述功能涉收费，另外迁移表结构的时候，有时候会报错；

5）DataX

它是阿里云DataWorks数据集成的开源版本，在阿里巴巴集团内被广泛使用的离线数据同步工具/平台。DataX 实现了包括 MySQL、Oracle、OceanBase、SqlServer、Postgre、HDFS、Hive、ADS、HBase、TableStore(OTS)、MaxCompute(ODPS)、Hologres、DRDS 等各种异构数据源之间高效的数据同步功能。它需要JDK(1.8以上，推荐1.8)、Python(2或3都可以)、Apache Maven 3.x (Compile DataX)依赖；介质下载后，执行：tar -zxvf datax.tar.gz；验证环境：python ./datax/bin/datax.py ./datax/job/job.json；查看对应编写迁移模板：python ./datax/bin/datax.py -r postgresqlreader -w mysqlwriter；

Datax主要是做数据同步的，在数据迁移方面有诸多不方便的地方，比如Datax是用多个task来实现数据同步的，每个task需要手动编写 json ，并指定表名与同步的字段名。如果数据迁移的表与字段太多，task的配置就是非常耗时的一件事情，这对于数据迁移来说实在太麻烦。因此，它对数据同步友好，但对数据迁移并不友好。

6）TurboDX

TurboDX for MySQL 专门针对兼容MySQL路线的数据库作为目标库的实时同步工具软件，支持Oracle(RAC)、SQLServer、MySQL、PostgreSQL、DB2、Informix等，全量+增量实时同步到MySQL/TiDB/Oceanbase/TDSQL/GlodenDB/SequoiaDB/
GreatDB/HotDB等。

我们可在一台Windows上下载数据迁移⼯具（TurboDX for MySQL），并完成安装；服务界面，按顺序启动TurboDXDB、TurboDX Server、TurboDXWEB 三项服务，浏览器本地访问：http://127.0.0.1:8422/turbodx，进入TurboDX 控制中心，默认登录用户密码为 admin/admin，配置好源和目标数据库后，配置迁移任务，如下所示：



完成后，启动迁移任务：

注意：它能实现全库全表迁移，也能自由过滤指定表迁移；迁移不需要人为干预，功能齐全；表结构迁移与数据迁移是分开的两个功能，可以单独操作。社区版需要使用Windows，但Linux版本需要联系官方索要。

7）案例1：基于Binlog的数据迁移，即Binlog实时采集 + 离线还原的解决方案；

只导出数据：mysqldump -uroot -p -t dbname > dbname.sql
只导出表结构：mysqldump -uroot -p -d dbname > dbname.sql
使用MySQL的SELECT INTO OUTFILE 、LOAD DATA INFILE快速导出导入数据;LOAD DATA INFILE语句从一个文本文件中以很高的速度读入一个表中。MySQL官方文档也说明了，该方法比一次性插入一条数据性能快20倍。
MySQL 5.7 全库备份时不会备份 mysql.proc 下的系统自身的存储过程，可以在执行完导入后先执行一次升级。

#执行升级
mysql_upgrade --upgrade-system-tables --skip-verbose --force

#!/bin/bash
while read line
do mysql -uroot -p12345678 database -e "LOAD DATA INFILE '/var/lib/mysql-files/$line.txt' INTO TABLE $line FIELDS TERMINATED BY ','"
done < tables.txt

说明：MYSQL导入数据出现The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
原因:MYSQL限制了导入与导出的目录权限；解决办法:

1.show variables like ‘%secure%’;
查看 secure-file-priv 当前的值是什么,将导出文件目录设置成secure-file-priv的值
2.修改配置可修改mysql配置文件
查看是否有secure_file_priv = 这样一行内容，如果没有，则手动添加，secure_file_priv = /home 表示限制为/home文件夹
3.其中secure_file_priv = 表示不限制目录，等号一定要有，否则mysql无法启动修改完配置文件后

8）案例2：通用binlog日志迁移步骤

MySQL中，binlog作为一种二进制日志文件，它记录了MySQL服务器执行的所有操作。对于每个修改操作，MySQL服务器将在binlog文件中写入一个条目。通过查看binlog文件，就可以了解修改操作的详细信息。基于这一特性，我们就可以利用binlog进行数据迁移，然后离线重新读取加载。

1、确认旧服务器上启用binlog，然后从旧服务器上导出binlog文件。执行：mysqlbinlog /var/lib/mysql/mysql-bin.000001 > binlog.sql，它会将binlog文件导出为文本文件binlog.sql；

2、新服务器上导入binlog文件：将binlog.sql文件从旧服务器移动到新服务器。将binlog.sql文件导入新服务器，执行：mysql> SOURCE /path/to/new_database.sql;执行后，将在新服务器上创建数据库，并将从binlog文件中导入的数据放入该数据库。

9）案例3：直接将原数据库Mysql的数据文件和库表结构文件直接拷贝到新服务器，挂载到同样配置的MySQL服务下。这种方案简单，时间占用短，文件可断点传输，操作步骤少。缺点：新旧服务器中MySQL版本及配置必须相同，可能引起未知问题。

一、把目标机器b的mysql停掉。
二、把源机器a上要迁移的库的整个目录复制到机器b的mysql data目录下。
三、修改目录权限为700，修改文件权限为660，并修改他们的所属用户和所属组为mysql。
四、到机器b上，刚才建的那个数据库的目录下，把所有的(.ibd)文件删除掉。
五、把机器a上，对应数据库目录下所有的(.ibd)文件复制到机器b上，修改文件的权限。
六、再启动机器b的mysql。

其中，show databases和show tables时，mysql其实是去目录下扫描，但执行select这些操作的时候，mysql优化器会去information_schema.TABLES 这个表里面获取信息。由于我们是直接复制文件过去，所以，这个表里面是没有信息的，所以就会提示表不存在。 第4、5步骤中就是使用正确的文件对应关系；也可参考如下：

alter table matlab DISCARD TABLESPACE;    //吧上备份数目录后，执行移除表空间，即与ibd的对应关系
show global variables like '%datadir%';
#将备份的/迁移的ibd文件，放到mysql->data->创建的数据库名称->下,即上面的目录下
alter table matlab IMPORT TABLESPACE;      //重建或重新导入表空间

Innodb存储文件分为：.frm，.idb
.frm：存储表定义
.ibd：存储数据和索引

MyISAM存储文件分为：.frm，.myd，.myi
.frm：存储表定义
.myd：存储数据
.myi：存储索引

10）Mysql自带迁移：

MySQL Replication是指将一个MySQL服务器上的数据自动同步到另一个MySQL服务器上的过程。MySQL Replication可以实现数据实时备份、数据实时转移等多种功能。创建主从同步指向主的过程。

11）mysqlimport

mysqlimport是将文本文件中的数据导入到MySQL数据库中的命令，也可以将数据迁移到另一个MySQL服务器。示例如下：

mysqlimport -u username -p --host=newserver --fields-terminated-by=',' dbname /path/to/backupfile.sql

12）案例4：新增实例进行迁移

A：业务 A 旧实例与新实例建立同步复制

#设置旧实例为只读库，防止数据写入
show global variables like '%read_on%';
set global super_read_only=1;
show global variables like '%read_on%';
#新实例的主库执行，建立旧实例到新实例的复制
CHANGE MASTER TO MASTER_HOST='10.186.60.201',
MASTER_USER='repl',
MASTER_PORT=3307,
MASTER_PASSWORD='repl',
MASTER_AUTO_POSITION = 1;
start slave;

B：业务 B 与 C 需要合并，所以这次保证数据实时同步采用了多源复制的方式。

#在新实例的主库执行，将业务B与C的数据都复制到新实例中。
CHANGE MASTER TO MASTER_HOST='10.186.60.209',
MASTER_USER='repl',
MASTER_PORT=3307,
MASTER_PASSWORD='repl',
MASTER_AUTO_POSITION = 1 FOR CHANNEL 'channel1';CHANGE MASTER TO MASTER_HOST='10.186.60.210',
MASTER_USER='repl',
MASTER_PORT=3307,
MASTER_PASSWORD='repl',
MASTER_AUTO_POSITION = 1 FOR CHANNEL 'channel2';start slave;