一键备份单个或多个数据库（脚本）

一、先创建数据库，下面是远程创建数据库的脚本

#!/bin/bash MYSQL_DIR=/usr/local/mariadb/bin for databases in $* do ${MYSQL_DIR}/mysql -h 192.168.1.21 -u root -p123456 -e "create database ${databases};" done echo "查看192.168.1.21机器所有数据库" ${MYSQL_DIR}/mysql -h 192.168.1.21 -u root -p123456 -e "show databases;"

执行后结果

[root@localhost src]# ./auto_createDB.sh test1 test2 test3 test4 test5 查看192.168.1.21机器所有数据库 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | test1 | | test2 | | test3 | | test4 | | test5 | +--------------------+

二、备份远程数据库，单个或多个数据库

#!/bin/bash MYSQL_DIR=/usr/local/mariadb/bin DATE=`date +%Y%m%d%H%M` if (( $# < 1 ));then echo "请输入要备份的数据库名" exit 1 fi for database in $* do ${MYSQL_DIR}/mysqldump -h 192.168.1.21 -ubackup -p123456 -B ${database}>${database}${DATE}.sql if [ $? -ne 0 ];then echo "数据库${database}备份失败，请检查" exit 1 else echo "数据库${database}备份成功！" fi done

执行结果

[root@localhost src]# ./auto_bakDB.sh test1 test2 test3 test4 数据库test1备份成功！ 数据库test2备份成功！ 数据库test3备份成功！ 数据库test4备份成功！ [root@localhost src]# ls test* test1202007041953.sql test2202007041953.sql test3202007041953.sql test4202007041953.sql

注：备份数据时需要添加备份权限，见下

创建备份用户及授权 >grant select,lock tables on *.* to "backup"@"192.168.1.%" identified by "123456"; >flush privileges; >select Host,User,Password from mysql.user;

------------------------end