定时备份mysql数据库的shell脚本
2024-09-06 08:39: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
最新文章
- salt yum安装lamp
- 修改mongodb3.0副本集用户密码遇到的坑
- C语言第4天循环,流程控制。
- 小结JS中的OOP(上)
- linux下查找文件和文件内容
- CSS中overflow:hidden
- DevOps之软件定义网络SDN
- Python连接MySQL数据库中各种坑
- 本机是wifi,虚拟机无法连接外网问题
- virtural machine eth1
- [HDFS_add_1] HDFS 启动过程分析
- 29.html5 移动端开发总结
- 系统批量运维管理器Fabric详解
- Python3.6.2在线安装pymysql模块
- 查看linux的IO占用
- 企业应用--Nginx&;web部署
- LFI/RFI总结
- XenServer DVSC
- ios真机连接不上记录,再次执行脚本说找不到真机的解决
- tcpdf