Creation of two database instances

As it is a best practice to install Drupal and CiviCRM into separate database instances, we shall do so.

We shall set two instances one for Drupal on port 3307 and the second for CiviCRM running on port 3308

First we create an user to manage database instances. In MySQL prompt we do

$ sudo mysql -u root -p
mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'127.0.0.1' IDENTIFIED BY 'multi_pass';

We shall setup our MySQL configuration file in /etc/my.cnf . We create the appropriate folders as put in the configuration file and set permissions to the user mysql on these folders

$ sudo mkdir /var/lib/mysql1 /var/lib/mysql2
$ sudo chown -R mysql:mysql /var/lib/mysql1 /var/lib/mysql2
$ sudo mkdir /var/run/mysqld
$ sudo chown -R mysql:mysql /var/run/mysqld
$ sudo chmod 755 /var/log/mysql/mysqld.log 
$ cp -Rp /var/lib/mysql/mysql /var/lib/mysql1/
$ cp -Rp /var/lib/mysql/mysql /var/lib/mysql2/

Drupal database folder will be /var/lib/mysql1

CiviCRM database folder will be /var/lib/mysql2

For permission purpose , we disable apparmor

sudo service apparmor stop
sudo service apparmor teardown
sudo /usr/sbin/update-rc.d -f apparmor remove

The /etc/my.cnf conf file will look like :

[mysqld_safe]
log-error=/var/log/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = multi_admin
password   = multi_pass
log = /var/log/mysqld_multi.log


# For Drupal
[mysqld1]
socket     = /var/lib/mysql1/mysql.sock1
port       = 3307
pid-file   = /var/run/mysqld/mysqld.pid1
datadir    = /var/lib/mysql1
user       = mysql
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin


# For CiviCRM
[mysqld2]
socket     = /var/lib/mysql2/mysql.sock2
port       = 3308
pid-file   = /var/run/mysqld/mysqld.pid2
datadir    = /var/lib/mysql2
user       = mysql
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin

To start the two instances, we run :

$ sudo mysqld_multi --defaults-file=/etc/my.cnf start

To check the status of the instances

$ sudo mysqld_multi --defaults-file=/etc/my.cnf report

mysqld_multi: How to run multiple instances of MySQLPercona Database Performance Blog

MySQL in a NutshellO’Reilly Online Learning