The application will use the selected Database Dump Engine to take a backup of your site's database. Choosing the right engine is important for the correct operation of the backup. The Configuration Wizard does that automatically for you. You can always fine tune the configuration settings of the database dump engine if you have problems or if you want to make the backup faster.
This engine will take a backup of your MySQL database using nothing but PHP functions in order to accomplish that. This database dump engine supports all of the advanced features available in MySQL 5, such as views, stored procedures and functions, triggers, merge tables, temporary/memory tables, even federated tables.
Restoring views, triggers, stored procedures and functions requires adequate privileges for the database user during the restoration process. Most hosts do not assign this kind of privileges. If your restoration fails with a MySQL error when restoring such database entities you may have to ask your host to assign those privileges to your database user.
Native MySQL Backup Engine
When enabled, the application will not include the username and password of database
connections in the backup archive. Please note that this option only removes the database username
and password from the
installation/sql/databases.ini file which is included in
the backup. It will not remove the database connection information from the configuration file of
your site's script which is by default backed up as well. If you want to remove the database
connection information for security reasons you should exclude your site's script configuration file
(e.g. wp-config.php for WordPress sites and configuration.php for Joomla! sites) from your backup
using the Files and Directories Exclusion filter feature.
When this is not checked, the application will create one INSERT statement for each data row of each table. When you have lots of rows with insignificant amounts of data, such as banner and click tracking logs, the overhead of the INSERT statement is much higher than the actual data, causing a massively bloated database dump file and a much longer backup and restoration duration. When this option is enabled, the dump engine will create a single INSERT statement for multiple rows of data, reducing the overhead and resulting into significantly smaller backup archives. Moreover, this will lead to much fewer SQL commands being run during restoration, which is of paramount importance on many restrictive shared hosting environments. It is suggested to turn this setting on.
If the previous setting is enabled, this setting defines the maximum length of a single INSERT statement. Most MySQL servers have a configured limit of maximum statement length and will not accept an INSERT statement over 1Mb. It is suggested to leave the default conservative setting (128Kb) unless you know what you're doing. If you get restoration failures indicating that you exceeded the maximum query length, please lower this setting and take a new backup.
By default, the application will only back up database tables and VIEWs. If your host supports this, you can also back up and restore advanced aspects of your MySQL database: stored procedures, stored functions and triggers. If your site makes use of any of those features you will have to tick the box. If the backup operation crashes or the database tables filter page is blank you must turn this option off for the application to work properly.
Using this feature requires that your host allows you to execute privileged SQL commands against the MySQL database:
Most shared hosting providers do not allow you to execute these commands. Trying to do so will usually cause the script execution to abruptly halt, most often without indicating the source of error. If you are in doubt, disable this option and retry taking a backup. This shouldn't be an issue with dedicated hosting, as long as you grant the SUPER privilege to the database user you use to connect to your site's database.
The application is able to split your MySQL database dump to smaller files. This allows for an improved compression ratio and also helps avoid several problems with certain lower end hosts -e.g. Strato.de- which put a restriction on the maximum size a file generated by PHP code can have.
Ideally, you should specify a setting which is about half as much as your Big file threshold setting in the archiver engine's configuration options pane. The reason to do that is that the archiver engines will not compress files with sizes over the value of this threshold. Since it's impossible to have absolute control of the size of the database dump, using half the value of this setting allows for the expected size fluctuation.
If you want to disable this feature and create a single big SQL dump file instead, just set this option to 0 Mb.
This setting has no effect on "Main site database only" backup profiles. This is because the nature of this backup type does not allow splitting the database archive dump. If you want something equivalent, please use the "All configured databases" backup type instead, as it creates an archive file which contains your (split) database dump and takes up MUCH less space on your web server.
Dumping table data happens in "batches", i.e. a few rows at a time. This parameter defines how many rows will be fetched from the table at any given time. If you are backing up tables with large chunks of binary data (e.g. files stored in BLOB fields) or if you have very large chunks of text stored in the database, the default value - 1000 rows - may cause a PHP memory or MySQL buffer exhaustion. If you get memory outage errors during the table backup, it is advisable to lower this setting. This is especially true if your MySQL and PHP combination does not allow a cursor to be effectively created and all data has to be transferred in PHP's memory. A value of 20 is a very safe value, at the expense of making your backup process slower and run more queries against your database server. Most sites and servers work fine with the default value of 1000 rows per batch.
When this option is enabled, the database dump engine will no longer try to figure out table and VIEW dependencies. This will speed up the database dump initialization step. This is recommended if and only if you have too many tables (over 200) in your database, you get timeout errors during the database dump initialization step and you do not use foreign keys, VIEWs, FUNCTIONs, PROCEDUREs, TRIGGERs or any tables using the MERGE database engine. If you do use any of those MySQL features in your tables there is a high probability that your SQL dump will be unable to be restored.
Newer versions of MySQL will include the engine in the definition of the table indexes. At the same time, not all engines are available on all hosts. Most importantly, the most popular local server environments (MAMP, XAMPP and WAMPserver) do not have all these engines enabled. This causes restoration failures. You are advised to always leave this box checked unless you are a database administrator and know perfectly well what you are doing.