When creating a backup with Duplicator, selecting the correct Database SQL Mode is crucial for ensuring a smooth and successful process.

Duplicator offers two SQL Modes under Settings » Backups: MySqlDump and PHP Code. Each has its strengths and weaknesses, and choosing the right one depends on your specific server configuration and database size.

Introduction to Database SQL Modes

Duplicator provides two primary SQL Modes to handle database during backup creation:

MySqlDump: A command-line utility used to dump databases in a very efficient and fast manner.

PHP Code: A PHP-based method for exporting databases, designed to handle larger databases in chunks to avoid server resource limitations.

Each mode is tailored for different scenarios, and understanding their pros and cons will help you choose the best one for your situation.

MySqlDump Mode

MySqlDump is the default SQL Mode set by Duplicator because it is generally the fastest and most efficient method for database export.

Advantage

• Speed: MySqlDump operates at the command-line level, making it significantly faster compared to PHP-based methods.
• Efficiency: It handles large databases more efficiently on servers with high resource availability.
• Low Resource Usage: Ideal for smaller databases with sufficient server resources.

Disadvantages

• Timeout and Memory Issues: On shared or low-resource servers, MySqlDump may encounter timeout and memory limitations, especially with larger databases.

Please Note: If MySqlDump is not detected, then this mode will be disabled.

Use Case: MySqlDump is best for smaller databases on servers with sufficient resources and shell access.

PHP Code Mode

PHP Code mode uses PHP scripts to export the database. It is designed to handle databases in smaller chunks, reducing the likelihood of server resource issues.

Advantages

• Reliability: Handles larger databases without timeout or memory issues by breaking the export into smaller, manageable chunks.
• Compatibility: Works on any server that supports PHP, making it a more universally compatible option.

Disadvantages

• Reduced Performance: Unlike MySqlDump, which can utilize the full power of the server’s processor, PHP Code mode breaks the database export into smaller pieces. This means it can’t take full advantage of the server’s resources, making it slower and less efficient for large databases.

Use Case: PHP Code mode is recommended for larger databases or servers with strict resource limitations.

Recommended Configurations

Fastest Configuration: MySqlDump + ShellZip

Best for: Servers with good resource availability and shell access. This configuration provides the fastest backup creation but is subject to timeout and memory issues for very large databases.

Safest Configuration: PHP Code + DupArchive

Best for: Any server, especially those with limited resources or large databases. This configuration is slower but highly reliable.

Balanced Configuration: PHP Code + PHP Zip

Best for: Servers with decent resources that might not support MySqlDump consistently. It provides a good balance between speed and reliability.

Choosing the Right SQL Mode for Your Setup

Choosing the right SQL Mode in Duplicator is critical for a smooth backup process. While MySqlDump is faster and efficient for smaller databases on well-equipped servers, PHP Code offers a reliable solution for larger databases or resource-constrained environments. Evaluate your server’s capabilities and database size to select the best configuration that suits your needs.

For further assistance or specific configuration recommendations, please contact support.