通过纯java代码备份mysql库

以往java备份mysql数据库需要通过dump工具,不同的服务器需要配置环境变量来实现,不具通用性,能不能使用纯java代码来实现呢?当然可以,先看一下如何使用,以jfinal为例子:

 PropKit.use("config.txt");
        MySqlInfo mySqlInfo = new MySqlInfo(PropKit.get("jdbcUrl"), PropKit.get("user"), PropKit.get("password"));
        MysqlExport mysqlExport = new MysqlExport(mySqlInfo);
        try {
            mysqlExport.export();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

以上代码是从配置文件中读取mysql的配置信息jdbcUrl,user,password,然后封装到MySqlInfo里.

MySqlInfo的代码:

public class MySqlInfo {
    private String jdbcUrl="";
    private String user="";
    private String password="";
    private String exportPath= "dbbak";

    public String getExportPath() {
        return PathKit.getWebRootPath()+"/"+exportPath;
    }

    public void setExportPath(String exportPath) {
        this.exportPath = exportPath;
    }

    public MySqlInfo(String jdbcUrl,  String user, String password) {
        this.jdbcUrl = jdbcUrl;
        this.user = user;
        this.password = password;
    }

    public String getJdbcUrl() {
        return jdbcUrl;
    }

    public void setJdbcUrl(String jdbcUrl) {
        this.jdbcUrl = jdbcUrl;
    }

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}

MysqlExport就是实际上的mysql导出备份类。

MysqlExport代码:

import org.zeroturnaround.zip.ZipUtil;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Objects;


public class MysqlExport {

    private Statement stmt;
    private String database;
    private String generatedSql = "";
    private String sqlFileName = "";
    private String zipFileName = "";
    private File generatedZipFile;
    private MySqlInfo mySqlInfo = null;


    public MysqlExport(MySqlInfo mySqlInfo) {
        this.mySqlInfo = mySqlInfo;
    }


    /**
     * 生成create语句
     *
     * @param table the table concerned
     * @return String
     * @throws SQLException exception
     */
    private String getTableInsertStatement(String table) throws SQLException {

        StringBuilder sql = new StringBuilder();
        ResultSet rs;
        boolean addIfNotExists = true;


        if (table != null && !table.isEmpty()) {
            rs = stmt.executeQuery("SHOW CREATE TABLE " + "`" + table + "`;");
            while (rs.next()) {
                String qtbl = rs.getString(1);
                String query = rs.getString(2);
                sql.append("\n\n--");
                sql.append("\n").append(MysqlUtil.SQL_START_PATTERN).append("  table dump : ").append(qtbl);
                sql.append("\n--\n\n");

                if (addIfNotExists) {
                    query = query.trim().replace("CREATE TABLE", "CREATE TABLE IF NOT EXISTS");
                }

                sql.append(query).append(";\n\n");
            }

            sql.append("\n\n--");
            sql.append("\n").append(MysqlUtil.SQL_END_PATTERN).append("  table dump : ").append(table);
            sql.append("\n--\n\n");
        }

        return sql.toString();
    }


    /**
     * 生成insert语句
     *
     * @param table the table to get inserts statement for
     * @return String generated SQL insert
     * @throws SQLException exception
     */
    private String getDataInsertStatement(String table) throws SQLException {

        StringBuilder sql = new StringBuilder();

        ResultSet rs = stmt.executeQuery("SELECT * FROM " + "`" + table + "`;");

        rs.last();
        int rowCount = rs.getRow();

        if (rowCount <= 0) {
            return sql.toString();
        }

        sql.append("\n--").append("\n-- Inserts of ").append(table).append("\n--\n\n");

        sql.append("\n/*!40000 ALTER TABLE `").append(table).append("` DISABLE KEYS */;\n");

        sql.append("\n--\n")
                .append(MysqlUtil.SQL_START_PATTERN).append(" table insert : ").append(table)
                .append("\n--\n");

        sql.append("INSERT INTO `").append(table).append("`(");

        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();

        for (int i = 0; i < columnCount; i++) {
            sql.append("`")
                    .append(metaData.getColumnName(i + 1))
                    .append("`, ");
        }

        sql.deleteCharAt(sql.length() - 1).deleteCharAt(sql.length() - 1).append(") VALUES \n");

        rs.beforeFirst();
        while (rs.next()) {
            sql.append("(");
            for (int i = 0; i < columnCount; i++) {

                int columnType = metaData.getColumnType(i + 1);
                int columnIndex = i + 1;

                if (Objects.isNull(rs.getObject(columnIndex))) {
                    sql.append("").append(rs.getObject(columnIndex)).append(", ");
                } else if (columnType == Types.INTEGER || columnType == Types.TINYINT || columnType == Types.BIT) {
                    sql.append(rs.getInt(columnIndex)).append(", ");
                } else {

                    String val = rs.getString(columnIndex);
                    val = val.replace("'", "\\'");

                    sql.append("'").append(val).append("', ");
                }
            }


            sql.deleteCharAt(sql.length() - 1).deleteCharAt(sql.length() - 1);

            if (rs.isLast()) {
                sql.append(")");
            } else {
                sql.append("),\n");
            }
        }

        sql.append(";");

        sql.append("\n--\n")
                .append(MysqlUtil.SQL_END_PATTERN).append(" table insert : ").append(table)
                .append("\n--\n");

        //enable FK constraint
        sql.append("\n/*!40000 ALTER TABLE `").append(table).append("` ENABLE KEYS */;\n");

        return sql.toString();
    }


    /**
     * 导出所有表的结构和数据
     *
     * @return String
     * @throws SQLException exception
     */
    private String exportToSql() throws SQLException {

        StringBuilder sql = new StringBuilder();
        sql.append("--");
        sql.append("\n-- Generated by workbgm");
        sql.append("\n-- Date: ").append(new SimpleDateFormat("d-M-Y H:m:s").format(new Date()));
        sql.append("\n--");

        //these declarations are extracted from HeidiSQL
        sql.append("\n\n/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;")
                .append("\n/*!40101 SET NAMES utf8 */;")
                .append("\n/*!50503 SET NAMES utf8mb4 */;")
                .append("\n/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;")
                .append("\n/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;");


        List<String> tables = MysqlUtil.getAllTables(database, stmt);

        for (String s : tables) {
            try {
                sql.append(getTableInsertStatement(s.trim()));
                sql.append(getDataInsertStatement(s.trim()));
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        sql.append("\n/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;")
                .append("\n/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;")
                .append("\n/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;");

        this.generatedSql = sql.toString();
        return sql.toString();
    }

    /**
     * 从jdbcUrl中提取数据库名
     *
     * @param jdbcUrl
     * @return
     */
    public String findDBName(String jdbcUrl) {
        String dbName = "";
        int s = jdbcUrl.lastIndexOf("/") + 1;
        int e = jdbcUrl.lastIndexOf("?");
        dbName = jdbcUrl.substring(s, e);
        return dbName;
    }

    /**
     * 导出入口
     *
     * @throws IOException            exception
     * @throws SQLException           exception
     * @throws ClassNotFoundException exception
     */
    public void export() throws IOException, SQLException, ClassNotFoundException {

        //connect to the database
        database = findDBName(this.mySqlInfo.getJdbcUrl());
        String driverName = "";

        Connection connection;

        connection = MysqlUtil.connectWithURL(this.mySqlInfo.getUser(),
                this.mySqlInfo.getPassword(), this.mySqlInfo.getJdbcUrl(),
                driverName
        );

        stmt = connection.createStatement();

        //generate the final SQL
        String sql = exportToSql();

        //create a temp dir to store the exported file for processing
        String dirName = this.mySqlInfo.getExportPath();
        File file = new File(dirName);
        if (!file.exists()) {
            boolean res = file.mkdir();
            if (!res) {
                System.out.println("Unable to create temp dir: " + file.getAbsolutePath());
                throw new IOException("Unable to create temp dir: " + file.getAbsolutePath());
            }
        }

        //write the sql file out
        File sqlFolder = new File(dirName + "/sql");
        if (!sqlFolder.exists()) {
            boolean res = sqlFolder.mkdir();
            if (!res) {
                throw new IOException(": Unable to create temp dir: " + file.getAbsolutePath());
            }
        }

        sqlFileName = getSqlFilename();
        FileOutputStream outputStream = new FileOutputStream(sqlFolder + "/" + sqlFileName);
        outputStream.write(sql.getBytes());
        outputStream.close();

        //zip the file
        zipFileName = dirName + "/" + sqlFileName.replace(".sql", ".zip");
        generatedZipFile = new File(zipFileName);
        ZipUtil.pack(sqlFolder, generatedZipFile);

        //clear the generated temp files
        clearTempFiles(true);
    }

    /**
     * 清理临时文件
     *
     * @param preserveZipFile bool
     */
    public void clearTempFiles(boolean preserveZipFile) {

        String dirName = this.mySqlInfo.getExportPath();

        //delete the temp sql file
        File sqlFile = new File(dirName + "/sql/" + sqlFileName);
        if (sqlFile.exists()) {
            boolean res = sqlFile.delete();
            System.out.println(": " + sqlFile.getAbsolutePath() + " deleted successfully? " + (res ? " TRUE " : " FALSE "));
        } else {
            System.out.println(": " + sqlFile.getAbsolutePath() + " DOES NOT EXIST while clearing Temp Files");
        }

        File sqlFolder = new File(dirName + "/sql");
        if (sqlFolder.exists()) {
            boolean res = sqlFolder.delete();
            System.out.println(": " + sqlFolder.getAbsolutePath() + " deleted successfully? " + (res ? " TRUE " : " FALSE "));
        } else {
            System.out.println(": " + sqlFolder.getAbsolutePath() + " DOES NOT EXIST while clearing Temp Files");
        }


        if (!preserveZipFile) {

            //delete the zipFile
            File zipFile = new File(zipFileName);
            if (zipFile.exists()) {
                boolean res = zipFile.delete();
                System.out.println(": " + zipFile.getAbsolutePath() + " deleted successfully? " + (res ? " TRUE " : " FALSE "));
            } else {
                System.out.println(": " + zipFile.getAbsolutePath() + " DOES NOT EXIST while clearing Temp Files");
            }

            //delete the temp folder
            File folder = new File(dirName);
            if (folder.exists()) {
                boolean res = folder.delete();
                System.out.println(": " + folder.getAbsolutePath() + " deleted successfully? " + (res ? " TRUE " : " FALSE "));
            } else {
                System.out.println(": " + folder.getAbsolutePath() + " DOES NOT EXIST while clearing Temp Files");
            }
        }

        System.out.println(": generated temp files cleared successfully");
    }

    /**
     * 生成文件名
     * sql file name.
     *
     * @return String
     */
    public String getSqlFilename() {
        return new SimpleDateFormat("yyyy_MM_dd_HH_mm_ss").format(new Date()) + "_" + database + "_database_dump.sql";
    }


    /**
     * 获取备份的sql zip文件
     *
     * @return
     */
    public File getGeneratedZipFile() {
        if (generatedZipFile != null && generatedZipFile.exists()) {
            return generatedZipFile;
        }
        return null;
    }
}

需要注意的是MysqlExport依赖一个zip打包类ZipUtil和mysql工具类MysqlUtil.

MysqlUtil的代码:

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

public class MysqlUtil {


    static final String SQL_START_PATTERN = "-- start";
    static final String SQL_END_PATTERN = "-- end";


    /**
     * 通过jdbcurl连接数据库
     * @param username database username
     * @param password database password
     * @param jdbcURL the user supplied JDBC URL. It's used as is. So ensure you supply the right parameters
     * @param driverName the user supplied mysql connector driver class name
     * @return Connection
     * @throws ClassNotFoundException exception
     * @throws SQLException exception
     */
    static Connection connectWithURL(String username, String password, String jdbcURL, String driverName) throws ClassNotFoundException, SQLException {
        String driver = (Objects.isNull(driverName) || driverName.isEmpty()) ? "com.mysql.jdbc.Driver" : driverName;
        return doConnect(driver, jdbcURL, username, password);
    }

    /**
     * 连接数据库
     * @param driver the class name for the mysql driver to use
     * @param url the url of the database
     * @param username database username
     * @param password database password
     * @return Connection
     * @throws SQLException exception
     * @throws ClassNotFoundException exception
     */
    private static Connection doConnect(String driver, String url, String username, String password) throws SQLException, ClassNotFoundException {
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, username, password);
        System.out.println("DB Connected Successfully");
        return  connection;
    }


    /**
     * 获取所有表
     * @param database the database name
     * @param stmt Statement object
     * @return List\<String\>
     * @throws SQLException exception
     */
    static List<String> getAllTables(String database, Statement stmt) throws SQLException {
        List<String> table = new ArrayList<>();
        ResultSet rs;
        rs = stmt.executeQuery("SHOW TABLE STATUS FROM `" + database + "`;");
        while ( rs.next() ) {
            table.add(rs.getString("Name"));
        }
        return table;
    }

}

ZipUtil需要用maven引入一下:

<dependency>
            <groupId>org.zeroturnaround</groupId>
            <artifactId>zt-zip</artifactId>
            <version>1.12</version>
            <type>jar</type>
        </dependency>


评论区

JFinal

2019-01-16 15:58

以前只见过用 jfinal 从 A 库导到 B 库的玩法,第一次见从库导到外部文件还带压缩功能的,点赞是必须的

穿越123

2019-01-16 17:29

这种备份方式太复杂了,不太推荐,建议直接使用服务器定时任务进行数据库备份

workbgm

2019-01-16 17:55

@穿越123 不要认为所有的人都用别人成熟的云服务平台

穿越123

2019-01-17 09:09

@workbgm 你不觉得你这样很复杂吗,有简单的方式为什么不用,就光你这些代码,我估计每半天弄不出来的,而使用服务器命令备份可能还不用半小时就搞定了

穿越123

2019-01-17 09:11

@workbgm 你这话就说的不对了,什么叫用别人成熟的云服务平台,难道你公司项目不是用linux部署

workbgm

2019-01-17 10:39

semooncun

2019-01-17 11:18

推荐还是shell脚本,jfinal定时任务触发就行了。。你这搞得太复杂了

马小酱

2019-01-17 13:54

支持楼主,很有意义!

zhangshiqiang

2019-01-17 16:39

代码是一方面。。。脚本更简洁一些。

这就是社会

2019-01-24 17:56

你这段代码能够备份某一个表吗

workbgm

2019-01-29 10:09

@这就是社会 库都备份下来,表还有什么问题,自己改一下