Mysql 备份shell并传到ceph上

#!/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 $Bac{k}_{d}ir/$Database
/usr/bin/mysqldump -h $Host-u$User -p$Pass -E -R --triggers --master-data=2 --single-transaction --max_allowed_packet=128M $Database|gzip-9>$Back_dir/$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/