Import large MySQL database backup files with Bigdump tool

Import large MySQL database backup files with Bigdump tool

created in 2010-07-01, Thursday 00:00

author Bai Jianpeng

In the joomla! 1.5 website anti-Black 9 commandments "The first thing we mentioned in this article is: Backup your joomla! in a timely and regular manner. Website. We also recommend using the Backup tool Akeeba Backup (formerly known as Joomlapack). The Akeeba backup comes with the kickstart.php tool to complete the recovery of the back up package (or the migration installation). However, this is a recovery for the entire station. What if we just want to restore the database?

The Common MySQL Database Recovery tool (also available for backup operations) is PhpMyAdmin, an open source, free tool that most hosts (such as Hawkhost) will provide free of charge. Believe that many stationmaster also used phpMyAdmin to carry on the backup and restore of the website database, it is very convenient, and has the multi-country language interface. However, there is a situation you may not have encountered, that is, when your database size is larger, such as SQL backup file is larger than 2MB, or even greater than 10MB, this time if you through the PhpMyAdmin to restore the database, will be error, the following prompt:

This is because your SQL file size is too large, more than the processing power of the phpMyAdmin, this situation in the network speed is relatively slow, particularly prominent, such as webmaster in the weekend at 8 o'clock this network congestion time to try to use phpMyAdmin to restore the large MySQL database backup, It is easy to encounter this problem.

It is clear that phpMyAdmin only applies to recovering smaller SQL file backups. For super-Large MySQL database backup recovery, you must change a dedicated recovery tool, that is:bigdump!

Introduction to the Bigdump Database import Tool

Bigdump is a tool script developed by the Germans Alexey Ozerov in PHP language with only one file named bigdump.php (you can rename the run). This file is run independently, so it has nothing to do with your website core program, whether it is Wordpress,drupal or JOOMLA, as long as you are using the MySQL database, you can use Bigdump to restore the oversized. sql format backup file.

The principle of bigdump work is also simple: cut segmented import. It reads the SQL file, reads only a small portion at a time, imports it, then restarts the recovery process, and then reads a small portion ... Cycle until you import the entire SQL file.

Bigdump Database Recovery Tool usage Demo

Suppose we already have a backup file for MySQL database, named Gate2.sql, with a file size of about 150MB (so large files don't expect to be recovered by PhpMyAdmin). The database character encoding is utf-8. Let's demonstrate how to restore this backup file to an online database using the Bigdump.php tool.

1. Get the Bigdump file and set

We download the Biddump tool and unzip it to get a bigdump.php file. There is no doubt that the bigdump.php file needs to set some parameters, otherwise how does it know the connection account of the database to be imported?

Opening the bigdump.php file with a text editor, starting at about line 38th, we set the MySQL database connection parameters to import. As shown in the following:

In general, set these items on the right. For some special databases, you also need to set the database character encoding around line 66th below. This parameter is not preset in the bigdump.php file, the original is:

$db _connection_charset = ";

If your database is UTF-8 encoded, you should change it here:

$db _connection_charset = ' UTF8 ';

Note that the hyphen is to be removed. As shown in the following:

Next will set up the bigdump.php file uploaded to the Joomla website, the recommended upload to/tmp this temporary directory.

2. Upload MySQL Database backup file

Now you need to upload the database backup file to the/tmp directory where bigdump.php is located. Considering that the file is large, we upload the file in zip format via FTP software FileZilla, and then use the Extplorer Explorer component of Joomla background to extract it remotely and get Gate2.sql file in the/tmp directory.

3. Start Bigdump Tool

Enter the URL of the bigdump.php in the browser address bar and press ENTER to run the file. For example, this demo is performed on a local test server, then the corresponding run URL is:

http://localhost/gate/tmp/bigdump.php

If you enter the correct URL, you should see the following screen:

This means that the Bigdump tool has started successfully, and it automatically scans all files and subdirectories in the directory at startup. If you find the SQL format or the gzip/zip format file, it is assumed that this is a database file, after which the corresponding action link is displayed. For example, it detects that there is a gate2.sql file in the directory where it is located, and the two action links are displayed in the same row on the right side of the table, namely "Start import" and "Delete file" (delete files). The table also shows the size of the SQL file, and you can see that the backup file is close to 150MB.

In, you can also see a file upload function, click the "Browse" button, you can upload a SQL file (or its compressed package) to import. However, we do not recommend using this feature. For large files, FTP uploads are the best choice.

4. Run Bigdump Import function

Click on the "Start Import" link to see the following screen:

Above this is the beginning of the import after a period of interception, you can see that bigdump is a smooth SQL file import. The table shows not only the file size, but also the number of bytes already imported and the overall progress.

After a period of time (about 20 minutes), this 150MB-size SQL file finally imports the end, see the following screen:

Where the progress bar was originally displayed, a message is now displayed:

Congratulations:end of file reached, assuming OK

It means that you have reached the end of the file and must have succeeded. You can be completely relieved to see the news. Bigdump has successfully imported your SQL backup file into the MySQL database you specified.

Note: Do not forget to delete the bigdump.php Backup tool and your SQL file after the database recovery has completed successfully!

Considerations for using the Bigdump Database Import Tool 1. The target database must be emptied before starting the import

If the target database contains records, then the bigdump can not be imported, the error will stop. So before you start running the bigdump.php file, empty the target database (it's a good idea to delete everything).

2. The SQL file used cannot contain Extended inserts

Perhaps many users who use Bigdump for the first time will encounter this problem. This is because when PhpMyAdmin exports the database, it has been checked by default to "insert with extensions".

The "extended insert" here is Extended inserts, and bigdump cannot handle SQL files with Extended inserts. Therefore, when you make a database backup, you must remove the "extended insert" check on the phpMyAdmin panel. As shown in the following:

3, how to make Akeeba Backup and bigdump work together?

We recommend that you use Akeeba Backup to create your backed up files. Can you use Akeeba Backup to make a database backup and then import through Bigdump? Of course it's possible. However, there are some special tricks that need to be included. We'll cover this tutorial in Akeeba Backup usage.

4. Time-out error still occurs using Bigdump import

This problem is common in servers with low configuration or server peak times. Then, you can try modifying the $linespersession parameter in the "Other settings (optional)" section of the bigdump.php file, modifying the default value of 3000 to a smaller size. (However, I think you should change a better server, such as Hawkhost).

5. What happens to MySQL server overloading?

If the server performs poorly, a MySQL server overload (overrun) failure may occur during the run of bigdump.php. You can modify the $delaypersession parameters in the bigdump.php file to resolve them. This parameter lets the bigdump.php file after the import of the end of a fragment, rest for a period of time (you set the value is the pause time, in milliseconds), and then start the next fragment of the import, so that the server is not too tired.

6. Give the. sql format file directly to Bigdump

In this demo, we first upload the backup package in the ZIP format to the server, then unzip it into a. sql file and import it via Bigdump. Why not use Bigdump directly to import a compressed backup file?

Yes, Bigdump has the ability to import a compressed SQL backup file (zip/gzip), but when processing a compressed file, it needs to be decompressed in the temp directory before it is read. Please note: This decompression is not a one-time, but every import of a small fragment, it is necessary to unzip the entire package again. Therefore, the direct import of the compressed format file, instead makes the bigdump.php run slower.

7. I have more strange questions ...

In general, it is very easy to run bigdump.php to import a SQL backup file. If you follow the above precautions and the operation process, still can not successfully complete the database backup file import, then please go to bigdump official website read more FAQ, find the reason. If not resolved, you can go to the Joomla Chinese forum post discussion.

Download bigdump (bigdump.php) Large database SQL file Segmentation import Tool v0.32b English version

File title: Bigdump (bigdump.php) Large database SQL file Segmentation import Tool v0.32b English version (details) File type: the Zip Version: 0.32b File Size: Kb Number of downloads: 5843

Import large MySQL database backup files with Bigdump tool