最近项目需要定时备份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" >&2 exit 2 fi username="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" 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" >&2 exit 2 fi ;; *) echo "$now error option there is only permmit -h host,-P port -u user,-p password ,-f fileUrl,-n dbName" >&2 exit 2 ;; 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 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_url done else echo "$now mysql connection error" >&2 exit 2 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 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/ total 22368 -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]# cat success.log =============================2020-02-24_19:36:01================================= begin to backup mysql at : 2020-02-24_19:36:01 begin 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:03 begin 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:30 begin 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:41 begin 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]# 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: 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