mysql用sql语句怎么做个脚本备份_mysql备份脚本

一、介绍两种日志

1、redo log是InnoDB存储引擎层的日志，又称重做日志文件，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。在实例和介质失败(media failure)时，redo log文件就能派上用场，如数据库掉电，InnoDB存储引擎会使用redo log恢复到掉电前的时刻，以此来保证数据的完整性。

在一条更新语句进行执行的时候，InnoDB引擎会把更新记录写到redo log日志中，然后更新内存，此时算是语句执行完了，然后在空闲的时候或者是按照设定的更新策略将redo log中的内容更新到磁盘中，这里涉及到WAL即Write Ahead logging技术，他的关键点是先写日志，再写磁盘。

有了redo log日志，那么在数据库进行异常重启的时候，可以根据redo log日志进行恢复，也就达到了crash-safe。

redo log日志的大小是固定的，即记录满了以后就从头循环写。

2、binlog是属于MySQL Server层面的，又称为归档日志，属于逻辑日志，是以二进制的形式记录的是这个语句的原始逻辑，依靠binlog是没有crash-safe能力的

3、区别：

redo log是属于innoDB层面，binlog属于MySQL Server层面的，这样在数据库用别的存储引擎时可以达到一致性的要求。

redo log是物理日志，记录该数据页更新的内容；binlog是逻辑日志，记录的是这个更新语句的原始逻辑

redo log是循环写，日志空间大小固定；binlog是追加写，是指一份写到一定大小的时候会更换下一个文件，不会覆盖。

binlog可以作为恢复数据使用，主从复制搭建，redo log作为异常宕机或者介质故障后的数据恢复使用。

二、xtra+binlog增量备份脚本

1、特点

mysqldump优点：mysqldump的优点就是逻辑备份，把数据生成SQL形式保存，在单库，单表数据迁移，备份恢复等场景方便，SQL形式的备份文件通用，也方便在不同数据库之间移植。对于InnoDB表可以在线备份。

mysqldump缺点：mysqldump是单线程，数据量大的时候，备份时间长，甚至有可能在备份过程中非事务表长期锁表对业务造成影响(SQL形式的备份恢复时间也比较长)。mysqldump备份时会查询所有的数据，这可能会把内存中的热点数据刷掉

innobackupex优点：物理备份可以绕过MySQL Server层，加上本身就是文件系统级别的备份，备份速度块，恢复速度快，可以在线备份，支持并发备份，支持加密传输，支持备份限速

innobackupex缺点：要提取部分库表数据比较麻烦，不能按照基于时间点来恢复数据，并且不能远程备份，只能本地备份，增量备份的恢复也比较麻烦。如果使用innobackupex的全备+binlog增量备份就可以解决基于时间点恢复的问题。

2、备份策略

根据需求，使用innobackupex全备份+innobackupex增量备份+binlog方式进行备份。

在出现问题时使用innobackupex快速的恢复

3、环境准备

开启binlog

编辑/etc/my.cnf文件添加在[mysqld]版块下添加如下变量，添加后重启服务。

#开启，并且可以将mysql-bin改为其它的日志名

log-bin=mysql-bin

#添加id号，如果做主从，就不能一样

server-id=1#超过200M将生产新的文件，最大和默认值是1GB

max_binlog_size=1G

#此参数表示binlog使用最大内存的数，默认1M。

max_binlog_cache_size=1M

#此参数表示binlog日志保留的时间，默认单位是天。

expire_logs_days=7

创建授权用户

create user 'back'@'localhost' identified by '123456';

grant reload,lock tables,replication client,create tablespace,process,super on*.* to 'back'@'localhost';

grant create,insert,select on percona_schema.* to 'back'@'localhost';

安装innobackupex

1.安装依赖yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev-devel2.下载安装wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

yum -y install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

三.添加脚本

全量备份

创建备份目录mkdir -p /root/binmkdir -p /bak/mysql-xback

编写脚本，要指定备份命令的账号和密码

vim/root/bin/mybak-all.sh#!/bin/bash

#全量备份，只备份一次

#指定备份目录

backup_dir="/bak/mysql-xback"#检查

[[-d ${backup_dir} ]] || mkdir -p ${backup_dir}if [[ -d ${backup_dir}/all-backup ]];then

echo "全备份已存在"exit1

fi#命令，需要设置

innobackupex--defaults-file=/etc/my.cnf --user=back --password='123456' --no-timestamp ${backup_dir}/all-backup &> /tmp/mysql-backup.logtail -n 1 /tmp/mysql-backup.log | grep 'completed OK!'

if [[ $? -eq 0 ]];then

echo "all-backup" > /tmp/mysql-backup.txtelse

echo "备份失败"exit1

fi

增量备份

编写脚本，要指定备份目录

vim/root/bin/mybak-section.sh#!/bin/bash

#增量备份

#备份目录

backup_dir="/bak/mysql-xback"#新旧备份

old_dir=`cat /tmp/mysql-backup.txt`

new_dir=`date +%F-%H-%M-%S`

#检查if [[ ! -d ${backup_dir}/all-backup ]];then

echo "还未全量备份"exit1

fi#命令/usr/bin/innobackupex --user=back --password='123456' --no-timestamp --incremental --incremental-basedir=${backup_dir}/${old_dir} ${backup_dir}/${new_dir} &> /tmp/mysql-backup.logtail -n 1 /tmp/mysql-backup.log | grep 'completed OK!'

if [[ $? -eq 0 ]];then

echo "${new_dir}" > /tmp/mysql-backup.txtelse

echo "备份失败"exit1

fi

binlog

创建备份目录mkdir -p /bak/mysql-binback

用于单点，备份binlog，要指定备份目录位置和其它变量

vim/root/bin/mybak-binlog.sh#!/bin/bash

#

# 注意：执行脚本前修改脚本中的变量

# 功能：cp方式增量备份

#

# 适用：centos6+# 语言：中文

#

#使用：./xx.sh -uroot -p'123456'，将第一次增量备份后的binlog文件名写到/tmp/binlog-section中，若都没有，自动填写mysql-bin.000001#过程：增量先刷新binlog日志，再查询/tmp/binlog-section中记录的上一次备份中最新的binlog日志的值

# cp中间的binlog日志，并进行压缩。再将备份中最新的binlog日志写入。

#恢复：先进行全量恢复，再根据全量备份附带的time-binlog.txt中的记录逐个恢复。当前最新的Binlog日志要去掉有问题的语句，例如drop等。

#[变量]

#mysql这个命令所在绝对路径

my_sql="/usr/local/mysql/bin/mysql"#mysqldump命令所在绝对路径

bak_sql="/usr/local/mysql/bin/mysqldump"#binlog日志所在目录

binlog_dir=/usr/local/mysql/data

#mysql-bin.index文件所在位置

binlog_index=${binlog_dir}/mysql-bin.index

#备份到哪个目录

bak_dir=/bak/mysql-binback

#这个脚本的日志输出到哪个文件

log_dir=/tmp/mybak-binlog.log

#保存的天数，4周就是28天

save_day=10#[自动变量]

#当前年

date_nian=`date +%Y-`

begin_time=`date +%F-%H-%M-%S`

#所有天数的数组

save_day_zu=($(for i in `seq 1 ${save_day}`;do date -d -${i}days "+%F";done))

#开始/usr/bin/echo >>${log_dir}/usr/bin/echo "time:$(date +%F-%H-%M-%S) info:开始增量备份" >>${log_dir}

#检查

${my_sql} $* -e "show databases;" &> /tmp/info_error.txtif [[ $? -ne 0 ]];then

/usr/bin/echo "time:$(date +%F-%H-%M-%S) info:登陆命令错误" >>${log_dir}/usr/bin/cat /tmp/info_error.txt #如果错误则显示错误信息

exit1

fi#移动到目录

cd ${bak_dir}

bak_time=`date +%F-%H-%M`

bak_timetwo=`date +%F`

#刷新

${my_sql} $* -e "flush logs"

if [[ $? -ne 0 ]];then

/usr/bin/echo "time:$(date +%F-%H-%M-%S) error:刷新binlog失败" >>${log_dir}

exit1

fi#获取开头和结尾binlog名字

last_bin=`cat /tmp/binlog-section`

next_bin=`tail -n 1 ${binlog_dir}/mysql-bin.index`echo ${last_bin} |grep 'mysql-bin' &> /dev/null

if [[ $? -ne 0 ]];then

echo "mysql-bin.000001" > /tmp/binlog-section #不存在则默认第一个

last_bin=`cat /tmp/binlog-section`fi#截取需要备份的binlog行数

a=`/usr/bin/sort ${binlog_dir}/mysql-bin.index | uniq | grep -n ${last_bin} | awk -F':' '{print $1}'`

b=`/usr/bin/sort ${binlog_dir}/mysql-bin.index | uniq | grep -n ${next_bin} | awk -F':' '{print $1}'`

let b--#输出最新节点/usr/bin/echo "${next_bin}" > /tmp/binlog-section

#创建文件rm -rf mybak-section-${bak_time}/usr/bin/mkdir mybak-section-${bak_time}for i in `sed -n "${a},${b}p" ${binlog_dir}/mysql-bin.index | awk -F'./' '{print $2}'`do

if [[ ! -f ${binlog_dir}/${i} ]];then

/usr/bin/echo "time:$(date +%F-%H-%M-%S) error:binlog文件${i} 不存在" >>${log_dir}

exit1

fi

cp -rf ${binlog_dir}/${i} mybak-section-${bak_time}/

if [[ ! -f mybak-section-${bak_time}/${i} ]];then

/usr/bin/echo "time:$(date +%F-%H-%M-%S) error:binlog文件${i} 备份失败" >>${log_dir}

exit1

fi

done#压缩if [[ -f mybak-section-${bak_time}.tar.gz ]];then

/usr/bin/echo "time:$(date +%F-%H-%M-%S) info:压缩包mybak-section-${bak_time}.tar.gz 已存在" >>${log_dir}/usr/bin/rm -irf mybak-section-${bak_time}.tar.gzfi

/usr/bin/tar -cf mybak-section-${bak_time}.tar.gz mybak-section-${bak_time}if [[ $? -ne 0 ]];then

/usr/bin/echo "time:$(date +%F-%H-%M-%S) error:压缩失败" >>${log_dir}

exit1

fi#删除binlog文件夹/usr/bin/rm -irf mybak-section-${bak_time}if [[ $? -ne 0 ]];then

/usr/bin/echo "time:$(date +%F-%H-%M-%S) info:删除sql文件失败" >>${log_dir}

exit1

fi#整理压缩的日志文件for i in `ls | grep "^mybak-section.*tar.gz$"`do

echo $i | grep ${date_nian} &> /dev/null

if [[ $? -eq 0 ]];thena=`echo ${i%%.tar.gz}`

b=`echo ${a:(-16)}` #当前日志年月日

c=`echo ${b%-*}`

d=`echo ${c%-*}`

#看是否在数组中，不在其中，并且不是当前时间，则删除。echo ${save_day_zu[*]} |grep -w $d &> /dev/null

if [[ $? -ne 0 ]];then[["$d" != "$bak_timetwo" ]] && rm -rf $ifi

else#不是当月的，其他类型压缩包，跳过

continuefi

done#结束

last_time=`date +%F-%H-%M-%S`/usr/bin/echo "begin_time:${begin_time} last_time:${last_time}" >>${log_dir}/usr/bin/echo "time:$(date +%F-%H-%M-%S) info:增量备份完成" >>${log_dir}/usr/bin/echo >>${log_dir}

用于主从，备份relay-bin，要指定备份目录位置和其它变量

vim/root/bin/mybak-binlog.sh#!/bin/bash

#

# 注意：执行脚本前修改脚本中的变量

# 功能：cp方式增量备份

#

# 适用：centos6+# 语言：中文

#

#使用：./xx.sh -uroot -p'123456'#[变量]

#mysql这个命令所在绝对路径

my_sql="/usr/local/mysql/bin/mysql"#mysqldump命令所在绝对路径

bak_sql="/usr/local/mysql/bin/mysqldump"#binlog日志所在目录

binlog_dir=/usr/local/mysql/data

#mysql-bin.index文件所在位置

binlog_index=${binlog_dir}/mysql-bin.index

#备份到哪个目录

bak_dir=/bak/mysql-binback

#这个脚本的日志输出到哪个文件

log_dir=/tmp/mybak-binlog.log

#保存的天数，4周就是28天

save_day=10#[自动变量]

#当前年

date_nian=`date +%Y-`

begin_time=`date +%F-%H-%M-%S`

#所有天数的数组

save_day_zu=($(for i in `seq 1 ${save_day}`;do date -d -${i}days "+%F";done))

#开始/usr/bin/echo >>${log_dir}/usr/bin/echo "time:$(date +%F-%H-%M-%S) info:开始增量备份" >>${log_dir}

#检查

${my_sql} $* -e "show databases;" &> /tmp/info_error.txtif [[ $? -ne 0 ]];then

/usr/bin/echo "time:$(date +%F-%H-%M-%S) info:登陆命令错误" >>${log_dir}/usr/bin/cat /tmp/info_error.txt #如果错误则显示错误信息

exit1

fi#移动到目录

cd ${bak_dir}

bak_time=`date +%F-%H-%M`

bak_timetwo=`date +%F`

#创建文件rm -rf mybak-section-${bak_time}/usr/bin/mkdir mybak-section-${bak_time}for i in `ls ${binlog_dir}| grep relay-bin`do

cp -rf ${binlog_dir}/${i} mybak-section-${bak_time}/

if [[ ! -f mybak-section-${bak_time}/${i} ]];then

/usr/bin/echo "time:$(date +%F-%H-%M-%S) error:binlog文件${i} 备份失败" >>${log_dir}

exit1

fi

done#压缩if [[ -f mybak-section-${bak_time}.tar.gz ]];then

/usr/bin/echo "time:$(date +%F-%H-%M-%S) info:压缩包mybak-section-${bak_time}.tar.gz 已存在" >>${log_dir}/usr/bin/rm -irf mybak-section-${bak_time}.tar.gzfi

/usr/bin/tar -cf mybak-section-${bak_time}.tar.gz mybak-section-${bak_time}if [[ $? -ne 0 ]];then

/usr/bin/echo "time:$(date +%F-%H-%M-%S) error:压缩失败" >>${log_dir}

exit1

fi#删除binlog文件夹/usr/bin/rm -irf mybak-section-${bak_time}if [[ $? -ne 0 ]];then

/usr/bin/echo "time:$(date +%F-%H-%M-%S) info:删除sql文件失败" >>${log_dir}

exit1

fi#整理压缩的日志文件for i in `ls | grep "^mybak-section.*tar.gz$"`do

echo $i | grep ${date_nian} &> /dev/null

if [[ $? -eq 0 ]];thena=`echo ${i%%.tar.gz}`

b=`echo ${a:(-16)}` #当前日志年月日

c=`echo ${b%-*}`

d=`echo ${c%-*}`

#看是否在数组中，不在其中，并且不是当前时间，则删除。echo ${save_day_zu[*]} |grep -w $d &> /dev/null

if [[ $? -ne 0 ]];then[["$d" != "$bak_timetwo" ]] && rm -rf $ifi

else#不是当月的，其他类型压缩包，跳过

continuefi

done#结束

last_time=`date +%F-%H-%M-%S`/usr/bin/echo "begin_time:${begin_time} last_time:${last_time}" >>${log_dir}/usr/bin/echo "time:$(date +%F-%H-%M-%S) info:增量备份完成" >>${log_dir}/usr/bin/echo >> ${log_dir}

重写备份

创建备份目录mkdir -p /bak/xback

编写脚本，要指定备份目录位置

vim/root/bin/mybak-rewrite.sh#!/bin/bash

#xbak备份脚本

#每周六执行一次

#10 4 * * 6 /bin/bash /root/bin/mybak-rewrite.sh#清理并备份

[[-d /bak/xback ]] || mkdir -p /bak/xback

cd/bak/xbackrm -rf *.tar.gz

[[-d bak/mysql-xback ]] || echo "bak-dir not found"cd/bak/mysql-xbacktar -cf XtraBackup.tar.gz *

mv XtraBackup.tar.gz /bak/xbackrm -rf /bak/mysql-xback/*#全备份一次

bash /root/bin/mybak-all.sh

备份周期

添加权限chmod +x /root/bin/*先进行一次innobackupex全量备份，后面的增量均在全量的基础上备份。

bash /root/bin/mybak-all.sh

每天2点进行一次innobackupex增量备份+binlog日志备份

每周4点将之前的innobackupex备份打包，并启动新的全量备份

crontab -e

30 2 * * * /bin/bash /root/bin/mybak-binlog.sh -uback -p'123456'

40 2 * * * /bin/bash /root/bin/mybak-section.sh

10 4 * * 6 /bin/bash /root/bin/mybak-rewrite.sh

四.容灾测试

写入测试数据

创建脚本，脚本将创建一个single库，s1表，持续写入数据。

vim/root/bin/mysql_test.sh#!/bin/bash

#混合测试数据库脚本

#将创建一个single数据库，其中创建一个s1表

#如果数据库存在，将会写入数据，可以在写入部分sleep1来让数据持续写入

#使用方法 ./xx.sh -uroot -p'123456'#检查

mysql $* -e "show databases;" &> /tmp/info_error.txtif [[ $? -ne 0 ]];then

echo "time:$(date +%F-%H-%M-%S) info:登陆命令错误"

cat /tmp/info_error.txt #如果错误则显示错误信息echo

echo "./xx.sh -uroot -p'123456'"exit1

fi#检查库是否存在

mysql $* -e "use single;" &> /tmp/info_error.txtif [[ $? -eq 0 ]];thenmysql $* -e "use single;select * from s1 where id=1;"

if [[ $? -ne 0 ]];thenmysql $* -e "use single;drop table s1;"mysql $* -e "use single;create table s1(id int AUTO_INCREMENT PRIMARY KEY,name char(20),age int);"

fi

elsemysql $* -e "create database single;"mysql $* -e "use single;create table s1(id int AUTO_INCREMENT PRIMARY KEY,name char(20),age int);"

fi#name随机数

random_name() {

local zu=(q we r t y u i o p a s d f g h j k l z x c v b n m)for i in `seq 1 5`dolocal a=`echo $[RANDOM%24]`echo -n ${zu[a]}done}

#age随机数

random_age() {

local a=`echo $[RANDOM%99]`echo$a

}

#写入部分for i in `seq 1 1000`dob=`random_name`

c=`random_age`sleep 2mysql $* -e "use single;insert into s1(name,age) values('${b}',${c});"

done执行脚本，持续写入测试数据

bash/root/bin/mysql_test.sh -uroot -p'123456'

模拟备份

1.开启另一个终端，检查数据是否在写入

mysql-uroot -p'123456' -e "use single;select count(*) from s1;"

2.进行全备份，返回ok正确

cd/root/bin

bash mybak-all.sh

3.模拟第一天晚上，因为在持续写入数据，等一会再进行增量备份，并备份binlog

bash mybak-section.shbash mybak-binlog.sh -uback -p'123456'

4.等一会，模拟第二天晚上

bash mybak-section.shbash mybak-binlog.sh -uback -p'123456'

错误恢复

1.模拟出现问题了，停止写入

ctl+c2.查看当前总数据，为129条

mysql-uroot -p'123456' -e "use single;select count(*) from s1;"

3.模拟误删除数据库

mysql-uroot -p'123456' -e "drop database single;"

4.对最新的状态备份binlog

bash mybak-binlog.sh -uback -p'123456'

5.关闭mysql

systemctl stop mysql6.删除数据目录，否则无法恢复rm -fr /usr/local/mysql/data/*7.对全备份进行封装，返回ok正确

innobackupex --apply-log --redo-only /bak/mysql-xback/all-backup/

8.合并第一次模拟到全备份，最后一个参数是指定全备份

innobackupex --apply-log --redo-only --incremental-dir=/bak/mysql-xback/2018-12-12-14-51-26/ /bak/mysql-xback/all-backup/

9.合并第二次模拟到全备份

innobackupex --apply-log --redo-only --incremental-dir=/bak/mysql-xback/2018-12-12-14-52-47/ /bak/mysql-xback/all-backup/

10.开始恢复

innobackupex --copy-back /bak/mysql-xback/all-backup/

11.添加权限并启动

chown -R mysql.mysql /usr/local/mysql/data/

systemctl start mysql

12.查看数据恢复到125条，正常前是129条

mysql -uroot -p'123456' -e "use single;select count(*) from s1;"

13.查看备份时的pos点

cat /bak/mysql-xback/2018-12-12-14-52-47/xtrabackup_info | grep binlog_pos

显示如下

binlog_pos = filename ‘mysql-bin.000003’, position ‘1509’

14.解压最后2个binlog压缩包

cd /bak/mysql-binback/

tar -xf mybak-section-2018-12-12-15-07.tar.gz

tar -xf mybak-section-2018-12-12-15-15.tar.gz

15.进入第二个包，它是在最后一次增量备份后才执行的binlog

cd mybak-section-2018-12-12-15-07

将1509pos点之前的行删除，每个binlog文件前18行要保留

mysqlbinlog mysql-bin.000003 > 03.log

cat 03.log | grep -n 1509

恢复数据

cat 03.log | mysql -uroot -p'123456'

当前总是为128

mysql -uroot -p'123456' -e "use single;select count(*) from s1;"

16.进入倒数第一个包，它存放出问题之前的数据

cd mybak-section-2018-12-12-15-15

将drop这个有问题的指令之后的行删除

mysqlbinlog mysql-bin.000004 > 04.log

cat 04.log | grep -n drop

恢复数据

cat 04.log | mysql -uroot -p'123456'

当前总是为129

mysql -uroot -p'123456' -e "use single;select count(*) from s1;"

五.重写测试

进入到脚本目录

cd/root/bin

执行重写脚本

bash mybak-rewrite.sh可以看到原先目录只有一个全备份ls /bak/mysql-xback/原先的全备份和增量备份的打包ls -lh /bak/xback/