#!/bin/bash
# 要备份的数据库名,多个数据库用空格分开
databases=(db1 db2 db3)
# 备份文件要保存的目录
basepath='/root/backup/mysql/'
if [ ! -d "$basepath" ]; then
mkdir -p "$basepath"
fi
# 循环databases数组
for db in ${databases[*]}
do
# 备份数据库生成SQL文件
/bin/nice -n 19 /usr/bin/mysqldump -uUSER -pPASSWORD --database $db > $basepath$db-$(date +%Y%m%d).sql
# 将生成的SQL文件压缩
/bin/nice -n 19 tar zPcf $basepath$db-$(date +%Y%m%d).sql.tar.gz $basepath$db-$(date +%Y%m%d).sql
# 删除7天之前的备份数据
find $basepath -mtime +7 -name "*.sql.tar.gz" -exec rm -rf {} \;
done
# 删除生成的SQL文件
rm -rf $basepath/*.sql
#!/bin/bash
databases=(test mysql)
basepath='/data/db_bk/'
if [ ! -d "$basepath" ]; then
mkdir -p "$basepath"
fi
for db in ${databases[*]}
do
/usr/bin/nice -n 19 /usr/local/mysql/bin/mysqldump -uroot -ppaSlave --database $db > $basepath$db-$(date +%Y%m%d).sql
/usr/bin/nice -n 19 tar -czf $basepath$db-$(date +%Y%m%d).sql.tar.gz $basepath$db-$(date +%Y%m%d).sql
find $basepath -mtime +1 -name "*.sql.tar.gz" -exec rm -rf {} \;
done
rm -rf $basepath/*.sql
~
mysql备份并把备份集传到ceph上。
mkdir -p /data/shell
mkdir -p /data/far_backup
cat >> /data/shell/mysql_bk.sh <<EOF
#!/bin/bash
source ~/.bash_profile
IFS=$‘\n’
Back_dir=/data/db_backup
for line in cat /data/shell/conn_info.txt
do
User=echo $line | awk '{print $2}'
Pass=echo $line | awk '{print $3}'
Host=echo $line | awk '{print $1}'
#Port=echo $line | awk '{print $5}'
Database=echo $line | awk '{print $4}'
for i in $Database
do
mkdir -p
B
a
c
k
d
i
r
/
Back_dir/
Backdir/Database
/usr/bin/mysqldump -h
H
o
s
t
−
u
Host -u
Host−uUser -p$Pass -E -R --triggers --master-data=2 --single-transaction --max_allowed_packet=128M
D
a
t
a
b
a
s
e
∣
g
z
i
p
−
9
>
Database | gzip -9 >
Database∣gzip−9>Back_dir/
D
a
t
a
b
a
s
e
/
Database/
Database/{Database}fulldate +%F
.sql.gz
done
done
find $Back_dir/ -mtime +6 -type f -name “*.gz” >> $Back_dir/delete_list.log
RN=cat $Back_dir/delete_list.log |wc -l
if [ $RN -gt 0 ]; then
find $Back_dir/ -name “*.gz” -type f -mtime +6 -exec rm -f {} ;
else
exitcd
fi
rm -f $Back_dir/delete_list.log
EOF
cat >> /data/shell/cp_far.sh << EOF
#!/bin/bash
mount -t ceph xx.xx.xx.xx:6789,xxx:6789,xxx:6789:/dba /data/far_backup -o name=dba,secret=x==
rsync -aruvzP --bwlimit=80000 /data/db_backup/* /data/far_backup
umount /data/far_backup
EOF
cat >> /data/shell/conn_info.txt <<EOF
ip root xxx dbname
EOF
chmod +x /data/shell/*.sh
GRANT all ON . TO ‘mysqlmo’@‘xx’ identified by ‘mysqlmo’ WITH MAX_USER_CONNECTIONS 3;
CREATE USER ‘mysqlmo’@‘xx’ IDENTIFIED BY ‘mysqlmo’ ;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON . TO ‘mysqlmo’@‘10.20.13.17’ identified by ‘mysqlmo’;
update mysql.user set max_connections=3 where user=‘mysqlmo’;
flush privileges;
select user,max_connections from mysql.user;
yum install -y psmisc rsync lsof
lsof |grep /data/far_backup/