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.

Setting MySQL Replication