mysql数据库备份方式_如何使用mysql-backup4j以编程方式备份​​MySQL数据库

mysql数据库备份方式

by Seun Matt

通过Seun Matt

如何使用mysql-backup4j以编程方式备份​​MySQL数据库 (How to backup your MySQL database programmatically using mysql-backup4j)

In this article, we’re going to be looking at mysql-backup4j, a very flexible Java library that we can use to back-up our database periodically.

在本文中，我们将研究mysql-backup4j ，这是一个非常灵活的Java库，可用于定期备份数据库。

Once our app is in production, we can’t afford to not have a timely backup in case of eventualities. Usually, what makes the process somewhat arduous is if we have to manually trigger the process all the time.

一旦我们的应用程序投入生产，一旦发生意外，我们将无法及时进行备份。 通常，使该过程有些艰巨的是，如果我们必须一直手动触发该过程。

Imagine a scenario in which we have both automated and manual processes of database backup — that’s what we’re about to do.

设想一个场景，在该场景中，我们同时具有自动和手动的数据库备份过程-这就是我们要做的。

2.依赖安装 (2. Dependency Installation)

Let’s add the dependency to our project’s pom.xml:

让我们将依赖项添加到项目的pom.xml中 ：

<dependency>   <groupId>com.smattme</groupId>   <artifactId>mysql-backup4j</artifactId>   <version>1.0.1</version></dependency>

The latest version can be found here.

最新版本可在此处找到。

3.以编程方式导出MySQL数据库 (3. Exporting MySQL Database Programmatically)

Exporting a MySQL database programmatically is very straightforward with mysql-backup4j. We only need to instantiate it and pass it a Java Properties object that has the right configuration properties set:

使用mysql-backup4j以编程方式导出MySQL数据库非常简单。 我们只需要实例化它，并将其传递给设置了正确配置属性的Java Properties对象：

//required properties for exporting of db Properties properties = new Properties(); properties.setProperty(MysqlExportService.DB_NAME, "database-name"); properties.setProperty(MysqlExportService.DB_USERNAME, "root"); properties.setProperty(MysqlExportService.DB_PASSWORD, "root");

//properties relating to email config properties.setProperty(MysqlExportService.EMAIL_HOST, "smtp.mailtrap.io"); properties.setProperty(MysqlExportService.EMAIL_PORT, "25"); properties.setProperty(MysqlExportService.EMAIL_USERNAME, "mailtrap-username"); properties.setProperty(MysqlExportService.EMAIL_PASSWORD, "mailtrap-password");properties.setProperty(MysqlExportService.EMAIL_FROM, "test@smattme.com"); properties.setProperty(MysqlExportService.EMAIL_TO, "backup@smattme.com");

//set the outputs temp dir properties.setProperty(MysqlExportService.TEMP_DIR, new File("external").getPath());

MysqlExportService mysqlExportService = new MysqlExportService(properties); mysqlExportService.export();

From the snippet above, we created a new Properties object and then added the required properties for the database connection, which are: the database name, username, and password.

在上面的代码段中，我们创建了一个新的Properties对象，然后添加了数据库连接所需的属性，即：数据库名称，用户名和密码。

Supplying just these properties will make mysql-backup4j assume that the database is running on localhost at port 3306. It will, therefore, attempt connection using these values alongside the supplied username and password.

仅提供这些属性将使mysql-backup4j假定数据库正在端口3306的 localhost上运行 。 因此，它将尝试使用这些值以及提供的用户名和密码进行连接。

At this point, the library can export our database and generate a zip file containing the SQL dump file. The file is named in the format:

此时，库可以导出我们的数据库并生成一个包含SQL转储文件的zip文件。 该文件以以下格式命名：

randomstring_day_month_year_hour_minute_seconds_database_name_dump.zip

Since we have supplied complete email credentials as part of the properties used to configure it, the zipped database dump will be sent via email to the configured address. If no email config is set, then nothing happens after backup.

由于我们已经提供了完整的电子邮件凭据作为用于配置它的属性的一部分，因此压缩的数据库转储将通过电子邮件发送到配置的地址。 如果未设置电子邮件配置，则备份后什么也不会发生。

Another important config that we set is the TEMP_DIR; this is the directory that will be used by the library to temporarily store the generated files while still processing. This dir should be writable by the running program.

我们设置的另一个重要配置是TEMP_DIR。 这是库将用于在仍在处理的同时临时存储生成的文件的目录。 该目录应可由正在运行的程序写入 。

The TEMP_DIR will be automatically deleted once the backup operation is complete. Sweet and simple right? Yeah.

备份操作完成后，将自动删除TEMP_DIR 。 甜美简单吧？ 是的

4.将生成的压缩文件发送到任何云存储 (4. Sending Generated Zipped File to any Cloud Storage)

Though the library can send the backup to a pre-configured email address, it also provides a means for us to get the generated file as a Java File object so we can do whatever we want with it.

尽管该库可以将备份发送到预先配置的电子邮件地址，但它还为我们提供了一种将生成的文件作为Java File对象获取的方式，因此我们可以对它进行任何操作。

For us to achieve that, we’ve got to add this configuration property:

为了实现这一点，我们必须添加以下配置属性：

//... properties.setProperty(MysqlExportService.PRESERVE_GENERATED_ZIP, "true");

This property instructs mysql-backup4j to preserve the generated zip file so that we can access it:

此属性指示mysql-backup4j保存生成的zip文件，以便我们可以访问它：

File file = mysqlExportService.getGeneratedZipFile();

Now that we have a file object, we can upload it to any cloud storage of our choice using appropriate SDKs and libraries.

现在我们有了文件对象，我们可以使用适当的SDK和库将其上传到我们选择的任何云存储中。

Once we’re done, we have to manually clear the zip file from the TEMP_DIR by calling:

完成后，我们必须通过调用以下命令从TEMP_DIR手动清除zip文件：

mysqlExportService.clearTempFiles(false);

This aspect is very important so we won’t have redundant files in our local storage. If we want to get the raw exported SQL dump as a String, we only need to call this method:

这方面非常重要，因此我们在本地存储中不会有多余的文件。 如果我们想将导出的原始SQL转储获取为字符串，则只需调用此方法：

String generatedSql = mysqlExportService.getGeneratedSql();

I love the flexibility of this library. Other properties that can be set are:

我喜欢这个库的灵活性。 可以设置的其他属性是：

properties.setProperty(MysqlExportService.DELETE_EXISTING_DATA, "true"); properties.setProperty(MysqlExportService.DROP_TABLES, "true");properties.setProperty(MysqlExportService.ADD_IF_NOT_EXISTS, "true"); properties.setProperty(MysqlExportService.JDBC_DRIVER_NAME, "root.ss");properties.setProperty(MysqlExportService.JDBC_CONNECTION_STRING, "jdbc:mysql://localhost:3306/database-name");

DELETE_EXISTING_DATA will add a DELETE * FROM table SQL statement before an INSERT INTO table SQL statement(s).

DELETE_EXISTING_DATA将在INSERT INTO表 SQL语句之前添加DELETE * FROM表 SQL语句。

DROP_TABLES will add a DROP TABLE IF EXISTS SQL statement before CREATE TABLE IF NOT EXISTS statement.

DROP_TABLES将在CREATE TABLE IF NOT EXISTS语句之前添加DROP TABLE IF EXISTS SQL语句。

ADD_IF_NOT_EXISTS which is by default true will add an IF NOT EXISTS clause to CREATE TABLE statements.

默认情况下为true的 ADD_IF_NOT_EXISTS会将IF NOT EXISTS子句添加到CREATE TABLE语句中。

We can specify the JDBC_DRIVER_NAME and the JDBC_CONNECTION_STRING also via the properties.

我们也可以通过属性指定JDBC_DRIVER_NAME和JDBC_CONNECTION_STRING 。

If our database happens to be running on another host or port other than localhost:3306 then we can use the JDBC_CONNECTION_STRING property to configure the connection. The DB_NAME will be extracted from the supplied connection string.

如果我们的数据库恰好在localhost：3306以外的其他主机或端口上运行，则可以使用JDBC_CONNECTION_STRING属性来配置连接。 DB_NAME将从提供的连接字符串中提取。

We can automate this process by using Java job schedulers like quartz or other means. Moreover, in a typical web application, we can just create a path for it that will trigger the backup process in a Service or a Controller.

我们可以使用石英或其他方式的Java作业调度程序来自动执行此过程 。 而且，在典型的Web应用程序中，我们可以为其创建一个路径，该路径将触发Service或Controller中的备份过程。

We can even integrate it into a web application such that the backup will be triggered when the database has a significant record update. The possibilities are limited only by our creativity.

我们甚至可以将其集成到Web应用程序中，以便在数据库具有重大记录更新时触发备份。 可能性仅受我们的创造力限制。

5.导入数据库转储 (5. Importing the Database Dump)

Yep! We’ve been able to backup our database and lock it away in a secure vault. But how do we import the database and do a restoration?

是的 我们已经能够备份数据库并将其锁定在安全的保管库中。 但是，我们如何导入数据库并进行还原呢？

First, we have to unzip the generated zip file and extract the SQL dump into a folder. Then we can use database clients like HeidiSQL and Adminer to import the database. Using a database manager client will provide a visual aid and other great tools that come with it.

首先，我们必须解压缩生成的zip文件并将SQL转储解压缩到一个文件夹中。 然后，我们可以使用HeidiSQL和Adminer之类的数据库客户端来导入数据库。 使用数据库管理器客户端将提供视觉帮助以及随附的其他出色工具。

However, let’s say we find ourselves in need of restoring the database programmatically, within the app, while it’s still running.

但是，假设我们发现自己需要在应用程序仍在运行时以编程方式还原该数据库。

All we need to do is read the content of the generated SQL dump as a String and pass it to the MySqlImportService of the library with minimum configurations:

我们所需要做的就是以最小的配置读取生成SQL转储的内容，并将其传递给库的MySqlImportService ：

String sql = new String(Files.readAllBytes(Paths.get("path/to/sql/dump/file.sql")));

boolean res = MysqlImportService.builder() .setDatabase("database-name") .setSqlString(sql).setUsername("root") .setPassword("root") .setDeleteExisting(true).setDropExisting(true) .importDatabase();

assertTrue(res);

From the snippet above, we read the SQL from a file system, and then we used the MySqlImportService to perform the import operation.

从上面的代码片段中，我们从文件系统中读取SQL，然后使用MySqlImportService执行导入操作。

We configured MySqlImportService to delete any existing content in the table and to drop existing tables. We can always fine tune these parameters to suit our needs. The service will return true on successful operation or false otherwise.

我们将MySqlImportService配置为删除表中的任何现有内容并删除现有表。 我们可以随时调整这些参数以适合我们的需求。 服务将在成功操作后返回true，否则返回false。

What if our database is running on another server and port other than localhost:3306? We can configure that as well using the setJdbcConnString() method.

如果我们的数据库在除localhost：3306之外的其他服务器和端口上运行该怎么办？ 我们也可以使用setJdbcConnString()方法进行配置。

Although we read the SQL file from a local file system, if we’re in a web interface, we can actually provide an interface that will allow the file be selected from the file system. Then the content can be read and sent as an HTTP POST request to the server.

尽管我们从本地文件系统读取SQL文件，但是如果我们在Web界面中，我们实际上可以提供一个接口，该接口允许从文件系统中选择文件。 然后，可以读取内容并将其作为HTTP POST请求发送到服务器。

六，结论 (6. Conclusion)

Wheew! That’s some productivity tool we’ve just looked at. Remember to star mysql-backup4j on Github if you liked it.

he！ 这是我们刚刚研究过的一些生产力工具。 如果喜欢，请记住在Github上给mysql-backup4j加上星号。

Now, go and utilize it in your project. Questions? Contributions? Appreciation? Kindly drop them in the comment section below.

现在，在您的项目中使用它。 有什么问题吗 有贡献吗？ 升值？ 请把它们放在下面的评论部分。

Read my other technical posts and views of life from https://smattme.com

从https://smattme.com阅读我的其他技术文章和生活观点

If you find this post helpful, learned anything at all, clap for the article and share it with your friends on Facebook and Twitter. Be proud of the quality content you read.

如果您发现这篇文章很有帮助，从中学到了什么，就拍一下文章，并在Facebook和Twitter上与您的朋友分享。 以阅读的高质量内容为荣。

Originally published at smattme.com.

最初发布于smattme.com 。

翻译自: https://www.freecodecamp.org/news/how-to-backup-mysql-database-programmatically-using-mysql-backup4j-2b53a1cbf9b2/

mysql数据库备份方式