CentOS 从远程服务器备份 MySQL 数据库到本地

下面是一个完整指南，介绍如何在 CentOS 上从远程服务器备份 MySQL 数据库并恢复到当前服务器上。

1. 准备工作

首先确保本地和远程服务器都安装了必要的工具：

# 在本地服务器上安装 MySQL 客户端和工具
sudo yum install -y mysql mysql-server

# 确认 mysqldump 工具是否可用
which mysqldump

2. 从远程服务器备份数据库

方法一：直接使用 mysqldump 从远程备份

mysqldump -h REMOTE_HOST -u REMOTE_USER -p REMOTE_DB_NAME --compress > backup.sql

系统会提示你输入远程 MySQL 用户的密码。

方法二：使用 SSH 备份远程数据库（推荐）

这种方法更安全，因为它通过 SSH 隧道加密传输数据：

ssh user@remote_server "mysqldump -u REMOTE_USER -p'REMOTE_PASSWORD' REMOTE_DB_NAME" > backup.sql

如果需要备份所有数据库：

ssh user@remote_server "mysqldump -u REMOTE_USER -p'REMOTE_PASSWORD' --all-databases" > all_dbs_backup.sql

3. 在当前服务器上恢复数据库

首先确保本地 MySQL 服务已启动：

sudo systemctl start mysqld
sudo systemctl status mysqld

创建目标数据库（如果需要）

mysql -u LOCAL_USER -p -e "CREATE DATABASE IF NOT EXISTS LOCAL_DB_NAME CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

导入备份数据

mysql -u LOCAL_USER -p LOCAL_DB_NAME < backup.sql

4. 创建一键备份脚本

为了自动化这个过程，可以创建一个 Shell 脚本：

#!/bin/bash

# 配置变量
REMOTE_HOST="remote_server_ip"
REMOTE_SSH_USER="ssh_username"
REMOTE_MYSQL_USER="remote_mysql_username"
REMOTE_MYSQL_PASS="remote_mysql_password"
REMOTE_DB_NAME="remote_database_name"

LOCAL_MYSQL_USER="local_mysql_username"
LOCAL_MYSQL_PASS="local_mysql_password"
LOCAL_DB_NAME="local_database_name"

# 备份目录
BACKUP_DIR="/path/to/backup/directory"
mkdir -p $BACKUP_DIR

# 生成带时间戳的备份文件名
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
BACKUP_FILE="$BACKUP_DIR/${REMOTE_DB_NAME}_${TIMESTAMP}.sql"

# 从远程服务器备份数据库
echo "开始从 $REMOTE_HOST 备份数据库 $REMOTE_DB_NAME..."
ssh $REMOTE_SSH_USER@$REMOTE_HOST "mysqldump -u $REMOTE_MYSQL_USER -p'$REMOTE_MYSQL_PASS' $REMOTE_DB_NAME" > $BACKUP_FILE

# 检查备份是否成功
if [ $? -eq 0 ] && [ -s $BACKUP_FILE ]; then
    echo "数据库备份成功: $BACKUP_FILE"
    
    # 确保目标数据库存在
    echo "创建本地数据库 $LOCAL_DB_NAME (如果不存在)..."
    mysql -u $LOCAL_MYSQL_USER -p"$LOCAL_MYSQL_PASS" -e "CREATE DATABASE IF NOT EXISTS $LOCAL_DB_NAME CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
    
    # 导入备份到本地数据库
    echo "将备份导入到本地数据库 $LOCAL_DB_NAME..."
    mysql -u $LOCAL_MYSQL_USER -p"$LOCAL_MYSQL_PASS" $LOCAL_DB_NAME < $BACKUP_FILE
    
    if [ $? -eq 0 ]; then
        echo "数据导入成功!"
    else
        echo "数据导入失败!"
        exit 1
    fi
else
    echo "数据库备份失败!"
    exit 1
fi

echo "备份和恢复过程完成!"

将以上内容保存为 mysql_backup_restore.sh，然后设置执行权限：

chmod +x mysql_backup_restore.sh

5. 设置定时备份（可选）

使用 crontab 设置定期自动备份：

crontab -e

添加如下内容（每天凌晨 2 点执行备份）：

0 2 * * * /path/to/mysql_backup_restore.sh > /path/to/backup.log 2>&1

安全建议

避免在脚本中明文保存密码。考虑使用 ~/.my.cnf 文件存储凭据：
```
[client]
user=your_username
password=your_password
```
设置适当权限：
```
chmod 600 ~/.my.cnf
```
使用 SSH 密钥认证 而不是密码登录远程服务器
为备份用户设置只读权限，只给予必要的数据库访问权限
定期测试备份，确保可以成功恢复
对备份文件进行加密，特别是包含敏感数据时

故障排除

连接错误：确保防火墙允许 MySQL 端口 (3306) 的连接

sudo firewall-cmd --permanent --add-port=3306/tcp
sudo firewall-cmd --reload

权限问题：确保 MySQL 用户有足够的权限

GRANT SELECT, LOCK TABLES ON database_name.* TO 'backup_user'@'%';
FLUSH PRIVILEGES;

内存不足：对于大型数据库，可能需要使用分段备份或增加服务器的交换空间
备份速度慢：添加 --compress 选项以减少网络传输数据量

这份指南应该能满足在 CentOS 系统上从远程服务器备份 MySQL 数据库到本地的需求。根据你的具体环境和数据库大小，可能需要调整某些参数。

重新生成

语音播放

复制回答

如何在CentOS上从远程服务器备份MySQL数据库到本地