# mysql-backup-restore

**Repository Path**: nichenxyx/mysql-backup-restore

## Basic Information

- **Project Name**: mysql-backup-restore
- **Description**: MySQL数据库的备份与恢复
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 1
- **Forks**: 0
- **Created**: 2019-11-11
- **Last Updated**: 2020-12-19

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# MySQL Backup Restore

MySQL数据库的备份与恢复， 项目中需要对系统数据进行备份和恢复， 要求能在界面上点击进行自主备份和恢复， 所以有了这篇文章， 我把其中不涉及业务的部分摘了出来， 做了一下整理, 全部源代码在https://gitee.com/nichenxyx/mysql-backup-restore

先介绍一下整体思路

在使用JDBC去做系统数据的整体备份和删除是不太好做的， 更何况封装后的JPA呢， 所以使用原生的方式反而更方便

我在MySQL-5.7.22版本下将mysql命令程序和mysqldump命令程序直接拷贝了出来， 作为程序备份和恢复的基础工具并写了两个脚本执行相关业务逻辑的东西

下面说一下两个脚本，最后再说一下Java的逻辑

## mysql-backup.sh -- 备份脚本

用法

```
 -h host MySQL所在主机
 -p port (可选参数)MySQL的端口, 默认3306
 -u user 连接MySQL的用户
 -P password 连接MySQL的密码
 -d database 需要备份的数据库
 -n ignore tables (可选参数)需要忽略的表, 如果有多个, 使用英文逗号分隔
```

示例

```
./mysql-bakup.sh -h 192.168.1.2 -p 3306 -u root -P password -d aa -n menu,role
```

结果

会以当前日期生成一个`-finished.tar.gz`结尾的文件

全部源代码

```shell
#!/bin/bash

#   desc: 备份数据库
# author: zhuliang

while getopts 'h:p:u:P:d:n' OPT; do
	case $OPT in
		h)
			host="$OPTARG";;
		p)
			port="$OPTARG";;
		u)
			user="$OPTARG";;
		P)
			password="$OPTARG";;
		d)
			database="$OPTARG";;
		n)
		    ignoreTables="$OPTARG";;
		?)
			echo "$OPTARG"
			echo -e "Usage: `basename $0` [options] optionsValue \n -h host\n -p port\n -u user\n -P password\n -d database\n -n ignore tables, optional, if has multiple value, split with \",\""
			exit 0
	esac
done

if [[ -z $host ]]; then
	echo "-h parameter missed, for help add -h parameter"
	exit 1
fi
if [[ -z $user ]]; then
	echo "-u parameter missed, for help add -h parameter"
	exit 3
fi
if [[ -z $password ]]; then
	echo "-P parameter missed, for help add -h parameter"
	exit 4
fi
if [[ -z $database ]]; then
	echo "-d parameter missed, for help add -h parameter"
	exit 5
fi

if [[ -z $port ]]; then
	port=3306
fi

# 进入命令所在目录
cd `dirname $0` 

chmod +x ./mysqldump

# 拼接需要执行的命令
cmd="./mysqldump --host=$host --port=$port --user=$user --password=$password --databases $database --allow-keywords --compress --force --hex-blob --quick"

ignoreArray=(${ignoreTables//,/ })
for table in ${ignoreArray[@]}
do
	cmd="$cmd --ignore-table=$database.$table "
done

echo $cmd

date=`date +"%Y%m%d%H%M%S"`
logfile=backup.log

date +"%Y-%m-%d %H:%M:%S Start backup database..." >> $logfile

sqlFile=$date.sql
tarFile=$date.tar.gz

# 执行命令并将结果保存
$cmd > $sqlFile

tar zcvf $tarFile $sqlFile

rm -rf $sqlFile

# 最终以finished.tar.gz结尾才算是最终备份成功
mv $tarFile $date-finished.tar.gz

date +"%Y-%m-%d %H:%M:%S Finished backup database" >> $logfile

```



## mysql-restore.sh -- 恢复脚本

```
-h host MySQL所在主机
-p port (可选参数)MySQL的端口, 默认3306
-u user 连接MySQL的用户
-P password 连接MySQL的密码
-d database 需要恢复的数据库
-f sql tar file 需要用于恢复的压缩文件
```

示例

```
./mysql-restore.sh -h 192.168.1.2 -p 3306 -u root -P password -d aa -f20191111062402-finished.tar.gz
```

源代码

```shell
#!/bin/bash

#   desc: 恢复数据
# author: zhuliang

while getopts 'h:p:u:P:d:f:' OPT; do
        case $OPT in
                h)
                        host="$OPTARG";;
                p)
                        port="$OPTARG";;
                u)
                        user="$OPTARG";;
                P)
                        password="$OPTARG";;
                d)
                        database="$OPTARG";;
                f)
                        sqlTarFile="$OPTARG";;
                ?)
                        echo -e "Usage: `basename $0` [options] optionsValue \n -h host\n -p port\n -u user\n -P password\n -d database\n -f sql tar file"
                        exit 0
        esac
done

if [[ -z $host ]]; then
        echo "-h parameter missed, for help add -h parameter"
        exit 1
fi
if [[ -z $user ]]; then
        echo "-u parameter missed, for help add -h parameter"
        exit 3
fi
if [[ -z $password ]]; then
        echo "-P parameter missed, for help add -h parameter"
        exit 4
fi
if [[ -z $database ]]; then
        echo "-d parameter missed, for help add -h parameter"
        exit 5
fi
if [[ -z $sqlTarFile ]]; then
        echo "-f parameter missed, for help add -h parameter"
        exit 6
fi

if [[ -z $port ]]; then
	port=3306
fi

cd `dirname $0`

date=`date +"%Y%m%d-%H%M%S"`
logfile=backup.log

date +"%Y-%m-%d %H:%M:%S Start restore data..." >> $logfile

sqlFile=`uuidgen`.sql
tar zxvf $sqlTarFile -O > $sqlFile

chmod +x ./mysql
./mysql --host=$host --port=$port --user=$user --password=$password $database < $sqlFile

rm -rf $sqlFile

date +"%Y-%m-%d %H:%M:%S Finished restore data" >> $logfile

```



## BackupRestoreService --Java调用

先看一下代码

````java
package com.vhengdata.service;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.util.StrUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Service;

import java.io.*;
import java.sql.*;
import java.util.*;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

@Service
@Slf4j
public class BackupRestoreService {

    private final String BACKUP_POSTFIX = "-finished.tar.gz";
    private volatile boolean backuping = false;
    private volatile boolean restoring = false;

    @Value("${spring.datasource.url:}")
    private String jdbcUrl;

    @Value("${spring.datasource.username:}")
    private String username;

    @Value("${spring.datasource.password:}")
    private String password;


    public List<String> getBackups() {
        String backupPath = "backup";
        File backupFolder = new File(backupPath);
        String[] backups = backupFolder.list(new FilenameFilter() {
            @Override
            public boolean accept(File dir, String name) {
                return name.endsWith(BACKUP_POSTFIX);
            }
        });
        List<String> backList = CollUtil.toList(backups).stream()
                .map(filename -> filename.replace(BACKUP_POSTFIX, ""))
                .sorted((b1, b2) -> b2.compareToIgnoreCase(b1))
                .collect(Collectors.toList());

        return backList;
    }

    public void backupData() {
        if (backuping) {
            throw new ServiceException(ResultEnum.TASK_BASY.getCode(), "当前正在进行备份操作, 请稍后再试");
        }

        if (restoring) {
            throw new ServiceException(ResultEnum.TASK_BASY.getCode(), "当前正在进行数据恢复操作, 请稍后再试");
        }

        try {

            backuping = true;

            String configBackupFolder = fileConfig.getBackup();
            File configBackupFolderFile = new File(configBackupFolder);
            if (!configBackupFolderFile.exists()) {
                configBackupFolderFile.mkdirs();
            }

            // 获取参数
            if (StrUtil.isEmpty(jdbcUrl)) {
                throw new ServiceException(ResultEnum.PARAM_DATA_ERROR.getCode(), "数据库配置不正确");
            }
            String host, port, db, user, pwd;
            Map<String, String> dbConfigMap = readConfigFromJdbcUrl(jdbcUrl);
            host = dbConfigMap.get("host");
            port = dbConfigMap.get("port");
            db = dbConfigMap.get("db");
            if (dbConfigMap.containsKey("user")) {
                user = dbConfigMap.get("user");
            } else {
                user = username;
            }

            if (dbConfigMap.containsKey("password")) {
                pwd = dbConfigMap.get("password");
            } else {
                pwd = password;
            }

            String shellpath = configBackupFolder + "/mysql-bak.sh";
            File shellFile = new File(shellpath);
            if (!FileUtil.exist(shellFile)) {
                InputStream inputStream = new ClassPathResource("mysql/mysql-bak.sh").getInputStream();
                FileOutputStream outputStream = new FileOutputStream(shellFile);
                IoUtil.copy(inputStream, outputStream);
                inputStream.close();
                outputStream.close();
            }
            String mysqlpath = configBackupFolder + "/mysqldump";
            if (!FileUtil.exist(mysqlpath)) {
                InputStream inputStream = new ClassPathResource("mysql/mysqldump").getInputStream();
                FileOutputStream outputStream = new FileOutputStream(new File(mysqlpath));
                IoUtil.copy(inputStream, outputStream);
                inputStream.close();
                outputStream.close();
            }

            if (!shellFile.canExecute()) {
                shellFile.setExecutable(true);
            }

            String shellCmd = String.format("%s -h %s -p %s -u %s -P %s -d %s", shellFile.getAbsolutePath(), host, port, user, pwd, db);
            log.info("Backup: {}", shellCmd);
            Runtime.getRuntime().exec(shellCmd).waitFor();
        } catch (InterruptedException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            backuping = false;
        }
    }

    public void restoreData(String filename) {
        if (backuping) {
            throw new ServiceException(ResultEnum.TASK_BASY.getCode(), "当前正在进行备份操作, 请稍后再试");
        }

        if (restoring) {
            throw new ServiceException(ResultEnum.TASK_BASY.getCode(), "当前正在进行数据恢复操作, 请稍后再试");
        }

        try {
            restoring = true;

            if (!filename.endsWith(BACKUP_POSTFIX)) {
                filename += BACKUP_POSTFIX;
            }

            String configBackupFolder = fileConfig.getBackup();
            File configBackupFolderFile = new File(configBackupFolder);
            if (!configBackupFolderFile.exists()) {
                configBackupFolderFile.mkdirs();
            }

            String backupFilePath = configBackupFolder + "/" + filename;
            File backupFile = new File(backupFilePath);
            if (!backupFile.exists()) {
                throw new TourException(ResultEnum.PARAM_ERROR.getCode(), "备份文件不存在");
            }
            String backupFileAbPath = backupFile.getAbsolutePath();

            // 获取参数
            if (StrUtil.isEmpty(jdbcUrl)) {
                throw new TourException(ResultEnum.PARAM_DATA_ERROR.getCode(), "数据库配置不正确");
            }
            String host, port, db, user, pwd;
            Map<String, String> dbConfigMap = readConfigFromJdbcUrl(jdbcUrl);
            host = dbConfigMap.get("host");
            port = dbConfigMap.get("port");
            db = dbConfigMap.get("db");
            if (dbConfigMap.containsKey("user")) {
                user = dbConfigMap.get("user");
            } else {
                user = username;
            }

            if (dbConfigMap.containsKey("password")) {
                pwd = dbConfigMap.get("password");
            } else {
                pwd = password;
            }

            String shellpath = configBackupFolder + "/mysql-restore.sh";
            File shellFile = new File(shellpath);
            if (!FileUtil.exist(shellFile)) {
                InputStream inputStream = new ClassPathResource("mysql/mysql-restore.sh").getInputStream();
                FileOutputStream outputStream = new FileOutputStream(shellFile);
                IoUtil.copy(inputStream, outputStream);
                inputStream.close();
                outputStream.close();
            }
            String mysqlpath = configBackupFolder + "/mysql";
            if (!FileUtil.exist(mysqlpath)) {
                InputStream inputStream = new ClassPathResource("mysql/mysql").getInputStream();
                FileOutputStream outputStream = new FileOutputStream(new File(mysqlpath));
                IoUtil.copy(inputStream, outputStream);
                inputStream.close();
                outputStream.close();
            }

            if (!shellFile.canExecute()) {
                shellFile.setExecutable(true);
            }

            String shellCmd = String.format("%s -h %s -p %s -u %s -P %s -d %s -f %s", shellFile.getAbsolutePath(), host, port, user, pwd, db, backupFileAbPath);
            log.info("Restore: {}", shellCmd);
            Runtime.getRuntime().exec(shellCmd).waitFor();
        } catch (InterruptedException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            restoring = false;
        }
    }
	
	// jdbc:mysql://localhost:3306/worksql?useUnicode=true&characterEncoding=UTF-8
    private Map<String, String> readConfigFromJdbcUrl(String jdbcUrl) {
        Map<String, String> configMap = new HashMap<>(8);
        if (StrUtil.isEmpty(jdbcUrl)) {
            return configMap;
        }
        Pattern pattern = Pattern.compile("jdbc:mysql:\\/\\/(.*)\\/(.*)", Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(jdbcUrl);
        if (matcher.find()) {

            // 主机地址和端口
            String hostPort = matcher.group(1);
            if (hostPort.contains(":")) {
                String[] hostPorts = hostPort.split(":");
                configMap.put("host", hostPorts[0]);
                configMap.put("port", hostPorts[1]);
            } else {
                configMap.put("host", hostPort);
                configMap.put("port", "3306");
            }

            // 连接的数据库
            String dbParams = matcher.group(2);
            if (dbParams.contains("?")) {
                configMap.put("db", dbParams.split("\\?")[0]);
            } else {
                configMap.put("db",dbParams);
                return configMap;
            }

            // 用户名和密码
            String params = dbParams.split("\\?")[1];
            if (params.length() > 1) {
                params = params.substring(1);
                String[] queries = params.split("&");
                for (String query : queries) {
                    String[] keyValue = query.split("=");
                    if (keyValue[0].equals("user")) {
                        configMap.put("user", keyValue[1]);
                        continue;
                    }
                    if (keyValue[0].equals("password")) {
                        configMap.put("password", keyValue[1]);
                    }
                }
            }
            return configMap;
        }
        return configMap;
    }

}
````

这个本身是运行爱Springboot框架下的， 额外引入的包是hutool库和lombok

程序为了减少配置， 从springboot的配置文件中获取系统数据库的配置， 并解析jdbc的url获取相关连接参数

两个执行脚本和两个基本命令是放在resource目录下的mysql目录下， 跟其他代码一起打包到jar文件中发布

在执行的时候会先将打包在jar里的文件进行释放， 然后再执行， 在单机的并发处理上使用volatile关键词修饰的两个boolean变量防止出现两个进程备份进程同时进行（分布式环境下的问题处理有分布式下的处理方式）