Linux 数据库分库分表定时备份

环境准备

mysqldump

安装：yum -y install mysql-client / apt-get install mysql-client
使用：mysqldump -u root -pxxx -h127.0.0.1 -P yyy db_name --default-character-set=utf8 > /data/xxx.sql

crontab

yum install vixie-cron
yum install crontabs

crontab服务命令
service crond start //启动服务
service crond stop //关闭服务
service crond restart //重启服务
service crond reload //重新载入配置

shell脚本及定时任务

shell脚本

#!/bin/bash
mysql_user=root
mysql_pwd=E8rQ1W1tgF57xOrP
# backup path
backup_dir=/opt/mysqlbackup
# backup date
backup_date=$(date +%Y%m%d%H%M%s)
# backup count
backup_count=3
# base cmd
mysql_cmd="mysql -u$mysql_user -p$mysql_pwd"
mysql_dump="mysqldump -u$mysql_user -p$mysql_pwd -x -B -F -R"

# dbs
db_list=`$mysql_cmd -e "show databases;" |sed 1d|egrep "^db_" `
echo "$db_list"
db_names=($db_list)
echo "$backup_date 需要备份的数据库共 ${#db_names[*]} 个，分别为：${db_names[*]}"

# to backup
for db_name in $db_list;do
 echo "backup $db_name start"
 # mkdir
 if [ ! -d "$backup_dir/$db_name/" ]; then
    mkdir $backup_dir/$db_name/
    echo "dictory $backup_dir/$db_name/ created"
 fi

  # mysqldump -uroot -p -B db > /backup_dir/backup_name
  $mysql_dump $db_name >$backup_dir/$db_name/$db_name-$backup_date.dump

  # log
  echo "backup $db_name-$backup_date.dump finished" >>$backup_dir/$db_name/log.txt

  # history backup file name to be delete (with path)
  rm_file_name=$(ls -l -crt $backup_dir/$db_name/*.dump | awk '{print $9}' | head -1)

  # current backup files count
  current_backup_count=$(ls -l -crt $backup_dir/$db_name/*.dump | awk '{print $9}' | wc -l)

  # delete
  if [ $current_backup_count -gt $backup_count ]; then
    rm $rm_file_name
    # log
    echo "rm $rm_file_name finished" >>$backup_dir/$db_name/log.txt
  fi

  echo "backup $db_name finish"

done

echo -e "$backup_date backup finished\n\n"

定时任务配置

SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
HOME=/

# For details see man 4 crontabs

# Example of job definition:
# .---------------- minute (0 - 59)
# |  .------------- hour (0 - 23)
# |  |  .---------- day of month (1 - 31)
# |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...
# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# |  |  |  |  |
# *  *  *  *  * user-name command to be executed

# 数据库定时备份任务
00 */1 * * * root /opt/mysqlbackup/mysqlbackup.sh >> /opt/mysqlbackup/log.txt 

问题

暂无

参考

mysqldump命令的安装

Liunx------Shell脚本备份数据库

MySQL数据备份与还原(mysqldump)

linux定时执行shell脚本