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%