Membuat MySQL Replication Lag Monitor dengan PHP dan AWS Lambda

5 min read

Disclaimer
Saya bekerja di AWS, semua opini adalah dari saya pribadi. (I work for AWS, my opinions are my own.)
MySQL Replication Lag Monitor dengan PHP dan AWS Lambda
MySQL Replication Lag Monitor dengan PHP dan AWS Lambda

TeknoCerdas.com – Salam cerdas untuk kita semua. Dalam sebuah instalasi MySQL replication ketertinggalan atau lag pada Slave sering terjadi. Pada tulisan ini TeknoCerdas akan membahas bagaimana membuat replication lag monitor untuk MySQL dengan PHP dan AWS Lambda.

Secara singkat replication lag adalah keterlambatan MySQL slave untuk mereplikasi data dari MySQL master.

Apa yang terjadi ketika terjadi lag pada MySQL replication? Kemungkinan terburuk adalah aplikasi tidak berfungsi sebagai mana mestinya. Untuk itu peran monitoring untuk MySQL replication penting untuk mengetahui akar masalah.

Replication lag monitoring yang akan dibuat dengan model Serverless dengan perhitungan bahwa aplikasi ini akan tidak dibuka setiap waktu. Monitoring terdiri dari sebuah PHP script dan berjalan diatas AWS Lambda dan menggunakan custom runtime Bref PHP.

Kriteria dari aplikasi monitoring ini adalah:

  • Serverless – Tidak perlu ada pengelolaan server
  • Sebuah aplikasi web normal bukan API
  • Tidak ada trigger alarm yang dikirim ketika replikasi lag terjadi
  • Aplikasi dapat melakukan skip error pada Slave agar proses replikasi dapat kembali berjalan.
  • Proses otentikasi diabaikan untuk simplifikasi

Daftar Isi

Persiapan Pembuatan MySQL Replication Lag Monitor

Sebelum mulai membuat PHP script untuk monitoring replication lag pada MySQL Slave beberapa syarat yang harus diperhatikan.

  • Memiliki akun AWS yang aktif
  • Memiliki pemahaman dasar tentang AWS Lambda dan Custom Runtime
  • Memiliki pemahaman dasar tentang Serverless Framework
  • Memiliki pemahaman dasar tentang PHP
  • Memiliki pemahaman dasar tentang MySQL server
  • Memiliki instalasi MySQL Master-Slave yang sudah berjalan

Jika anda tidak memiliki prasyarat diatas silahkan lanjutkan membaca. Karena mungkin banyak informasi baru yang diperoleh meskipun tanpa mencoba langsung tutorial ini.

Membuat MySQL Akun untuk Monitoring

Script perlu melakukan beberapa hal yang membutuhkan privilege tertentu. Untuk itu perlu dibuatkan akun baik disisi Master dan Slave. Berikut ini adalah akun yang dibutuhkan.

UserServerPrivilegeCatatan
master_monitorMasterREPLICATION CLIENTUntuk menjalankan SHOW MASTER STATUS
slave_monitorSlaveSUPERUntuk menjalankan manajemen Slave dan SET global variabel
Akun MySQL yang dibutuhkan

Pada server MySQL master jalankan query berikut.

mysql> CREATE USER 'master_monitor'@'%' IDENTIFIED BY 'teknocerdas123';
mysql> GRANT REPLICATION CLIENT ON *.* TO 'master_monitor'@'%';

Pada server MySQL slave jalankan query berikut.

mysql> CREATE USER 'slave_monitor'@'%' IDENTIFIED BY 'teknocerdas123';
mysql> GRANT SUPER ON *.* TO 'slave_monitor'@'%';

Membuat Script Replication Lag Monitoring

Script ini hanya terdiri dari sebuah file PHP. Karena aplikasi yang dibuat cukup sederhana maka view HTML akan digabungkan menjadi satu untuk simplifikasi.

Berikut adalah file index.php yang akan menampilkan monitoring replication lag pada Slave.

<?php

$requiredVars =  [
    'DSN_MASTER', 'USER_MASTER', 'PASSWD_MASTER',
    'DSN_SLAVE', 'USER_SLAVE', 'PASSWD_SLAVE'
];

foreach ($requiredVars as $_env) {
    if (! getenv($_env)) {
        printf('Please specify "%s" environment variable.', $_env);
        exit(1);
    }
}

try {
    $masterPdo = new PDO(getenv('DSN_MASTER'), getenv('USER_MASTER'), getenv('PASSWD_MASTER'));
} catch (PDOException $e) {
    printf('Failed to connect Master. Message: %s', $e->getMessage());
    exit(2);
}

try {
    $slavePdo = new PDO(getenv('DSN_SLAVE'), getenv('USER_SLAVE'), getenv('PASSWD_SLAVE'));
} catch (PDOException $e) {
    printf('Failed to connect Slave. Message: %s', $e->getMessage());
    exit(2);
}
?><!DOCTYPE html>
<html>
<head>
<style type="text/css">
  table {
    border-collapse: collapse;
  }
  tr td {
    border: 1px solid #ccc;
    padding: 4px 8px;
  }
  tr td.head {
    background-color: #f1f1f1;
  }
  input.btn-danger {
    border: none;
    padding: 12px 18px;
    font-size: 20px;
    color: white;
    background-color: #c60000;
  }
  p.green {
    background-color: ;
  }
</style>
</head>
<body>
<?php
try {
    $skipError = (isset($_GET['skip_error']) && $_GET['skip_error'] === 'yes') ? true : false;

    if ($skipError) :
        $slavePdo->exec('STOP SLAVE');
        $slavePdo->exec('SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1');
        $slavePdo->exec('START SLAVE'); ?>

        <p>Queries below has been run to skip the error.</p>
        <ul>
            <li>STOP SLAVE</li>
            <li>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1</li>
            <li>START SLAVE</li>
        </ul>
        <p><a href="<?= @explode('?', @$_SERVER['HTTP_REFERER'])[0]; ?>">Back to Master-Slave status</a></p>
        </body></html><?php
        exit;
    endif;

    $masterResult = $masterPdo->query("SHOW MASTER STATUS", PDO::FETCH_ASSOC)->fetchAll();
    $slaveResult = $slavePdo->query("SHOW SLAVE STATUS", PDO::FETCH_ASSOC)->fetchAll();

    ?><h2>Master vs Slave Summary</h2>
    <table>
        <tr>
            <td class="head">Status</td>
            <td class="head">Master</td>
            <td class="head">Slave</td>
        </tr>
        <tr>
            <td>Log File</td>
            <td><?= $masterResult[0]['File'] ?></td>
            <td><?= $slaveResult[0]['Master_Log_File'] ?></td>
        </tr>
        <tr>
            <td>Log Position</td>
            <td><?= $masterResult[0]['Position'] ?></td>
            <td><?= $slaveResult[0]['Exec_Master_Log_Pos'] ?></td>
        </tr>
    </table>

    <?php if ($slaveResult[0]['Slave_SQL_Running'] === 'No') : ?>
    <p>Slave SQL Thread is not running. Do you want to skip error?</p>
    <form method="GET" action="">
        <input type="hidden" name="skip_error" value="yes">
        <input type="submit" value="Yes, SKIP ERROR" class="btn-danger">
    </form>
    <?php endif ; ?>

    <h2>Master Status</h2>

    <table><?php
    foreach ($masterResult[0] as $key => $value) : ?>
        <tr>
            <td class="head"><?= $key ?></td>
            <td><?= $value ?></td>
    </tr><?php endforeach; ?>
    </table>

    <h2>Slave Status</h2>

    <table><?php
    foreach ($slaveResult[0] as $key => $value) : ?>
        <tr>
            <td class="head"><?= $key ?></td>
            <td><?= $value ?></td>
    </tr><?php endforeach; ?>
    </table><?php
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
}
?></body></html>

Script PHP diatas akan mencoba membaca data dari dua MySQL server yaitu Master dan Slave. Pada Master perintah yang digunakan adalah SHOW MASTER STATUS dan pada Slave perintah yang digunakan adalah SHOW SLAVE STATUS.

Jika ada terjadi kasus SQL Thread tidak jalan maka diasumsikan bahwa sedang terjadi masalah di Slave sehingga. Langkah yang dilakukan adalah menginstruksikan Slave untuk melakukan skip 1 perintah SQL yang ada pada relay log.

Untuk menjalankan script PHP diatas pada lokal komputer cukup gunakan PHP built-in web server. Pastikan semua environment variabel telah diset sebelum PHP dijalankan.

$ export DSN_MASTER="mysql:host=YOUR_MASTER_IP;dbname=" \
USER_MASTER=master_monitor PASSWD_MASTER=teknocerdas123 \
DSN_SLAVE="mysql:host=YOUR_SLAVE_IP;dbname=" \
USER_SLAVE=slave_monitor PASSWD_SLAVE=teknocerdas123

Setelah itu jalankan PHP web server pada direktori yang sama dengan script tersebut.

$ php -S 0.0.0.0:8080 -t ./

Deployment dengan Serverless Framework

Untuk melakukan deployment ke AWS Lambda akan digunakan Serverless Framework. Selain itu dibutuhkan juga Bref PHP sebagai tool pembantu untuk deployment.

$ composer require bref/bref -vvv

Setelah itu buat sebuah file YAML serverless.yml untuk menyimpan konfigurasi dari Serverless Framework. Gunakan konfigurasi berikut.

service: TeknoCerdas

provider:
    name: aws
    region: ap-southeast-1
    runtime: provided

plugins:
    - ./vendor/bref/bref

functions:
    lag_monitor:
        handler: index.php
        description: 'MySQL Replication Lag Monitor'
        timeout: 28 # in seconds (API Gateway has a timeout of 29 seconds)
        layers:
            - ${bref:layer.php-73-fpm}
        events:
            -   http: 'ANY /'
            -   http: 'ANY /{proxy+}'
        environment:
            DSN_MASTER: ${env:DSN_MASTER}
            USER_MASTER: ${env:USER_MASTER}
            PASSWD_MASTER: ${env:PASSWD_MASTER}
            DSN_SLAVE: ${env:DSN_SLAVE}
            USER_SLAVE: ${env:USER_SLAVE}
            PASSWD_SLAVE: ${env:PASSWD_MASTER}

# Exclude files from deployment
package:
    exclude:
        - 'node_modules/**'
        - 'tests/**'

Untuk melakukan deployment jangan lupa export dulu environment variabel yang dibutuhkan.

$ export DSN_MASTER="mysql:host=YOUR_MASTER_IP;dbname=" \
USER_MASTER=master_monitor PASSWD_MASTER=teknocerdas123 \
DSN_SLAVE="mysql:host=YOUR_SLAVE_IP;dbname=" \
USER_SLAVE=slave_monitor PASSWD_SLAVE=teknocerdas123

Baru kemudian lanjutkan dengan perintah deploy.

$ serverless deploy
Serverless: Packaging service...
Serverless: Excluding development dependencies...
Serverless: Creating Stack...
Serverless: Checking Stack create progress...
........
Serverless: Stack create finished...
Serverless: Uploading CloudFormation file to S3...
Serverless: Uploading artifacts...
Serverless: Uploading service TeknoCerdas.zip file to S3 (801.48 KB)...
Serverless: Validating template...
Serverless: Updating Stack...
Serverless: Checking Stack update progress...
.................................
Serverless: Stack update finished...
Service Information
service: TeknoCerdas
stage: dev
region: ap-southeast-1
stack: TeknoCerdas-dev
resources: 12
api keys:
  None
endpoints:
  ANY - https://wonfz77rp9.execute-api.ap-southeast-1.amazonaws.com/dev
  ANY - https://wonfz77rp9.execute-api.ap-southeast-1.amazonaws.com/dev/{proxy+}
functions:
  lag_monitor: TeknoCerdas-dev-lag_monitor
layers:
  None

Alamat URL dari aplikasi dapat dilihat pada atribut endpoints. Buka alamat tersebut pada web browser dan jika semua konfigurasi benar maka tampilannya akan seperti dibawah ini.

Tampilan MySQL Replication Lag Monitor
Tampilan MySQL Replication Lag Monitor

Melakukan Tes Replication Lag Pada Slave

Ketika terjadi query yang error karena object pada Slave tidak ditemukan atau karena satu dan lain hal maka SQL thread akan berhenti untuk mereplikasi. Sehingga yang terjadi adalah Slave mengalama lag atau tertinggal.

Hal ini ditunjukkan dengan status “Slave_SQL_Running” bernilai “No”. Aplikasi monitoring yang dibuat akan otomatis menampilkan pilihan untuk melakukan Skip Error dengan melakukan set GLOBAL SQL_SLAVE_SKIP_COUNTER = 1.

Untuk mensimulasikannya jalankan perintah berikut untuk mengentikan thread SQL Query pada Slave.

mysql> STOP SLAVE SQL_THREAD;

Setelah itu refresh aplikasi lag monitoring pada browser. Harusnya akan muncul sebuah tombol untuk melakukan Skip Error.

Simulasi Terjadinya Replication Lag pada Slave
Simulasi Terjadinya Replication Lag pada Slave

Jika tombol SKIP ERROR ditekan maka aplikasi akan hal berikut agar Slave kembali berjalan.

STOP SLAVE
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1
START SLAVE

Proses perbaikan hanya melakukan skip 1 perintah SQL. Sehingga jika terjadi banyak error maka cara terbaik adalah melakukan sinkronisasi ulang slave.