DEV Community: Muhammad Adib Ahmad Roslan

Multiple MySQL Router Client in Single Server/Nodes

Muhammad Adib Ahmad Roslan — Fri, 15 Apr 2022 16:38:22 +0000

Introductions

As shown as example of above architecture diagram, your application servers require to connect to 3 different InnoDB MySQL Cluster.

Are you Planning to connect to multiple Cluster at once from a single server? heres the plan...

MySQL InnoDB Cluster 1
- c1-node-1
- c1-node-2
- c1-node-3
MySQL InnoDB Cluster 2
- c2-node-1
- c2-node-2
- c2-node-3
MySQL InnoDB Cluster 3
- c3-node-1
- c3-node-2
- c3-node-3

Prerequisite

install the mysqlrouter as usual
do not bootstrap anything yet
set your /etc/hosts files containing all the relevant servers
always set different base port so it doesnt clash to each other
always set different https port so it doesnt clash to each other

Configuration

run the command for bootstrapping the servers

  ##boostrap the Cluster 1

  $> mysqlrouter --boostrap <boostrap-user>@<c1-node-1> -d <c1-conf-directory> --user=mysqlrouter --conf-base-port=6446 --https-port=18443


  ##boostrap the Cluster 2

  $> mysqlrouter --boostrap <boostrap-user>@<c1-node-2> -d <c2-conf-directory> --user=mysqlrouter --conf-base-port=7446 --https-port=28443


  ##boostrap the Cluster 3

  $> mysqlrouter --boostrap <boostrap-user>@<c1-node-3> -d <c3-conf-directory> --user=mysqlrouter --conf-base-port=8446 --https-port=38443

once the boostrap is done, you can start using the nohup command to make it run on the background

  $> nohup mysqlrouter -c <c1-config-directory> &
  $> nohup mysqlrouter -c <c2-config-directory> &
  $> nohup mysqlrouter -c <c3-config-directory> &

to check your setup is working just check the ps command

  ps -ef | grep mysqlrouter

the command above should show 3 of the instances of mysqlrouter processes has been successfully started.

Thats All, Thanks for reading!

Laravel Dusk with Github Actions

Muhammad Adib Ahmad Roslan — Sat, 28 Nov 2020 03:37:01 +0000

Hello,

i was tasked to do Unit Testing for one of my projects, the test case that i was tasked with is :-

User can login and see Dashboard
User can login and see Developer Portal
User can login and see Administration Panel

basically the test is quite easy and straight-forward right?

so im off with installing Laravel Dusk since this kind of test involves with the actions of seeing the UI itself.

if you're not familiar with Laravel Dusk, Laravel Dusk is a official library that are supported by the Laravel itself.

Laravel Dusk provides an expressive, easy-to-use browser automation and testing API. By default, Dusk does not require you to install JDK or Selenium on your machine. Instead, Dusk uses a standalone ChromeDriver installation. However, you are free to utilize any other Selenium compatible driver you wish. - Laravel Dusk

So Without further ado,

The Preparation

Make sure that you have created the user factory, user seeder as well as enable user factory inside DatabaseSeeder.php file before starting this unit test. since its requires dummy user to test the test case.

Laravel Dusk Installation

installing Laravel Dusk is quite the breeze, just run this into the CLI of your project composer require --dev laravel/dusk after installing the packages you can then run php artisan dusk:install to install your laravel dusk.

Laravel Dusk Environment Configuration

By Default, when the laravel dusk environment configurations file .env.dusk is not exist, it will automatically use the .env as the environment configuration temporarily. in our case, we need to use a separate database which is called dusktest database and create the .env.dusk file onto your root project folder. Here's the sample of my .env.dusk

Important Make sure to generate the APP_KEY as well. run php artisan key:generate then copy the key from .env to .env.dusk

APP_NAME=dusk
APP_ENV=local
APP_KEY={your-key-here}
APP_DEBUG=true
APP_URL=http://127.0.0.1:8000

LOG_CHANNEL=stack
FLARE_KEY=

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=dusktest
DB_USERNAME=root
DB_PASSWORD=

BROADCAST_DRIVER=log
CACHE_DRIVER=array
QUEUE_CONNECTION=sync
SESSION_DRIVER=file
SESSION_LIFETIME=120

REDIS_HOST=127.0.0.1
REDIS_PASSWORD=null
REDIS_PORT=6379

MAIL_MAILER=array
MAIL_HOST=smtp.mailtrap.io
MAIL_PORT=2525
MAIL_USERNAME=null
MAIL_PASSWORD=null
MAIL_ENCRYPTION=null
MAIL_FROM_NAME="${APP_NAME}"
MAIL_FROM_ADDRESS="no-reply@ksud.com"

AWS_ACCESS_KEY_ID=
AWS_SECRET_ACCESS_KEY=
AWS_DEFAULT_REGION=us-east-1
AWS_BUCKET=

PUSHER_APP_ID=
PUSHER_APP_KEY=
PUSHER_APP_SECRET=
PUSHER_APP_CLUSTER=mt1

MIX_PUSHER_APP_KEY="${PUSHER_APP_KEY}"
MIX_PUSHER_APP_CLUSTER="${PUSHER_APP_CLUSTER}"

The Test Scripts

Run php artisan dusk:make CommonTest onto your CLI , it will created the Test File inside my-project/tests/Browser/CommonTest.php . After that we need to create the test case inside the test scripts . Here's the test scripts that i've created .

<?php

namespace Tests\Browser;

use App\Models\User;
use Illuminate\Foundation\Testing\DatabaseMigrations;
use Illuminate\Support\Facades\Artisan;
use Laravel\Dusk\Browser;
use Tests\DuskTestCase;

class CommonTest extends DuskTestCase
{
    use DatabaseMigrations;

    /**
     * Can Login and see Dashboard
     *
     * @return void
     */
    public function testCanLoginAndSeeDashboard()
    {
        //Seed dummy user data
        Artisan::call('db:seed');
        $user = User::first();
        $this->browse(function ($first) use ($user){
            $first->loginAs($user)
                  ->visit('/dashboard')
                  ->assertSee('Dashboard');
        });
    }

    /**
     * Can Login and see Developer Portal
     *
     * @return void
     */
    public function testCanLoginAndSeeDeveloperPortal()
    {
        //Seed dummy user data
        Artisan::call('db:seed');
        $user = User::first();
        $this->browse(function ($first) use ($user){
            $first->loginAs($user)
                  ->visit('/developer')
                  ->assertSee('Connective Exchange');
        });
    }

    /**
     * Can Login and see Administration Panel
     *
     * @return void
     */
    public function testCanLoginAndSeeAdministrationPanel()
    {
        //Seed dummy user data
        Artisan::call('db:seed');
        $user = User::first();
        $this->browse(function ($first) use ($user){
            $first->loginAs($user)
                  ->visit('/admin')
                  ->assertSee('Resources');
        });
    }
}

Github Action Workflow

So basically if you're familiar with github actions , the github action workflow scripts should be inside my-project/.github/workflows/actions.yml you can rename the file as you desired.

# my-project/.github/workflows/actions.yml
name: Unit Test

on:
  push:
    branches: [develop]
  pull_request:
    branches: [master, develop]

jobs:
  PHPUnit:
    runs-on: ubuntu-latest
    services:
      mysql:
        image: mysql:8.0.21
        env:
          MYSQL_ALLOW_EMPTY_PASSWORD: yes
          MYSQL_DATABASE: dusktest
        ports:
          - 3306:3306
        options: --health-cmd="mysqladmin ping" --health-interval=10s --health-timeout=5s --health-retries=3

    steps:
      - uses: actions/checkout@v2
      - name: Configure Application
        run: bin/ci
      - name: Configure Laravel Dusk
        run: bin/ci-dusk
      - name: Execute Dusk tests via PHPUnit
        run: php artisan dusk

you have to create some of this bash files to ensure the scripts working.

# my-project/bin/ci

#!/usr/bin/env bash

cp .env.example .env
composer install -q --no-ansi --no-interaction --no-scripts --no-progress --prefer-dist
chmod -R 777 storage bootstrap/cache
php artisan key:generate
php artisan config:clear

# my-project/bin/ci-dusk

#!/usr/bin/env bash

php artisan dusk:chrome-driver `/opt/google/chrome/chrome --version | cut -d " " -f3 | cut -d "." -f1`
chmod -R 0755 vendor/laravel/dusk/bin/
php artisan serve  > /dev/null 2>&1 &
curl localhost:8000 &
./vendor/laravel/dusk/bin/chromedriver-linux > /dev/null 2>&1 &

Simpy push your codes to github, and it should be good to go! Thank you for reading.

Honorable Mentions

Nasrul Hazim (Helping out with the workflow configurations)

Working the project with 'Team'

Muhammad Adib Ahmad Roslan — Wed, 26 Feb 2020 02:37:45 +0000

introductions

This is maybe not gonna work for some of you . but so far it has been helping me throughout my software development career path.

Tips & Best Practices

Naming Conventions

it would be nice if the variable naming conventions follows Laravel Best Practice it would be like 20 minutes reading.

Pushing and Pulling Codes from Repositories

refer this docs git forks and upstreams
always pull from Master (upstream/master) upstream branch every single time u want to commit or start coding or before pushing codes.
always check for conflict issue and fix it before pushing .
always follow commit message formats Commit Message Standards . most of the time i'll be using feat and fix. below are the sample of my commit message
- fixes - fix : update logic codes of this feature
- feature - feat : create new endpoints for feature
if youre fixing bug or adding feature or enhancement, put the github issue number as follows fix : issue #385 or feat : issue #385
always run bin/csfix.sh before pushing. this is to ensures that code style across application would be the same and formatted.

Code Branching

always practice creating a new branch for new features or fix.
everytime you want to do sprint endpoints always create new branch for your sprint eg: sprint-12 do not use the Master branch for every commit you want to make.
once the commit and push the codes into the branch , make a pull request to upstream Master (upstream/master) branch .
it'll be easier if u create new branch from upstream Master (upstream/master) and code whatever u need inside that separate branch.
after pull request has been merge, please delete the branch. and start over again if you want to run a new sprint.
always pull codes from upstream Master (upstream/master) by running git pull upstream master

i would be grateful if you can add more.

Setting up MySQL InnoDB cluster on Private Network

Muhammad Adib Ahmad Roslan — Wed, 26 Feb 2020 02:27:22 +0000

Introduction

Out off all the tutorial out there, all the cluster setup are shown for non private networks. since the setup is for private network, u need to skip certain stuff or might as well add few stuffs.

First thing first, the checklist:

All the server nodes in the same Private Network.
Minimum of 3 Servers nodes.
MySQL 8.0 community version installed on all those servers over here MySQL Installation.
MySQL Shell installed on one those servers MySQL Shell Installation.

Server Setup Details

App Server : apt 127.0.0.4
DB Server node 1 : dbt1 127.0.0.1
DB Server node 2 : dbt2 127.0.0.2
DB Server node 3 : dbt3 127.0.0.3

After checked all the checklist, start off with the hosts config inoder to allow the private network can talk to each other.

run sudo nano /etc/hosts
set the nodes internal ip into the host .

  127.0.0.1 db1
  127.0.0.2 db2
  127.0.0.3 db3

remove all iptables config by running sudo iptables -F or set the iptables properly
set the firewalld permanent to allow 3306 33060 33061 33062
configure the my.cnf settings on /etc/my.cnf
backup the mysql database (incase of emergency)
create a user specifically for clusteradmin access to all the server nodes

Setting up MySQL Innodb Cluster

start setting up the cluster by running the $ mysqlshell
run this on the cli $ mysqlshell
run this on the mysqlshell
- dba.configureInstance('clusteradmin@127.0.0.1') to configure dbt1 for mysql clustering
- dba.configureInstance('clusteradmin@127.0.0.2') to configure dbt2 for mysql clustering
- dba.configureInstance('clusteradmin@127.0.0.3') to configure dbt3 for mysql clustering.
- on the same mysqlshell run this command \c clusteradmin@127.0.0.1 . to connect to the first master nodes which is the dbt1.
- run and type this in the mysqlshell cluster=dba.createCluster('clusterName') to instantiate and create the cluster group.
- after that we need to add the instance to the group by running this commands
- cluster.addInstance('clusteradmin@127.0.0.2') this commands is for adding the second db nodes to the cluster group of clusterName.
  - just key in the password of clusteradmin user and choose clone to clone from the master nodes.
- cluster.addInstance('clusteradmin@127.0.0.3') this commands is for adding the third db nodes to the cluster group of clusterName.
  - just key in the password of clusteradmin user and choose Incremental Recovery to get the metadata from the master nodes.
- after all the instance successfully added, you can run cluster.status() in the connected mysqlshell to check if the clustering working. if the output as shown 3 nodes are available, then the clustering is working .

Setting up the MySQL Router

Always make sure that you have installed the mysql-router into your application server. following this tutorial MySQL Router Installation.

Steps to Configure:

if you have mysqld installed and running make sure that mysqld on your app server is not running by run the command $ sudo systemctl stop mysqld
just run this command to bootsrapping the router to the cluster server $ mysqlrouter --bootstrap mysqlrouter@127.0.0.1:3306 --conf-use-sockets --directory=/tmp/mysqlrouter
change directory to /tmp/mysqlrouter
run bash start.sh
Press Enter on your keyboard
it will create the router sockets automatically for you.
configure your MySQL client at /etc/my.cnf with :

   [client]
   socket=/tmp/mysqlrouter/mysql.sock

Verifiying the MySQL Router Works

configure your application by using the new port given .
try to logged into the cluster through router config by running mysql -uroot -p -P 6446
see if the database shown on the output is has the same amount of tables in the cluster