Linux 定时备份MySQL数据库

一、创建备份专用的账号，并赋予相应的权限

/**
* 假设给本地mysql的test数据库创建一个dumper备份专用账号，密码为123456
**/
/*创建一个数据库备份的专用账号*/
CREATE USER dumper@localhost IDENTIFIED BY '123456'; 

/*如果备份时指定 --all-databases，需要赋予show databases权限*/
GRANT SHOW DATABASES ON *.* TO dumper@localhost; 

/*如果备份时指定 -F，需要赋予截断二进制日志的权限*/
GRANT Reload ON *.* TO dumper@localhost;

/*如果备份时指定 -R，需要赋予查看自定义函数、存储过程的权限*/
GRANT SELECT ON `mysql`.`proc` TO dumper@localhost;

/*赋予查询数据的权限*/
GRANT SELECT ON test.* TO dumper@localhost; 

/*赋予查看视图ddl的权限*/
GRANT SHOW VIEW ON test.* TO dumper@localhost; 

/*赋予锁表权限*/
GRANT LOCK TABLES ON tesst.* TO dumper@localhost; 

/*触发器的权限*/
GRANT TRIGGER ON test.* TO dumper@localhost;

二、创建备份目录

> cd /home/gmd
> mkdir mysql_dumper

## 创建mysql_dumper文件夹，用于存放配置文件和备份文件。该文件需要较大的存储空间，可以通过以下指令寻找存储空间比较大的文件夹路径。
> df -h

三、创建备份配置文件

3.1 创建备份配置文件（my.cnf）

## 文件名 my.cnf 

## 查询被修改过的数据库 用的用户名和密码
[mysql]
user=dumper
password=123456

## 备份数据库用的用户名和密码
[mysqldump]
user=dumper
password=123456


# 如果有一些表不需要备份，可以添加如下配置，注意，必须放在[mysqldump]下面，必须写成库名.表名的格式。
# 也可以在mysqldump后面带上参数--ignore-table=edu.table1
ignore-table=edu.table1
ignore-table=edu.table2

3.2 创建查询需备份的数据库的SQL文件（sql文件）

/*3d.sql 查询最近3天有更改的数据库*/

SET GROUP_CONCAT_MAX_LEN = 102400;

SELECT 
    GROUP_CONCAT(DISTINCT t.TABLE_SCHEMA SEPARATOR ' ') table_schema
FROM (
    SELECT
        t.TABLE_SCHEMA
    FROM
        information_schema.TABLES t
    WHERE
        t.TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    GROUP BY
        t.TABLE_SCHEMA
    HAVING
        MAX(IFNULL(t.UPDATE_TIME, t.CREATE_TIME)) >= DATE_SUB(NOW(), INTERVAL 3 DAY)
) t
GROUP BY ''

/*all.sql 查询所有非系统库*/

SET GROUP_CONCAT_MAX_LEN = 102400;
 
SELECT
  GROUP_CONCAT(DISTINCT t.TABLE_SCHEMA SEPARATOR ' ') table_schema
FROM
  information_schema.TABLES t
WHERE
  t.TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
GROUP BY ''

四、编写备份脚本（backups.sh）

备份策略：每月01号备份所有数据库，其余每天备份最近3天有变动的数据库

## 文件名 backups.sh

# 定义变量
dir=/home/gmd/mysql_dumper             # 备份主目录
dir_date=`date +%Y%m%d`                # 备份日期目录
file_time=`date +%H%M`                 # 备份文件时间标识
mysqldump="mysqldump --defaults-file=${dir}/my.cnf"     # MySQL备份程序位置
mysql="mysql --defaults-file=${dir}/my.cnf"             # MySQL程序路径
 
# 每月01日，备份所有的库，其余日期只备份3日内变动的库
if [ "`date +%d`" = "01" ]
then
  mysql --defaults-file=${dir}/my.cnf < ${dir}/all.sql > ${dir}/tmp
else
  mysql --defaults-file=${dir}/my.cnf < ${dir}/3d.sql > ${dir}/tmp
fi
dbname=(`tail -n1 ${dir}/tmp`)                # 数据库名
file_name=("${dbname[@]}")                    # 备份文件名
dbnum=${#dbname[*]}                           # 数据库数量
rm -f ${dir}/tmp

# 删除30天前的备份
if test -d ${dir}/`date -d"30 days ago" +%Y%m%d`/
then
    rm -rf ${dir}/`date -d"30 days ago" +%Y%m%d`
fi

# 创建今天的文件夹
if test ! -d ${dir}/${dir_date}/
then
    mkdir ${dir}/${dir_date}
fi

# 备份、压缩数据
for ((i=0;i<${dbnum};i++)); do
  echo ${i}/${dbnum}:bak ${dbname[${i}]} to ${file_name[${i}]}
  ${mysqldump} -R -F ${dbname[${i}]} > ${dir}/${dir_date}/${file_name[${i}]}_${file_time}.sql
  zip -j ${dir}/${dir_date}/${file_name[${i}]}_${file_time}.zip ${dir}/${dir_date}/${file_name[${i}]}_${file_time}.sql
  rm -f ${dir}/${dir_date}/${file_name[${i}]}_${file_time}.sql
done

至此，可以直接通过 ./backup.sh 指令看看程序是否有问题。 

五、添加Linux定时任务

## 查看当前用户的定时任务
> crontab -l

## 添加定时任务
> crontab -e

## 内容如下：
MAILTO=""
0 1 * * * /home/gmd/mysql_dumper/backups.sh         # MySQL定时备份每天晚上1点定时执行

六、将备份的文件上传至其他文件服务器

如果无需将文件上传至文件服务器，那么至此就结束了。如果除了本地备份之外，还需要将文件备份至其他文件服务器的话，只需要在backup.sh文件后添加如下语句即可。

说明：你需要准备一个文件服务器。

## 文件服务器信息，可以添加多个，
ftp_server=("192.168.1.121 192.168.1.122")      # ftp服务器ip数组
ftp_user=("ftp_user1 ftp_user2")                # 对应ftp服务器的账号
ftp_pw=("ftp_pwd01 ftp_pwd02")                  # 对应的ftp账号的密码
ftpnum=${#ftp_server[*]}                        # ftp数量
ftpdir=my_project01                  # ftp相对路径（可以用本机ip或项目名称）


# 上传到ftp服务器
for ((i=0;i<${ftpnum};i++)); do
ftp -n<<!
open ${ftp_server[${i}]}
user ${ftp_user[${i}]} ${ftp_pw[${i}]}
prompt off
binary
hash
mkdir ${ftpdir}
mkdir ${ftpdir}/${dir_date}
cd ${ftpdir}/${dir_date}
lcd ${dir}/${dir_date}/
mput *_${file_time}.zip
close
bye
!
done