Linux - MySQL迁移至一主一从

Linux - MySQL迁移至一主一从

  • 迁移准备
    • 安装MySQL
  • ibd文件迁移
    • 原服务器操作
    • 目标服务器操作
  • 一主一从
  • 增量同步
  • 异常解决
  • 结尾

首先部分单独安装MySQL,请参考Linux - MySQL安装,迁移数据量比较大约400G左右且网络不通故使用文件迁移,需开启一段时间只读和锁表。

迁移准备

测试机作用
172.17.7.9原MySQL
10.3.69.6目标主库
10.3.69.7目标从库

原服务器与目标服务器不在同一局域网内而且无外网。

思路

先检测原MySQL健康的表进行记录,设置原MySQL为只读以及锁表,防止迁移过程中继续写入,记录时间点用作迁移后增量同步,导出所有表结构和ibd文件,解锁恢复读写,将表结构和ibd文件转至目标主库以及从库,都先进行恢复ibd文件,然后进行主从连接,使用binlog导出原MySQL时间点开始的sql日志,并增量同步至目标主库。

安装MySQL

先在目标2台服务器上面安装MySQL,请参考Linux - MySQL安装,其中my.cnf配置参考以下,其中server_id主库与从库不一致 主库6 从库7,其他配置都一致,具体参数值参考自己MySQL需求大小进行配置。

[mysqld]
basedir=/usr/local/mysql/mysql8
datadir=/home/mysql/data
log-error=/home/mysql/log/mysql.logserver_id=6
gtid_mode=on
enforce_gtid_consistency=on
binlog_format=row
log-slave-updates=on
log-bin=mysqlbin-log
binlog_row_image = minimalmax_connections=2000
lower_case_table_names=1
character-set-server=utf8mb4
default_authentication_plugin=mysql_native_password
max_binlog_size=1G
max_binlog_cache_size=4G
log_timestamps=system

配置好后就初始化MySQL,然后重置密码操作什么的,先不进行主从连接。

ibd文件迁移

原服务器操作

注:其中有些打通ssh免密也进行了记录(假设原服务器和目标服务器可以互通),本次迁移未使用到ssh免密

ssh免密

ssh-keygen -t rsa
ssh-copy-id root@10.3.69.6
ssh-copy-id root@10.3.69.7
ssh root@10.3.69.6   # 测试是否免密登录
ssh root@10.3.69.7

创建一个export.sh脚本

找一个磁盘够大的位置(看数据量),创建一个transfer文件夹,在这个文件夹里面创建脚本
我们要一次一个库的去迁移(数据量有点大没磁盘了)当然也可以一次全部迁移全部的库(自行加一个循环),先查询某个库的所有表名,然后导出这个库所有的表结构,然后把ibd文件复制压缩即可
此处我自己写了一个py每过10s向数据库orders表中新增一条数据,模拟真实写入

#!/bin/bash# 数据库连接信息 变量 - 需要更改的
DB_USER="root"          # 替换为你的数据库用户名
DB_PASSWORD="123456"    # 替换为你的数据库密码
DB_HOST="172.17.7.9"    # 主机地址
DB_NAME="dw"            # 数据库名称,想迁移整个MySQL所有库可以写个大循环
MYSQLDUMP_PATH="/usr/local/mysql/mysql8/bin/mysqldump"   # mysqldump 路径
MYSQL_DATA_DIR="/home/weekeight/mysql/data/${DB_NAME}"   # MySQL 数据目录路径# 注:下面的所有值都不需要修改
# 输出文件路径
TABLES_FILE="${DB_NAME}_table_name.txt"
SCHEMA_FILE="${DB_NAME}_schema.sql"
# 创建 dw 文件夹(如果不存在)
DW_DIR="./${DB_NAME}"
mkdir -p "$DW_DIR"
# 查询所有表名并保存到文件,移除标题行
mysql -u "$DB_USER" -p"$DB_PASSWORD" -h "$DB_HOST" -D "$DB_NAME" -e "SHOW TABLES;" | sed '1d' > "$TABLES_FILE"
if [ $? -ne 0 ]; thenecho "导出表名时发生错误"exit 1
fi
echo "表名已成功导出到 $TABLES_FILE"
# 使用 mysqldump 导出表结构到文件
$MYSQLDUMP_PATH -u "$DB_USER" -p"$DB_PASSWORD" -h "$DB_HOST" --no-data --single-transaction --routines --triggers --events "$DB_NAME" > "$SCHEMA_FILE"
if [ $? -ne 0 ]; thenecho "导出表结构时发生错误"exit 1
elseecho "表结构已成功导出到 $SCHEMA_FILE"
fi
# 复制 IBD 文件到 dw 文件夹
while IFS= read -r table; doibd_file="${MYSQL_DATA_DIR}/${table}.ibd"if [ -f "$ibd_file" ]; thencp "$ibd_file" "$DW_DIR/"echo "复制了 $ibd_file$DW_DIR/"elseecho "警告: $ibd_file 不存在"fi
done < "$TABLES_FILE"
# 压缩打包 dw 文件夹
TAR_FILE="${DB_NAME}_ibd_files.tar.gz"
tar -czvf "$TAR_FILE" "$DW_DIR"
if [ $? -eq 0 ]; thenecho "dw 文件夹已成功压缩打包为 $TAR_FILE"
elseecho "压缩打包时发生错误"exit 1
fi
# 清理临时文件夹
rm -rf "$DW_DIR"

上面的脚本写好记得添加权限

chmod +x export.sh

搞好sh脚本后先不用管他,接下来我们进入MySQL进行设置只读和锁表,然后执行脚本,恢复MySQL

SET GLOBAL read_only = ON;    # 设置全局只读模式
FLUSH TABLES WITH READ LOCK;  # 锁表
SHOW ENGINE INNODB STATUS\G   # 检查当前是否还有事务正在执行
# 记录一下当前时间(16:26:00,order结尾11),用作后续的增量同步,order结尾11验证后面的增量同步order.ibd中只存在11条数据
# 然后去执行上面的./export.sh脚本,脚本执行完毕后继续在MySQL中执行以下命令恢复MySQL
UNLOCK TABLES;                # 解除锁表
SET GLOBAL read_only = OFF;   # 恢复读写模式,恢复后order表记录到38,上面sh脚本保存的ibd文件中不包含12-38

现在的我们已经生成了三个文件了,一个ibd数据文件、一个dw库所有表名文件、一个dw库表结构文件,可以把export.sh文件删除了,已经没用了
在这里插入图片描述
接下来就是把这三个文件移动到目标服务器了,下面使用scp,

scp -r ./transfer/ root@10.3.69.6:/home/weekeight/
scp -r ./transfer/ root@10.3.69.7:/home/weekeight/

目标服务器操作

下面的操作在目标服务器的两台上面同步执行命令

cd /home/weekeight/transfer/   # 进入移动过来的文件夹里面

创建一个import.sh脚本

#!/bin/bash# 变量 - 需要更改的
DB_USER="root"          # 替换为你的数据库用户名
DB_PASSWORD="123456"    # 替换为你的数据库密码
DB_NAME="dw"            # 迁移数据库名
MYSQL_DATA_DIR="/home/mysql/data/${DB_NAME}"   # MySQL 数据目录路径
TRANSFER_DIR="/home/weekeight/transfer"        # 资源目录,就是从原服务器移动过来的# 注:下面的所有值都不需要修改
IBD_TAR_FILE="${TRANSFER_DIR}/${DB_NAME}_ibd_files.tar.gz"
SCHEMA_FILE="${TRANSFER_DIR}/${DB_NAME}_schema.sql"
TABLES_NAME="${TRANSFER_DIR}/${DB_NAME}_table_name.txt"
log_file="all.log"
# 清空日志文件
> "$log_file"
# 检查数据库是否存在
check_db_exists() {mysql -u "$DB_USER" -p"$DB_PASSWORD" -e "SHOW DATABASES LIKE '$DB_NAME';" | grep -w "$DB_NAME" > /dev/null
}
# 检查数据库是否存在
if check_db_exists; thenecho "数据库 $DB_NAME 已存在。" | tee -a "$log_file"
else# 创建数据库echo "正在创建数据库 $DB_NAME..." | tee -a "$log_file"mysql -u "$DB_USER" -p"$DB_PASSWORD" -e "CREATE DATABASE IF NOT EXISTS $DB_NAME;"if [ $? -ne 0 ]; thenecho "创建数据库 $DB_NAME 失败,请检查数据库连接信息和权限。" | tee -a "$log_file"exit 1elseecho "数据库 $DB_NAME 创建成功。" | tee -a "$log_file"fi
fi
# 导入表结构
echo "正在导入表结构..." | tee -a "$log_file"
mysql -u "$DB_USER" -p"$DB_PASSWORD" "$DB_NAME" < "$SCHEMA_FILE"
if [ $? -eq 0 ]; thenecho "表结构已成功导入到数据库 $DB_NAME 中。" | tee -a "$log_file"
elseecho "导入表结构失败,请检查 $SCHEMA_FILE 是否存在且格式正确。" | tee -a "$log_file"exit 1
fi
# 解压 IBD 文件压缩包
echo "解压 IBD 文件..." | tee -a "$log_file"
tar -xzvf "$IBD_TAR_FILE" -C "$TRANSFER_DIR"
if [ $? -ne 0 ]; thenecho "解压失败,请检查 $IBD_TAR_FILE 是否存在且无损坏。" | tee -a "$log_file"exit 1
elseecho "解压成功,文件已放置在 $TRANSFER_DIR 中。" | tee -a "$log_file"
fi
# 循环读取表名
while IFS= read -r table_name; doecho "开始处理表 $table_name..." | tee -a "$log_file"# 设置源和目标路径source_path="${TRANSFER_DIR}/${DB_NAME}/${table_name}.ibd"destination_path="${MYSQL_DATA_DIR}/${table_name}.ibd"# 清除表空间mysql -u "$DB_USER" -p"$DB_PASSWORD" -e "ALTER TABLE $DB_NAME.$table_name DISCARD TABLESPACE;"if [ $? -ne 0 ]; thenecho "表 $table_name: DISCARD TABLESPACE 失败,跳过" | tee -a "$log_file"continuefi# 复制 .ibd 文件cp "$source_path" "$destination_path"if [ $? -ne 0 ]; thenecho "表 $table_name: 文件复制失败,跳过" | tee -a "$log_file"continuefi# 修改文件拥有者为 mysqlchown mysql:mysql "$destination_path"if [ $? -ne 0 ]; thenecho "表 $table_name: 更改文件拥有者失败,跳过" | tee -a "$log_file"continuefi# 导入表空间mysql -u "$DB_USER" -p"$DB_PASSWORD" -e "ALTER TABLE $DB_NAME.$table_name IMPORT TABLESPACE;"if [ $? -ne 0 ]; thenecho "表 $table_name: IMPORT TABLESPACE 失败,跳过" | tee -a "$log_file"continuefiecho "表 $table_name 迁移成功" | tee -a "$log_file"
done < "$TABLES_NAME"
echo "所有表处理完成,详情见 $log_file" | tee -a "$log_file"

创建import.sh后执行下面操作

chmod +x import.sh   # 修改权限
./import.sh          # 执行,执行完毕后检查一下打印是否正确,可以查看all.log日志
cd ..
rm -rf ./transfer    # 删除移动过来的文件夹即可,已无用

一主一从

下面是在目标服务器的MySQL上的操作,是进行一主一从的连接

首先在主库上面查看gtid值

mysql> SHOW VARIABLES LIKE 'gtid_executed';
+---------------+-------------------------------------------+
| Variable_name | Value                                     |
+---------------+-------------------------------------------+
| gtid_executed | 36513eed-b6bd-11ef-843c-6c92bfcb11f0:1-76 |
+---------------+-------------------------------------------+
1 row in set (0.01 sec)

然后在从库上面进行绑定连接

SET @@GLOBAL.GTID_PURGED='36513eed-b6bd-11ef-843c-6c92bfcb11f0:1-76';   # 把主库的gtid添加到这里
change master to master_host='10.3.69.6',master_port=3306,master_user='root',master_password='123456',master_auto_position=1 for channel 'master1';   # 连接主库创建管道名为msater1
start slave for channel 'master1';   # 单独启动master1进行复制,可使用start slave;
show slave status\G   # 查看从库的状态,状态中Slave_IO_Running与Slave_SQL_Running同为yes时正常,不然就是异常

下面的几个命令不需要执行,如果主从连接出现了问题,那么我们就要停止连接,清除配置,重新连接时使用的

stop slave;        # 停止主从
reset master;      # 重置主库配置
reset slave all;   # 重置从库配置

下面就是去主库上面进行创建一个数据库,或者写入数据,查看从库是否变化了

增量同步

上方已经记录了时间以及order参数(16:26:00),目标数据库的参数为11,原数据库的参数是38

先在原mysql上面进行查看binlog的file,然后导出binlog信息

mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000014 | 33034659 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
  • –skip-gtids:跳过GTID事件,必填项,不加的话导出的数据用不了,GTID冲突
  • –database:指定数据库
  • –start-datetime:指定开始时间,此时间即锁表后的时间,上面已经记录了
  • –stop-datetime:指定结束时间,最好指定此时间,导出的数据包头不包尾,这样的话如果有需要,下次的增量同步这个就是开始时间
  • binlog的文件在MySQL数据目录中,上面已经有文件名称了
/usr/local/mysql/mysql8/bin/mysqlbinlog --skip-gtids --database=dw --start-datetime="2024-12-12 16:26:00" --stop-datetime="2024-12-13 10:00:00" /home/weekeight/mysql/data/binlog.000014 > ./dw20241213100000.sql

把dw20241213100000.sql文件发送到目标服务器主库上面,从库不需要。

scp -r ./dw20241213100000.sql root@10.3.69.6:/usr/local/

下面在主库上导入sql

mysql -u root -p dw < dw20241213100000.sql

然后查看主库以及从库orders参数是否增加。

异常解决

@@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.

主库执行增量的sql文件时报错
binlog导出sql信息时使用–skip-gtids跳过GTID事件

Multiple channels exist on the slave. Please provide channel name as an argument

需要将从机的master清空(本机连接的有master的也要清空)
stop slave; reset slave all; reset master;

@@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED

reset master;

@@GLOBAL.GTID_PURGED cannot be changed: the new value must be a superset of the old value

之前已经存在gtid了想再需要配置一个多个的
SET @@GLOBAL.GTID_PURGED=‘以前的gtid…,新的gtid’;

结尾

至此MySQL迁移至一主一从已经全部搞定,如果有问题或者有其他好的方案望提议,共同学习。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/489758.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

PaddleOCR模型ch_PP-OCRv3文本检测模型研究(一)骨干网络

从源码上看&#xff0c;PaddleOCR一共支持四个版本&#xff0c;分别是PP-OCR、PP-OCRv2、PP-OCRv3、PP-OCRv4。本文选择PaddleOCR的v3版本的骨干网络作为研究对象&#xff0c;力图探究网络模型的内部结构。 文章目录 研究起点卷归层压发层残差层骨干网代码实验小结 研究起点 参…

数据结构——栈的模拟实现

大家好&#xff0c;今天我要介绍一下数据结构中的一个经典结构——栈。 一&#xff1a;栈的介绍 与顺序表和单链表不同的是&#xff1a; 顺序表和单链表都可以在头部和尾部插入和删除数据&#xff0c;但是栈的结构就锁死了&#xff08;栈的底部是堵死的&#xff09;栈只能从…

Ubuntu 安装 Samba Server

在 Mac 上如何能够与Ubuntu 服务器共享文件夹&#xff0c;需要在 Ubuntu 上安装 Samba 文件服务器。本文将介绍如何在 Ubuntu 上安装 Samba 服务器从而达到以下目的&#xff1a; Mac 与 Ubuntu 共享文件通过用户名密码访问 安装 Samba 服务 sudo apt install samba修改配置文…

Alan Chhabra:MongoDB AI应用程序计划(MAAP) 为客户提供价值

MongoDB全球合作伙伴执行副总裁 Alan Chhabra 每当有人向我问询MongoDB&#xff0c;我都会说他们很可能在不觉之间已经与MongoDB有过交集。事实上&#xff0c;包括70%财富百强在内的许多世界领先企业公司都在使用MongoDB。我们在MongoDB所做的一切都是为了服务客户&#xff0c…

计算机组成原理与系统结构——微程序控制

笔记内容及图片整理自XJTUSE “计算机组成原理与系统结构” 课程ppt&#xff0c;仅供学习交流使用&#xff0c;谢谢。 基本概念 微指令 将控制单元实现为基本逻辑单元之间的互连并非易事&#xff0c;且设计相对呆板&#xff0c;难以灵活地改变&#xff0c;因此实现微程序控制…

CUDA算子手撕与面试指南

引言 最近秋招落幕&#xff0c;期间一直在找高性能计算&#xff08;HPC&#xff09;相关岗位&#xff0c;整理了一些CUDA算子手撕的代码和知识点分享给大家。 项目地址&#xff1a;https://github.com/Tongkaio/CUDA_Kernel_Samples 如果觉得本项目对你有帮助&#xff0c;欢…

IIS部署程序https是访问出现403或ERR_HTTP2_PROTOCOL_ERROR

一、说明 在windows server 2016中的IIS程序池里部署一套系统&#xff0c;通过https访问站点&#xff0c;同时考虑到安全问题以及防攻击等行为&#xff0c;就用上了WAF云盾功能&#xff0c;能有效的抵挡部分攻击&#xff0c;加强网站的安全性和健壮性。 应用系统一直能够正常…

【EthIf-05】 Ethernet Interface main function

Ethernet Interface main function函数有以下功能和要点&#xff1a; 要实现支持轮询模式下帧传输确认和帧接收的以太网接口轮询机制&#xff1a;实现轮询功能&#xff0c;定期检查传入的帧。帧传输确认&#xff1a;包括确认帧已成功传输的机制。可配置轮询周期&#xff1a;允…

康耐视智能相机(Insight)通过ModbusTCP发送字符串到倍福(BECKHOFF)PLC中

文章目录 1.背景2.分析3.实现3.1.PLC的ModbusTCP_Server3.1.1.安装TF6250-Modbus-TCP3.1.2.PLC设置 3.2.智能相机的ModbusTCP_Client3.2.1.了解ModbusTCP的协议3.2.2.根据协议写代码3.2.2.1.纯函数代码3.2.2.2.脚本代码 3.2.3.非脚本处理时的代码逻辑图3.2.4.关于代码的问题及解…

ruoyi Cannot find module ‘@/views/system/user/index‘

Cannot find module /views/system/user/index 删除node_module 后打包成功

如何将你的 Ruby 应用程序从 OpenSearch 迁移到 Elasticsearch

作者&#xff1a;来自 Elastic Fernando Briano 将 Ruby 代码库从 OpenSearch 客户端迁移到 Elasticsearch 客户端的指南。 OpenSearch Ruby 客户端是从 7.x 版 Elasticsearch Ruby 客户端分叉而来的&#xff0c;因此代码库相对相似。这意味着当将 Ruby 代码库从 OpenSearch 迁…

spring cloud contract http实例

微服务很多时&#xff0c;服务之前相互调用&#xff0c;接口参数的一致性要变得很难维护。 spring cloud contract 提供了测试接口一致性的方法。 一 项目配置 plugins {id "groovy"id "org.springframework.cloud.contract" version "4.0.5"i…

帆软的无数据展示方案

文章目录 需求描述第一步、设置控件第二步、设置数据集优化改进 在日常工作中&#xff0c;使用到帆软报表工具&#xff0c;以下记录日常使用的过程&#xff0c; 需求描述 用帆软报表展示销量的信息&#xff0c;选择不同的订单状态&#xff0c;展示其订单数和总金额。 第一步、…

【操作系统】实验九:设备驱动程序

实验9 设备驱动程序 在钻研Linux内核的人当中&#xff0c;大多数人都是在写设备驱动程序。尽管由于设备的特殊性&#xff0c;使得每个驱动程序都不一样。但是编写设备驱动程序的许多原则和基本技巧都是一样的&#xff0c;甚至Windows下的设备驱动程序从原理上讲也与之相通。在…

文件上传—阿里云OSS对象存储

目录 一、OSS简介 二、OSS基本使用 1. 注册账号 2. 基本配置 (1) 开通OSS (2) 创建存储空间 (3) 修改权限 (4) 配置完成&#xff0c;上传一张图片&#xff0c;检验是否成功。 (5) 创建AccessKey 三、Java项目集成OSS 1. 导入依赖 2. Result.java代码&#xff1a; …

极米投影仪-看电视

文章目录 前言操作步骤1.进入GitHub下载软件2.修改后缀3.粘贴到U盘 前言 使用网络提供的电视接口免费看电视。 操作步骤 1.进入GitHub下载软件 地址&#xff1a;https://github.com/andandroidor/ourtv 也可以直接下载我已保存的apk&#xff0c;地址&#xff1a;https://d…

重生之我在异世界学编程之C语言:深入文件操作篇(下)

大家好&#xff0c;这里是小编的博客频道 小编的博客&#xff1a;就爱学编程 很高兴在CSDN这个大家庭与大家相识&#xff0c;希望能在这里与大家共同进步&#xff0c;共同收获更好的自己&#xff01;&#xff01;&#xff01; 函数递归与迭代 引言正文一、文件的基本操作&#…

Rust HTTP请求库

GITHUB 地址 LTPP-GIT 地址 接口文档 说明 http-request 是一个轻量级、高效的库&#xff0c;用于在 Rust 应用程序中构建、发送和处理 HTTP/HTTPS 请求。它提供了一个简单直观的 API&#xff0c;使开发人员可以轻松地与使用 “HTTP” 或 “HTTPS” 协议的 Web 服务进行交互…

ERC论文阅读(03)--instructERC论文阅读笔记(2024-12-14)

instructERC论文阅读笔记 2024-12-14 论文题目&#xff1a;InstructERC: Reforming Emotion Recognition in Conversation with Multi-task Retrieval-Augmented Large Language Models 说明&#xff1a;以下内容纯属本人看论文及复现代码的记录&#xff0c;如想了解论文细节&…

uniapp - 微信小程序

一、background-image 大图不显示的问题 解决方法&#xff1a; 1、使用网络地址&#xff1b;2、使用 base64 urlTobase64(filePath) {// #ifdef MP-WEIXINlet img ${filePath},imgBase64 wx.getFileSystemManager().readFileSync(img, "base64"),base64Url data:…