云服务器 ECS Linux 服务器下 MySQL 自动备份脚本的使用方法

  • A+
所属分类:MySQL
高性能企业级服务器首台5折

问题说明


如何对 ECS Linux 系统中部署的 MySQL 进行自动备份。

 

处理办法


在 ECS Linux 系统中搭建了 MySQL 服务,用户可以使用如下脚本实现 MySQL 的定期自动备份。

使用方法如下:

  1. 将以下脚本拷贝到本地,上传到服务器上,名称叫 “autoback.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
<span class="hljs-meta">#!/bin/bash</span>
<span class="hljs-comment">#-----------------------------------------------#</span>
<span class="hljs-comment">#This is a  free GNU GPL version 3.0 or abover</span>
<span class="hljs-comment">#Copyright (C) 2008 06 05</span>
<span class="hljs-comment">#mysql_backup Dedicated copyright by My</span>
<span class="hljs-comment">#-----------------------------------------------#</span>
<span class="hljs-built_in">echo</span> -e [`date +<span class="hljs-string">"%Y-%m-%d %H:%M:%S"</span>`] start
<span class="hljs-comment">#system time</span>
time=`date +<span class="hljs-string">"%y-%m-%d"</span>`
<span class="hljs-comment">#host IP</span>
host=<span class="hljs-string">"127.0.0.1"</span>
<span class="hljs-comment">#database backup user</span>
user=<span class="hljs-string">"root"</span>
<span class="hljs-comment">#database password</span>
passwd=<span class="hljs-string">"yourpasswd"</span>
<span class="hljs-comment">#Create a backup directory</span>
mkdir -p /backup/db/<span class="hljs-string">"<span class="hljs-variable">$time</span>"</span>
<span class="hljs-comment">#list database name</span>
all_database=`/usr/bin/mysql -u<span class="hljs-variable">$user</span> -p<span class="hljs-variable">$passwd</span> -Bse <span class="hljs-string">'show databases'</span>`
<span class="hljs-comment">#in the table from the database backup</span>
<span class="hljs-keyword">for</span> i <span class="hljs-keyword">in</span> <span class="hljs-variable">$all_database</span>
<span class="hljs-keyword">do</span>
/usr/bin/mysqldump -u<span class="hljs-variable">$user</span> -p<span class="hljs-variable">$passwd</span> <span class="hljs-variable">$i</span> &gt; /backup/db/<span class="hljs-string">"<span class="hljs-variable">$time</span>"</span>/<span class="hljs-string">"<span class="hljs-variable">$i</span>"</span>_<span class="hljs-string">"<span class="hljs-variable">$time</span>"</span>.sql
<span class="hljs-keyword">done</span>
<span class="hljs-built_in">echo</span> -e [`date +<span class="hljs-string">"%Y-%m-%d %H:%M:%S"</span>`]  end
<span class="hljs-built_in">exit</span> 0

脚本中的数据库名和数据库密码以用户需要备份的数据库信息为准,需要用户修改下。

  1. 运行 crontab -e,写入以下内容:

1
30 5 * * * root sh /root/autobackup.sh &gt;/dev/null 2&gt;&amp;1

保存退出,之后每天早上 5:30 就会自动备份数据库了。

注意:备份会占用磁盘空间,及时清理不需要的数据或者扩展磁盘空间。 

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: