Linux环境下MySQL数据库导入导出完整指南

                    
                                MySQL
                            
                                Linux
                            
                                MySQL
                            
                    发布日期:  
                    2025-02-21
                
                    文章字数:  
                    1.1k
                
                    阅读时长:  
                    4 分
                
一、数据库导出操作1.1 导出指定数据表mysqldump -h [主机地址] -u [用户名] -p[密码] [数据库名] [表名] > /path/to/output_file.sql
参数说明：
-h：数据库服务器地址（本地可省略）
-u：MySQL用户名
-p：密码（注意-p与密码之间无空格）
>：重定向输出到文件
安全操作建议：
# 推荐方式（密码交互式输入）
mysqldump -h 192.168.1.100 -u root -p mydb users > backup_users.sql
1.2 导出整个数据库mysqldump -h [主机地址] -u [用户名] -p[密码] [数据库名] > /path/to/full_dump.sql
高级导出选项：
# 导出并压缩（适用于大数据库）
mysqldump -u root -p mydb | gzip > mydb_backup.sql.gz

# 仅导出表结构
mysqldump -u root -p --no-data mydb > schema_only.sql

# 导出时忽略指定表
mysqldump -u root -p mydb --ignore-table=mydb.logs > exclude_logs.sql
二、数据库导入操作2.1 基本导入命令mysql -h [主机地址] -u [用户名] -p[密码] [数据库名] < /path/to/input_file.sql
典型使用场景：
# 导入完整数据库
mysql -u root -p mydb < full_dump.sql

# 导入指定表数据
mysql -u root -p mydb < users_data.sql
2.2 常见错误处理方案错误示例：
ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
解决方案：
打开SQL文件查找权限相关语句
vim input_file.sql
注释或删除以下类型语句：
/*!50503 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
SET @@GLOBAL.gtid_purged='...';
保存文件后重新导入
预防措施（导出时添加参数）：
mysqldump --set-gtid-purged=OFF -u root -p mydb > clean_dump.sql
三、高级操作技巧3.1 导出时排除触发器/事件# 不导出触发器
mysqldump --skip-triggers -u root -p mydb > no_triggers.sql

# 不导出事件
mysqldump --skip-events -u root -p mydb > no_events.sql
3.2 分块导出大表# 按行数分块导出
mysqldump -u root -p --where="1 LIMIT 0,1000000" mydb big_table > part1.sql
mysqldump -u root -p --where="1 LIMIT 1000000,1000000" mydb big_table > part2.sql
3.3 导入进度显示# 使用pv工具监控进度
pv input_file.sql | mysql -u root -p mydb

# 安装pv：yum install pv 或 apt-get install pv
四、生产环境注意事项4.1 权限管理规范

操作类型
所需权限

完整导出
SELECT, LOCK TABLES

部分表导出
SELECT 权限

数据库导入
CREATE, INSERT, ALTER 等

4.2 版本兼容性处理# 导出时指定兼容模式
mysqldump --compatible=ansi -u root -p mydb > compatible.sql

# 跨版本迁移建议流程：
源服务器导出 -> 转换字符集 -> 目标服务器导入验证
4.3 日志记录与验证# 记录导入导出操作日志
{
    echo "[$(date)] 开始导出数据库"
    mysqldump -u root -p mydb > backup.sql
    echo "[$(date)] 导出完成，文件大小: $(du -h backup.sql)"
} >> /var/log/db_ops.log 2>&1

# 验证备份完整性
mysqlcheck -u root -p --check-upgrade mydb
五、典型问题解决方案5.1 中文乱码问题导出时指定字符集：
mysqldump -u root -p --default-character-set=utf8mb4 mydb > utf8_backup.sql
导入时设置字符集：
mysql -u root -p --default-character-set=utf8mb4 mydb < backup.sql
5.2 存储引擎不兼容转换存储引擎：
sed -i 's/ENGINE=MyISAM/ENGINE=InnoDB/g' backup.sql
5.3 外键约束冲突导入时临时禁用外键检查：
/* 在SQL文件开头添加 */
SET FOREIGN_KEY_CHECKS=0;

/* 在文件结尾恢复 */
SET FOREIGN_KEY_CHECKS=1;
六、自动化脚本示例6.1 自动备份脚本#!/bin/bash
DATE=$(date +%Y%m%d)
BACKUP_DIR="/data/backups"
DB_NAME="mydb"
USER="root"

mysqldump -u $USER -p$PASSWORD --single-transaction $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# 保留最近7天备份
find $BACKUP_DIR -name "*.sql.gz" -type f -mtime +7 -delete
6.2 自动恢复脚本#!/bin/bash
INPUT_FILE=$1
DB_NAME="mydb"
USER="root"

# 预处理SQL文件
sed -i '/^SET @@/d' $INPUT_FILE
sed -i '/^CREATE ALGORITHM=/d' $INPUT_FILE

mysql -u $USER -p$PASSWORD $DB_NAME < $INPUT_FILE
七、最佳实践总结安全规范
使用mysql_config_editor存储登录凭证
设置备份文件权限为600
定期测试备份恢复流程
性能优化
大数据库使用--single-transaction参数
启用--quick模式加速导出
并行导出多个表：mydumper工具
监控指标
# 监控备份文件大小变化
watch -n 3600 'du -h /backups | tail -n1'

# 检查最后一次备份时间
ls -lht /backups | head -n2
通过掌握这些技巧，我们可以高效安全地管理MySQL数据库的导入导出操作。建议至少每月进行一次完整的备份恢复演练，确保在紧急情况下能快速恢复数据。

                        文章作者:
                    
                    Crazy Boy
                
                        文章链接:
                    
                    https://crazy-boy.com/posts/b820.html
                
                        版权声明:
                    
                    本博客所有文章除特別声明外，均采用
                    CC BY 4.0
                    许可协议。转载请注明来源
                    Crazy Boy
                    !
                
                                    MySQL
                                
                                    Linux
                                
赏感谢赞赏哦~支付宝
微 信

        评 论
    
                 上一篇

                          基于Redis的实时排行榜系统设计与实现
                        
                            一、核心设计原理1.1 架构设计采用Redis ZSET+数据库双存储方案：

Redis ZSET：存储组合分数实现实时排序（时间复杂度O(logN)）
数据库：持久化原始数据
分布式锁：保证数据一致性

1.2 同分排序算法组合分数公式
                        
                            2025-02-21
                        
                                    系统设计
                                
                        Redis
                    
                        PHP
                    
                        排行榜
                    
                下一篇 
            
                          redis中，如何批量删除匹配特定模式的键
                        
                            在 redis-cli 中，我们可以使用批量删除命令来删除匹配特定模式的键。以下是步骤：

使用 KEYS 命令找到要删除的键。
使用 xargs 和 DEL 命令批量删除这些键。

以下是完整的命令：
123redis-cli --sca
                        
                                2025-01-26
                            
                                    Redis
                                
                        Redis
                    
                                MySQL
                            
                                Linux
                            
                                MySQL
                            
                    发布日期:  
                    2025-02-21
                
                    文章字数:  
                    1.1k
                
                    阅读时长:  
                    4 分
                
一、数据库导出操作1.1 导出指定数据表mysqldump -h [主机地址] -u [用户名] -p[密码] [数据库名] [表名] > /path/to/output_file.sql
参数说明：
-h：数据库服务器地址（本地可省略）
-u：MySQL用户名
-p：密码（注意-p与密码之间无空格）
>：重定向输出到文件
安全操作建议：
# 推荐方式（密码交互式输入）
mysqldump -h 192.168.1.100 -u root -p mydb users > backup_users.sql
1.2 导出整个数据库mysqldump -h [主机地址] -u [用户名] -p[密码] [数据库名] > /path/to/full_dump.sql
高级导出选项：
# 导出并压缩（适用于大数据库）
mysqldump -u root -p mydb | gzip > mydb_backup.sql.gz

# 仅导出表结构
mysqldump -u root -p --no-data mydb > schema_only.sql

# 导出时忽略指定表
mysqldump -u root -p mydb --ignore-table=mydb.logs > exclude_logs.sql
二、数据库导入操作2.1 基本导入命令mysql -h [主机地址] -u [用户名] -p[密码] [数据库名] < /path/to/input_file.sql
典型使用场景：
# 导入完整数据库
mysql -u root -p mydb < full_dump.sql

# 导入指定表数据
mysql -u root -p mydb < users_data.sql
2.2 常见错误处理方案错误示例：
ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
解决方案：
打开SQL文件查找权限相关语句
vim input_file.sql
注释或删除以下类型语句：
/*!50503 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
SET @@GLOBAL.gtid_purged='...';
保存文件后重新导入
预防措施（导出时添加参数）：
mysqldump --set-gtid-purged=OFF -u root -p mydb > clean_dump.sql
三、高级操作技巧3.1 导出时排除触发器/事件# 不导出触发器
mysqldump --skip-triggers -u root -p mydb > no_triggers.sql

# 不导出事件
mysqldump --skip-events -u root -p mydb > no_events.sql
3.2 分块导出大表# 按行数分块导出
mysqldump -u root -p --where="1 LIMIT 0,1000000" mydb big_table > part1.sql
mysqldump -u root -p --where="1 LIMIT 1000000,1000000" mydb big_table > part2.sql
3.3 导入进度显示# 使用pv工具监控进度
pv input_file.sql | mysql -u root -p mydb

# 安装pv：yum install pv 或 apt-get install pv
四、生产环境注意事项4.1 权限管理规范

操作类型
所需权限

完整导出
SELECT, LOCK TABLES

部分表导出
SELECT 权限

数据库导入
CREATE, INSERT, ALTER 等

4.2 版本兼容性处理# 导出时指定兼容模式
mysqldump --compatible=ansi -u root -p mydb > compatible.sql

# 跨版本迁移建议流程：
源服务器导出 -> 转换字符集 -> 目标服务器导入验证
4.3 日志记录与验证# 记录导入导出操作日志
{
    echo "[$(date)] 开始导出数据库"
    mysqldump -u root -p mydb > backup.sql
    echo "[$(date)] 导出完成，文件大小: $(du -h backup.sql)"
} >> /var/log/db_ops.log 2>&1

# 验证备份完整性
mysqlcheck -u root -p --check-upgrade mydb
五、典型问题解决方案5.1 中文乱码问题导出时指定字符集：
mysqldump -u root -p --default-character-set=utf8mb4 mydb > utf8_backup.sql
导入时设置字符集：
mysql -u root -p --default-character-set=utf8mb4 mydb < backup.sql
5.2 存储引擎不兼容转换存储引擎：
sed -i 's/ENGINE=MyISAM/ENGINE=InnoDB/g' backup.sql
5.3 外键约束冲突导入时临时禁用外键检查：
/* 在SQL文件开头添加 */
SET FOREIGN_KEY_CHECKS=0;

/* 在文件结尾恢复 */
SET FOREIGN_KEY_CHECKS=1;
六、自动化脚本示例6.1 自动备份脚本#!/bin/bash
DATE=$(date +%Y%m%d)
BACKUP_DIR="/data/backups"
DB_NAME="mydb"
USER="root"

mysqldump -u $USER -p$PASSWORD --single-transaction $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# 保留最近7天备份
find $BACKUP_DIR -name "*.sql.gz" -type f -mtime +7 -delete
6.2 自动恢复脚本#!/bin/bash
INPUT_FILE=$1
DB_NAME="mydb"
USER="root"

# 预处理SQL文件
sed -i '/^SET @@/d' $INPUT_FILE
sed -i '/^CREATE ALGORITHM=/d' $INPUT_FILE

mysql -u $USER -p$PASSWORD $DB_NAME < $INPUT_FILE
七、最佳实践总结安全规范
使用mysql_config_editor存储登录凭证
设置备份文件权限为600
定期测试备份恢复流程
性能优化
大数据库使用--single-transaction参数
启用--quick模式加速导出
并行导出多个表：mydumper工具
监控指标
# 监控备份文件大小变化
watch -n 3600 'du -h /backups | tail -n1'

# 检查最后一次备份时间
ls -lht /backups | head -n2
通过掌握这些技巧，我们可以高效安全地管理MySQL数据库的导入导出操作。建议至少每月进行一次完整的备份恢复演练，确保在紧急情况下能快速恢复数据。

                        文章作者:
                    
                    Crazy Boy
                
                        文章链接:
                    
                    https://crazy-boy.com/posts/b820.html
                
                        版权声明:
                    
                    本博客所有文章除特別声明外，均采用
                    CC BY 4.0
                    许可协议。转载请注明来源
                    Crazy Boy
                    !
                
                                    MySQL
                                
                                    Linux
                                
赏感谢赞赏哦~支付宝
微 信

        评 论
    
                 上一篇

                          基于Redis的实时排行榜系统设计与实现
                        
                            一、核心设计原理1.1 架构设计采用Redis ZSET+数据库双存储方案：

Redis ZSET：存储组合分数实现实时排序（时间复杂度O(logN)）
数据库：持久化原始数据
分布式锁：保证数据一致性

1.2 同分排序算法组合分数公式
                        
                            2025-02-21
                        
                                    系统设计
                                
                        Redis
                    
                        PHP
                    
                        排行榜
                    
                下一篇 
            
                          redis中，如何批量删除匹配特定模式的键
                        
                            在 redis-cli 中，我们可以使用批量删除命令来删除匹配特定模式的键。以下是步骤：

使用 KEYS 命令找到要删除的键。
使用 xargs 和 DEL 命令批量删除这些键。

以下是完整的命令：
123redis-cli --sca
                        
                                2025-01-26
                            
                                    Redis
                                
                        Redis
                    
                  目录
            
                  目录
            
        hexo