在Linux服务器上自动备份MySQL数据库 [英] Automatically Backup MySQL database on linux server

问题描述

我需要一个自动备份MySql数据库的脚本.我知道已经有很多关于此主题的文章和脚本，但这是我的不同之处.

1. 该脚本需要在托管MySql数据库的计算机(这是一台Linux计算机)上运行.
2. 备份必须保存到数据库所在的同一服务器上.
3. 需要每30分钟进行一次备份.
4. 如果备份早于一周，则将其删除，除非它是该周创建的第一个备份.即在这些备份中，backup_1_12_2010_0-00_Mon.db，backup_1_12_2010_0-30_Mon.db，backup_1_12_2010_1-00_Mon.db ... backup_7_12_2010_23-30_Sun.db等仅保留了备份_1_12_2010_0-00_Mon.db.

有人有类似的想法或从哪里开始的想法吗?

解决方案

就像许多人一样做.

1. 该脚本需要在托管MySql数据库的计算机上运行(这是Linux计算机).
   =>在此机器"A"上创建本地bash或perl脚本(或其他)"myscript"

2. 备份必须保存到数据库所在的同一服务器上.
   =>在脚本"myscript"中，您可以仅使用 mysqldump .从本地备份中，您可以创建一个 tarball ，并通过 scp 发送到远程计算机.最后，您可以将备份脚本放入 crontab (crontab -e).

一些提示和函数可以帮助您入门，因为我不会发布整个脚本，它不能完全解决问题，但并不遥远:

#!/bin/sh
...
MYSQLDUMP="$(which mysqldump)"   
FILE="$LOCAL_TARBALLS/$TARBALL/mysqldump_$db-$SNAPSHOT_DATE.sql"  
$MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db > $FILE && $GZIP $GZ_COMPRESSION_LEVEL $FILE   

function create_tarball()
{
local tarball_dir=$1
tar -zpcvf $tarball_dir"_"$SNAPSHOT_DATE".tar.gz" $tarball_dir >/dev/null
return $?
}

function send_tarball()
{
local PROTOCOLE_="2"
local IPV_="4"
local PRESERVE_="p"
local COMPRESSED_="C"
local PORT="-P $DESTINATION_PORT"
local EXECMODE="B"

local SRC=$1
local DESTINATION_DIR=$2
local DESTINATION_HOST=$DESTINATION_USER"@"$DESTINATION_MACHINE":"$DESTINATION_DIR

local COMMAND="scp -$PROTOCOLE_$IPV_$PRESERVE_$COMPRESSED_$EXECMODE $PORT $SRC $DESTINATION_HOST &"

echo "remote copy command: "$COMMAND
[[ $REMOTE_COPY_ACTIVATED = "Yes" ]] && eval $COMMAND

}

然后删除早于日期"的文件，您可以查看查找，然后将焦点放在 mtime 和更新选项上. /p>

如前所述，进行本地备份并没有特别的兴趣，除了可以轻松发送tarball并在发送后将其删除的temproray文件之外.

I need a script that automatically makes a backup of a MySql Database. I know there are a lot of posts and scripts out there on this topic already but here is where mine differs.

1. The script needs to run on the machine hosting the MySql database (It is a linux machine).
2. The backups must be saved onto the same server that the database is on.
3. A backup needs to be made every 30 minutes.
4. When a backup is older than a week it is deleted unless it is the very first backup created that week. i.e out of these backups backup_1_12_2010_0-00_Mon.db, backup_1_12_2010_0-30_Mon.db, backup_1_12_2010_1-00_Mon.db ... backup_7_12_2010_23-30_Sun.db etc only backup_1_12_2010_0-00_Mon.db is kept.

Anyone have anything similar or any ideas where to start?

解决方案

Doing pretty much the same like many people.

1. The script needs to run on the machine hosting the MySql database (It is a linux machine).
   => Create a local bash or perl script (or whatever) "myscript" on this machine "A"

2. The backups must be saved onto the same server that the database is on.
   => in the script "myscript", you can just use mysqldump. From the local backup, you may create a tarball that you send via scp to your remote machine. Finally you can put your backup script into the crontab (crontab -e).

Some hints and functions to get you started as I won't post my entire script, it does not fully do the trick but not far away :

#!/bin/sh
...
MYSQLDUMP="$(which mysqldump)"   
FILE="$LOCAL_TARBALLS/$TARBALL/mysqldump_$db-$SNAPSHOT_DATE.sql"  
$MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db > $FILE && $GZIP $GZ_COMPRESSION_LEVEL $FILE   

function create_tarball()
{
local tarball_dir=$1
tar -zpcvf $tarball_dir"_"$SNAPSHOT_DATE".tar.gz" $tarball_dir >/dev/null
return $?
}

function send_tarball()
{
local PROTOCOLE_="2"
local IPV_="4"
local PRESERVE_="p"
local COMPRESSED_="C"
local PORT="-P $DESTINATION_PORT"
local EXECMODE="B"

local SRC=$1
local DESTINATION_DIR=$2
local DESTINATION_HOST=$DESTINATION_USER"@"$DESTINATION_MACHINE":"$DESTINATION_DIR

local COMMAND="scp -$PROTOCOLE_$IPV_$PRESERVE_$COMPRESSED_$EXECMODE $PORT $SRC $DESTINATION_HOST &"

echo "remote copy command: "$COMMAND
[[ $REMOTE_COPY_ACTIVATED = "Yes" ]] && eval $COMMAND

}

Then to delete files older than "date", you can look at man find and focus on the mtime and newer options.

Edit: as said earlier, there is no particular interest in doing a local backup except a temproray file to be able send a tarball easily and delete it when sent.

这篇关于在Linux服务器上自动备份MySQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！