备份前准备

创建单独用于备份的用户,并授予相应的权限

1
2
3
create user db_backup@'localhost' identified by 'backup123';
grant select,lock tables,show view,trigger,event on backup_database.* to db_backup@'localhost';
grant reload,Replication client on *.* to db_backup@'localhost';

方案1:全量逻辑备份(使用mysqldump,方案1)

备份

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
#!/bin/bash
backup_file_name=db_backup_`date '+%Y-%m-%d'`.sql.gz
mysqldump -udb_backup -pbackup123 --single-transaction --flush-logs --master-data=2 backup_database | gzip > $backup_file_name

if [ -e $backup_file_name ] ;then
   echo "Start to copy files"
#   scp $backup_file_name  lihu@ubuntu-vm-02:/home/lihu
else
   echo "No file to copy"
fi

额外的参数 --skip-add-drop-table:生成的备份文件中不要有drop table的语句,默认是有的。 --no-data:不导出数据,仅导出表结构 --no-create-info:不导出建表语句,仅导出数据

  • 恢复
  1. gzip -d fileName 解压备份文件
  2. 执行mysql -uusername -ppassword databasename < backupfile

这个恢复的速度比较慢。

方案2:利用binlog增量备份

  1. 先执行全量备份(计划执行)
  2. 增量复制binlog文件(计划执行)
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
#!/bin/bash
BakDir=/home/lihu/backup/
BinDir=/var/log/mysql                             
LogFile=/home/lihu/backup/bak.log
BinFile=/var/log/mysql/mysql-bin.index          

mysqladmin -uroot -proot flush-logs
Counter=`wc -l $BinFile |awk '{print $1}'`
NextNum=0
for file in `cat $BinFile`
do
    base=`basename $file`
    NextNum=`expr $NextNum + 1`
    if [ $NextNum -eq $Counter ];then
        echo $base skip! >> $LogFile
    else
        dest=$BakDir/$base
        if(test -e $dest)
        then
            echo $base exist! >> $LogFile
        else
            cp $BinDir/$base $BakDir
            echo $base copying >> $LogFile
         fi
    fi
done
  1. 使用rsync同步binlog文件,到另一台服务器上。 rsync -avz -e ssh XXX@ubuntu-vm-01:/home/xxx/backup/ ~/backup/

方案3: 物理备份(使用Xtrabackup)

创建单独用于备份的用户,并授予相应的权限,这个需要的权限和逻辑备份的不一样

1
2
3
create user db_xtrabackup@'localhost' identified by 'backup123';
grant reload,lock tables,replication client,create tablespace,super on *.* to db_xtrabackup@'localhost';
grant PROCESS on *.* to db_xtrabackup@'localhost';
  • 全量备份
  1. create sudo xtrabackup -udb_xtrabackup -pbackup123 --backup --target-dir=/data/backups
  2. prepare xtrabackup --prepare --target-dir=/data/backups
  3. restore

xtrabackup --copy-back --target-dir=/data/backups/

  • 增量备份 xtrabackup --backup --target-dir=/data/backups/inc1 --incremental-basedir=/data/backups/base –incremental-basedir:指定是基于哪个备份做增量备份。

测试的时候可能出现的问题,文件句柄打开太多 ulimit -n 65535