Configuring MySQL

In the previous section we configured Apache. Now we will set up MySQL with three databases—one for each of our sites. Again, if you are using the Vagrant-based virtual host, you will not need to do this part. It has been done already.

Again, we will assume that MySQL is already set up and running, and we will also assume that you can access the MySQL database as a privileged user and create user accounts and databases.

The first thing to do is create three new databases, one for each site. We will call these databases books_local, cooks_local, and looks_local to correspond to our three hostnames.

From a MySQL monitor (such as mysql on the command line), issue the following command:

CREATE DATABASE books_local;
CREATE DATABASE cooks_local;
CREATE DATABASE looks_local;

That is all that is required to create our databases. But we need to explicitly grant permissions for another MySQL user to access these databases. We will configure our Drupal sites to use this account to authenticate to the database. You can create a separate account for each site if you'd like, but we will just create one.

GRANT ALL
ON books_local.*
TO 'drupal'@'localhost'
IDENTIFIED BY 'secret';
GRANT ALL
ON looks_local.*
TO 'drupal'@'localhost'
IDENTIFIED BY 'secret';
GRANT ALL
ON cooks_local.*
TO 'drupal'@'localhost'
IDENTIFIED BY 'secret';

This will give the user drupal, whose password is secret, sufficient privileges to access all three of our databases. Notice that the Drupal account has been restricted to connections from the localhost. This is designed to prevent a remote user from connecting as our Drupal user.

It is a good idea to test these credentials using another MySQL client. This will help us preemptively troubleshoot. Here is an example from a UNIX shell using the mysql client.

$ mysql -u drupal -p secret books_local
mysql> SHOW TABLES;
Empty set (0.00 sec)

The snippet above shows that we connected to the database with our credentials, then successfully executed the SHOW TABLES SQL statement.

At this point, MySQL should be ready for Drupal, and we are done preparing the environment.