mysql 备份库的shell_定时备份mysql数据库的shell脚本

最近项目需要定时备份mysql数据库的数据，根据需求写了一份定时备份mysql数据库的脚本。在这儿记一下以后要用了可以直接拿来用

-h mysql的地址  默认为localhost

-P 端口号  默认为3306

-u 用户  默认为root

-p 密码 默认为123456

-f  备份存放地址  默认为 /bak 下面

-n 指定数据库  默认为所有数据库(除开mysql系统自带数据库)

#!/bin/bash

now=$(date "+%Y-%m-%d_%H:%M:%S")echo "=============================$now================================="

echo "begin to backup mysql at : $now"mysqlDumpurl=$(whichmysqldump)

mysqlUrl=$(whichmysql)if [ -n $mysqlDumpurl] | [ -n mysqlUrl ]; then

echo "cant't find mysql application" >&2exit2

fiusername="root"dbName=""mysql_host="localhost"mysql_port="3306"password="123456"back_url="/bak/back_$now.sql"

while getopts h:P:u:p:f:n: opt; do

case "$opt" inh) mysql_host=$OPTARG ;;

n) dbName=$OPTARG ;;

P) mysql_port=$OPTARG ;;

u) username=$OPTARG ;;

p) password=$OPTARG ;;

f)

fileUrl=$OPTARGif [ -d $fileUrl ]; then

if [[ $fileUrl == */ ]]; thenback_url="$fileUrlback_$now.sql"

elseback_url="$fileUrl/back_$now.sql"

fi

else

echo "$fileUrl is not a directory" >&2exit2

fi;;*)echo "$now error option there is only permmit -h host,-P port -u user,-p password ,-f fileUrl,-n dbName" >&2exit2;;esac

doneresult=""

if [[ -n $dbName ]]; thenresult=$dbNameelseresult=$($mysqlUrl -h$mysql_host -P$mysql_port -u$username -p$password -e 'show databases' | grep -v 'Warning\|Database\|information_schema\|performance_schema\|sys\|mysql')fi

if [ $? -eq 0 ]; then

for db in $result; do

echo "begin to backup database : $db"$mysqlDumpurl-h$mysql_host -P$mysql_port -u $username -p$password $db >>$back_urldone

else

echo "$now mysql connection error" >&2exit2

fiend=$(date "+%Y-%m-%d_%H:%M:%S")echo "end to backup mysql at : $end"

echo "=============================$end================================="

例如指令如下  即可立刻进行备份

sh /root/mysql_bak/mysqlbak.sh -h 192.168.0.1 -P 3306 -u root -p 123456 -n bz -f /bak/test 2>>/root/mysql_bak/error.log 1>> /root/mysql_bak/success.log

也可以放在linux上定时执行即可，例如每天下午7点半执行的话

[root@db-mysql mysql_bak]# crontab -e

然后加上如下任务

30 19 * * * /root/mysql_bak/mysqlbak.sh -h 192.168.0.1 -P 3306 -u root -p 123456 -n bz -f /bak/test 2>>/root/mysql_bak/error.log 1>> /root/mysql_bak/success.log

查看备份文件

[root@db-mysql mysql_bak]# ll /bak/test/total22368

-rw-r--r-- 1 root root 823238 Feb 24 19:04 back_2020-02-24_19:04:48.sql-rw-r--r-- 1 root root 823238 Feb 24 19:06 back_2020-02-24_19:06:29.sql-rw-r--r-- 1 root root 823238 Feb 24 19:06 back_2020-02-24_19:06:50.sql-rw-r--r-- 1 root root 134459 Feb 24 19:07 back_2020-02-24_19:07:29.sql-rw-r--r-- 1 root root 2833376 Feb 24 19:08 back_2020-02-24_19:08:12.sql-rw-r--r-- 1 root root 2833376 Feb 24 19:10 back_2020-02-24_19:10:06.sql-rw-r--r-- 1 root root 2833376 Feb 24 19:12 back_2020-02-24_19:12:01.sql-rw-r--r-- 1 root root 2833376 Feb 24 19:13 back_2020-02-24_19:13:19.sql-rw-r--r-- 1 root root 2833376 Feb 24 19:13 back_2020-02-24_19:13:38.sql-rw-r--r-- 1 root root 2833376 Feb 24 19:14 back_2020-02-24_19:14:20.sql-rw-r--r-- 1 root root 823242 Feb 24 19:14 back_2020-02-24_19:14:42.sql-rw-r--r-- 1 root root 823242 Feb 24 19:32 back_2020-02-24_19:32:29.sql-rw-r--r-- 1 root root 823242 Feb 24 19:36 back_2020-02-24_19:36:01.sql-rw-r--r-- 1 root root 0 Feb 24 19:45 back_2020-02-24_19:45:03.sql-rw-r--r-- 1 root root 823238 Feb 24 19:46 back_2020-02-24_19:46:30.sql-rw-r--r-- 1 root root 0 Feb 24 19:46 back_2020-02-24_19:46:41.sql

查看日志

[root@db-mysql mysql_bak]# catsuccess.log=============================2020-02-24_19:36:01=================================begin to backup mysql at :2020-02-24_19:36:01begin to backup database : bz

end to backup mysql at :2020-02-24_19:36:01

=============================2020-02-24_19:36:01=================================

=============================2020-02-24_19:45:03=================================begin to backup mysql at :2020-02-24_19:45:03begin to backup database : bz

end to backup mysql at :2020-02-24_19:45:03

=============================2020-02-24_19:45:03=================================

=============================2020-02-24_19:46:30=================================begin to backup mysql at :2020-02-24_19:46:30begin to backup database : bz

end to backup mysql at :2020-02-24_19:46:30

=============================2020-02-24_19:46:30=================================

=============================2020-02-24_19:46:41=================================begin to backup mysql at :2020-02-24_19:46:41begin to backup database : bz

end to backup mysql at :2020-02-24_19:46:41

=============================2020-02-24_19:46:41=================================

[root@db-mysql mysql_bak]# caterror.log

mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysqldump: [Warning] Using a password on the command line interface can be insecure.-bash: at: command not found-bash: at: command not found/bin/sh: root: command not found

mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysqldump: Got error:2003: Can't connect to MySQL server on'192.168.3.147'(111) when trying to connect

mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysqldump: [Warning] Using a password on the command line interface can be insecure.

mysqldump: Got error:1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect