MySQL自动备份并清理多少天前的备份文件

自动备份
一、利用mysql提供的mysqldump工具进行数据库的导入导出

二、使用shell命令执行mysqldump
/usr/local/mysql/bin/mysqldump --opt -ubatsing -pbatsingpw -hlocalhost timepusher > /usr/local/sql/back/mcpsms_titan_`date +%F`.sql
注解：
1、执行/usr/local/mysql/bin/mysqldump  mysql安装目录bin目录下的可执行文件；
2、--opt是quick,add-drop-table,add-locks,extended-insert,lock-tables几个参数的名称，一般都要使用，具体意思自行搜索；
3、-u数据库用户名 -p数据库密码 -h数据库地址 数据库名 > 导出的文件路径
4、`date+%F`是shell中生成当前日期，格式如2018-05-30，所以成功导出的文件名为mcpsms_titan_2015-05-30.sql；
5、检查生成的sql文件是否有问题

三、整理shell脚本，方便重用
#!/bin/sh

# tabase info
DB_USER="root"
DB_PASS="root"
DB_HOST="localhost"
DB_NAME="mcpsms-titan"

# Others vars
BIN_DIR="/usr/local/mysql/bin"            #the mysql bin path
BCK_DIR="/usr/local/sql/back"    #the backup file directory
DATE=`date +%F`

# TODO
# /usr/local/mysql/bin/mysqldump --opt -ubatsing -pbatsingpw -hlocalhost timepusher > /usr/local/sql/back/mcpsms_titan_`date +%F`.sql
$BIN_DIR/mysqldump --opt -u$DB_USER -p$DB_PASS -h$DB_HOST $DB_NAME  > $BCK_DIR/mcpsms_titan_$DATE.sql

四、测试shell脚本
1、进入脚本文件目录chmod +x sqlAutoBak.sh添加执行权限
2、./sqlAutoBak.sh
如果mysql包含多个实例，需要指定sock文件，修改脚本为：
#!/bin/sh

# tabase info
DB_USER="root"
DB_PASS="root"
DB_HOST="localhost"
DB_NAME="mcpsms-titan"

# Others vars
BIN_DIR="/usr/local/mysql/bin"            #the mysql bin path
BCK_DIR="/usr/local/sql/back"    #the backup file directory
DATE=`date +%F`

# TODO
# /usr/local/mysql/bin/mysqldump --opt -ubatsing -pbatsingpw -hlocalhost timepusher > /usr/local/sql/back/mcpsms_titan_`date +%F`.sql
$BIN_DIR/mysqldump --opt -u$DB_USER -p$DB_PASS -S /data/3306/mysql.sock  -h$DB_HOST $DB_NAME > $BCK_DIR/mcpsms_titan_$DATE.sql

五、压缩mysql备份数据
sqlAutoBak.sh修改如下：
#!/bin/sh

# tabase info
DB_USER="root"
DB_PASS="root"
DB_HOST="localhost"
DB_NAME="mcpsms-titan"

# Others vars
BIN_DIR="/usr/local/mysql/bin"            #the mysql bin path
BCK_DIR="/usr/local/sql/back"    #the backup file directory
DATE=`date +%F`

# TODO
# /usr/local/mysql/bin/mysqldump --opt -ubatsing -pbatsingpw -hlocalhost timepusher > /usr/local/sql/back/mcpsms_titan_`date +%F`.sql
$BIN_DIR/mysqldump --opt -u$DB_USER -p$DB_PASS -S /data/3306/mysql.sock  -h$DB_HOST $DB_NAME | gzip > $BCK_DIR/mcpsms_titan_$DATE.sql.gz
压缩一定要写在后面，不然会报错

如果该数据库的用户没有分配锁表的权限，则备份会报错when using LOCK TABLES。那是因为mysqldump命令默认在导出时是要锁定表的，所以解决方式有两个。一个是给用户开放锁表的权限；另一个是在命令中加上--skip-lock-tables这个参数。即是：
#!/bin/sh

# tabase info
DB_USER="root"
DB_PASS="root"
DB_HOST="localhost"
DB_NAME="mcpsms-titan"

# Others vars
BIN_DIR="/usr/local/mysql/bin"            #the mysql bin path
BCK_DIR="/usr/local/sql/back"    #the backup file directory
DATE=`date +%F`

# TODO
# /usr/local/mysql/bin/mysqldump --opt -ubatsing -pbatsingpw -hlocalhost timepusher > /usr/local/sql/back/mcpsms_titan_`date +%F`.sql
$BIN_DIR/mysqldump --opt -u$DB_USER -p$DB_PASS -S /data/3306/mysql.sock  -h$DB_HOST $DB_NAME --skip-lock-tables | gzip > $BCK_DIR/mcpsms_titan_$DATE.sql.gz

六、设置linux定时任务执行脚本
1、编辑定时任务列表
crontab -e
2、插入下面这一行
00 05 * * * /bin/sh /usr/local/sql/sqlAutoBak.sh
#每天早上5:00执行
3、查看任务是否创建成功
crontab -l

七、第二天检查自动生成的sql文件是否符合要求

自动删除N天前备份的数据文件
一、删除文件命令
find 对应目录 -mtime +天数 -name "文件名" -exec rm -rf {} \;

二、计划任务
1、创建shell脚本
touch /usr/local/sql/sqlAutoDel.sh
chmod +x sqlAutoDel.sh
新建一个执行文件，并且分配权限
2、编辑shell脚本
#!/bin/sh
find /usr/local/sql/back/ -mtime +2 -name '*.gz' -exec rm -rf {} \;
保存退出
3、计划任务
crontab -e
将sqlAutoDel.sh加入到计划任务中
10 05 * * * /bin/sh /usr/local/sql/sqlAutoDel.sh >/dev/null 2>&1
#每天早上5:10执行清理任务