记录数据库备份与检查脚本

目录

一.主要内容概述

1.备份内容

2.检查内容

二.脚本

1.检查脚本

2.数据备份脚本

三.备份参数概述

1.设置导出字符集

2.GTID特性介绍

3.其他导出时需要注意的选项

4.避免表级锁等待

一.主要内容概述

1.备份内容

• 统计表中的数据单元格数目
• 备份全部数据库数据
• 只备份数据结构
• 只备份数据

2.检查内容

• 检查数据库服务器的状态
• 过滤数据库启动时间
• 过滤数据库列表
• 查询MYSQL最大并发连接数
• 查看SELECT指令被执行数
• 查看UPDATE指令被执行次数
• 查看DELETE指令被查看的次数
• 查看INSERT指令被查看的次数
• 查看COMMIT指令被查看的次数
• 查看ROLLBACK指令被查看的次数
• 查看服务器总指令数
• 查看慢查询数
• 数据库QPS
• 数据库TPS

二.脚本

1.检查脚本

#!/bin/bash
# 定义数据库相关的变量
MYSQL_USER=root          
MYSQL_PASS=mysql_P@s5   # 密码
MYSQL_PORT=3306         # 端口
MYSQL_HOST=localhost    # 数据库ip
MYSQL_ADMIN="mysqladmin -u$MYSQL_USER -p$MYSQL_PASS -P$MYSQL_PORT -h$MYSQL_HOST"
MYSQL_COMM="mysql -u$MYSQL_USER -p$MYSQL_PASS -P$MYSQL_PORT -h$MYSQL_HOST -e"
 
# 定义变量：显示颜色属性
SUCCESS="echo -en \\033[1;32m"   # 绿色
FAILURE="echo -en \\033[1;31m"   # 红色
WARNING="echo -en \\033[1;33m"   # 黄色
NORMAL="echo -en \\033[0;39m"    # 黑色
 
# 检查数据库服务器的状态
$MYSQL_ADMIN ping &> /dev/null
if [ $? -ne 0 ];then
     $FAILURE
     echo "无法连接数据库服务器"
     $NORMAL
     exit
else
     echo -n "数据库状态："
     $SUCCESS
     echo "[OK]"
     $NORMAL
fi
mysql_test(){
# 过滤数据库启动时间
RUN_TIME=$($MYSQL_COMM "SHOW GLOBAL STATUS LIKE 'uptime'" | awk '/Uptime/{print $2}')
echo -n "数据库已运行时间(秒)："
$SUCCESS
echo $RUN_TIME
$NORMAL
 
# 过滤数据库列表
DB_LIST=$($MYSQL_COMM "SHOW DATABASES")
DB_COUNT=$($MYSQL_COMM "SHOW DATABASES" | awk 'NR>=2&&/^[^+]/{db_count++} END{print db_count}')
echo -n "数据库有$DB_COUNT个数据库，分别为:"
$SUCCESS
echo $DB_LIST
$NORMAL
 
# 查询MYSQL最大并发连接数
MAX_CON=$($MYSQL_COMM "SHOW VARIABLES LIKE 'max_connections'" | awk '/max/{print $2}')
echo -n "MYSQL最大并发连接数:"
$SUCCESS
echo $MAX_CON
$NORMAL
 
# 查看SELECT指令被执行数
NUM_SELECT=$($MYSQL_COMM "SHOW GLOBAL STATUS LIKE 'com_select'" | awk '/Com_select/{print $2}')
echo -n "SELECT指令被执行数:"
$SUCCESS
echo $NUM_SELECT
$NORMAL
 
# 查看UPDATE指令被执行次数
NUM_UPDATE=$($MYSQL_COMM "SHOW GLOBAL STATUS LIKE 'com_update'" | awk '/Com_update/{print $2}')
$SUCCESS
echo -n "UPDATE指令被执行次数:"
echo $NUM_UPDATE
$NORMAL
 
# 查看DELETE指令被查看的次数
NUM_DELETE=$($MYSQL_COMM "SHOW GLOBAL STATUS LIKE 'com_delete'" | awk '/Com_delete/{print $2}')
$SUCCESS
echo -n "DELETE指令被查看的次数:"
echo $NUM_DELETE
$NORMAL
 
# 查看INSERT指令被查看的次数
NUM_INSERT=$($MYSQL_COMM "SHOW GLOBAL STATUS LIKE 'com_insert'" | awk '/Com_insert/{print $2}')
$SUCCESS
echo -n "INSERT指令被查看的次数:"
echo $NUM_INSERT
$NORMAL
 
# 查看COMMIT指令被查看的次数
NUM_COMMIT=$($MYSQL_COMM "SHOW GLOBAL STATUS LIKE 'com_commit'" | awk '/Com_commit/{print $2}')
$SUCCESS
echo -n "COMMIT指令被查看的次数:"
echo $NUM_COMMIT
$NORMAL
 
# 查看ROLLBACK指令被查看的次数
NUM_ROLLBACK=$($MYSQL_COMM "SHOW GLOBAL STATUS LIKE 'com_rollback'" | awk '/Com_rollback/{print $2}')
$SUCCESS
echo -n "ROLLBACK指令被查看的次数:"
echo $NUM_ROLLBACK
$NORMAL
 
# 查看服务器总指令数
NUM_QUESTION=$($MYSQL_COMM "SHOW GLOBAL STATUS LIKE 'Questions'" | awk '/Questions/{print $2}')
$SUCCESS
echo -n "QUESTION服务器总指令次数:"
echo $NUM_QUESTION
$NORMAL
 
# 查看慢查询数
NUM_SLOW_QUERY=$($MYSQL_COMM "SHOW GLOBAL STATUS LIKE 'slow_queries'" | awk '/Slow_queries/{print $2}')
$SUCCESS
echo -n "SLOW_QUERY慢查询数:"
echo $NUM_SLOW_QUERY
$NORMAL
 
# 数据库QPS
echo -n "数据库QPS:"
$SUCCESS
awk 'BEGIN{print '"$NUM_QUESTION/$RUN_TIME"'}'
$NORMAL
 
# 数据库TPS
echo -n "数据库TPS:"
$SUCCESS
awk 'BEGIN{print '"($NUM_COMMIT+$NUM_ROLLBACK)/$RUN_TIME"'}'
$NORMAL
}
mysql_test 2>/dev/null

2.数据备份脚本

#!/bin/bash
## 基本配置变量
USER="root"                # 输入用户
PORT=3306                  # 端口
PASSWORD="xxx"      # 密码
HOST="xxx.xxx.xxx.xxx"        # 数据库ip
DB_md=`mysql -u${USER} -p"${PASSWORD}" -h${HOST} -P${PORT} --default-character-set=utf8mb4 -e "show databases"|grep -Ev "^(Database|mysql|performance_schema|information_schema|sys)$"|xargs`  # 所有库名
TB="show tables"            # 查看表名
 
back (){
## 准备数据备份目录
if [ -d /data/sql-back ];then
   echo ""
else
   mkdir -p /data/sql-back
   ALL_LOG=/data/sql-back   # 备份目录
fi
if [ -d /data/sql-back/data ];then
   echo ""
else
   mkdir -p /data/sql-back/data    
   DATA_LOG=/data/sql-back/data   # 备份数据sql目录
fi
if [ -d /data/sql-back/structure ];then
   echo ""
else
   mkdir -p /data/sql-back/structure
   ST_LOG=/data/sql-back/structure   # 备份数据sql表结构目录
fi
 
 
 
## 准备哈希值           
if [ -e ${ALL_LOG}/count_sum.txt ];then
   touch count_sum.txt
else
   rm -rf  ${ALL_LOG}/count_sum.txt &&  touch ${ALL_LOG}/count_sum.txt
fi
# 所有库名
for i in ${DB_md}
do
    # 所有表名
    for j in `mysql -u$USER -p$PASSWORD -P$PORT -h$HOST -D $i --skip-column-names -e "$TB" `
    do
        # 统计表中的数据单元格数目
        su=`mysql -u$USER -p$PASSWORD -P$PORT -h$HOST -D $i --skip-column-names -e "select count(*) from $j"`
        echo -e ${i}:${j}:${su} >> ${ALL_LOG}/count_sum.txt
    done
    echo -e "\n++++++++++++++++++++++++++++++++" >> ${ALL_LOG}/count_sum.txt
done
 
# 备份全部数据库数据
echo "备份全部数据库数据"
/usr/local/mysql/bin/mysqldump -u${USER}  -p${PASSWORD} --default-character-set=utf8 --single-transaction --master-data=2 --all-databases > /data/sql-back/all.sql 
## 只备份数据结构
echo "只备份数据结构"
for s in ${DB_md}
do
     /usr/local/mysql/bin/mysqldump -u${USER}  -p${PASSWORD} --default-character-set=utf8 --set-gtid-purged=OFF --hex-blob --no-data --skip-lock-tables --complete-insert --skip-tz-utc --compact --databases ${s} > $ST_LOG/$s.sql 
done
 
## 只备份数据
echo "只备份数据"
for d in ${DB_md}
do
     /usr/local/mysql/bin/mysqldump -u${USER}  -p${PASSWORD} --default-character-set=utf8 --set-gtid-purged=OFF --hex-blob --no-create-info --skip-lock-tables --complete-insert --skip-tz-utc --compact --databases ${d} > $DATA_LOG/$d.sql 
done
}
back 2> /dev/null

三.备份参数概述

1.设置导出字符集

如果不指定，mysqldump默认使用UTF8字符集进行导出。

2.GTID特性介绍

MySQL 5.6引入了GTID特性，因此5.6版本的mysqldump工具增加了set-gtid-purged选项。

说明

• ON：在mysqldump输出中包含SET @@GLOBAL.GTID_PURGED语句。

• OFF：在mysqldump输出中不包含 SET @@GLOBAL.GTID_PURGED 语句。

• AUTO：默认值。对于启用了GTID的实例，会输出 SET @@GLOBAL.GTID_PURGED语句；对于没有启动或者不支持GTID的实例，不输出任何GTID相关信息。

因此对于使用MySQL 5.6及以上版本自带的mysqldump工具导出RDS MySQL 5.5版本实例数据时，需要设置set-gtid-purged为OFF，否则会报以下错误：

Error: Server has GTIDs disabled.
或者
mysqldump: Couldn’t execute ‘SELECT @@GTID_MODE’: Unknown system variable ‘GTID_MODE’ <1193>

3.其他导出时需要注意的选项

4.避免表级锁等待