linux 迁移mysql数据

Leo韦
129 阅读7分钟

一、适用场景

本教程适用于 Linux 系统 MySQL/MariaDB 的数据库迁移场景，包括：

  1. 本地数据库备份与导入（同服务器数据迁移）；
  1. 跨服务器迁移（源服务器备份→目标服务器导入）；
  1. 数据库迁移后权限配置、数据一致性验证。

二、前提准备

1. 环境确认

  • 源服务器：已安装 MySQL/MariaDB，且 mysqldump 工具可用（默认随数据库安装）；
  • 目标服务器：已安装 MySQL/MariaDB（版本建议与源服务器一致，避免兼容性问题）；
  • 权限要求：操作需具备数据库管理员权限（如 root 账号），文件操作需有读写权限。

2. 工具说明

  • mysqldump：MySQL 官方备份工具，用于将数据库导出为 SQL 脚本文件；
  • mysql：MySQL 客户端工具，用于执行 SQL 脚本导入数据；
  • 跨服务器迁移需用到 scp/rsync（文件传输）或直接远程备份。

三、核心迁移步骤（本地 / 同服务器）

1. 数据库备份（导出 SQL 文件）

# 1. 进入备份目录（建议选择空间充足的目录，避免/var/lib/mysql空间不足）
cd /tmp/backup  # 自定义备份目录，需提前创建：mkdir -p /tmp/backup
# 2. 执行备份命令（导出 alarm_db 数据库）
mysqldump -uroot -p --default-character-set=utf8 --single-transaction --quick --lock-tables=false alarm_db > alarm_db.sql
  • 核心参数优化说明（避免备份失败或数据不一致）：
    • -uroot：指定数据库用户名（root 为管理员账号）；
    • -p：提示输入密码（也可直接在 -p 后加密码，如 -p123456，不推荐生产环境使用）；
    • --default-character-set=utf8：指定字符集为 UTF-8，避免中文乱码；
    • --single-transaction：创建一致性快照，避免备份时锁表（InnoDB 引擎适用）；
    • --quick：快速导出，避免占用过多内存；
    • --lock-tables=false：不锁表（适合生产环境持续运行的数据库）；
    • alarm_db：待备份的数据库名；

    • alarm_db.sql：导出的 SQL 文件名（可自定义路径和名称）。

2. 目标数据库创建（若不存在）

# 1. 登录 MySQL/MariaDB 客户端
sudo mysql -uroot -p  # 或 sudo mariadb -uroot -p（MariaDB 专用）
# 2. 执行创建数据库命令（指定字符集为 UTF-8，与备份一致）
CREATE DATABASE IF NOT EXISTS alarm_db DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
# 3. 退出客户端
exit;
  • 说明：IF NOT EXISTS 表示若数据库已存在则不报错，避免重复创建失败。

3. 导入数据到目标数据库

# 执行导入命令（将 SQL 文件导入 alarm_db 数据库）
mysql -uroot -p --default-character-set=utf8 alarm_db < /tmp/backup/alarm_db.sql
  • 参数说明：
    • < /tmp/backup/alarm_db.sql：指定导入的 SQL 文件路径（需与备份文件路径一致）；
    • 导入过程中若提示密码，输入数据库 root 密码即可；
    • 大数据库导入耗时较长，请勿中断终端连接（可使用 nohup 后台执行：nohup mysql -uroot -p... &）。

四、扩展场景：跨服务器迁移（源服务器→目标服务器）

当需要将数据库从 A 服务器迁移到 B 服务器时，分「先备份再传输」和「直接远程备份」两种方式：

方法 1：先备份到本地，再传输到目标服务器

# 1. 源服务器执行备份（步骤同三、1）
mysqldump -uroot -p --default-character-set=utf8 alarm_db > alarm_db.sql
# 2. 通过 scp 传输 SQL 文件到目标服务器（需知道目标服务器 IP 和账号）
scp /tmp/backup/alarm_db.sql root@目标服务器IP:/tmp/backup/
# 3. 目标服务器执行导入（步骤同三、2+三、3）
# 先在目标服务器创建 /tmp/backup 目录：mkdir -p /tmp/backup
# 再执行导入命令：mysql -uroot -p --default-character-set=utf8 alarm_db < /tmp/backup/alarm_db.sql

方法 2：直接远程备份（无需在源服务器留存 SQL 文件）

# 在目标服务器直接执行远程备份并导入（一步到位）
mysqldump -uroot -p -h 源服务器IP --default-character-set=utf8 alarm_db | mysql -uroot -p -h 127.0.0.1 alarm_db
  • 说明：
    • -h 源服务器IP：指定源数据库所在服务器的 IP；
    • 管道符 |：将源服务器的备份数据直接传输到目标服务器的 MySQL 客户端，无需中间文件；
    • 需确保源服务器的 MySQL 允许远程连接（已开放 3306 端口，且 root 账号支持远程访问）。

五、迁移后验证与权限配置

1. 验证数据导入成功

# 登录目标数据库
mysql -uroot -p
# 切换到 alarm_db 数据库
use alarm_db;
# 验证核心表数据（示例：查看表列表和前10条数据）
show tables;
select * from 核心表名 limit 10;  # 替换为实际表名，确认数据存在
# 退出客户端
exit;

2. 配置数据库用户权限（若需）

若迁移后需要给应用程序分配数据库访问权限，执行以下命令：

# 登录 MySQL
mysql -uroot -p
# 创建用户并授权（示例：给 server 用户授权 alarm_db 全部权限）
CREATE USER IF NOT EXISTS 'server'@'%' IDENTIFIED BY 'test123';  # 用户名/密码可自定义
GRANT ALL PRIVILEGES ON alarm_db.* TO 'server'@'%';  # 授予所有权限
FLUSH PRIVILEGES;  # 刷新权限生效
# 退出客户端
exit;
  • 说明：'server'@'%' 中的 % 表示允许从任意 IP 访问，若仅允许指定 IP，替换为 'server'@'192.168.0.%'（网段）或具体 IP。

六、进阶优化：备份文件压缩与定时备份

1. 备份时压缩 SQL 文件（节省空间）

# 备份并压缩（使用 gzip 压缩，生成 .sql.gz 文件）
mysqldump -uroot -p --default-character-set=utf8 alarm_db | gzip > alarm_db.sql.gz
# 导入时解压并导入（无需单独解压）
gzip -dc alarm_db.sql.gz | mysql -uroot -p --default-character-set=utf8 alarm_db
  • 说明：gzip -dc 表示解压并输出到标准输出，适合大数据库备份（压缩后体积可减少 70% 以上）。

2. 定时备份脚本（自动化运维）

创建定时任务，定期备份数据库（避免迁移后数据丢失）：

# 1. 创建备份脚本（/usr/local/mysql/backup_mysql.sh）
vim /usr/local/mysql/backup_mysql.sh
# 2. 脚本内容（示例：每天凌晨2点备份 alarm_db，保留7天备份）
#!/bin/bash
BACKUP_DIR=/tmp/mysql_backup
DB_NAME=alarm_db
DB_USER=root
DB_PASS=你的数据库密码  # 若担心密码泄露，可配置 my.cnf 免密登录
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7
# 创建备份目录
mkdir -p $BACKUP_DIR
# 执行备份并压缩
mysqldump -u$DB_USER -p$DB_PASS --default-character-set=utf8 --single-transaction $DB_NAME | gzip > $BACKUP_DIR/$DB_NAME_$DATE.sql.gz
# 删除7天前的旧备份
find $BACKUP_DIR -name "$DB_NAME*.sql.gz" -mtime +$RETENTION_DAYS -delete
# 3. 给脚本添加执行权限
chmod +x /usr/local/mysql/backup_mysql.sh
# 4. 添加到 crontab 定时任务（每天凌晨2点执行）
crontab -e
# 添加以下内容：
0 2 * * * /usr/local/mysql/backup_mysql.sh

七、常见问题排查

1. 备份时提示「Access denied」（权限不足）

  • 原因：root 账号权限不足，或 /tmp/backup 目录无写入权限；
  • 解决：
    • 确认 root 账号密码正确，且有数据库备份权限；
    • 给备份目录授权：chmod 777 -R /tmp/backup。

2. 导入时提示「Unknown database 'alarm_db'」

  • 原因：目标服务器未创建 alarm_db 数据库；
  • 解决：先执行 CREATE DATABASE alarm_db; 创建数据库，再重新导入。

3. 导入后中文乱码

  • 原因：备份或导入时字符集不一致；
  • 解决：确保备份和导入命令均添加 --default-character-set=utf8，且数据库字符集为 UTF-8（show variables like 'character_set_database'; 验证）。

4. 跨服务器迁移时连接超时

  • 原因：源服务器未开放 3306 端口，或 MySQL 不允许远程访问；
  • 解决：
    • 开放端口：sudo firewall-cmd --permanent --add-port=3306/tcp && sudo firewall-cmd --reload（CentOS）；
    • 授权远程访问：mysql -uroot -p → GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY '密码'; → FLUSH PRIVILEGES;。

八、核心注意事项

  1. 迁移前停止写入：若为生产环境迁移，建议先停止源数据库的写入操作（如暂停应用服务），避免备份时数据不一致；
  1. 备份文件校验：备份完成后可通过 ls -lh alarm_db.sql 查看文件大小，确认备份成功（空文件可能是命令执行失败）；
  1. 版本兼容性：源数据库和目标数据库版本差异不宜过大（如 MySQL 5.7 → MySQL 8.0 需注意语法兼容），建议提前测试；
  1. 敏感数据保护：备份文件包含数据库所有数据，需设置权限（chmod 600 alarm_db.sql），避免泄露。
avatar
文章
阅读
粉丝