現在的應用系統越來越多的都是業務系統與資料庫系統是分離的，這裡就會涉及到資料庫備份的問題。如果業務系統與資料庫是在同一伺服器，可以很簡單的用mysql自帶的指令：mysqldump --databases dbName > dbNameBak.sql
現在是資料庫伺服器與應用系統不在同一台伺服器，我們可以通過JDBC方式進行備份資料庫操作，
代碼如下：
備份資料庫主要方法：backDB
public void backupDB() {
Connection conn = null;
String dbName = null;
try {
conn = jdbcDao.getDataSource().getConnection();
dbName = conn.getCatalog();
} catch (SQLException e) {
throw new RuntimeException("無法擷取資料庫連接配接！");
}
String tableName = null,
procName = null;
BufferedWriter writer = null;
Statement stmtInfo = null, stmtData = null;
ResultSet rsInfo = null, rsData = null;
try {
//存放檔案目錄
String folderBackup = CommonUtils.absoluteClassPathUsrPath(Constants.FOLDER_BACKUP);
//檔案名
String sqlFilename = String.format("%s-%s.sql", dbName, Dates.dateToString(new Date(), Constants.DATETIME_FORMAT_FILENAME));
writer = new BufferedWriter(new FileWriter(folderBackup + Constants.PATH_SEPARATOR + sqlFilename));
writer.write(";");
writer.newLine();
writer.write(";");
writer.newLine();
writer.write(";");
writer.newLine();
writer.write(";");
writer.newLine();
writer.write(";");
writer.newLine();
writer.write(";");
writer.flush();
stmtInfo = conn.createStatement();
rsInfo = stmtInfo.executeQuery(String.format("SHOW FULL TABLES FROM `%s` WHERE TABLE_TYPE = 'BASE TABLE'", dbName));
// 周遊所有表
while(rsInfo.next()) {
tableName = rsInfo.getString(1);
this.dbBackExportTable(conn, tableName, writer, true);
} //end for tables
if(null != rsInfo) {
rsInfo.close();
}
if(null != stmtInfo) {
stmtInfo.close();
}
stmtInfo = conn.createStatement();
rsInfo = stmtInfo.executeQuery(String.format("SELECT `SPECIFIC_NAME` from `INFORMATION_SCHEMA`.`ROUTINES` WHERE `ROUTINE_SCHEMA` = '%s' AND ROUTINE_TYPE = 'PROCEDURE'; ", dbName));
while (rsInfo.next()) {
procName = rsInfo.getString(1);
stmtData = conn.createStatement();
rsData = stmtData.executeQuery(String.format("SHOW CREATE PROCEDURE `%s`", procName));
if(!rsData.next()) {
continue ;
}
writer.newLine();
writer.newLine();
writer.write(String.format("", procName));
writer.newLine();
writer.write(String.format(";", procName));
writer.newLine();
writer.write("DELIMITER $$");
writer.newLine();
writer.append("$$");
writer.newLine();
writer.write("DELIMITER ;");
if(null != rsData) {
rsData.close();
}
if(null != stmtData) {
stmtData.close();
}
}
writer.newLine();
writer.newLine();
writer.write(";");
writer.newLine();
writer.write(";");
writer.newLine();
writer.write(";");
writer.newLine();
writer.write(";");
writer.newLine();
writer.close();
if(null != stmtData) {
stmtData.close();
}
if(null != rsData) {
rsData.close();
}
if(null != stmtInfo) {
stmtInfo.close();
}
if(null != rsInfo) {
rsInfo.close();
}
} catch (Exception e) {
throw new RuntimeException(e.getMessage());
}
}
備份具體表資料
private void dbBackExportTable(Connection conn, String tableName, BufferedWriter writer, boolean bulkFlag) throws SQLException, IOException {
Statement stmt = null;
ResultSet rs = null;
stmt = conn.createStatement();
rs = stmt.executeQuery(String.format("SHOW CREATE TABLE `%s`", tableName));
if(!rs.next()) {
return ;
}
writer.newLine();
writer.newLine();
writer.write(String.format("", tableName));
writer.newLine();
writer.write(String.format("DROP TABLE IF EXISTS `%s`;", tableName));
writer.newLine();
writer.write(rs.getString(2) + ";");
writer.newLine();
if(null != rs) {
rs.close();
}
if(null != stmt) {
stmt.close();
}
// 先擷取記錄數
stmt = conn.createStatement();
rs = stmt.executeQuery(String.format("SELECT COUNT(1) FROM `%s`", tableName));
int rowCount = rs.next() ? rs.getInt(1) : 0;
if(0 >= rowCount) {
writer.flush();
return ;
}
writer.write(String.format("", tableName));
writer.newLine();
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);
stmt.setFetchDirection(ResultSet.FETCH_REVERSE);
rs = stmt.executeQuery(String.format("SELECT * FROM `%s`", tableName));
int colCount = 0;
Object colValue = null;
// 所有資料用","連接配接
if(!bulkFlag) {
while(rs.next()) {
colCount = rs.getMetaData().getColumnCount();
writer.write(String.format("INSERT INTO `%s` VALUES (", tableName));
// 擷取表每一列資料
for(int j = 0; j < colCount; j ++) {
if(j > 0) {
writer.write(',');
}
colValue = rs.getObject(j + 1);
if(null != colValue) {
writer.write(String.format("'%s'", CommonUtils.escapeString(colValue.toString())));
} else {
writer.write("NULL");
}
} //end for one record columns
writer.write(");");
writer.newLine();
writer.flush();
} //end for table records
}
// 每行資料獨立分開
else {
ResultSetMetaData rsMetaData = null;
int counter = 0;
while(rs.next()) {
++ counter;
rsMetaData = rs.getMetaData();
colCount = rsMetaData.getColumnCount();
// 第一條記錄，則列出列名
if(1 == counter) {
writer.write(String.format("INSERT INTO `%s` (", tableName));
for(int i = 0; i < colCount; i ++) {
if(i > 0) {
writer.write(",");
}
writer.append('`').append(rsMetaData.getColumnName(i + 1)).append('`');
}
writer.append(") VALUES ");
}
// 擷取表每一列資料
for(int j = 0; j < colCount; j ++) {
writer.write((0 >= j) ? '(' : ',');
colValue = rs.getObject(j + 1);
if(null != colValue) {
writer.write(String.format("'%s'", CommonUtils.escapeString(colValue.toString())));
} else {
writer.write("NULL");
}
} //end for one record columns
// 是否是最後記錄
if(rowCount > counter) {
writer.write("),");
} else {
writer.write(");");
}
writer.flush();
} //end for table records
}
if(null != rs) {
rs.close();
}
if(null != stmt) {
stmt.close();
}
}
說明：上面用到的處理資料方法CommonUtils.escapeString(String x)))，請參看文章：MySQL字元轉義涉及的問題及解決
需要注意的是，從資料庫擷取的原資料如果涉及單、雙引号，雖然不會影響導出的sql内容，但是導出的内容就可能無法正常再導入到原資料庫，原因就是資料内容包含有單雙引号(PS：此處排查浪費了近2天時間)
待完善内容：上面所有的導出sql相關内容都放在一個StringBuilder對象中， 對于資料量不大并且記憶體足夠的情況下沒有問題，但是如果資料庫資料比較大或者配置設定給java記憶體有限，可能導緻記憶體不夠而報錯java.lang.OutOfMemoryError: Java heap space。
參考文章：