Content tagged with: database

Setting up a multi-site Drupal 6 installation with shared databases and single sign-on

Submitted by Eric on Mon, 09/07/2009 - 13:33

In this blog entry, I'll explain how I setup a multi-site Drupal 6 installation with shared databases and single sign-on. This will enable you to store your users (and other desired tables) in a separate database, and share them across multiple sites.

Before installing Drupal I created 3 databases, added 2 mysql users, and granted permissions. Both mysql users will have access to the shared database which will contain the shared tables:

$ mysql
# create new databases:
mysql> create database drupal_ms_1;
mysql> create database drupal_ms_2;
mysql> create database drupal_ms_shared;

# create new user "drupal_ms_1" and grant privileges to the databases:
mysql> grant all privileges on drupal_ms_1.* to 'drupal_ms_1'@'localhost' identified by 'drupal_ms_1';
mysql> grant all privileges on drupal_ms_shared.* to 'drupal_ms_1'@'localhost' identified by 'drupal_ms_1';

# create new user "drupal_ms_2" and grant privileges to the databases:
mysql> grant all privileges on drupal_ms_2.* to 'drupal_ms_2'@'localhost' identified by 'drupal_ms_2';
mysql> grant all privileges on drupal_ms_shared.* to 'drupal_ms_2'@'localhost' identified by 'drupal_ms_2';

For my setup I wanted to use a single Drupal filesystem, so I created a name-based Apache virtualhost to host both domain names (ms1.erl.dev & ms2.erl.dev):

NameVirtualHost *:80

<VirtualHost *:80>
ServerName ms1.erl.dev
ServerAlias ms2.erl.dev
DocumentRoot /var/www/vhosts/ms.erl.dev/httpdocs
ErrorLog logs/ms.erl.dev-error_log
CustomLog logs/ms.erl.dev-access_log common
</VirtualHost>

I downloaded and unpacked the Drupal installation file:

$ cd /var/www/vhosts
$ mkdir ms.erl.dev
$ cd ms.erl.dev
$ wget http://ftp.drupal.org/files/projects/drupal-6.13.tar.gz
$ tar -xzf drupal-6.13.tar.gz
$ mv drupal-6.13 httpdocs

I setup 2 new sites folders in the sites folder, and copied the default.settings.php file into my first site:

$ cd httpdocs/sites
$ mkdir ms1.erl.dev
$ mkdir ms2.erl.dev
$ cp default/default.settings.php ms1.erl.dev

At this point my databases and filesystem were ready to go so I installed Drupal for the first site (ms1.erl.dev). During installation, I entered "localhost" for the database host, drupal_ms_1 as the database, and drupal_ms_1 as the database user. I left the db_prefix setting blank (we'll change that later).

Now that Drupal was installed and the tables were created, it was time to modify the installation to enable single sign-on and shared databases.

You'll need to decide which tables to share across the sites. I decided on users, sessions, and authmap (but you could add more as desired, like roles, profile_*, etc). I used the following commands to transfer the desired tables from the drupal_ms_1 database into the shared database, remove the tables from drupal_ms_1, and then replicated drupal_ms_1 to drupal_ms_2:

$ mysqldump drupal_ms_1 users sessions authmap | mysql drupal_ms_shared
$ mysql drupal_ms_1
mysql> drop table authmap, sessions, users;
mysql> exit
$ mysqldump drupal_ms_1 | mysql drupal_ms_2

Next, I modified the settings.php for my first site (sites/ms1.erl.dev/settings.php) to enable the shared database and single sign-on configuration. The $db_prefix variable is used to specify a different database using the [DATABASE].[TABLE] syntax. You'll also need to add the $cookie_domain variable to ensure the cookie set by Drupal will work across both domain names.

<?php
// Replace:
$db_prefix = '';
// With:
$db_prefix = array(
  'default' => '',
  'users' => 'drupal_ms_shared.',
  'sessions' => 'drupal_ms_shared.',
  'authmap' => 'drupal_ms_shared.'
);
// Add:
$cookie_domain = '.erl.dev';
?>

I then copied the ms1.erl.dev settings.php file into my second sites folder. Edit the new ms2.erl.dev/settings.php $db_url variable to point to the drupal_ms_2 database (and update the user and password as well).

$ cp ms1.erl.dev/settings.php ms2.erl.dev

After you delete your domain cookies everything should be working! To test this out, log into your first site and then open a new window/tab and go to your second site. You should be logged in as the same user. If you log out from one site, you should also be logged out from the other site.

7 comments

How to dump and compress all mysql databases on a server into separate files using PHP and PEAR

Submitted by Eric on Mon, 06/22/2009 - 21:20

Here's a quick code snippet I just wrote to dump and compress (gzip) all mysql databases on a server into separate files using PHP and PEAR.

<?php
// include PEAR DB library
require_once('DB.php');

// define the DSN in an array
// NOTE: user must have access to all databases
$dsn = array(
  'phptype' => 'mysql',
  'username' => 'YOURUSER',
  'password' => 'YOURPASSWORD',
  'hostspec' => 'HOSTNAME', // localhost?
);

// instantiate a PEAR DB object
$DB =& DB::connect($dsn);

// check for an error
if (DB::isError($DB)) die($DB->getMessage());

// set the DB fetch mode to associative
$DB->setFetchMode(DB_FETCHMODE_ASSOC);

// define sql statement
$sql = "show databases";

// fetch sql result
$databases = $DB->getAll($sql);

// loop through results
foreach ($databases as $index => $result) {

  // define the mysqldump command
  $command = "mysqldump -u " . $dsn['username'] . " "
    . "-p" . $dsn['password'] . " "
    . "-h " . $dsn['hostspec'] . " "
    . $result['Database'] . " | gzip > "
    . $result['Database'] . ".sql.gz";

  // execute command
  `$command`;
    
}
?>

Add new comment

Taking a SQL statement and generating a table from the results

Submitted by Eric on Tue, 06/02/2009 - 10:33

Functionality that I regularly create in applications is the ability to take a SQL statement and return a table of results for reports, debugging, etc. Here's a helper function I created to implement this functionality. I purposely left off pagination, sortable columns, and other features to focus on how the Drupal theme_table() function operates. This helper function will use the column names from the SQL as the table column headers, so make sure they are properly formatted.

<?php
// first, I defined this function in my module:
function _MYMODULE_sql_to_table($sql) {
    
  $html = "";
    
  // execute sql
  $resource = db_query($sql);
    
  // fetch database results in an array
  $results = array();
  while ($row = db_fetch_array($resource)) $results[] = $row;
    
  // ensure results exist
  if (!count($results)) {
    $html .= "Sorry, no results could be found.";
    return $html;    
  }

  // create an array to contain all table rows
  $rows = array();
    
  // get a list of column headers
  $columnNames = array_keys($results[0]);
    
  // loop through results and create table rows
  foreach ($results as $key => $data) {

    // create row data
    $row = array();
    
    // loop through column names
    foreach ($columnNames as $c) {
      $row[] = array(
        'data' => $data[$c],
        'class' => strtolower(str_replace(' ', '-', $c)),
      );
    }

    // add row to rows array
    $rows[] = $row;
        
  }
    
  // loop through column names and create headers
  $header = array();
  foreach ($columnNames as $c) {
    $header[] = array(
      'data' => $c,
      'class' => strtolower(str_replace(' ', '-', $c)),
    );
  }
    
  // generate table html
  $html .= theme('table', $header, $rows);
    
  return $html;
    
}

// then you can call it in your code...
function _MYMODULE_some_page_callback() {
  
  $html = "";

  $sql = "select * from {node}";

  $html .= _MYMODULE_sql_to_table($sql);

  return $html;
}
?>

Add new comment

Migrating a Drupal site from development to production

Submitted by Eric on Thu, 03/12/2009 - 10:31

It's important to treat content management systems like any other application and segment development, test, and production. Before adding content and attaching files to nodes, it's very important to set the file system path correctly and establish a deployment procedure.

One way to make the transition from development to production a smooth process is to use the same directory for your files. If you are using one Drupal installation for each project sites/default/files will work fine. If you set your file system path specific to your development hostname (for example: site/dev.HOSTNAME.com/files), you'll be in trouble when you transition to a new hostname.

Here's an example process to show how you could transition a site from development to test (or any other environment).

1. Migrate your MySQL database to the new server. Each environment should use its own database.

# connect to mysql as a privileged user to create a new database and user
$ mysql -u USER -pPASSWORD -h NEWSERVERHOST
> create database NEWDATABASE;
> GRANT ALL PRIVILEGES ON NEWDATABASE.* TO 'NEWUSER'@'localhost' IDENTIFIED BY 'NEWPASSWORD';
> exit;

# backup database
$ mysqldump -u USER -pPASSWORD DATABASE > DATABASE.sql

# import database
$ mysql -u NEWUSER -pNEWPASSWORD -h NEWSERVERHOST NEWDATABASE < DATABASE.sql

2. Copy your Drupal installation to the new server. Hopefully, you're using subversion (or some other version control software) to deploy these files.

3. Create a new folder in your sites directory for the new hostname. Copy your existing settings.php into the new folder. Edit this file and update the MySQL connection string to match your new database, user, and password.

4. Create a new Apache virtual host for your new hostname and direct DNS accordingly.

Now, if DNS has been setup for your new hostname, the site should be up and running on the new server with a new hostname.

Additional thoughts: It's very important to avoid hard coding hostnames and absolute paths in your links, code, and themes. I promise, it will make your deployment process a nightmare.

2 comments

Integrating a Drupal project and database with subversion

Submitted by Eric on Tue, 12/23/2008 - 10:58

It's important to realize that making changes to Drupal (configuration changes, uploading a file, etc) affect both the file system and the database. That's why I feel it's important to integrate your MySQL database with your subversion file system. For instance, if you upgrade a Drupal module, you should commit a backup of your database with the file system changes to ensure you have a definitive snap snot of your project, and can revert to a previous revision as necessary.

All of my Drupal projects are subversion integrated for proper version control. A typical subversion tree resembles:

client
    project
        trunk
            httpdocs
                [all drupal files go here]
            archive
        branches
        tags

When I checkout projects to my Linux file system, I use the following command to ensure I checkout everything parallel to the httpdocs folder. I use the "archive" folder for items (like database dumps) that I'd like to keep outside of the httpdocs, so they are not public.

$ cd /var/www/vhosts
$ mkdir PROJECTNAME.erl.dev
$ cd PROJECTNAME.erl.dev
$ svn checkout https://SVNPATH/client/project/trunk .

Now that my file system is integrated with subversion, I can backup my MySQL database and check it into subversion before I make a critical change to the Drupal configuration, such as upgrading a module:

$ cd /var/www/vhosts/PROJECTNAME.erl.dev/archive
$ mysqldump -u USER -pPASSWORD -h HOST DATABASENAME > DATABASENAME.sql
$ svn stat
?      DATABASENAME.sql
$ svn add DATABASENAME.sql
$ svn commit DATABASENAME.sql -m "backup'd database prior to database change"

NOTE: if you've already checked your database into subversion and you're creating another backup, your svn commands (and output from the svn stat command) will be slightly different. A question mark represents a new file, while a capital "M" represents a modified file:

$ cd /var/www/vhosts/PROJECTNAME.erl.dev/archive
$ mysqldump -u USER -pPASSWORD -h HOST DATABASENAME > DATABASENAME.sql
$ svn stat
M      DATABASENAME.sql
$ svn commit DATABASENAME.sql -m "backup'd database prior to database change"

Let's say you upgrade a module (or Drupal) and get an unexpected error, you can now revert your database:

$ cd /var/www/vhosts/PROJECTNAME.erl.dev/archive
$ mysql -u USER -pPASSWORD -h HOST
mysql> drop database DATABASENAME;
mysql> create database DATABASENAME;
mysql> exit
$ mysql -u USER -pPASSWORD -h HOST DATABASENAME < DATABASENAME.sql

Add new comment