Cara menggunakan mysql skip replication error

Question

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.

Table of Contents Show

Persiapan Pembuatan MySQL Replication Lag Monitor
Membuat MySQL Akun untuk Monitoring
Membuat Script Replication Lag Monitoring
Deployment dengan Serverless Framework
Melakukan Tes Replication Lag Pada Slave

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.

UserServerPrivilegeCatatanmaster_monitorMasterREPLICATION CLIENTUntuk menjalankan

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

2slave_monitorSlaveSUPERUntuk menjalankan manajemen Slave dan SET global variabelAkun 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

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

3 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

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

2 dan pada Slave perintah yang digunakan adalah

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

5.

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

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

6 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

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

7. Buka alamat tersebut pada web browser dan jika semua konfigurasi benar maka tampilannya akan seperti dibawah ini.

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

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

8.

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

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

0

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

Simulasi Terjadinya Replication Lag pada Slave

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

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

1

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

Share

Programming

#aws
#Cloud Computing
#lambda
#master-slave
#mysql
#php
#replikasi
#serverless

Rio Astamal Follow Adalah penulis utama di TeknoCerdas.com. Rio Astamal seorang yang sangat antusias dengan web development sejak 2003. Sejak November 2021 Rio Astamal bekerja di Amazon Web Services (AWS) sebagai Developer Advocate untuk Indonesia. Dia mengelola TeknoCerdas.com di waktu senggangnya sebagai salah satu sarana untuk ikut mencerdaskan pembaca dalam dunia IT.

kode mysql