Databases for Site Owners: Create, Back Up and Restore
Understand what your site's database holds, then create one, export a backup and import it again from your control panel.
// A database setup has 3 parts database: user1_site user: user1_admin access: ALL PRIVILEGES // WordPress reads them from wp-config.php
🧰 What you need first
- Access to your control panel
- A recent backup
🎯 What you'll learn
- Create a database and user
- Export a database backup
- Import a backup safely
🛠 Tools needed
- MySQL Databases (cPanel)
- phpMyAdmin
What's in the database
Your site's files hold the design and code. The database holds the content: posts, pages, users, settings and comments. A WordPress site with a lost database is an empty site.
MySQL / MariaDB
The database systems most hosts use. They work the same way for site owners.
Create a database and user
- In cPanel, open MySQL Databases (or the Database Wizard).
- Create a new database. cPanel adds your account name as a prefix, like
user1_site. - Create a new user with a strong password. Save it in your password manager.
- Under Add User to Database, pick both and choose ALL PRIVILEGES.
Export a backup
- Open phpMyAdmin and click your database on the left.
- Click Export, keep Quick and SQL selected, then click Export.
- Store the .sql file somewhere safe.
Import a backup
Warning: Importing into a database that already has tables can overwrite them. Export a fresh backup before you import anything.
- In phpMyAdmin, click the database you want to fill.
- Click Import and choose your .sql file.
- Click Import at the bottom and wait for the success message.
Note: Large files may hit the upload limit. Raise it in your PHP settings, or ask your host to import it for you.
Connect your site
For WordPress, the database details live in wp-config.php:
define( 'DB_NAME', 'user1_site' );
define( 'DB_USER', 'user1_admin' );
define( 'DB_PASSWORD', 'your-strong-password' );
define( 'DB_HOST', 'localhost' );
Common problems
"Error establishing a database connection"
Why: The details in wp-config.php don't match, or the user isn't added to the database.
Fix: Check every value in wp-config.php and confirm the user has privileges on that database.
Import fails with a timeout or size error
Why: The file is too big for phpMyAdmin.
Fix: Compress it as .zip or .gz, raise the PHP limits, or ask your host for help.
Key takeaways
Keep going
- Move a whole site: Migrating a Site to a New Host
- Automate it: Setting Up Automatic Backups
Enjoyed this guide?
Support free learning at mC0de.
