mysql备份数据库案例_案例一、备份MySQL数据库

本脚本的核心知识点是如何备份所有的库。

一台MySQL服务器&＃xff0c;跑着5个数据库&＃xff0c;没有做主从&＃xff0c;需要对这5个数据库进行备份&＃xff0c;具体要求如下&＃xff1a;

1)每天备份一次&＃xff0c;所有数据库

2)备份数据存到/data/backup/下

3)备份文件名格式示例&＃xff1a;database_name-2019-07-01.sql

4)对1天以前的所有sql文件进行压缩&＃xff0c;格式为gzip

5)本地数据保留1周

6)备份的数据同步到远程备份中心&＃xff0c;本机可以用rsync命令同步&＃xff0c;同步目标地址为rsync://10.10.20.100/mysqlbak/

7)远程备份数据保留一个月

本案例参考脚本&＃xff1a;

#!/bin/bash

##该脚本用来备份本机数据库

##本地保留一周&＃xff0c;远程保留一个月

##作者&＃xff1a;

##日期&＃xff1a;

##版本&＃xff1a;v0.1

mysqldump&＃61;"/usr/local/mysql/bin/mysqldump"

bakuser&＃61;"backup"

passwd&＃61;"34KpmyzUp"

bakdir&＃61;"/data/backup"

remote_dir&＃61;"rsync://10.10.20.100/mysqlbak"

d1&＃61;&＃96;date &＃43;%F&＃96;

d2&＃61;&＃96;date &＃43;%d&＃96;

#定义日志

exec &> /tmp/mysql_bak.log

echo "mysql backup begin at &＃96;date&＃96;"

#对所有数据库进行遍历

for db in db1 db2 db3 db4 db5

do

$mysqldump -u$bakuser -p$passwd $db > $bakdir/$db-$d1.sql

done

#对一天前的所有sql文件压缩

find $bakdir -type f -name "*.sql" -mtime &＃43;1 |xargs gzip

#查找一周前的老文件&＃xff0c;并删除

find $bakdir -type f -mtime &＃43;7 |xargs rm

#把当天的备份文件备份到远程

for db in db1 db2 db3 db4 db5

do

rsync -a $bakdir/$db-$d1.sql $remote_dir/$db-$d2.sql

done

echo "mysql backup end at &＃96;date&＃96;"

说明&＃xff1a;

1)所有以#开头的行都是为解释说明的文字&＃xff0c;为了便于理解&＃xff0c;使用了中文&＃xff0c;尽可能用英文&＃xff0c;因为中文可能会出现乱码。这些注释文字用来对该脚本的描述&＃xff0c;通常会写脚本的作用、作者以及时间或者版本等信息。

2)shell脚本中&＃xff0c;可以使用反引号来引用一条命令的结果&＃xff0c;然后将其保存到变量中。

3)&>&＃xff0c;指定正确输出和错误输出到/tmp/mysql_bak.log

4)在shell脚本中&＃xff0c;一旦遇到exec命令&＃xff0c;则它会接管该shell脚本中后续所有的命令&＃xff0c;可以理解为从exec那行以下所有命令都“被拉到一个盒子里去执行”&＃xff0c;而这个盒子就是exec本身&＃xff0c;所以exec &> 1.txt&＃xff0c;就可以理解为这个盒子里所有操作的输出全部都到了1.txt中。

5)shell脚本中&＃xff0c;循环遍历&＃xff0c;可使用for&＃xff0c;语法为&＃xff1a;for ... ; do  ... ; done

6)本例中要求远程备份文件保存一个月&＃xff0c;由于脚本不能到远程机器上执行find命令&＃xff0c;所以在此想了个办法&＃xff0c;date &＃43;%d表示日期&＃xff0c;一个月的所有日期就都有了&＃xff0c;每天一个新的sql文件。例如&＃xff0c;本月15日sql文件将会覆盖上月15日的&＃xff0c;同理&＃xff0c;下个月会覆盖本月相同日期的文件&＃xff0c;因为文件名是一样的。但无论如何&＃xff0c;总能保存一个月。

7)本例中&＃xff0c;并没有考虑要把当天的sql文件压缩后再同步到远程机器上&＃xff0c;如果想要压缩&＃xff0c;也可以这样做&＃xff1a;1)压缩&＃xff1b;2)同步&＃xff1b;3)解压缩。不压缩的话&＃xff0c;如果数据大&＃xff0c;传输就是一个问题。

知识点一&＃xff1a;MySQL数据库备份

假设已知备份数据的用户为backup&＃xff0c;密码为34KpmyzUp&＃xff0c;要备份5个库分别为db1-db5。备份数据库的命令为&＃xff1a;mysqldump -ubackup -p34KpmyzUp db1 > db1.sql

如果是远程MySQL服务&＃xff0c;要加上IP和port&＃xff0c;例如&＃xff1a;mysqldump -ubackup -p34KpmyzUp -h192.168.100.100 -P3308 db1 > db1.sql

mysqldump工具常用用法&＃xff0c;如下&＃xff1a;

1)只备份表结构&＃xff0c;不备份数据mysqldump -ubackup -p34KpmyzUp -h192.168.100.100 -P3308 -d db1 > db1.sql

2)只备份数据&＃xff0c;不备份表结构mysqldump -ubackup -p34KpmyzUp -h192.168.100.100 -P3308 -t db1 > db1.sql

3)只备份指定表mysqldump -ubackup -p34KpmyzUp -h192.168.100.100 -P3308 db1 tb1 > db1_tb1.sql

4)导出全部数据库mysqldump -ubackup -p34KpmyzUp -h192.168.100.100 -P3308 -A > all_db.sql

5)导出指定多个库mysqldump -ubackup -p34KpmyzUp -h192.168.100.100 -P3308 -B db1 db2 > db1_2.sql

6)--master-data

我们在使用mysqldump工具备份时&＃xff0c;偶尔会用到--master-data选项&＃xff0c;它可以在导出时增加binlog文件的位置和change master语句(这样有利于我们做从库)。

这个选项有两个值&＃xff0c;分别为--master-data&＃61;1和--master-data&＃61;2。

当值等于1&＃xff0c;就会添加一个CHANGE MASTER语句&＃xff1b;

当值等于2&＃xff0c;就会在CHANGE MASTER语句前添加注释&＃xff0c;这个参数会--lock-all-tables锁表&＃xff0c;除非你指定了--single-transation。

若MySQL数据量很大&＃xff0c;比如有几十GB&＃xff0c;使用mysqldump备份工具会很慢&＃xff0c;推荐使用percona-xtrabackup备份工具该工具官方下载地址为https://www.percona.com/software/mysql-database/percona-xtrabackup&＃xff0c;它包含两个备份工具&＃xff1a;xtrabackup和innobackupex。如果数据引擎为myisam或者个别表的数据引擎为myisam&＃xff0c;则需要使用innobackupex。

知识点二&＃xff1a;date命令

date命令&＃xff1a;在Linux系统里可以显示当前的系统日期和时间&＃xff0c;示例&＃xff1a;

# date

2019年 07月 01日 星期一 19:16:52 CST

在shell脚本中&＃xff0c;date命令非常有用&＃xff0c;就比如本例&＃xff0c;要求备份文件名称要带日期。

date &＃43;%F      2019-07-01      日期

date &＃43;%T      18:29:10           时间

date &＃43;%Y       2019                年份(四位数)

date &＃43;%y        19                   年份(两位数)

date &＃43;%m       07                   月

date &＃43;%d        01                   日

date &＃43;%H       18                    小时

date &＃43;%M       29                    分钟

date &＃43;%S        10                    秒钟

date &＃43;%s         1561980346     时间戳(距离1970-01-01 00:000:00 GMT过去多少秒)

date &＃43;%w        1                      周几

date &＃43;%W        26                    本年度第几周

date -d"-1 day" &＃43;%F     2019-06-30     昨天的日期

date -d"1 month ago" &＃43;%F     2019-06-01     一个月以前的日期

date -d"-1 min" &＃43;%Y:%H:%M     2019:19:31   格式化输出指定日期和时间

知识点三&＃xff1a;find命令

在Linux系统里找文件有一个很厉害的工具—find。常用用法示例&＃xff1a;

1)找到logs目录下一周以前的文件find logs/ -type f -mtime &＃43;7

2)找到/etc/目录下所有文件为.cnf结尾的文件find /etc/ -type f -name "*.cnf"

3)找到当前目录下所有权限为777的目录find . -type d -perm 777

4)找到/tmp/下大小超过1MB的文件find /tmp/ -type f -size &＃43;1M

5)找到当前目录下10天前的文件并删除find . -type f -mtime &＃43;10 -exec rm -f {} \;

还可以使用管道&＃43;xargs命令的形式&＃xff1a;find . -type f -mtime &＃43;10 |xargs rm

本例中&＃xff0c;要求把一天前的备份文件找到&＃xff1a;find /data/backup/ -name "*.sql" -mtime &＃43;1

知识点四&＃xff1a;gzip压缩

gzip支持压缩文件&＃xff0c;但不支持压缩目录&＃xff0c;压缩文件的命令&＃xff1a;

gzip file

此时文件被压缩为file.gz&＃xff0c;该文件被压缩后也会被删除掉。解压.gz的文件&＃xff0c;使用-d选项&＃xff0c;如&＃xff1a;

gzip -d file.gz

本例中&＃xff0c;把一天前的备份文件压缩&＃xff0c;命令为&＃xff1a;find /data/backup/ -name "*.sql" -mtime &＃43;1 |xargs gzip

知识点五&＃xff1a;rsync

rsync同步数据有两种方式&＃xff0c;一种是通过ssh协议&＃xff0c;需要知道对方机器的用户密码或者通过密钥认证&＃xff0c;此方式相对来说安全性较差&＃xff0c;毕竟知道了对方机器的密码或者建立了密钥认证&＃xff0c;那么就能登录到对方机器上执行命令。

另一种是通过在远程机器上开启一个rsyncd的服务&＃xff0c;通过它特有的通信协议进行数据传输&＃xff0c;该方式不用密码或建立密钥认证&＃xff0c;但可以设置虚拟用户及密码&＃xff0c;这样就安全多了。本例中给出的远程备份地址&＃xff0c;属于第二种方式。

平时使用rsync同步数据时&＃xff0c;使用-a选项基本上就可以达到我们想要的效果了&＃xff0c;只是有时候会有个别需求&＃xff0c;会用到-a --no-OPTION&＃xff0c;-u&＃xff0c;-L&＃xff0c;--delete&＃xff0c;--exclude及--progress这些&＃xff0c;如果遇到特殊需求了&＃xff0c;可以查一下rsync的man文档。