> For the complete documentation index, see [llms.txt](https://task-devops.gitbook.io/documentation/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://task-devops.gitbook.io/documentation/creation-of-two-database-instances.md).

# 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

```sql
$ 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

```bash
$ 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/
```

{% hint style="info" %}
Drupal database folder will be /var/lib/mysql1

CiviCRM database folder will be /var/lib/mysql2
{% endhint %}

For permission purpose , we disable **apparmor**

```bash
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 : 

```bash
[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 :

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

To check the status of the instances 

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

{% embed url="<https://www.percona.com/blog/2014/08/26/mysqld_multi-how-to-run-multiple-instances-of-mysql/>" %}

{% embed url="<https://www.oreilly.com/library/view/mysql-in-a/0596007892/re288.html>" %}