MySQL备份

来源：互联网发布：淘宝详情页怎么做编辑：程序博客网时间：2024/04/27 21:19

1. 准备工作

1) 创建用户并授权

grant all on *.* to 'xtrabackup'@'localhost' identified by '123456'

2) 下载XtraBackup
wget http://www.percona.com/downloads/XtraBackup/XtraBackup-2.1.9/binary/Linux/x86_64/percona-xtrabackup-2.1.9-744-Linux-x86_64.tar.gz

3) 解压缩XtraBackup

tar –xvf percona-xtrabackup-2.1.9-744-Linux-x86_64.tar.gz mv percona-xtrabackup-2.1.9-744-Linux-x86_64 /usr/local/xtrabackupchown –R mysql.mysql /usr/local/xtrabackupcd /usr/binln -s /usr/local/xtrabackup/bin/innobackupexln -s /usr/local/xtrabackup/bin/xtrabackup_55ln -s /usr/local/xtrabackup/bin/xtrabackup_56ln -s /usr/local/xtrabackup/bin/xtrabackup

4)设置/etc/my.cnf

datadir=/usr/local/mysql/data/

5)安装依赖软件

yum -y  install perl-Time-HiRes yum -y install perl-DBD-MySQL

2. 物理备份脚本innobackup.sh

用today=`date +u` 整除7 判断是星期几，如果为0，周日全备，其它增量备份。如果是周一，已全备为基础增量备份，如果是其它，则以上一个增量备份为基础增量备份。同一个日期只保留一个备份，用crontab可实现自动备份。

#!/bin/shall_dir=/mysqlbackup/allinc_dir=/mysqlbackup/incUSER=xtrabackupPASSWORD=123456today=`date +u`today2=`date +%Y-%m-%d`today3=`date +%Y%m%d`yesterday=`expr $today3 - 1`if [ $(($today%7)) -eq 0 ]; then  rm -rf $all_dir/$today3 2>> $all_dir/all.log  innobackupex --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=$USER --password=$PASSWORD $all_dir 2>> $all_dir/all.log  mv $all_dir/$today2* $all_dir/$today3elif [ $(($today%7)) -eq 1 ]; then  rm -rf $inc_dir/$today3 2>> $inc_dir/increment.log    innobackupex --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=$USER --password=$PASSWORD --incremental $inc_dir --incremental-basedir=$all_dir/$yesterday 2>> $inc_dir/increment.log  mv $inc_dir/$today2* $inc_dir/$today3else rm -rf $inc_dir/$today3 2>> $inc_dir/increment.log innobackupex --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=$USER --password=$PASSWORD --incremental $inc_dir --incremental-basedir=$inc_dir/$yesterday 2>> $inc_dir/increment.log mv $inc_dir/$today2* $inc_dir/$today3 fi

3. 逻辑备份脚本dumpbackup.sh

用mysqldump实现，删除前15天的备份。

#!/bin/shUSER=xtrabackupPASSWORD=123456dump_dir=/mysqlbackup/dumptoday=`date +%Y%m%d`old_day=`expr $today - 15`    for db in `mysql -u$USER -p$PASSWORD -N -e "show databases"|grep -v -E "(percona|information_schema|performance_schema)"`do  chattr -i $dump_dir/*    #rm -f $dump_dir/*$old_day.sql* &>/dev/null       filename=${db}_${today}  mysqldump -u$USER -p$PASSWORD --events --routines  $db |gzip > $dump_dir/${filename}.sql.gz      chattr +i $dump_dir/*   done

0 0