Replika Master-Slave MySQL

Latar Belakang

Pokoke ada database penting yang perlu dibackup di secondary server. Period.

Asumsi

  • Server MySQL master dan slave menggunakan OS Ubuntu (walaupun sebenarnya tidak harus Ubuntu, tetapi semua contoh setup di sini berjalan di atas OS tersebut).
  • Di bakal server slave sudah terinstall MySQL-server.
  • Sebagai perumpamaan, ip address MySQL master (untuk selanjutnya akan disingkat master saja) adalah : 1.2.3.4.
  • Sebagai perumpamaan, ip address MySQL slave (untuk selanjutnya akan disingkat slave saja) adalah: 4.3.2.1.
  • Masing-masing server tersambung dan dapat melakukan ping satu sama lain melalui LAN/WAN/VPN. Pastikan firewall melewatkan paket di antara ke-2 server tersebut (jika ada firewall).
  • Semua perintah shell dijalankan dengan privilege root. Sebenarnya yang paling aman adalah dengan menggunakan sudo. Tapi kali ini biar saya jadi ignorant sebentar.
  • Database yang akan direplicate bernama : TheDataBase.

Setup Server Master

  • Edit file /etc/mysql/my.cnf dengan parameter sebagai berikut:
    bind-address   	    = 1.2.3.4
    server-id           = 1 
    log_bin             = /var/log/mysql/mysql-bin.log
    binlog_do_db  	    = TheDataBase

    note: server-id sebenarnya bisa diisi bebas, yang penting berbeda dengan server-id slave nantinya.

  • Restart MySQL dengan perintah: service mysql restart .
  • Masuk ke MySQL shell dengan perintah : mysql -u root -pR4h4514  (ganti dengan password root MySQL server yang sedang disetup). Lalu jalankan perintah:
    mysql> GRANT REPLICATION SLAVE ON *.* TO 'backup'@'%' IDENTIFIED BY 'P455w0rd';
    mysql> FLUSH PRIVILEGES;

    ganti password P455w0rd dengan yang lebih aman.

  • masih di dalam shell MySQL, jalankan langkah berikut:
    mysql> USE TheDataBase;
    mysql> FLUSH TABLES WITH READ LOCK;
    mysql> SHOW MASTER STATUS;

    perintah MySQL terakhir akan memunculkan table seperti berikut:

    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000235 |  7991253 | TheDatabase  |                  |
    +------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)

    simpan output tersebut untuk keperluan nanti pada saat setup slave server.

  • Dengan kondisi terakhir database masih dilock, buka window/console shell linux baru untuk menjalankan langkah selanjutnya. Hal ini dilakukan agar database tetap dalam kondisi locked. Jika kita menjalankan perintah di windows/console MySQL yang sama, maka database akan otomatis unlocked. Setelah membuka windows/shell baru, jalankan langkah berikut:
    root@master:~# mysqldump -u root -p --opt TheDataBase > /usr/local/src/TheDataBase.sql

    Lokasi penyimpanan file hasil dump bebas bisa di mana saja. Ini hanya kebiasaan saya dalam menyimpan file dump di /usr/local/src.

  • Kembali ke window/shell MySQL sebelumnya lalu unlock database dengan cara:
    mysql> UNLOCK TABLES;
    mysql> QUIT;
  • Salin file hasil dump tersebut ke bakal server slave:
    root@master:~# scp /usr/local/src/TheDataBase.sql root@4.3.2.1:/usr/local/src/

Setup Server Slave

  • Login ke server slave dan masuk ke shell MySQL sebagai root. Jalankan langkah berikut ini:
    mysql> CREATE DATABASE TheDataBase;
    mysql> EXIT;
  • Import database yang sudah disalin sebelumnya:
    root@slave:~# mysql -u root -p TheDataBase < /usr/ocal/src/TheDataBase.sql
  • Edit file /etc/mysql/my.cnf seperti berikut:
    bind-address 	 = 4.3.2.1
    server-id	 = 2 
    relay-log 	 = /var/log/mysql/mysql-relay-bin.log
    log_bin          = /var/log/mysql/mysql-bin.log
    binlog_do_db 	 = TheDataBase
  • Restart MySQL dengan perintah: service mysql restart
  • Masuk ke shell MySQl sebagai root lalu jalankan perintah:
    mysql> CHANGE MASTER TO MASTER_HOST='1.2.3.4',MASTER_USER='backup', MASTER_PASSWORD='P455w0rd', MASTER_LOG_FILE='mysql-bin.000235', MASTER_LOG_POS=  7991253;
    mysql> START SLAVE;

    catatan:

    MASTER_HOST diisi dengan ip address MySQL master yaitu 1.2.3.4.

    MASTER_LOG_FILE dan MASTER_LOG_POS diisi dengan nama file log dan position sesuai di table output perintah SHOW MASTER STATUS pada saat setup master di atas.

    MASTER_USER dan MASTER_PASSWORD diisi dengan user dan password yang sudah ditambahkan pada saat setup master sebelumnya.

  • Jika kita menjalankan perintah shell MySQL di slave server dengan perintah:
    mysql> SHOW SLAVE STATUS\G

    maka akan muncul output:

    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 202.52.146.51
                      Master_User: backup
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000235
              Read_Master_Log_Pos: 7991253
                   Relay_Log_File: mysqld-relay-bin.000002
                    Relay_Log_Pos: 3282325
            Relay_Master_Log_File: mysql-bin.000235
                 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: 7991253
                  Relay_Log_Space: 3282482
                  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
    1 row in set (0.00 sec)

    Perhatikan Read_Master_Log_Pos dan Exec_Master_Log_Pos memiliki nilai yang sama dengan output table SHOW MASTER STATUS sebelumnya. Begitu juga dengan Master_Log_File dan Relay_Master_Log_File.

Setting IPTABLES

Untuk membatasi akses MySQL maka dibuat aturan sebagai berikut:

  • Akses MySQL di master server hanya boleh dari ip address slave server, dan localhost.
  • Akses selain dari source di atas, akan diblok.

Rule dasar iptables-nya adalah sebagai berikut:

iptables -A INPUT -p tcp -s 4.3.2.1 --sport 1024:65535 -d 1.2.3.4 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s 1.2.3.4 --sport 3306 -d 4.3.2.1 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT
iptables -A INPUT -i lo -p tcp --dport mysql -j ACCEPT
iptables -A INPUT -i eth0 -p tcp --dport mysql -s 4.3.2.1 -j ACCEPT
iptables -A INPUT -p tcp --dport mysql -j DROP

Supaya rule iptables tersebut persistent walauapun server direstart, install package iptables-persistent:

root@master:~# apt-get install -y iptables-persistent
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
libgsasl7 libgnutls-openssl27 libntlm0
Use 'apt-get autoremove' to remove them.
The following NEW packages will be installed:
iptables-persistent
0 upgraded, 1 newly installed, 0 to remove and 153 not upgraded.
Need to get 8,960 B of archives.
After this operation, 58.4 kB of additional disk space will be used.
Get:1 http://kambing.ui.ac.id/ubuntu/ precise/universe iptables-persistent all 0.5.3ubuntu2 [8,960 B]
Fetched 8,960 B in 0s (63.1 kB/s)
debconf: unable to initialize frontend: Dialog
debconf: (Dialog frontend requires a screen at least 13 lines tall and 31 columns wide.)
debconf: falling back to frontend: Readline
Preconfiguring packages ...
Configuring iptables-persistent
-------------------------------
Current iptables rules can be saved to the configuration file /etc/iptables/rules.v4. These rules will then be loaded automatically
during system startup.
Rules are only saved automatically during package installation. See the manual page of iptables-save(8) for instructions on keeping
the rules file up-to-date.
Save current IPv4 rules? y <---- jawab Y

Current iptables rules can be saved to the configuration file /etc/iptables/rules.v6. These rules will then be loaded automatically
during system startup.
Rules are only saved automatically during package installation. See the manual page of ip6tables-save(8) for instructions on
keeping the rules file up-to-date.
Save current IPv6 rules? n <---- jawab N

Selecting previously unselected package iptables-persistent.
(Reading database ... 42575 files and directories currently installed.)
Unpacking iptables-persistent (from .../iptables-persistent_0.5.3ubuntu2_all.deb) ...
Processing triggers for ureadahead ...
ureadahead will be reprofiled on next reboot
Setting up iptables-persistent (0.5.3ubuntu2) ...
debconf: unable to initialize frontend: Dialog
debconf: (Dialog frontend requires a screen at least 13 lines tall and 31 columns wide.)
debconf: falling back to frontend: Readline
* Loading iptables rules... * IPv4... * skipping IPv6 (no rules to load)...

Secara otomatis, rule iptables akan di save di lokasi /etc/iptables/rules.v4.

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>