Clone remote WordPress database on local machine

Suppose you have a WordPress website on a remote server, and you want to clone it locally. This post documents my current approach to doing this. I’m sure there is a plugin that makes this process easier, but I haven’t had the time to research.

See this post if you want to do the opposite – clone a local WordPress database on a remote server.

Create dump of remote database using TablePlus

(You could also do this via the via the command line).

• If you haven’t already, create a connection to your remote database.
• Click on File > Backup.
• Select database. Click on Add option... from the right and choose --no-tablespaces to suppress a MySQL warning.
• Click Start backup... and choose a location to back up to. $HOME/Dumps or a folder in OneDrive / Dropbox are candidates.

Restore database using dump file using TablePlus

• If you haven’t already, create a connection to your remote database.
• Click on File > Restore.
• Select database.
• Choose the dump file that you created from the previous step.

Update columns in database

You’ll want to update the wp_options.siteurl and wp_options.home columns to the URL that you use to access your WordPress website locally. This can be something like http://localhost:8000 if you’re using PHP’s built-in web server or a local domain like my-app.test if you’ve setup Nginx locally.

UPDATE
	wp_options
SET
	option_value = '<local-domain>'
WHERE
	option_name = 'siteurl'
	OR option_name = 'home';

Transfer media files

If you need to transfer media files to the remote server, see this post.

Update all URLs elsewhere

Use the Better Search Replace plugin to replace all references to your remote URL (e.g., https://sajadtorkamani.com) with your local URL (e.g., http://sajadtorkamani.test).

• Set the Search for field to your remote URL.
• Set the Replace with field to your local URL.
• Select all tables.
• Make sure Run as dry run is checked.
• Click Run Search / Replace.
• Once you’re happy with the dry run, uncheck Run as dry run, and click Run Search / Replace again.