Building on the configuration management strategy I described last time, I wrote some scripts to make it easier for other developers to migrate the production database to the QA server or to get a copy of the production database for their local system. I needed to consider the following factors:

• Domain name changes: Because we use Domain Access to serve multiple subdomains using a single Drupal installation and shared sign-on, we needed to make sure that all instances of the domain root are replaced and the correct domain_root is set. For example, the site URLs might be:
  

  Production	QA	Local
  example.com	qa.example.com	local.example.com
  foo.example.com	foo.qa.example.com	foo.local.example.com
  bar.example.com	bar.qa.example.com	bar.local.example.com

• Privacy concerns: The QA database and local copies of the database should not contain sensitive user information. All users aside from the administrator should be removed from the system, and all content on site should be limited to editorial content.
• Efficiency: We don’t need data like access logs or watchdog logs in QA or for local testing. This saves quite a lot of time and space during the data migration process.

Here’s how I did it:

1. I identified tables I needed to copy over and tables that I could just leave empty. I did this by going through the output of “SHOW TABLES” in a MySQL console.
2. In my Makefile, I declared a DB_DATA variable that contained a list of tables I wanted to copy.
3. I wrote a backup_partial target in my Makefile that issued a series of commands:

   mysqldump -u ${DB_USER} --password=${DB_PASSWORD} ${DB} --no-data > partial.dump  # export the schema
   mysqldump -u ${DB_USER} --password=${DB_PASSWORD} ${DB} --opt --complete-insert ${DB_DATA} >> partial.dump # Dump some of the data
   mysqldump -u ${DB_USER} --password=${DB_PASSWORD} ${DB} --opt --complete-insert --where='uid< =1' users users_roles >> partial.dump # Dump the admin and anonymous users
   echo "UPDATE node SET uid=1;" >> partial.dump # Set all the node authors to admin
   echo "REPLACE INTO search_index_queue (nid, module, timestamp)  select nid, type, unix_timestamp(now()) FROM node;" >> partial.dump # Prepare for reindexing
   

4. I wrote a shell script on an internal server that accepted an argument (the domain to translate to) and performed the following steps:
   1. ssh into the production server and run make with my backup_partial target, compressing the resulting partial.dump
   2. scp the partial.dump.gz from the production server onto the internal server
   3. unpack partial.dump.gz
   4. figure out what $DOMAIN is supposed to be based on the arguments
   5. run perl -pi -e "s/example.com/$DOMAIN/" partial.dump
   6. load partial.dump into my database
   7. run cron.php if it can
5. I added two buttons to my web-based deploy script: one button to migrate the production database to the QA server, one button to make a copy of the production database for the domain “local.example.com”. Both buttons call
6. I created multisite settings.php in my Drupal directory (ex: sites/local.example.com and sites/qa.example.com). The production settings go in default/settings.php, and the multisite settings.php override it like this:

   $conf = array(
     'domain_root' => 'local.example.com',
   );
   $cookie_domain = '.' . $conf['domain_root'];
   

   $conf allows you to override Drupal variables returned by variable_get.

So now, I can click on a button to migrate a sanitized copy of the production database to the QA server or to my local system. Sweet!