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

时间：2021-04-30 06:11:13

最近项目需要定时备份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=$(which mysqldump)

mysqlUrl=$(which mysql)

if [ -n $mysqlDumpurl] | [ -n mysqlUrl ]; then

    echo "cant't find mysql application" >&

    exit

fi

username="root"

dbName=""

mysql_host="localhost"

mysql_port=""

password=""

back_url="/bak/back_$now.sql"

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

    case "$opt" in

    h) mysql_host=$OPTARG ;;

    n) dbName=$OPTARG ;;

    P) mysql_port=$OPTARG ;;

    u) username=$OPTARG ;;

    p) password=$OPTARG ;;

    f)

        fileUrl=$OPTARG

        if [ -d $fileUrl ]; then

            if [[ $fileUrl == */ ]]; then

                back_url="$fileUrlback_$now.sql"

            else

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

            fi

        else

            echo "$fileUrl is not a directory" >&

            exit

        fi

        ;;

    *)

        echo "$now error option there is only permmit -h host,-P port -u user,-p password ,-f fileUrl,-n dbName" >&

        exit

        ;;

    esac

done

result=""

if [[ -n $dbName ]]; then

    result=$dbName

else

    result=$($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  ]; 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_url

    done

else

    echo "$now mysql connection error" >&

    exit

fi

end=$(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  -u root -p  -n bz  -f /bak/test >>/root/mysql_bak/error.log  >> /root/mysql_bak/success.log

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

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

　　然后加上如下任务

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

　　查看备份文件

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

total

-rw-r--r--  root root   Feb  : back_2020--24_19::.sql

-rw-r--r--  root root   Feb  : back_2020--24_19::.sql

-rw-r--r--  root root   Feb  : back_2020--24_19::.sql

-rw-r--r--  root root   Feb  : back_2020--24_19::.sql

-rw-r--r--  root root  Feb  : back_2020--24_19::.sql

-rw-r--r--  root root  Feb  : back_2020--24_19::.sql

-rw-r--r--  root root  Feb  : back_2020--24_19::.sql

-rw-r--r--  root root  Feb  : back_2020--24_19::.sql

-rw-r--r--  root root  Feb  : back_2020--24_19::.sql

-rw-r--r--  root root  Feb  : back_2020--24_19::.sql

-rw-r--r--  root root   Feb  : back_2020--24_19::.sql

-rw-r--r--  root root   Feb  : back_2020--24_19::.sql

-rw-r--r--  root root   Feb  : back_2020--24_19::.sql

-rw-r--r--  root root        Feb  : back_2020--24_19::.sql

-rw-r--r--  root root   Feb  : back_2020--24_19::.sql

-rw-r--r--  root root        Feb  : back_2020--24_19::.sql

　　查看日志

[root@db-mysql mysql_bak]# cat success.log

=============================--24_19::=================================

begin to backup mysql at : --24_19::

begin to backup database : bz

end to backup mysql at : --24_19::

=============================--24_19::=================================

=============================--24_19::=================================

begin to backup mysql at : --24_19::

begin to backup database : bz

end to backup mysql at : --24_19::

=============================--24_19::=================================

=============================--24_19::=================================

begin to backup mysql at : --24_19::

begin to backup database : bz

end to backup mysql at : --24_19::

=============================--24_19::=================================

=============================--24_19::=================================

begin to backup mysql at : --24_19::

begin to backup database : bz

end to backup mysql at : --24_19::

=============================--24_19::=================================

　

[root@db-mysql mysql_bak]# cat error.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: : 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: : Access denied for user 'root'@'localhost' (using password: YES) when trying to connect

相关文章