淄博网站制作企业营销,国内三大it外包公司,微页制作平台网站建设,个人网站源码免费下载一、背景 因某业务系统OS国产化改造#xff0c;现需将生成环境Mysql 主从迁移到新部署的BCLinux OS主机上#xff1b;如果保障业务不断#xff0c;平滑迁移并成功割接将是本次方案的重要方向#xff0c;现场环境涉及需迁移数据780G左右#xff0c;目标主机OS版本#xff…一、背景 因某业务系统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。通过这些 APIGalera 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是独立专用的数据库版本
2Galera 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_methodTOI)的所以必须要保证每个节点都是同时执行的当然对于不是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原理总结 事务在本地节点执行时采取乐观策略成功广播到所有节点后再做冲突检测 \检测出冲突时本地事务优先被回滚 \每个节点独立、异步执行队列中的WS \事务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作为一个监控项进行监控及时关注集群检通信问题。
九、迁移工具及案例
1ium是一款强大的数据库管理工具支持多种数据库管理包括MySQL、Oracle、SQL Server、PostgreSQL等。如数据可视化、数据同步、数据备份、数据传输等chdowsux和Mac OS X等。如数据库设计、数据建模、数据管理、SQL开发等
2dbForge Studio for MySQL
dbForge Studio for MySQL是一款专业的MySQL数据库管理工具支持多种功能如数据库设计、数据建模、数据管理、数据同步、数据备份等。它
3Toolkit
Toolkit是MySQL官方推出的一款数据库迁移工具支持多种数据库之间的转换包括MySQL、Oracle、SQL Server、PostgreSQL等。如数据传输、数据转换、数据同步等
4Navicat Premium Navicat Premium 是一套多连接数据库开发工具让你在单一应用程序中同时连接多种类型的数据库MySQL、MariaDB、MongoDB、SQL Server、SQLite、Oracle 和 PostgreSQL可一次快速方便地访问所有数据库。它也自带里数据库迁移能力打开【工具】-【传输工具】如下所示 上述功能涉收费另外迁移表结构的时候有时候会报错
5DataX
它是阿里云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的配置就是非常耗时的一件事情这对于数据迁移来说实在太麻烦。因此它对数据同步友好但对数据迁移并不友好。
6TurboDX
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存储索引 10Mysql自带迁移
MySQL Replication是指将一个MySQL服务器上的数据自动同步到另一个MySQL服务器上的过程。MySQL Replication可以实现数据实时备份、数据实时转移等多种功能。创建主从同步指向主的过程。
11mysqlimport
mysqlimport是将文本文件中的数据导入到MySQL数据库中的命令也可以将数据迁移到另一个MySQL服务器。示例如下
mysqlimport -u username -p --hostnewserver --fields-terminated-by, dbname /path/to/backupfile.sql12案例4新增实例进行迁移
A业务 A 旧实例与新实例建立同步复制
#设置旧实例为只读库防止数据写入
show global variables like %read_on%;
set global super_read_only1;
show global variables like %read_on%;
#新实例的主库执行建立旧实例到新实例的复制
CHANGE MASTER TO MASTER_HOST10.186.60.201,
MASTER_USERrepl,
MASTER_PORT3307,
MASTER_PASSWORDrepl,
MASTER_AUTO_POSITION 1;
start slave;B业务 B 与 C 需要合并所以这次保证数据实时同步采用了多源复制的方式。
#在新实例的主库执行将业务B与C的数据都复制到新实例中。
CHANGE MASTER TO MASTER_HOST10.186.60.209,
MASTER_USERrepl,
MASTER_PORT3307,
MASTER_PASSWORDrepl,
MASTER_AUTO_POSITION 1 FOR CHANNEL channel1;CHANGE MASTER TO MASTER_HOST10.186.60.210,
MASTER_USERrepl,
MASTER_PORT3307,
MASTER_PASSWORDrepl,
MASTER_AUTO_POSITION 1 FOR CHANNEL channel2;start slave;
