Support

Documentation

Database dump engines

Native MySQL Backup Engine

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 ground-breaking features available in MySQL 5, such as views, stored procedures and functions, triggers, merge tables, temporary/memory tables, even federated tables.

[Important]Important

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

MySQL Compatibility

This option controls the MySQL version compatibility when creating the database SQL dump file. In fact, it forces Akeeba Backup to request the appropriate CREATE TABLE commands from your database server. It is useful when migrating your site to another host with a different MySQL version. The available options are:

  • Default. This is the recommended option. The full feature set of your database server will be used when generating the CREATE command. Your target database server must run MySQL of a matching major version, i.e. MySQL 5 if the host you're backing up runs on MySQL 5.

  • MySQL 4.1. Akeeba Backup will request from your database server to provide definitions (CREATE commands) in a MySQL 4.1 friendly format.

    [Important]Important

    This option will take effect in MySQL 4.1 or greater database hosts. If you use it on older MySQL version the backup might fail!

    [Warning]Warning

    Do not use this option if your site is already running on MySQL 4.x or if both your site and the target host run on MySQL 5.x. Otherwise, crucial information about the database's encoding might be lost in the process, causing broken text on sites using non-ASCII character sets.

Blank out username/password

When enabled, Akeeba Backup will not include the username and password of database connections in the backup. 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 does not remove the database connection information from the configuration.php file of Joomla!. If you want to remove the database connection information for security reasons you should exclude configuration.php from your backup using the Files and Directories Exclusion filter feature of Akeeba Backup.

Generate extended INSERTs

When this is not checked, Akeeba Backup will create one INSERT statement for each data row of each table. When you have lots of rows with insignificant amount 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. 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 less 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, unless you are going to restore to a MySQL 4.1 host.

Max packet size for extended INSERTs

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.

Dump PROCEDUREs, FUNCTIONs and TRIGGERs

By default, Akeeba Backup 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 you the database tables filter page is blank you must turn this option off for Akeeba Backup to work properly.

[Warning]Warning

Using this feature requires that your host allows you to execute privileged SQL commands against the MySQL database:

  • SHOW PROCEDURE STATUS

  • SHOW FUNCTION STATUS

  • SHOW TRIGGERS

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

Size for split SQL dump files

Akeeba Backup 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 cheap hosts 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 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.

[Important]Important

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.

Number of rows per batch

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 servers work fine with the default value of 1000 rows per batch.

No dependency tracking

When this option is enabled, Akeeba Backup's 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.

Reverse Engineering Database Dump Engine
[Warning]Warning

Due to its nature we consider this method a beta feature.

This engine will take a backup of your database by reverse engineering its structure. This is the only possible method for non-MySQL databases (PostgreSQL, SQL Server, Windows Azure SQL). This database dump engine only supports a rudimentary feature set of your database server: tables and views only with their constraints and foreign key relations. It doesn't support advanced entities such as triggers, procedures and functions. The supported feature set should be adequate for backing up a Joomla! site.

[Important]Important

Reverse engineering the database structure usually requires adequate privileges for the database user during the backup process. The same goes for restoration of VIEWs during the restoration process. Most hosts do not assign this kind of privileges. If your backup or restoration fails with a database error when backing up or restoring your site you may have to ask your host to assign those privileges to your database user.

Blank out username/password

When enabled, Akeeba Backup will not include the username and password of database connections in the backup. 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 does not remove the database connection information from the configuration.php file of Joomla!. If you want to remove the database connection information for security reasons you should exclude configuration.php from your backup using the Files and Directories Exclusion filter feature of Akeeba Backup.

Generate extended INSERTs

When this is not checked, Akeeba Backup will create one INSERT statement for each data row of each table. When you have lots of rows with insignificant amount 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. 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 less 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, unless you are going to restore to a MySQL 4.1 host.

Max packet size for extended INSERTs

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

Size for split SQL dump files

Akeeba Backup 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 cheap hosts 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 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.

[Important]Important

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.

Number of rows per batch

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 servers work fine with the default value of 1000 rows per batch.

Dump PROCEDUREs, FUNCTIONs and TRIGGERs

By default, Akeeba Backup will only back up database tables and VIEWs. If your host supports this, you can also back up and restore advanced aspects of your 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 you the database tables filter page is blank you must turn this option off for Akeeba Backup to work properly.

[Warning]Warning

THIS OPTION CURRENTLY HAS NO EFFECT! We are working on providing a solution in a future version.

No dependency tracking

When this option is enabled, Akeeba Backup's 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 or VIEWs. If you do use any of those database features there is a high probability that your SQL dump will be unable to be restored.