巴拉巴拉
小魔仙

备份数据库到阿里云OSS——数据备份是不是要做好呢~

有没有遇到过服务器的系统突然故障了呢?数据库万一丢失了呢!!??数据损失是很严重的,轻则丢失不重要的数据,重则,,,所有用户和采集的数据都丢失,,,(*@ο@*) 哇~想想都可怕

虽然现在有很多备份数据库的方法,但是我这里提供了一个奇怪的思路。

  1. 利用Spring-Quartz进行定时操作,这样就能定时的备份数据库啦~
  2. 用MyBatis查询各个表的结构和数据,当然不能忘了视图
  3. 将结构和数据序列化成SQL语句然后上传到阿里云OSS(利用OSS的追加上传功能)

原本我选择了阿里云的OSS和FTP作为选择方案,因为自己的FTP服务器和阿里云OSS都是很便宜的方案,FTP可以从网上随便购买到空间,用来备份不是特别大的SQL文件还是绰绰有余哒。但是后来我还是选择了使用OSS,因为OSS不存在购买和续费的问题,空间还是无限大,只要单个文件不超过5g,永久在线(除非阿里的OSS服务器挂了/关了/倒闭了/网线断了)

我这里做过几次测试,一个一百多兆的mysql数据库还是可以备份哒。

相关maven:主要用阿里云的OSS和quartz定时插件,这两个内容都在本博客前面的篇幅中介绍过,大家可以翻翻看~顺便浏览浏览本博客啦~增加点击量和访问量/笑哭

<!-- 阿里云OSS支持 -->
<dependency>
  <groupId>com.aliyun.oss</groupId>
  <artifactId>aliyun-sdk-oss</artifactId>
  <version>2.4.0</version>
</dependency>

<!-- quartz支持 -->
<dependency>
  <groupId>org.quartz-scheduler</groupId>
  <artifactId>quartz</artifactId>
  <version>2.2.1</version>
</dependency>

<!-- 阿里提供仓库 -->
<repository>
  <id>sonatype-nexus-staging</id>
  <name>Sonatype Nexus Staging</name>
  <url>https://oss.sonatype.org/service/local/staging/deploy/maven2/</url>
  <releases>
	<enabled>true</enabled>
  </releases>
  <snapshots>
	<enabled>true</enabled>
  </snapshots>
</repository>

spring-quartz.xml

我习惯吧各个功能的spring都分开,所以spring-quartz.xml这个里面只有quartz的配置内容~这样看着更清晰呀,是吧~

cronExpression="0 0 2 * * ?"

--表示每天凌晨2点执行一次

<value>SQLBackup</value>

--表示在阿里云OSS中生成SQL文件的位置前缀,这里是SQLBakup,则生成的key会是SQLBackup/2017_2_21_02_00_00.sql

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
			http://www.springframework.org/schema/beans/spring-beans-4.1.xsd
			http://www.springframework.org/schema/context
			http://www.springframework.org/schema/context/spring-context-4.1.xsd">

	<!-- 定时调度,关闭 -->
	<!-- 注解扫描方式,可以使用注解 -->
	<context:component-scan base-package="com.againfly.project.bean" />

	
 	<bean id="methodInvokingJobDetail" class="org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean">
	    <property name="targetObject" ref="quartzJob"/>
	    <property name="targetMethod" value="backupDataBaseToOss"/>
		<property name="arguments">
			<array>
				<value>SQLBackup</value>
			</array>
		</property>
	</bean>

	<bean id="cronTrigger" class="org.springframework.scheduling.quartz.CronTriggerFactoryBean">
	    <property name="jobDetail" ref="methodInvokingJobDetail"/>
	    <property name="startDelay" value="2000"/>
	    <property name="cronExpression" value="0 0 2 * * ?"/>
	</bean>
	<bean class="org.springframework.scheduling.quartz.SchedulerFactoryBean" lazy-init="false">
	    <property name="triggers">
	        <list>
	            <ref bean="cronTrigger" />
	        </list>
	    </property>
	</bean>
</beans>

QuartzJob.java工作类:

主要实现了定时任务工作时间,在OSS中生成一个可以追加的key,key名称为当前时间,导出顺序为:

1.获取所有表,遍历表,导出表结构,利用do{}while()循环获取该表的分页数据,遍历数据,导出INSERT INTO数据

2.获取所有视图,导出创建视图结构

package com.againfly.project.bean;

import com.againfly.project.entity.system.Table;
import com.againfly.project.service.BackupService;
import com.againfly.project.utils.BackupDbUtils;
import com.againfly.project.utils.aliyun.AliYunConst;
import com.againfly.project.utils.aliyun.oss.OssUtil;
import com.aliyun.oss.OSSClient;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created by Jecced on 2017/2/21
 */
@Component
public class QuartzJob {

    @Resource
    private BackupService backupService;

    public void backupDataBaseToOss(String priKey){
        SimpleDateFormat sdf = new SimpleDateFormat("/yyyy_MM_dd_HH_mm_ss");
        String key = null == priKey ? "" : priKey + sdf.format(new Date()) + ".sql";
        System.out.print(key);
        OSSClient ossClient = new OSSClient(AliYunConst.OSS_END_POINT, AliYunConst.ACCESS_KEY, AliYunConst.ACCESS_SECRET);
        long position = 0l;
        boolean found = ossClient.doesObjectExist(AliYunConst.OSS_BUCK_NAME, key);
        if(found){return; }
        position = OssUtil.append(ossClient, key, "#----------------导出表结构和表数据-----------------\n", position);
        for(Table table : backupService.SHOW_TABLE_STATUS()){
            /*导出建表语句*/
            position = OssUtil.append(ossClient, key, backupService.CREATE_TABLE_SQL(table.getName()), position);
            position = OssUtil.append(ossClient, key, "\n;\n\n", position);
            /*导出insert语句*/
            int pageNo = 0;
            int pageSize = 5000;
            List<Map<String, Object>> limitData = null;
            boolean flag = false;
            do{
                limitData = backupService.limitData(table.getName(), pageNo * pageSize, pageSize);
                flag = null != limitData && 0 != limitData.size() && limitData.size() == pageSize;
                pageNo++;
                position = OssUtil.append(ossClient, key, BackupDbUtils.GenInsertSql(table.getName(), limitData), position);
                limitData = null;
                System.gc();
            }while (flag);
            System.gc();
        }
        position = OssUtil.append(ossClient, key, "#-------------------导出视图结构-------------------\n", position);
        for(Table view : backupService.SHOW_VIEW_STATUS()){
            position = OssUtil.append(ossClient, key, backupService.CREATE_VIEW_SQL(view.getName()), position);
        }
        ossClient.shutdown();
    }
}

根据读取出来的表数据进行生成SQL语句工具类:

下面的GenCreateSql@Deprecated标记啦,因为现在已经改良啦~不用这个方法来生成create table语句了,现在有更简单的方法啦~

直接show create table table_name;就能获取表的建表数据啦。

GenInsertSql方法已经对插入数据做了批量插入语句的分割。每个insert语句插入多少条数据可以修改下面的size的大小,我这里写的是1000

package com.againfly.project.utils;

import com.againfly.project.entity.system.Columns;

import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * Created by Jecced on 2017/2/21
 */
public class BackupDbUtils {
    public static final String STR_ENTER = System.getProperty("line.separator");

    public static String GenInsertSql(String tableName, List<Map<String, Object>> allData){
        if(null == allData || 0 == allData.size() || OssUtil.StringIsNull(tableName)){
            return "";
        }
        Set<String> keys = allData.get(0).keySet();

        int size = 1000;
        int count = 0;

        StringBuilder sb = new StringBuilder("INSERT INTO ");
        sb.append(tableName).append('(');
        for(String key : keys){
            sb.append('`').append(key).append("`,");
        }
        sb.delete(sb.length() - 1, sb.length()).append(") VALUES");
        String firstSql = sb.toString();
        sb.setLength(0);
        while (allData.size() > size * count){
            StringBuilder singleSQL = new StringBuilder(firstSql);
            for(int i = count * size; i < size * (count + 1) && i < allData.size(); i ++){
                singleSQL.append(STR_ENTER).append("\t(");
                Map<String,Object> entity = allData.get(i);
                for (String key : keys){
                    Object obj = entity.get(key);
                    if(null == obj || obj instanceof Number || obj instanceof Boolean){
                        singleSQL.append(obj).append(',');
                    }else{
                        if(obj instanceof String && ((String) obj).contains("'")){
                            obj = ((String) obj).replaceAll("'","\\\\'");
                        }
                        singleSQL.append('\'').append(obj).append('\'').append(',');
                    }
                }
                singleSQL.delete(singleSQL.length() - 1, singleSQL.length()).append("),");
            }
            singleSQL.delete(singleSQL.lastIndexOf(","), singleSQL.length()).append(";").append(STR_ENTER).append(STR_ENTER);
            sb.append(singleSQL);
            ++count;
        }
        return sb.toString();
    }

    @Deprecated
    public static String GenCreateSql(String tableName,List<Columns> columnsList){
        StringBuilder sb = new StringBuilder("CREATE TABLE `");
        sb.append(tableName).append("` (\n");
        List<String> priameKey = new LinkedList<>();
        for(Columns col : columnsList){
            if("PRI".equalsIgnoreCase(col.getKey())){
                priameKey.add(col.getField());
            }
            sb.append("\t`").append(col.getField()).append("` ").append(col.getType()).append(" ");
            sb.append("YES".equalsIgnoreCase(col.getNullStr()) ? "NULL " : "NOT NULL ");
            if(OssUtil.StringIsNull(col.getDefaultStr())){

            }else if(col.getDefaultStr().contains("null")){
                sb.append("DEFAULT NULL ");
            }else{
                sb.append("DEFAULT ").append(col.getDefaultStr()).append(" ");
            }
            if(!OssUtil.StringIsNull(col.getExtra())){
                sb.append(col.getExtra());
            }
            sb.append(",\n");
        }
        if(0 == priameKey.size()){
            sb.delete(sb.lastIndexOf(","), sb.length());
        }else{
            sb.append("\tPRIMARY KEY (");
            for(String str : priameKey){
                sb.append("`").append(str).append("`,");
            }
            sb.delete(sb.length() - 1, sb.length());
            sb.append(")\n");
        }
        sb.append(")\n").append("COLLATE='utf8_general_ci'\n").append("ENGINE=InnoDB\n;");
        return sb.toString();
    }
}

 

————————OSS部分

AliYunConst.java:用来存储阿里云配置的几个常量,经常用阿里云服务的人肯定很熟悉ACCESS_KEY和ACCESS_SECRET两个配置吧~另外两个是OSS的配置啦。

package com.againfly.project.utils.aliyun;

/**
 * Created by Jecced 2017/2/21
 */
public class AliYunConst {
    public static final String ACCESS_KEY = "/****** your access_key ********/";
    public static final String ACCESS_SECRET = "/****** your access_secret ********/";

    public static final String OSS_END_POINT = "http://oss-cn-hangzhou.aliyuncs.com";
    public static final String OSS_BUCK_NAME = "/****** your buck_name ********/";
}

OssUtil.java

OSS可追加的对象要使用AppendObjectRequest,这个项目中只使用了第一个方法。

第二个方法可以用作参考OSS可追加内容的方法参考。

package com.againfly.project.utils.aliyun.oss;

import com.againfly.project.utils.aliyun.AliYunConst;
import com.aliyun.oss.OSSClient;
import com.aliyun.oss.model.AppendObjectRequest;
import com.aliyun.oss.model.AppendObjectResult;
import com.aliyun.oss.model.SimplifiedObjectMeta;

import java.io.ByteArrayInputStream;

/**
 * Created by Jecced on 2017/2/21
 */
public class OssUtil {
    /**
     * 在已有OSSClient链接,且知道position的情况下使用,追加文字内容
     * ps:position就是现有obj的大小,这个大小可以通过原数据中获取
     */
    public static long append(OSSClient ossClient, String key, String Content,long position){
        AppendObjectRequest aor = new AppendObjectRequest(AliYunConst.OSS_BUCK_NAME, key, new ByteArrayInputStream(
                Content.getBytes()));
        aor.setPosition(position);
        AppendObjectResult result = ossClient.appendObject(aor);
        return result.getNextPosition();
    }

    /**
     * 向OSS某一个key中追加文字内容
     */
    public static void appendObject(String key, String content){
        OSSClient ossClient = new OSSClient(AliYunConst.OSS_END_POINT, AliYunConst.ACCESS_KEY, AliYunConst.ACCESS_SECRET);

        long size = 0l;

        // Object是否存在
        boolean found = ossClient.doesObjectExist(AliYunConst.OSS_BUCK_NAME, key);
        if(found){
            SimplifiedObjectMeta objectMeta = ossClient.getSimplifiedObjectMeta(AliYunConst.OSS_BUCK_NAME, key);
            size = objectMeta.getSize();
        }

        AppendObjectRequest aor = new AppendObjectRequest(AliYunConst.OSS_BUCK_NAME, key, new ByteArrayInputStream(content.getBytes()));
        aor.setPosition(size);

        ossClient.appendObject(aor);

        ossClient.shutdown();
    }


    public static boolean StringIsNull(String str){
        return null == str || 0 == str.length();
    }
}

 

————————Mybatis部分

BackupMapper.xml

包含了实际的执行SQL语句,主要的也就是哪些show语句哈。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.againfly.project.dao.BackupMapper">

    <resultMap id="tableMap" type="com.againfly.project.entity.system.Table">
        <result property="autoIncrement" column="auto_increment" javaType="java.lang.Integer" jdbcType="INTEGER"/>
    </resultMap>
    <!-- 毕竟字段名不用有空格,就只能用resultMap -->
    <resultMap id="createTableMap" type="com.againfly.project.entity.system.CreateTable">
        <result property="createTable" column="Create Table" javaType="string" jdbcType="VARCHAR"/>
    </resultMap>
    <!-- 为了做对象复用,偷懒映射到了CreateTable实体类里面去了。。。 -->
    <resultMap id="createViewMap" type="com.againfly.project.entity.system.CreateTable">
        <result property="table" column="View" javaType="string" jdbcType="VARCHAR"/>
        <result property="createTable" column="Create View" javaType="string" jdbcType="VARCHAR"/>
    </resultMap>


    <!-- 显示所有表 -->
    <select id="SHOW_TABLE_STATUS" resultMap="tableMap">
        SHOW TABLE STATUS WHERE Engine IS NOT NULL;
    </select>

    <!-- 显示所有视图 -->
    <select id="SHOW_VIEW_STATUS" resultMap="tableMap">
        SHOW TABLE STATUS WHERE Engine IS NULL;
    </select>

    <!-- 查询建表语句 -->
    <select id="CREATE_TABLE" parameterType="String" resultMap="createTableMap">
        SHOW CREATE TABLE ${tableName};
    </select>

    <!-- 查询建立视图语句 -->
    <select id="CREATE_VIEW" parameterType="String" resultMap="createViewMap">
        SHOW CREATE VIEW ${viewName};
    </select>



    <!-- 列出所有的表名和视图名,这个现在也不用啦,用上面那几个 -->
    <select id="showTables" resultType="String">
        show tables;
    </select>

    <!-- default和null是Java的保留字,不能用来设置为字段名,只能用resultMap映射成其他字段了 -->
    <resultMap id="colMap" type="com.againfly.project.entity.system.Columns">
        <result column="Default" property="defaultStr"/>
        <result column="null" property="nullStr"/>
    </resultMap>

    <!-- 列出某个表中所有的字段和详情 -->
    <select id="showCol" parameterType="string" resultMap="colMap">
        SHOW COLUMNS from ${tableName}
    </select>

    <!-- 获取某个表中所有的数据,已经不用了,用下面那个分页的~ -->
    <select id="findAll" parameterType="string" resultType="map">
        SELECT * FROM ${tableName}
    </select>

    <!-- 获取某个表中的分页数据 -->
    <select id="limitData" resultType="map">
        SELECT * FROM ${tableName}
        LIMIT ${pageNo},${pageSize};
    </select>
</mapper>

MyBatis对应的dao和Service

BackupMapper.java

package com.againfly.project.dao;


import com.againfly.project.entity.system.Columns;
import com.againfly.project.entity.system.CreateTable;
import com.againfly.project.entity.system.Table;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

/**
 * Created by Jecced on 2017/2/20
 */
public interface BackupMapper {
    /** 获取所有表 */
    Table[] SHOW_TABLE_STATUS();

    /** 获取建表结构 */
    CreateTable CREATE_TABLE(@Param("tableName") String tableName);

    /** 获取所有视图 */
    Table[] SHOW_VIEW_STATUS();

    /** 获取视图结构 */
    CreateTable CREATE_VIEW(@Param("viewName") String viewName);

    /** 获取所有表名和视图名 */
    @Deprecated
    String[] showTables();

    /** 根据表名获取字段详情 */
    @Deprecated
    List<Columns> showCol(@Param("tableName")String tableName);

    /** 根据表名获取所有数据 */
    List<Map<String,Object>> findAll(@Param("tableName")String tableName);

    /** 根据表名获取分页数据 */
    List<Map<String, Object>> limitData(@Param("tableName")String tableName, @Param("pageNo")int pageNo, @Param("pageSize") int pageSize);
}

BackupService.java

package com.againfly.project.service;

import com.againfly.project.dao.BackupMapper;
import com.againfly.project.entity.system.Columns;
import com.againfly.project.entity.system.CreateTable;
import com.againfly.project.entity.system.Table;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;

/**
 * Created by Jecced on 2017/2/20
 */
@Service
public class BackupService{
    @Resource
    private BackupMapper dao;

    /**
     * 获取所有表信息
     */
    public Table[] SHOW_TABLE_STATUS(){
        return dao.SHOW_TABLE_STATUS();
    }

    /**
     * 获取建表结构
     */
    public String CREATE_TABLE_SQL(String tableName){
        CreateTable table = dao.CREATE_TABLE(tableName);
        return null == table ? null : table.getCreateTable();
    }

    /**
     * 获取所有视图
     */
    public Table[] SHOW_VIEW_STATUS(){
        return dao.SHOW_VIEW_STATUS();
    }

    /**
     * 获取视图结构
     */
    public String CREATE_VIEW_SQL(String viewName){
        CreateTable view = dao.CREATE_VIEW(viewName);
        return null == view ? null : view.getCreateTable();
    }

    /**
     * 获取所有表和视图名
     */
    @Deprecated
    public String[] showTable() {
        return dao.showTables();
    }

    /**
     * 获取表中所有字段详情
     */
    @Deprecated
    public List<Columns> showCol(String tableName){
        return dao.showCol(tableName);
    }

    /**
     * 查询表中所有数据
     */
    public List<Map<String,Object>> findAll(String tableName){
        return dao.findAll(tableName);
    }

    /**
     *  根据表名获取分页数据
     */
    public List<Map<String, Object>> limitData(String tableName,int pageNo,int pageSize){
        return dao.limitData(tableName, pageNo, pageSize);
    }

}

————————三个实体类

Table.java,CreateTable.java,Columns.java

package com.againfly.project.entity.system;

/**
 * Created by Jecced on 2017/2/21
 */
public class Table {
    /*表名*/
    private String name;
    /*引擎名*/
    private String engine;
    /*排序规则utf8_general_ci*/
    private String collation;
    /*备注*/
    private String comment;
    /*当前自动序列*/
    private int autoIncrement;
    
}
package com.againfly.project.entity.system;

/**
 * Created by Jecced on 2017/2/21
 */
public class CreateTable {
    /*表名视图名*/
    private String table;
    /*建表SQL或视图SQL*/
    private String createTable;
}
package com.againfly.project.entity.system;

/**
 * Created by Jecced on 2017/2/20
 */
public class Columns {
    /*字段名*/
    private String field;
    /*空字符串或者auto_increment*/
    private String extra;
    /*主键或其他键*/
    private String key;
    /*字段类型*/
    private String type;
    /*是否为null*/
    private String nullStr;//YES,NO
    /*默认值*/
    private String defaultStr;
}

这三个实体类都省略了get和set方法哈~~~

赞(0) 打赏
如果文章对你有帮助,欢迎你来评价反馈。AgainFly » 备份数据库到阿里云OSS——数据备份是不是要做好呢~
标签:

评论 1

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
  • Q Q(选填)
  1. #1
    头像

    代码好多,没看懂。。

    中国梦2年前 (2018-02-15)回复

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏