Zhun (Jack) Yang

Notes, Tips for Database Oracle Cloud, Exadata Cloud @ Customer, MySQL and more…

MySQL Innodb Cluster for Moodle Application

8 minute read

This guide provides a quick-start for Moodle Application of MySQL InnoDB Cluster with Read/Write Split PHP Plugin enabled.

We use MySQL 8 commercial server minimal version for container.

Regarding the performance of MySQL with Docker, check these blogs.

mysql with docker performance characteristics

mysql docker container understanding basics

Docker Compose Setup for InnoDB Cluster

GitHub Docker InnoDB Cluster

Prerequisites

  • Download mysql-commercial-server-minimal-8.0.12-1.1.el7.x86_64.rpm

  • Download mysql-shell-commercial-8.0.12-1.1.el7.x86_64.rpm

  • Download mysql-router-commercial-8.0.12-1.1.el7.x86_64.rpm

  • PHP extension mysqlnd_ms

    1. PHP 5.x - pecl install mysqlnd_ms

    2. PHP 7.x - run below command

      RUN git init && git clone https://github.com/sergiotabanelli/mysqlnd_ms.git && cd mysqlnd_ms && phpize && ./configure --enable-mysqlnd-ms --with-php-config=/usr/bin/php-config && make && make install

  • Download latest version Moodle

    wget https://download.moodle.org/stable35/moodle-latest-35.zip

moodle mysql innodb cluster

MySQL InnoDB Cluster Installation and Configuration

Let’s Start From the Dockerfile

FROM oraclelinux
add mysqlserver.rpm /root
add mysqlrouter.rpm /root
add mysqlshell.rpm /root
ARG MYSQLD_URL=/root/mysqlserver.rpm
ARG ROUTER_URL=/root/mysqlrouter.rpm
ARG SHELL_URL=/root/mysqlshell.rpm

# Install server
RUN rpmkeys --import http://repo.mysql.com/RPM-GPG-KEY-mysql \
  && yum install -y $MYSQLD_URL \
  && yum install -y $ROUTER_URL \
  && yum install -y $SHELL_URL \
  && yum install -y libpwquality \
  && yum install -y hostname \
  && yum install -y less vim-minimal net-tools \
  && rm -rf /var/cache/yum/* \
  && rm -rf /root/*.rpm
RUN mkdir /docker-entrypoint-initdb.d

ADD my.cnf /etc/my.cnf 

VOLUME /var/lib/mysql
VOLUME /var/lib/mysqlrouter

COPY innodb_cluster-entrypoint.sh /entrypoint.sh
ENTRYPOINT ["/entrypoint.sh"]

COPY healthcheck.sh /healthcheck.sh
HEALTHCHECK --start-period=60s --timeout=15s --interval=10s --retries=2 CMD /healthcheck.sh

EXPOSE 3306 6606 6446 6447 33060
CMD [""]

Build Docker Image

cd C:\docker\Docker-InnoDB-Cluster
#docker stop mysqlgr1 mysqlgr2 mysqlgr3 mysqlrouter1
#docker rm mysqlgr1 mysqlgr2 mysqlgr3 mysqlrouter1
#docker rmi yzjamm/innodb-cluster
docker network create --driver bridge grnet
docker build -t yzjamm/innodb-cluster .

Run Containers, Restrict Memory 1 GB to Each Container

docker run --memory="1024m" --name=mysqlgr1 -v C:\docker\Docker-InnoDB-Cluster\data\mysqlgr1data:/var/lib/mysql --hostname=mysqlgr1 --network=grnet -e MYSQL_ROOT_PASSWORD=root -e BOOTSTRAP=1 -e GROUP_NAME="664e2f34-3563-4a76-a515-2d945b01a6e1" -itd yzjamm/innodb-cluster
docker run --memory="1024m" --name=mysqlgr2 -v C:\docker\Docker-InnoDB-Cluster\data\mysqlgr2data:/var/lib/mysql --hostname=mysqlgr2 --network=grnet -e MYSQL_ROOT_PASSWORD=root -e GROUP_NAME="664e2f34-3563-4a76-a515-2d945b01a6e1" -e GROUP_SEEDS="mysqlgr1:6606" -itd yzjamm/innodb-cluster
docker run --memory="1024m" --name=mysqlgr3 -v C:\docker\Docker-InnoDB-Cluster\data\mysqlgr3data:/var/lib/mysql --hostname=mysqlgr3 --network=grnet -e MYSQL_ROOT_PASSWORD=root -e GROUP_NAME="664e2f34-3563-4a76-a515-2d945b01a6e1" -e GROUP_SEEDS="mysqlgr1:6606" -itd yzjamm/innodb-cluster
docker run --memory="1024m" --name=mysqlrouter1 --hostname=mysqlrouter1 --network=grnet -e NODE_TYPE=router -e MYSQL_HOST=mysqlgr1 -e MYSQL_ROOT_PASSWORD=root -p 6446:6446 -p 6447:6447 -itd yzjamm/innodb-cluster

Check Docker Status

C:\docker\yzjamm.github.io (master -> origin)
λ docker ps
CONTAINER ID        IMAGE                   COMMAND             CREATED             STATUS                        PORTS                                                             NAMES
bc0669088f64        yzjamm/innodb-cluster   "/entrypoint.sh "   13 seconds ago      Up 11 seconds (healthy)       3306/tcp, 6606/tcp, 33060/tcp, 0.0.0.0:6446-6447->6446-6447/tcp   mysqlrouter1
7a2573db6816        yzjamm/innodb-cluster   "/entrypoint.sh "   6 days ago          Up About a minute (healthy)   3306/tcp, 6446-6447/tcp, 6606/tcp, 33060/tcp                      mysqlgr3
250266f5fe3b        yzjamm/innodb-cluster   "/entrypoint.sh "   6 days ago          Up 2 minutes (healthy)        3306/tcp, 6446-6447/tcp, 6606/tcp, 33060/tcp                      mysqlgr2
6df5d24800a0        yzjamm/innodb-cluster   "/entrypoint.sh "   6 days ago          Up About an hour (healthy)    3306/tcp, 6446-6447/tcp, 6606/tcp, 33060/tcp                      mysqlgr1

Check InnoDB Cluster Status

C:\docker\yzjamm.github.io (master -> origin)
λ docker exec -it mysqlgr1 mysql -hmysqlgr1 -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 658
Server version: 8.0.12-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

innodb-cluster> SELECT * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | f03ad742-ea21-11e8-8881-0242ac120002 | mysqlgr1    |        3306 | ONLINE       | PRIMARY     | 8.0.12         |
| group_replication_applier | f6c9721a-ea21-11e8-ad03-0242ac120003 | mysqlgr2    |        3306 | ONLINE       | SECONDARY   | 8.0.12         |
| group_replication_applier | fbebaf65-ea21-11e8-b834-0242ac120004 | mysqlgr3    |        3306 | ONLINE       | SECONDARY   | 8.0.12         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

C:\docker\yzjamm.github.io (master -> origin)
λ docker exec -it mysqlgr1 mysqlsh --uri=root:root@mysqlgr1:3306
mysqlsh: [Warning] Using a password on the command line interface can be insecure.
Creating a session to 'root@mysqlgr1:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 787
Server version: 8.0.12-commercial MySQL Enterprise Server - Commercial
No default schema selected; type \use <schema> to set one.
MySQL Shell 8.0.12-commercial

Copyright (c) 2016, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help' or '\?' for help; '\quit' to exit.


 MySQL  mysqlgr1:3306  JS > dba.getCluster().status()
{
    "clusterName": "testcluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "mysqlgr1:3306",
        "ssl": "DISABLED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "mysqlgr1:3306": {
                "address": "mysqlgr1:3306",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "mysqlgr2:3306": {
                "address": "mysqlgr2:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "mysqlgr3:3306": {
                "address": "mysqlgr3:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            }
        }
    },
    "groupInformationSourceMember": "mysql://root@mysqlgr1:3306"
}

 MySQL  mysqlgr1:3306  JS >

Add One MySQL Instance into Cluster

docker run --memory="1024m" --name=mysqlgr4 --hostname=mysqlgr4 --network=grnet -e MYSQL_ROOT_PASSWORD=root -e GROUP_NAME="664e2f34-3563-4a76-a515-2d945b01a6e1" -e GROUP_SEEDS="mysqlgr1:6606" -itd yzjamm/innodb-cluster

G:\docker\Docker-InnoDB-Cluster
λ docker exec -it mysqlgr1 mysqlsh --uri=root:root@mysqlgr1:3306
mysqlsh: [Warning] Using a password on the command line interface can be insecure.
Creating a session to 'root@mysqlgr1:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 427
Server version: 8.0.12-commercial MySQL Enterprise Server - Commercial
No default schema selected; type \use <schema> to set one.
MySQL Shell 8.0.12-commercial

Copyright (c) 2016, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help' or '\?' for help; '\quit' to exit.


 MySQL  mysqlgr1:3306  JS > dba.getCluster().status()
{
    "clusterName": "testcluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "mysqlgr1:3306",
        "ssl": "DISABLED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "mysqlgr1:3306": {
                "address": "mysqlgr1:3306",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "mysqlgr2:3306": {
                "address": "mysqlgr2:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "mysqlgr3:3306": {
                "address": "mysqlgr3:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            }
        }
    },
    "groupInformationSourceMember": "mysql://root@mysqlgr1:3306"
}

 MySQL  mysqlgr1:3306  JS > dba.getCluster().rescan();
Rescanning the cluster...

Result of the rescanning operation:
{
    "defaultReplicaSet": {
        "name": "default",
        "newlyDiscoveredInstances": [
            {
                "host": "mysqlgr4:3306",
                "member_id": "6f691e37-f070-11e8-a8cb-0242ac130006",
                "name": null
            }
        ],
        "unavailableInstances": []
    }
}

A new instance 'mysqlgr4:3306' was discovered in the HA setup.
Would you like to add it to the cluster metadata? [Y/n]: Y
Adding instance to the cluster metadata...

Please provide the password for 'root@mysqlgr4:3306': ****
The instance 'root@mysqlgr4:3306' was successfully added to the cluster metadata.


 MySQL  mysqlgr1:3306  JS >

Remove One MySQL Instance from Cluster

G:\docker\Docker-InnoDB-Cluster
λ docker stop mysqlgr3
mysqlgr3

G:\docker\Docker-InnoDB-Cluster
λ docker ps
CONTAINER ID        IMAGE                   COMMAND             CREATED             STATUS                   PORTS                                                             NAMES
cd3b788276db        yzjamm/innodb-cluster   "/entrypoint.sh "   5 minutes ago       Up 5 minutes (healthy)   3306/tcp, 6446-6447/tcp, 6606/tcp, 33060/tcp                      mysqlgr4
f8d6154b8183        yzjamm/innodb-cluster   "/entrypoint.sh "   8 minutes ago       Up 8 minutes (healthy)   3306/tcp, 6606/tcp, 33060/tcp, 0.0.0.0:6446-6447->6446-6447/tcp   mysqlrouter1
3092e8a7e08c        yzjamm/innodb-cluster   "/entrypoint.sh "   9 minutes ago       Up 9 minutes (healthy)   3306/tcp, 6446-6447/tcp, 6606/tcp, 33060/tcp                      mysqlgr2
e2ecb5234259        yzjamm/innodb-cluster   "/entrypoint.sh "   9 minutes ago       Up 9 minutes (healthy)   3306/tcp, 6446-6447/tcp, 6606/tcp, 33060/tcp                      mysqlgr1

G:\docker\Docker-InnoDB-Cluster
λ docker exec -it mysqlgr1 mysqlsh --uri=root:root@mysqlgr1:3306
mysqlsh: [Warning] Using a password on the command line interface can be insecure.
Creating a session to 'root@mysqlgr1:3306'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 1149
Server version: 8.0.12-commercial MySQL Enterprise Server - Commercial
No default schema selected; type \use <schema> to set one.
MySQL Shell 8.0.12-commercial

Copyright (c) 2016, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help' or '\?' for help; '\quit' to exit.


 MySQL  mysqlgr1:3306  JS > dba.getCluster().status()
{
    "clusterName": "testcluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "mysqlgr1:3306",
        "ssl": "DISABLED",
        "status": "OK_PARTIAL",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure. 1 member is not active",
        "topology": {
            "mysqlgr1:3306": {
                "address": "mysqlgr1:3306",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "mysqlgr2:3306": {
                "address": "mysqlgr2:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "mysqlgr3:3306": {
                "address": "mysqlgr3:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "(MISSING)"
            },
            "mysqlgr4:3306": {
                "address": "mysqlgr4:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            }
        }
    },
    "groupInformationSourceMember": "mysql://root@mysqlgr1:3306"
}

 MySQL  mysqlgr1:3306  JS > dba.getCluster().rescan();
Rescanning the cluster...

Result of the rescanning operation:
{
    "defaultReplicaSet": {
        "name": "default",
        "newlyDiscoveredInstances": [],
        "unavailableInstances": [
            {
                "host": "mysqlgr3:3306",
                "label": "mysqlgr3:3306",
                "member_id": "fcc02b3c-f06f-11e8-8c38-0242ac130004"
            }
        ]
    }
}

The instance 'mysqlgr3:3306' is no longer part of the HA setup. It is either offline or left the HA group.
You can try to add it to the cluster again with the cluster.rejoinInstance('mysqlgr3:3306') command or you can remove it from the cluster configuration.
Would you like to remove it from the cluster metadata? [Y/n]: Y
Removing instance from the cluster metadata...

The instance 'mysqlgr3:3306' was successfully removed from the cluster metadata.


 MySQL  mysqlgr1:3306  JS >

**So far MySQL InnoDB Cluster installation and configuration is done. **

Moodle PHP Application and Extension Mysqlnd_ms Configuration

Dockerfile

FROM ubuntu:18.04
MAINTAINER yzjamm@hotmail.com
RUN apt-get update
RUN DEBIAN_FRONTEND=noninteractive apt-get install -y mysql-client build-essential libgmp3-dev libxml2-dev bison flex zip apache2 libapache2-mod-php php-common php-gd php-curl php-mail php-mail-mime php-mysql php-pear php-db php-mbstring php-xml php-xmlrpc php-zip php-intl php-soap php-dev php-memcached zip git vim wget iputils-ping libmemcached-dev 
#RUN pecl channel-update pecl.php.net && pecl install mysqlnd_ms
RUN git init && git clone https://github.com/sergiotabanelli/mysqlnd_ms.git && cd mysqlnd_ms && phpize && ./configure --enable-mysqlnd-ms --with-php-config=/usr/bin/php-config && make && make install
WORKDIR /var/www/html
RUN wget https://download.moodle.org/stable35/moodle-latest-35.zip && unzip /var/www/html/moodle-latest-35.zip
CMD mkdir /var/www/moodledata
CMD chown -R www-data /var/www/moodledata
CMD chmod -R 0770 /var/www/moodledata

CMD tail -f /dev/null
EXPOSE 80 3306

Start Moodle Container and Configure PHP Mysqlnd_ms Extension

docker run --name=jack_moodle --network=grnet -p 8090:80 -itd yzjamm/moodle
docker exec -it jack_moodle bash

vi /etc/php/7.2/mods-available/mysqlnd_ms.ini
mysqlnd_ms.enable=1
mysqlnd_ms.force_config_usage=0
mysqlnd_ms.config_file=/etc/php/7.2/mods-available/mysqlnd_ms_plugin.ini
extension=mysqlnd_ms.so

vi /etc/php/7.2/mods-available/mysqlnd_ms_plugin.ini
{
    "yzjamm_innodb_cluster": {
        "master": {
            "master_0": {
                "host": "xxx.xxx.xxx.xxx", -- MySQL Router ip address
                "port": "6446", -- MySQL Rourter R/W port
                "user": "root",
                "password" : "root",
                "connect_flags": 1
            }
        },
        "slave": {
            "slave_0": {
                "host": "xxx.xxx.xxx.xxx", -- MySQL Router ip address
                "port": "6447", -- MySQL Router R/O port 
                "user": "root",
                "password" : "root",
                "connect_flags": 1
            }
        }
    }
}

cd /etc/php/7.2/apache2/conf.d
ln -s /etc/php/7.2/mods-available/mysqlnd_ms.ini 20-mysqlnd_ms.ini
cd /etc/php/7.2/cli/conf.d
ln -s /etc/php/7.2/mods-available/mysqlnd_ms.ini 20-mysqlnd_ms.ini

service apache2 restart

#check module loaded into PHP
php -m|grep mysqlnd
php -i|grep mysqlnd_ms

Run Install PHP Script in Moodle Application

php /var/www/html/moodle/admin/cli/install.php

Copy below config.php into /var/www/html/moodle

<?php  // Moodle configuration file

unset($CFG);
global $CFG;
$CFG = new stdClass();

$CFG->dbtype    = 'mysqli';
$CFG->dblibrary = 'native';
$CFG->dbhost    = 'yzjamm_innodb_cluster';
$CFG->dbname    = 'moodle';
$CFG->dbuser    = 'root';
$CFG->dbpass    = 'root';
$CFG->prefix    = 'mdl_';
$CFG->dboptions = array (
          'dbpersist' => 0,
            'dbport' => 3306,
              'dbsocket' => '',
                'dbcollation' => 'utf8mb4_unicode_ci',
        );

$CFG->wwwroot   = 'http://127.0.0.1:8090/moodle';
$CFG->dataroot  = '/var/www/moodledata';
$CFG->admin     = 'admin';

$CFG->directorypermissions = 02777;

require_once(__DIR__ . '/lib/setup.php');

// There is no php closing tag in this file,
// // it is intentional because it prevents trailing whitespace problems!

Change moodle ownership to www-data

docker exec -it jack_moodle bash
root@c946beb8db77:/var/www/html#chown -R www-data:www-data moodle

So you all set. Have fun.

1543466166158

Comments

You May Also Enjoy

ExaC@C Migration 2 - Transportable Datapump

3 minute read

Transporting data is much faster than performing either an export/import or unload/load of the same data. It is faster because, for user-defined tablespaces,...

Rising Star ExaC@C

less than 1 minute read

Oracle Exadata Cloud @ Customer @ York University