Mysql database backup

Posted by mgason on Wed, 07 Aug 2019 13:29:17 +0200

Backup database
1. Using the cmd command line to perform data backup:
Backup command code format:
Mysqldump-h database server IP-P database uses port number-u account-p password database name > save path
mysqldump -h127.0.0.1 -p3306 -uroot -proot zbsqx>D:\MysqlBackup\20190807.sql
Enter the above code on the command line:

The above example is using root user to backup database zbsqx, database port 3306, database locally located (IP:
There is a backup file named 20190807.sql under the Mysql Backup path of the D disk after execution.

If the prompt is not an internal or external command,
The main reason is that the mysqldump command is not executed in the bin directory of mysql.
Because I configure the environment variables of msyql, I can execute the mysqldump command in the default directory of the command window.
Add the bin folder path under mysql to the path bar of the environment variable.
For example, I am: C: Program Files MYSQL MySQL Server 5.7 bin\

2. Using Java code to achieve database backup:
In the project, the database will be constantly modified! So keep backing up the data! If the modified database has problems, you can also use the previously backed-up data for temporary use! But using mysql select command to export data tables is not practical, because only a single table can be exported at a time, and there is a database privilege problem.
So the best choice is to use the mysqldump command, and use Java.lang.Runtime, the runtime class of java, to use the background window. Using java code to achieve data backup function is actually through Java to write commands to the command line to execute, so it is recommended to first simulate backup in cmd, after successful testing, backup using java code


   public class MysqlDataBackup {

   	 //Call the backupdata method through the main method
   		public static void main(String[] args) throws Exception{
   			try {
   				if (backupdata("", "root", "root", "D:/MysqlBackup", "2019-08-06.sql", "test")) {
   					System.out.println("Data backup was successful!");
   				} else {
   					System.out.println("Data backup failed!");
   			} catch (InterruptedException e) {
   	 * Java Code Implementing MySQL Database Export
   	 * @param hostIP MySQL The address IP of the server where the database is located
   	 * @param userName Database username
   	 * @param password Database password
   	 * @param savePath Database Export File Save Path
   	 * @param fileName Database export file name
   	 * @param databaseName The name of the database to export
   	 * @return Returning true indicates that the export was successful, otherwise returning false.
   	public static boolean backupdata(String hostIP, String userName, String password,  
   			String savePath,String fileName, String databaseName) throws Exception {		
   		File saveFile = new File(savePath);//Get the file path name
   		if (!saveFile.exists()) {saveFile.mkdirs();}//Determine whether the file exists! Create folders if the directory does not exist
   		//SavePath. endsWith (character) tests whether the string ends with the specified suffix
   		if(!savePath.endsWith(File.separator)){savePath = savePath + File.separator;}

   		//Create a processing stream to get the location where. sql files are saved
   		PrintWriter printWriter = new PrintWriter(new OutputStreamWriter(new FileOutputStream(savePath + fileName), "utf8"));
   		//Splice a command and use Runtime to use the background
   		Process process = Runtime.getRuntime().exec(" mysqldump -h" + hostIP + " -u" + userName + " -p" +
   		                                             password + " --set-charset=UTF8 " + databaseName);
   		//Get spliced strings converted to stream form
   		InputStreamReader inputStreamReader = new InputStreamReader(process.getInputStream(), "utf8");
   		//Create a buffer stream and read in. sql files to the cache stream
   		BufferedReader bufferedReader = new BufferedReader(inputStreamReader);
           String line;  
           //Reuse the processing stream for print output
           while((line = bufferedReader.readLine())!= null){
           //Refresh processing flow
           //If the stitching can be executed properly, it returns to a successful state! 0 means that the thread terminates normally.
   		if(process.waitFor() == 0){
   			return true;
   		//Closing flow

   		return false;

Implementation results:

Topics: Database MySQL Java mysqldump