Replikasi Mysql 5.6 di Centos 6.4

Sebelumnya kita sudah mencoba untuk mengupgrade Mysql 5.5 ke Mysql 5.6 di Centos 6.4. Dilanjutkan untuk membuat replikasi Mysql.

Replikasi ini bertujuan untuk membuat suatu backup online, dimana goal utamanya adalah bagian DRC perusahaan.

Sharing testing ini kita menggunakan VM Guest di Virtualbox.

VM Preparing
1. Buat VM Guest Centos 6.4 (masterdb)

2. Cloning VM Guest Centos 6.4 (slavedb)

3. IP VM menggunakan Host only adapter vboxnet0 pada kedua VM Guest

4. Buat hosts untuk penamaan seperti dibawah
 masterdb = 192.168.56.2

   slavedb = 192.168.56.5

5. Edit /etc/hosts di kedua server vm
   masterdb 192.168.56.2
   slavedb 192.168.56.5


 
Replikasi

A. Konfigurasi Seting Master Server

1.) Edit file /etc/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

innodb_flush_log_at_trx_commit=1

log_bin=/var/lib/mysql/mysql-bin-log/mysql_bin
sync_binlog=1

port = 3306
bind-address = 0.0.0.0
server_id = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Buat direktory log

# mkdir /var/lib/mysql/mysql-bin-log
# chown -R mysql.mysql /var/lib/mysql/mysql-bin-log/



# service mysql restart



2.) Buat user replikasi di server master

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'your_password';

Testing saya :

slave_user = slave01

password = password



3. Lock Master dan Dapatkan Binary Log 
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000004 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
Ingat di isian mysql_bin.000004 dan posisi 120

4. Backup Mysql
#mysqldump --all-databases --master-data > data_dump.sql -p
#scp data_dump.sql root@slavedb:/root

mysql>UNLOCK TABLES;





B. Konfigurasi Seting Slave Server

1.) Edit file /etc/my.cnf
[mysqld]
server_id = 2

log_bin=/var/lib/mysql/mysql-bin-log/mysql_bin

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES


# service mysql start

2.) Buat direktory log

# mkdir /var/lib/mysql/mysql-bin-log/
# chown -R mysql.mysql /var/lib/mysql/mysql-bin-log/

3.) Restore Data Backup di Server Slave
#mysql -u root -p < data_dump.sql

4.) Setup Konfigurasi Master di Slave Server untuk Mengaktifkan Replikasi
mysql > CHANGE MASTER TO MASTER_HOST='masterdb',
     -> MASTER_USER='slave01',
     -> MASTER_PASSWORD='password',
     -> MASTER_LOG_FILE='mysql_bin.000004',
     -> MASTER_LOG_POS=120;

Jalankan sebagai slave
mysql> START SLAVE;

C. Test Replikasi
1.) Jalankan di Master
mysql> CREATE DATABASE test02;
mysql> CREATE TABLE employee (c int);
mysql> INSERT INTO employee (c) VALUES (1);
mysql> INSERT INTO employee (c) VALUES (3);
mysql> SELECT * FROM employee;
+------+
| c    |
+------+
|    1 |
|    3 |
+------+
2 rows in set (0.00 sec)
Test kembali dengan membuat database yang baru lainnya.

2.) Jalankan di Slave
Seharusnya sudah ada database yang baru beserta isian datanya.

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: masterdb
                  Master_User: slave01
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000004
          Read_Master_Log_Pos: 347
               Relay_Log_File: slavedb-relay-bin.000004
                Relay_Log_Pos: 510
        Relay_Master_Log_File: mysql_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: 347
              Relay_Log_Space: 848
              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: a758639f-c76b-11e2-91ea-08002789e29e
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           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
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>


Selamat Mencoba

Ref. 
- http://www.tecmint.com/how-to-setup-mysql-master-slave-replication-in-rhel-centos-fedora/
- http://opensourcedbms.com/dbms/setup-replication-with-mysql-5-6-server-on-centos-6-redhat-el6-fedora/