shell脚本实现mysql数据备份

mysql数据备份

今天遇到一个很傻逼的问题，有人登上开发服务器，不知是有意还是无心；把mysql里面的库全部删除了。。。那个人结果如何，我们就不作讨论了。。。没办法我只能写个shell脚本，用crontab跑下定时；做些简单的数据备份了，顺便写个笔记

思路

其实很简单

1. 写一个shell脚本通过mysql的mysqldump，将数据导出成对应的sql文件；
2. 使用linux的crontab定时运行对应脚本，将sql，文件保存到对应的目录下；
3. 可想而知，随着数据量的增加和备份的频率都会导致备份服务器的硬盘资源使用率也会直线攀升；为了解决这个问题，我们就需要，定时清理备份内容；而我还是简单的使用了个shell脚本，通过crontab定时去清理；

---

注意

这里有几个问题需要注意的：

1. 通过mysqldump来导出对应的库表的sql，这样必然会造成mysql服务器的资源消耗（cup，内存，io等）；
2. mysqldump默认的方式会造成锁表，这个很恐怖，会导致线上的服务的中断，也许时间是短暂的，但是这是致命的；（可以通过配置改成事务形式，不锁表）
3. 随着数据量的增大，mysqldump导出sql的时间也会增长；

当然数据备份，你可以选择对应的时间段，按照对应的业务的情况定义备份的周期，又或者如果 mysql服务器上库多，根据不同业业务，不同时间段备份；这也是可以的。。。看具体业务情况咯！

因为我的只是开发和测试用，数据量不会很大，所以影响就可以忽略了，只要在大家都下班了后备份就没关系了（所以我定的是：每天深夜12点备份，一个月后吧上个月的清理了）

---

正题

1.mysqldump的权限说明

mysqldump 所需要的权限说明：

1. 对于table 来说mysqldump 最少要有select 权限。
2. 对于view 来说mysqldump 要有show view 权限。
3. 对于trrigger 来说mysqldump 要有trriger 权限。
4. 如果要产生一份一致的备份 mysqldump 要有lock tables 权限。

下面是用户创建预计（有不懂的可以另行google，就不多展开了）:

create user dumper@'127.0.0.1';
grant select on tempdb.* to dumper@'127.0.0.1';
grant show view on tempdb.* to dumper@'127.0.0.1';
grant lock tables on tempdb.* to dumper@'127.0.0.1';
grant trigger on tempdb.* to dumper@'127.0.0.1';

2.shell脚本（导出数据sql）

#!/bin/sh

# Database info
DB_USER="dumper"
DB_PASS="..."
DB_HOST="..."

# Database array
DB_NAME=("hotel" "food" "foodweb")

# Others vars
BIN_DIR="/usr/bin"            #the mysql bin path
BCK_DIR="/home/mysql-backups"    #the backup file directory
DATE=`date +%F`

# create file
mkdir $BCK_DIR/$DATE

# TODO
# /usr/bin/mysqldump --opt -ubatsing -pbatsingpw -hlocalhost timepusher > /mnt/mysqlBackup/db_`date +%F`.sql

for var in ${DB_NAME[@]};
do
    $BIN_DIR/mysqldump --opt --single-transaction --master-data=2 -u$DB_USER -p$DB_PASS -h$DB_HOST $DB_NAME > $BCK_DIR/$DATE/db_$var.sql
done

参数说明：

--master-data[=#]

在备份导出的文件里追加二进制binlog文件的位置和名称
如果值等于1，就会添加一个CHANGE MASTER语句
如果值等于2，就会在CHAGE MASTER语句前添加注释（不起作用了呗~）
这个参数会--lock-all-tables锁表，除非你指定了--single-transaction
这种情况下，锁表只会在dump开始的时候持续一小段时间,照理说 在dump的时候，任何动作都会影响到binlog文件 dump结束之后，选项会自动关闭锁表功能

--single-transaction

以事务的形式执行

3.shell脚本（按时批量清除N天前脚本）

#!/bin/sh

find /home/mysql-backups -mtime +30 -name "*.*" -exec rm -Rf {} \;

说明:

• /home/lifeccp/dicom/studies ：准备要进行清理的任意目录
• -mtime：标准语句写法
• ＋10：查找10天前的文件，这里用数字代表天数，＋30表示查找30天前的文件
• "."：希望查找的数据类型，".jpg"表示查找扩展名为jpg的所有文件，""表示查找所有文件
• -exec：固定写法
• rm -rf：强制删除文件，包括目录
• {} ; ：将find的结果放到里面

4.crontab定时启动脚本

crontab -e

0 0 * * * /home/sh/mysql-backups/dump.sh
0 0 1 * * /home/sh/mysql-backups/del.sh