Cara menggunakan mysql query_cache

Untuk dapat mencoba melakukan replikasi master to master mysql ini, silakan persiapkan (minimal) 2 server mysql siap pakai, contoh dengan IP 10.2.9.1 dan 10.2.9.2.

Table of Contents Show

Apa itu query di MySQL?
Apa perintah query yang digunakan untuk menampilkan data?
Bagaimana cara kerja dari MySQL?
Apa itu select pada database?

Langkah Replikasi Master to Master MySQL

1. Sesuaikan konfigurasi masing-masing server MySQL sebagai master. Ubah pada tiap file /etc/my.cnf.
Untuk master server 1 misalnya:

[mysqld]
port=3306
datadir=/home/mysql/
socket=/usr/local/mysql/tmp/mysql.sock
user=mysql
old_passwords=0
#symbolic-links=0
#query_cache_size = 268435456
query_cache_size = 256M
query_cache_type=1
query_cache_limit = 1048576
log = 1
long_query_time = 1
slow_query_log	= 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
general_log	= 1
general_log_file =	/var/log/mysqld.log
server_id           = 1
log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index       = /var/log/mysql/mysql-bin.log.index
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index     = /var/log/mysql/mysql-relay-bin.index
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1
bind-address = 0.0.0.0
local-infile = 0

sedangkan untuk server master 2:

[mysqld]
port=3306
datadir=/home/mysql
socket=/usr/local/mysql/tmp/mysql.sock
user=mysql
old_passwords=0
#symbolic-links=0
query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576
log = 1
long_query_time = 1
slow_query_log	= 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
general_log	= 1
general_log_file =	/var/log/mysqld.log
server_id           = 2
log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index       = /var/log/mysql/mysql-bin.log.index
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index     = /var/log/mysql/mysql-relay-bin.index
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1
bind-address = 0.0.0.0
local-infile = 0

2. Instal replikasi ke masing-masing server, login ke server1 sebagai root.
a. buatlah 3 user untuk keperluan replikasi ini: mysqlchkuser, mmm_monitor, mmm_agent, dan replication.
b. Grant mmm_monitor sebagai client replication.
c. Grant mmm_agent sebagai super dan client replication.
d. Grant replication sebagai slave
e. flush privileges mysql
f. flush juga read lock mysql
g. cek status master mysql
gambaran langkah-langkah diatas dalam perintah dan respon di console seperti berikut:

mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.5.12-log Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement
mysql> create user 'mysqlchkuser'@'localhost' identified by 'mysql321';
Query OK, 0 rows affected (0.06 sec)
mysql> create user 'mmm_monitor'@'%' identified by 'monitor_password';
Query OK, 0 rows affected (0.06 sec)
mysql> create user 'mmm_agent'@'%' identified by 'agent_password';
Query OK, 0 rows affected (0.00 sec)
mysql> create user 'replication'@'%' identified by 'replication_password';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'%' IDENTIFIED BY 'monitor_password';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'%' IDENTIFIED BY 'agent_password';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY 'replication_password';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
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.000001 |     1044 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

h. Dump database master server 1 ke master server 2 dengan mysqldump

mysqldump -u root -p --all-databases > /tmp/database-backup.sql

i. Pindah file backup ke master server 2 dengan scp,

scp /tmp/database-backup.sql 10.2.9.2:~/

j. Unlock table di master 1.
3. Login ke master server 2 sebagai root di mysql.
a. flush privileges yang ada.
b. ubah master mysql dengan master_host = ‘10.2.9.1’ (atau sesuai IP master server 1 anda) berikut dengan port (biasanya 3306), master user (dalam hal ini ‘replication’), password user dan log file mastering nya.

mysql> flush privileges;
Query OK, 0 rows affected (0.46 sec)
mysql> CHANGE MASTER TO master_host='10.2.9.1', master_port=3306, master_user='replication', -> master_password='replication_password', master_log_file='mysql-bin.000001', master_log_pos=1044;

c. jalankan slave pada master server 2 dan cek status slave-nya.

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G run command
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.2.9.1
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1044
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000001
             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: 1044
              Relay_Log_Space: 409
              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
1 row in set (0.00 sec)

d. cek plu status master-nya

mysql> show master status -> ;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 27957968 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 27957968 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

4. Kembali ke master server 1 dan setting master
a. Ubah master mysql dengan master host sesuai IP master server 2, sesuaikan port, user, password dan file log-nya.

mysql> CHANGE MASTER TO master_host='10.2.9.2', master_port=3306, master_user='replication', -> master_password='replication_password', master_log_file='mysql-bin.000002', master_log_pos=27957968;
Query OK, 0 rows affected (0.34 sec)

b. jalankan slave dan cek statusnya

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G run command
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.2.9.1
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 27957968
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000002
             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: 27957968
              Relay_Log_Space: 409
              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: 2
1 row in set (0.00 sec)

5. Uji coba replikasi.
a. Pada console master server 1, silakan lihat daftar database
b. Cobalah buat sebuah database baru

[mysqld]
port=3306
datadir=/home/mysql
socket=/usr/local/mysql/tmp/mysql.sock
user=mysql
old_passwords=0
#symbolic-links=0
query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576
log = 1
long_query_time = 1
slow_query_log	= 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
general_log	= 1
general_log_file =	/var/log/mysqld.log
server_id           = 2
log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index       = /var/log/mysql/mysql-bin.log.index
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index     = /var/log/mysql/mysql-relay-bin.index
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1
bind-address = 0.0.0.0
local-infile = 0

c. Coba cek di master server 2, harusnya database contohreplikasi otomatis juga dibuatkan.

[mysqld]
port=3306
datadir=/home/mysql
socket=/usr/local/mysql/tmp/mysql.sock
user=mysql
old_passwords=0
#symbolic-links=0
query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576
log = 1
long_query_time = 1
slow_query_log	= 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
general_log	= 1
general_log_file =	/var/log/mysqld.log
server_id           = 2
log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index       = /var/log/mysql/mysql-bin.log.index
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index     = /var/log/mysql/mysql-relay-bin.index
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1
bind-address = 0.0.0.0
local-infile = 0

Sekian. Semoga berhasil dan selamat menikmati replikasi data master to master MySQL.
Referensi:
1. emka.web.id (http://emka.web.id/programming/database/mysql/2012/replikasi-master-to-master-mysql/)
2. MySQL-mmm.org (http://mysql-mmm.org/mmm2:guide)
3. ALinux.web.id (http://alinux.web.id/2011/08/16/replicate-master-to-master-mysql.html)

Apa itu query di MySQL?

MySQL Query adalah perintah atau instruksi yang dapat digunakan untuk mengelola database atau tabel dalam database MySQL. Query lebih dikenal dengan sebutan SQL (Structured Query Language) yang artinya adalah sebuah bahasa yang digunakan untuk mengakses data dalam basis data relasional.

Apa perintah query yang digunakan untuk menampilkan data?

Query adalah perintah SQL yang bertujuan untuk menampilkan data tertentu dari tabel.

Bagaimana cara kerja dari MySQL?

Cara kerja MySQL.

MySQL membuat database yang dapat memodifikasi, menyimpan data, dan menentukan keterkaitan tabel-tabel yang ada di dalam software..

Kemudian, perangkat pengguna membuat request dengan perintah spesifik menggunakan bahasa SQL..

Terakhir, server akan menerima dan menjalankan perintah..

Apa itu select pada database?

1. SELECT. Select menjadi perintah dasar dari SQL dengan tipe sintaks DML. Perintah ini digunakan untuk menampilkan, mengambil maupun memilah informasi dari database atau data dari satu tabel serta beberapa tabel dalam relasi.