目录
设计需求
定时分库分表来备份Mysql数据库。
需求拆解
-
设计shell连接Mysql数据库的代码模块
-
设计shell对Mysql数据库进行分库分表的代码模块
-
设计shell定时备份Mysql数据表数据的代码模块
代码实现
#!/bin/bash
# 声明数据库变量
user="root"
#password="xxxxx" #这里是要设计这个变量和实际的密码,编辑规范
host="localhost"
# 输入密码
echo "pls input the Mysql Password: "
stty -echo
read passwd
# 判定密码是否有效
if [[ ${passwd} == ${password} ]];then
echo "Mysql Password is Right!"
continue
else
echo "Mysql Password is Wrong!"
exit
fi
# 获取Mysql数据库schema
dbname_lst=($(mysql -h"${host}" -u"${user}" -p"${passwd}" <<EOF 2> /dev/null | egrep -v 'information_schema|metastore|mysql|performance_schema|sys' |tail -n +2 |tr '\n' ' ' |sed 's/.$//'
show databases;
EOF
))
echo ">>>打印数据库database与数据表tables的备份信息<<<"
# 获取数据表tables
for idx in "${!dbname_lst[@]}"
do
# 获取单个数据库
dbname="${dbname_lst[idx]}"
# 打印数据库信息
echo "----数据库: ${dbname}"
# 备份单个数据库数据
# 声明数据库备份的路径
postfix_file=`date "+%s"`
dataBaseBackupDir="/Users/hadoop/shellHome/shellSqlFiles/MysqlBackup_db_${dbname}_${postfix_file}.sql"
mysqldump -h"${host}" -u"${user}" -p"${passwd}" --databases "${dbname}" >"${dataBaseBackupDir}" 2> /dev/null
dbbackcode=$([ $? -eq 0 ] && echo "0" || echo "1")
# 判定数据库数据备份是否成功
if [[ ${dbbackcode} == "0" ]];then
echo "------数据库:${dbname}数据备份成功!"
else
echo "------数据库:${dbname}数据备份失败!"
fi
# 生成数据表列表
table_name=($(mysql -h"${host}" -u"${user}" -p"${passwd}" -D"${dbname}" <<EOF 2> /dev/null |tail -n +2
show tables;
EOF))
# 备份每个数据表
for idx_t in "${!table_name[@]}"
do
tab="${table_name[idx_t]}"
# 输出数据库和数据表
echo "--------数据表: ${tab}"
# 声明数据表备份路径
dataTableBackupDir="/Users/hadoop/shellHome/shellSqlFiles/MysqlBackup_tab_${tab}_${postfix_file}.sql"
mysqldump -h"${host}" -u"${user}" -p"${passwd}" "${dbname}" "${tab}" >"${dataTableBackupDir}" 2> /dev/null
tabbackcode=$([ $? -eq 0 ] && echo "0" || echo "1")
# 判定数据表数据备份是否成功
if [[ ${tabbackcode} == "0" ]];then
echo "----------数据表:${tab}数据备份成功!"
else
echo "----------数据表:${tab}数据备份失败!"
fi
done
done
权限控制
chmod a+x /Users/hadoop/shellHome/shellScripts/PeriodicMysqlBackupDatabaseSharding.sh
定时备份
vim /etc/crontab
00 00 * * * root /Users/hadoop/shellHome/shellScripts/PeriodicMysqlBackupDatabaseSharding.sh