Cara menggunakan mysql slow connection

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

3 Apakah cara tercepat untuk mengakses database melalui MySQL client?
Apa itu Slow query?
Apa itu port 3306?
Apa saja kelemahan MySQL server?

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)

3 Apakah cara tercepat untuk mengakses database melalui MySQL client?

Cara paling cepat untuk memulai MySQL Client adalah dengan menggunakan Command Line Client. Aplikasi ini bisa diakses dari menu Start –> MySQL –> MySQL 8.0 Command Line Client.

Apa itu Slow query?

Slow Query Log adalah catatan yang berisi Query SQL yang memerlukan waktu lama dari long_query_time ketika dijalankan.

Apa itu port 3306?

Port 3306 adalah port protokol MySQL default, yang digunakan oleh klien mysql, konektor MySQL, dan utilitas seperti mysqldump dan mysqlpump. Port 33060 adalah port default untuk Database Extended Interface (protokol MySQL X).