mysql | Eric's Drupal Blog

Content tagged with: mysql

Eric's picture

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.

gradient spacer
Eric's picture

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`;
   
}
?>

gradient spacer
Eric's picture

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.

gradient spacer
Eric's picture

Creating and configuring a virtual machine for LAMP development

Submitted by Eric on Wed, 01/07/2009 - 10:45

After losing my development virtual machine yesterday, I thought I'd document my process for creating a new one. The first thing to do is download your favorite Linux distribution. I prefer Centos (RHEL without the support contract) but what's most important is creating one that's as close to your production environment as possible (to avoid package version differences, differences in documentation, and deployment issues). The second step is creating a new virtual machine and defining it's properties. For a basic development system, I give it 512MB of RAM and a large enough hard drive for your projects. After defining it's properties, point the CDrom drive at your downloaded Linux distribution ISO and install the operating system. Here's the fun part, getting everything to work...

Update your packages

$ yum update

Install your favorite text editor

$ yum install emacs

Configure sudo so you don't have to use root (in: /etc/sudoers)

%wheel ALL=(ALL) NOPASSWD: ALL

Create a new user for yourself, and set your new password

$ adduser Eric
$ passwd Eric
# NOTE: add your user to the wheel group in /etc/group so you can setup sudo
# NOTE: add your user to the apache group and vice versa
# NOTE: from now on, log in as your user and use sudo to execute commands that require root privileges

Install subversion

$ sudo yum install subversion

Install mysql

$ sudo yum install mysql-server

Install PHP

$ sudo yum install php php-cli php-common php-devel php-gd php-ldap php-mbstring php-mssql php-mysql php-odbc php-pear php-soap php-xml php-xmlrpc
# NOTE: The previous command will automatically install Apache (httpd) as a dependency

(OPTIONAL) upgrade PEAR packages

$ sudo pear upgrade-all

(OPTIONAL) install additional PEAR packages

$ sudo pear install DB HTML_QuickForm Mail Mail_Mime

(OPTIONAL) install openssl for HTTPS traffic

$ sudo yum install mod_ssl openssl

Set run levels for mysql and apache to ensure the services start automatically

$ sudo /sbin/chkconfig --level 2345 httpd on
$ sudo /sbin/chkconfig --level 2345 mysqld on

Set MySQL passwords

# root password:
$ /usr/bin/mysqladmin -u root password 'YOUR-NEW-PASSWORD'

# your user:
$ mysql -u root -p
mysql> grant all privileges on *.* to 'Eric'@'localhost' identified by 'YOUR-NEW-PASSWORD' with grant option;

# (OPTIONAL) you can add privileges for your user to connect from other computers:
mysql> grant all privileges on *.* to 'Eric'@'%' identified by 'YOUR-NEW-PASSWORD' with grant option;

Configure PHP (edit /etc/php.ini)

$ sudo emacs /etc/php.ini
display_errors = [Off per prod | On per dev/test]
error_reporting = E_ALL & ~E_NOTICE
memory_limit = 100M
upload_max_filesize = 100M
post_max_size = 100M

Configure Apache

# create a directory for all your vhosts, and set permissions
sudo mkdir /var/www/vhosts
sudo chown -R Eric.Eric /var/www/vhosts
sudo chmod -R 2770 /var/www/vhosts

# create a new configuration file to keep your changes separate from httpd.conf
$ sudo emacs /etc/httpd/conf.d/Eric.conf

# FILE CONTENTS - START

# set directory indexes to ensure php files are not read as text
DirectoryIndex index.php index.html index.html.var index.htm

# set a default character set
AddDefaultCharset ISO-8859-1

# enable name based virtual hosts, so you can host multiple hostnames on one server
NameVirtualHost *:80

# I create a separate directory for all my virtual hosts. This allows .htaccess files to work properly
<Directory /var/www/vhosts>
AllowOverride All
</Directory>

# add your first virtual host entry
<VirtualHost *:80>
ServerName SITEHOSTNAME
DocumentRoot /var/www/vhosts/SITEHOSTNAME/httpdocs
ErrorLog logs/SITEHOSTNAME-error_log
CustomLog logs/SITEHOSTNAME-access_log common
</VirtualHost>

# FILE CONTENTS - END

Configure MySQL (edit /etc/my.cnf)

[mysqld]
set-variable = max_allowed_packet=32M

Start Apache & MySQL

$ /etc/init.d/mysqld start
$ /etc/init.d/httpd start

(OPTIONAL) Configure Samba so you can edit your virtual machine filesystem from your host operating system
[documentation here]

Now, from your host operating system, edit your /etc/hosts file and point your development hostname to the new IP address of your virtual machine. If you go to that web address you should be able to reach your Apache virtual host on your new virtual machine!

gradient spacer
Eric's picture

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

gradient spacer
Syndicate content