場(chǎng)景

在Java代碼中調(diào)用mysqldump命令實(shí)現(xiàn)對(duì)指定的mysql數(shù)據(jù)庫(kù)和指定的表導(dǎo)出為sql文件。

并將sql文件進(jìn)行壓縮成zip存儲(chǔ)備份。

mysqldump 簡(jiǎn)介

mysqldump 是 MySQL 自帶的邏輯備份工具。

它的備份原理是通過(guò)協(xié)議連接到 MySQL 數(shù)據(jù)庫(kù)，將需要備份的數(shù)據(jù)查詢(xún)出來(lái)，將查詢(xún)出的數(shù)據(jù)轉(zhuǎn)換成對(duì)應(yīng)的insert 語(yǔ)句，當(dāng)我們需要還原這些數(shù)據(jù)時(shí)，只要執(zhí)行這些 insert 語(yǔ)句，即可將對(duì)應(yīng)的數(shù)據(jù)還原。

要想使用我們需要找到mysql安裝目錄下的bin下的mysqldump.exe

?

因?yàn)闆](méi)有將其添加到環(huán)境變量中，所以需要找到其所在的全路徑。

注：

博客：
https://blog.csdn.net/badao_liumang_qizhi
關(guān)注公眾號(hào)
霸道的程序猿
獲取編程相關(guān)電子書(shū)、教程推送與免費(fèi)下載。

實(shí)現(xiàn)

首先需要聲明一些執(zhí)行mysqldump的變量

??? private static String hostIP = "127.0.0.1";private static String userName = "root";private static String password = "123456";//sql文件存儲(chǔ)的路徑private static String savePath = "D:/bak";//sql文件存儲(chǔ)名private static String fileName = "badaoBak"+new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())+".sql";//數(shù)據(jù)庫(kù)名private static String databaseName = "test";private static final int BUFFER = 8192;//zip壓縮包存儲(chǔ)路徑private static String zipPath = "D:/bak/badao.zip";

然后新建方法用語(yǔ)執(zhí)行sql的導(dǎo)出

??? /*** 執(zhí)行數(shù)據(jù)備份* @return*/public static String dataBakExec(){String sqlFilePath = "";File saveFile = new File(savePath);// 如果目錄不存在if (!saveFile.exists()) {// 創(chuàng)建文件夾saveFile.mkdirs();}if(!savePath.endsWith(File.separator)){savePath = savePath + File.separator;}PrintWriter printWriter = null;BufferedReader bufferedReader = null;try {printWriter = new PrintWriter(new OutputStreamWriter(new FileOutputStream(savePath + fileName), "utf8"));sqlFilePath= savePath + fileName;//導(dǎo)出指定數(shù)據(jù)庫(kù)指定表的結(jié)構(gòu)和數(shù)據(jù)Process process = Runtime.getRuntime().exec("C:/Program Files/MySQL/MySQL Server 5.6/bin/mysqldump -h" + hostIP + " -u" + userName + " -p" + password + " --set-charset=UTF8 " + databaseName +" book ");//導(dǎo)出指定數(shù)據(jù)庫(kù)指定表的結(jié)構(gòu)//Process process = Runtime.getRuntime().exec("C:/Program Files/MySQL/MySQL Server 5.6/bin/mysqldump -h" + hostIP + " -u" + userName + " -p" + password + " --set-charset=UTF8 " + databaseName + " book -d");//導(dǎo)出指定數(shù)據(jù)庫(kù)指定表符合條件的結(jié)構(gòu)和數(shù)據(jù)//Process process = Runtime.getRuntime().exec("C:/Program Files/MySQL/MySQL Server 5.6/bin/mysqldump -h" + hostIP + " -u" + userName + " -p" + password + " --set-charset=UTF8 " + databaseName +" book "+" --where=\" price> 100" + "\" ");InputStreamReader inputStreamReader = new InputStreamReader(process.getInputStream(), "utf8");bufferedReader = new BufferedReader(inputStreamReader);String line;while((line = bufferedReader.readLine())!= null){printWriter.println(line);}printWriter.flush();//0 表示線程正常終止。if(process.waitFor() == 0){System.out.println("備份數(shù)據(jù)成功");}}catch (Exception e) {e.printStackTrace();} finally {try {if (bufferedReader != null) {bufferedReader.close();}if (printWriter != null) {printWriter.close();}} catch (IOException e) {e.printStackTrace();}}return? sqlFilePath;}

注意把這里的mysqldump的路徑改為自己的路徑。

執(zhí)行的命令如果不加具體的數(shù)據(jù)庫(kù)則導(dǎo)出所有的表，數(shù)據(jù)庫(kù)后面加表明則是導(dǎo)出具體的表。

并且還可以選擇導(dǎo)出表的結(jié)構(gòu)和數(shù)據(jù)以及符合要求的表數(shù)據(jù)。

具體自行搜索musqldump命令。

備份sql效果

?

sql備份成功后將其路徑返回，然后再新建一個(gè)生成zip壓縮包的方法

?? /*** 壓縮sql文件為zip* @param filePath sql文件路徑* @param zipPath? 要生成的zip壓縮包路徑*/public static void zipFile(String filePath,String zipPath) {ZipOutputStream out = null;try {out = new ZipOutputStream(new FileOutputStream(zipPath));} catch (FileNotFoundException e) {e.printStackTrace();}//得到文件列表信息File file = new File(filePath);// 壓縮zip包try {if (!file.exists()) {return;}BufferedInputStream bis = new BufferedInputStream(new FileInputStream(file));try {ZipEntry entry = new ZipEntry(file.getName());out.putNextEntry(entry);int count;byte data[] = new byte[BUFFER];while ((count = bis.read(data, 0, BUFFER)) != -1) {out.write(data, 0, count);}} catch (Exception e) {throw new RuntimeException(e);}finally {out.closeEntry();bis.close();}}catch (Exception e){e.printStackTrace();}finally {try {out.close();} catch (IOException e) {e.printStackTrace();}}System.out.println("生成zip成功");}

然后完整的main方法示例代碼

package com.badao.mysqlbak;import java.io.*; import java.text.SimpleDateFormat; import java.util.Date; import java.util.zip.ZipEntry; import java.util.zip.ZipOutputStream;public class MysqlBakMain {private static String hostIP = "127.0.0.1";private static String userName = "root";private static String password = "123456";//sql文件存儲(chǔ)的路徑private static String savePath = "D:/bak";//sql文件存儲(chǔ)名private static String fileName = "badaoBak"+new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())+".sql";//數(shù)據(jù)庫(kù)名private static String databaseName = "test";private static final int BUFFER = 8192;//zip壓縮包存儲(chǔ)路徑private static String zipPath = "D:/bak/badao.zip";public static void main(String[] args) {String sqlFilePath = dataBakExec();System.out.println("備份的sql文件保存路徑為:"+sqlFilePath);zipFile(sqlFilePath,zipPath);}/*** 執(zhí)行數(shù)據(jù)備份* @return*/public static String dataBakExec(){String sqlFilePath = "";File saveFile = new File(savePath);// 如果目錄不存在if (!saveFile.exists()) {// 創(chuàng)建文件夾saveFile.mkdirs();}if(!savePath.endsWith(File.separator)){savePath = savePath + File.separator;}PrintWriter printWriter = null;BufferedReader bufferedReader = null;try {printWriter = new PrintWriter(new OutputStreamWriter(new FileOutputStream(savePath + fileName), "utf8"));sqlFilePath= savePath + fileName;//導(dǎo)出指定數(shù)據(jù)庫(kù)指定表的結(jié)構(gòu)和數(shù)據(jù)Process process = Runtime.getRuntime().exec("C:/Program Files/MySQL/MySQL Server 5.6/bin/mysqldump -h" + hostIP + " -u" + userName + " -p" + password + " --set-charset=UTF8 " + databaseName +" book ");//導(dǎo)出指定數(shù)據(jù)庫(kù)指定表的結(jié)構(gòu)//Process process = Runtime.getRuntime().exec("C:/Program Files/MySQL/MySQL Server 5.6/bin/mysqldump -h" + hostIP + " -u" + userName + " -p" + password + " --set-charset=UTF8 " + databaseName + " book -d");//導(dǎo)出指定數(shù)據(jù)庫(kù)指定表符合條件的結(jié)構(gòu)和數(shù)據(jù)//Process process = Runtime.getRuntime().exec("C:/Program Files/MySQL/MySQL Server 5.6/bin/mysqldump -h" + hostIP + " -u" + userName + " -p" + password + " --set-charset=UTF8 " + databaseName +" book "+" --where=\" price> 100" + "\" ");InputStreamReader inputStreamReader = new InputStreamReader(process.getInputStream(), "utf8");bufferedReader = new BufferedReader(inputStreamReader);String line;while((line = bufferedReader.readLine())!= null){printWriter.println(line);}printWriter.flush();//0 表示線程正常終止。if(process.waitFor() == 0){System.out.println("備份數(shù)據(jù)成功");}}catch (Exception e) {e.printStackTrace();} finally {try {if (bufferedReader != null) {bufferedReader.close();}if (printWriter != null) {printWriter.close();}} catch (IOException e) {e.printStackTrace();}}return? sqlFilePath;}/*** 壓縮sql文件為zip* @param filePath sql文件路徑* @param zipPath? 要生成的zip壓縮包路徑*/public static void zipFile(String filePath,String zipPath) {ZipOutputStream out = null;try {out = new ZipOutputStream(new FileOutputStream(zipPath));} catch (FileNotFoundException e) {e.printStackTrace();}//得到文件列表信息File file = new File(filePath);// 壓縮zip包try {if (!file.exists()) {return;}BufferedInputStream bis = new BufferedInputStream(new FileInputStream(file));try {ZipEntry entry = new ZipEntry(file.getName());out.putNextEntry(entry);int count;byte data[] = new byte[BUFFER];while ((count = bis.read(data, 0, BUFFER)) != -1) {out.write(data, 0, count);}} catch (Exception e) {throw new RuntimeException(e);}finally {out.closeEntry();bis.close();}}catch (Exception e){e.printStackTrace();}finally {try {out.close();} catch (IOException e) {e.printStackTrace();}}System.out.println("生成zip成功");}}

運(yùn)行效果

?

?

總結(jié)

以上是生活随笔為你收集整理的Java中使用mysqldump实现mysql数据库备份并将sql文件打成zip压缩包的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。