WordPress – Steps to import MySQL DB from online host to localhost

Steps I use to import data (ie posts, settings info etc) from my online hosting account to the MySQL database on my localhost (ie my own pc). This will not import any new plugins, updates or ploaded images or files. This post covers exporting and importing the wordpress Database.

NOTE: I do not claim this is the most efficient or the most secure method. It is simply how I do it.

I use:

  • XAMPP to run the apache server
  • MySQL workbench to import the DB file onto my pc
  • phpMyAdmin via my online hosts cpanel to create the export file

Steps in short:

  1. Backup database from localhost using MySQL client ( I use MySQL workbench)
  2. Export data from online hosting site using phpMyAdmin
  3. Modify site settings in exported file
  4. Import data from SQL file created in phpMyAdmin

Details:

1. Backup database from localhost using MySQL client. Screen shots shown here are using MySQL workbench…

Export as a single file:

Archive the file for backup. I typically give the file a more meaningful name and I will select the ‘include create schema’ box as shown below:

2. Export data from online hosting site using phpMyAdmin
i. Access hosting account using cpanel
ii. Locate and open phpMyAdmin (in cpanel Database category)
iii. Use phpMyAdmin to export a copy of the desired online database by:

a) Locate database in database list

 

 

 

 

 

 

 

 

b) Use ‘Export’ function to view export options:

 

c) Leave most options but select ‘Add drop table’ to renew all tables within database. Note: select ‘create database’ as well if you intend on refreshing the database completely (you will need to drop the database).

d) Select ‘go’ to run the export function and save the outputed file in a suitable location (ie archives)

 

3) In exported SQL file, look for the site domain name that is specified in ‘wp_options’ table script. For example, looking for site dradma.com…

 

Modify these lines to work on the localhost. For example….

4. Import data from SQL file created in phpMyAdmin using selected MySQL client program. Below shows MySQL workbench process…open SQL file (phpMyAdmin export), run SQL script and check the database has successfully been imported.

 

 

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *