使用mysqldump实现分库分表备份-shell脚本解析

介绍：

分库分表备份多用于有多个数据库，表的备份，便于维护和数据恢复，缺点是文件多且碎

mysqldump工具介绍：

mysqldump命令是MySQL数据库自带的一个备份命令，它支持数据库全备也可以指定库进行备份，它备份的

基本命令的使用

（1）mysqldump语法格式

mysqldump  [options] –u 用户名 -p 密码 数据库名  >  备份的文件

文件以文件形式备份，且文件内容都是以sql语句形式存在。

（2）mysqldump的简单选项

-A    备份所有数据库

-B    表示指定备份多个库，同时执行了use db和create databases db命令

-F    刷新binlog日志

-d    只备份表结构

-t     只备份表数据

-l     指定表上锁，让用户不能访问写入数据库指定表的数据

-x    所有表上锁

编程思路：

利用mysqldump -uroot -p db1 >db1.sql语句完成自动备份

1.获取数据库名称并生成对应库名的备份文件

mysql -uroot -p123456 -e "show databases"| grep -Evi "database|information_schema|performance_schema" | sed -r 's#^([a-z].*$)#mysqldump -uroot -p123456 --events -B \1 | gzip >/root/back/\1.sql.gz#g'|sh

解析：

mysql -uroot -p123456 -e部分为root用户使用密码123456连接到数据库

"show databases"部分为执行命令得到所有数据库，如下

grep -Evi "database|information_schema|performance_schema"部分为过滤包含"database"、"information_schema"和"performance_schema"的行也就是不备份系统库

-E：启用扩展的正则表达式匹配模式。

-v：反转匹配，只显示不匹配的行。

-i：忽略大小写

sed -r 's#^([a-z].*$)#mysqldump -uroot -p123456 --events -B \1   部分为使用sed命令将每行数据转换为mysqldump命令

上文提到过  -B  表示指定备份多个库，同时执行了use db和create databases db命令

^([a-z].*$)：匹配以小写字母开头的行

\1：表示正则表达式中的第一个捕获组，即数据库名

至此通过管道符组成mysqldump命令备份了所有非系统数据库

gzip >/root/back/\1.sql.gz#g'   部分为将上步命令生成的数据库备份文件压缩并放到/root/back/目录下并命名为原本数据库名称

|sh   将上一步的输出通过管道传递给sh命令，执行上命令

2.备份库：

#!/bin/bash

MYUSER=root

MYPASSWD=123456

BACKDIR="/root/back"

MYHOST='192.168.80.142'

MYCMD="mysql -u$MYUSER -p$MYPASSWD  -h$MYHOST"

MYDUMP="mysqldump -u$MYUSER -p$MYPASSWD  -h$MYHOST -x -E -B -F -R "

DBLIST=`$MYCMD   -e "show databases;"|grep -Evi "database|_schema|mysql"`

[ ! -d $BACKDIR ] && mkdir $BACKDIR

for dbname in $DBLIST

do

    $MYDUMP $dbname|gzip > $BACKDIR/${dbname}_$(date +%F).sql.gz

done

解析：

MYUSER=root

MYPASSWD=123456

BACKDIR="/root/back"

MYHOST='192.168.80.142'

MYCMD="mysql -u$MYUSER -p$MYPASSWD  -h$MYHOST"

这部分完成登录mysql

BACKDIR="/root/back"：将备份文件保存目录设置为/root/back

MYHOST='192.168.80.142'：将数据库主机地址设置为192.168.80.142

MYCMD="mysql -u$MYUSER -p$MYPASSWD -h$MYHOST"：将MySQL命令设置为连接到指定主机的命令

MYDUMP="mysqldump -u$MYUSER -p$MYPASSWD -h$MYHOST -x -E -B -F -R "：将mysqldump命令设置为备份数据库的命令选项意为

-x：跳过锁定表。

-E：包括事件。

-B：备份所有数据库。

-F：强制创建视图。

-R：包括存储过程和函数。

DBLIST=$MYCMD -e "show databases;"|grep -Evi "database|_schema|mysql"`：执行MySQL命令，获取所有数据库的列表，并使用grep命令进行过滤。过滤掉了名称为"database"、以"_schema"结尾或包含"mysql"的数据库。

[ ! -d $BACKDIR ] && mkdir $BACKDIR：检查备份目录是否存在，如果不存在则创建该目录。

for dbname in $DBLIST：遍历数据库列表。

$MYDUMP $dbname|gzip > $BACKDIR/${dbname}_$(date +%F).sql.gz：执行备份命令，并将备份文件以压缩格式保存到指定目录。备份文件的命名格式为"数据库名_日期.sql.gz"，其中日期的格式为YYYY-MM-DD。

2.备份表：

#!/bin/bash
MYUSER=root
MYPASSWD=123456
BACKDIR="/root/back"
MYHOST='192.168.80.142'
MYCMD="mysql -u$MYUSER -p$MYPASSWD  -h$MYHOST"
MYDUMP="mysqldump -u$MYUSER -p$MYPASSWD  -h$MYHOST -x "
DBLIST=`$MYCMD   -e "show databases;"|grep -Evi "database|_schema|mysql"`
[ ! -d $BACKDIR ] && mkdir $BACKDIR
for dbname in $DBLIST 
do 
    TLIST=`$MYCMD -e "show tables from $dbname;" |sed 1d`
    for tname in $TLIST
    do
      mkdir -p $BACKDIR/$dbname
      $MYDUMP $dbname $tname|gzip > $BACKDIR/${dbname}/${dbname}_${tname}_$(date +%F).sql.gz 
    done
done

解析：

前段为登录，上文解释过

MYDUMP="mysqldump -u$MYUSER -p$MYPASSWD -h$MYHOST -x "：将mysqldump命令设置为备份数据库的命令

-x：跳过锁定表。

DBLIST=$MYCMD -e "show databases;"|grep -Evi "database|_schema|mysql"`：执行MySQL命令，获取所有数据库的列表，并使用grep命令进行过滤。过滤掉了名称为"database"、以"_schema"结尾或包含"mysql"的数据库。

[ ! -d $BACKDIR ] && mkdir $BACKDIR：检查备份目录是否存在，如果不存在则创建该目录。

for dbname in $DBLIST：遍历数据库列表。

TLIST=`$MYCMD -e "show tables from $dbname;" |sed 1d`：执行MySQL命令，获取指定数据库中的所有表的列表，并使用sed命令删除第一行的表头信息。

for tname in $TLIST：遍历表列表。

mkdir -p $BACKDIR/$dbname：创建数据库目录，如果目录已存在则不会报错。

$MYDUMP $dbname $tname|gzip > $BACKDIR/${dbname}/${dbname}_${tname}_$(date +%F).sql.gz：执行备份命令，并将备份文件以压缩格式保存到指定目录。备份文件的命名格式为"数据库名/数据库名_表名_日期.sql.gz"，其中日期的格式为YYYY-MM-DD。