How to Backup and Restore MySQL Database on Hosting with phpMyAdmin

The database is the backbone of most websites — WordPress, WooCommerce, custom membership systems. If your database is lost or corrupted without a backup, the consequences can be catastrophic. This guide walks you through backing up and restoring MySQL on shared web hosting using phpMyAdmin and the DirectAdmin Backup Tool, with no command line required.

1. Why Back Up Your Database?

Common scenarios that damage or destroy databases:

• WordPress plugin updates that conflict with or alter the database schema
• Accidental SQL queries such as DELETE FROM users without a WHERE clause
• Malware attacks that delete or encrypt data
• Failed hosting migrations with corrupt Export/Import operations
• Disk-full conditions causing database corruption

AsiaGB provides automatic backups twice a month (1st and 15th), but these do not replace your own backups. If data is lost on the 10th, you can only restore to the snapshot taken on the 1st — everything between is gone.

2. Export a Database with phpMyAdmin

phpMyAdmin is the most convenient method for databases up to about 50 MB.

Export Steps

1. Log in to DirectAdmin → go to MySQL Management → click phpMyAdmin.
2. Select the database from the left sidebar.
3. Click the Export tab at the top.
4. Choose Method: Custom for more control.
5. Confirm all tables are selected.
6. Format: SQL.
7. Under Object creation options, check Add DROP TABLE — this prevents errors when restoring over an existing database.
8. Click Export — the .sql file will be downloaded to your computer.

Format and Options You Should Know

When you set the Method to Custom, phpMyAdmin exposes options that directly affect how complete and restorable your backup is. Understanding them helps you restore in a single attempt without hitting errors midway:

• Format = SQL — this is the standard format that restores straight back into MySQL. CSV or JSON are for moving data elsewhere, not for restoring an entire database.
• Add DROP TABLE / VIEW / PROCEDURE / EVENT — drops the existing table before recreating it, preventing the Table already exists error when importing over a database that already holds data.
• Add CREATE DATABASE / USE statement — if checked, the file recreates the database itself. On shared hosting we recommend leaving this off, because DirectAdmin database names usually carry a username prefix (e.g. user_dbname), so the name in the file may not match the one you created.
• Add IF NOT EXISTS — keeps statements from erroring when an object already exists, useful when merging data.
• Character set of the file = utf8mb4 — critical for Thai-language sites and any site using emoji. Always choose utf8mb4 so multibyte characters do not turn into question marks on restore.
• Maximal length of created query — the default is fine for most cases; leave it as is.

3. Backup with the DirectAdmin Backup Tool

DirectAdmin's built-in backup tool supports large databases and can create a full backup (files + database) in one operation.

Steps

1. Log in to DirectAdmin.
2. Go to Advanced Features → Create/Restore Backups.
3. Select MySQL Databases (and Home Directory for a full backup).
4. Click Create Backup.
5. Wait for completion — the backup file appears in your home directory.
6. Download it via File Manager or FTP.

4. Restore a Database with phpMyAdmin

To restore a database from a previously exported .sql file:

Create a New Database (if needed)

If the original database was deleted, create a new one first in DirectAdmin → MySQL Management → Create Database. Note the username and password.

Import via phpMyAdmin

1. Open phpMyAdmin → select the target database.
2. Click the Import tab.
3. Click Choose File → select your .sql file (must be under 50 MB).
4. Format: SQL (already selected by default).
5. Click Import and wait for completion.

5. Handling Large Backups When the File Exceeds the Limit

Data-heavy sites — a WooCommerce store with thousands of orders, or a long-running forum — can grow well past 50 MB, where phpMyAdmin import fails. Here is how to deal with it, from the easiest approach to the most advanced:

Method 1 — Export/Import with gzip

Compressing to .sql.gz reduces the file size by 70–80%, often bringing a file that exceeded the limit back into uploadable range. phpMyAdmin imports .gz files directly, with no need to decompress first:

1. During Export, set Compression to gzipped to produce database.sql.gz.
2. To restore, open the Import tab and select the .sql.gz file — phpMyAdmin decompresses it automatically.

Method 2 — Split the File with BigDump

If the file is too large to upload into phpMyAdmin even after compression, use the BigDump script, which reads the .sql file in chunks and runs them as small batches to avoid timeouts:

1. Upload the .sql file and bigdump.php to the same folder via File Manager or FTP.
2. Edit $db_server, $db_name, $db_username, and $db_password inside bigdump.php to match your database.
3. Open the bigdump.php URL in your browser and click Start Import — the script runs continuously in passes.
4. When finished, delete bigdump.php and the .sql file immediately for security; never leave them in a public directory.

Method 3 — Command-Line mysqldump (if your hosting allows it)

If your hosting plan includes SSH access, using mysqldump from the command line is the fastest and most reliable approach for large databases, because it is not bound by the web server's limits:

# Backup — export the whole database and gzip it in one command
mysqldump -u USERNAME -p --default-character-set=utf8mb4 DBNAME | gzip > backup_20260607.sql.gz

# Restore — decompress and import back
gunzip < backup_20260607.sql.gz | mysql -u USERNAME -p DBNAME

# Back up only specific tables
mysqldump -u USERNAME -p DBNAME wp_posts wp_options > partial.sql

6. Automatic Backups and Important Cautions

Remembering to back up manually every week is easy to forget. A more sustainable approach is to automate the backup and be aware of the pitfalls that make a backup unrestorable:

Schedule Automatic Backups with a Cron Job

If your hosting offers the Cron Jobs menu in DirectAdmin, you can schedule mysqldump to run automatically. Here is a cron entry that backs up every day at 2 AM and names files by date:

# Runs daily at 02:00 — set in DirectAdmin → Cron Jobs
0 2 * * * mysqldump -u USERNAME -pPASSWORD --default-character-set=utf8mb4 DBNAME | gzip > ~/backups/db_$(date +\%Y\%m\%d).sql.gz

For WordPress there is an even easier route: plugins like UpdraftPlus or WP-DBManager let you schedule backups and send the files to Google Drive or Dropbox automatically, reducing the risk of keeping backups on the same server.

Caution: utf8mb4 Charset

This is the number-one cause of Thai or emoji characters turning into ??? or ภา after a restore. Make sure the charset at export, the .sql file, and the destination database are all utf8mb4. If you export as latin1 and import into utf8mb4, the characters can be permanently corrupted and very hard to recover — so choose utf8mb4 from the export step onward.

Caution: Foreign Keys and Table Order

Databases with foreign key constraints (for example an orders table referencing customers) may fail to restore if orders is imported before customers exists, triggering Cannot add or update a child row. To handle this:

• Use a backup file from phpMyAdmin/mysqldump, which orders tables and adds SET FOREIGN_KEY_CHECKS=0; at the top — standard tools handle this for you.
• If it still errors, manually add SET FOREIGN_KEY_CHECKS=0; at the start of the .sql file and SET FOREIGN_KEY_CHECKS=1; at the end.
• Match the engine (InnoDB vs MyISAM) — foreign keys only work on InnoDB.

7. AsiaGB Automatic Backup Policy

8. Best Practices

• Back up before every significant change — plugin updates, schema changes, migrations.
• Store backups in multiple locations — local computer plus Google Drive or Dropbox.
• Include the date in the filename — e.g., mysite_db_20260512.sql.
• Test your restores — a backup you have never tested is a backup you cannot trust.
• Compress the file — a .sql.gz file is typically 70–80% smaller.
• Schedule a reminder — monthly for low-traffic sites, weekly for actively updated ones.

Frequently Asked Questions

How often should I back up my database?

It depends on how often your data changes. A news site or online store taking orders every day should back up daily, or at least weekly. A company site that updates rarely is fine with a monthly backup. The guiding question is: "If my data rolled back to the last backup point, could I accept losing what happened in between?" If not, back up more frequently.

Why do Thai characters become question marks after a restore?

This happens when the charset of the backup file and the destination database do not match. Prevent it by selecting utf8mb4 as the Character set at export time, and creating the destination database as utf8mb4 as well. Once the data is corrupted it is hard to recover, so back up with the correct charset before trouble strikes.

AsiaGB already backs up — do I still need my own backups?

Yes. AsiaGB's automatic backups take a snapshot twice a month (the 1st and 15th). If data is lost in between, you can only restore to the most recent snapshot, and anything created afterward is gone. Backing up yourself before significant changes closes that gap.

Is restoring a database over an existing one dangerous?

Importing a file that contains DROP TABLE deletes the current data and replaces it with the backup's data immediately. So always back up the current data before restoring over it — that way, if the backup you are restoring turns out to have a problem, you can return to where you started.