Setup MSQL 5.1 Replication di Centos 6

• 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