kode mysql

Cara menggunakan mysql reset master

19 Agustus 2020 5 min read

Disclaimer
Saya bekerja di AWS, semua opini adalah dari saya pribadi. (I work for AWS, my opinions are my own.)

Ilustrasi MySQL Master-Slave replication. (Sumber: Medium)

TeknoCerdas.com – Salam cerdas untuk kita semua. Pada tulisan ini TeknoCerdas akan membahas bagaimana sinkronisasi ulang data replikasi pada MySQL Master-Slave. Dimana hal ini akan terjadi jika slave mengalami korup atau terjadi lag yang terlalu jauh dari MySQL master karena error yang tidak bisa di-skip.

Sinkronisasi ulang data replikasi akan memastikan bahwa data yang ada pada slave memiliki integrasi yang sama dengan master. Sehingga aplikasi yang melakukan query ke Slave server mendapatkan data yang sesuai.

Otomasi yang akan dibuat adalah cukup sederhana dengan menggunakan Bash script. Dan diasumsikan arsitektur terdari dari 1 Master dan 1 Slave.

Daftar Isi

  • Persiapan Pembuatan Sinkronisasi Ulang Data Replikasi
  • Membuat SSH Key untuk Master
  • Menyalin SSH Publik Key Master ke Slave
  • Membuat Script Sinkronisasi Ulang Data Replikasi
  • Menjalankan Script Sinkronisasi Ulang Data Replikasi
  • Melakukan Test Replikasi Master-Slave

Persiapan Pembuatan Sinkronisasi Ulang Data Replikasi

Sebelum mulai membuat script untuk sinkronisasi ulang data replikasi pada MySQL Master-Slave terdapat beberapa syarat yang harus diperhatikan.

  • Memiliki pemahaman dasar tentang MySQL server
  • Memiliki instalasi MySQL Master-Slave yang sudah berjalan
  • Memiliki pemahaman dasar tentang Shell Script
  • Memiliki pemahaman dasar tentang SSH
  • Memiliki pemahaman dasar tentang Ubuntu atau Linux pada umumnya

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

Membuat SSH Key untuk Master

SSH Key ini digunakan untuk melakukan login ke MySQL Slave server sebagai bagian dari otomasi yang akan dilakukan.

Generate SSH key baru dikomputer lokal untuk dikirimkan ke server MySQL Master.

$ mkdir keys $ ssh-keygen -f ./keys/master.key

Setelah itu private key perlu disalin ke server Master tepatnya dibawah user ubuntu.

$ cat ./keys/master.key | ssh ubuntu@master-server tee /home/ubuntu/.ssh/master.key

Menyalin SSH Publik Key Master ke Slave

Berikutnya adalah menyalin SSH Publik key dari Master ke server Slave agar user ubuntu dari Master dapat login via SSH.

$ cat ./keys/master.key | ssh ububtu@slave-server tee -a /home/ubuntu/.ssh/authorized_keys

Membuat Script Sinkronisasi Ulang Data Replikasi

Script yang akan dibuat hanya terdiri dari satu file bernama functions.sh yang akan di-include pada shell terminal untuk menjalankan.

$ mkdir scripts/ $ touch scripts/functions.sh

Buka scripts/functions.sh dengan sebuah teks editor kemudian salin kode berikut.

#!/bin/bash # # Helper functions to re-sync MySQL replication data. # # @author Rio Astamal <> # Default variable values. Changes according to your needs. [[ -z "$MASTER_IP" ]] && MASTER_IP=54.169.169.81 [[ -z "$MASTER_PRIVATE_IP" ]] && MASTER_PRIVATE_IP=172.31.23.24 [[ -z "$SLAVE_IP" ]] && SLAVE_IP=18.136.124.167 [[ -z "$SSH_KEY" ]] && SSH_KEY="~/.ssh/teknocerdas.key" [[ -z "$MASTER_USER" ]] && MASTER_USER=ubuntu [[ -z "$SLAVE_USER" ]] && SLAVE_USER=ubuntu [[ -z "$BACKUP_FILE" ]] && BACKUP_FILE=/tmp/backup.sql.gz # Group the related functions activate_mysql_replication() { echo "> Creating replication user on Master..." create_replication_user || echo ">> User probably already exists. No need to worry." echo "> Creating replication backup on Master..." create_replication_backup echo "> Sending backup file from Master to Slave..." send_backup_to_slave echo "> Importing backup file on Slave..." import_backup_from_master echo "> Activating Slave..." activate_slave_db } # Begin sync Master to Slave activate_slave_db() { local data_master=$( echo "SHOW MASTER STATUS" | query_on_master -N ) local master_file=$( echo "$data_master" | awk '{print $1}' ) local master_pos=$( echo "$data_master" | awk '{print $2}' ) cat <<EOF | query_on_slave || return 1 STOP SLAVE; RESET SLAVE; CHANGE MASTER TO MASTER_HOST='$MASTER_PRIVATE_IP', MASTER_USER='replicator', MASTER_PASSWORD='penyalin', MASTER_PORT=3306, MASTER_LOG_FILE='$master_file', MASTER_LOG_POS=$master_pos, MASTER_CONNECT_RETRY=10; START SLAVE EOF return 0 } # Import the backup from the compressed file import_backup_from_master() { echo "STOP SLAVE; RESET SLAVE; " | query_on_slave cat <<EOF | exec_on_slave bash gunzip -c $BACKUP_FILE | sudo mysql EOF } # Send the backup file to Slave via Rsync send_backup_to_slave() { cat <<EOF | exec_on_master bash || return 1 sudo chown ubuntu $BACKUP_FILE rsync -e 'ssh -i ~/.ssh/master.key -o LogLevel=quiet -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null' \ $BACKUP_FILE $SLAVE_USER@$SLAVE_IP:$BACKUP_FILE rm -f $BACKUP_FILE EOF return 0 } # Create all databases backup and compress it create_replication_backup() { echo "FLUSH TABLES WITH READ LOCK" | query_on_master cat <<EOF | exec_on_master sudo bash mysqldump --all-databases | gzip -c > $BACKUP_FILE EOF echo "UNLOCK TABLES" | query_on_master } # Create replication user on Master that is used by the Slave server create_replication_user() { cat <<EOF | query_on_master || return 1 CREATE USER 'replicator'@'%' IDENTIFIED BY 'penyalin'; GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%'; EOF return 0 } # Execute MySQL command on slave query_on_slave() { exec_on_slave sudo mysql $@ } # Execute MySQL command on master query_on_master() { exec_on_master sudo mysql $@ } # Execute shell command on MySQL Slave node exec_on_slave() { ssh -i $SSH_KEY -o LogLevel=quiet -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null $SLAVE_USER@$SLAVE_IP $@ } # Execute shell command on MySQL Master node exec_on_master() { ssh -i $SSH_KEY -o LogLevel=quiet -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null $MASTER_USER@$MASTER_IP $@ }

Script diatas memiliki sejumlah helpers yang memudahkan eksekusi perintah shell OS atau pada shell MySQL.

  • exec_on_master Fungsi untuk melakukan eksekusi perintah pada server Master.
  • exec_on_slave Fungsi untuk melakukan eksekusi perintah pada server Master.
  • query_on_master Fungsi untuk melakukan eksekusi perintah pada MySQL Master.
  • query_on_slave Fungsi untuk melakukan eksekusi perintah pada MySQL Slave.

Sebagai contoh untuk menjalankan perintah pada server Master cukup gunakan fungsi berikut.

$ echo "ls -lh /" | exec_on_master bash

Untuk menjalankan SQL query pada MyQL master gunakan fungsi berikut.

$ echo "SHOW DATABASES" | query_on_master

Menjalankan Script Sinkronisasi Ulang Data Replikasi

Pastikan variabel pada file telah diset atau gunakan environment variabel untuk menset nilai dari variabel yang diperlukan.

$ export MASTER_IP = YOUR_VALUE \ MASTER_PRIVATE_IP = YOUR_VALUE \ SLAVE_IP = YOUR_VALUE \ SSH_KEY = YOUR_VALUE \ MASTER_USER = YOUR_VALUE \ SLAVE_USER = YOUR_VALUE \ BACKUP_FILE = YOUR_VALUE$ source scripts/functions.sh

Kemudian panggil fungsi activate_mysql_replication untuk memulai sinkronisasi ulang data dari Master.

$ activate_mysql_replication > Creating replication user on Master... > Creating replication backup on Master... > Sending backup file from Master to Slave... > Importing backup file on Slave... > Activating Slave...

Cek status dari Master dan Slave apakah status replikasi sudah berjalan.

$ echo "SHOW MASTER STATUS\G" | query_on_master *************************** 1. row *************************** File: ip-172-31-23-24-bin.000004 Position: 758 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set:$ echo "SHOW SLAVE STATUS\G" | query_on_slave *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.31.23.24 Master_User: replicator Master_Port: 3306 Connect_Retry: 10 Master_Log_File: ip-172-31-23-24-bin.000004 Read_Master_Log_Pos: 758 Relay_Log_File: ip-172-31-11-59-relay-bin.000002 Relay_Log_Pos: 540 Relay_Master_Log_File: ip-172-31-23-24-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 758 Relay_Log_Space: 759 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 34df5eba-e135-11ea-8ac6-06f865510276 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace:

Dapat terlihat bahwa Slave sudah berjalan dan menunggu data dari Master untuk direplikasi.

Melakukan Test Replikasi Master-Slave

Buat sebuah database baru pada Master.

$ echo "CREATE DATABASE dari_master" | query_on_master$ echo "SHOW DATABASES" | query_on_master Database dari_master information_schema mysql performance_schema sys

Dapat terlihat sebuah database baru bernama “dari_master”. Pada sisi Slave harusnya database tersebut juga otomatis dibuat.

$ echo "SHOW DATABASES" | query_on_slave Database dari_master information_schema mysql performance_schema sys

Dapat terlihat bahwa database “dari_master” sukses direplikasi ke MyQL Slave server.

Jika ingin mengulang proses replikasi dari awal cukup jalankan fungsi activate_mysql_replication maka secara otomatis replikasi akan dijalankan ulang dari awal.

Kelemahan proses ini adalah saat pembuatan file backup menggunakan mysqldump mengunci seluruh database di Master. Sehingga akan menyebabkan proses “write” dihentikan di Master. Ini tidak masalah jika ukuran dari database hanya beberapa Gigabytes. Namun jika ukurannya sudah puluhan bahkan ratusan GB proses ini akan menjadi bottleneck.

Solusi dari masalah diatas bisa menggunakan tools buatan Percona yaitu XtraBackup. Kelebihan dari Xtrabackup daripada mysqldump adalah tidak perlu ada database locking ketika backup dilakukan. Dengan demikian maka aplikasi dapat terus berjalan – zero downtime. TeknoCerdas akan mengulas penggunaan XtraBackup di waktu yang akan datang.

Pos Terkait

Cara menggunakan the ioncube php loader
Penggunaan fungsi SEMAFORE pada PHP
Cara menggunakan dropdown bertingkat php
Cara menggunakan C数组 pada JavaScript
Cara membuat password phpmyadmin xampp linux
Cara menggunakan running text bootstrap
Cara menggunakan crud php-native github
Cara menggunakan regexextract examples google sheets
Cara menggunakan php code smells
Data structures using python lab manual

Toplist

#1
Top 10 berikut ini yang bukan merupakan penetapan tujuan visi perusahaan adalah 2022
#2
Top 10 sebuah bak air berbentuk balok dengan ukuran panjang 40 cm, lebar 30 cm, dan tinggi 35 cm 2022
#3
Top 9 banjir bukan sekadar bencana alam paragraf 3 gagasan utama 2022
#4
Top 10 diketahui himpunan himpunan sebagai berikut p bilangan ganjil yang kurang dari 15 2022
#5
Top 10 penyelesaian dari persamaan 3x 2 y 9 dan 2x y 3 adalah 2022
#6
Top 10 bagaimana cara memberi salam kepada orang tua dan guru yang benar brainly 2022
#7
Top 10 semakin tinggi nilai toleransi semakin tinggi nilai integrasi nasional 2022
#8
Top 10 benda kerajinan tidak boleh membahayakan pemakainya maka benda kerajinan memiliki prinsip 2022
#9
Top 9 bagian tutup sumbat termos biasanya terbuat dari benda berjenis brainly 2022

Postingan terbaru

LIHAT SEMUA

Kiat Bagus Cara Belajar Apa Yang kode Teknologi Bagaimana Kesehatan dan kecantikan Location Jelaskan Contoh Sebutkan Apa arti Arti kata Top List Berapa List Mengapa Harus Membandingkan Toplist Permainan Belajar dengan baik Anak Apa yang Review php Adalah disebut Perbedaan Fungsi Beda Google Makanan lezat Berikut ini Dimaksud Cryto Q&a Tentang Q&A Top Seks Konstruksi python Daftar Teratas Rumah Sekolah How Teknik Facebook