wordpress 数据库
In a previous article we saw how to manually back up a WordPress website. In particular, we saw how to backup a database, and how to restore it.
在上一篇文章中，我们看到了如何手动备份WordPress网站 。 特别是，我们看到了如何备份数据库以及如何还原数据库。
However, when we try to restore a database, a problem can occur, not discussed in the previous article. What about big databases? Using the command line, size isn’t typically an issue, since we can use MySQL to export or input files of any size. However, if you don’t have SSH access to a particular hosting environment, large database exports and imports can be a real problem using tools such as phpMyAdmin. There are often limits to the allowed file uploads. But there is a way to easily backup and restore large WordPress databases.
但是，当我们尝试还原数据库时，可能会发生问题，而在上一篇文章中没有进行讨论。 大数据库呢？ 使用命令行，大小通常不是问题，因为我们可以使用MySQL导出或输入任何大小的文件。 但是，如果您无法通过SSH访问特定的托管环境，则使用phpMyAdmin之类的工具进行大型数据库导出和导入可能是一个实际问题。 允许的文件上传通常受到限制。 但是有一种方法可以轻松备份和还原大型WordPress数据库。
In this article, I’ll cover how to restore big database dumps with a useful tool called BigDump. Essentially, BigDump is a PHP script that allows you to import a database dump as big as you want, even if your upload limit is low. This article assumes that you don’t have SSH/command line access to your server, or are more comfortable using a web application. Note that BigDump is released under the GNU GPL 2 license.
在本文中，我将介绍如何使用称为BigDump的有用工具还原大型数据库转储。 本质上，BigDump是一个PHP脚本，即使您的上传限制很低，它也允许您导入所需大小的数据库转储。 本文假定您没有对服务器的SSH /命令行访问权限，或者更喜欢使用Web应用程序。 请注意，BigDump是根据GNU GPL 2许可发布的。
After restoring a big database dump with BigDump, I’ll show you how to automatically generate these dumps thanks to a WordPress plugin named WP-DBManager, because you don’t have time to waste manually backing up your database every day!
在使用BigDump恢复大型数据库转储之后，我将向您展示如何借助一个名为WP-DBManager的WordPress插件自动生成这些转储，因为您没有时间浪费每天手动备份数据库！
I’ve already covered how to back up a WordPress database in the previous article, so we won’t describe how to do that again. However, there’s something you should know if you want to use BigDump.
在上一篇文章中 ，我已经介绍了如何备份WordPress数据库 ，因此我们将不再描述如何进行此操作。 但是，如果要使用BigDump，则应了解一些信息。
In practice, BigDump splits your file into as many files as necessary for your server to allow the import, and sometimes the split can cause trouble – if you use extended inserts that allow you to optimize your SQL queries by merging several INSERT queries into one.
实际上，BigDump会将文件分割成服务器允许导入所需的尽可能多的文件，有时，分割可能会造成麻烦-如果您使用扩展插入，通过将多个INSERT查询合并为一个来优化SQL查询，则可以。
Let’s clarify the situation with a simple example. Assume that we have a table tbl with three columns a, b and c. In this table we have two rows: (1, 2, 3) and (4, 5, 6). If we don’t use extended inserts, two queries are necessary to insert the two rows:
让我们用一个简单的例子来阐明情况。 假设我们有一个表tbl其中包含三列a ， b和c 。 在此表中，我们有两行： (1, 2, 3)和(4, 5, 6) 。 如果我们不使用扩展插入，则需要两个查询来插入两行：
INSERT INTO tbl (a, b, c) VALUES (1, 2, 3); INSERT INTO tbl (a, b, c) VALUES (4, 5, 6);However, we can use extended inserts to optimize this insert:
但是，我们可以使用扩展插入来优化此插入：
INSERT INTO tbl (a, b, c) VALUES (1, 2, 3), (4, 5, 6);Here we inserted two rows with one query. Of course in this example the gained time is negligible, but with a big table containing a large number of rows, the advantage can be significant.
在这里，我们用一个查询插入了两行。 当然，在此示例中，获得的时间可以忽略不计，但是对于包含大量行的大表而言，其优势可能非常明显。
The problem is that if your table is a very big one, BigDump can’t split these types of queries. That’s why we must avoid extended inserts when we want to use BigDump: we must ask phpMyAdmin (or your preferred tool/method) to export our data in the shape of the first piece of SQL code above, with INSERT INTO in every insert.
问题是，如果您的表很大，则BigDump无法拆分这些类型的查询。 这就是为什么我们要使用BigDump时必须避免扩展插入的原因：我们必须要求phpMyAdmin(或您首选的工具/方法)以上面第一段SQL代码的形式导出数据，并在每个插入中插入INSERT INTO 。
The file will then be bigger. However, as we’ll use a script that supports any size we need, size won’t be a problem.
该文件将更大。 但是，由于我们将使用支持所需大小的脚本，因此大小不会有问题。
The good news is we can ask phpMyAdmin to avoid extended inserts. To do this, when we export a database we can choose the “Custom” option to customize the way the dump is generated.
好消息是我们可以要求phpMyAdmin避免扩展插入。 为此，在导出数据库时，我们可以选择“ Custom ”选项来定制转储的生成方式。
Then, in the “Data creation options” section, we will find the option “Syntax to use when inserting data“. The default value is extended inserts: change it to the first one (“include column names in every INSERT statement“). That way, phpMyAdmin will generate inserts as we see above.
然后，在“ 数据创建选项 ”部分中，我们将找到“ 插入数据时使用的语法 ”选项。 默认值为扩展插入：将其更改为第一个(“ 在每个INSERT语句中包括列名称 ”)。 这样，phpMyAdmin将生成我们上面所看到的插入。
We’re now ready to use BigDump!
现在我们可以使用BigDump了！
You can download BigDump from the official BigDump website. You’ll download an archive containing a PHP file called bigdump.php.
您可以从BigDump 官方网站下载BigDump。 您将下载一个包含名为bigdump.phpPHP文件的存档。
You can place BigDump anywhere you want on your computer, in a directory accessible from your web server (for example, I created a special folder named “Tools” which contains some useful tools like BigDump).
您可以将BigDump放在计算机上所需的任何位置，可从Web服务器访问该目录(例如，我创建了一个名为“ Tools ”的特殊文件夹，其中包含一些有用的工具，例如BigDump)。
Then, you can access BigDump by using its URL (in my example, it’s http://127.0.0.1/Tools/bigdump.php).
然后，您可以使用其URL访问BigDump(在我的示例中为http://127.0.0.1/Tools/bigdump.php )。
Before importing our big file, we need to initialize BigDump to allow it to know how to access our database. That can be achieved by editing the bigdump.php file.
导入大文件之前，我们需要初始化BigDump以使其知道如何访问数据库。 这可以通过编辑bigdump.php文件来实现。
The first four defined variables, right after the big comment informing us about the license, are the ones you will need to edit.
您需要编辑的变量中的前四个已定义变量(紧随大注告知我们有关许可证的信息之后)。
Their names are pretty clear: you must indicate in $db_server the server where your database is stored, in $db_name the name of your database and in $db_username and $db_password your login information. These details are the same as what you’ll find inside wp-config.php or any other web application that uses MySQL.
它们的名称非常清楚：您必须在$db_server指示存储数据库的服务器，在$db_name指示数据库的名称，并在$db_username和$db_password $db_server指示登录信息。 这些详细信息与wp-config.php或使用MySQL的任何其他Web应用程序中的内容相同。
By default, BigDump uses the utf8 charset but you can modify this by editing the value of the variable $db_connection_charset defined right after the ones we just edited.
默认情况下，BigDump使用utf8字符集，但是您可以通过在刚刚编辑的变量之后立即编辑定义的变量$db_connection_charset db_connection_charset的值来修改它。
We’re now ready to import our big file. Just access BigDump by visiting its URL, as described above.
现在，我们准备导入我们的大文件。 如上所述，只需通过访问其URL访问BigDump。
There are two ways we can import a big file. The first one is by using the form BigDump created: you use the “Browse…” button as usual to select your file, and hit the “Upload” button to submit your file. The problem with this method is you are still limited by the upload limit of your server. Moreover, the directory containing BigDump must be writable for PHP.
我们可以通过两种方式导入大文件。 第一种是使用BigDump创建的形式：照常使用“ 浏览… ”按钮选择文件，然后单击“上载 ”按钮提交文件。 这种方法的问题是您仍然受到服务器上载限制的限制。 此外，包含BigDump的目录对于PHP必须是可写的。
The second method is by directly uploading your file on your server, using FTP/SFTP/SCP for example. Your file must be placed in the same directory of the bigdump.php file.
第二种方法是直接将文件上传到服务器上，例如使用FTP / SFTP / SCP。 您的文件必须放在bigdump.php文件的同一目录中。
Once your file is uploaded (thanks to the form or via FTP/SFTP/SCP), it is accessible from the BigDump interface.
一旦文件上传(通过表格或通过FTP / SFTP / SCP进行上传)，就可以从BigDump界面访问该文件。
If the folder is writable, you can delete your SQL files directly from this interface once it’s used. To import the file into the database indicated into the variables we edited above, hit the “Start Import” link on the line corresponding to the file you just uploaded.
如果该文件夹是可写的，则使用后即可直接从该界面删除SQL文件。 要将文件导入到我们在上面编辑的变量中指示的数据库中，请单击与刚上传的文件相对应的行上的“ 开始导入 ”链接。
Then, BigDump will display a new page indicating the progress of the import. All you have to do here is wait for the end of the file to be reached. You can also abort the import by hitting the “STOP” link below the table.
然后，BigDump将显示一个指示导入进度的新页面。 您在这里要做的就是等待文件结束。 您也可以通过点击表格下方的“ STOP ”链接来中止导入。
Once the import is finished, that’s it! Your data is imported into your database and you can delete your SQL files. Congratulations, you just got around the size limit when you want to import an SQL file!
导入完成后就可以了！ 您的数据已导入数据库，您可以删除SQL文件。 恭喜，您刚要导入SQL文件时就达到了大小限制！
We know how to manually back up our WordPress database. And how to restore it, even if it’s huge, thanks to BigDump. However, manually backing up a database is not a fun task, especially if we do it every week, or every day. That’s why there’s a wide range of tools that can automatically do this for us.
我们知道如何手动备份WordPress数据库。 以及如何恢复它，即使它很大，也要感谢BigDump。 但是，手动备份数据库并不是一项有趣的任务，特别是如果我们每周或每天都这样做。 这就是为什么有各种各样的工具可以自动为我们做到这一点的原因。
Also mentioned in a previous article on WordPress maintenance, WP-DBManager is freely available from WordPress.org. WP-DBManager stores its backups in the /wp-content/backup-db directory and, in some cases, it can’t create this folder by itself. To fix this issue, create this folder and make it writable for PHP.
在上一篇有关WordPress维护的文章中也提到过，WP-DBManager可从WordPress.org免费获得。 WP-DBManager将其备份存储在/wp-content/backup-db目录中，在某些情况下，它无法单独创建此文件夹。 要解决此问题，请创建此文件夹并使它可用于PHP。
To create the first initial backup of your database, you can go to the ‘Backup DB‘ entry of the ‘Database‘ menu (which appears with the plugin activation). At the bottom of the page you’ll be able to hit a ‘Backup‘ button which will launch the backup.
要创建数据库的第一个初始备份，您可以转到“ 数据库 ”菜单的“ 备份数据库 ”条目(随插件激活一起出现)。 在页面底部，您将能够单击“ 备份 ”按钮，这将启动备份。
You can manage your backups via “Manage Backup DB“. Here you’ll be able to delete old backups, download the ones you want to retrieve or even send them by email.
您可以通过“ 管理备份数据库 ” 管理备份 。 在这里，您将能够删除旧备份，下载要检索的备份，甚至通过电子邮件发送备份。
The section we’re really interested in is “DB Options“, specifically the subsection labeled “Automatic Scheduling“. The first option, “Automatic Backing Up of DB“, allows you to set a time interval for your backups. For example, if you set “2 weeks”, WP-DBManager will automatically backup your database every two weeks. You’ll be able to access these backups from the section we described above, but you can also choose to receive them by email, which might come in handy for some.
我们真正感兴趣的部分是“ DB Options ”，特别是标有“ Automatic Scheduling ”的小节。 第一个选项“ 数据库自动备份 ”允许您设置备份的时间间隔。 例如，如果设置为“ 2周”，则WP-DBManager将每两周自动备份一次数据库。 您可以从上述部分访问这些备份，但是您也可以选择通过电子邮件接收这些备份，这对于某些人可能会派上用场。
Backing up your WordPress database is important. It contains the data for your website, and without a backup of it, if your WordPress website crashes, you risk losing valuable data.
备份WordPress数据库非常重要。 它包含您网站的数据，并且没有备份，如果您的WordPress网站崩溃，则可能会丢失宝贵的数据。
There are several ways to back up your database: the manual way and the automatic way with WP-DBManager. You can even develop your own tools. If this is the case, then please don’t hesitate to share them in the comments below!
有几种备份数据库的方法：手动方法和使用WP-DBManager的自动方法。 您甚至可以开发自己的工具。 如果是这种情况，请不要犹豫，在下面的评论中分享它们！
Moreover, with BigDump, you can easily restore your data regardless of the size. In just one click you can restore, without the need to manually split the file.
此外，借助BigDump，无论大小如何，您都可以轻松还原数据。 只需单击一下即可恢复，而无需手动分割文件。
翻译自: https://www.sitepoint.com/backing-up-and-restoring-large-wordpress-databases/
wordpress 数据库
相关资源：wordpress数据表及其结构