dnet服务器销售,备份MySQL/MariaDB的权限信息，并推送到远端备份中心服务器

记得Percona Toolkit中有相应的工具，没有具体研究。本文使用bash脚本实现权限信息导出为SQL语句，并推送到远程服务器。

前提

远程服务器，这个一个逻辑概念，实际处理是通过SSH通道进行数据传输，所以也可以是127.0.0.1这样的本机地址。因此我们需要打通SSH互信，在MySQL/MariaDB创建相应用户，具体参考另一篇文章：一个脚本实现全量增量备份，并推送到远端备份中心服务器

实现

废话不多说，直接上代码：

#!/bin/bash

export PATH=/usr/sbin:/usr/bin:/bin:/sbin:${PATH}

# 显示使用帮助

usage () {

echo "Usage: ${0} [OPTION...]"

echo " -H, --help This option displays a help screen and exits."

echo " -u, --user=name This option specifies the MySQL username used when"

echo " connecting to the server, if that's not the current user."

echo " The option accepts a string argument. See mysql --help"

echo " for details."

echo " -h, --host=name This option specifies the host to use when connecting to"

echo " the database server with TCP/IP. The option accepts a"

echo " string argument. See mysql --help for details."

echo " -P, --port=# This option specifies the port to use when connecting to"

echo " the database server with TCP/IP. The option accepts a"

echo " string argument. See mysql --help for details."

echo " -p, --password=name This option specifies the password to use when connecting"

echo " to the database. It accepts a string argument. See mysql"

echo " --help for details."

echo " -S, --socket=name This option specifies the socket to use when connecting"

echo " to the local database server with a UNIX domain socket."

echo " The option accepts a string argument. See mysql --help"

echo " for details."

echo " -R, --remote-server The remote server with SSH where you want to put the backup"

echo " file into."

echo " -D, --remote-dir The backup directory on remote server"

echo " -v, --version Output version information and exit."

}

# 读取命令后参数

OPTS=`getopt -o Hvh:P:u:p:S:R:D: --long help,version,host:,port:,user:,password:,socket:,remote-server:,remote-dir: -n 'parse-options' -- "$@"`

if [ $? != 0 ]

then

exit 1

fi

eval set -- "$OPTS"

# 参数默认值设定

HELP=0

VERSION=0

MYSQL_HOST='127.0.0.1'

MYSQL_PORT=3306

MYSQL_USER='backup'

MYSQL_PASS='changeme'

MYSQL_SOCK=''

REMOTE_SERVER=''

REMOTE_DIR=''

LOCAL_DATE=$(date +'%Y_%m_%d_%H_%M')

LOCAL_FILE="/var/tmp/privileges_${LOCAL_DATE}.sql"

# 参数赋值

while true

do

case "$1" in

-H | --help ) HELP=1; break ;; # 显示帮助信息，无需解析更多参数直接退出

-v | --version ) VERSION=1; break ;; # 显示版本信息，无需解析更多参数直接退出

-h | --host ) MYSQL_HOST=$2; shift 2 ;; # 备份的主机，默认localhost

-P | --port ) MYSQL_PORT=$2; shift 2 ;; # 服务端口，默认3306

-u | --user ) MYSQL_USER=$2; shift 2 ;; # 登录用户，默认backup

-p | --password ) MYSQL_PASS=$2; shift 2 ;; # 登录密码

-S | --socket ) MYSQL_SOCK=$2; shift 2 ;; # 嵌套字文件位置

-R | --remote-server ) REMOTE_SERVER=$2; shift 2 ;; # 远程服务器信息，比如root@10.0.0.1

-D | --remote-dir ) REMOTE_DIR=$2; shift 2 ;; # 远程备份路径，比如/data/backup

-- ) shift; break ;;

* ) break ;;

esac

done

# 显示版本

if [[ $VERSION -eq 1 ]]

then

echo "MySQL Privileges Backup v1.0.0"

exit 0

fi

# 显示帮助

if [[ $HELP -eq 1 ]]

then

usage

exit 0

fi

# 对参数进行判断，如果没有提供则报错并退出

if ! [ -n "${REMOTE_SERVER}" ]

then

echo "Please specify the action you want to run with -R or --remote-server"

exit 1

fi

if ! [ -n "${REMOTE_DIR}" ]

then

echo "Please specify the action you want to run with -D or --remote-dir"

exit 1

fi

# 判断有效日期，暂时没用

:<

case ${M} in

[13578]|10|12) days=31 ;;

[469]|11) days=30 ;;

*) days=29 ;;

ecas

!

while read row

do

[ -z "$row" ] && break

IFS="$(echo -e '\t')" read Host User <<< "${row}"

mysql -N -h${MYSQL_HOST} -u${MYSQL_USER} -P${MYSQL_PORT} -p${MYSQL_PASS} --database=mysql -e "SHOW GRANTS FOR ${User}@'${Host}';" | while read line

do

echo "${line};" >> ${LOCAL_FILE}

done

done << EOF

$(mysql -N -h${MYSQL_HOST} -u${MYSQL_USER} -P${MYSQL_PORT} -p${MYSQL_PASS} --database=mysql -e "SELECT Host,User FROM mysql.user ORDER BY User;")

EOF

if [ -f "${LOCAL_FILE}" ]

then

cat ${LOCAL_FILE} | ssh ${REMOTE_SERVER} "cat - > ${REMOTE_DIR}_${LOCAL_DATE}.sql"

rm -rf ${LOCAL_FILE}

fi

exit 0

代码比较简单，主要是一段while循环，就不做过多解释了。

使用方法

可是使用backup-privileges.sh -H查看使用帮助

# ./backup-privileges.sh -H

Usage: ./backup-privileges.sh [OPTION...]

-H, --help This option displays a help screen and exits.

-u, --user=name This option specifies the MySQL username used when

connecting to the server, if that's not the current user.

The option accepts a string argument. See mysql --help

for details.

-h, --host=name This option specifies the host to use when connecting to

the database server with TCP/IP. The option accepts a

string argument. See mysql --help for details.

-P, --port=# This option specifies the port to use when connecting to

the database server with TCP/IP. The option accepts a

string argument. See mysql --help for details.

-p, --password=name This option specifies the password to use when connecting

to the database. It accepts a string argument. See mysql

--help for details.

-S, --socket=name This option specifies the socket to use when connecting

to the local database server with a UNIX domain socket.

The option accepts a string argument. See mysql --help

for details.

-R, --remote-server The remote server with SSH where you want to put the backup

file into.

-D, --remote-dir The backup directory on remote server

-v, --version Output version information and exit.

#

#

# ./backup-privileges.sh -v

MySQL Privileges Backup v1.0.0

-EOF-