Ubuntu环境下MySQL数据迁移与整合指南

一、数据迁移：从源数据库到目标数据库

数据迁移是将数据从一个MySQL实例（源）转移到另一个实例（目标）的过程，适用于系统升级、环境迁移等场景。以下是常用方法：

1. 使用mysqldump+mysql命令行（通用方法）

适用场景：小到中型数据库、跨版本迁移、需要备份文件保留。
步骤：

• 导出源数据库：在源服务器上执行mysqldump命令，生成包含结构和数据的SQL文件。

  mysqldump -u [源用户名] -p[源密码] [源数据库名] > backup.sql
  

  （注：-p后直接跟密码会有安全风险，建议仅输入-p，然后交互式输入密码。）
• 传输备份文件：使用scp将backup.sql复制到目标服务器。

  scp backup.sql [目标用户名]@[目标服务器IP]:/path/to/destination/
  

• 导入到目标数据库：在目标服务器上创建新数据库，然后导入SQL文件。

  mysql -u [目标用户名] -p[目标密码] -e "CREATE DATABASE [目标数据库名];"
  mysql -u [目标用户名] -p[目标密码] [目标数据库名] < /path/to/destination/backup.sql
  

2. 使用mysqlpump（MySQL 5.7+并行工具）

适用场景：大型数据库、需要加快导出速度。
mysqlpump是mysqldump的并行版本，支持多线程导出，显著缩短大型数据库的迁移时间。
步骤：

• 导出数据：

  mysqlpump -u [源用户名] -p[源密码] --databases [源数据库名] > backup.sql
  

• 传输与导入：与mysqldump步骤一致（同上）。

3. 使用xtrabackup（热备份，适用于生产环境）

适用场景：大型数据库、需要零停机迁移、InnoDB引擎。
xtrabackup是Percona提供的热备份工具，可在不锁表的情况下备份数据，适合生产环境。
步骤：

• 安装工具：

  sudo apt-get update && sudo apt-get install percona-xtrabackup-24
  

• 备份数据：在源服务器上执行热备份，指定目标目录。

  xtrabackup --backup --user=[源用户名] --password=[源密码] --target-dir=/path/to/backup
  

• 准备备份：在源服务器上执行prepare命令，使备份可恢复。

  xtrabackup --prepare --target-dir=/path/to/backup
  

• 传输备份：将备份目录复制到目标服务器。

  scp -r /path/to/backup [目标用户名]@[目标服务器IP]:/path/to/destination/
  

• 恢复数据：在目标服务器上停止MySQL服务，复制备份文件到数据目录，然后启动服务。

  sudo systemctl stop mysql
  sudo rm -rf /var/lib/mysql/*  # 清空现有数据（谨慎操作）
  sudo cp -R /path/to/destination/backup/* /var/lib/mysql/
  sudo chown -R mysql:mysql /var/lib/mysql  # 修复权限
  sudo systemctl start mysql
  

4. 使用图形化工具（适合新手）

适用场景：不熟悉命令行、需要可视化操作。
常用工具：

• Navicat：支持MySQL数据同步、迁移，提供图形化界面和向导。
• HeidiSQL：轻量级工具，支持Windows平台，可连接Ubuntu上的MySQL服务器。
• MySQL Workbench：MySQL官方工具，支持数据迁移向导（Database Migration Wizard）。

二、数据整合：合并多个数据源

数据整合是将多个MySQL数据库或多个数据源（如MySQL与MongoDB）的数据合并到一个统一系统中的过程，适用于数据仓库、企业中台等场景。

1. 使用MySQL内置复制（Real-time Sync）

适用场景：实时同步两个MySQL数据库、主从架构。
步骤：

• 配置主服务器（Master）：
  编辑/etc/mysql/my.cnf，添加以下配置：

  [mysqld]
  server-id = 1
  log_bin = /var/log/mysql/mysql-bin.log
  binlog_do_db = [需要同步的数据库名]
  

  重启MySQL服务：sudo systemctl restart mysql。
  创建复制用户：

  CREATE USER 'replicator'@'%' IDENTIFIED BY 'password';
  GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
  FLUSH PRIVILEGES;
  

  获取二进制日志位置：

  FLUSH TABLES WITH READ LOCK;
  SHOW MASTER STATUS;  -- 记录File和Position
  UNLOCK TABLES;
  

• 配置从服务器（Slave）：
  编辑/etc/mysql/my.cnf，添加以下配置：

  [mysqld]
  server-id = 2
  relay_log = /var/log/mysql/mysql-relay-bin.log
  log_bin = /var/log/mysql/mysql-bin.log
  replicate_do_db = [需要同步的数据库名]
  

  重启MySQL服务：sudo systemctl restart mysql。
  设置主服务器信息：

  CHANGE MASTER TO
    MASTER_HOST='主服务器IP',
    MASTER_USER='replicator',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',  -- 主服务器记录的File
    MASTER_LOG_POS=123;                  -- 主服务器记录的Position
  START SLAVE;
  

  检查同步状态：SHOW SLAVE STATUS\G（确保Slave_IO_Running和Slave_SQL_Running为Yes）。

2. 使用第三方数据同步工具（高性能、易管理）

适用场景：大规模数据同步、跨数据源整合、企业级需求。
常用工具：

• FineDataLink：支持MySQL与其他数据源（如MongoDB、Redis、数据仓库）的双向同步，提供可视化流程设计、数据转换（清洗、脱敏）、实时/定时同步等功能，适合企业数据中台建设。
• Apache NiFi：开源数据流自动化工具，支持MySQL与其他数据源的集成，通过拖拽界面配置数据同步流程。
• Talend：企业级ETL工具，支持MySQL数据抽取、转换、加载（ETL），可与Hadoop、Spark等大数据平台集成。

3. 使用ETL工具（Extract-Transform-Load）

适用场景：数据仓库构建、复杂数据转换。
步骤：

• Extract：从源数据库（如MySQL）抽取数据，可使用mysqldump、SELECT INTO OUTFILE或ETL工具的抽取组件。
• Transform：对数据进行清洗（去重、填充缺失值）、转换（格式转换、字段映射）、脱敏（掩码、加密），例如将MySQL中的日期格式转换为数据仓库要求的格式。
• Load：将处理后的数据加载到目标数据库（如数据仓库、另一个MySQL实例），可使用LOAD DATA INFILE或ETL工具的加载组件。

三、迁移与整合的注意事项

1. 备份优先：迁移前务必对源数据库进行完整备份（如使用mysqldump或xtrabackup），防止数据丢失。
2. 版本兼容性：确保源和目标MySQL版本兼容（如MySQL 8.0导出的数据可导入到8.0及以上版本，避免跨大版本迁移）。
3. 字符集一致：源和目标数据库的字符集（如UTF-8）需一致，避免乱码问题。
4. 停机时间规划：对于大型数据库，选择低峰时段迁移，减少对业务的影响；使用xtrabackup可实现零停机迁移。
5. 数据验证：迁移后检查数据完整性（如表数量、行数、关键字段值），确保数据一致。
6. 权限设置：迁移后检查目标数据库的用户权限，确保应用能正常访问。