Instalasi dan penggunaan mydumper pada Linux¶

Tujuan awal adalah melakukan migrasi basis data dari server lama ke server baru. Awalnya yang terpikir adalah menggunakan Desktop Database Client seperti DBeaver, atau juga mysqldump/mariadb-dump, dan variasi pendekatan lainnya dari kedua pilihan tersebut.

Namun dalam perjalanan pembelajaran menggunakan metode konvensional, dan tulisan sederhana dalam penggunaan mydumper untuk melakukan pencadangan basis data peladen MariaDB, akhirnya repositori mydumper mencuat kepermukaan memohon untuk dikenali lebih dalam

What is MyDumper?

MyDumper is a MySQL Logical Backup Tool. It has 2 tools:

mydumper which is responsible to export a consistent backup of MySQL databases
myloader reads the backup from mydumper, connects the to destination database and imports the backup.

Both tools use multithreading capabilities.
MyDumper is Open Source and maintained by the community, it is not a Percona, MariaDB or MySQL product.

Why do we need MyDumper?

Parallelism (hence, speed) and performance (avoids expensive character set conversion routines, efficient code overall)
Easier to manage output (separate files for tables, dump metadata, etc, easy to view/parse data)
Consistency - maintains snapshot across all threads, provides accurate master and slave log positions, etc
Manageability - supports PCRE for specifying database and tables inclusions and exclusions

Instalasi¶

Instalasi pada sistem operasi yang mendukung

RedHat/CentosUbuntu/DebianFreeBSDmacOS

release=$(curl -Ls -o /dev/null -w %{url_effective} https://github.com/mydumper/mydumper/releases/latest | cut -d'/' -f8)
# RHEL 7
yum install https://github.com/mydumper/mydumper/releases/download/${release}/mydumper-${release:1}.el7.x86_64.rpm
# RHEL 8
yum install https://github.com/mydumper/mydumper/releases/download/${release}/mydumper-${release:1}.el8.x86_64.rpm

# Instalasi pustaka yang diperlukan
apt-get install libatomic1
# Inslasi mydumper
release=$(curl -Ls -o /dev/null -w %{url_effective} https://github.com/mydumper/mydumper/releases/latest \
    | cut -d'/' -f8) wget https://github.com/mydumper/mydumper/releases/download/${release}/mydumper_${release:1}.$(lsb_release -cs)_amd64.deb \
    && dpkg -i mydumper_${release:1}.$(lsb_release -cs)_amd64.deb

pkg install mydumper

# Menggunakan Homebrew
brew install mydumper

Penggunaan & Konfigurasi¶

Dalam penggunaan mydumper dapat mengkombinasikan perintah yang dieksekusi langsung melalui CLI dengan parameter-parameternya, atau disiapkan berkas konfigurasi mydumper.cnf untuk meringkas panjang perintah yang akan dieksekusi

Eksekusi perintah CLI¶

Perintah CLI

mydumper --defaults-file /home/user/mydumper.cnf --set-names=utf8mb4 --compress -v 3 --use-savepoints -B 'nama-db-sumber' --stream
myloader --defaults-file /home/user/mydumper.cnf -v 3 -o -B 'nama-db-tujuan' --stream

Berkas konfigurasi¶

mydumper.cnf

[mydumper]
host = sumber.localhost
user = db-pengguna-sumber
password = sandi-pengguna-sumber
outputdir = /tmp/backup
#rows = 10000

[myloader]
host = tujuan.localhost
user = db-pengguna-tujuan
password = sandi-pengguna-tujuan
innodb-optimize-keys = AFTER_IMPORT_PER_TABLE
directory = /tmp/restore

[mydumper_global_variables]
sync_binlog = 0
slow_query_log = OFF

[mydumper_session_variables]
wait_timeout = 300

[myloader_global_variables]
sync_binlog = 0
innodb_flush_log_at_trx_commit = 0

[myloader_session_variables]
long_query_time = 300

[myloader_session_variables_mariadb]
# This setting replaces the default in the section [myloader_session_variables]. More details in #987
SQL_MODE='NO_AUTO_VALUE_ON_ZERO' /*!40101
UNIQUE_CHECKS=1 /*!40114
FOREIGN_KEY_CHECKS=0 /*!40114