A proper MySQL database backup is essential before migrating a website, upgrading an application, modifying database tables, changing hosting providers, or performing major maintenance.

phpMyAdmin provides two primary export methods: Quick and Custom. While Quick Export is convenient, Custom Export gives much greater control over what is included in the backup and how the SQL file is generated.

This guide explains how to create a complete MySQL or MariaDB database backup using phpMyAdmin's Custom Export feature, including database structure, table data, indexes, AUTO_INCREMENT values, triggers, routines, and events where supported.

For security, all examples in this article use fictional names:

Website:

examplewebsite.com

Database:

example_database

Database user:

example_dbuser

These names should be replaced with your actual values when performing the procedure.

Why Use Custom Export Instead of Quick Export?

phpMyAdmin's Quick Export is useful when you simply need a standard SQL dump.

However, Custom Export is preferable when you want more control over:

• Tables included in the backup
• Database structure
• Table data
• DROP statements
• CREATE TABLE statements
• AUTO_INCREMENT values
• INSERT syntax
• Triggers
• Events
• Stored procedures and functions, where available
• Character encoding
• Compression
• Compatibility options

For an important production database, Custom Export also lets you review the backup configuration before downloading the file.

Step 1: Open phpMyAdmin

Log in to your hosting control panel and open phpMyAdmin.

Depending on your hosting provider, phpMyAdmin may be available under sections such as:

Databases → MySQL Databases → phpMyAdmin

Once phpMyAdmin opens, select the required database from the left sidebar.

For example:

example_database

Make absolutely sure the correct database is selected before exporting.

Step 2: Check the Database Tables

After selecting the database, phpMyAdmin should display its tables.

Typical tables might look like:

users

customers

orders

settings

products

logs

or, for a WordPress installation:

wp_posts

wp_postmeta

wp_options

wp_users

wp_usermeta

The actual names will depend on the website or application.

Check that the expected tables are present before creating the backup.

Step 3: Open Export

Click:

Export

phpMyAdmin will normally offer:

Quick — display only the minimal options

and

Custom — display all possible options

Choose:

Custom

This exposes the detailed export configuration.

Step 4: Select All Required Tables

Under the table selection section, ensure that all tables are selected when creating a full database backup.

A common backup mistake is exporting only selected tables.

If the database contains 50 tables but only 30 are selected, the SQL file will not represent the complete database.

For a complete backup:

Select All Tables

Unless you deliberately want a partial backup, do not exclude tables merely because they appear unimportant.

Step 5: Select SQL as the Export Format

Under Format, select:

SQL

SQL is normally the appropriate format when the backup may later need to be restored into MySQL or MariaDB.

An SQL dump can contain commands such as:

CREATE TABLE ...
INSERT INTO ...
ALTER TABLE ...

These instructions allow the database structure and data to be reconstructed during import.

CSV, JSON, XML, and other export formats are useful for particular purposes, but they are generally not substitutes for a complete restorable SQL backup.

Step 6: Configure Output Settings

Select:

Save output to a file

This causes phpMyAdmin to generate a downloadable backup file.

You may see additional settings such as:

File name template

The default is normally sufficient.

A backup may be named something like:

example_database.sql

If compression is enabled, it might instead be:

example_database.sql.gz

Step 7: Select the Correct Character Set

For most modern websites and applications, use:

utf-8

or retain the appropriate default UTF-8 setting presented by phpMyAdmin.

The database itself may use character sets or collations such as:

utf8mb4

utf8mb4_unicode_ci

utf8mb4_0900_ai_ci

Do not randomly change database character sets or collations during a routine backup.

Character encoding is particularly important when the database contains multilingual content, special characters, symbols, or emoji.

Step 8: Choose Compression

phpMyAdmin may provide options such as:

• None
• gzip
• zip

For a larger database, gzip is usually a good choice.

For example:

example_database.sql.gz

Compression can substantially reduce the downloaded file size, particularly when the database contains large amounts of repetitive text.

For a small database, selecting None is also perfectly acceptable.

The important point is that compression changes the size of the backup file; it does not mean database records have been intentionally removed.

Step 9: Export Both Structure and Data

For a complete backup, the export needs both:

Structure

and

Data

Structure describes how tables are constructed.

For example:

CREATE TABLE `customers` (
  `id` int NOT NULL,
  `name` varchar(200) NOT NULL,
  `email` varchar(255) DEFAULT NULL
);

Data contains the actual records:

INSERT INTO `customers` VALUES
(1,'Example Customer','customer@example.com');

A backup containing only structure creates empty tables.

A backup containing only data may not be independently restorable because the destination tables must already exist.

Therefore, a normal full backup should contain:

Structure + Data

Step 10: Object Creation Options

phpMyAdmin normally provides several object creation settings.

For a general full backup, consider the following configuration.

Add DROP Statements

Enable:

Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT / TRIGGER statement

When supported by the phpMyAdmin version, this can generate statements such as:

DROP TABLE IF EXISTS `customers`;

before:

CREATE TABLE `customers`;

This can be useful when restoring over an existing database because the old object can be removed before it is recreated.

Important

DROP statements are powerful.

When the backup is imported into an existing production database, they may delete existing tables or other database objects before restoring them.

Always verify the target database before importing such a backup.

Step 11: Add CREATE TABLE Statements

Enable:

Add CREATE TABLE statement

This is essential for a normal complete backup.

Without CREATE TABLE statements, the SQL dump may contain data but not the instructions required to recreate the table structure.

Step 12: Should "IF NOT EXISTS" Be Enabled?

phpMyAdmin may provide:

Add IF NOT EXISTS

This can produce syntax such as:

CREATE TABLE IF NOT EXISTS `customers` ...

This option is not mandatory for every backup.

For a clean restore where DROP statements are used before CREATE statements, IF NOT EXISTS may provide little additional benefit.

It can nevertheless be useful for particular migration or import workflows.

Step 13: Preserve AUTO_INCREMENT Values

Enable:

AUTO_INCREMENT value

Suppose the latest customer record has ID 875.

The database may need to know that the next generated ID should continue from an appropriate value rather than restarting incorrectly.

The dump may contain something similar to:

ALTER TABLE `customers`
  MODIFY `id` int NOT NULL AUTO_INCREMENT,
  AUTO_INCREMENT=876;

Preserving AUTO_INCREMENT information is therefore recommended for a full backup.

Step 14: Enclose Names with Backquotes

Enable:

Enclose table and column names with backquotes

For example:

SELECT `name`, `email`
FROM `customers`;

instead of:

SELECT name, email
FROM customers;

Backquotes help MySQL correctly interpret identifiers and can avoid problems when table or column names conflict with reserved words.

For general-purpose backups, enabling this option is recommended.

Step 15: Data Creation Options

The data section determines how phpMyAdmin writes database records into the SQL dump.

For a normal backup, the function should generally be:

INSERT

For example:

INSERT INTO `customers` VALUES (...);

Step 16: Use Extended Inserts

Enable:

Extended inserts

Instead of generating an individual statement for every row:

INSERT INTO `customers` VALUES (1,'Customer A');
INSERT INTO `customers` VALUES (2,'Customer B');
INSERT INTO `customers` VALUES (3,'Customer C');

phpMyAdmin can combine multiple rows:

INSERT INTO `customers` VALUES
(1,'Customer A'),
(2,'Customer B'),
(3,'Customer C');

Extended inserts usually result in:

• Smaller SQL files
• Fewer SQL statements
• Faster imports
• More efficient backups

For most backups, keep Extended inserts enabled.

Step 17: Complete Inserts

The Complete inserts option includes column names in INSERT statements.

For example:

INSERT INTO `customers`
(`id`,`name`,`email`)
VALUES
(1,'Customer A','customer@example.com');

This is more explicit than:

INSERT INTO `customers`
VALUES
(1,'Customer A','customer@example.com');

Complete inserts can improve readability and make certain imports more resilient to column-order differences, but they also increase backup size.

For a standard full backup where the schema is restored together with the data, leaving Complete inserts OFF is generally reasonable.

Step 18: INSERT IGNORE

For a normal full database backup, keep:

INSERT IGNORE: OFF

INSERT IGNORE can cause MySQL to continue past certain insertion errors rather than treating them normally.

That may be useful for specialized migration scenarios, but it is generally not what you want in a faithful backup/restore process.

Errors during restoration can indicate important inconsistencies that should be investigated.

Step 19: INSERT DELAYED

Keep:

INSERT DELAYED: OFF

INSERT DELAYED is obsolete for modern MySQL use and is unnecessary for normal backup and restore operations.

Step 20: Truncate Table Before Insert

For a normal portable backup, keep:

Truncate table before insert: OFF

If DROP and CREATE statements are already being used, tables will be recreated before the data is inserted.

TRUNCATE TABLE is therefore generally unnecessary.

It can also be destructive if the dump is accidentally imported into a database containing data that was not intended to be removed.

Step 21: Maximal Length of Created Query

Some phpMyAdmin versions provide a setting such as:

Maximal length of created query

A typical default may be around:

50000

The exact default varies.

There is usually no need to set this to an extremely large value.

This option controls how large generated INSERT statements can become.

On shared hosting, excessively large SQL statements may create import problems because of server limits.

Unless you have a specific reason to change it, the phpMyAdmin default is generally a sensible choice.

Step 22: Hexadecimal for Binary Columns

If available, enable:

Use hexadecimal for binary columns

Binary information is safer to represent in hexadecimal form inside a text-based SQL dump.

This is particularly useful when tables contain binary values that might otherwise be difficult to represent reliably in plain SQL text.

Step 23: Export Triggers

If your database uses triggers, make sure they are included.

A trigger is database logic that executes automatically when particular events occur.

For example:

CREATE TRIGGER ...
AFTER INSERT ON ...

A database can appear to restore correctly while application behavior changes because its triggers were not restored.

Therefore:

Dump triggers: ON

when applicable.

Step 24: Export Stored Procedures and Functions

Some databases use stored routines, including:

• Stored procedures
• Functions

If your phpMyAdmin and hosting account provide an option to export routines, include them when creating a complete backup.

Not every website uses stored routines, but applications that do may depend on them.

Step 25: Export Events

MySQL Event Scheduler can execute scheduled database tasks.

For example:

CREATE EVENT ...
ON SCHEDULE ...

If the database uses events, include them in the backup.

Note that exporting an event and having it actually execute on the destination server are different matters. The destination server must support and permit the MySQL Event Scheduler.

Recommended phpMyAdmin Custom Export Configuration

For a typical full MySQL backup, the configuration can be summarized as:

Export Method:
Custom

Tables:
Select All

Format:
SQL

Output:
Save output to a file

Compression:
gzip for larger databases
None is acceptable for smaller databases

Character Set:
UTF-8 / appropriate existing setting

Export:
Structure + Data

Object Creation:
DROP statements = ON
CREATE TABLE = ON
IF NOT EXISTS = Optional
AUTO_INCREMENT = ON
Backquotes = ON

Data:
INSERT = ON
Extended Inserts = ON
Complete Inserts = OFF
INSERT IGNORE = OFF
INSERT DELAYED = OFF
Truncate before INSERT = OFF

Binary:
Hexadecimal for binary columns = ON

Database Objects:
Triggers = ON
Events = ON, if applicable
Routines = ON, if available and applicable

Exact option names and availability vary between phpMyAdmin versions and hosting providers.

Why Can a 400 MB Database Produce Only a 30 MB SQL Export?

This is one of the most common sources of confusion.

Suppose a hosting control panel reports:

Database size: approximately 400 MB

but phpMyAdmin produces:

SQL backup: approximately 30 MB

This does not automatically mean the backup is incomplete.

The two figures can represent very different things.

1. Database Storage Is Not the Same as SQL Dump Size

MySQL stores databases using database-engine files and internal storage structures.

The reported storage footprint can include:

• Table data
• Indexes
• Allocated pages
• Free space
• Fragmentation
• Internal overhead
• Engine-specific structures

An SQL dump is a logical representation of the database.

It typically contains SQL instructions needed to recreate tables and insert their data.

It does not make a byte-for-byte copy of MySQL's internal storage files.

2. Indexes Can Consume Significant Space

Consider a table with millions of records and several indexes.

The database server may report:

Table data: 120 MB
Indexes: 180 MB
Total: 300 MB

The SQL backup does not need to export an index as a 180 MB binary structure.

Instead, it can contain the definition:

KEY `idx_email` (`email`)

When restored, MySQL rebuilds the index.

This can make the SQL dump substantially smaller than the database's reported storage size.

3. Free or Fragmented Space

Tables can grow as data is inserted and updated.

After records are removed, the physical allocation does not necessarily shrink immediately.

For example, a table could have:

Allocated storage: 200 MB
Current logical data: 60 MB

A logical SQL export may primarily represent the current records and schema, not all unused allocated storage.

4. Compression Makes the Difference Even Larger

Suppose:

Uncompressed SQL:
80 MB

After gzip compression:

Compressed backup:
15 MB

This can be completely normal.

SQL text often compresses very efficiently because it contains repetitive table names, field names, syntax, and similar data.

Therefore, comparing:

hosting database size

directly with:

compressed .sql.gz size

is not a reliable backup validation method.

5. A Small Backup Can Still Indicate a Problem

Although a smaller SQL dump can be normal, you should not assume every small export is complete.

Check for:

• Were all tables selected?
• Was Structure and Data selected?
• Did phpMyAdmin report an export error?
• Were large tables excluded?
• Was the browser download interrupted?
• Did the server hit a PHP timeout?
• Did phpMyAdmin hit memory limits?
• Was only structure exported?
• Was only a subset of rows exported?
• Was compression enabled?

Backup validation is more reliable than comparing file sizes.

How to Verify the SQL Backup

After downloading the backup, do not immediately assume the backup is valid just because a file exists.

Perform basic checks.

Check the File Size

Confirm that the file is not:

0 KB

or suspiciously tiny for a database known to contain significant data.

However, remember that size alone does not prove completeness.

Inspect the SQL File

For an uncompressed .sql file, open it using a capable text editor if the file is not excessively large.

You should see SQL statements such as:

CREATE TABLE

and:

INSERT INTO

If the file contains CREATE TABLE statements but no INSERT statements for tables that definitely contain data, you may have exported structure only.

Check Large Tables Specifically

Before exporting, review the database's table list in phpMyAdmin.

Identify the largest tables.

Suppose you see:

orders          85 MB
order_items     60 MB
customers       20 MB
activity_log    150 MB

After export, search the SQL file for these table names.

You should find their schema and, where applicable, data statements.

This is more useful than simply comparing the total backup size with the hosting panel's reported database size.

Best Verification: Test Restore

The strongest practical validation is to restore the backup into a separate test database.

For example:

Original:

example_database

Test:

example_database_restore_test

Import the backup into the test database and verify:

• Tables exist
• Table counts are reasonable
• Important records exist
• Application-specific relationships appear intact
• Views work
• Triggers exist
• Routines exist, if applicable
• Events exist, if applicable
• AUTO_INCREMENT settings are appropriate

For an important production system, a backup that has never been tested should not be treated with the same confidence as a backup that has successfully completed a restore test.

What If phpMyAdmin Cannot Export a Large Database?

Shared hosting environments commonly impose limits on:

• PHP execution time
• PHP memory
• HTTP request duration
• Download size
• Database connection duration

For a large database, phpMyAdmin may therefore be less reliable than command-line tools.

When SSH access is available, mysqldump is commonly used:

mysqldump -u example_dbuser -p example_database > example_database.sql

For compression:

mysqldump -u example_dbuser -p example_database | gzip > example_database.sql.gz

Do not place the database password directly in a command that may be saved in shell history.

Command-line backup availability depends on the hosting provider and account permissions.

Full Backup vs Partial Backup

A full logical backup normally includes:

• All required tables
• Table structures
• Table records
• Index definitions
• Primary keys
• AUTO_INCREMENT information
• Views
• Triggers, where applicable
• Stored procedures/functions, where applicable
• Events, where applicable

A partial export may intentionally contain only:

• One table
• Selected tables
• Structure only
• Data only

Partial exports are useful for development and troubleshooting, but should not be confused with a complete database backup.

Security Recommendations

Database backups can contain extremely sensitive information.

Depending on the application, an SQL file may contain:

• Customer names
• Email addresses
• Phone numbers
• Addresses
• User account information
• Password hashes
• Application configuration
• API-related configuration
• Order information
• Business records

Treat SQL backups as sensitive files.

Do not upload database backups to publicly accessible website folders.

Avoid locations such as:

public_html/backups/

especially when files can potentially be downloaded over the web.

Store backups in a protected location and remove temporary copies from hosting accounts after they are no longer required.

Do Not Share Real Database Credentials in Screenshots

When requesting technical support, hide or replace:

• Database name
• Database username
• Password
• Server hostname where sensitive
• IP addresses where appropriate
• API keys
• Authentication tokens
• Customer information

For documentation, use examples such as:

examplewebsite.com

example_database

example_dbuser

instead of exposing production information.

Recommended Backup Strategy

A good backup strategy should not depend on a single SQL file stored in one location.

Consider maintaining:

1. Hosting-provider backup
2. Manual SQL backup
3. Off-site backup
4. Periodic restore test

For business-critical systems, backups should be automated and monitored.

A backup process that silently fails for several months can be more dangerous than having no backup policy at all, because it creates false confidence.

Before Major Website Changes

Always create a fresh database backup before:

• Website migration
• CMS upgrade
• Plugin installation or upgrade
• Theme modification
• Database optimization
• Bulk search and replace
• Application upgrade
• PHP version change
• Major code deployment
• Database schema modification
• Hosting migration

Also back up the website files.

Remember:

Database backup ≠ complete website backup.

A website may depend on files containing:

• Uploaded images
• Documents
• Themes
• Plugins
• PHP source code
• Configuration files
• .htaccess
• Application assets

For disaster recovery, both website files and the database may be required.

Frequently Asked Questions

1. Should I use Quick or Custom Export in phpMyAdmin?

Quick Export is suitable for simple exports. Custom Export is preferable when you want to review exactly what is included and configure structure, data, DROP statements, triggers, compression, and other options.

2. Which format should I select for a MySQL backup?

For a normal restorable MySQL or MariaDB backup, select SQL.

3. Should I select all tables?

Yes, when your goal is a complete database backup.

4. Should I export Structure and Data?

Yes. Structure recreates database objects and tables, while Data restores their records.

5. Should I enable DROP TABLE?

It is useful for backups intended to recreate tables during restoration. However, importing a dump containing DROP statements into the wrong database can delete existing objects, so verify the destination carefully.

6. Should CREATE TABLE be enabled?

Yes for a normal complete backup.

7. Should AUTO_INCREMENT values be included?

Yes. This helps preserve the correct sequence information for auto-generated IDs.

8. Should Extended Inserts be enabled?

Generally yes. They reduce the number of SQL statements and can improve dump size and import performance.

9. Should Complete Inserts be enabled?

Not necessarily. Complete inserts explicitly list columns and can improve readability or portability in some scenarios, but they increase file size. For a routine schema-and-data backup, they can usually remain disabled.

10. Should INSERT IGNORE be enabled?

Normally no. For a faithful restore, database errors are usually something you want to identify rather than silently bypass.

11. Should "Truncate table before insert" be enabled?

Normally no for a standard full backup, particularly when DROP and CREATE statements are already included.

12. Should I use gzip compression?

Yes, especially for larger SQL exports. SQL text normally compresses very well.

13. Does gzip remove database information?

No. gzip compresses the exported file. Decompressing it restores the original SQL content.

14. Why is my SQL export much smaller than the database size shown by my hosting provider?

The hosting figure can include indexes, allocated space, fragmentation, and database-engine overhead. A logical SQL dump represents schema and data differently, and compression can make the downloaded file even smaller.

15. Does a 30 MB SQL file mean a 400 MB database was not fully exported?

Not necessarily. It may be perfectly valid. Check that all tables, structure, and data were exported, and ideally perform a test restore.

16. Are indexes included in an SQL backup?

Their definitions normally are when structure is exported. MySQL can rebuild the physical index structures during restoration.

17. Should triggers be exported?

Yes, when the database uses them.

18. Should stored procedures and functions be exported?

Yes when they are used and phpMyAdmin provides the required export option.

19. Should MySQL events be included?

Yes if the application depends on scheduled database events.

20. What is the safest way to confirm that a backup works?

Restore it into a separate test database and verify the resulting database.

21. Can phpMyAdmin export very large databases?

It can, but hosting limits may cause problems. For large databases, command-line tools or the hosting provider's backup system may be more reliable.

22. Can I restore a .sql.gz file directly?

Many phpMyAdmin installations support compressed SQL imports, but the allowed formats and upload-size limits depend on the server configuration.

23. Is a database export enough to back up my complete website?

No. Website files and the database should both be backed up.

24. Where should SQL backups be stored?

Prefer a protected local, server, or off-site backup location that is not publicly accessible through the website.

25. How often should databases be backed up?

It depends on how frequently the data changes and how much data the business can afford to lose. Frequently changing production databases generally require more frequent automated backups than static websites.

Conclusion

phpMyAdmin's Custom Export provides a practical way to create a complete logical MySQL or MariaDB database backup when configured correctly.

For a typical full backup, select:

Custom → All Tables → SQL → Structure + Data → CREATE TABLE → AUTO_INCREMENT → Extended Inserts → Triggers → Events/Routines where applicable → gzip for larger databases.

DROP statements can also be included when the intended restore procedure requires existing objects to be replaced.

Most importantly, do not judge backup completeness only by comparing the SQL file size with the database size reported by the hosting panel.

A database reported as hundreds of megabytes may legitimately produce a much smaller SQL or compressed SQL backup because indexes, unused allocation, fragmentation, internal storage structures, and compression affect the numbers differently.

The best proof of a backup is a successful test restoration.

 

#phpMyAdmin #MySQL #DatabaseBackup #SQLBackup #MySQLBackup #Database #SQL #WebHosting #WebsiteBackup #DatabaseExport #phpMyAdminExport #CustomExport #DatabaseSecurity #DataBackup #BackupStrategy #MySQLDatabase #SQLExport #DatabaseMigration #WebsiteMigration #DatabaseRestore #MySQLRestore #phpMyAdminBackup #MySQLTips #DatabaseTips #WebDevelopment #WebsiteManagement #Hosting #SharedHosting #ServerManagement #DataProtection #DisasterRecovery #BackupAndRestore #mysqldump #DatabaseAdmin #DBA #DatabaseManagement #TechGuide #TechnicalSupport #ITSupport #SystemAdministrator #SysAdmin #WebAdministrator #DataSecurity #MySQLTutorial #phpMyAdminTutorial #SQLDatabase #DatabaseOptimization #WebsiteSecurity #BackupBestPractices #TechnicalGuide