mysql数据备份

总体介绍

针对不同的场景下，我们应该制定不同的备份策略对数据库进行备份， 一般情况下，备份策略一般为以下四种：

• 直接cp,tar复制数据库文件
• mysqldump+复制BIN LOGS
• lvm2快照+复制BIN LOGS
• xtrabackup

以上几种解决方案分别针对于不同的场景：

• 如果数据量较小， 可以使用第一种方式，直接复制数据库文件
• 如果数据量还行，可以使用第二种方式，先使用mysqldump对数据库进行完全备份，然后定期备份BINARY LOG达到增量备份的效果
• 如果数据量一般，而又不过分影响业务运行，可以使用第三种方式，使用lvm2的快照对数据文件进行备份，而后定期备份BINARY LOG达到增量备份的效果
• 如果数据量很大，而又不过分影响业务运行，可以使用第四种方式，使用xtrabackup进行完全备份后，定期使用xtrabackup进行增量备份或差异备份

以下介绍常用的两种备份方式：

1、使用mysqldump命令备份：

mysqldump命令将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。
mysqldump命令的工作原理很简单。它先查出需要备份的表的结构，再在文本文件中生成一个CREATE语句。然后，将表中的所有记录转换成一条INSERT语句。然后通过这些语句，就能够创建表并插入数据。

mysql> show master logs;  #查看数据库所有日志文件。
mysql> flush logs; #将内存中log日志写磁盘，保存在当前binlog文件中，并产生一个新的binlog日志文件。
mysql> reset master;  #删除所有二进制日志，在（mysql-bin.000001）开始记录。 
##命令如下：
mysqldump --all-databases > dump.sql
#或  
mysqldump -u username -p dbname table1 table2 ...-> BackupName.sql   

其中：

• dbname参数表示数据库的名称；
• table1和table2参数表示需要备份的表的名称，为空则整个数据库备份；
• BackupName.sql参数表示备份文件的名称，文件名前面可以加上一个绝对路径。通常将数据库备份成一个后缀名为sql的文件；

使用mysqldump命令备份的数据库还原语法如下：

mysql -u root -p [dbname] < BackupName.sql

2、xtrabackup备份

Percona XtraBackup具体详解可参考博客：https://blog.csdn.net/sinat_29214327/article/details/81517233

• 2.1、安装

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm

yum install percona-xtrabackup-24

yum -y install perl-DBD-MySQL.x86_64

• 2.2、创建备份用户

mysql> grant reload,lock tables,replication client on *.* to 'backup'@'localhost' identified by '123456';

mysql> grant process on *.* to backup@'localhost';

mysql> flush privileges;

• 2.3、全量物理备份

----> 2.3.1、生成当前时间戳命名的备份文件

innobackupex --user=backup --password=123456 /data/mysql/backup

---->2.3.2、生成当前时间戳命名的备份文件让备份文件准备，备份文件不能直接用于恢复，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务，此时数据文件处于不一致的状态。因此，我们现在就是要通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。

innobackupex --user=backup --password=123456 --apply-log /data/mysql/backup/2019-04-30_14-11-17

---->2.3.3、将mysql的数据目录链接到这个已经恢复的备份目录，或直接用copy-back拷贝至mysql数据目录

systemctl stop mysqld
mv mysql mysql.old
ln -s /data/mysql/backup/2019-04-30_14-11-17/  /var/lib/mysql
chown  mysql:mysql /var/lib/mysql
chown -R mysql:mysql /data/mysql/backup/*

systemctl start mysqld  #重启mysql即可 恢复

• 2.4、增量备份

#全备
innobackupex --user=backup --password=123456 /data/mysql/backup 

#第一次增量备份
innobackupex  --user=backup --password=123456 --incremental /data/mysql/backup/inc  --incremental-basedir=/data/mysql/backup/2019-04-30_14-11-17/

#第二次增量备份
innobackupex  --user=backup --password=123456 --incremental /data/mysql/backup/inc  --incremental-basedir=incremental /data/mysql/backup/inc/2019-04-30_14-35-26

#恢复 
innobackupex --apply-log --redo-only ~/data/mysql/backup/full/2019-04-30_16-29-09/

## 注意每次恢复后设置 备份文件的mysql用户权限

innobackupex --apply-log --redo-only /data/mysql/backup/full/2019-04-30_20-52-43 --incremental-dir=/data/mysql/backup/inc/2019-04-30_21-01-31

相关shell脚本

• mysql和xtrabackup安装

#####  mysql  安装   默认5.7    
yum  -y install http://mirrors.d.com/software/mysql/5.7/mysql57-community-release-el7-11.noarch.rpm 

#echo "#### install   mysql：默认安装最新版5.7，  若需要之前版本需要修改/etc/yum.repos.d/mysql-community.repo 中对应版本的enabled值"

yum -y install mysql-community-server
mysqladmin --version

#  deta目录   /var/lib/mysql
if [ ! -d /var/lib/mysql ]; then
    echo install  mysql  failed
    exit 0;
fi

service mysqld start

###### mysql 设置
#设置 root  密码   123456
mysqladmin -u root password "123456";
#允许所有客户端访问
 ##mysql -uroot -p123456 -D mysql -e "update user set Host='%' where User='root' and Host='127.0.0.1';"

# Grant all on *.* to 'root'@'%' identified by 'password' with grant option;
mysql -uroot -p123456 -e "Grant all on *.* to 'root'@'%' identified by 'password' with grant option;"
#创建备份用户  backup  123456
mysql -uroot -p123456 -e "grant reload,lock tables,replication client ,process on *.* to 'backup'@'localhost' identified by '123456';"

mysql -uroot -p123456 -e ' flush privileges;'

mysql -uroot -p123456 -D mysql  -e 'select host,user from user;'  


#xtrabackup安装 
yum   -y install   http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
yum -y install   percona-xtrabackup-24
#yum  -y  install  perl-DBD-MySQL.x86_64

• 备份脚本，定时执行

#!/bin/bash

# mysql备份文件，该文件每天执行，每周一全备，每天基于本周一的全备做增量备份
#全备最近四周，增备最近七天
#  crontab -e
#  00 03 * * 1  /data/mysql/backup/backup.sh
backupfull_dir='/data/mysql/backup/full'
backupinc_dir='/data/mysql/backup/inc'
username='backup'
password='123456'
#需要在备份服务器上对备份文件定期删除处理
backup_host='10.0.12.59'   
last4week_day=`date -d -4weeks '+%Y-%m-%d'`  
week_day=`date +%w`
lastmon_day=`date -d "last Mon" +"%Y-%m-%d"`
today=`date +%Y-%m-%d`

# Every Monday  full back 
if [ $week_day == 1 ]; then
    #周一删除四周前的基础备份，新增基础备份
    rm -rf ${backupfull_dir}/${last4week_day}*
    ulimit -n 65535 && innobackupex --user=$username --password=$password $backupfull_dir
    #备份文件传到其他服务器
    if [ $backup_host ]; then 
      new_full=`ls ${backupfull_dir} |grep $today`
      #tar czvf ${new_full}.tar $new_full 
      cd $backupfull_dir && tar -cf - ${new_full} | pigz > ${new_full}.tgz
      rsync -rl ${new_full}.tar root@10.0.12.59:/data/mysql/backup/full 
      rm -rf ${new_full}.tar
    fi
fi

#每天增量备份，基于周一的全备
full_dir=`ls ${backupfull_dir} |grep $lastmon_day`

if [ $full_dir ]; then
   ulimit -n 65535 && innobackupex  --user=$username --password=$password --incremental $backupinc_dir  --incremental-basedir=${backupfull_dir}/${full_dir} 
   #备份文件传到其他服务器
   if [ $backup_host ]; then
     new_inc=`ls ${backupinc_dir} |grep $today`
     #tar czvf ${new_inc}.tar $new_inc
     cd $backupinc_dir && tar -cf - ${new_inc} | pigz > ${new_inc}.tgz
     rsync -rl ${new_inc}.tar root@10.0.12.59:/data/mysql/backup/inc
     rm -rf ${new_inc}.tar
   fi
fi
#删除 一周之前的增量备份
find $backupinc_dir -maxdepth 1  -mtime +7 | xargs rm -rf

参考博客

https://www.cnblogs.com/SQL888/p/5751631.html
http://www.cnblogs.com/youkanyouxiao/p/8335173.html