学习目标
- 了解数据迁移基本概念
- 掌握数据库跨版本迁移（5.7 => 8.0）
- 掌握数据库云迁移
- DTS公网数据迁移
- 同构与异构数据迁移
- 数据库DBA简历及项目编写
一、数据迁移概述
1、什么是数据迁移
数据迁移（Data Migration） 是指将数据从一个系统、平台、存储介质将数据迁移到另一个目标环境的过程，迁移过程中需保证数据的完整性、一致性、安全性和可追溯性，以确保业务系统能够在新环境中平稳运行。
在企业数字化转型、系统升级、国产化改造（信创）、云上迁移等场景中，数据迁移是最核心、风险最高的环节之一。
2、数据迁移的主要类型
类型说明示例跨版本迁移（Version Upgrade）同类型数据库版本升级MySQL 5.7 → 8.0数据库云迁移（Cloud Migration）从本地IDC迁往云平台自建机房 → 阿里云 / 华为云异构数据库迁移（Database Migration）不同数据库之间迁移MySQL → DM8 / MySQL → Oracle
3、数据迁移的总体流程
数据迁移通常分为 6 个阶段，每个阶段都有明确目标与关键工具：
阶段目标工具与方式1️⃣ 需求分析明确迁移范围、数据量、性能要求Excel清单、架构分析2️⃣ 方案设计设计迁移架构与工具链DataX / Canal / DTS / OGG3️⃣ 全量迁移批量导出旧数据并导入目标系统mysqldump / mydumper / DataX4️⃣ 增量同步捕获迁移期间的新变更数据Canal / DTS / Debezium / OGG5️⃣ 双写与校验新旧系统并行运行、数据比对校验双写机制 + pt-table-checksum6️⃣ 切换与收尾平滑切换业务流量、下线旧系统短暂停写切换、监控验证
二、数据库跨版本迁移（5.7=>8.0）
1、升级背景与意义
MySQL 8.0 是 MySQL 官方推出的重大版本更新，相比 MySQL 5.7 在性能、安全性、JSON 支持、事务一致性和复制机制等方面都有显著提升。
升级目标说明稳定性提升重构 InnoDB 存储引擎、改进事务与锁机制性能优化增强索引（Descending Index）、改进排序与窗口函数安全增强默认启用 caching_sha2_password、改进权限模型JSON/CTE支持原生 JSON 函数、窗口函数、递归查询复制增强支持多源复制、GTID 优化、Group Replication
2、升级（迁移）方式总览
升级方式特点场景适用原地升级（In-place Upgrade）在原服务器上直接升级 MySQL 二进制和数据文件测试或小型数据库逻辑迁移（Logical Migration）导出数据（mysqldump / mydumper）后导入新版本版本跨度大（如 5.7 → 8.0）物理迁移（Physical Copy）拷贝数据文件（需引擎兼容）同版本、低风险迁移中间件迁移（同步切换）使用同步工具进行平滑升级（迁移）生产系统、零停机需求
3、推荐升级策略（企业生产环境）
推荐采用「逻辑迁移 + 实时同步 + 校验切换」三阶段方案。
3.1 阶段一：全量逻辑迁移（Full Dump）
- 使用
mysqldump或mydumper从 MySQL5 导出数据； - 导入 MySQL8 时自动升级元数据结构；
- 确保字符集统一（建议 utf8mb4）。
# 导出
mysqldump -h old数据库地址 -uroot -p --single-transaction --routines --triggers --events --set-gtid-purged=OFF db_itheima > db_itheima.sql
# 导入
mysql -h new数据库地址 -uroot -p db_itheima < db_itheima.sql
⚠️ 注意事项：
sql_mode、character_set_server需统一；- MySQL 8.0 默认不再支持
utf8（需改为utf8mb4）； - 检查保留关键字（如 rank, groups, system 等）。
3.2 阶段二：增量同步跨版本主从方案（Binlog Sync）
在全量迁移完成后，为了保持实时一致，可使用以下工具之一：
工具特点推荐场景Canal监听 MySQL5 binlog → 写入 MySQL8内网迁移、稳定可控DTS云端迁移服务（支持 MySQL → MySQL）云上场景DebeziumKafka/Connector 增量同步大型微服务架构Replication原生主从复制方式简单架构迁移
3.3 CentOS 7.6 基本配置
网络配置
[root@jaking ~]# cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)
[root@jaking ~]# ifconfig
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.88.100 netmask 255.255.255.0 broadcast 192.168.88.255
inet6 fe80::d8ec:b255:8f22:71ea prefixlen 64 scopeid 0x20<link>
ether 00:50:56:29:f5:6e txqueuelen 1000 (Ethernet)
RX packets 460 bytes 43382 (42.3 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 302 bytes 35858 (35.0 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536
inet 127.0.0.1 netmask 255.0.0.0
inet6 ::1 prefixlen 128 scopeid 0x10<host>
loop txqueuelen 1000 (Local Loopback)
RX packets 32 bytes 2592 (2.5 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 32 bytes 2592 (2.5 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
[root@jaking ~]# cd /etc/sysconfig/network-scripts/
[root@jaking network-scripts]# ls
ifcfg-ens33 ifdown-ppp ifup-eth ifup-sit
ifcfg-lo ifdown-routes ifup-ippp ifup-Team
ifdown ifdown-sit ifup-ipv6 ifup-TeamPort
ifdown-bnep ifdown-Team ifup-isdn ifup-tunnel
ifdown-eth ifdown-TeamPort ifup-plip ifup-wireless
ifdown-ippp ifdown-tunnel ifup-plusb init.ipv6-global
ifdown-ipv6 ifup ifup-post network-functions
ifdown-isdn ifup-aliases ifup-ppp network-functions-ipv6
ifdown-post ifup-bnep ifup-routes
[root@jaking network-scripts]# vim ifcfg-ens33
[root@jaking network-scripts]# cat ifcfg-ens33
TYPE=Ethernet
PROXY_METHOD=none
BROWSER_ONLY=no
BOOTPROTO=static
DEFROUTE=yes
IPV4_FAILURE_FATAL=no
IPV6INIT=yes
IPV6_AUTOCONF=yes
IPV6_DEFROUTE=yes
IPV6_FAILURE_FATAL=no
IPV6_ADDR_GEN_MODE=stable-privacy
NAME=ens33
DEVICE=ens33
ONBOOT=yes
IPADDR=192.168.88.76
GATEWAY=192.168.88.2
NETMASK=255.255.255.0
DNS1=192.168.88.2
DNS2=114.114.114.114
[root@jaking network-scripts]# systemctl disable NetworkManager --now
[root@jaking network-scripts]# systemctl restart network
[root@jaking network-scripts]# systemctl status network
● network.service - LSB: Bring up/down networking
Loaded: loaded (/etc/rc.d/init.d/network; bad; vendor preset: disabled)
Active: active (exited) since Thu 2026-02-26 21:46:35 EST; 4s ago
Docs: man:systemd-sysv-generator(8)
Process: 9859 ExecStop=/etc/rc.d/init.d/network stop (code=exited, status=0/SUCCESS)
Process: 10039 ExecStart=/etc/rc.d/init.d/network start (code=exited, status=0/SUCCESS)
Feb 26 21:46:35 jaking systemd[1]: Starting LSB: Bring up/down networking...
Feb 26 21:46:35 jaking network[10039]: Bringing up loopback interface: [ ...]
Feb 26 21:46:35 jaking network[10039]: Bringing up interface ens33: Connec...)
Feb 26 21:46:35 jaking network[10039]: [ OK ]
Feb 26 21:46:35 jaking systemd[1]: Started LSB: Bring up/down networking.
Hint: Some lines were ellipsized, use -l to show in full.
[root@jaking network-scripts]# ifconfig
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.88.76 netmask 255.255.255.0 broadcast 192.168.88.255
inet6 fe80::d8ec:b255:8f22:71ea prefixlen 64 scopeid 0x20<link>
ether 00:50:56:29:f5:6e txqueuelen 1000 (Ethernet)
RX packets 460 bytes 43382 (42.3 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 302 bytes 35858 (35.0 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536
inet 127.0.0.1 netmask 255.0.0.0
inet6 ::1 prefixlen 128 scopeid 0x10<host>
loop txqueuelen 1000 (Local Loopback)
RX packets 32 bytes 2592 (2.5 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 32 bytes 2592 (2.5 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
[root@jaking network-scripts]#
yum 源配置
[root@jaking ~]# rm -rf /etc/yum.repos.d/*
[root@jaking ~]# vi /etc/yum.repos.d/aliyun.repo
[root@jaking ~]# cat /etc/yum.repos.d/aliyun.repo
[aliyun-os]
name=aliyun-os
baseurl=https://mirrors.aliyun.com/centos/7/os/x86_64/
enabled=1
gpgcheck=0
[aliyun-epel]
name=aliyun-epel
baseurl=https://mirrors.aliyun.com/epel/7/x86_64/
enabled=1
gpgcheck=0
[aliyun-extra]
name=aliyun-extra
baseurl=https://mirrors.aliyun.com/centos/7/extras/x86_64/
enabled=1
gpgcheck=0
[root@jaking ~]# vi /etc/yum.repos.d/mysql-community.repo
[root@jaking ~]# cat /etc/yum.repos.d/mysql-community.repo
[mysql-connectors-community]
name=MySQL Connectors Community
baseurl=http://repo.mysql.com/yum/mysql-connectors-community/el/7/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[mysql-tools-community]
name=MySQL Tools Community
baseurl=http://repo.mysql.com/yum/mysql-tools-community/el/7/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
# Enable to use MySQL 5.5
[mysql55-community]
name=MySQL 5.5 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.5-community/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
# Enable to use MySQL 5.6
[mysql56-community]
name=MySQL 5.6 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[mysql80-community]
name=MySQL 8.0 Community Server
baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[mysql-tools-preview]
name=MySQL Tools Preview
baseurl=http://repo.mysql.com/yum/mysql-tools-preview/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[mysql-cluster-7.5-community]
name=MySQL Cluster 7.5 Community
baseurl=http://repo.mysql.com/yum/mysql-cluster-7.5-community/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[mysql-cluster-7.6-community]
name=MySQL Cluster 7.6 Community
baseurl=http://repo.mysql.com/yum/mysql-cluster-7.6-community/el/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[root@jaking ~]# vi mysql-community-source.repo
[root@jaking ~]# cat mysql-community-source.repo
[mysql-connectors-community-source]
name=MySQL Connectors Community - Source
baseurl=http://repo.mysql.com/yum/mysql-connectors-community/el/7/SRPMS
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[mysql-tools-community-source]
name=MySQL Tools Community - Source
baseurl=http://repo.mysql.com/yum/mysql-tools-community/el/7/SRPMS
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[mysql55-community-source]
name=MySQL 5.5 Community Server - Source
baseurl=http://repo.mysql.com/yum/mysql-5.5-community/el/7/SRPMS
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[mysql56-community-source]
name=MySQL 5.6 Community Server - Source
baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/7/SRPMS
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[mysql57-community-source]
name=MySQL 5.7 Community Server - Source
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/SRPMS
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[mysql80-community-source]
name=MySQL 8.0 Community Server - Source
baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/SRPMS
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[mysql-tools-preview-source]
name=MySQL Tools Preview - Source
baseurl=http://repo.mysql.com/yum/mysql-tools-preview/el/7/SRPMS
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[mysql-cluster-7.5-community-source]
name=MySQL Cluster 7.5 Community - Source
baseurl=http://repo.mysql.com/yum/mysql-cluster-7.5-community/el/7/SRPMS
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[mysql-cluster-7.6-community-source]
name=MySQL Cluster 7.6 Community - Source
baseurl=http://repo.mysql.com/yum/mysql-cluster-7.6-community/el/7/SRPMS
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[root@jaking ~]# ls /etc/yum.repos.d/
aliyun.repo mysql-community.repo mysql-community-source.repo
安装 MySQL 5.7
CentOS Stream 9 源码编译安装 MySQL 5.7
安装 MySQL 8.0
CentOS Stream 9 dnf 安装 MySQL 8.0
CentOS Stream 9 部署 MySQL 8.0 (yum / rpm / 源码)
3.4 全量迁移 + 增量迁移 MySQL 5.7 到 MySQL 8.0
在 MySQL 5.7 准备测试数据
create database db_itheima default charset=utf8;
use db_itheima;
show databases;
CREATE TABLE tb_user (
user_id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
password VARCHAR(100) NOT NULL,
email VARCHAR(100),
phone VARCHAR(20),
create_time DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE tb_goods (
goods_id INT PRIMARY KEY AUTO_INCREMENT,
goods_name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT DEFAULT 0,
category VARCHAR(50),
create_time DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE tb_order (
order_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
goods_id INT NOT NULL,
quantity INT NOT NULL DEFAULT 1,
total_price DECIMAL(10,2) NOT NULL,
order_time DATETIME DEFAULT CURRENT_TIMESTAMP,
status VARCHAR(20) DEFAULT 'pending',
FOREIGN KEY (user_id) REFERENCES tb_user(user_id),
FOREIGN KEY (goods_id) REFERENCES tb_goods(goods_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO tb_user (username, password, email, phone) VALUES
('alice', 'MySQL@666', 'alice@example.com', '13800000001'),
('bob', 'MySQL@666', 'bob@example.com', '13800000002'),
('charlie', 'MySQL@666', 'charlie@example.com', '13800000003');
select * from tb_user;
INSERT INTO tb_goods (goods_name, price, stock, category) VALUES
('iPhone 15', 6999.00, 20, '手机'),
('MacBook Pro 14', 13999.00, 10, '笔记本'),
('AirPods Pro 2', 1999.00, 30, '耳机'),
('iPad Air', 4599.00, 15, '平板');
select * from tb_goods;
INSERT INTO tb_order (user_id, goods_id, quantity, total_price, status) VALUES
(1, 1, 1, 6999.00, 'paid'),
(2, 3, 2, 3998.00, 'shipped'),
(3, 4, 1, 4599.00, 'pending'),
(1, 2, 1, 13999.00, 'paid');
select * from tb_order;
show tables;
**全量迁移：**mysqldump 备份与还原
备份与还原思路：
[MySQL 5.7 源库]
│
│ ① 全量逻辑备份（mysqldump）
▼
[备份文件 backup.sql]
│
│ ② 传输至目标服务器
▼
[MySQL 8.0 目标库]
│
│ ③ 恢复导入（mysql < backup.sql）
▼
✅ 数据迁移完成
mysqldump****全库备份：
这里的操作在 MySQL5.7 所在的服务器上执行
提前创建/backup目录，开启二进制日志，mkdir /backup:
mkdir /backup
vim /etc/my.cnf
[mysqld]
# 配置 server-id ，后续主从需要
server-id=76
# 开启二进制日志
log-bin=mysql-bin
# 二进制日志格式
binlog_format=ROW
# 二进制日志过期时间（可选）
expire_logs_days=7
重启 MySQL
systemctl restart mysqld
yum 安装的 MySQL5.7，/etc/my.cnf 配置如下：
vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=76
log-bin=mysql-bin
binlog_format=ROW
expire_logs_days=7
重启 MySQL
systemctl restart mysqld
自定义数据目录的 MySQL5.7，/etc/my.cnf 配置如下：
[root@jaking ~]# mkdir /backup
[root@jaking ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.43, for Linux (x86_64) using EditLine wrapper
[root@jaking ~]# vim /etc/my.cnf
[root@jaking ~]# cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
port=3306
socket=/tmp/mysql.sock
symbolic-links=0
log-error=/data/mysql/mysql.err
pid-file=/data/mysql/mysql.pid
character-set-server=utf8mb4
server-id=76
log-bin=mysql-bin
binlog_format=ROW
expire_logs_days=7
[root@jaking ~]# systemctl restart mysqld
[root@jaking ~]# systemctl status mysqld
● mysqld.service - MySQL 5.7 Server
Loaded: loaded (/etc/systemd/system/mysqld.service; enabled; preset: disa>
Active: active (running) since Fri 2025-11-28 11:05:08 CST; 5s ago
Main PID: 31664 (mysqld)
Tasks: 27 (limit: 36187)
Memory: 177.2M
CPU: 237ms
CGroup: /system.slice/mysqld.service
└─31664 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
Nov 28 11:05:08 jaking systemd[1]: Started MySQL 5.7 Server.
[root@jaking ~]#
mkdir /backup/
mysqldump -h127.0.0.1 -uroot -p'MySQL@666' \
--single-transaction \
--master-data=2 \
--routines \
--events \
--triggers \
--set-gtid-purged=OFF \
--default-character-set=utf8mb4 \
--hex-blob \
--databases db_itheima \
> /backup/db_itheima_$(date +%F).sql
参数说明：
参数作用--single-transaction保证一致性备份（适合 InnoDB）--master-data=2记录 binlog 位点（方便后续同步）--routines导出存储过程和函数--events导出事件调度器对象--triggers导出触发器--set-gtid-purged=OFF禁止 GTID 自动导入（跨版本推荐）--default-character-set=utf8mb4指定字符集，避免乱码--hex-blob二进制字段安全导出--databases可一次导出多个数据库> /backup/...sql输出路径（推荐命名带日期）
[root@jaking ~]# ls /backup/
[root@jaking ~]# mysqldump -h127.0.0.1 -uroot -p'MySQL@666' \
--single-transaction \
--master-data=2 \
--routines \
--events \
--triggers \
--set-gtid-purged=OFF \
--default-character-set=utf8mb4 \
--hex-blob \
--databases db_itheima \
> /backup/db_itheima_$(date +%F).sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@jaking ~]# ls /backup/
db_itheima_2025-11-28.sql
[root@jaking ~]# head -n 60 /backup/db_itheima_2025-11-28.sql
-- MySQL dump 10.13 Distrib 5.7.43, for Linux (x86_64)
--
-- Host: 127.0.0.1 Database: db_itheima
-- ------------------------------------------------------
-- Server version 5.7.43-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
--
-- Current Database: `db_itheima`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db_itheima` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `db_itheima`;
--
-- Table structure for table `tb_goods`
--
DROP TABLE IF EXISTS `tb_goods`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tb_goods` (
`goods_id` int(11) NOT NULL AUTO_INCREMENT,
`goods_name` varchar(100) NOT NULL,
`price` decimal(10,2) NOT NULL,
`stock` int(11) DEFAULT '0',
`category` varchar(50) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`goods_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `tb_goods`
--
LOCK TABLES `tb_goods` WRITE;
/*!40000 ALTER TABLE `tb_goods` DISABLE KEYS */;
INSERT INTO `tb_goods` VALUES (1,'iPhone 15',6999.00,20,'手机','2025-11-28 10:58:29'),(2,'MacBook Pro 14',13999.00,10,'笔记本','2025-11-28 10:58:29'),(3,'AirPods Pro 2',1999.00,30,'耳机','2025-11-28 10:58:29'),(4,'iPad Air',4599.00,15,'平板','2025-11-28 10:58:29');
/*!40000 ALTER TABLE `tb_goods` ENABLE KEYS */;
UNLOCK TABLES;
--
[root@jaking ~]# wc -l /backup/db_itheima_2025-11-28.sql
138 /backup/db_itheima_2025-11-28.sql
[root@jaking ~]#
mysqldump****全库恢复：
这里的操作在 MySQL8.0 所在的服务器上执行
初始化数据库（强烈建议）
说明：如果是全新安装的 MySQL8.0，那么这一步可以跳过！
cat >/etc/my.cnf<<EOF
[mysqld]
# 基础路径
basedir=/export/server/mysql
datadir=/export/server/mysql/data
socket=/tmp/mysql.sock
port=3306
# 日志
log-error=/export/server/mysql/master.err
log-bin=/export/server/mysql/data/binlog
server-id=101
# 字符集
character_set_server=utf8mb4
collation-server=utf8mb4_unicode_ci
EOF
systemctl stop mysqld
pkill mysqld
rm -rf /export/server/mysql/data/*
rm -rf /tmp/mysqld.log
/export/server/mysql/bin/mysqld --initialize --user=mysql --basedir=/export/server/mysql &>/tmp/mysqld.log
grep password /tmp/mysqld.log | awk '{print $NF}'
mysqld --user=mysql --skip-grant-tables --skip-networking &
mysql
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MySQL@666';
FLUSH PRIVILEGES;
EXIT;
pkill mysqld
systemctl start mysqld
systemctl enable mysqld
systemctl status mysqld --no-pager
mysql -uroot -p'MySQL@666'
show databases;
1️⃣ 将 backup.sql 拷贝到 MySQL 8.0 服务器
scp /backup/db_itheima_2025-11-28.sql root@192.168.88.101:/root/
2️⃣ 登录目标服务器，创建目标数据库（可提前手动创建，也可让 SQL 自动创建）
CREATE DATABASE db_itheima CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
3️⃣ mysqldump恢复数据库
# 恢复指定数据库
mysql -uroot -p'MySQL@666' < /root/db_itheima_2025-11-28.sql
# 大文件恢复优化（了解）
mysql -uroot -p \
--init-command="SET autocommit=0; SET foreign_key_checks=0; SET unique_checks=0;" \
< /root/db_itheima_2025-11-05.sql
说明：禁用外键、唯一检查与自动提交可显著提升导入速度。
4️⃣ 恢复后验证
检查数据库、数据表以及数据结构
SHOW DATABASES;
USE db_itheima;
SHOW TABLES;
SHOW CREATE TABLE tb_user\G
[root@mysql80 ~]# mysql -V
mysql Ver 8.0.43 for Linux on x86_64 (MySQL Community Server - GPL)
[root@mysql80 ~]# cat /etc/redhat-release
CentOS Stream release 9
[root@mysql80 ~]# mysql -uroot -pMySQL@666
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.43 MySQL Community Server - GPL
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| db_itheima |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> USE db_itheima;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SHOW TABLES;
+----------------------+
| Tables_in_db_itheima |
+----------------------+
| tb_goods |
| tb_order |
| tb_user |
+----------------------+
3 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE tb_user\G
*************************** 1. row ***************************
Table: tb_user
Create Table: CREATE TABLE `tb_user` (
`user_id` int NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`password` varchar(100) NOT NULL,
`email` varchar(100) DEFAULT NULL,
`phone` varchar(20) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3
1 row in set (0.01 sec)
mysql>
检查数据量一致
SELECT COUNT(*) FROM tb_user;
SELECT COUNT(*) FROM tb_goods;
SELECT COUNT(*) FROM tb_order;
mysql> show tables;
+----------------------+
| Tables_in_db_itheima |
+----------------------+
| tb_goods |
| tb_order |
| tb_user |
+----------------------+
3 rows in set (0.01 sec)
mysql> SELECT COUNT(*) FROM tb_user;
+----------+
| COUNT(*) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM tb_goods;
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM tb_order;
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
mysql>
校验数据完整性
① 行数对比
SELECT table_name, table_rows
FROM information_schema.tables
WHERE table_schema='db_itheima';
mysql> SELECT table_name, table_rows
-> FROM information_schema.tables
-> WHERE table_schema='db_itheima';
+------------+------------+
| TABLE_NAME | TABLE_ROWS |
+------------+------------+
| tb_goods | 4 |
| tb_order | 4 |
| tb_user | 3 |
+------------+------------+
3 rows in set (0.01 sec)
mysql>
② 哈希对比
SELECT MD5(GROUP_CONCAT(user_id ORDER BY user_id)) FROM tb_user;
信息摘要算法 MD5**（Message-Digest Algorithm 5）**是一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值，通常用一个32位的十六进制字符串来表示;可以把它理解为一个数据的 “数字指纹”。
[root@jaking ~]# mysql -uroot -pMySQL@666
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.43-log Source distribution
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_itheima |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use db_itheima;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------+
| Tables_in_db_itheima |
+----------------------+
| tb_goods |
| tb_order |
| tb_user |
+----------------------+
3 rows in set (0.00 sec)
mysql> SELECT MD5(GROUP_CONCAT(user_id ORDER BY user_id)) FROM tb_user;
+---------------------------------------------+
| MD5(GROUP_CONCAT(user_id ORDER BY user_id)) |
+---------------------------------------------+
| 55b84a9d317184fe61224bfb4a060fb0 |
+---------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT MD5(GROUP_CONCAT(user_id ORDER BY user_id)) FROM tb_user;
+---------------------------------------------+
| MD5(GROUP_CONCAT(user_id ORDER BY user_id)) |
+---------------------------------------------+
| 55b84a9d317184fe61224bfb4a060fb0 |
+---------------------------------------------+
1 row in set (0.00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.43 |
+-----------+
1 row in set (0.01 sec)
mysql>
关于MD5案例拓展：
[root@jaking ~]# touch file1.txt file2.txt
[root@jaking ~]# md5sum file1.txt
d41d8cd98f00b204e9800998ecf8427e file1.txt
[root@jaking ~]# scp file1.txt 192.168.88.76:/root
root@192.168.88.76's password:
file1.txt 100% 0 0.0KB/s 00:00
[root@jaking ~]# md5sum file2.txt
d41d8cd98f00b204e9800998ecf8427e file2.txt
[root@jaking ~]# echo 666 >> file2.txt
[root@jaking ~]# md5sum file2.txt
971658bc2f5bdee5660844a83b5bf0a2 file2.txt
[root@jaking ~]# echo 666 >> file1.txt
[root@jaking ~]# md5sum file2.txt
971658bc2f5bdee5660844a83b5bf0a2 file2.txt
[root@jaking ~]# echo 777 >> file1.txt
[root@jaking ~]# md5sum file1.txt
076514a9bd52436384bd41c74b5f7fc2 file1.txt
[root@jaking ~]# echo 77 >> file2.txt
[root@jaking ~]# md5sum file2.txt
b9f1a20a6aea47d9bab3664d1e299a09 file2.txt
[root@jaking ~]# scp file2.txt 192.168.88.76:/root
root@192.168.88.76's password:
file2.txt 100% 7 8.4KB/s 00:00
[root@jaking ~]#
[root@mysql57 ~]# ls
db_itheima_2025-11-28.sql name_AIOps5.sh selected_students.txt
file1.txt ping.txt
[root@mysql57 ~]# md5sum file1.txt
d41d8cd98f00b204e9800998ecf8427e file1.txt
[root@mysql57 ~]# ls
db_itheima_2025-11-28.sql file2.txt ping.txt
file1.txt name_AIOps5.sh selected_students.txt
[root@mysql57 ~]# md5sum file2.txt
b9f1a20a6aea47d9bab3664d1e299a09 file2.txt
[root@mysql57 ~]#
校验字符集是否统一
SHOW VARIABLES LIKE 'character_set%';
mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.43-log |
+------------+
1 row in set (0.00 sec)
mysql>
mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+--------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb3 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb3 |
| character_set_system | utf8mb3 |
| character_sets_dir | /export/server/mysql/share/charsets/ |
+--------------------------+--------------------------------------+
8 rows in set (0.00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.43 |
+-----------+
1 row in set (0.00 sec)
mysql>
查看表结构
SELECT TABLE_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'db_itheima';
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.43-log |
+------------+
1 row in set (0.00 sec)
mysql> SELECT TABLE_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, INDEX_LENGTH
-> FROM information_schema.TABLES
-> WHERE TABLE_SCHEMA = 'db_itheima';
+------------+------------+----------------+-------------+--------------+
| TABLE_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | INDEX_LENGTH |
+------------+------------+----------------+-------------+--------------+
| tb_goods | 4 | 4096 | 16384 | 0 |
| tb_order | 4 | 4096 | 16384 | 32768 |
| tb_user | 3 | 5461 | 16384 | 0 |
+------------+------------+----------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql>
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.43 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT TABLE_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, INDEX_LENGTH
-> FROM information_schema.TABLES
-> WHERE TABLE_SCHEMA = 'db_itheima';
+------------+------------+----------------+-------------+--------------+
| TABLE_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | INDEX_LENGTH |
+------------+------------+----------------+-------------+--------------+
| tb_goods | 4 | 4096 | 16384 | 0 |
| tb_order | 4 | 4096 | 16384 | 32768 |
| tb_user | 3 | 5461 | 16384 | 0 |
+------------+------------+----------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql>
常见错误与解决方案：
问题原因解决方法导入时报 "Invalid default value for 'timestamp'"8.0 的严格模式在导入时加 --set-gtid-purged=OFF 并检查 sql_mode表结构不兼容新旧版本 DDL 差异手动调整表定义（尤其是 JSON、ENUM、索引前缀）性能慢外键约束和 autocommit导入时关闭外键检查数据不一致中途中断或导入异常再次导入该表或使用 pt-table-checksum 校验修复
🚩 问题：ERROR 1067 (42000): Invalid default value for 'create_time'
📖 原因：
MySQL 8.0 默认启用了更严格的 SQL 模式 (sql_mode)， 其中包含：
STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE
而在 MySQL 5.7 中，可能有类似定义：
create_time timestamp not null default '0000-00-00 00:00:00'
这在 8.0 中是不被允许的。
✅ 解决方案：
修改表定义：
alter table tb_user modify create_time timestamp not null default current_timestamp;
或者临时放宽模式导入：
mysql --init-command="SET sql_mode='NO_ENGINE_SUBSTITUTION';" -uroot -p < backup.sql
**增量迁移：**GTID 主从配置
**主库（**MySQL 5.7）配置
修改 /etc/my.cnf 配置文件
vim /etc/my.cnf
[mysqld]
server-id = 76
log-bin = mysql-bin
binlog_format = ROW
expire_logs_days=7
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON
binlog_checksum = NONE # 避免 8.0 校验错误
重启 MySQL
systemctl restart mysqld
[root@mysql57 ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.44, for Linux (x86_64) using EditLine wrapper
[root@mysql57 ~]# vim /etc/my.cnf
[root@mysql57 ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id = 76
log-bin = mysql-bin
binlog_format = ROW
expire_logs_days=7
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON
binlog_checksum = NONE
[root@mysql57 ~]# systemctl restart mysqld
[root@mysql57 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Thu 2025-11-27 23:13:52 EST; 43s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 44904 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 44887 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 44907 (mysqld)
CGroup: /system.slice/mysqld.service
└─44907 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/m...
Nov 27 23:13:52 mysql57 systemd[1]: Starting MySQL Server...
Nov 27 23:13:52 mysql57 systemd[1]: Started MySQL Server.
[root@mysql57 ~]#
在主库上创建复制用户repl及授权
mysql -uroot -pMySQL@666
CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'MySQL@666';
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl'@'%';
flush privileges;
[root@mysql57 ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.44, for Linux (x86_64) using EditLine wrapper
[root@mysql57 ~]# mysql -uroot -pMySQL@666
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44-log MySQL Community Server (GPL)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_itheima |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'MySQL@666';
Query OK, 0 rows affected (0.03 sec)
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> ^DBye
[root@mysql57 ~]#
**从库（**MySQL 8.0）配置
修改 /etc/my.cnf 配置文件
vim /etc/my.cnf
[mysqld]
server-id = 101
relay_log = relay-bin
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
重启 MySQL
systemctl restart mysqld
二进制安装的 MySQL8.0
[root@mysql80 ~]# mysql -V
mysql Ver 8.0.43 for Linux on x86_64 (MySQL Community Server - GPL)
[root@mysql80 ~]# vim /etc/my.cnf
[root@mysql80 ~]# cat /etc/my.cnf
[mysqld]
port=3306
basedir=/export/server/mysql
datadir=/export/server/mysql/data
socket=/tmp/mysql.sock
character_set_server=utf8
collation-server=utf8_unicode_ci
log-bin=/export/server/mysql/data/binlog
server-id = 101
relay_log = relay-bin
read_only = ON
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
[root@mysql80 ~]# systemctl restart mysqld
[root@mysql80 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/etc/systemd/system/mysqld.service; enabled; preset: disa>
Active: active (running) since Fri 2025-11-28 12:19:00 CST; 8s ago
Process: 10587 ExecStart=/export/server/mysql/bin/mysqld --daemonize --pid>
Main PID: 10596 (mysqld)
Tasks: 38 (limit: 22927)
Memory: 369.4M
CPU: 767ms
CGroup: /system.slice/mysqld.service
└─10596 /export/server/mysql/bin/mysqld --daemonize --pid-file=/e>
Nov 28 12:18:59 mysql80 mysqld[10596]: 2025-11-28T04:18:59.827567Z 0 [System] >
Nov 28 12:18:59 mysql80 mysqld[10596]: 2025-11-28T04:18:59.829320Z 0 [Warning]>
Nov 28 12:18:59 mysql80 mysqld[10596]: 2025-11-28T04:18:59.829341Z 0 [Warning]>
Nov 28 12:18:59 mysql80 mysqld[10596]: 2025-11-28T04:18:59.841264Z 1 [System] >
Nov 28 12:19:00 mysql80 mysqld[10596]: 2025-11-28T04:19:00.001924Z 1 [System] >
Nov 28 12:19:00 mysql80 mysqld[10596]: 2025-11-28T04:19:00.218569Z 0 [Warning]>
Nov 28 12:19:00 mysql80 mysqld[10596]: 2025-11-28T04:19:00.219592Z 0 [System] >
Nov 28 12:19:00 mysql80 mysqld[10596]: 2025-11-28T04:19:00.249151Z 0 [System] >
Nov 28 12:19:00 mysql80 mysqld[10596]: 2025-11-28T04:19:00.250050Z 0 [System] >
Nov 28 12:19:00 mysql80 systemd[1]: Started MySQL Server.
[root@mysql80 ~]#
dnf/yum 安装的 MySQL8.0
[root@mysql80 ~]# mysql -V
mysql Ver 8.0.46 for Linux on x86_64 (MySQL Community Server - GPL)
[root@mysql80 ~]# vim /etc/my.cnf
[root@mysql80 ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id = 101
relay_log = relay-bin
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
[root@mysql80 ~]# systemctl restart mysqld
[root@mysql80 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; preset: disable>
Active: active (running) since Sun 2026-04-26 15:38:37 CST; 5s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 38268 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SU>
Main PID: 38314 (mysqld)
Status: "Server is operational"
Tasks: 38 (limit: 22926)
Memory: 366.0M
CPU: 3.067s
CGroup: /system.slice/mysqld.service
└─38314 /usr/sbin/mysqld
Apr 26 15:38:34 mysql80 systemd[1]: Starting MySQL Server...
Apr 26 15:38:37 mysql80 systemd[1]: Started MySQL Server.
[root@mysql80 ~]# mysql -uroot -pMySQL@666
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.46 MySQL Community Server - GPL
Copyright (c) 2000, 2026, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db_itheima |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> ^DBye
[root@mysql80 ~]#
建立主从复制关系（在从服务器上操作）
mysql -uroot -pMySQL@666
CHANGE MASTER TO
MASTER_HOST='192.168.88.76',
MASTER_USER='repl',
MASTER_PASSWORD='MySQL@666',
MASTER_AUTO_POSITION=1;
START SLAVE;
SHOW SLAVE STATUS\G
-----------------------------------------------------
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
[root@mysql80 ~]# mysql -uroot -pMySQL@666
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.43 MySQL Community Server - GPL
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db_itheima |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.88.76',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='MySQL@666',
-> MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 7 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.32 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.88.76
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 739
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 955
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 739
Relay_Log_Space: 1159
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: f9eb7164-cb76-11f0-a131-000c294d6f91
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: f9eb7164-cb76-11f0-a131-000c294d6f91:1-3
Executed_Gtid_Set: f9eb7164-cb76-11f0-a131-000c294d6f91:1-3
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
mysql>
主从状态故障分析（拓展）
show slave status\G
可能会出现以下问题：
[root@mysql80 ~]# mysql -urepl -pMySQL@666 -h192.168.88.76
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.88.76:3306' (113)
[root@mysql80 ~]# yum install -y telnet
Last metadata expiration check: 2:09:11 ago on Fri 28 Nov 2025 10:11:58 AM CST.
Package telnet-1:0.17-85.el9.x86_64 is already installed.
Dependencies resolved.
Nothing to do.
Complete!
[root@mysql80 ~]# telnet 192.168.88.76 3306
Trying 192.168.88.76...
telnet: connect to address 192.168.88.76: No route to host
[root@mysql80 ~]# telnet 192.168.88.76 22
Trying 192.168.88.76...
Connected to 192.168.88.76.
Escape character is '^]'.
SSH-2.0-OpenSSH_7.4
c
Protocol mismatch.
Connection closed by foreign host.
[root@mysql80 ~]# telnet 192.168.88.76 3306
Trying 192.168.88.76...
Connected to 192.168.88.76.
Escape character is '^]'.
N
5.7.44-logHX'F2MF)mysql_native_password
!#08S01Got packets out of orderConnection closed by foreign host.
解决方法：
[root@mysql57 ~]# iptables -F
[root@mysql57 ~]# systemctl stop firewalld
[root@mysql57 ~]# setenforce 0
[root@mysql80 ~]# mysql -urepl -pMySQL@666 -h192.168.88.76
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.44-log MySQL Community Server (GPL)
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
mysql> ^DBye
[root@mysql80 ~]#
重建主从（拓展）
以下操作在主库和从库执行：
reset master;
以下操作在从库执行：
-- 停止复制
STOP SLAVE;
-- 重置复制
RESET SLAVE ALL;
-- 重新配置复制
CHANGE MASTER TO
MASTER_HOST='192.168.88.76',
MASTER_USER='repl',
MASTER_PASSWORD='MySQL@666',
MASTER_AUTO_POSITION=1;
-- 启动复制
START SLAVE;
-- 检查主从复制状态
SHOW SLAVE STATUS\G
跳过错误
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '8607328d-c781-11f0-b455-000c294d6f91:45' at source log mysql-bin.000004, end_log_pos 15856. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
-- 1. 停止复制
STOP SLAVE;
-- 2. 手动声明“我已经执行过 :45 这条事务了”
SET SESSION gtid_next = '8607328d-c781-11f0-b455-000c294d6f91:45';
BEGIN;
COMMIT;
SET SESSION gtid_next = 'AUTOMATIC';
-- 3. 启动复制
START SLAVE;
-- 4. 检查主从复制状态
SHOW SLAVE STATUS\G
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;
SHOW SLAVE STATUS\G
测试数据同步
注意：一定要在主库执行以下操作！
mysql -uroot -pMySQL@666
CREATE DATABASE `bookdb666` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
show databases;
use bookdb666;
CREATE TABLE book666 (ID int,Name char(16),Price int,Publishing char(16));
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('1','《Linux从入门到精通》','66','电子工业出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('2','《云计算趋势》','68','人民邮电出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('3','《操作系统设计与实现》','90','机械工业出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('4','《高性能MySQL1》','71','清华大学出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('5','《高性能MySQL2》','72','清华大学出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('6','《高性能MySQL3》','73','清华大学出版社');
show tables;
select * from book666;
[root@mysql57 ~]# mysql -uroot -pMySQL@666
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.44-log MySQL Community Server (GPL)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_itheima |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> CREATE DATABASE `bookdb666` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bookdb666 |
| db_itheima |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> use bookdb666;
Database changed
mysql>
mysql> CREATE TABLE book666 (ID int,Name char(16),Price int,Publishing char(16));
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO book666(ID,Name,Price,Publishing) VALUES('1','《Linux从入门 到精通》','66','电子工业出版社');
Query OK, 1 row affected (0.04 sec)
mysql> INSERT INTO book666(ID,Name,Price,Publishing) VALUES('2','《云计算趋势》','68','人民邮电出版社');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO book666(ID,Name,Price,Publishing) VALUES('3','《操作系统设计与实现》','90','机械工业出版社');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO book666(ID,Name,Price,Publishing) VALUES('4','《高性能MySQL1》','71','清华大学出版社');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO book666(ID,Name,Price,Publishing) VALUES('5','《高性能MySQL2》','72','清华大学出版社');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO book666(ID,Name,Price,Publishing) VALUES('6','《高性能MySQL3》','73','清华大学出版社');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> show tables;
+---------------------+
| Tables_in_bookdb666 |
+---------------------+
| book666 |
+---------------------+
1 row in set (0.00 sec)
mysql> select * from book666;
+------+-----------------------------------+-------+-----------------------+
| ID | Name | Price | Publishing |
+------+-----------------------------------+-------+-----------------------+
| 1 | 《Linux从入门到精通》 | 66 | 电子工业出版社 |
| 2 | 《云计算趋势》 | 68 | 人民邮电出版社 |
| 3 | 《操作系统设计与实现》 | 90 | 机械工业出版社 |
| 4 | 《高性能MySQL1》 | 71 | 清华大学出版社 |
| 5 | 《高性能MySQL2》 | 72 | 清华大学出版社 |
| 6 | 《高性能MySQL3》 | 73 | 清华大学出版社 |
+------+-----------------------------------+-------+-----------------------+
6 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bookdb666 |
| db_itheima |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.44-log |
+------------+
1 row in set (0.00 sec)
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| bookdb666 |
| db_itheima |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.01 sec)
mysql> use bookdb666
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------+
| Tables_in_bookdb666 |
+---------------------+
| book666 |
+---------------------+
1 row in set (0.00 sec)
mysql> select * from book666;
+------+-----------------------------------+-------+-----------------------+
| ID | Name | Price | Publishing |
+------+-----------------------------------+-------+-----------------------+
| 1 | 《Linux从入门到精通》 | 66 | 电子工业出版社 |
| 2 | 《云计算趋势》 | 68 | 人民邮电出版社 |
| 3 | 《操作系统设计与实现》 | 90 | 机械工业出版社 |
| 4 | 《高性能MySQL1》 | 71 | 清华大学出版社 |
| 5 | 《高性能MySQL2》 | 72 | 清华大学出版社 |
| 6 | 《高性能MySQL3》 | 73 | 清华大学出版社 |
+------+-----------------------------------+-------+-----------------------+
6 rows in set (0.00 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.43 |
+-----------+
1 row in set (0.00 sec)
mysql>
3.5 阶段三：校验与切换（Check & Switch）
迁移完成后，必须进行一致性校验与平滑切换
校检方式：
工具功能示例pt-table-checksum比对主从差异pt-table-checksum --replicate=percona.checksumspt-table-sync自动修复差异数据pt-table-sync --execute自定义SQLCOUNT / SUM / CHECKSUM 校验比较记录数和聚合值
3.5.1 Percona Toolkit工具介绍
Percona Toolkit
- pt-table-checksum
- pt-table-sync
这两个工具都是 Percona Toolkit 里的，非常流行、强大，用于 MySQL 主从校验和修复。
- 适用 MySQL、Percona、MariaDB 各种环境。
- 官网：https://www.percona.com/software/database-tools/percona-toolkit
pt-table-checksum performs an online replication consistency check by executing checksum queries on the master, which produces different results on replicas that are inconsistent with the master. The optional DSN specifies the master host.
The following command will connect to the replication master on localhost, checksum every table, and report the results on every detected replica.
为什么必须在主库执行
pt-table-checksum 的工作机制决定了它必须在主库运行：
校验查询执行点：工具在主库上执行 REPLACE INTO ... SELECT 形式的 checksum 查询
复制传播：这些查询通过 MySQL 复制机制传播到从库执行
结果对比：工具自动探测从库，对比主从的 checksum 结果差异
Percona 官方论坛的技术支持人员也明确回复用户："pt-table-checksum needs to be run on the master."
官方文档链接
官方手册（最新版）：https://docs.percona.com/percona-toolkit/pt-table-checksum.html
Man Page 文档：https://manpages.debian.org/testing/percona-toolkit/pt-table-checksum.1p
官方不仅推荐，而且设计上就是要求在主库执行。DSN 参数指向的就是主库地址，工具会自动处理从库的探测和对比。
3.5.2 在主库安装Percona Toolkit
CentOS 7 安装 Percona Toolkit
wget https://downloads.percona.com/downloads/percona-toolkit/3.2.1/binary/redhat/7/x86_64/percona-toolkit-3.2.1-1.el7.x86_64.rpm
yum localinstall -y percona-toolkit-3.2.1-1.el7.x86_64.rpm
# yum install -y yum-utils # 可选
# yum-config-manager --disable percona-tools # 可选
CentOS Stream 9 安装 Percona Toolkit
第一步：下载并添加 Percona YUM 仓库
yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
第二步：启用 Percona 工具仓库
percona-release enable tools release
yum clean all
yum makecache
第三步：安装 percona-toolkit
yum install -y percona-toolkit
3.5.3 Percona Toolkit实现主从一致性修复
第一步：检查主从一致性
在主库 192.168.88.76 上执行如下命令：
pt-table-checksum \
--host=192.168.88.76 \
--user=repl \
--password='MySQL@666' \
--port=3306 \
--replicate=percona.checksums \
--no-check-binlog-format
# Percona 官方 MySQL 主从数据一致性校验工具，用于检查主从库表数据是否一致
pt-table-checksum \
# 连接的主库 IP 地址（必须指定主库）
--host=192.168.88.76 \
# 连接 MySQL 的用户名
--user=repl \
# 连接 MySQL 的密码
--password='MySQL@666' \
# MySQL 端口号
--port=3306 \
# 校验结果存储到 percona 库的 checksums 表中（库和表会自动创建）
--replicate=percona.checksums \
# 不检查 binlog 格式（允许在 STATEMENT/MIXED 模式下运行，避免报错）
--no-check-binlog-format
--replicate=percona.checksums：校验结果会写进主库上的 percona.checksums 表（会自动创建）
分配权限，建议优先在主库执行以下命令；如果从库没有同步到对应的权限，那么从库也需要执行以下命令。
mysql -uroot -pMySQL@666
-- CREATE USER 'repl'@'%' IDENTIFIED BY 'MySQL@666';
GRANT SUPER, REPLICATION SLAVE, REPLICATION CLIENT, PROCESS, SELECT ON *.* TO 'repl'@'%';
GRANT CREATE, DELETE, INSERT, UPDATE ON percona.* TO 'repl'@'%';
FLUSH PRIVILEGES;
show grants for repl;
-- 创建一个名为 repl 的 MySQL 用户，允许从任意 IP（%）连接，密码为 MySQL@666
CREATE USER 'repl'@'%' IDENTIFIED BY 'MySQL@666';
-- 授予全局权限：
-- SUPER：执行高级操作，pt工具必需
-- REPLICATION SLAVE / REPLICATION CLIENT：主从复制、查看复制状态权限
-- PROCESS：查看数据库连接进程
-- SELECT：查询所有库表数据（用于校验数据）
GRANT SUPER, REPLICATION SLAVE, REPLICATION CLIENT, PROCESS, SELECT ON *.* TO 'repl'@'%';
-- 授予 percona 库下所有表的 增删改查 创建权限
-- 用于 pt-table-checksum 写入校验结果到 percona.checksums 表
GRANT CREATE, DELETE, INSERT, UPDATE ON percona.* TO 'repl'@'%';
-- 刷新权限，让授权立即生效
FLUSH PRIVILEGES;
-- 查看 repl 用户拥有的所有权限，验证授权是否正确
show grants for repl;
重新运行：
pt-table-checksum \
--host=192.168.88.76 \
--user=repl \
--password='MySQL@666' \
--port=3306 \
--replicate=percona.checksums \
--no-check-binlog-format
运行结果：
补充：
结果解释：
检查结束后，输出中 DIFFS 列如果为 1，说明该表主从不一致。
深入分析：
[root@jaking ~]# pt-table-checksum \
> --host=192.168.88.76 \
> --user=repl \
> --password='MySQL@666' \
> --port=3306 \
> --replicate=percona.checksums \
> --no-check-binlog-format
Checking if all tables can be checksummed ...
Starting checksum ...
*******************************************************************
Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
possibly with SSL_ca_file|SSL_ca_path for verification.
If you really don't want to verify the certificate and keep the
connection open to Man-In-The-Middle attacks please set
SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
at /usr/bin/pt-table-checksum line 332.
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
11-22T10:19:02 0 0 6 0 1 0 0.323 bookdb666.book666
11-22T10:19:02 0 0 4 0 1 0 0.318 db_itheima.tb_goods
11-22T10:19:03 0 0 4 0 1 0 0.322 db_itheima.tb_order
11-22T10:19:03 0 0 3 0 1 0 0.315 db_itheima.tb_user
11-22T10:19:03 0 0 0 0 1 0 0.315 mysql.columns_priv
11-22T10:19:04 0 0 3 0 1 0 0.360 mysql.db
11-22T10:19:04 0 0 2 0 1 0 0.312 mysql.engine_cost
11-22T10:19:04 Skipping table mysql.event because it has problems on these replicas:
Table mysql.event does not exist on replica server2
This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check.
11-22T10:19:04 Error checksumming table mysql.event: Error getting row count estimate of table mysql.event on replica server2: DBD::mysql::db selectrow_hashref failed: Table 'mysql.event' doesn't exist [for Statement "EXPLAIN SELECT * FROM `mysql`.`event` WHERE 1=1"] at /usr/bin/pt-table-checksum line 6930.
11-22T10:19:04 1 0 0 0 0 0 0.003 mysql.event
11-22T10:19:04 0 0 0 0 1 0 0.315 mysql.func
11-22T10:19:05 0 1 50 3 1 0 0.316 mysql.help_category
11-22T10:19:05 0 1 909 77 1 0 0.320 mysql.help_keyword
11-22T10:19:05 0 1 1806 248 1 0 0.319 mysql.help_relation
11-22T10:19:06 0 1 660 45 1 0 0.350 mysql.help_topic
11-22T10:19:06 0 0 0 0 1 0 0.317 mysql.ndb_binlog_index
11-22T10:19:06 0 1 1 1 1 0 0.338 mysql.plugin
11-22T10:19:06 Skipping table mysql.proc because it has problems on these replicas:
Table mysql.proc does not exist on replica server2
This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check.
11-22T10:19:06 Error checksumming table mysql.proc: Error getting row count estimate of table mysql.proc on replica server2: DBD::mysql::db selectrow_hashref failed: Table 'mysql.proc' doesn't exist [for Statement "EXPLAIN SELECT * FROM `mysql`.`proc` WHERE 1=1"] at /usr/bin/pt-table-checksum line 6930.
11-22T10:19:06 1 0 0 0 0 0 0.005 mysql.proc
11-22T10:19:07 0 0 0 0 1 0 0.317 mysql.procs_priv
11-22T10:19:07 0 1 1 0 1 0 0.313 mysql.proxies_priv
11-22T10:19:07 0 0 6 0 1 0 0.314 mysql.server_cost
11-22T10:19:08 0 0 0 0 1 0 0.316 mysql.servers
11-22T10:19:08 0 1 2 0 1 0 0.314 mysql.tables_priv
11-22T10:19:08 0 0 0 0 1 0 0.316 mysql.time_zone
11-22T10:19:09 0 0 0 0 1 0 0.315 mysql.time_zone_leap_second
11-22T10:19:09 0 0 0 0 1 0 0.317 mysql.time_zone_name
11-22T10:19:09 0 0 0 0 1 0 0.349 mysql.time_zone_transition
11-22T10:19:10 0 0 0 0 1 0 0.311 mysql.time_zone_transition_type
11-22T10:19:10 0 1 4 1 1 0 0.314 mysql.user
11-22T10:19:10 0 0 6 0 1 0 0.325 sys.sys_config
[root@jaking ~]#
从检查结果来看，主从复制整体状态是相当不错的，但有一些需要注意的地方。
好的方面：
- 主从复制状态正常：
Slave_IO_Running: Yes- I/O 线程正常运行Slave_SQL_Running: Yes- SQL 线程正常运行Seconds_Behind_Master: 0- 从库没有延迟-
没有错误信息
- 大部分业务表数据一致：
bookdb666.book666: 0 diffsdb_itheima.tb_goods: 0 diffsdb_itheima.tb_order: 0 diffs-
db_itheima.tb_user: 0 diffs
需要注意的问题：
1. 系统表存在差异
有几个 MySQL 系统表显示有差异：
mysql.help_category: 1 diffmysql.help_keyword: 1 diffmysql.help_relation: 1 diffmysql.help_topic: 1 diffmysql.plugin: 1 diffmysql.proxies_priv: 1 diffmysql.tables_priv: 1 diff-
mysql.user: 1 diff
2. 缺少系统表
从库缺少以下系统表：
mysql.event- 不存在于从库-
mysql.proc- 不存在于从库
建议操作：
1. 忽略系统表差异（推荐）
对于生产环境，通常可以忽略系统表的差异，因为它们不影响业务数据：
# 下次检查时忽略系统表
pt-table-checksum \
--host=192.168.88.76 \
--user=repl \
--password='MySQL@666' \
--port=3306 \
--replicate=percona.checksums \
--no-check-binlog-format \
--ignore-tables=mysql.event,mysql.proc \
--databases=bookdb666,db_itheima
2. 或者创建缺失的表（了解）
如果确实需要这些表，可以在从库创建：
-- 在从库执行
CREATE TABLE IF NOT EXISTS mysql.event (
db char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
name char(64) NOT NULL DEFAULT '',
body longblob NOT NULL,
definer char(141) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
execute_at datetime DEFAULT NULL,
interval_value int(11) DEFAULT NULL,
interval_field enum('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') DEFAULT NULL,
starts datetime DEFAULT NULL,
ends datetime DEFAULT NULL,
status enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',
on_completion enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',
created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
modified timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
last_executed datetime DEFAULT NULL,
comment char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
originator int(10) unsigned NOT NULL,
time_zone char(64) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
character_set_client char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
collation_connection char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
db_collation char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
body_utf8 longblob,
PRIMARY KEY (db,name)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Events';
总体评价：
主从复制状态良好，业务数据表完全一致，系统表的少量差异通常不会影响业务运行。
建议：
- 重点关注业务表：确保
bookdb666和db_itheima数据库中的表数据一致 - 监控复制状态：继续保持当前的监控
- 定期检查：定期运行 pt-table-checksum 确保数据一致性
1.
主从环境运行得很稳定！
第二步：检查和修复主从差异
只查看（不会真的修复）
pt-table-sync --print --replicate=percona.checksums h=192.168.88.76,u=repl,p='MySQL@666'
会打印出修复主从差异需要执行的 SQL（只显示，不执行）
自动修复（谨慎使用，建议先备份！）
默认操作
pt-table-sync --execute --replicate=percona.checksums h=192.168.88.76,u=repl,p='MySQL@666'
忽略 mysql 库
pt-table-sync --execute \
--replicate=percona.checksums \
--ignore-databases=mysql \
h=192.168.88.76,u=repl,p='MySQL@666'
指定业务数据库 db_itheima
pt-table-sync --execute \
--replicate=percona.checksums \
--databases=db_itheima \
h=192.168.88.76,u=repl,p='MySQL@666'
自动让从库数据和主库一致
补充：授予对业务数据库的权限，例如 db_itheima 和 bookdb666
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON db_itheima.* TO 'repl'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON bookdb666.* TO 'repl'@'%';
FLUSH PRIVILEGES;
-- 登录 MySQL 主库执行
GRANT DELETE, INSERT, UPDATE, LOCK TABLES ON mysql.* TO 'repl'@'%';
FLUSH PRIVILEGES;
-- 同步完成后立即回收权限
REVOKE DELETE, INSERT, UPDATE, LOCK TABLES ON mysql.* FROM 'repl'@'%';
FLUSH PRIVILEGES;
方案一：限制只同步业务数据库（推荐）
使用 --databases 参数指定只同步业务数据库：
pt-table-sync --execute --replicate=percona.checksums \--databases=db_itheima,bookdb666 \h=192.168.88.76,u=repl,p='MySQL@666'
方案二：忽略系统表
使用 --ignore-tables 参数忽略有问题的系统表：
pt-table-sync --execute --replicate=percona.checksums \
--ignore-tables=mysql.event,mysql.proc,mysql.help_category,mysql.help_keyword,mysql.help_relation,mysql.help_topic,mysql.plugin,mysql.proxies_priv,mysql.tables_priv,mysql.user \h=192.168.88.76,u=repl,p='MySQL@666'
通过使用 --databases 参数限定只同步业务数据库 db_itheima 和 bookdb666，避开了对 mysql 系统库的同步操作，从而解决了权限错误问题。
对于 mysql 系统库中帮助文档相关表（如 help_topic）存在的差异，只要不影响数据库的正常运行和主从复制功能，通常可以忽略。许多生产环境也会忽略这些系统表的差异。
切换方式：
- 停止业务写入旧库；
- 等待同步延迟为 0；
- 切换应用连接到新库；
- 保留旧库为回滚备份（7天以上）。
三、数据库云迁移（MySQL=>RDS）
1、数据库云迁移
这类迁移可分为 三种模式：离线迁移（停机）、在线迁移（不停机）、混合迁移（全量离线+增量在线）
2、迁移总体目标
迁移要求一般有三点：
数据完整性 ✅（结构 + 数据）
迁移过程最短停机时间 ⏱️（理想：不停机）
数据一致性 💯（目标数据库的数据与源数据库的数据一致）
3、自建 MySQL → RDS 的常见迁移方式
迁移方式工具 / 技术实时能力是否需要停机适用场景 & 特点1️⃣ 逻辑迁移（全量）mysqldump / mydumper / DMS 导入❌ 无增量⚠️ 视业务写入情况（常需短暂停写）小规模数据库（<20–50GB），架构简单，迁移方便；若业务不停写则会出现数据不一致。2️⃣ 逻辑迁移（全量 + 增量）mydumper + binlog / DM / gh-ost✅ 具备实时性🚫 不停机通过解析 binlog 同步增量，实现不停机迁移，适用于中大型 DB。3️⃣ 物理迁移（全量）xtrabackup / mysqlbackup❌ 无增量⚠️ 需短暂停写（切换点）大数据量（50GB–TB），速度快，但需要处理切换瞬间一致性。4️⃣ 物理迁移（全量 + 增量）xtrabackup full + incr + binlog✅ 是🚫 不停机最常见生产迁移方式，性能好，支持大规模数据库。5️⃣ 在线迁移服务云厂商 DTS / DMS 迁移✅ 实时增量🚫 不停机云上迁移首选方案，可跨网络、跨地域；自动处理一致性。6️⃣ 主从复制迁移自建 replication（目标库做从库）✅ 实时🚫 不停机最灵活、透明度最高；最终通过切换提升为主库。7️⃣ 离线迁移（隔离/无公网）闪电立方 + binlog 或 手动物理盘拷贝⚙️ 近实时（靠 binlog）🚫 不停机（短暂切换）网络不通、数据量超大（TB 级）时的最佳选择。8️⃣ 工具导入导出（GUI）DMS / Navicat / DataGrip❌ 无增量⚠️ 通常需要停写适合测试环境、小项目、结构简单的小库。
4、阿里云RDS
4.1 阿里云RDS概述
阿里云关系型数据库服务（RDS）是一种在云端提供的高可用性、可扩展、安全的关系型数据库服务。它支持多种数据库引擎，包括MySQL、PostgreSQL、Oracle等，并提供了丰富的监控、备份、恢复、容灾等功能，帮助企业快速构建和运维高可用、高性能的数据库系统。
4.2 阿里云RDS特点
高可用性：支持多可用区部署和主备自动切换，保障业务连续性和数据安全。
优越性能：采用读写分离、负载均衡等优化技术，提升数据库并发处理能力。
全面安全：多层安全防护体系，支持多种加密和隐私保护措施。
丰富运维：提供监控、自动备份和多种容灾工具，确保系统稳定运行和数据可快速恢复。
4.3 申请（购买）阿里云RDS
申请地址：
选择RDS配置信息
至少充值100元，否则不能购买云产品！后续阿里会不会改这个限制，那就看情况了。
进入RDS管理控制台，查看RDS实例：
4.4 配置与登陆RDS
单击实例数，进入RDS数据库中心，查看基本信息：
单击账号管理，为RDS添加账号 + 密码
创建高权限账号，root，密码为MySQL@666
账号创建成功后，如下图所示：
使用刚刚配置的账号与密码登录
4.5 创建DB数据库
创建db_itheima数据库，如下图所示
单击SQL查询菜单，进入DMS控制台，刷新已登录实例：
基于以上数据库，我们可以通过以下操作创建数据表：
案例1：基于DMS在db_itheima数据库中创建tb_students数据表，包含字段id、name、age、gender、mobile信息
示例代码：
CREATE TABLE `tb_students` (
id INT AUTO_INCREMENT,
name VARCHAR(20),
age TINYINT UNSIGNED,
gender ENUM('male', 'female'),
mobile CHAR(11),
PRIMARY KEY(id)
) DEFAULT CHARSET=UTF8;
运行效果：
或者
目前这里，先了解。
案例2：使用SQL插入测试数据
示例代码：
INSERT INTO `tb_students` VALUES (NULL, '张三', 18, 'male', '13575330001');
INSERT INTO `tb_students` VALUES (NULL, '李四', 19, 'male', '13575330002');
INSERT INTO `tb_students` VALUES (NULL, '王五', 20, 'male', '13575330003');
INSERT INTO `tb_students` VALUES (NULL, '赵六', 21, 'female', '13575330004');
INSERT INTO `tb_students` VALUES (NULL, '田七', 22, 'female', '13575330005');
INSERT INTO `tb_students` VALUES (NULL, '周八', 23, 'male', '13575330006');
INSERT INTO `tb_students` VALUES (NULL, '韩九', 24, 'female', '13575330007');
INSERT INTO `tb_students` VALUES (NULL, '钱十', 25, 'male', '13575330008');
运行效果：
或者
目前这里，先了解。
案例3：使用SQL实现数据查询操作
SELECT gender, COUNT(id) AS cnt FROM tb_students GROUP BY gender;
或者
目前这里，先了解。
4.5 开通RDS外网地址
rm-uf61e2b6m48iu4jd76o.mysql.rds.aliyuncs.com
mysql -h rm-uf61e2b6m48iu4jd76o.mysql.rds.aliyuncs.com -u root -p'MySQL@666'
[root@server2 ~]# mysql -h rm-uf61e2b6m48iu4jd76o.mysql.rds.aliyuncs.com -u root -p'MySQL@666'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1638
Server version: 8.0.36 Source distribution
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| __recycle_bin__ |
| db_itheima |
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
7 rows in set (0.04 sec)
mysql>
CREATE DATABASE `bookdb666` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
show databases;
use bookdb666;
CREATE TABLE book666 (ID int,Name char(16),Price int,Publishing char(16));
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('1','《Linux从入门到精通》','66','电子工业出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('2','《云计算趋势》','68','人民邮电出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('3','《操作系统设计与实现》','90','机械工业出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('4','《高性能MySQL1》','71','清华大学出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('5','《高性能MySQL2》','72','清华大学出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('6','《高性能MySQL3》','73','清华大学出版社');
show tables;
select * from book666;
create database testdb;
use testdb;
CREATE TABLE students (
id INT,
name VARCHAR(20),
age INT,
gender ENUM('male', 'female'),
score DECIMAL(11, 2),
cls_id INT
);
desc students;
INSERT INTO students (id, name, age, gender, score, cls_id) VALUES
(1, '张三', 18, 'male', 85.50, 101),
(2, '李四', 19, 'female', 92.00, 101),
(3, '王五', 20, 'male', 78.25, 102),
(4, '赵六', 17, 'female', 88.75, 102),
(5, '孙七', 21, 'male', 59.00, 103),
(6, '周八', 19, 'female', 96.50, 103);
select * from students;
备份数据
mysqldump -h rm-uf61e2b6m48iu4jd76o.mysql.rds.aliyuncs.com -u root -p'MySQL@666' \
--databases bookdb666 testdb \
--set-gtid-purged=OFF \
--single-transaction \
> database_backup_$(date +%Y%m%d).sql
拓展操作
[root@jaking ~]# cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)
[root@jaking ~]# ping rm-7xv76a2p44f38z4rquo.mysql.cn-guangzhou.rds.aliyuncs.com
PING rm-7xv76a2p44f38z4rquo.mysql.cn-guangzhou.rds.aliyuncs.com (8.148.170.99) 56(84) bytes of data.
64 bytes from 8.148.170.99 (8.148.170.99): icmp_seq=1 ttl=128 time=42.0 ms
64 bytes from 8.148.170.99 (8.148.170.99): icmp_seq=2 ttl=128 time=39.4 ms
^C
--- rm-7xv76a2p44f38z4rquo.mysql.cn-guangzhou.rds.aliyuncs.com ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1001ms
rtt min/avg/max/mdev = 39.430/40.721/42.013/1.307 ms
[root@jaking ~]# mysql -uroot -pMySQL@666
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44-log MySQL Community Server (GPL)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> ^DBye
[root@jaking ~]# mysql -uroot -pMySQL@666 -hrm-7xv76a2p44f38z4rquo.mysql.cn-guangzhou.rds.aliyuncs.com
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 326
Server version: 8.0.36 Source distribution
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| __recycle_bin__ |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.03 sec)
mysql> ^DBye
[root@jaking ~]# mysql -uroot -pMySQL@666 -h8.148.170.99
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 341
Server version: 8.0.36 Source distribution
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| __recycle_bin__ |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.03 sec)
mysql> ^DBye
[root@jaking ~]#
4.7 RDS数据导出与导入
思考1：如果我们希望把RDS上的数据导出到文件应该如何操作呢？
答：使用数据导出操作
实践步骤：
点击常用功能 => 数据导出 => 批量数据导出，选择合适的配置信息，如下图所示：
提交申请后，等待导出完成。导出完成后，可以通过下方按钮下载数据：
下载后得到一个压缩包，压缩包中就是我们导出的.sql文件了
补充：
思考2：我们想把本地的文件导入到RDS，该如何操作呢？
答：使用数据导入功能实现
实现步骤：
点击常用功能 => 数据导入 => 批量数据导入，选择合适的配置信息，如下图所示：
等待预检查，审批，执行，最终如下图所示：
返回数据库，查看数据是否导入成功，如下图所示：
可能会遇到以下问题：
解决方法：
表已经存在，不能重复创建。
如果是在生产环境，这个数据导入的操作就不用再进行了。
如果是在测试环境，可以考虑删除已经存在的表，然后再进行数据导入的操作。
[root@mysql80 ~]# mysql -uroot -pMySQL@666 -hrm-7xvaojw8t5v5uv3v3ro.mysql.cn-guangzhou.rds.aliyuncs.com
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3091
Server version: 8.0.36 Source distribution
Copyright (c) 2000, 2026, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| __recycle_bin__ |
| bookdb666 |
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
7 rows in set (0.05 sec)
mysql> use bookdb666;
shoReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------+
| Tables_in_bookdb666 |
+---------------------+
| book666 |
+---------------------+
1 row in set (0.04 sec)
mysql> drop table book666;
Query OK, 0 rows affected (0.05 sec)
mysql> show tables;
Empty set (0.04 sec)
mysql>
再次进行数据导入（重试）
或者
重新进行数据导入
补充：
[root@jaking ~]# mysql -uroot -pMySQL@666 -hrm-7xv8x04izq5287jqnzo.mysql.cn-guangzhou.rds.aliyuncs.com
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2064
Server version: 8.0.36 Source distribution
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| __recycle_bin__ |
| abc |
| abcd |
| abcde |
| abcdef |
| db_itheima |
| haha |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
11 rows in set (0.03 sec)
mysql> drop database db_itheima;
Query OK, 3 rows affected (0.03 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| __recycle_bin__ |
| abc |
| abcd |
| abcde |
| abcdef |
| haha |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
10 rows in set (0.03 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| __recycle_bin__ |
| abc |
| abcd |
| abcde |
| abcdef |
| db_itheima |
| haha |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
11 rows in set (0.03 sec)
mysql> use db_itheima
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------+
| Tables_in_db_itheima |
+----------------------+
| tb_goods |
| tb_order |
| tb_user |
+----------------------+
3 rows in set (0.03 sec)
mysql> select * from tb_goods;
+----------+----------------+----------+-------+-----------+---------------------+
| goods_id | goods_name | price | stock | category | create_time |
+----------+----------------+----------+-------+-----------+---------------------+
| 1 | iPhone 15 | 6999.00 | 20 | 手机 | 2025-11-28 10:58:29 |
| 2 | MacBook Pro 14 | 13999.00 | 10 | 笔记本 | 2025-11-28 10:58:29 |
| 3 | AirPods Pro 2 | 1999.00 | 30 | 耳机 | 2025-11-28 10:58:29 |
| 4 | iPad Air | 4599.00 | 15 | 平板 | 2025-11-28 10:58:29 |
+----------+----------------+----------+-------+-----------+---------------------+
4 rows in set (0.02 sec)
mysql> select * from tb_order;
+----------+---------+----------+----------+-------------+---------------------+---------+
| order_id | user_id | goods_id | quantity | total_price | order_time | status |
+----------+---------+----------+----------+-------------+---------------------+---------+
| 1 | 1 | 1 | 1 | 6999.00 | 2025-11-28 10:58:29 | paid |
| 2 | 2 | 3 | 2 | 3998.00 | 2025-11-28 10:58:29 | shipped |
| 3 | 3 | 4 | 1 | 4599.00 | 2025-11-28 10:58:29 | pending |
| 4 | 1 | 2 | 1 | 13999.00 | 2025-11-28 10:58:29 | paid |
+----------+---------+----------+----------+-------------+---------------------+---------+
4 rows in set (0.03 sec)
mysql> select * from tb_user;
+---------+----------+-----------+---------------------+-------------+---------------------+
| user_id | username | password | email | phone | create_time |
+---------+----------+-----------+---------------------+-------------+---------------------+
| 1 | alice | MySQL@666 | alice@example.com | 13800000001 | 2025-11-28 10:58:29 |
| 2 | bob | MySQL@666 | bob@example.com | 13800000002 | 2025-11-28 10:58:29 |
| 3 | charlie | MySQL@666 | charlie@example.com | 13800000003 | 2025-11-28 10:58:29 |
+---------+----------+-----------+---------------------+-------------+---------------------+
3 rows in set (0.03 sec)
mysql>
5、云端RDS MySQL与本地MySQL互相迁移数据
登录阿里云 RDS MySQL 数据库
mysql -uroot -pMySQL@666 -hrm-7xvaojw8t5v5uv3v3ro.mysql.cn-guangzhou.rds.aliyuncs.com
# mysql：MySQL 客户端登录工具
# -uroot：使用 root 用户登录
# -pMySQL@666：密码直接跟在 -p 后面（无空格）
# -h：指定数据库主机地址（这里是阿里云 RDS 地址）
mysql -uroot -pMySQL@666 -hrm-7xvaojw8t5v5uv3v3ro.mysql.cn-guangzhou.rds.aliyuncs.com
准备测试数据
DROP DATABASE IF EXISTS `bookdb666`;
CREATE DATABASE `bookdb666` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
show databases;
use bookdb666;
CREATE TABLE book666 (ID int,Name char(16),Price int,Publishing char(16));
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('1','《Linux从入门到精通》','66','电子工业出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('2','《云计算趋势》','68','人民邮电出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('3','《操作系统设计与实现》','90','机械工业出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('4','《高性能MySQL1》','71','清华大学出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('5','《高性能MySQL2》','72','清华大学出版社');
INSERT INTO book666(ID,Name,Price,Publishing) VALUES('6','《高性能MySQL3》','73','清华大学出版社');
show tables;
select * from book666;
DROP DATABASE IF EXISTS `testdb`;
create database testdb;
use testdb;
CREATE TABLE students (
id INT,
name VARCHAR(20),
age INT,
gender ENUM('male', 'female'),
score DECIMAL(11, 2),
cls_id INT
);
desc students;
INSERT INTO students (id, name, age, gender, score, cls_id) VALUES
(1, '张三', 18, 'male', 85.50, 101),
(2, '李四', 19, 'female', 92.00, 101),
(3, '王五', 20, 'male', 78.25, 102),
(4, '赵六', 17, 'female', 88.75, 102),
(5, '孙七', 21, 'male', 59.00, 103),
(6, '周八', 19, 'female', 96.50, 103);
select * from students;
备份数据库（生产环境安全备份）
mysqldump -uroot -p'MySQL@666' -hrm-7xvaojw8t5v5uv3v3ro.mysql.cn-guangzhou.rds.aliyuncs.com \
--databases bookdb666 \
--set-gtid-purged=OFF \
--single-transaction \
> database_backup_$(date +%Y%m%d).sql
# mysqldump：MySQL 官方备份工具
# -uroot：使用 root 用户
# -p'MySQL@666'：密码（带特殊符号，用单引号更安全）
# -h：阿里云 RDS 地址
mysqldump -uroot -p'MySQL@666' -hrm-7xvaojw8t5v5uv3v3ro.mysql.cn-guangzhou.rds.aliyuncs.com \
# 只备份指定的数据库：bookdb666
--databases bookdb666 \
# 关闭 GTID 相关配置（阿里云 RDS / 主从环境必须加，否则会报错）
--set-gtid-purged=OFF \
# 热备份，不锁表，不影响业务运行（InnoDB 引擎必备）
--single-transaction \
# 备份输出到文件，文件名自动带上当天日期
> database_backup_$(date +%Y%m%d).sql
[root@jaking ~]# mysqldump -uroot -p'MySQL@666' -hrm-7xvaojw8t5v5uv3v3ro.mysql.cn-guangzhou.rds.aliyuncs.com \
--databases bookdb666 \
--set-gtid-purged=OFF \
--single-transaction \
> database_backup_$(date +%Y%m%d).sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@jaking ~]# ls
anaconda-ks.cfg percona-toolkit-3.6.0-1.el9.x86_64.rpm
database_backup_20260427.sql selected_students.txt
Name_Zhengzhou_AIOps7.sh
[root@jaking ~]# vim database_backup_20260427.sql
[root@jaking ~]# head -n 26 database_backup_20260427.sql
-- MySQL dump 10.13 Distrib 8.0.46, for Linux (x86_64)
--
-- Host: rm-7xvaojw8t5v5uv3v3ro.mysql.cn-guangzhou.rds.aliyuncs.com Database: bookdb666
-- ------------------------------------------------------
-- Server version 8.0.36
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Current Database: `bookdb666`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `bookdb666` /*!40100 DEFAULT CHARACTER SET utf8mb3 */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `bookdb666`;
--
[root@jaking ~]#
把云端RDS的数据导入本地MySQL
# 1. 登录MySQL数据库
mysql -uroot -pMySQL@666
# 2. 查看所有数据库（MySQL内执行）
show databases;
# 3. 删除 bookdb666 数据库（MySQL内执行）
drop database bookdb666;
# 4. 再次查看数据库，确认已删除（MySQL内执行）
show databases;
# 5. 退出MySQL（MySQL内执行）
exit
# 6. 导入备份文件，恢复数据库（Linux终端执行）
mysql -uroot -pMySQL@666 < database_backup_20260427.sql
# 7. 重新登录MySQL，验证恢复结果（Linux终端执行）
mysql -uroot -pMySQL@666
# 8. 查看数据库是否恢复（MySQL内执行）
show databases;
# 9. 进入恢复后的库（MySQL内执行）
use bookdb666;
# 10. 查看表是否存在（MySQL内执行）
show tables;
# 11. 查询数据是否完整（MySQL内执行）
select * from book666;
[root@jaking ~]# mysql -uroot -pMySQL@666
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.46 MySQL Community Server - GPL
Copyright (c) 2000, 2026, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| bookdb666 |
| db_itheima |
| information_schema |
| mysql |
| percona |
| performance_schema |
| sys |
| testdb |
+--------------------+
8 rows in set (0.00 sec)
mysql> drop database bookdb666;
Query OK, 1 row affected (0.04 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db_itheima |
| information_schema |
| mysql |
| percona |
| performance_schema |
| sys |
| testdb |
+--------------------+
7 rows in set (0.00 sec)
mysql> ^DBye
[root@jaking ~]# ls
anaconda-ks.cfg percona-toolkit-3.6.0-1.el9.x86_64.rpm
database_backup_20260427.sql selected_students.txt
Name_Zhengzhou_AIOps7.sh
[root@jaking ~]# mysql -uroot -pMySQL@666 < database_backup_20260427.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@jaking ~]# mysql -uroot -pMySQL@666
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.46 MySQL Community Server - GPL
Copyright (c) 2000, 2026, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| bookdb666 |
| db_itheima |
| information_schema |
| mysql |
| percona |
| performance_schema |
| sys |
| testdb |
+--------------------+
8 rows in set (0.01 sec)
mysql> use bookdb666;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------+
| Tables_in_bookdb666 |
+---------------------+
| book666 |
+---------------------+
1 row in set (0.00 sec)
mysql> select * from book666;
+------+-----------------------------------+-------+-----------------------+
| ID | Name | Price | Publishing |
+------+-----------------------------------+-------+-----------------------+
| 1 | 《Linux从入门到精通》 | 66 | 电子工业出版社 |
| 2 | 《云计算趋势》 | 68 | 人民邮电出版社 |
| 3 | 《操作系统设计与实现》 | 90 | 机械工业出版社 |
| 4 | 《高性能MySQL1》 | 71 | 清华大学出版社 |
| 5 | 《高性能MySQL2》 | 72 | 清华大学出版社 |
| 6 | 《高性能MySQL3》 | 73 | 清华大学出版社 |
+------+-----------------------------------+-------+-----------------------+
6 rows in set (0.00 sec)
mysql>
把本地MySQL的数据导入云端RDS
先查看本地数据（如果没有的话就创建测试数据）
-- 1. 创建数据库 db_itheima（如果不存在则创建）
CREATE DATABASE IF NOT EXISTS db_itheima;
-- 2. 使用该数据库
USE db_itheima;
-- 3. 创建用户表 tb_user
DROP TABLE IF EXISTS tb_user;
CREATE TABLE tb_user (
user_id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL,
email VARCHAR(100),
phone VARCHAR(20),
create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- 4. 创建商品表 tb_goods
DROP TABLE IF EXISTS tb_goods;
CREATE TABLE tb_goods (
goods_id INT PRIMARY KEY AUTO_INCREMENT,
goods_name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT NOT NULL,
category VARCHAR(50),
create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- 5. 创建订单表 tb_order
DROP TABLE IF EXISTS tb_order;
CREATE TABLE tb_order (
order_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
goods_id INT NOT NULL,
quantity INT NOT NULL,
total_price DECIMAL(10,2) NOT NULL,
order_time DATETIME DEFAULT CURRENT_TIMESTAMP,
status VARCHAR(20)
);
-- 6. 插入用户测试数据
INSERT INTO tb_user (username, password, email, phone) VALUES
('alice', 'MySQL@666', 'alice@example.com', '13800000001'),
('bob', 'MySQL@666', 'bob@example.com', '13800000002'),
('charlie', 'MySQL@666', 'charlie@example.com', '13800000003');
-- 7. 插入商品测试数据
INSERT INTO tb_goods (goods_name, price, stock, category) VALUES
('iPhone 15', 6999.00, 20, '手机'),
('MacBook Pro 14', 13999.00, 10, '笔记本'),
('AirPods Pro 2', 1999.00, 30, '耳机'),
('iPad Air', 4599.00, 15, '平板');
-- 8. 插入订单测试数据
INSERT INTO tb_order (user_id, goods_id, quantity, total_price, status) VALUES
(1, 1, 1, 6999.00, 'paid'),
(2, 3, 2, 3998.00, 'shipped'),
(3, 4, 1, 4599.00, 'pending'),
(1, 2, 1, 13999.00, 'paid');
-- 9. 查看所有表
SHOW TABLES;
-- 10. 查询三张表数据（验证是否创建成功）
SELECT * FROM tb_user;
SELECT * FROM tb_goods;
SELECT * FROM tb_order;
# 登录MySQL数据库
mysql -uroot -pMySQL@666
# 查看MySQL中所有数据库
show databases;
# 切换使用 db_itheima 数据库
use db_itheima;
# 查看当前数据库下的所有数据表
show tables;
# 查询 tb_goods 商品表中的所有数据
select * from tb_goods;
# 查询 tb_order 订单表中的所有数据
select * from tb_order;
# 查询 tb_user 用户表中的所有数据
select * from tb_user;
[root@jaking ~]# mysql -uroot -pMySQL@666
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.46 MySQL Community Server - GPL
Copyright (c) 2000, 2026, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| bookdb666 |
| db_itheima |
| information_schema |
| mysql |
| percona |
| performance_schema |
| sys |
| testdb |
+--------------------+
8 rows in set (0.00 sec)
mysql> use db_itheima;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------+
| Tables_in_db_itheima |
+----------------------+
| tb_goods |
| tb_order |
| tb_user |
+----------------------+
3 rows in set (0.01 sec)
mysql> select * from tb_goods;
+----------+----------------+----------+-------+-----------+---------------------+
| goods_id | goods_name | price | stock | category | create_time |
+----------+----------------+----------+-------+-----------+---------------------+
| 1 | iPhone 15 | 6999.00 | 20 | 手机 | 2026-04-26 03:11:46 |
| 2 | MacBook Pro 14 | 13999.00 | 10 | 笔记本 | 2026-04-26 03:11:46 |
| 3 | AirPods Pro 2 | 1999.00 | 30 | 耳机 | 2026-04-26 03:11:46 |
| 4 | iPad Air | 4599.00 | 15 | 平板 | 2026-04-26 03:11:46 |
+----------+----------------+----------+-------+-----------+---------------------+
4 rows in set (0.00 sec)
mysql> select * from tb_order;
+----------+---------+----------+----------+-------------+---------------------+---------+
| order_id | user_id | goods_id | quantity | total_price | order_time | status |
+----------+---------+----------+----------+-------------+---------------------+---------+
| 1 | 1 | 1 | 1 | 6999.00 | 2026-04-26 03:11:46 | paid |
| 2 | 2 | 3 | 2 | 3998.00 | 2026-04-26 03:11:46 | shipped |
| 3 | 3 | 4 | 1 | 4599.00 | 2026-04-26 03:11:46 | pending |
| 4 | 1 | 2 | 1 | 13999.00 | 2026-04-26 03:11:46 | paid |
+----------+---------+----------+----------+-------------+---------------------+---------+
4 rows in set (0.00 sec)
mysql> select * from tb_user;
+---------+----------+-----------+---------------------+-------------+---------------------+
| user_id | username | password | email | phone | create_time |
+---------+----------+-----------+---------------------+-------------+---------------------+
| 1 | alice | MySQL@666 | alice@example.com | 13800000001 | 2026-04-26 03:11:46 |
| 2 | bob | MySQL@666 | bob@example.com | 13800000002 | 2026-04-26 03:11:46 |
| 3 | charlie | MySQL@666 | charlie@example.com | 13800000003 | 2026-04-26 03:11:46 |
+---------+----------+-----------+---------------------+-------------+---------------------+
3 rows in set (0.00 sec)
mysql>
备份数据库（生产环境安全备份）
mysqldump -uroot -p'MySQL@666' \
--databases db_itheima \
--set-gtid-purged=OFF \
--single-transaction \
> database_local_backup_$(date +%Y%m%d).sql
# 完整备份命令 + 逐行中文注释
mysqldump -uroot -p'MySQL@666' \ # 使用root用户、密码连接MySQL
--databases db_itheima \ # 指定要备份的数据库：db_itheima
--set-gtid-purged=OFF \ # 关闭GTID相关配置（避免导入时报错）
--single-transaction \ # 热备份，不锁表，业务正常运行
> database_local_backup_$(date +%Y%m%d).sql # 备份输出到文件，文件名带当天日期
[root@jaking ~]# mysqldump -uroot -p'MySQL@666' \
--databases db_itheima \
--set-gtid-purged=OFF \
--single-transaction \
> database_local_backup_$(date +%Y%m%d).sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@jaking ~]# ls
anaconda-ks.cfg Name_Zhengzhou_AIOps7.sh
database_backup_20260427.sql percona-toolkit-3.6.0-1.el9.x86_64.rpm
database_local_backup_20260427.sql selected_students.txt
[root@jaking ~]# vim database_local_backup_20260427.sql
[root@jaking ~]# tail -n 66 database_local_backup_20260427.sql
-- Table structure for table `tb_order`
--
DROP TABLE IF EXISTS `tb_order`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tb_order` (
`order_id` int NOT NULL AUTO_INCREMENT,
`user_id` int NOT NULL,
`goods_id` int NOT NULL,
`quantity` int NOT NULL,
`total_price` decimal(10,2) NOT NULL,
`order_time` datetime DEFAULT CURRENT_TIMESTAMP,
`status` varchar(20) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `tb_order`
--
LOCK TABLES `tb_order` WRITE;
/*!40000 ALTER TABLE `tb_order` DISABLE KEYS */;
INSERT INTO `tb_order` VALUES (1,1,1,1,6999.00,'2026-04-27 11:46:30','paid'),(2,2,3,2,3998.00,'2026-04-27 11:46:30','shipped'),(3,3,4,1,4599.00,'2026-04-27 11:46:30','pending'),(4,1,2,1,13999.00,'2026-04-27 11:46:30','paid');
/*!40000 ALTER TABLE `tb_order` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `tb_user`
--
DROP TABLE IF EXISTS `tb_user`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tb_user` (
`user_id` int NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`password` varchar(50) NOT NULL,
`email` varchar(100) DEFAULT NULL,
`phone` varchar(20) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `tb_user`
--
LOCK TABLES `tb_user` WRITE;
/*!40000 ALTER TABLE `tb_user` DISABLE KEYS */;
INSERT INTO `tb_user` VALUES (1,'alice','MySQL@666','alice@example.com','13800000001','2026-04-27 11:46:30'),(2,'bob','MySQL@666','bob@example.com','13800000002','2026-04-27 11:46:30'),(3,'charlie','MySQL@666','charlie@example.com','13800000003','2026-04-27 11:46:30');
/*!40000 ALTER TABLE `tb_user` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2026-04-27 11:49:06
[root@jaking ~]#
# 1. 远程连接阿里云RDS MySQL数据库（-h 指定远程服务器地址）
mysql -uroot -pMySQL@666 -hrm-7xvaojw8t5v5uv3v3ro.mysql.cn-guangzhou.rds.aliyuncs.com
# 2. 查看远程MySQL所有数据库（MySQL内执行）
show databases;
# 3. 退出远程MySQL连接（MySQL内执行）
exit;
# 4. 将本地备份文件导入远程阿里云MySQL，实现数据同步（Linux终端执行）
mysql -uroot -pMySQL@666 -hrm-7xvaojw8t5v5uv3v3ro.mysql.cn-guangzhou.rds.aliyuncs.com < database_local_backup_20260427.sql
# 5. 重新远程登录阿里云MySQL，验证数据是否导入成功
mysql -uroot -pMySQL@666 -hrm-7xvaojw8t5v5uv3v3ro.mysql.cn-guangzhou.rds.aliyuncs.com
# 6. 查看数据库，确认db_itheima已恢复（MySQL内执行）
show databases;
# 7. 切换到db_itheima数据库（MySQL内执行）
use db_itheima;
# 8. 查看库中所有表（MySQL内执行）
show tables;
# 9. 查询三张表数据，验证数据完整（MySQL内执行）
select * from tb_goods;
select * from tb_order;
select * from tb_user;
[root@jaking ~]# mysql -uroot -pMySQL@666 -hrm-7xvaojw8t5v5uv3v3ro.mysql.cn-guangzhou.rds.aliyuncs.com
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9249
Server version: 8.0.36 Source distribution
Copyright (c) 2000, 2026, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| __recycle_bin__ |
| bookdb666 |
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
7 rows in set (0.03 sec)
mysql> exit;
Bye
[root@jaking ~]# ls
anaconda-ks.cfg Name_Zhengzhou_AIOps7.sh
database_backup_20260427.sql percona-toolkit-3.6.0-1.el9.x86_64.rpm
database_local_backup_20260427.sql selected_students.txt
[root@jaking ~]# mysql -uroot -pMySQL@666 -hrm-7xvaojw8t5v5uv3v3ro.mysql.cn-guangzhou.rds.aliyuncs.com < database_local_backup_20260427.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@jaking ~]# mysql -uroot -pMySQL@666 -hrm-7xvaojw8t5v5uv3v3ro.mysql.cn-guangzhou.rds.aliyuncs.com
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9288
Server version: 8.0.36 Source distribution
Copyright (c) 2000, 2026, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| __recycle_bin__ |
| bookdb666 |
| db_itheima |
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
8 rows in set (0.04 sec)
mysql> use db_itheima
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------+
| Tables_in_db_itheima |
+----------------------+
| tb_goods |
| tb_order |
| tb_user |
+----------------------+
3 rows in set (0.04 sec)
mysql> select * from tb_goods;
+----------+----------------+----------+-------+-----------+---------------------+
| goods_id | goods_name | price | stock | category | create_time |
+----------+----------------+----------+-------+-----------+---------------------+
| 1 | iPhone 15 | 6999.00 | 20 | 手机 | 2026-04-27 11:46:30 |
| 2 | MacBook Pro 14 | 13999.00 | 10 | 笔记本 | 2026-04-27 11:46:30 |
| 3 | AirPods Pro 2 | 1999.00 | 30 | 耳机 | 2026-04-27 11:46:30 |
| 4 | iPad Air | 4599.00 | 15 | 平板 | 2026-04-27 11:46:30 |
+----------+----------------+----------+-------+-----------+---------------------+
4 rows in set (0.03 sec)
mysql> select * from tb_order;
+----------+---------+----------+----------+-------------+---------------------+---------+
| order_id | user_id | goods_id | quantity | total_price | order_time | status |
+----------+---------+----------+----------+-------------+---------------------+---------+
| 1 | 1 | 1 | 1 | 6999.00 | 2026-04-27 11:46:30 | paid |
| 2 | 2 | 3 | 2 | 3998.00 | 2026-04-27 11:46:30 | shipped |
| 3 | 3 | 4 | 1 | 4599.00 | 2026-04-27 11:46:30 | pending |
| 4 | 1 | 2 | 1 | 13999.00 | 2026-04-27 11:46:30 | paid |
+----------+---------+----------+----------+-------------+---------------------+---------+
4 rows in set (0.06 sec)
mysql> select * from tb_user;
+---------+----------+-----------+---------------------+-------------+---------------------+
| user_id | username | password | email | phone | create_time |
+---------+----------+-----------+---------------------+-------------+---------------------+
| 1 | alice | MySQL@666 | alice@example.com | 13800000001 | 2026-04-27 11:46:30 |
| 2 | bob | MySQL@666 | bob@example.com | 13800000002 | 2026-04-27 11:46:30 |
| 3 | charlie | MySQL@666 | charlie@example.com | 13800000003 | 2026-04-27 11:46:30 |
+---------+----------+-----------+---------------------+-------------+---------------------+
3 rows in set (0.04 sec)
mysql> ^DBye
[root@jaking ~]#
6、数据完整性验证
1️⃣ 验证数据库与表是否创建成功
SHOW DATABASES;
USE db_itheima;
SHOW TABLES;
-- 查询当前MySQL实例下所有数据库名称
SHOW DATABASES;
-- 切换并进入指定数据库 db_itheima，后续操作默认在此库执行
USE db_itheima;
-- 查询当前数据库内所有数据表名称
SHOW TABLES;
2️⃣ 验证表结构一致性
SHOW CREATE TABLE tb_goods\G
SHOW CREATE TABLE tb_order\G
SHOW CREATE TABLE tb_user\G
-- 查看 tb_user 表的完整建表语句（包含字段、类型、主键、索引、引擎、字符集等所有信息）
-- \G：将结果竖排格式化显示，更易读（替代分号 ; 使用）
SHOW CREATE TABLE tb_goods\G
SHOW CREATE TABLE tb_order\G
SHOW CREATE TABLE tb_user\G
3️⃣ 验证总记录数
SELECT COUNT(*) FROM tb_user;
SELECT COUNT(*) FROM tb_goods;
SELECT COUNT(*) FROM tb_order;
-- 统计 tb_user 用户表 总数据行数
SELECT COUNT(*) FROM tb_user;
-- 统计 tb_goods 商品表 总数据行数
SELECT COUNT(*) FROM tb_goods;
-- 统计 tb_order 订单表 总数据行数
SELECT COUNT(*) FROM tb_order;
4️⃣ 校验主键范围
SELECT MIN(user_id), MAX(user_id) FROM tb_user;
SELECT MIN(user_id), MAX(user_id) FROM tb_order;
源库与目标库的最小 / 最大主键值应相同
-- 查询 tb_user 表中 user_id 字段的 最小值 和 最大值
SELECT MIN(user_id), MAX(user_id) FROM tb_user;
SELECT MIN(user_id), MAX(user_id) FROM tb_order;
5️⃣ 哈希快速一致性验证（MD5 指纹）
# 源库
SELECT MD5(GROUP_CONCAT(user_id ORDER BY user_id)) AS hash_source FROM tb_user;
SELECT MD5(GROUP_CONCAT(user_id ORDER BY user_id)) AS hash_source FROM tb_order;
# 目标库
SELECT MD5(GROUP_CONCAT(user_id ORDER BY user_id)) AS hash_target FROM tb_user;
SELECT MD5(GROUP_CONCAT(user_id ORDER BY user_id)) AS hash_target FROM tb_order;
# 源库（主库/备份前的库）
# 1. 按user_id排序，把所有user_id拼接成一个长字符串
# 2. 对拼接后的字符串计算MD5哈希值，别名hash_source
SELECT MD5(GROUP_CONCAT(user_id ORDER BY user_id)) AS hash_source FROM tb_user;
SELECT MD5(GROUP_CONCAT(user_id ORDER BY user_id)) AS hash_source FROM tb_order;
# 目标库（从库/恢复后的库）
# 逻辑同上，计算目标库的MD5哈希值，别名hash_target
SELECT MD5(GROUP_CONCAT(user_id ORDER BY user_id)) AS hash_target FROM tb_user;
SELECT MD5(GROUP_CONCAT(user_id ORDER BY user_id)) AS hash_target FROM tb_order;
6️⃣ 抽样数据比对
SELECT * FROM tb_user WHERE user_id IN (1, 10, 100);
SELECT * FROM tb_goods WHERE goods_id IN (1, 10, 100);
SELECT * FROM tb_order WHERE user_id IN (1, 10, 100);
-- 查询 tb_user 表中 user_id 等于 1、10、100 的全部字段数据
SELECT * FROM tb_user WHERE user_id IN (1, 10, 100);
SELECT * FROM tb_goods WHERE goods_id IN (1, 10, 100);
SELECT * FROM tb_order WHERE user_id IN (1, 10, 100);
7️⃣ 订单表汇总统计对比
SELECT COUNT(*) AS total_orders, SUM(total_price) AS total_amount FROM tb_order;
-- 统计 tb_order 订单表的：总订单数 + 总销售金额
SELECT
COUNT(*) AS total_orders, -- 统计总记录数，别名 total_orders（总订单数）
SUM(total_price) AS total_amount -- 对 total_price 字段求和，别名 total_amount（销售总金额）
FROM tb_order;
四、DTS（Data Transmission Service）在线迁移
阿里云官方专用的不停机迁移工具，支持从本地 MySQL（IDC、自建机房）实时迁移至 RDS。
优势：
- 全量 + 增量同步
- 结构自动迁移（DDL）
- 支持校验、一致性检查
- 支持公网 / VPN / 专线 / 数据迁移网关连接
迁移流程概述：
[自建 MySQL]
│
├─① 结构迁移（DDL）
├─② 全量数据迁移
├─③ 增量同步（实时追平）
│
└──④ 业务切换到 RDS（延迟 < 1s）
1、Xtrabackup软件安装
Xtrabackup工具版本 8.0.35软件安装：
dnf localinstall percona-xtrabackup-80-8.0.35-31.1.el9.x86_64.rpm -y
2、创建备份用户并授权
需要的权限：
https://docs.percona.com/percona-xtrabackup/8.0/privileges.html
flush tables with read lock ：锁表
backup_admin：备份权限
REPLICATION CLIENT：备份时，需要读取二进制文件位置
进入到MySQL终端（先登录）：
CREATE USER 'admin'@'localhost' identified with mysql_native_password by 'MySQL@666';
GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'admin'@'localhost';
GRANT SELECT ON performance_schema.log_status TO 'admin'@'localhost';
GRANT SELECT ON performance_schema.keyring_component_status TO 'admin'@'localhost';
GRANT SELECT ON performance_schema.replication_group_members TO 'admin'@'localhost';
FLUSH PRIVILEGES;
performance_schema.log_status：该表存储有关 MySQL 服务器日志（如错误日志、查询日志、慢查询日志等）的状态信息。授权访问该表，可以使用户查询当前日志的状态信息。
performance_schema.keyring_component_status：该表存储有关 MySQL 加密密钥管理的信息。如果启用了 MySQL 密钥环（Keyring）插件并且配置了加密功能，授权访问此表可以让用户查看密钥环组件的状态。
performance_schema.replication_group_members：这个表包含有关 MySQL 复制组成员的信息，尤其是在 MySQL 8.0 及以上版本的组复制（Group Replication）设置中。如果你正在使用组复制，授权访问该表能让用户查看与复制成员相关的状态。
说明：
在数据库中需要以下权限：
RELOAD和LOCK TABLES权限：为了执行FLUSH TABLES WITH READ LOCK（针对MyISAM引擎）
REPLICATION CLIENT权限：为了获取binary log位置
PROCESS权限：显示有关在服务器中执行的线程的信息（即有关会话执行的语句的信息），允许使用SHOW ENGINE
3、Xtrabackup全量备份
第一步：创建全量备份
xtrabackup --user=admin --password=MySQL@666 --backup --target-dir=/full_xtrabackup
可能会遇到以下报错：
原因1：可能在/etc目录下还有my.cnf文件，影响了xtrabackup 的执行
原因2：xtrabackup 拥有自己的默认配置，默认读取了/var/lib/mysql/mysql.sock文件
解决方案：
方案1：把你的套接字文件创建一个软链接，放置于/var/lib/mysql/mysql.sock文件中（不推荐）
mkdir /var/lib/mysql
ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock
方案2：在xtrabackup中添加一个-S选项，执行套接字（推荐方案二）
xtrabackup -S /tmp/mysql.sock --user=admin --password=MySQL@666 --backup --target-dir=/full_xtrabackup
第二步：预备阶段，把备份这段时间内产生的日志整合到全量备份中
xtrabackup --user=admin --password=MySQL@666 --prepare --target-dir=/full_xtrabackup
4、购买阿里云ECS服务器
选择登录/注册菜单：
单击注册按钮：
填写注册账号与密码信息：
输入账号、密码信息：
进入管理控制台：
效果如下图所示：
阿里云充值（可选）
由于需要购买阿里云相关组件，如ECS、RDS、SLB等等，所以我们需要先对账号进行充值操作。
如下图所示：
选择充值菜单，进行充值操作。注：首次充值不得少于100元，否则后期有些组件无法购买！
领取免费ECS资源
选择合适的ECS配置：
设置完成后，点击前往控制台：
或者直接购买
远程连接ECS服务器
使用远程连接软件连接ECS
本次案例我们以MobaXterm远程连接为主，首先我们需要获取ECS服务器的公网地址，如下图所示：
在MobaXterm端填写阿里云ECS信息：
输入root管理员账号与密码：
回车，登录Linux操作系统，如下图所示：
ECS服务器简单测试，使用yum install sl -y安装sl软件，查看服务是否可用
yum install epel-release -y
yum install sl -y
安装完成后，效果如下图所示
运行sl命令，查看效果
运行结果：
Lin1ux老司机开火车
vim /etc/motd
_oo0oo_
088888880
88" . "88
(| -_- |)
0\ = /0
___/‘---‘\___
.‘ \\\\| |// ‘.
/ \\\\||| : |||// \
/_ ||||| -:- |||||- \
| | \\\\\\ - /// | |
| \_| ‘‘\---/‘‘ |_/ |
\ .-\__ ‘-‘ __/-. /
___‘. .‘ /--.--\ ‘. .‘___
."" ‘< ‘.___\_<|>_/___.‘ >‘ "".
| | : ‘- \‘.;‘\ _ /‘;.‘/ - ‘ : | |
\ \ ‘_. \_ __\ /__ _/ .-‘ / /
=====‘-.____‘.___ \_____/___.-‘____.-‘=====
‘=---=‘
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
佛祖保佑 iii 永不宕机
到此ECS远程连接服务器结束！
5、ECS安装MySQL8数据库
优化Linux系统
sed -i -r 's/SELINUX=[ep].*/SELINUX=disabled/g' /etc/selinux/config
# sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config &> /dev/null
setenforce 0
systemctl stop firewalld &> /dev/null
systemctl disable firewalld &> /dev/null
iptables -F
iptables -t nat -F
iptables -P INPUT ACCEPT
iptables -P FORWARD ACCEPT
dnf 安装 MySQL 8.0
CentOS Stream 9 dnf 安装 MySQL 8.0
二进制安装 MySQL 8.0
vim mysql8_install.sh
#!/bin/bash
if rpm -q libaio &> /dev/null; then
echo "libaio已安装，跳过安装"
else
echo "开始安装依赖"
dnf -y install libaio &> /dev/null
if [ $? -ne 0 ];then
echo "libaio安装失败"
exit 1
fi
fi
echo "进行解压操作"
if ls -l mysql-8.0.43-linux-glibc2.28-x86_64 &> /dev/null; then
echo "已解压，跳过"
else
if [ -f mysql-8.0.43-linux-glibc2.28-x86_64.tar.xz ]; then
tar -xf mysql-8.0.43-linux-glibc2.28-x86_64.tar.xz
ls -l mysql-8.0.43-linux-glibc2.28-x86_64
fi
fi
echo "判断是否安装过MariaDB，进行清理"
rpm -qa | grep mariadb | xargs -r dnf remove -y
if [ -f /etc/my.cnf ]; then
rm -rf /etc/my.cnf
fi
id mysql &> /dev/null
[ $? -ne 0 ] && useradd -r -s /sbin/nologin mysql
rm -rf /export/server
mkdir -p /export/server
cp -r mysql-8.0.43-linux-glibc2.28-x86_64 /export/server/mysql
chown -R mysql:mysql /export/server/mysql
echo "正在进入mysql目录，对其进行初始化操作..."
cd /export/server/mysql
bin/mysqld --initialize --user=mysql --basedir=/export/server/mysql --datadir=/export/server/mysql/data 2>&1 | tee /tmp/mysqld.log | grep password | awk '{print $NF}' > /tmp/mysql_temp_password.txt
bin/mysql_ssl_rsa_setup --datadir=/export/server/mysql/data &> /dev/null
cat >/etc/my.cnf<<EOF
[mysqld]
port=3306
basedir=/export/server/mysql
datadir=/export/server/mysql/data
socket=/tmp/mysql.sock
character_set_server=utf8
collation-server=utf8_unicode_ci
EOF
cat >/etc/systemd/system/mysqld.service<<EOF
[Unit]
Description=MySQL Server
After=network.target
[Service]
User=mysql
Group=mysql
Type=forking
# MySQL 执行命令及路径
ExecStart=/export/server/mysql/bin/mysqld --daemonize --pid-file=/export/server/mysql/data/mysqld.pid
ExecStop=/export/server/mysql/bin/mysqladmin --defaults-file=/export/server/mysql/my.cnf shutdown
# Ensure MySQL has sufficient time to start up
TimeoutSec=600
# PID 文件路径
PIDFile=/export/server/mysql/data/mysqld.pid
# Enable these options to auto-restart the service if it crashes
Restart=on-failure
RestartSec=5
[Install]
WantedBy=multi-user.target
EOF
echo "正在刷新后台服务，然后启动mysqld..."
systemctl daemon-reload
systemctl start mysqld
systemctl enable mysqld
#10.重置mysql管理员密码为MySQL@666
echo "正在重置mysql管理员密码..."
cd /export/server/mysql
temp_password=`cat /tmp/mysql_temp_password.txt`
bin/mysqladmin -uroot password 'MySQL@666' -p"$temp_password"
echo 'export PATH=$PATH:/export/server/mysql/bin' >> /etc/profile
source /etc/profile
mysql -V
echo "MySQL8安装成功，安装路径：/export/server/mysql，数据库初始密码：MySQL@666"
执行mysql8_install.sh脚本安装MySQL8
chmod +x mysql8_install.sh
source mysql8_install.sh
[root@jaking ~]# mysql -uroot -pMySQL@666
mysql: [Warning] Using a password on the command line interface can be insecure .
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.43 MySQL Community Server - GPL
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
mysql> ^DBye
[root@jaking ~]# cat /etc/redhat-release
CentOS Stream release 9
[root@jaking ~]# dmidecode -s system-manufacturer
Alibaba Cloud
[root@jaking ~]# dmidecode -s system-product-name
Alibaba Cloud ECS
[root@jaking ~]#
6、ECS安装Xtrabackup实现备份与还原数据
从本地 scp percona-xtrabackup 软件包到 ECS 服务器
或者
直接在 ECS 服务器上用 wget 下载 percona-xtrabackup 软件包
Xtrabackup工具版本 8.0.35软件安装：
dnf localinstall percona-xtrabackup-80-8.0.35-31.1.el9.x86_64.rpm -y
创建备份用户并授权，xtrabackup需要的权限：
https://docs.percona.com/percona-xtrabackup/8.0/privileges.html
flush tables with read lock ：锁表
backup_admin：备份权限
REPLICATION CLIENT：备份时，需要读取二进制文件位置
进入到MySQL终端（先登录）：
[root@mysql ~]# mysql -uroot -pMySQL@666
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.43 MySQL Community Server - GPL
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| bookdb666 |
| db_itheima |
| information_schema |
| mysql |
| percona |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.04 sec)
[root@iZ7xv5mmvqbxz2p7xenoxjZ ~]# mysql -uroot -pMySQL@666
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.46 MySQL Community Server - GPL
Copyright (c) 2000, 2026, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
create user 'admin'@'localhost' identified with mysql_native_password by 'MySQL@666';
GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'admin'@'localhost';
GRANT SELECT ON performance_schema.log_status TO 'admin'@'localhost';
GRANT SELECT ON performance_schema.replication_group_members TO 'admin'@'localhost';
GRANT SELECT ON performance_schema.keyring_component_status TO 'admin'@'localhost';
flush privileges;
performance_schema.log_status：该表存储有关 MySQL 服务器日志（如错误日志、查询日志、慢查询日志等）的状态信息。授权访问该表，可以使用户查询当前日志的状态信息。
performance_schema.replication_group_members：这个表包含有关 MySQL 复制组成员的信息，尤其是在 MySQL 8.0 及以上版本的组复制（Group Replication）设置中。如果你正在使用组复制，授权访问该表能让用户查看与复制成员相关的状态。
performance_schema.keyring_component_status：该表存储有关 MySQL 加密密钥管理的信息。如果启用了 MySQL 密钥环（Keyring）插件并且配置了加密功能，授权访问此表可以让用户查看密钥环组件的状态。
dmidecode -s system-product-name
说明：
在数据库中需要以下权限：
RELOAD和LOCK TABLES权限：为了执行FLUSH TABLES WITH READ LOCK（针对MyISAM引擎）
REPLICATION CLIENT权限：为了获取binary log位置
PROCESS权限：显示有关在服务器中执行的线程的信息（即有关会话执行的语句的信息），允许使用SHOW ENGINE
把本地的MySQL数据传输到云端
如果还没有测试数据的话，请参考以下操作
create database db_itheima default charset=utf8;
use db_itheima;
show databases;
CREATE TABLE tb_user (
user_id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
password VARCHAR(100) NOT NULL,
email VARCHAR(100),
phone VARCHAR(20),
create_time DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE tb_goods (
goods_id INT PRIMARY KEY AUTO_INCREMENT,
goods_name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT DEFAULT 0,
category VARCHAR(50),
create_time DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE tb_order (
order_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
goods_id INT NOT NULL,
quantity INT NOT NULL DEFAULT 1,
total_price DECIMAL(10,2) NOT NULL,
order_time DATETIME DEFAULT CURRENT_TIMESTAMP,
status VARCHAR(20) DEFAULT 'pending',
FOREIGN KEY (user_id) REFERENCES tb_user(user_id),
FOREIGN KEY (goods_id) REFERENCES tb_goods(goods_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO tb_user (username, password, email, phone) VALUES
('alice', 'MySQL@666', 'alice@example.com', '13800000001'),
('bob', 'MySQL@666', 'bob@example.com', '13800000002'),
('charlie', 'MySQL@666', 'charlie@example.com', '13800000003');
select * from tb_user;
INSERT INTO tb_goods (goods_name, price, stock, category) VALUES
('iPhone 15', 6999.00, 20, '手机'),
('MacBook Pro 14', 13999.00, 10, '笔记本'),
('AirPods Pro 2', 1999.00, 30, '耳机'),
('iPad Air', 4599.00, 15, '平板');
select * from tb_goods;
INSERT INTO tb_order (user_id, goods_id, quantity, total_price, status) VALUES
(1, 1, 1, 6999.00, 'paid'),
(2, 3, 2, 3998.00, 'shipped'),
(3, 4, 1, 4599.00, 'pending'),
(1, 2, 1, 13999.00, 'paid');
select * from tb_order;
show tables;
在本地用Xtrabackup做一次全备，记得提前安装Xtrabackup
xtrabackup --user=admin --password=MySQL@666 --backup --target-dir=/full_xtrabackup
xtrabackup --user=admin --password=MySQL@666 --prepare --target-dir=/full_xtrabackup
或者
xtrabackup -S /tmp/mysql.sock --user=admin --password=MySQL@666 --backup --target-dir=/full_xtrabackup
xtrabackup --user=admin --password=MySQL@666 --prepare --target-dir=/full_xtrabackup
[root@localhost ~]# xtrabackup -S /tmp/mysql.sock --user=admin --password=MySQL@666 --backup --target-dir=/full_xtrabackup
[root@localhost ~]# xtrabackup --user=admin --password=MySQL@666 --prepare --target-dir=/full_xtrabackup
[root@localhost ~]# ls /
afs boot etc full_xtrabackup lib media opt root sbin sys usr
bin dev export home lib64 mnt proc run srv tmp var
[root@localhost ~]# ls /full_xtrabackup/
backup-my.cnf '#innodb_redo' xtrabackup_binlog_info
binlog.000005 mysql xtrabackup_checkpoints
binlog.index mysql.ibd xtrabackup_info
db_itheima performance_schema xtrabackup_logfile
ib_buffer_pool sys xtrabackup_tablespaces
ibdata1 undo_001
ibtmp1 undo_002
[root@localhost ~]#
[root@localhost ~]# mysql -uroot -pMySQL@666
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.43 MySQL Community Server - GPL
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| bank_system |
| bookdb666 |
| db_itheima |
| information_schema |
| mysql |
| percona |
| performance_schema |
| sys |
| testdb |
| testdb2025 |
+--------------------+
10 rows in set (0.01 sec)
mysql> ^DBye
[root@localhost ~]# ls /
afs dev full_xtrabackup lib64 mysql-backup root srv usr
bin etc home media opt run sys var
boot export lib mnt proc sbin tmp
[root@localhost ~]# tar czvf full_xtrabackup.tar.gz /full_xtrabackup/*
tar: Removing leading `/' from member names
/full_xtrabackup/backup-my.cnf
tar: Removing leading `/' from hard link targets
/full_xtrabackup/bank_system/
/full_xtrabackup/bank_system/bank.ibd
/full_xtrabackup/binlog.000023
/full_xtrabackup/binlog.index
/full_xtrabackup/bookdb666/
/full_xtrabackup/bookdb666/book666.ibd
/full_xtrabackup/db_itheima/
/full_xtrabackup/db_itheima/tb_user2.ibd
/full_xtrabackup/db_itheima/tb_user3.ibd
/full_xtrabackup/db_itheima/tb_user4.ibd
/full_xtrabackup/db_itheima/tb2_people.ibd
/full_xtrabackup/db_itheima/students.ibd
/full_xtrabackup/db_itheima/tb_people.ibd
/full_xtrabackup/db_itheima/tb_test_myisam_404.sdi
/full_xtrabackup/db_itheima/tb_test_myisam.MYI
/full_xtrabackup/db_itheima/tb_test_myisam.MYD
/full_xtrabackup/ib_buffer_pool
...省略部分输出信息...
[root@localhost ~]# ls -hl full_xtrabackup.tar.gz
-rw-r--r--. 1 root root 74M Nov 25 22:49 full_xtrabackup.tar.gz
[root@localhost ~]# scp full_xtrabackup.tar.gz root@8.138.148.141:/root
The authenticity of host '8.138.148.141 (8.138.148.141)' can't be established.
ED25519 key fingerprint is SHA256:7BQ/OLWPUvh8/r8AS67cQ3M/UHQQsX8TLzu5vL9vnpc.
This key is not known by any other names
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '8.138.148.141' (ED25519) to the list of known hosts.
root@8.138.148.141's password:
full_xtrabackup.tar.gz 100% 73MB 6.7MB/s 00:10
[root@localhost ~]#
[root@jaking ~]# ls -hl full_xtrabackup.tar.gz
-rw-r--r-- 1 root root 74M Nov 25 22:51 full_xtrabackup.tar.gz
[root@jaking ~]# dmidecode -s system-product-name
Alibaba Cloud ECS
[root@jaking ~]# tar xf full_xtrabackup.tar.gz
[root@jaking ~]# ls
full_xtrabackup
full_xtrabackup.tar.gz
mysql-8.0.43-linux-glibc2.28-x86_64
mysql-8.0.43-linux-glibc2.28-x86_64.tar.xz
mysql8_install.sh
percona-xtrabackup-80-8.0.35-31.1.el9.x86_64.rpm
快速还原数据库中的数据
停止 MySQL 数据库服务
systemctl stop mysqld # 看情况而定，有时候可能不需要停服
还原数据
rm -rf /var/lib/mysql # 或者 mv /var/lib/mysql /tmp
xtrabackup --copy-back --target-dir=/root/full_xtrabackup
或者
rm -rf /export/server/mysql/data
xtrabackup --copy-back --target-dir=/root/full_xtrabackup
第一次还原可能会报错
...
Error: datadir must be specified.
出现以上问题的主要原因在于，xtrabackup 工具无法找到MySQL中的数据目录
解决方案：把my.cnf配置文件传递给xtrabackup ，让其自动识别这个文件中的datadir
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/full_xtrabackup
如果xtrabackup --copy-back返回结果为`Completed OK!'，代表数据真正恢复成功
ll /var/lib/mysql
或者
ll /export/server/mysql/data
[root@jaking ~]# ls /export/server/mysql/
bin data docs include lib LICENSE man README share support-files
[root@jaking ~]# ls /export/server/mysql/data/
bank_system ib_buffer_pool mysql.ibd testdb2025
binlog.000023 ibdata1 percona undo_001
binlog.index ibtmp1 performance_schema undo_002
bookdb666 '#innodb_redo' sys xtrabackup_info
db_itheima mysql testdb
[root@jaking ~]# ll /export/server/mysql/data/
total 116784
drwxr-x--- 2 root root 22 Nov 25 22:53 bank_system
-rw-r----- 1 root root 237 Nov 25 22:53 binlog.000023
-rw-r----- 1 root root 14 Nov 25 22:53 binlog.index
drwxr-x--- 2 root root 25 Nov 25 22:53 bookdb666
drwxr-x--- 2 root root 4096 Nov 25 22:53 db_itheima
-rw-r----- 1 root root 18088 Nov 25 22:53 ib_buffer_pool
-rw-r----- 1 root root 12582912 Nov 25 22:53 ibdata1
-rw-r----- 1 root root 12582912 Nov 25 22:53 ibtmp1
drwxr-x--- 2 root root 6 Nov 25 22:53 '#innodb_redo'
drwxr-x--- 2 root root 143 Nov 25 22:53 mysql
-rw-r----- 1 root root 27262976 Nov 25 22:53 mysql.ibd
drwxr-x--- 2 root root 27 Nov 25 22:53 percona
drwxr-x--- 2 root root 8192 Nov 25 22:53 performance_schema
drwxr-x--- 2 root root 28 Nov 25 22:53 sys
drwxr-x--- 2 root root 176 Nov 25 22:53 testdb
drwxr-x--- 2 root root 20 Nov 25 22:53 testdb2025
-rw-r----- 1 root root 50331648 Nov 25 22:53 undo_001
-rw-r----- 1 root root 16777216 Nov 25 22:53 undo_002
-rw-r----- 1 root root 504 Nov 25 22:53 xtrabackup_info
[root@jaking ~]#
第五步：还原数据时，一定要记得更改/export/server/mysql/data目录下的文件拥有者以及所属组权限，否则mysql无法启动
chown -R mysql:mysql /export/server/mysql/data
第六步：启动MySQL，测试其是否正常
创建.err日志并设置权限为mysql.mysql（这部分可以忽略）
touch /export/server/mysql/主机名称.err
chown mysql.mysql /export/server/mysql/主机名称.err
systemctl restart mysqld
mysql -uroot -pMySQL@666
常见问题说明
问题1：不喜欢读错误
解决方案：遇到问题时，往前或者往后预读1-2行，往往都能找到问题！
问题2：喜欢按照自己想法去修改文件，如/etc/my.cnf文件
[mysqld]
port=3306
basedir=/export/server/mysql
datadir=/export/server/mysql/data
socket=/tmp/mysql.sock
character_set_server=utf8
collation-server=utf8_unicode_ci
# 开启慢查询日志
slow_query_log=1
# 指定慢查询日志文件存放路径
slow_query_log_file=/export/server/mysql/logs/mysql-slow.log
# 设置超过 1 秒的查询被记录
long_query_time=1
# 记录未使用索引的查询（可选）
log_queries_not_using_indexes=0
server-id=10
log_error=/export/server/mysql/logs/error.log
log-bin=/export/server/mysql/data/binlog
# 设置binlog日志存储格式，默认对sql语句进行编码，无法直观查看对应SQL语句
binlog_format=statement
# 设置密码验证插件，从mysql5.7密码验证发生了改变，可能会导致很多客户端无法连接MySQL服务器端
default_authentication_plugin=mysql_native_password
问题3：要确认最终文件以及权限
备份：备份完成后，确认备份目录下有没有生成备份文件，终端有没有提示Complete Ok!
还原：xtrabackup软件会到/etc/my.cnf中找datadir目录，所以必须要有这一行
还原后：我们的data文件夹中的所有数据都是root:root，必须更改为mysql:mysql，否则mysqld无法启动！
7、阿里云DTS工具使用
DTS数据迁移服务
免费试用DTS
进入管理控制台-数据迁移-创建任务：
创建mysql_to_rds任务（记得在ECS上创建好MySQL以及购买好RDS MySQL）：
ECS MySQL
[root@jaking ~]# dmidecode -s system-product-name Alibaba Cloud ECS
[root@jaking ~]# mysql -uroot -pMySQL@666 mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.46 MySQL Community Server - GPL
Copyright (c) 2000, 2026, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| bookdb666 |
| db_itheima |
| information_schema |
| mysql |
| percona |
| performance_schema |
| sys |
| testdb |
+--------------------+
8 rows in set (0.00 sec)
mysql>
RDS MySQL
[root@jaking ~]# dmidecode -s system-product-name VMware Virtual Platform
[root@jaking ~]# mysql -uroot -pMySQL@666 -hrm-7xvaojw8t5v5uv3v3ro.mysql.cn-guangzhou.rds.aliyuncs.com
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 38404
Server version: 8.0.36 Source distribution
Copyright (c) 2000, 2026, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| __recycle_bin__ |
| bookdb666 |
| db_itheima |
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
8 rows in set (0.04 sec)
mysql> drop database db_itheima;
Query OK, 4 rows affected (0.04 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| __recycle_bin__ |
| bookdb666 |
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
7 rows in set (0.04 sec)
mysql>
单击测试连接以进行下一步：
单击下一步高级配置：
校检完成后，保存并返回列表
任务运行结果：
可能会遇到的问题：
解决方法：
源端
[root@jaking ~]# dmidecode -s system-product-name
Alibaba Cloud ECS
[root@jaking ~]# mysql -uroot -pMySQL@666
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 8.0.43 MySQL Community Server - GPL
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'MySQL@666';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT SELECT, RELOAD, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'root'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> ^DBye
[root@jaking ~]#
解决问题后再检测，会出现以下页面：
接下来在目标端执行以下操作
-- 创建 root@DTS_IP
-- DROP USER 'root'@'%';
CREATE USER 'root'@'%' IDENTIFIED BY 'MySQL@666';
-- 授权仅限 DTS 迁移所需权限
GRANT SELECT, RELOAD, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER
ON *.* TO 'root'@'%';
-- 刷新权限
FLUSH PRIVILEGES;
在目标数据库创建db_itheima数据库
或者
[root@jaking ~]# mysql -uroot -pMySQL@666 -hrm-7xv76a2p44f38z4rquo.mysql.cn-guangzhou.rds.aliyuncs.com
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5444
Server version: 8.0.36 Source distribution
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| __recycle_bin__ |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.03 sec)
mysql> create database db_itheima;
Query OK, 1 row affected (0.03 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| __recycle_bin__ |
| db_itheima |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.03 sec)
mysql>
检查结果:失败
失败原因:
源库账号缺乏如下权限
REPLICATION SLAVE,REPLICATION CLIENT
解决方案:
在源库中进行赋权
grant REPLICATION SLAVE,REPLICATION CLIENT on *.* TO 'root'@'%'
[root@jaking ~]# dmidecode -s system-product-name
Alibaba Cloud ECS
[root@jaking ~]# mysql -uroot -pMySQL@666
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 95
Server version: 8.0.43 MySQL Community Server - GPL
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant REPLICATION SLAVE,REPLICATION CLIENT on *.* TO 'root'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql>
在ECS自建MySQL源数据库执行以下命令
GRANT REPLICATION CLIENT ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;
[root@jaking ~]# dmidecode -s system-product-name
Alibaba Cloud ECS
[root@jaking ~]# mysql -uroot -pMySQL@666
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 126
Server version: 8.0.43 MySQL Community Server - GPL
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| bank_system |
| bookdb666 |
| db_itheima |
| information_schema |
| mysql |
| percona |
| performance_schema |
| sys |
| testdb |
| testdb2025 |
+--------------------+
10 rows in set (0.00 sec)
mysql> use db_itheima
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------+
| Tables_in_db_itheima |
+----------------------+
| students |
| tb2_people |
| tb_people |
| tb_test_myisam |
| tb_user2 |
| tb_user3 |
| tb_user4 |
+----------------------+
7 rows in set (0.00 sec)
mysql>
8、基于DTS实现数据校验
DTS迁移工具可能有变化，如果要校验的话，记得勾选数据校验方式。
可以考虑删除当前DTS迁移任务，然后重新创建DTS迁移任务。
[root@jaking ~]# mysql -h rm-7xv39i64561e23mnb1o.mysql.cn-guangzhou.rds.aliyuncs.com -uroot -pMySQL@666
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 8.0.36 Source distribution
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| __recycle_bin__ |
| db_itheima |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.09 sec)
mysql> create database testdb;
Query OK, 1 row affected (0.13 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| __recycle_bin__ |
| db_itheima |
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
7 rows in set (0.08 sec)
mysql> use db_itheima
Database changed
mysql> show tables;
Empty set (0.10 sec)
mysql> CREATE TABLE book666 (ID int,Name char(16),Price int,Publishing char(16));
Query OK, 0 rows affected (0.10 sec)
mysql> show tables;
+----------------------+
| Tables_in_db_itheima |
+----------------------+
| book666 |
+----------------------+
1 row in set (0.05 sec)
mysql> drop table book666;
Query OK, 0 rows affected (0.06 sec)
mysql> show tables;
+----------------------+
| Tables_in_db_itheima |
+----------------------+
| students |
| tb2_people |
| tb_people |
| tb_test_myisam |
| tb_user2 |
| tb_user3 |
| tb_user4 |
+----------------------+
7 rows in set (0.06 sec)
mysql> desc students;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
| age | int | YES | MUL | NULL | |
| gender | enum('male','female') | YES | | NULL | |
| score | decimal(11,2) | YES | | NULL | |
| cls_id | int | YES | MUL | NULL | |
+--------+-----------------------+------+-----+---------+-------+
6 rows in set (0.05 sec)
mysql> desc tb_people;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| name | varchar(50) | YES | UNI | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.10 sec)
mysql>
mysql> select * from tb_people limit 10;
+---------+--------------+------+
| id | name | age |
+---------+--------------+------+
| 1552962 | user_1552962 | 22 |
| 1552963 | user_1552963 | 41 |
| 1552964 | user_1552964 | 40 |
| 1552965 | user_1552965 | 58 |
| 1552966 | user_1552966 | 28 |
| 1552967 | user_1552967 | 33 |
| 1552968 | user_1552968 | 22 |
| 1552969 | user_1552969 | 35 |
| 155297 | user_155297 | 34 |
| 1552970 | user_1552970 | 50 |
+---------+--------------+------+
10 rows in set (0.08 sec)
mysql> select * from tb_people limit 10;
+---------+--------------+------+
| id | name | age |
+---------+--------------+------+
| 1552962 | user_1552962 | 22 |
| 1552963 | user_1552963 | 41 |
| 1552964 | user_1552964 | 40 |
| 1552965 | user_1552965 | 58 |
| 1552966 | user_1552966 | 28 |
| 1552967 | user_1552967 | 33 |
| 1552968 | user_1552968 | 22 |
| 1552969 | user_1552969 | 35 |
| 155297 | user_155297 | 34 |
| 1552970 | user_1552970 | 50 |
+---------+--------------+------+
10 rows in set (0.12 sec)
mysql> select * from tb_people where id=1552025;
+---------+--------------+------+
| id | name | age |
+---------+--------------+------+
| 1552025 | user_1552025 | 58 |
+---------+--------------+------+
1 row in set (0.82 sec)
mysql>
[root@jaking ~]# mysql -uroot -pMySQL@666 -hrm-0jlay494500kste3f0o.mysql.rds.aliyuncs.com
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 823
Server version: 8.0.36 Source distribution
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| __recycle_bin__ |
| db_itheima |
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
7 rows in set (0.02 sec)
mysql> use db_itheima
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------+
| Tables_in_db_itheima |
+----------------------+
| tb_goods |
| tb_order |
| tb_user |
+----------------------+
3 rows in set (0.02 sec)
mysql> select * from tb_goods;
+----------+----------------+----------+-------+-----------+---------------------+
| goods_id | goods_name | price | stock | category | create_time |
+----------+----------------+----------+-------+-----------+---------------------+
| 1 | iPhone 15 | 6999.00 | 20 | 手机 | 2025-11-30 11:03:19 |
| 2 | MacBook Pro 14 | 13999.00 | 10 | 笔记本 | 2025-11-30 11:03:19 |
| 3 | AirPods Pro 2 | 1999.00 | 30 | 耳机 | 2025-11-30 11:03:19 |
| 4 | iPad Air | 4599.00 | 15 | 平板 | 2025-11-30 11:03:19 |
+----------+----------------+----------+-------+-----------+---------------------+
4 rows in set (0.02 sec)
mysql> select * from tb_order;
+----------+---------+----------+----------+-------------+---------------------+---------+
| order_id | user_id | goods_id | quantity | total_price | order_time | status |
+----------+---------+----------+----------+-------------+---------------------+---------+
| 1 | 1 | 1 | 1 | 6999.00 | 2025-11-30 11:03:19 | paid |
| 2 | 2 | 3 | 2 | 3998.00 | 2025-11-30 11:03:19 | shipped |
| 3 | 3 | 4 | 1 | 4599.00 | 2025-11-30 11:03:19 | pending |
| 4 | 1 | 2 | 1 | 13999.00 | 2025-11-30 11:03:19 | paid |
+----------+---------+----------+----------+-------------+---------------------+---------+
4 rows in set (0.02 sec)
mysql> select * from tb_user;
+---------+----------+-----------+---------------------+-------------+---------------------+
| user_id | username | password | email | phone | create_time |
+---------+----------+-----------+---------------------+-------------+---------------------+
| 1 | alice | MySQL@666 | alice@example.com | 13800000001 | 2025-11-30 11:03:19 |
| 2 | bob | MySQL@666 | bob@example.com | 13800000002 | 2025-11-30 11:03:19 |
| 3 | charlie | MySQL@666 | charlie@example.com | 13800000003 | 2025-11-30 11:03:19 |
+---------+----------+-----------+---------------------+-------------+---------------------+
3 rows in set (0.03 sec)
mysql>
五、NineData数据迁移（拓展）
1、NineData企业自研平台
NineData 是集成了数据库 DevOps、数据复制、数据备份、数据对比多个模块的云服务，支持混合云（自建库+云数据库的业务架构）和多云（多个不同云厂商数据库组成的业务架构）架构下的企业数据管理，大幅降低企业的数据运维难度和成本。
https://www.ninedata.cloud/docker
2、功能说明
功能介绍数据库 DevOps在线查询与管理数据的云原生数据库 DevOps 工具，支持多种数据库类型，提供数据查询、数据库 DevOps、SQL 定时任务、数据追踪与回滚、数据归档与清理、SQL 代码审核、慢查询分析、批量数据库变更、库表分组查询与变更、测试数据生成、数据导入导出等能力。随时随地的团队协作机制以及可定制化的 SQL 开发规范能力，实现高效、安全的 SQL 开发。备份与恢复支持多环境、多类型数据库的备份恢复功能。具备秒级 RPO、细粒度数据恢复、备份数据在线查询等能力。数据复制支持同构、异构数据源之间实时、批量数据复制。实现数据迁移、异地容灾、数据库多活、数据仓库及数据湖的数据集成等业务场景。数据对比对比两个数据源的结构定义以及数据的一致性。不一致时可自动生成数据变更 SQL 语句，在目标端执行即可修复差异数据，保证两个数据源的一致性。数据对比支持的数据库对象包括表、存储过程、函数、触发器、视图等。
3、NineData优势
多环境、多云无缝对接：支持多个云厂商、自建数据库之间的数据无缝流动及管理。支持 AWS、阿里云、华为云及腾讯云等云平台。基于自研网络技术，可通过私网安全访问用户在 VPC 网络中的数据源。通过 NineData 的专属集群及数据网关，实现自建数据源的安全访问及管理。
丰富的数据源：支持同构、异构数据源的统一管理。随着企业的业务发展，单一的数据库架构难以满足业务需求，而组合多种类型数据库的数据管理方式通常需要采用很多不同的工具，无形中提高了运维门槛和成本。通过 NineData 平台，可以脱离繁杂的工具，轻松管理不同的数据库类型。
全链路数据安全：数据作为企业核心生产要素，数据安全掌握着企业的命脉。NineData 围绕数据的整个生命周期，提供端到端的数据加密能力有效防止未经授权的数据查询及操作行为，规避数据泄漏带来的业务影响。
SaaS 模式：即开即用，可快速开启企业级数据管理，无需规划服务器和预安装软件。同时，NineData 还提供完善的任务管理能力，包括全链路监控、报警、异常定位及自主修复能力，可大幅降低企业的运维投入。
高可用：所有服务采用高可用架构，存在多个节点，可在任意节点出现故障的情况下，快速将请求或任务切换至正常节点运行。同时，复制、备份及对比等长时间运行的任务支持无损断点续传，一旦任务运行的节点出现异常，可在节点切换后继续传输数据。
4、安装Docker
建议用阿里云ECS来部署
CPU 8C 32G
存储 100G
Welcome to Alibaba Cloud Elastic Compute Service !
Activate the web console with: systemctl enable --now cockpit.socket
Last login: Wed Nov 26 22:29:50 2025 from 123.160.164.186
[root@ninedata ~]# grep -c 'processor' /proc/cpuinfo
8
[root@ninedata ~]# free -h
total used free shared buff/cache available
Mem: 30Gi 739Mi 28Gi 10Mi 767Mi 29Gi
Swap: 0B 0B 0B
[root@ninedata ~]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 4.0M 0 4.0M 0% /dev
tmpfs 16G 0 16G 0% /dev/shm
tmpfs 6.1G 8.6M 6.1G 1% /run
efivarfs 256K 7.4K 244K 3% /sys/firmware/efi/efivars
/dev/vda3 100G 4.4G 96G 5% /
/dev/vda2 200M 7.1M 193M 4% /boot/efi
tmpfs 3.1G 0 3.1G 0% /run/user/0
[root@ninedata ~]# dmidecode -t system | grep 'Manufacturer\|Product Name'
Manufacturer: Alibaba Cloud
Product Name: Alibaba Cloud ECS
[root@ninedata ~]#
[root@ninedata ~]# yum install wget -y
[root@ninedata ~]# wget https://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repo -O /etc/yum.repos.d/docker-ce.repo
[root@ninedata ~]# dnf install docker-ce -y
[root@ninedata ~]# systemctl enable docker
[root@ninedata ~]# systemctl start docker
[root@ninedata ~]# systemctl status docker
5、安装NineData
dnf install -y iptables iptables-nft
modprobe ip_tables
modprobe iptable_nat
modprobe iptable_filter
modprobe nf_nat
modprobe br_netfilter
# 调整当前会话的文件限制
ulimit -n 65535
# 调整系统全局文件限制
echo 65535 > /proc/sys/fs/file-max
sysctl -p
cat <<EOF >/etc/modules-load.d/ninedata.conf
overlay
br_netfilter
EOF
cat <<EOF >/etc/sysctl.d/99-ninedata.conf
net.bridge.bridge-nf-call-iptables = 1
net.ipv4.ip_forward = 1
EOF
sysctl --system
docker run -d \
--name ninedata \
--privileged \
--cgroupns=host \
-v /sys/fs/cgroup:/sys/fs/cgroup:rw \
-v /opt/ninedata:/u01 \
-p 9999:9999 \
swr.cn-east-3.myhuaweicloud.com/ninedata/ninedata:latest
参考：
https://www.ninedata.cloud/docker
-------------------------------------------------------------------------------------------
参数说明
-p 9999:9999：NineData 通过 9999 端口提供服务，此参数将容器的 9999 端口（冒号后）映射到服务器（冒号前），确保客户端浏览器可以直接访问到 NineData 服务。
--privileged：赋予容器运行和访问的系统权限。
-v /opt/ninedata:/u01：将服务器的 /opt/ninedata 目录挂载到容器的 /u01 目录，用于存放数据。
--name ninedata：设置容器名称为 ninedata，可自行指定其他容器名称。
-d swr.cn-east-3.myhuaweicloud.com/ninedata/ninedata:latest：NineData 的镜像地址，用于拉取 NineData 的镜像文件。
可能会遇到的问题：
解决方法：
先删除旧容器：
docker rm -f ninedata
再重新运行：
docker run -d \
--name ninedata \
--privileged \
--cgroupns=host \
--ulimit nofile=65535:65535 \
-v /sys/fs/cgroup:/sys/fs/cgroup:rw \
-v /opt/ninedata:/u01 \
-p 9999:9999 \
swr.cn-east-3.myhuaweicloud.com/ninedata/ninedata:latest
补充：
docker run -d \
--name ninedata \
--privileged \
--cgroupns=host \
--ulimit nofile=65535:65535 \
-v /sys/fs/cgroup:/sys/fs/cgroup:rw \
-v /opt/ninedata:/u01 \
-p 9999:9999 \
--dns=223.5.5.5 \
--dns=223.6.6.6 \
swr.cn-east-3.myhuaweicloud.com/ninedata/ninedata:latest
[root@ninedata ~]# docker rm -f ninedata
ninedata
[root@ninedata ~]# docker run -d \
--name ninedata \
--privileged \
--cgroupns=host \
--ulimit nofile=65535:65535 \
-v /sys/fs/cgroup:/sys/fs/cgroup:rw \
-v /opt/ninedata:/u01 \
-p 9999:9999 \
swr.cn-east-3.myhuaweicloud.com/ninedata/ninedata:latest
eb2d5b12a8faf16ba2e5824af53786212f31b4a9868f2822698d7b1e470f4802
[root@ninedata ~]# docker ps
CONTAINER ID IMAGE COMMA ND CREATED STATUS PORTS NAMES
eb2d5b12a8fa swr.cn-east-3.myhuaweicloud.com/ninedata/ninedata:latest "/ent rypoint.sh" About a minute ago Up About a minute 0.0.0.0:9999->9999/tcp, [::]:9999->9999/tcp ninedata
[root@ninedata ~]# docker ps -a
CONTAINER ID IMAGE COMMA ND CREATED STATUS PORTS NAMES
eb2d5b12a8fa swr.cn-east-3.myhuaweicloud.com/ninedata/ninedata:latest "/ent rypoint.sh" About a minute ago Up About a minute 0.0.0.0:9999->9999/tcp, [::]:9999->9999/tcp ninedata
[root@ninedata ~]# netstat -pantul|grep 9999
tcp 0 0 0.0.0.0:9999 0.0.0.0:* LISTEN 8435/docker-proxy
tcp6 0 0 :::9999 :::* LISTEN 8442/docker-proxy
[root@ninedata ~]# dmidecode -s system-manufacturer
Alibaba Cloud
[root@ninedata ~]# dmidecode -s system-product-name
Alibaba Cloud ECS
[root@ninedata ~]#
容器启动完成后，NineData 服务会自动在容器内部署并初始化服务，该过程预计需要 5 ~ 10 分钟。通过 docker logs -f ninedata 命令可以查看初始化进度，等待屏幕中打印出如下提示，即代表 NineData 服务已经顺利启动。
常见错误：https://docs.ninedata.cloud/community_edition/deploy_ninedata/#%E5%B8%B8%E8%A7%81%E9%97%AE%E9%A2%98
如果是阿里云平台，需要配置安全组，放行9999端口：
在浏览器中输入 NineData 的连接地址即可打开 NineData 控制台的登录页，NineData 服务默认端口号为 9999，初始管理员账号与密码均为 admin。首次登录后页面会弹出修改密码窗口，请立即更改管理员密码。
首次登录后：
6、同构数据库迁移
第一步：在ECS服务器安装MySQL
优化Linux系统
sed -i -r 's/SELINUX=[ep].*/SELINUX=disabled/g' /etc/selinux/config
# sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config &> /dev/null
setenforce 0
systemctl stop firewalld &> /dev/null
systemctl disable firewalld &> /dev/null
iptables -F
iptables -t nat -F
iptables -P INPUT ACCEPT
iptables -P FORWARD ACCEPT
vim mysql8_install.sh
#!/bin/bash
if rpm -q libaio &> /dev/null; then
echo "libaio已安装，跳过安装"
else
echo "开始安装依赖"
dnf -y install libaio &> /dev/null
if [ $? -ne 0 ];then
echo "libaio安装失败"
exit 1
fi
fi
echo "进行解压操作"
if ls -l mysql-8.0.43-linux-glibc2.28-x86_64 &> /dev/null; then
echo "已解压，跳过"
else
if [ -f mysql-8.0.43-linux-glibc2.28-x86_64.tar.xz ]; then
tar -xf mysql-8.0.43-linux-glibc2.28-x86_64.tar.xz
ls -l mysql-8.0.43-linux-glibc2.28-x86_64
fi
fi
echo "判断是否安装过MariaDB，进行清理"
rpm -qa | grep mariadb | xargs -r dnf remove -y
if [ -f /etc/my.cnf ]; then
rm -rf /etc/my.cnf
fi
id mysql &> /dev/null
[ $? -ne 0 ] && useradd -r -s /sbin/nologin mysql
rm -rf /export/server
mkdir -p /export/server
cp -r mysql-8.0.43-linux-glibc2.28-x86_64 /export/server/mysql
chown -R mysql:mysql /export/server/mysql
echo "正在进入mysql目录，对其进行初始化操作..."
cd /export/server/mysql
bin/mysqld --initialize --user=mysql --basedir=/export/server/mysql --datadir=/export/server/mysql/data 2>&1 | tee /tmp/mysqld.log | grep password | awk '{print $NF}' > /tmp/mysql_temp_password.txt
bin/mysql_ssl_rsa_setup --datadir=/export/server/mysql/data &> /dev/null
cat >/etc/my.cnf<<EOF
[mysqld]
port=3306
basedir=/export/server/mysql
datadir=/export/server/mysql/data
socket=/tmp/mysql.sock
character_set_server=utf8
collation-server=utf8_unicode_ci
EOF
cat >/etc/systemd/system/mysqld.service<<EOF
[Unit]
Description=MySQL Server
After=network.target
[Service]
User=mysql
Group=mysql
Type=forking
# MySQL 执行命令及路径
ExecStart=/export/server/mysql/bin/mysqld --daemonize --pid-file=/export/server/mysql/data/mysqld.pid
ExecStop=/export/server/mysql/bin/mysqladmin --defaults-file=/export/server/mysql/my.cnf shutdown
# Ensure MySQL has sufficient time to start up
TimeoutSec=600
# PID 文件路径
PIDFile=/export/server/mysql/data/mysqld.pid
# Enable these options to auto-restart the service if it crashes
Restart=on-failure
RestartSec=5
[Install]
WantedBy=multi-user.target
EOF
echo "正在刷新后台服务，然后启动mysqld..."
systemctl daemon-reload
systemctl start mysqld
systemctl enable mysqld
#10.重置mysql管理员密码为MySQL@666
echo "正在重置mysql管理员密码..."
cd /export/server/mysql
temp_password=`cat /tmp/mysql_temp_password.txt`
bin/mysqladmin -uroot password 'MySQL@666' -p"$temp_password"
echo 'export PATH=$PATH:/export/server/mysql/bin' >> /etc/profile
source /etc/profile
echo "MySQL8安装成功，安装路径：/export/server/mysql，数据库初始密码：MySQL@666"
chmod +x mysql8_install.sh
source mysql8_install.sh
[root@jaking ~]# mysql -uroot -pMySQL@666
mysql: [Warning] Using a password on the command line interface can be insecure .
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.43 MySQL Community Server - GPL
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
mysql> ^DBye
[root@jaking ~]# cat /etc/redhat-release
CentOS Stream release 9
[root@jaking ~]# dmidecode -s system-manufacturer
Alibaba Cloud
[root@jaking ~]# dmidecode -s system-product-name
Alibaba Cloud ECS
[root@jaking ~]#
ECS上的MySQL授权
mysql -uroot -p'MySQL@666'
mysql> create user root@'%' identified by 'MySQL@666';
mysql> grant all on *.* to root@'%';
mysql> flush privileges;
ECS上的MySQL创建测试数据
create database db_itheima default charset=utf8;
use db_itheima;
CREATE TABLE tb_user (
user_id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
password VARCHAR(100) NOT NULL,
email VARCHAR(100),
phone VARCHAR(20),
create_time DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE tb_goods (
goods_id INT PRIMARY KEY AUTO_INCREMENT,
goods_name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT DEFAULT 0,
category VARCHAR(50),
create_time DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE tb_order (
order_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
goods_id INT NOT NULL,
quantity INT NOT NULL DEFAULT 1,
total_price DECIMAL(10,2) NOT NULL,
order_time DATETIME DEFAULT CURRENT_TIMESTAMP,
status VARCHAR(20) DEFAULT 'pending',
FOREIGN KEY (user_id) REFERENCES tb_user(user_id),
FOREIGN KEY (goods_id) REFERENCES tb_goods(goods_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO tb_user (username, password, email, phone) VALUES
('alice', 'MySQL@666', 'alice@example.com', '13800000001'),
('bob', 'MySQL@666', 'bob@example.com', '13800000002'),
('charlie', 'MySQL@666', 'charlie@example.com', '13800000003');
INSERT INTO tb_goods (goods_name, price, stock, category) VALUES
('iPhone 15', 6999.00, 20, '手机'),
('MacBook Pro 14', 13999.00, 10, '笔记本'),
('AirPods Pro 2', 1999.00, 30, '耳机'),
('iPad Air', 4599.00, 15, '平板');
INSERT INTO tb_order (user_id, goods_id, quantity, total_price, status) VALUES
(1, 1, 1, 6999.00, 'paid'),
(2, 3, 2, 3998.00, 'shipped'),
(3, 4, 1, 4599.00, 'pending'),
(1, 2, 1, 13999.00, 'paid');
show tables;
select * from tb_user;
select * from tb_goods;
select * from tb_order;
第二步：准备RDS数据库
创建高权限账号
创建数据库与账号
第三步：NineData创建数据复制
任务名称mysql_to_rds
创建MySQL数据源
可能会遇到数据库连接问题：
解决方法：
[root@ninedata mysql]# mysql -uroot -pMySQL@666
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.43 MySQL Community Server - GPL
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'MySQL@666';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql>
创建RDS数据源
可能会遇到网络问题，按照以下方法解决：
[root@ninedata ~]# docker exec -it ninedata /bin/bash
root@27280aed6902:/# cat /etc/resolv.conf
# Generated by Docker Engine.
# This file can be edited; Docker Engine will not make further changes once it
# has been modified.
nameserver 100.100.2.136
nameserver 100.100.2.138
# Based on host file: '/etc/resolv.conf' (legacy)
# Overrides: []
root@27280aed6902:/# ping -c 3 rm-0jl7fk821p7d8d579vo.mysql.rds.aliyuncs.com
^C
root@27280aed6902:/# ping -c 3 223.5.5.5
PING 223.5.5.5 (223.5.5.5): 56 data bytes
^C
--- 223.5.5.5 ping statistics ---
3 packets transmitted, 0 packets received, 100% packet loss
root@27280aed6902:/# ping -c 3 223.5.5.5
PING 223.5.5.5 (223.5.5.5): 56 data bytes
64 bytes from 223.5.5.5: seq=0 ttl=121 time=11.696 ms
64 bytes from 223.5.5.5: seq=1 ttl=121 time=7.958 ms
64 bytes from 223.5.5.5: seq=2 ttl=121 time=7.942 ms
--- 223.5.5.5 ping statistics ---
3 packets transmitted, 3 packets received, 0% packet loss
round-trip min/avg/max = 7.942/9.198/11.696 ms
root@27280aed6902:/# ping -c 3 rm-0jl7fk821p7d8d579vo.mysql.rds.aliyuncs.com
PING rm-0jl7fk821p7d8d579vo.mysql.rds.aliyuncs.com (8.152.81.237): 56 data bytes
64 bytes from 8.152.81.237: seq=0 ttl=98 time=0.259 ms
64 bytes from 8.152.81.237: seq=1 ttl=98 time=0.249 ms
64 bytes from 8.152.81.237: seq=2 ttl=98 time=0.277 ms
--- rm-0jl7fk821p7d8d579vo.mysql.rds.aliyuncs.com ping statistics ---
3 packets transmitted, 3 packets received, 0% packet loss
round-trip min/avg/max = 0.249/0.261/0.277 ms
root@27280aed6902:/#
[root@ninedata mysql]# iptables -t nat -L -n
# Warning: iptables-legacy tables present, use iptables-legacy to see them
Chain PREROUTING (policy ACCEPT)
target prot opt source destination
Chain INPUT (policy ACCEPT)
target prot opt source destination
Chain OUTPUT (policy ACCEPT)
target prot opt source destination
Chain POSTROUTING (policy ACCEPT)
target prot opt source destination
Chain DOCKER (0 references)
target prot opt source destination
DNAT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:9999 to:172.18.0.2:9999
[root@ninedata mysql]# ip addr show docker0
3: docker0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP group default
link/ether 16:cd:c5:c5:48:bc brd ff:ff:ff:ff:ff:ff
inet 172.18.0.1/16 brd 172.18.255.255 scope global docker0
valid_lft forever preferred_lft forever
inet6 fe80::14cd:c5ff:fec5:48bc/64 scope link
valid_lft forever preferred_lft forever
[root@ninedata mysql]# iptables -t nat -A POSTROUTING -s 172.18.0.0/16 ! -o docker0 -j MASQUERADE
[root@ninedata mysql]# iptables -t nat -L -n
# Warning: iptables-legacy tables present, use iptables-legacy to see them
Chain PREROUTING (policy ACCEPT)
target prot opt source destination
Chain INPUT (policy ACCEPT)
target prot opt source destination
Chain OUTPUT (policy ACCEPT)
target prot opt source destination
Chain POSTROUTING (policy ACCEPT)
target prot opt source destination
MASQUERADE all -- 172.18.0.0/16 0.0.0.0/0
Chain DOCKER (0 references)
target prot opt source destination
DNAT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:9999 to:172.18.0.2:9999
[root@ninedata mysql]#
# 删除 MASQUERADE 规则
# iptables -t nat -D POSTROUTING -s 172.18.0.0/16 ! -o docker0 -j MASQUERADE
创建数据复制
注意：如果源数据源和目标数据源的名称一直不可选，那么请刷新Web页面，然后再选择！
自动配置映射
第四步：NineData开启数据一致性对比
运行效果：
验证RDS数据
[root@jaking ~]# mysql -hrm-0jl7fk821p7d8d579vo.mysql.rds.aliyuncs.com -uroot -pMySQL@666
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1339
Server version: 8.0.36 Source distribution
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| __recycle_bin__ |
| db_itheima |
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
| testdb666 |
+--------------------+
8 rows in set (0.03 sec)
mysql> use db_itheima;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------+
| Tables_in_db_itheima |
+----------------------+
| tb_goods |
| tb_order |
| tb_user |
+----------------------+
3 rows in set (0.02 sec)
mysql> select * from tb_goods;
+----------+----------------+----------+-------+-----------+---------------------+
| goods_id | goods_name | price | stock | category | create_time |
+----------+----------------+----------+-------+-----------+---------------------+
| 1 | iPhone 15 | 6999.00 | 20 | 手机 | 2025-11-27 23:13:26 |
| 2 | MacBook Pro 14 | 13999.00 | 10 | 笔记本 | 2025-11-27 23:13:26 |
| 3 | AirPods Pro 2 | 1999.00 | 30 | 耳机 | 2025-11-27 23:13:26 |
| 4 | iPad Air | 4599.00 | 15 | 平板 | 2025-11-27 23:13:26 |
+----------+----------------+----------+-------+-----------+---------------------+
4 rows in set (0.02 sec)
mysql> select * from tb_order;
+----------+---------+----------+----------+-------------+---------------------+---------+
| order_id | user_id | goods_id | quantity | total_price | order_time | status |
+----------+---------+----------+----------+-------------+---------------------+---------+
| 1 | 1 | 1 | 1 | 6999.00 | 2025-11-27 23:13:26 | paid |
| 2 | 2 | 3 | 2 | 3998.00 | 2025-11-27 23:13:26 | shipped |
| 3 | 3 | 4 | 1 | 4599.00 | 2025-11-27 23:13:26 | pending |
| 4 | 1 | 2 | 1 | 13999.00 | 2025-11-27 23:13:26 | paid |
+----------+---------+----------+----------+-------------+---------------------+---------+
4 rows in set (0.02 sec)
mysql> select * from tb_user;
+---------+----------+-----------+---------------------+-------------+---------------------+
| user_id | username | password | email | phone | create_time |
+---------+----------+-----------+---------------------+-------------+---------------------+
| 1 | alice | MySQL@666 | alice@example.com | 13800000001 | 2025-11-27 23:13:26 |
| 2 | bob | MySQL@666 | bob@example.com | 13800000002 | 2025-11-27 23:13:26 |
| 3 | charlie | MySQL@666 | charlie@example.com | 13800000003 | 2025-11-27 23:13:26 |
+---------+----------+-----------+---------------------+-------------+---------------------+
3 rows in set (0.02 sec)
mysql>
7、异构数据库迁移（了解）
支持数据库源如下：
六、数据库项目及项目文档编写
1、技能描述
● 了解 SQL 优化，具有丰富的优化经验，通过创建索引、查询优化、表结构设计、配置优化来提高 SQL 性能。
● 熟悉主流数据库 MySQL，掌握 MySQL 主从复制、MHA 高可用、数据备份、Mycat 读写分离等。
● 掌握缓存中间件 Redis 数据持久化、哨兵模式以及集群模式，能够完成 MongoDB 分片集群的部署。
2、工作职责
工作经历01：
1、负责数据库的日常管理和维护，确保数据库的稳定性和安全性；
2、负责数据库性能分析，优化 SQL 语句、优化索引、解决性能瓶颈;
3、负责设计和维护 MySQL 数据库函数和存储过程；
4、负责数据库的部署、优化、调试以及故障排查；
5、根据业务及项目需求，进行数据分析及数据汇总统计。
工作经历02：
1、负责公司内部系统日常巡检，确保系统运行正常。
2、快速响应并解决系统出现的各种异常问题，包括但不限于故障排查、日志分析、系统恢复等 工作。
3、根据业务需求通过 Shell 编写自动化脚本，部署和升级业务相关的服务。
4、实时接收各大水库各指标数据，监测数据变化。
5、负责 MySQL 数据库的日常管理和维护，确保 MySQL 数据库的稳定性和安全性。
6、运维文档的收集和整理。
工作经历03：
● 负责数据库性能分析，优化 SQL 语句、调整参数、优化索引，解决性能瓶颈。
● 负责设计和维护 Oracle 数据库函数和存储过程。
● 负责 DM8 的部署、优化、调试以及故障排查。
● 根据业务及项目需求，进行数据分析及数据汇总统计。
工作经历04：
● 负责数据库用户和权限管理，加固系统安全。
● 负责数据库系统监控，故障排除和问题解决，及时处理数据库系统故障。
● 负责设计数据库高可用架构主从复制、负载均衡方案，支持高并发场景。
● 负责数据库系统的升级、迁移和扩容等工作，确保数据库系统的可靠性和扩展性。
3、数据库DBA项目
项目名称：XXX省份周边水库监测平台运维
项目时间：2022.06-2023.04
**项目介绍：**公司承接了多个省市重点水库监测系统的建设与维护任务，系统汇集来自各地水库的水
位、水流、水质、水量等实时数据。这些数据通过物联网设备上传至数据中心并写入 MySQL 数
据库，作为支撑水资源调度、水质预警、水电调控的核心数据基础。需要监测各大水库各种数据的
变化，同时，随着设备数量增多与数据激增，数据库面临写入压力大、查询缓慢、备份时间长等挑
战，需进行系统化优化与管理。
个人职责：
1、监测服务器性能，定期分析资源使用情况，针对 CPU、内存、磁盘 I/O 等关键指标进行调优。
2、通过 Shell 编写自动化脚本，部署和升级业务相关的服务。
3、实时接收各大水库各指标数据，监测水库数据变化，特殊时期定时提交数据给相关负责人。
4、负责 MySQL 数据库的日常管理和维护，确保 MySQL 数据库的稳定性和安全性。
项目名称：人寿保险公司基于信创 DM 达梦数据库的实施运维
项目时间：2024.06-2025.06
**项目介绍：**为推动中国人寿保险核心系统国产化替代，项目将原有 MySQL / Oracle 逐步迁移至 达梦 DM 数据库（DM8），涉及保单、客户、理赔、财务等核心业务模块。项目要求数据库具备高可用、高可靠性、高性能与可扩展能力，为后续信创全栈替换提供稳定基础。
个人职责：
● 监控实例状态、空间、备份、日志，处理企业微信告警。
● SQL 优化，优化慢查询，以及锁问题。
● 备份检查验证，监控主从是否同步。
● 数据备份、恢复演练与灾备保障。
● 信创迁移与兼容性支持。