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

1.1 MySQL架构和备份策略

#### 数据库架构
	MySQL架构的基础架构为主从复制(replication)，目前数据量有 50G +。

#### mydumper工具的安装
    01:是在角色为slave的服务器上安装的;
    02:安装方法请参考博文：https://chenliang.blog.csdn.net/article/details/108571328  的"第1章"

#### 备份需求
    每天对业务库进行分库分表逻辑全备
    
#### 备份策略
	01：每天凌晨2点整在slave上进行备份;
	02：备份使用 mydumper 工具;
	03：备份的数据在本机保留最近7天的数据;
 
#### 脚本实现思路
	01：停止slave的sql线程(让slave的io线程继续从master上取binlog并存放到relaylog)
	02：手动刷新binlog文件;
	03：for循环备份出相关的业务库(每个业务库下的数据保存到分别的目录下)
	04：备份完成后启动slave的SQL线程
	05：删除本机7天前的备份数据
	06：退出脚本

1.2 备份数据的保存目录规划

/data/bak_mysql_3306/                                <== 这是目录[基本目录]
└── 2020-09-19                                       <== 这是目录[每天一个目录]
    ├── 20200919-小时-业务库1的库名-library-all-object  <== 这是目录
    │   ├── 备份出来的文件1
    │   ├── 备份出来的文件2
    │   ├── 备份出来的文件3
    │   ├── 备份出来的文件4
    │   ├── 备份出来的文件5
    │   └── 备份出来的文件N 
    │
    ├── 20200919-小时-业务库2的库名-library-all-object  <== 这是目录
    │   ├── 备份出来的文件1
    │   ├── 备份出来的文件2
    │   ├── 备份出来的文件3
    │   ├── 备份出来的文件4
    │   ├── 备份出来的文件5
    │   └── 备份出来的文件N
    └── bak.log                                         <== 这是脚本执行时记录的日志

1.3 Shell脚本内容

脚本是用root@localhost用户连接mysql后进行备份的,涉及到密码,我一般是把脚本的权限更改成700，该脚本也只能在操作系统下的root用户下执行,脚本中有进行限制;

#!/bin/bash
#
## Define variables
RETVAL=0
User="root"
Port="3306"
Pass="chenliang"
Path="/data/apps/mysql/bin"
Sock="/data/mysql/$Port/run/mysql.sock"
Login="$Path/mysql -u$User -p$Pass -S $Sock"
Dump="mydumper --user=$User --password=$Pass --socket=$Sock"
Dir1="/data/bak_mysql_3306"
Dir2=$(date +%Y-%m-%d)
Time=$(date +%Y%m%d-%H)
Baklog="$Dir1/$Dir2/bak.log"

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

## Determine $Dir1/$Dir2/ directory is exists
if [ ! -d "$Dir1/$Dir2" ];then
   mkdir -p $Dir1/$Dir2
   RETVAL=$?
   if [ $RETVAL -eq 0 ];then
      echo -ne "$Time\n  01:mkdir -p $Dir1/$Dir2 dir success\n" >>$Baklog
    else
      echo -ne "$Time\n  01:mkdir -p $Dir1/$Dir2 dir fail -- exit\n">>$Baklog
      exit 1
   fi   
fi

## Check the installation of the MyDumper tool
Stat1=$(which mydumper|wc -l)
if [ $Stat1 -eq 0 ];then
   echo "  **: Not install mydumper(which mydumper)? exit 1 script"  >>$Baklog
   exit 1
fi

Stat2=$(mydumper -V|wc -l)
if [ $Stat2 -eq 0  ];then
   echo "  **: mydumper is install but myduper -V ?,exit 1 scipts" >>$Baklog
   exit 1 
fi


## Stop slave sql thread
$Login -e "stop slave sql_thread;"
RETVAL=$?
if [ $RETVAL -eq 0 ];then
   echo "  02:stop slave sql thread success" >>$Baklog
  else
   echo "  03:stop slave sql thread fail --exit" >>$Baklog
fi


## Manual flush logs;
$Login -e "flush logs;"
RETVAL=$?
if [ $RETVAL -eq 0 ];then
   echo "  03:flush logs success" >>$Baklog
  else
   echo "  03:flush logs fail" >>$Baklog
fi


## For loop backup mysql $Port instances business library and table
Database=$($Login -e "show databases;"|grep -Ev "_schema|mysql|sys|Database")
for database in $Database
do
    # Create $Dir1/$Dir2/${Time}-${database}-library-all-object directory
    mkdir $Dir1/$Dir2/${Time}-${database}-library-all-object
    RETVAL=$?
    if [ $RETVAL -eq 0 ];then
       echo  "  04:mkdir $Dir1/$Dir2/${Time}-${database}-library-all-object dir success" >>$Baklog
      else
       echo  "  04:mkdir $Dir1/$Dir2/${Time}-${database}-library-all-object dir fail   " >>$Baklog
    fi    

    # Backup database
    $Dump --database=$database --routines --events --triggers \
    --compress --outputdir=$Dir1/$Dir2/${Time}-${database}-library-all-object
    RETVAL=$?
    if [ $RETVAL -eq 0 ];then
       echo "    05:bak $database library all object securess" >>$Baklog
      else
       echo "    05:bak $database library all object fail    " >>$Baklog
    fi
done


## Start slave sql thread
$Login -e "start slave sql_thread;"
RETVAL=$?
if [ $RETVAL -eq 0 ];then
   echo "  06:start slave sql_thread success" >>$Baklog
  else
   echo "  06:start slave sql_thread fail   " >>$Baklog
fi


## Keep the last 7 days of data
find $Dir1 -maxdepth 1 -type d  -mtime +7|xargs rm -fr
RETVAL=$?
if [ $RETVAL -eq 0  ];then
   echo "  07:find $Dir1 -maxdepth 1 -type d -mtime +7  success" >>$Baklog
  else
   echo "  07:find $Dir1 -maxdepth 1 -type d -mtime +7  fail   " >>$Baklog
fi


## Scripts return values
exit $RETVAL

1.4 备份出来的结果

[root@node22 ~]# tree -p /data/bak_mysql_3306/
/data/bak_mysql_3306/
├── [drwxr-xr-x]  2020-09-19
│   ├── [drwxr-xr-x]  20200919-02-chenliang-library-all-object
│   │   ├── [-rw-r--r--]  chenliang.cl1-schema.sql.gz    # chenliang库下cl1表的创建语句;
│   │   ├── [-rw-r--r--]  chenliang.cl1.sql.gz           # chenliang库下cl1表中的数据
│   │   ├── [-rw-r--r--]  chenliang.cl2-schema.sql.gz    # chenliang库下cl2表的创建语句
│   │   ├── [-rw-r--r--]  chenliang.cl2.sql.gz           # chenliang库下cl2表中的数据
│   │   ├── [-rw-r--r--]  chenliang-schema-create.sql.gz # chenliang库的创建语句
│   │   └── [-rw-r--r--]  metadata                       # 备份时记录的binlog
│   ├── [drwxr-xr-x]  20200919-02-lili-library-all-object
│   │   ├── [-rw-r--r--]  lili.li1-schema.sql.gz         
│   │   ├── [-rw-r--r--]  lili.li1.sql.gz               
│   │   ├── [-rw-r--r--]  lili.li2-schema.sql.gz
│   │   ├── [-rw-r--r--]  lili.li2.sql.gz
│   │   ├── [-rw-r--r--]  lili-schema-create.sql.gz
│   │   └── [-rw-r--r--]  metadata
│   └── [-rw-r--r--]  bak.log
│
└── [drwxr-xr-x]  2020-09-20
    ├── [drwxr-xr-x]  20200920-02-chenliang-library-all-object
    │   ├── [-rw-r--r--]  chenliang.cl1-schema.sql.gz
    │   ├── [-rw-r--r--]  chenliang.cl1.sql.gz
    │   ├── [-rw-r--r--]  chenliang.cl2-schema.sql.gz
    │   ├── [-rw-r--r--]  chenliang.cl2.sql.gz
    │   ├── [-rw-r--r--]  chenliang-schema-create.sql.gz
    │   └── [-rw-r--r--]  metadata
    ├── [drwxr-xr-x]  20200920-02-lili-library-all-object
    │   ├── [-rw-r--r--]  lili.li1-schema.sql.gz
    │   ├── [-rw-r--r--]  lili.li1.sql.gz
    │   ├── [-rw-r--r--]  lili.li2-schema.sql.gz
    │   ├── [-rw-r--r--]  lili.li2.sql.gz
    │   ├── [-rw-r--r--]  lili-schema-create.sql.gz
    │   └── [-rw-r--r--]  metadata
    └── [-rw-r--r--]  bak.log

6 directories, 26 files

1.5 备份的日志查看

[root@node22 ~]# cat /data/bak_mysql_3306/2020-09-19/bak.log 
20200919-02
  01:mkdir -p /data/bak_mysql_3306/2020-09-19 dir success
  02:stop slave sql thread success
  03:flush logs success
  04:mkdir /data/bak_mysql_3306/2020-09-19/20200919-02-chenliang-library-all-object dir success
    05:bak chenliang library all object securess
  04:mkdir /data/bak_mysql_3306/2020-09-19/20200919-02-lili-library-all-object dir success
    05:bak lili library all object securess
  06:start slave sql_thread success
  07:find /data/bak_mysql_3306 -maxdepth 1 -type d -mtime +7  success
[root@node22 ~]# 
[root@node22 ~]# 
[root@node22 ~]# 
[root@node22 ~]# 
[root@node22 ~]# cat /data/bak_mysql_3306/2020-09-20/bak.log 
20200920-02
  01:mkdir -p /data/bak_mysql_3306/2020-09-20 dir success
  02:stop slave sql thread success
  03:flush logs success
  04:mkdir /data/bak_mysql_3306/2020-09-20/20200920-02-chenliang-library-all-object dir success
    05:bak chenliang library all object securess
  04:mkdir /data/bak_mysql_3306/2020-09-20/20200920-02-lili-library-all-object dir success
    05:bak lili library all object securess
  06:start slave sql_thread success
  07:find /data/bak_mysql_3306 -maxdepth 1 -type d -mtime +7  success
[root@node22 ~]# 

1.6 slave的状态查看

[root@node22 ~]# mysql -uroot -pchenliang -S /data/mysql/3306/run/mysql.sock -e "show slave status\G"
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.1.21
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 21_mysql_bin.000001
          Read_Master_Log_Pos: 5131
               Relay_Log_File: 22_relay_bin.000010
                Relay_Log_Pos: 313
        Relay_Master_Log_File: 21_mysql_bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 5131
              Relay_Log_Space: 8458
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 21
                  Master_UUID: 6f108d1f-f133-11ea-93a4-000c29e5fcf9
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 6f108d1f-f133-11ea-93a4-000c29e5fcf9:1-11
            Executed_Gtid_Set: 6f108d1f-f133-11ea-93a4-000c29e5fcf9:1-11
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: