Clone local WordPress database on remote server

Suppose you have a WordPress website on your local machine, and you want to clone it on a remote server. 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 remote to local.

Create mysqldump on local machine

Use a GUI tool (e.g., TablePlus) to make this easier or do it via the command line. Either way, you want to create a .dump file that contains all the SQL statements needed to recreate your database on the remote server. Something like this:

DROP TABLE IF EXISTS `wp_commentmeta`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 SET character_set_client = utf8mb4 ;
  `meta_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `comment_id` bigint unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `meta_value` longtext COLLATE utf8mb4_unicode_520_ci,
  PRIMARY KEY (`meta_id`),
  KEY `comment_id` (`comment_id`),
  KEY `meta_key` (`meta_key`(191))
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
// Blah blah blah

Recreate database on remote machine

Apply the mysqldump to your production server. Again, you can use a GUI tool or the command-line.

Update site URL and name

Run the following SQL query:

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

Update all URLs elsewhere

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

Transfer media files

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