利用shell开发mysql分库分表备份脚本

说明：

01：数据库架构为主从复制（gtid+增强版半同步主从复制）
02：备份是在从库上进行备份,使用的工具为mysqldump
03：备份的思想如下所示：
    A：停止从库的sql线程(让io线程继续到master上去取binlog)
    B：刷新binlog（把缓存中的数据刷新到磁盘）
    C：针对每个库进行全备
    D：针对每个库中的每个表进行全备,这里就不要刷新binlog日志了
    E：启动sql线程
04：利用crond+脚本的方式对数据库进行定时备份
05：只在本机保存最近7天的数据

 脚本

#!/bin/bash
#
# Define variables
RETVAL=0
User=root
Port=3306
Pass="chenliang"
Time=$(date +%Y_%m_%d)
Sock="/mysql/$Port/mysql.sock"
Path="/mysql/apps/mysql/bin"
Login="$Path/mysql -u$User -p$Pass -S $Sock"
Dump="$Path/mysqldump -u$User -p$Pass -S $Sock"
Database=$($Login -e "show databases;"|grep -Ev "_schema|mysql|sys|Database")

# Determine the user to execute
if [ "$UID" -ne "$RETVAL" ];then
   echo "Must be roo to run scripts"
   exit 1
fi

# Load locall functions
[ -f /etc/init.d/functions ] && source /etc/init.d/functions

# Stop slave sql thread
$Login -e "stop slave sql_thread;"
RETVAL=$?
if [ $RETVAL -eq 0 ];then
   action "stop slave sql_thread" /bin/true
  else
   action "stop slave sql_thread" /bin/false
fi

# flush logs;
$Login -e "flush logs;"

# For loop backup mysql $Port instances database and table
for database in $Database
do
	# Determine $Bak_dir1 exists
	Bak_dir1="/data/bak_${Port}/sub_database/$Time"
	if [ ! -d "$Bak_dir1" ];then
	   mkdir $Bak_dir1 -p
	   RETVAL=$?
	   if [ $RETVAL -eq 0 ];then
	      action " mkdir \"$Bak_dir1\" dir" /bin/true
            else
	      action " mkdir \"$Bak_dir1\" dir" /bin/false
	   fi
	fi

    # Backup database
    $Dump -B -F -R -x --set-gtid-purged=off --master-data=2 $database|gzip >$Bak_dir1/${Time}_${Port}_database-${database}.sql.gz
    RETVAL=$?
	if [ $RETVAL -eq 0 ];then
	   action "    backup \"$database\" database" /bin/true
          else
	   action "    backup \"$database\" database" /bin/false
	fi
       
    # Find delete -mtime +7 data
    Bak_dir11="/data/bak_${Port}/sub_database"
    find $Bak_dir11 -maxdepth 1 -type d -mtime +7|xargs rm -fr

	Table=$($Login -e "use $database;show tables;"|grep -v "Tables_in")
	for table in $Table
	do
	    # Determine $Bak_dir2 exists
    	Bak_dir2="/data/bak_${Port}/sub_table/$database/$Time"
		if [ ! -d "$Bak_dir2" ];then
	           mkdir $Bak_dir2 -p
	           RETVAL=$?
		   if [ $RETVAL -eq 0 ];then
	              action "      mkdir \"$Bak_dir2\" dir" /bin/true
	             else
	              action "      mkdir \"$Bak_dir2\" dir" /bin/false
		   fi
		fi	

	    $Dump -R -x --set-gtid-purged=off $database $table|gzip >$Bak_dir2/${Time}_${Port}_${database}_table-${table}.sql.gz
	     RETVAL=$?
	    if [ $RETVAL -eq 0 ];then
	       action "        backup ${database}.${table} table" /bin/true
	      else
	       action "        backup ${database}.${table} table" /bin/false
	    fi

        # Find delete $Bak_dir22 -mtime +7 data
        Bak_dir22="/data/bak_${Port}/sub_table/$database"
        find $Bak_dir22 -maxdepth 1 -type d -mtime +7|xargs rm -fr
	done
done

# mysqladmin flush-logs
$Path/mysqladmin -u$User -p$Pass -S $Sock flush-logs

# Start slave sql thread
$Login -e "start slave sql_thread;"
RETVAL=$?
if [ $RETVAL -eq 0 ];then
   action "start slave sql_thread" /bin/true
  else
   action "start slave sql_thread" /bin/false
fi

# Scripts return values
exit $RETVAL

 

　　

 

　　

转载于:https://www.cnblogs.com/chenliangc/articles/11480326.html