Key Takeaways

• BigDump is a PHP script that allows you to import large WordPress database dumps, bypassing server upload limits. It can handle database dumps of any size, making it an ideal solution for those without SSH/command line access to their server.
• When using BigDump, it’s important to avoid extended inserts during the database export process. This can be achieved by selecting the “include column names in every INSERT statement” option in phpMyAdmin’s “Data creation options” section.
• WP-DBManager is a WordPress plugin that can automatically backup your database, saving you the time and effort of doing it manually. It allows you to set a time interval for automatic backups and can even send these backups to your email.
• Regularly backing up your WordPress database is crucial for the security and integrity of your website. With tools like BigDump and WP-DBManager, you can easily backup and restore your data, regardless of the size of your database.

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.

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.

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.

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!

Generating a BigDump-ready Database Dump

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.

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.

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:

<span>INSERT INTO tbl (a, b, c) VALUES (1, 2, 3);
</span><span>INSERT INTO tbl (a, b, c) VALUES (4, 5, 6);</span>

However, we can use extended inserts to optimize this insert:

<span>INSERT INTO tbl (a, b, c) VALUES (1, 2, 3), (4, 5, 6);</span>

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.

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.

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.

We’re now ready to use BigDump!

Restoring a Big Database with BigDump

Download and Install BigDump

You can download BigDump from the official BigDump website. You’ll download an archive containing a PHP file called bigdump.php.

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).

Then, you can access BigDump by using its URL (in my example, it’s http://127.0.0.1/Tools/bigdump.php).

Initializing BigDump

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.

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.

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.

We’re now ready to import our big file. Just access BigDump by visiting its URL, as described above.

Importing a Big Database Dump

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.

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.

Once your file is uploaded (thanks to the form or via FTP/SFTP/SCP), it is accessible from the BigDump interface.

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.

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.

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!

WP-DBManager – a WordPress Plugin to Automatically Backup Your Database

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.

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.

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.

In Conclusion

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.

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!

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.

Frequently Asked Questions (FAQs) about Backing Up and Restoring Large WordPress Databases

What is the Importance of Backing Up a WordPress Database?

Backing up your WordPress database is crucial for the security and integrity of your website. It ensures that you have a copy of your website’s data, including posts, comments, and user information, which can be restored in case of data loss or corruption. Regular backups also allow you to revert to a previous version of your site if an update or change causes issues.

How Often Should I Backup My WordPress Database?

The frequency of backups depends on how often you update your site. If you regularly add new content or make changes, daily backups are recommended. However, if your site remains relatively static, weekly or monthly backups may suffice. Remember, the more frequent the backups, the less data you risk losing.

Can I Use WP-CLI to Backup My WordPress Database?

Yes, WP-CLI is a powerful tool that allows you to interact with your WordPress site via the command line. It includes commands for backing up your database. However, it requires a certain level of technical knowledge and is best used by advanced users or developers.

Are There Plugins Available for Backing Up WordPress Databases?

Yes, there are numerous plugins available that can automate the backup process. These plugins can schedule regular backups, store them in various locations, and even restore your site from a backup. Some popular options include UpdraftPlus, BackupBuddy, and VaultPress.

How Can I Restore My WordPress Database from a Backup?

Restoring your database from a backup can be done through your hosting provider’s control panel, using a plugin, or via WP-CLI. The process involves importing the backup file into your database. It’s important to note that restoring a database will overwrite your current data.

What Should I Do If My Database Is Too Large to Backup?

If your database is too large to backup using conventional methods, you can use tools like WP-CLI or phpMyAdmin to export your database in parts. Alternatively, you can optimize your database to reduce its size by deleting unnecessary data.

Can I Backup My WordPress Database Manually?

Yes, you can manually backup your WordPress database using phpMyAdmin. This involves exporting your database to a .sql file, which can be stored and used for restoration. However, this method requires technical knowledge and is not recommended for beginners.

What Data Is Included in a WordPress Database Backup?

A WordPress database backup includes all the data that makes up your website. This includes posts, pages, comments, user data, site settings, and plugin settings. However, it does not include your themes, plugins, and uploads, which are stored in the wp-content directory.

How Can I Secure My WordPress Database Backups?

To secure your backups, store them in multiple locations, including a secure off-site location. Encrypting your backups can also add an extra layer of security. Additionally, ensure that your backups are regularly updated to include the latest data.

What Are the Risks If I Don’t Backup My WordPress Database?

Without regular backups, you risk losing all your website data in case of a server crash, hacking attempt, or a simple human error. This could result in loss of valuable content, customer information, and ultimately, a significant impact on your business. Regular backups ensure that you can quickly restore your site to its previous state.

The above is the detailed content of Backing up and Restoring Large WordPress Databases. For more information, please follow other related articles on the PHP Chinese website!