MySQL: How to copy database from web host to localhost

Let’s say you want to copy the MySQL database used on a website to a local installation of PHP/MySQL for testing. In order to do this, you will need the following:

  • Access to the cpanel of the website that has phpMyAdmin installed
  • An Apache server with phpMyAdmin installed locally. For this, you can use Bitnami’s WAMP stack if you’re on Windows, or their MAMP installation package if you’re developing on a Mac.

Step 1: Export the website database

Once you have logged into the website cpanel, click to open the phpMyAdmin.

Inside the phpMyAdmin tool, select the database that you want to copy (on the left) and then click the Export tab and choose the SQL format option. Then click the “Go” button. For example:

Then in the dialog box that pops up, choose “Save File” to save the database.sql file to your hard drive.


Step 2: Import the database locally

First, log into your local phpMyAdmin tool. In my case it was at 127.0.0.1:8080/phpmyadmin:

Then, when inside phpMyAdmin, create a new database to house the database copy that you are going to be importing. Note: I gave my database the exact same name as it has on the website.

With the new database created locally, it should appear on the left and it will be an empty database with no tables yet. For example:

Now that the empty database exists locally, we can import the database that we exported from the website. Select the new empty database, click the “Import” tab, then “Choose File”.

After selecting the exported database, click “Go”.

When the import process completes, there should be a success message.

Success! Now you should also see the imported database tables.