备份内容:对MySQL服务器中的studydb,coursedb的数据库进行远程备份,要求每天
凌晨2:30执行。每个库备份为独立的.sql的文件,然后压缩为.tgz格式.文件名中嵌入
执行备份的日期和时刻。
1,备机和主机之间要连接畅通,不能有防火墙的阻拦。
2,MySQL服务器必须允许从备份主机远程访问,并且得授权远程访问的用户能够查询
studydb,coursedb库。
operatorgrants--> select lock
MySQL-Master:
[root@localhost ~]# mysql -uroot -p123456
mysql> grant select,lock tables on studydb.* to 'operator'@'192.168.1.2' identified by 'pwd123';
Query OK, 0 rows affected (0.00 sec)
mysql> grant select,lock tables on coursedb.* to 'operator'@'192.168.1.2' identified by 'pwd123';
Query OK, 0 rows affected (0.00 sec)
MySQL-Backup:
创建用来存放备份文件的目录(如:/opt/mysql_dbbak)
编写备份脚本:mysql_dbbak.sh
设定周期任务计划: crontab -e
[root@localhost ~]# vi /opt/mysql_dbbak.sh
#!/bin/bash
#This is a mysql backup scripts.
#-------------Define Mysql link and destination base informations---#
MY_USER="operator"
MY_PASS="pwd123"
MY_HOST="192.168.1.1"
MY_CONN="-u$MY_USER -p$MY_PASS -h$MY_HOST"
MY_DB1="studydb"
MY_DB2="coursedb"
#------------Define the Directory,tools,time,file name of backup---------#
[ -d /opt/mysql_dbbak ] || mkdir -p /opt/mysql_dbbak
BF_DIR="/opt/mysql_dbbak"
BF_CMD="/usr/bin/mysqldump"
BF_TIME=$(date +%Y%m%d-%H%M%S)
NAME_1="$MY_DB1-$BF_TIME"
NAME_2="$MY_DB2-$BF_TIME"
#----------export .sql scripts,then to compress(and remove source files)---#
cd $BF_DIR
$BF_CMD $MY_CONN --databases $MY_DB1 > $NAME_1.sql
$BF_CMD $MY_CONN --databases $MY_DB2 > $NAME_2.sql
/bin/tar zcf $NAME_1.tgz $NAME_1.sql --remove &> /dev/null
/bin/tar zcf $NAME_2.tgz $NAME_2.sql --remove &> /dev/null
[root@localhost ~]# crontab -e
*/2****/opt/mysql_dbbak.sh
转载于:https://blog.51cto.com/only013/1344182