Setting MySQL Replication

Langkah-langkah melakukan konfigurasi untuk replikasi data dari master ke slave adalah sebagi berikut

Pada Master

1. Edit my.cnf seperti contoh berikut ini:
   [mysqld]
   log-bin=mysql-bin
   binlog_do_db=cobaku
   server-id=1
   datadir=/var/lib/mysql
   socket=/var/lib/mysql/mysql.sock
   user=mysql
   # Disabling symbolic-links is recommended to prevent assorted security risks
   symbolic-links=0

   [mysqld_safe]
   log-error=/var/log/mysqld.log
   pid-file=/var/run/mysqld/mysqld.pid

   [innodb]
   innodb_flush_log_at_trx_commit=1
   sync_binlog=1

   Perhatikan:
   – server-id isikan dengan 1
   – log-bin isikan dengan mysql-bin
   – binlog_do_db isikan dengan database yang akan direplikasi

2. Restart mysql misalnya service mysqld restart
3. Membuat user untuk slave melakukan replikasi.
   Dari mysql shell:
   CREATE USER salin@119.235.248.xxx IDENTIFIED BY ‘pastisama’;
   GRANT REPLICATION SLAVE ON *.* TO salin@119.235.248.115;
   FLUSH PRIVILLIGES;
4. Melihat posisi log
   Dari mysql shell:
   mysql> use cobaku;
   Reading table information for completion of table and column names
   You can turn off this feature to get a quicker startup with -A

   Database changed
   mysql> FLUSH TABLES WITH READ LOCK;
   Query OK, 0 rows affected (0.00 sec)

   mysql> SHOW MASTER STATUS;
   +——————+———-+————–+——————+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +——————+———-+————–+——————+
   | mysql-bin.000005 |      106 | cobaku       |                  |
   +——————+———-+————–+——————+
   1 row in set (0.00 sec)

5. Backup database
   Dari Linux shell: mysqldump –opt cobaku > cobaku.sql

Pada Slave:

1. Edit my.cnf seperti contoh berikut ini:
   [mysqld]
   server-id=2
   relay-log=mysql-relay-bin.log
   log_bin=mysql-bin.log
   binlog_do_db=cobaku
   datadir=/var/lib/mysql
   socket=/var/lib/mysql/mysql.sock
   user=mysql
   # Disabling symbolic-links is recommended to prevent assorted security risks
   symbolic-links=0

   [mysqld_safe]
   log-error=/var/log/mysqld.log
   pid-file=/var/run/mysqld/mysqld.pid

   Perhatikan:
   – server-id isikan dengan 2
   – relay-log isikan dengan mysql-relay-bin.log
   – binlog_do_db silahkan diisi sesuai dengan Master

2. Buat database:
   CREATE cobaku;
3. Restore database
   Dari linux shell: mysql cobaku < cobaku.sql
4. Memulai replication:
   CHANGE MASTER TO MASTER_HOST=’mvps02.master.xxxx’,MASTER_USER=’salin’,                                                                                         MASTER_PASSWORD=’pastisama’, MASTER_LOG_FILE=’mysql-bin.000005′, MASTER_LOG_POS=106;
   START SLAVE;
5. Melihat status replication:
   SHOW SLAVE STATUS\G
   *************************** 1. row ***************************
                  Slave_IO_State: Waiting for master to send event
                     Master_Host: mvps02.solusiweb.com
                     Master_User: salin
                     Master_Port: 3306
                   Connect_Retry: 60
                 Master_Log_File: mysql-bin.000005
             Read_Master_Log_Pos: 106
                  Relay_Log_File: mysql-relay-bin.000002
                   Relay_Log_Pos: 251
           Relay_Master_Log_File: mysql-bin.000005
                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: 106
                 Relay_Log_Space: 406
                 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:
   1 row in set (0.00 sec)
6. Pada saat ini, jika ada perubahan data di master, maka slave akan secara aktif mengambil perubahan dan diupdate ke server slave

Jika ada kesulitan koneksi dari shell linux slave kita bisa mencoba koneksi : mysql -h mvps02.masterxxx -u salin -ppastisama.

Informasi lebih lanjuit silahkan mengunjungi:
1. https://www.digitalocean.com/community/articles/how-to-set-up-master-slave-replication-in-mysql .
2. http://dev.mysql.com/doc/refman/5.1/en/replication.html .

Kunjungi www.proweb.co.id untuk menambah wawasan anda.