在linux服务器上自动备份MySQL数据库

Question

提问by Jay

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.

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

The script needs to run on the machine hosting the MySql database (It is a linux machine).
The backups must be saved onto the same server that the database is on.
A backup needs to be made every 30 minutes.
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.

该脚本需要在托管 MySql 数据库的机器上运行（它是一台 linux 机器）。
备份必须保存在数据库所在的同一台服务器上。
需要每 30 分钟进行一次备份。
当备份超过一周时，它会被删除，除非它是该周创建的第一个备份。即在这些备份中，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.db12_10_Mon.db 等只保留了 backup_010_Mon.db 等。

Anyone have anything similar or any ideas where to start?

任何人都有类似的东西或任何想法从哪里开始？

Answer 1

采纳答案by hornetbzz

Doing pretty much the same like many people.

和很多人做的差不多。

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"
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 tarballthat you send via scpto your remote machine. Finally you can put your backup script into the crontab(crontab -e).

该脚本需要在托管 MySql 数据库的机器上运行（它是一台 linux 机器）。
=> 在这台机器“A”上创建一个本地 bash 或 perl 脚本（或其他）“myscript”
备份必须保存在数据库所在的同一台服务器上。
=> 在脚本“myscript”中，你可以只使用mysqldump。从本地备份，您可以创建一个通过scp发送到远程机器的tarball。最后，您可以将备份脚本放入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=
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=
local DESTINATION_DIR=
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 findand focus on the mtimeand neweroptions.

然后要删除早于“日期”的文件，您可以查看man find并关注mtime和更新的选项。

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.

编辑：如前所述，除了可以轻松发送 tarball 并在发送时将其删除的临时文件之外，对进行本地备份没有特别的兴趣。

Answer 2

回答by pgl

You can do most of this with a one-line cronjob set to run every 30 minutes:

您可以使用设置为每 30 分钟运行一次的单行 cronjob 来完成大部分工作：

mysqldump -u<user> -p<pass> <database> > /path/to/dumps/db.$(date +%a.%H:%M).dump

This will create a database dump every 30 minutes, and every week it'll start overwriting the previous week's dumps.

这将每 30 分钟创建一次数据库转储，并且每周它将开始覆盖前一周的转储。

Then have another cronjob that runs once a week that copies the most recent dump to a separate location where you're keeping snapshots.

然后有另一个每周运行一次的 cronjob，将最近的转储复制到您保存快照的单独位置。

Answer 3

回答by Alfabravo

Answer: A cron

答案：一个 cron

Description:

说明：

Try creating a file something.sh with this:

尝试使用以下内容创建文件 something.sh：

 #!/bin/sh
 mysqldump -u root -p pwd --opt db1.sql > /respaldosql/db1.sql
 mysqldump -u root -p pwd --opt db2.sql > /respaldosql/db2.sql
 cd /home/youuser/backupsql/
 tar -zcvf backupsql_$(date +%d%m%y).tgz *.sql
 find -name '*.tgz' -type f -mtime +2 -exec rm -f {} \;

Give the adequate permission to the file

给文件足够的权限

 chmod 700 mysqlrespaldo.sh

or

或者

 sudo chmod 700 something.sh

and then create a cron with

然后创建一个cron

 crontab -e

setting it like

设置它像

 **0 1 * * *** /home/youruser/coolscripts/something.sh

Remember that the numbers or '*' characters have this structure:

请记住，数字或“*”字符具有以下结构：

Minutes (range 0-59)
Hours (0-23)
Day of month (1-31)
Month (1-12)
Day of the week (0-6 being 0=Domingo)
Absolute path to script or program to run

Minutes (range 0-59)
Hours (0-23)
Day of month (1-31)
Month (1-12)
Day of the week (0-6 being 0=Domingo)
Absolute path to script or program to run

Answer 4

回答by Ankit Singhania

Create a shell script like the one below:

创建一个如下所示的 shell 脚本：

#!/bin/bash
mysqldump -u username -p'password' dbname > /my_dir/db_$(date+%m-%d-%Y_%H-%M-%S).sql
find /mydir -mtime +10 -type f -delete

Replace username, password and your backup directory(my_dir). Save it in a directory(shell_dir) as filename.sh

替换用户名、密码和您的备份目录（my_dir）。将其保存在目录（shell_dir）中作为 filename.sh

Schedule it to run everyday using crontab -e like:

使用 crontab -e 安排它每天运行，例如：

30 8 * * * /shell_dir/filename.sh

This will run everyday at 8:30 AM and backup the database. It also deletes the backup which is older than 10 days. If you don't wanna do that just delete the last line from the script.

这将在每天上午 8:30 运行并备份数据库。它还会删除超过 10 天的备份。如果您不想这样做，只需从脚本中删除最后一行。

Answer 5

回答by shaneonabike

My preference is for AutoMySQLBackupwhich comes with Debian. It's really easy and creates daily backups, which can be configured. As well, it stores on weekly and then one monthly backup as well.

我更喜欢Debian 附带的AutoMySQLBackup。这真的很容易，并且可以创建每日备份，可以进行配置。同样，它每周存储一次，然后每月备份一次。

I have had this running for a while and it's super easy to configure and use!

我已经运行了一段时间，它非常容易配置和使用！

Answer 6

回答by ggg

You might consider this Open Source tool, matiri, https://github.com/AAFC-MBB/matiriwhich is a concurrent mysql backup script with metadata in Sqlite3. Features (more than you were asking for...):

您可能会考虑这个开源工具 matiri，https://github.com/AAFC-MBB/matiri，它是一个并发 mysql 备份脚本，在 Sqlite3 中带有元数据。特点（比你要求的更多......）：

Multi-Server: Multiple MySQL servers are supported whether they are co-located on the same or separate physical servers.
Parallel: Each database on the server to be backed up is done separately, in parallel (concurrency settable: default: 3)
Compressed: Each database backup compressed
Checksummed: SHA256 of each compressed backup file stored and the archive of all files
Archived: All database backups tar'ed together into single file
Recorded: Backup information stored in Sqlite3 database

多服务器：支持多个 MySQL 服务器，无论它们位于同一台还是单独的物理服务器上。
Parallel：要备份的服务器上的每个数据库都单独完成，并行（并发可设置：默认：3）
压缩：每个数据库备份都被压缩
校验和：存储的每个压缩备份文件的 SHA256 和所有文件的存档
存档：所有数据库备份都打包成一个文件
记录：Sqlite3数据库中存储的备份信息

Full disclosure: original matiri author.

完全披露：原始 matiri 作者。

Answer 7

回答by apogoreliy

After a brief reading the question and the good answers i would add few more points. Some of them are mentioned already.

在简要阅读问题和好的答案后，我会补充几点。其中一些已经提到。

The backup process can involve next steps:

备份过程可能涉及以下步骤：

Create a backup
Compress the backup file
Encrypt the compressed backup
Send the backup to a cloud (DropBox, OneDrive, GoogleDrive, AmazonS3,...)
Get a notification about results
Setup a schedule to run the backup process periodically
Delete the old backup files

创建备份
压缩备份文件
加密压缩备份
将备份发送到云端（DropBox、OneDrive、GoogleDrive、AmazonS3...）
获取结果通知
设置计划以定期运行备份过程
删除旧的备份文件

To compound a script to cover all the backup steps you need an effort and knowledge.

要复合脚本以涵盖所有备份步骤，您需要付出努力和知识。

I would like to share a link to an article(i'm one of the writers) which describes the most used ways to backup MySQL databaseswith some details:

我想分享一篇文章的链接（我是作者之一），该文章描述了备份 MySQL 数据库的最常用方法，并提供了一些详细信息：

Bash script

# Backup storage directory  
backup_folder=/var/backups

# Notification email address 
recipient_email=<[email protected]>

# MySQL user
user=<user_name>

# MySQL password
password=<password>

# Number of days to store the backup 
keep_day=30 

sqlfile=$backup_folder/all-database-$(date +%d-%m-%Y_%H-%M-%S).sql
zipfile=$backup_folder/all-database-$(date +%d-%m-%Y_%H-%M-%S).zip 

# Create a backup 
sudo mysqldump -u $user -p$password --all-databases > $sqlfile 

if [ $? == 0 ]; then
   echo 'Sql dump created' 
else
   echo 'mysqldump return non-zero code' | mailx -s 'No backup was created!' $recipient_email  
   exit 
fi 

# Compress backup 
zip $zipfile $sqlfile 

if [ $? == 0 ]; then
   echo 'The backup was successfully compressed' 
else
   echo 'Error compressing backup' | mailx -s 'Backup was not created!' $recipient_email 
   exit 
fi 

rm $sqlfile 

echo $zipfile | mailx -s 'Backup was successfully created' $recipient_email 

# Delete old backups 
find $backupfolder -mtime +$keep_day -delete

Automysqlbackup

sudo apt-get install automysqlbackup
wget https://github.com/sixhop/AutoMySQLBackup/archive/master.zip

mkdir /opt/automysqlbackup
mv AutoMySQLBackup-master.zip 
cd /opt/automysqlbackup
tar -zxvf AutoMySQLBackup-master.zip

./install.sh

sudo nano /etc/automysqlbackup/automysqlbackup.conf

CONFIG_configfile="/etc/automysqlbackup/automysqlbackup.conf"
CONFIG_backup_dir='/var/backup/db'
CONFIG_mysql_dump_username='root'
CONFIG_mysql_dump_password='my_password'
CONFIG_mysql_dump_host='localhost'
CONFIG_db_names=('my_db')
CONFIG_db_exclude=('information_schema')
CONFIG_mail_address='[email protected]'
CONFIG_rotation_daily=6
CONFIG_rotation_weekly=35
CONFIG_rotation_monthly=150

automysqlbackup /etc/automysqlbackup/automysqlbackup.conf

Third party tools

Bash 脚本

# Backup storage directory  
backup_folder=/var/backups

# Notification email address 
recipient_email=<[email protected]>

# MySQL user
user=<user_name>

# MySQL password
password=<password>

# Number of days to store the backup 
keep_day=30 

sqlfile=$backup_folder/all-database-$(date +%d-%m-%Y_%H-%M-%S).sql
zipfile=$backup_folder/all-database-$(date +%d-%m-%Y_%H-%M-%S).zip 

# Create a backup 
sudo mysqldump -u $user -p$password --all-databases > $sqlfile 

if [ $? == 0 ]; then
   echo 'Sql dump created' 
else
   echo 'mysqldump return non-zero code' | mailx -s 'No backup was created!' $recipient_email  
   exit 
fi 

# Compress backup 
zip $zipfile $sqlfile 

if [ $? == 0 ]; then
   echo 'The backup was successfully compressed' 
else
   echo 'Error compressing backup' | mailx -s 'Backup was not created!' $recipient_email 
   exit 
fi 

rm $sqlfile 

echo $zipfile | mailx -s 'Backup was successfully created' $recipient_email 

# Delete old backups 
find $backupfolder -mtime +$keep_day -delete

自动备份mysql

sudo apt-get install automysqlbackup
wget https://github.com/sixhop/AutoMySQLBackup/archive/master.zip

mkdir /opt/automysqlbackup
mv AutoMySQLBackup-master.zip 
cd /opt/automysqlbackup
tar -zxvf AutoMySQLBackup-master.zip

./install.sh

sudo nano /etc/automysqlbackup/automysqlbackup.conf

CONFIG_configfile="/etc/automysqlbackup/automysqlbackup.conf"
CONFIG_backup_dir='/var/backup/db'
CONFIG_mysql_dump_username='root'
CONFIG_mysql_dump_password='my_password'
CONFIG_mysql_dump_host='localhost'
CONFIG_db_names=('my_db')
CONFIG_db_exclude=('information_schema')
CONFIG_mail_address='[email protected]'
CONFIG_rotation_daily=6
CONFIG_rotation_weekly=35
CONFIG_rotation_monthly=150

automysqlbackup /etc/automysqlbackup/automysqlbackup.conf

第三方工具

Hope it would be helpful!

希望它会有所帮助！

在linux服务器上自动备份MySQL数据库

提问by Jay

采纳答案by hornetbzz

回答by pgl

回答by Alfabravo

回答by Ankit Singhania

回答by shaneonabike

回答by ggg

回答by apogoreliy

相关推荐

最近更新

标签

在linux服务器上自动备份MySQL数据库

提问by Jay

采纳答案by hornetbzz

回答by pgl

回答by Alfabravo

回答by Ankit Singhania

回答by shaneonabike

回答by ggg

回答by apogoreliy

相关推荐

C# 中的“受保护”方法？

C# 如何处理被阻止的剪贴板和其他奇怪的东西

C# 视频课程

C# 自动将一个对象的属性值应用到另一个相同类型的对象？

相关推荐

最近更新

标签