Backup and Restore MySQL Databases

Backup and Restore MySQL Databases: Backups in cPanel and phpMyAdmin

A database contains the dynamic content of your website. For WordPress, this includes posts, pages, users, comments, settings, and plenty of plugin data. E-commerce systems, forums, and customer portals also store critical information inside MySQL or MariaDB databases.

An up-to-date database backup is therefore highly important before running updates, performing migrations, making manual changes, installing plugins, or applying major customizations to your website.

When Should You Back Up a Database?

Creating a database backup is recommended whenever changes to the website or the database structure are imminent. For production environments especially, a backup should always be generated before an actual issue occurs.

• Before WordPress, plugin, or theme updates: If an update causes errors, you can easily restore the database to its previous state.
• Before website migrations: When moving to a new hosting provider or changing domains, the database is typically exported and then imported.
• Before manual database alterations: Direct modifications made inside phpMyAdmin take effect instantly and should always be backed up beforehand.
• Before e-commerce adjustments: Orders, products, and customer data are business-critical and require protection.
• Before testing or development tasks: A backup ensures you can safely revert to a known working setup.

Exporting a Database via phpMyAdmin

phpMyAdmin is the ideal tool when you need to back up a single database as an SQL file. This file can be imported again later on.

1. Log in to cPanel.
2. In the Databases section, open the phpMyAdmin tool.
3. Select the desired database from the left-hand sidebar.
4. Click the Export tab in the top navigation menu.
5. Choose Quick as the export method if you want to create a standard backup of the entire database.
6. Select SQL as the format.
7. Click Go (or OK).
8. Save the downloaded SQL file securely on your computer.

Export Method: Quick or Custom?

Restoring a Database via phpMyAdmin

To restore an SQL file, use the import function inside phpMyAdmin. This process will upload the contents of your SQL file directly into the chosen target database.

1. If needed, first create a new empty database inside cPanel.
2. Assign an appropriate database user with the necessary privileges to this new database.
3. Open phpMyAdmin.
4. Select the target database from the left-hand sidebar.
5. Click the Import tab in the top menu.
6. Choose your .sql file from your computer.
7. Verify that the selected format is set to SQL.
8. Start the process by clicking Go (or Import).

After a successful import, the database tables should become visible in the left sidebar of phpMyAdmin. Once completed, verify that your website connects properly to the database.

Common Restore Mistake: CREATE DATABASE or USE

SQL files generated by external systems sometimes contain instructions to create a new database or select a specific database name. In cPanel, the target database is typically already created and must be selected manually before running the import in phpMyAdmin.

The lines causing these issues usually look like this:

CREATE DATABASE databasename;
CREATE DATABASE IF NOT EXISTS databasename;
USE databasename;

If your import fails with an Access denied error message, open the SQL file using a suitable text editor and remove these lines, provided that the target database has already been set up in cPanel.

Restoring Large Databases

When working with very large SQL files, importing via phpMyAdmin can fail due to upload file size limits, memory limits, or script execution timeouts. The exact thresholds depend on your server and PHP configuration.

Potential solutions include:

• Compressing the SQL file, provided the file extension is supported by phpMyAdmin.
• Splitting the SQL file into smaller, separate chunks.
• Executing the import via SSH, if SSH access is available on your hosting plan.
• Contacting CURIAWEB support if the file cannot be reliably imported via phpMyAdmin.

Database Backups vs. Full Website Backups

A website generally consists of two core parts: files and the database. Both components must be backed up if you want to ensure a complete restore can be done.

Checks to Perform After a Restore

After importing a database, you should thoroughly test your website. Pay specific attention to whether the database connection is fully functional and if the site targets the correct database.

• Database Name: Check that it includes the full cPanel prefix.
• Database User: Ensure the username includes the full cPanel prefix.
• Password: Match it exactly with the password configured inside cPanel.
• Database Host: This is often localhost, unless stated otherwise by your setup.
• User Privileges: Confirm that the user has been mapped to the database correctly.
• Test the Site: Check your homepage, admin login, contact forms, shopping cart, and other key inner pages.

Security Recommendations for Database Backups

• Do not store database backups permanently inside publicly accessible web directories.
• Download your database backups to your local computer or a secure storage location immediately after export.
• Keep backups containing personal data protected against unauthorized access.
• Delete outdated backups when they are no longer required.
• Always perform a fresh backup right before running major changes.

Summary

With phpMyAdmin, you can easily export and import MySQL and MariaDB databases. This is incredibly helpful for website moves, WordPress updates, manual tweaks, or disaster recovery scenarios.

Keep in mind: A database backup is a vital part of data security, but it does not replace a full website backup containing your files.

Are you looking to restore a database or want a backup checked before a migration?
The CURIAWEB Technical Team is happy to support you with database backups, imports, and restorations.

Open a Support Ticket