MySQL、MongoDB 数据库备份

发表于 | 分类于
  1. 线上服务器自动备份数据库
  2. 内网自动备份数据库及下载线上服务器的数据库备份文件
  3. 本地运行PHP脚本,通过 SSH2 连接内网打包数据库备份文件并下载
服务器 Host Port 备份目录
线上 192.168.0.7 22 /data/backup
内网 192.168.0.9 22 /data/backup
自动备份并下载远程数据库脚本 backup.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
#!/bin/sh
# MySQL 参数
declare -A MYSQL_CONFIG=(['host']='127.0.0.1' ['port']=3306 ['user']='backup' ['pwd']='123456')
# 需要备份的 MySQL 数据库
MYSQL_DB_NAMES=('test' 't1' 't2')

# MongoDB 参数
declare -A MONGODB_CONFIG=(['host']='127.0.0.1' ['port']=27017 ['user']='test' ['pwd']='123456')
MONGO_DB_NAMES=('test')

# 远程服务器参数
declare -A REMOTE_SERVER=(['host']='192.168.0.7' ['port']=22 ['user']='root')
# 需要备份的目录
REMOTE_BACKUP_DIR='/data/backup/'

# 备份文件路径
BACKUP_PATH='/data/backup'

function doDump() {
    echo -e "\033[33m==>开始备份..."
    dump_date=`date +%Y%m%d`

    # 遍历要备份的MySQL数据库
    for db_name in ${MYSQL_DB_NAMES[@]}
    do
        # 数据库备份路径
        backup_dir="${BACKUP_PATH}/mysql/${dump_date}"
        # 目录不存在则创建
        if [ ! -d "${backup_dir}" ]; then
            # 创建备份文件目录
            mkdir -p "${backup_dir}"
            if [ "$?" -ne "0" ]; then
                echo -e "\033[31m无法创建${backup_dir}目录"
                exit 1
            fi
        fi

        # 备份文件路径
        dump_file="${backup_dir}/${db_name}.sql"
        /usr/local/mysql/bin/mysqldump -u${MYSQL_CONFIG['user']} -p${MYSQL_CONFIG['pwd']} -h ${MYSQL_CONFIG['host']} -P ${MYSQL_CONFIG['port']} ${db_name} > ${dump_file}
        if [ "$?" -eq "0" ]
        then
            echo -e "\033[32m mysql - ${db_name} 备份成功, 路径：${dump_file}"
        else
            echo -e "\033[31m mysql - ${db_name} 备份失败"
        fi
    done

    # 遍历要备份的MongoDB数据库
    for db_name in ${MONGO_DB_NAMES[@]}
    do
        # 数据库备份路径
        backup_dir="${BACKUP_PATH}/mongodb/${dump_date}"
        # 目录不存在则创建
        if [ ! -d "${backup_dir}" ]; then
            # 创建备份文件目录
            mkdir -p "${backup_dir}"
            if [ "$?" -ne "0" ]; then
                echo -e "\033[31m无法创建${backup_dir}目录"
                exit 1
            fi
        fi

        /usr/local/mongodb/bin/mongodump -h ${MONGODB_CONFIG['host']} --port ${MONGODB_CONFIG['port']} -u ${MONGODB_CONFIG['user']} -p ${MONGODB_CONFIG['pwd']} -d ${db_name} --out=${backup_dir}
        if [ "$?" -eq "0" ]
        then
            echo -e "\033[32m mongodb - ${db_name} 备份成功, 路径：${backup_dir}"
        else
            echo -e "\033[31m mongodb - ${db_name} 备份失败"
        fi
    done

    # 备份路径
    backup_dir="${BACKUP_PATH}/remote"
    # 目录不存在则创建
    if [ ! -d "${backup_dir}" ]; then
        # 创建备份文件目录
        mkdir -p "${backup_dir}"
        if [ "$?" -ne "0" ]; then
            echo -e "\033[31m无法创建${backup_dir}目录"
            exit 1
        fi
    fi
    # 远程服务器文件名称
    file_name="test.${dump_date}.sql"
    scp -P ${REMOTE_SERVER['port']} ${REMOTE_SERVER['user']}@${REMOTE_SERVER['host']}:${REMOTE_BACKUP_DIR}${file_name} ${backup_dir}
    echo -e "\033[32m file - ${file_name} 备份成功, 路径：${backup_dir}/${file_name}"

    echo -e "\033[33m==>备份结束.\033[0m"
}

doDump
本地 PHP 脚本
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
<?php

class SSH2
{
    /**
     * @var resource
     */
    private $ssh;

    /**
     * SSH2 constructor.
     *
     * @param string $host
     * @param int    $port
     * @param string $username
     * @param string $password
     */
    public function __construct(string $host, int $port, string $username, string $password)
    {
        $this->ssh = ssh2_connect($host, $port);
        ssh2_auth_password($this->ssh, $username, $password);
    }

    /**
     * Exec.
     *
     * @param string $command
     *
     * @return bool|string
     */
    public function exec(string $command)
    {
        $stream = ssh2_exec($this->ssh, $command);
        stream_set_blocking($stream, true);

        return stream_get_contents($stream);
    }

    /**
     * Scp send.
     *
     * @param string   $local
     * @param string   $remote
     * @param int|null $mode
     *
     * @return bool
     */
    public function scpSend(string $local, string $remote, ?int $mode = null)
    {
        return ssh2_scp_send($this->ssh, $local, $remote, $mode);
    }

    /**
     * Scp recv.
     *
     * @param string $remote
     * @param string $local
     *
     * @return bool
     */
    public function scpRecv(string $remote, string $local)
    {
        return ssh2_scp_recv($this->ssh, $remote, $local);
    }
}

try {
    $ip = '192.168.0.9';
    $port = 22;
    $username = 'root';
    $password = '123456';
    $ssh2 = new SSH2($ip, $port, $username, $password);
    // 打包远程文件
    $ssh2->exec('cd /data/backup && zip -r backup.zip ./');
    // 拷贝远程文件至本地
    $ssh2->scpRecv('/data/backup/backup.zip', 'D:\backup.zip');
} catch (Exception $e) {
    exit($e->getMessage());
}
相关命令
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 1. MySQL 创建备份用户
GRANT ALL PRIVILEGES ON test.* to 'backup'@'127.0.0.1' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;
# 若要授权多个数据库
GRANT ALL PRIVILEGES ON t1.* to 'backup'@'127.0.0.1';
GRANT ALL PRIVILEGES ON t2.* to 'backup'@'127.0.0.1';
FLUSH PRIVILEGES;

# 2. 可以将 mysql 用于备份的用户信息保存至 my.cnf 配置文件, 避免出现  mysqldump: [Warning] Using a password on the command line interface can be insecure. 警告
vi /etc/my.cnf
[mysqldump]
user=backup
password=123456

# 3. MongoDB 数据库恢复命令
mongorestore -h 127.0.0.1 --port 27017 -u test -p 123456 -d yapi /data/backup/mongodb/20190930/test

# 4. crontab 命令 - 每天 00:30 运行脚本
30 0 * * * /bin/sh /data/backup/backup.sh > /dev/null 2>&1
0%