【进阶】迁移mysql数据库（小白详细教程）

上天_去_做颗惺星 EVE_BLUE

已于 2025-03-10 20:36:51 修改

阅读量1.2k

点赞数 26

分类专栏：技能文章标签：数据库 mysql sql

于 2025-03-10 20:28:46 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_45927422/article/details/146162936

版权

技能专栏收录该内容

10 篇文章

订阅专栏

基础数据库迁移

从原数据库导出数据

打开终端或命令提示符，使用mysqldump导出原数据库的数据。

如：导出其中的一个数据库database1的全部数据到database1_dump.sql 文件
mysqldump -uusername1 -ppassword1 -hremote_host1 -P3306 database1 > database1_dump.sql

创建新数据库
通过 MySQL 客户端连接到目标 MySQL 服务器，在目标 MySQL 服务器中创建一个新的数据库database1_new来存放原数据库database1的数据。

CREATE DATABASE database1_new;
或 
CREATE DATABASE IF NOT EXISTS database1_new;

将导出的 SQL 文件database1_dump.sql导入到刚刚创建的新数据库database1_new中。

mysql -uusername1 -ppassword1 -hremote_host1 -P3306 database1_new < database1_dump.sql
// tips：命令中的用户、密码、ip、port根据实际情况填写
1、若干ip跟原数据库一致，这种是同一个mysql服务器之中迁移数据库
2、如果ip是新的ip，也就是不同的mysql服务器之间迁移数据库

常用备份参数介绍

//1、如果你需要只复制数据库的表结构而不包括数据，可以使用 --no-data 参数：
mysqldump -uusername1 -ppassword1 -hremote_host1 -P3306 --no-data database1 > database1_nodata_dump.sql 
//2、如果你只需要导出数据而不包括表结构，可以使用 --no-create-info 参数
//3、如果你想要完整的数据库复制（包括结构和数据），则只需使用最初的命令即可。

--single-transaction 参数：在导出数据时开启一个事务，确保数据的一致性（适用于 InnoDB 存储引擎的表）

备份范围

--all-databases # 备份所有数据库（简化操作，无需指定库名）。
--databases db1 db2 # 备份多个指定数据库（用空格分隔）。
--ignore-table=db.table # 忽略指定表（可多次使用，如--ignore-table=mydb.logs）。

备份内容控制

--no-data
仅备份表结构，不备份数据（适用于迁移表结构）。
--no-create-info
仅备份数据，不备份表结构（需配合已有结构的恢复）。
--skip-triggers
不备份触发器。
--skip-routines
不备份存储过程和函数（默认备份）。
--events
备份事件调度器（需显式启用）。

备份优化与压缩

--single-transaction
InnoDB 专用：开启事务确保一致性快照（避免锁表，需配合--quick）。
--quick
逐行导出数据，减少内存占用（默认启用）。
--compress
压缩客户端与服务器之间的传输数据（网络带宽优化）。
--result-file=file
直接指定输出文件路径（避免重定向符号>的编码问题）。

高级选项

--where="condition"
按条件导出数据（如--where="id>100"）。
--hex-blob
以十六进制格式导出二进制字段（避免乱码）。
--add-drop-database
在CREATE DATABASE前添加DROP DATABASE语句（覆盖式恢复）。
--skip-lock-tables
不锁表（可能导致备份不一致，慎用）。

典型使用场景

# 压缩备份，直接压缩输出
mysqldump -u root -p123456 mydb |gzip> backup.sql.gz
# 解压恢复gzip-d backup.sql.gz
mysql -u root -p123456 mydb < backup.sql

注意事项

权限要求：确保用户有SELECT（数据）、SHOW VIEW（视图）、TRIGGER（触发器）等权限。
锁表问题：

默认情况下，mysqldump会锁表（MyISAM 引擎）。
对 InnoDB 使用–single-transaction避免锁表（需在事务隔离模式下运行）。

大表备份优化：

结合–quick和–single-transaction减少内存占用。
分批次导出（如按时间范围）。

备份验证：恢复前建议检查备份文件完整性（如head/tail查看关键语句）。
实战技巧

定时备份：通过cron设置每日自动备份：bash复制# 每天 2 点备份并压缩02 * * * mysqldump -u root -p123456 --all-databases |gzip> /backups/db_$(date +%F).sql.gz
增量备份：配合binlog实现（需启用 MySQL 二进制日志）。
远程备份：bash复制mysqldump -h remote_host -u root -p123456 mydb |ssh user@local “cat > backup.sql”
掌握这些参数和场景，即可高效完成 MySQL 数据的备份与恢复！

mysqldump 详细说明与原理介绍

mysqldump 概述

mysqldump 是 MySQL 数据库中用于逻辑备份的工具。它通过生成 SQL 语句将数据库的结构和数据导出到一个文件中，适用于规模较小、业务不繁忙的数据库备份需求。其原理主要包括获取一致性快照、备份表结构和数据、处理事务和非事务表等。通过合理使用 mysqldump 的参数，可以实现高效、可靠的数据库备份和恢复操作。mysqldump 支持多种备份方式，包括全库备份、单库备份、单表备份等。

mysqldump 的原理

mysqldump 的工作原理是通过执行一系列 SQL 命令来获取数据库的结构和数据，并将其写入一个 SQL 文件中。以下是其主要步骤和原理：

获取一致性快照
在执行备份时，mysqldump 通过以下命令确保数据的一致性：

FLUSH TABLES WITH READ LOCK：该命令会关闭所有打开的表，并对所有表加全局读锁。它的作用是确保在备份过程中不会有数据修改。
START TRANSACTION WITH CONSISTENT SNAPSHOT：该命令用于开启一个事务一致性快照，确保在备份期间读取的数据是事务开始时的一致性状态。

备份表结构和数据
mysqldump 会逐个表执行 SHOW CREATE TABLE 命令以获取表结构，然后使用 SELECT * FROM 命令获取表的数据。这些数据会以 INSERT 语句的形式写入备份文件。
处理事务和非事务表
对于支持事务的表（如 InnoDB），mysqldump 使用 --single-transaction 参数来确保备份的一致性。对于非事务表（如 MyISAM），mysqldump 会通过全局读锁 (FLUSH TABLES WITH READ LOCK) 来确保数据一致性。
备份存储过程和函数
通过 -R 参数，mysqldump 可以备份数据库中的存储过程和函数。
记录二进制日志信息
使用 --master-data 参数，mysqldump 会在备份文件中记录当前二进制日志的状态信息（如 MASTER_LOG_FILE 和 MASTER_LOG_POS），这对于主从复制中的数据恢复非常有用。

mysqldump 的常用命令

备份全库

mysqldump -uusername -ppassword --all-databases > all_databases.sql

备份单个数据库

mysqldump -uusername -ppassword database_name > database_name.sql

备份单个表

mysqldump -uusername -ppassword database_name table_name > table_name.sql

只备份表结构

mysqldump -uusername -ppassword --no-data database_name > database_structure.sql

只备份数据

mysqldump -uusername -ppassword --no-create-info database_name > database_data.sql

使用事务一致性备份

mysqldump -uusername -ppassword --single-transaction database_name > database_name.sql

mysqldump 的注意事项

非事务表的锁问题：对于 MyISAM 等非事务表，FLUSH TABLES WITH READ LOCK 可能会导致较长时间的锁等待，影响数据库的性能。
数据库大小限制：mysqldump 适用于中小型数据库，对于大型数据库，建议使用物理备份工具如 XtraBackup。
备份文件的安全性：备份文件中可能包含敏感信息，需妥善保管。
恢复时的字符集问题：在备份和恢复时，需确保源数据库和目标数据库的字符集一致，以避免数据乱码问题。