Lots of websites run on software that needs to interact with a database. If you’re using WordPress, Drupal, Joomla, or any number of other web applications, you’re more than likely using a MySQL database.

If you’re a beginner, like I once was, after last week’s posts about staging servers you’re probably wondering how to make a copy of your website’s database for your shiny new staging website.

Fortunately, if your web host uses cPanel, it’s not terribly hard to do. Here’s how.

Step 0. Log into cPanel and click into PhpMyAdmin.

cPanel Databases

Step 1. Find the database you want to copy in PhpMyAdmin.

Click on it to open the database.

phpMyAdmin databases

Step 2. Export.

phpMyAdmin Export

Once you click “Export” it’s just a matter of following the prompts. All the default options are probably going to be just fine. The only trick here is sometimes that you’ll need to make sure to save the export to a file rather than just opening it in your text editing app. There’s probably a checkbox for that on the export options screen.

Remember where you save the file. You’ll need it later.

Step 3. If you don’t have another database for the staging site, create it.

If you install a clean copy of your website software on your staging site, chances are you’ve already got a new database for that new installation that was created when you installed it. If that’s your case, you can skip this and go in to Step 4. If not, you’ll need to create a new database yourself.

Most hosts disable creating new databases in phpMyAdmin. So back out to your main cPanel dashboard and this time select MySQL Databases:

MySQL Databases

Here, simply create a new database:

Create Database

Then, a little farther down, create a new user and assign the user to the database you just created. Make sure to:

Add Database User

  • Remember the database name, the database username, and the user’s password (so you can enter it into your web software’s configuration files), and
  • Give your new user all privileges on the new database when asked.

Step 4. Go back to phpMyAdmin and click into the new database.

Then click on “Import” — it’s right next to “Export”. Follow the prompts. Again, the default options are probably fine for most situations.

That’s it!

All that remains is to enter your new database name, database username and password into your web software’s configuration files, and you should be up and running.

Well, almost up and running. You may need to go through your database copy and change some entries, such as the site url to get things to work correctly. What, exactly, you’ll need to do to get it working will depend on what you’re web software is. But, rest assured, you have a database copy now that you can tinker with without worrying about screwing things up on your live site.

(If you happen to be using WordPress, I’ll show you the database entries you’ll need to change to get your staging site running in a future post.)

Database Icon by Tim Morgan