MySQL备份方案

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
 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

 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
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';