How to copy data from live site to test site Drupal

  • Posted on: 8 May 2017
  • By: jmu

I am working on Drupal 7.54 and using Secure Page module to control the SSL. If you are trying to create a test site for your existing live site, this official Drupal website is good enough. ( If your test website works like charming, then you don't need to waste your time to read this blog anymore. Here, I just want to share some solutions that I used when I try to make my test website work.

Let's start. 

If you haven't exported data from your live database yet, here are some little tips:

  1. Do not export data and structure at the same time. 
  2. Adding --single-transaction when you use mysqldump command (REASON)

Here is a setup checklist after you copying the database:

  1. Make sure you change the db config in var/www/yourtheme/sites/default/settings.php file
  2. Make sure you change $base_url to your test site in settings.php
  3. Make sure you run update.php after you config the new database in your test website
  4. Make sure you change $update_free_access to FALSE after you update your website
  5. Make sure you set your settings.php file permission back to 644 once everything was done

Once those things were done, the website should be work. But what if you find your test site keep point to your live site? Here is the solution:

  1. Clear all cache table -- you can use "TRUNCATE tableName" 
  2. Clean your website cache -- if you are using chrome, you can find "clear browsing data" in advanced setting
  3. Check if you set anything in .htaccess file
  4. Check "variable" table if there is any value contains your live site URL -- How to check and modify method is below

If you are not familiar with MySQL database query, you can follow these steps:

  1. SELECT *,CONVERT(value USING utf8) FROM variable where value like '%yourLiveSiteURL%';

    This query is for you to search if there is any value includes your live site url. We have to convert the "value" because Drupal stores value as BLOB in this table. After executing the query, if there is any row showed up, then it might be the reason why you keep jump to your live site. In my case, I am using Secure Page module in my website, therefore, all my test web page used live domain based on this module setting.

  2. Here are two queries I used to modify the value:

    update variable set value=replace(value, 's:21:"";', 's:24:"";') where name ='securepages_basepath';

    update variable set value=replace(value, 's:0:"0";', 's:1:"0";') where name ='securepages_enable';  

    Be sure you modify the "S:Number" when you change the URL. The number means how many characters in the following quote. For example, in my case, "" includes 21 characters, so I used "s:21" in my query. If you don't want to count your domain name, you can use this online tool: letter count ( It is a very simple but helpful tool that I used. Besides, if you also use Secure Page Module, don't forget to set the "enable" as 0. Otherwise, your test website will still jump to your live site.

  3. After modifying the database, it would be better to clean cache ("drush cc all" OR go to "admin/config/development/performance") and run update.php again to make sure all your settings are used.

If your test site still not working, feel free to leave me a message. smiley

Add new comment

This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.
Enter the characters shown in the image.