Setup MSQL 5.1 Replication di Centos 6

KETERANGAN
Ini kalau pakai SSL. Jika ingin tidak pakai SSL, tinggal skip bagian SSL.
PERSIAPAN
  • Cek dulu apakah ssl sudah diaktifkan di mysql server master
    root@server1:~# mysql -u root -p
    mysql> show variables like ‘%ssl%’;
  • Jika belum, maka aktifkan dulu dengan menambahkan string “ssl” (tanpa quote) pada /etc/my.cnf dan restart mysql

KONFIGURASI di MASTER

  • MySQL harus bisa listen all interface. Caranya comment pada bind-address = 127.0.0.1 di file /etc/my.cnf. Untuk test, engan perintah berikut:
    root@server1:~# netstat -tap | grep mysql
    tcp 0 0 *:mysql *:* LISTEN 2166/mysqld
  • Membuat folder untuk log mysql jika belum ada
    root@server1:~# mkdir /var/log/mysql
    root@server1:~#  chown mysql:mysql /var/log/mysql
  • Membuat CA certificate
    root@server1:~# (mkdir /etc/mysql/newcerts) cd /etc/mysql/newcerts
    root@server1:~# openssl genrsa 2048 > ca-key.pem
    root@server1:~# openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem
  • Membuat server certificate
    root@server1:~# openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem
    root@server1:~#  openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
  • Membuat client certificate
    root@server1:~# openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem
    root@server1:~#  openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
  • Transfer file ca-cert.pem, client-cert.pem, and client-key.pem ke slave di direktori /etc/mysql/newcerts juga
  • Buka /etc/my.cnf lalu masukkan berikut:
    ssl
    ssl-ca=/etc/mysql/newcerts/ca-cert.pem
    ssl-cert=/etc/mysql/newcerts/server-cert.pem
    ssl-key=/etc/mysql/newcerts/server-key.pem
  • Restart mysql
  • Membuat user slave d master
    root@server1:~# mysql -u root -p
    mysql> GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘slave_password’ REQUIRE SSL;
    mysql> GRANT USAGE ON *.* TO ‘slave_user’@’%’ REQUIRE SSL;
    mysql> FLUSH PRIVILEGES;
  • Penomoran server dan penentuan database di /etc/my.cnf
    server-id = 1
    log_bin = /var/log/mysql/mysql-bin.log
    expire_logs_days = 10
    max_binlog_size = 100M
    binlog_do_db = exampledb
  • Restart mysql
  • Flush & Lock mysql database dulu dan lihat posisi di master status
    root@server1:~# mysql -u root -p
    mysql> USE exampledb;
    mysql> FLUSH TABLES WITH READ LOCK;
    mysql> SHOW MASTER STATUS;
    +——————+———-+————–+——————+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +——————+———-+————–+——————+
    | mysql-bin.000001 |  3096416 | exampledb    |                  |
    +——————+———-+————–+——————+
    1 row in set (0.00 sec)
  •  INGAT, jangan tutup atau quit dari mysql dulu karena pada pisisi ini harus ngedump database. Buka terminal baru:
    root@server1:~# mysqldump -u root -pyourrootsqlpassword –opt exampledb > snapshot.sql
  • Kirim snapshot.sql ke server slave
  • Dari terminal yang sebelumnya:
    mysql> UNLOCK TABLES;
    mysql> quit;

KONFIGURASI di SLAVE

  • Buka  /etc/my.cnf buat setting server-id, master-connect-retry, replicate-do-db di bagian  [mysqld]
    ssl
    server-id=2
    master-connect-retry=60
    replicate-do-db=exampledb
  • restart mysql
  • membuat database
    root@server2:~# mysql -u root -p
    mysql> CREATE DATABASE exampledb;
    mysql> STOP SLAVE;
    mysql> quit;
  •  memasukkan data awal:
    root#server2:~# mysql -u root -pyourrootsqlpassword exampledb < snapshot.sql
  • set slave:
    root@server2:~# mysql -u root -p
    mysql> CHANGE MASTER TO MASTER_HOST=’192.168.0.100′, MASTER_USER=’slave_user’, MASTER_PASSWORD=’slave_password’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=3096416, MASTER_SSL=1, MASTER_SSL_CA = ‘/etc/mysql/newcerts/ca-cert.pem’, MASTER_SSL_CERT = ‘/etc/mysql/newcerts/client-cert.pem’, MASTER_SSL_KEY = ‘/etc/mysql/newcerts/client-key.pem’;
    mysql> START SLAVE;
    mysql> SHOW SLAVE STATUS \G

Leave a Reply

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