JAVA 透過程式手段進行mysql 資料庫備份、資料導出

這是一個java class
用途：以回傳整個string的方式 備份整個mySQL 資料庫
我用這個CLASS來完成透過API發動備份資料庫的行為
而不用MYSQL本身的資料庫備份排程
這再一些情形會使用到
譬如
1.mySQL代管業者 的備份需要額外付費
2.某些情形 需要透過API的方式發動備份行為(mySQL沒有此功能)
3.資料庫備份要寫到異地，當sql本機儲存空間不給備份寫入時，用這種方式引出資料庫content，再用io stream方式存到其他地方。


將CODE複製起來貼到空白文件
 存成MySqlBackup.java
再import到你的JAVA專案即可使用 

FENDER 2017
 */
package toolkit;

import java.io.*;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.log4j.Logger;

/**
 *
 * @author dinuka
 */
public class MySqlBackup {

    private int STREAM_BUFFER = 512000;
    private boolean status = false;
    //initializing the logger  
    static Logger log = Logger.getLogger(MySqlBackup.class.getName());

    /**
     * This method is used for backup the mysql database
     *
     * @param host - hostname - localhost/127.0.0.1
     * @param port - 3306
     * @param user - username
     * @param password - password
     * @param db - database name
     * @param backupfile - file path to backup from the current location ex/
     * "/backup/"
     * @param mysqlDumpExePath - file path to mysqldump.exe from the current
     * location ex/ copy the mysqldump.exe from mysql bin in to backup folder
     * and set the path as backup ex/ "/backup/mysqldump.exe"
     * @return the status of the backup true/false
     */
    public boolean backupDatabase(String host, String port, String user, String password, String db, String backupfile, String mysqlDumpExePath) {
        try {
            // Get MySQL DUMP data
            String dump = getServerDumpData(host, port, user, password, db, mysqlDumpExePath);
            //check the backup dump
            if (status) {
                byte[] data = dump.getBytes();
                // Set backup folder
                String rootpath = System.getProperty("user.dir") + backupfile;

                // See if backup folder exists
                File file = new File(rootpath);
                if (!file.isDirectory()) {
                    // Create backup folder when missing. Write access is needed.
                    file.mkdir();
                }
                // Compose full path, create a filename as you wish
                DateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");
                Date date = new Date();
                String filepath = rootpath + "backup(With_DB)-" + db + "-" + host + "-(" + dateFormat.format(date) + ").sql";
                // Write SQL DUMP file
                File filedst = new File(filepath);
                FileOutputStream dest = new FileOutputStream(filedst);
                dest.write(data);
                dest.close();
                log.info("Backup created successfully for - " + db + " " + host);
            } else {
                //when status false  
                log.error("Could not create the backup for - " + db + " " + host);
            }

        } catch (Exception ex) {
            log.error(ex, ex.getCause());
        }

        return status;
    }

    private String getServerDumpData(String host, String port, String user, String password, String db, String mysqlDumpExePath) {
        StringBuilder dumpdata = new StringBuilder();
        String execlient = "";
        try {
            if (host != null && user != null && password != null && db != null) {
                // Set path. Set location of mysqldump 
                //  For example: current user folder and lib subfolder          

                execlient = System.getProperty("user.dir") + mysqlDumpExePath;

                // Usage: mysqldump [OPTIONS] database [tables]
                // OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
                // OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
                String command[] = new String[]{execlient,
                    "--host=" + host,
                    "--port=" + port,
                    "--user=" + user,
                    "--password=" + password,
                    "--skip-comments",
                    "--databases",
                    db};

                // Run mysqldump
                ProcessBuilder pb = new ProcessBuilder(command);
                Process process = pb.start();

                InputStream in = process.getInputStream();
                BufferedReader br = new BufferedReader(new InputStreamReader(in));

                int count;
                char[] cbuf = new char[STREAM_BUFFER];

                // Read datastream
                while ((count = br.read(cbuf, 0, STREAM_BUFFER)) != -1) {
                    dumpdata.append(cbuf, 0, count);
                }

                //set the status
                int processComplete = process.waitFor();
                if (processComplete == 0) {                   
                    status = true;
                } else {
                    status = false;
                }
                // Close
                br.close();
                in.close();
            }

        } catch (Exception ex) {
            log.error(ex, ex.getCause());
            return "";
        }
        return dumpdata.toString();
    }

    
    public static void main(String[] args) throws SQLException {
        MySqlBackup b = new MySqlBackup();
        // b.backupDatabase("10.100.3.108", "3306", "root", "root", "payroll", "/backup/", "/backup/mysqldump.exe");
        //b.backupDatabase("localhost", "3306", "root", "", "payroll", "/backup/", "/backup/mysqldump.exe");
        //  b.restoreDatabase("root", "", "backup/backup-payroll-10.100.3.108-27-09-2012.sql");
        // b.test();
      //  b.backupDataWithDatabase("C:\\wamp\\bin\\mysql\\mysql5.5.16\\bin\\mysqldump.exe", "localhost", "3306", "root", "", "payroll", "C:/Users/dinuka/Desktop/test/");
         b.backupAllDatabases("C:\\wamp\\bin\\mysql\\mysql5.5.16\\bin\\mysqldump.exe", "localhost", "3306", "root", "", "C:/Users/dinuka/Desktop/test/");
    }

    public boolean backupDataWithOutDatabase(String dumpExePath, String host, String port, String user, String password, String database, String backupPath) {
        boolean status = false;
        try {
            Process p = null;

            DateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");
            Date date = new Date();
            String filepath = "backup(without_DB)-" + database + "-" + host + "-(" + dateFormat.format(date) + ").sql";

            String batchCommand = "";
            if (password != "") {
                //only backup the data not included create database
                batchCommand = dumpExePath + " -h " + host + " --port " + port + " -u " + user + " --password=" + password + " " + database + " -r \"" + backupPath + "" + filepath + "\"";
            } else {
                batchCommand = dumpExePath + " -h " + host + " --port " + port + " -u " + user + " " + database + " -r \"" + backupPath + "" + filepath + "\"";
            }

            Runtime runtime = Runtime.getRuntime();
            p = runtime.exec(batchCommand);
            int processComplete = p.waitFor();

            if (processComplete == 0) {
                status = true;
                log.info("Backup created successfully for without DB " + database + " in " + host + ":" + port);
            } else {
                status = false;
                log.info("Could not create the backup for without DB " + database + " in " + host + ":" + port);
            }

        } catch (IOException ioe) {
            log.error(ioe, ioe.getCause());
        } catch (Exception e) {
            log.error(e, e.getCause());
        }
        return status;
    }

    public boolean backupDataWithDatabase(String dumpExePath, String host, String port, String user, String password, String database, String backupPath) {
        boolean status = false;
        try {
            Process p = null;

            DateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");
            Date date = new Date();
            String filepath = "backup(with_DB)-" + database + "-" + host + "-(" + dateFormat.format(date) + ").sql";

            String batchCommand = "";
            if (password != "") {
                //Backup with database
                batchCommand = dumpExePath + " -h " + host + " --port " + port + " -u " + user + " --password=" + password + " --add-drop-database -B " + database + " -r \"" + backupPath + "" + filepath + "\"";
            } else {
                batchCommand = dumpExePath + " -h " + host + " --port " + port + " -u " + user + " --add-drop-database -B " + database + " -r \"" + backupPath + "" + filepath + "\"";
            }

            Runtime runtime = Runtime.getRuntime();
            p = runtime.exec(batchCommand);
            int processComplete = p.waitFor();


            if (processComplete == 0) {
                status = true;
                log.info("Backup created successfully for with DB " + database + " in " + host + ":" + port);
            } else {
                status = false;
                log.info("Could not create the backup for with DB " + database + " in " + host + ":" + port);
            }

        } catch (IOException ioe) {
            log.error(ioe, ioe.getCause());
        } catch (Exception e) {
            log.error(e, e.getCause());
        }
        return status;
    }

    public boolean backupAllDatabases(String dumpExePath, String host, String port, String user, String password, String backupPath) {
        boolean status = false;
        try {
            Process p = null;

            DateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");
            Date date = new Date();
            String filepath = "backup(with_DB)-All-" + host + "-(" + dateFormat.format(date) + ").sql";

            String batchCommand = "";
            if (password != "") {
                //Backup with database
                batchCommand = dumpExePath + " -h " + host + " --port " + port + " -u " + user + " --password=" + password + " --add-drop-database -A  -r \"" + backupPath + "" + filepath + "\"";
            } else {
                batchCommand = dumpExePath + " -h " + host + " --port " + port + " -u " + user + " --add-drop-database -A  -r \"" + backupPath + "" + filepath + "\"";
            }

            Runtime runtime = Runtime.getRuntime();
            p = runtime.exec(batchCommand);
            int processComplete = p.waitFor();


            if (processComplete == 0) {
                status = true;
                log.info("Backup created successfully with All DBs in " + host + ":" + port);
            } else {
                status = false;
                log.info("Could not create the backup for All DBs in " + host + ":" + port);
            }

        } catch (IOException ioe) {
            log.error(ioe, ioe.getCause());
        } catch (Exception e) {
            log.error(e, e.getCause());
        }
        return status;
    }

    /**
     * Restore the backup into a local database
     *
     * @param dbUserName - user name
     * @param dbPassword - password
     * @param source - backup file
     * @return the status true/false
     */
    public boolean restoreDatabase(String dbUserName, String dbPassword, String source) {

        String[] executeCmd = new String[]{"mysql", "--user=" + dbUserName, "--password=" + dbPassword, "-e", "source " + source};

        Process runtimeProcess;
        try {
            runtimeProcess = Runtime.getRuntime().exec(executeCmd);
            int processComplete = runtimeProcess.waitFor();

            if (processComplete == 0) {
                log.info("Backup restored successfully with " + source);
                return true;
            } else {
                log.info("Could not restore the backup " + source);
            }
        } catch (Exception ex) {
            log.error(ex, ex.getCause());
        }

        return false;

    }
}


See also :

留言

張貼留言

這個網誌中的熱門文章

Office 2021 離線安裝封裝與KMS啟動步驟

圖片
1.到Office 365 Customization Tool   https://config.office.com/deploymentsettings  選擇建立新的配置 Office來源套件選擇Office LTSC 標準版2021大量授權 OR 其他你想要的版本   剩下根據你的安裝配置進行設定 在安裝選項 改成本機來源 (這邊選擇安裝環境的安裝檔案路徑 可以設定網路磁碟如 \\192.168.0.1\office2021) 完成後匯出設定檔案 會得到一個XML檔案  這就是你office 的安裝組態檔案  這邊命名為office.xml 我的組態檔案像這樣 <Configuration ID="4b609851-a8f8-4cce-bf99-22096f57e715">   <Add OfficeClientEdition="64" Channel="PerpetualVL2021" SourcePath="C:\PROPLUS" AllowCdnFallback="TRUE">     <Product ID="ProPlus2021Volume" PIDKEY="FXYTK-NJJ8C-GB6DW-3DYQT-6F7TH">       <Language ID="zh-tw" />       <ExcludeApp ID="Lync" />       <ExcludeApp ID="OneDrive" />     </Product>   </Add>   <Property Name="SharedComputerLicensing" Value="0" />   <Property Name="SCLCacheOverride" Value="0" />   <Property Name="AUTOACTIVATE" ...
閱讀完整內容

Ollama使用心得與模型導入教學

# 前言 隨著大語言模型的普及，在本地運行AI模型已成為許多開發者和研究者的需求。Ollama作為一款開源的本地AI模型運行平台，為我們提供了便捷的解決方案。經過一段時間的使用，我想分享一些心得與實戰經驗。 # 一、Ollama的主要功能與定位 Ollama是一個專為本地運行而設計的大語言模型平台，主要特色包括： **核心功能：** - 支援多種開源模型（LLaMA、Mistral、CodeLlama等） - 提供簡潔的命令列介面 - 支援模型量化，減少記憶體使用 - 內建API伺服器，方便整合到應用程式中 **定位優勢：** - 完全離線運行，保護隱私 - 不依賴雲端服務，降低使用成本 - 適合開發測試環境 - 支援自定義模型導入 # 二、安裝與Cuda加速設定 ## 2.1 基本安裝 **Windows系統：** ```bash # 下載並安裝Ollama winget install Ollama.Ollama ``` **Linux系統：** ```bash curl -fsSL https://ollama.com/install.sh | sh ``` ## 2.2 Cuda加速設定 要充分利用GPU加速，需要確保系統已安裝NVIDIA驅動和CUDA toolkit： **檢查Cuda狀態：** ```bash # 檢視GPU使用情況 nvidia-smi -l 1 ``` **確認Cuda版本：** ```bash nvcc --version ``` **Ollama會自動偵測Cuda環境**，如果安裝正確，啟動時會顯示GPU加速已啟用。 # 三、模型取得與匯入方式 ## 3.1 官方模型下載 ```bash # 下載熱門模型 ollama pull llama2 ollama pull mistral ollama pull codellama ``` ## 3.2 自定義模型導入 ### 從Hugging Face下載GGUF檔案 1. **找到適合的模型：**    - 前往 huggingface.co    - 搜尋支援GGUF格式的模型    - 下載.gguf檔案 2. **創建Modelfile：** ```bash # 創建Modelfile echo 'FROM ./you...
閱讀完整內容

ARC下NSMutableDictionary 無法使用retainCount

今天用到 NSMutableDictionary 在alloc之後 很自然的就補了release 想要觀察有沒有放乾淨 所以使用了retainCount卻發生了錯誤 NSMutableDictionary* jsonObject = [[NSMutableDictionary alloc] init] ; [jsonObject setObject:account forKey:@"username"]; [jsonObject setObject:password forKey:@"password"]; [jsonObject setObject:@"2" forKey:@"source"]; NSString *jsonInput = [jsonObject JSONRepresentation]; NSLog(@"jsonObject RetanCount:%@", [jsonObject retainCount]); //Bad_Access Here 爬了一下文，原來arc在建立NSMutableDictionary這類的物件時。便已經會自動使用autorelease 因此不支援retainCount 值得注意的是 當NSMutableDictionary裡面每增加一個物件(KEY)時 該NSMutableDictionary的retain便會自動加一 不過在新的arc功能下 已經不用那麼在意retain值了。     You shouldn't use -retainCount to figure out what is going on. The Apple docs specifically state:     Important: This method is typically of no value in debugging memory management issues. Because any number of framework objects may have retained an object in order to hold refer...
閱讀完整內容