Untuk mengikuti tutorial ini setidaknya anda membutuhkan 4 server, 3 server sebagai uji coba kluster server MySQL dan 1 sebagai load balancer. Install dan buat 4 server Linux dengan Virtualbox/VMWare.
Tutorial ini menggunakan skenario 3 server replika untuk menangani seluruh request dari Client dengan ip address sebagai berikut:
mysql1: 192.168.1.138/24
mysql2: 192.168.1.139/24
mysql3: 192.168.1.140/24
Dalam kluster ini, server mysql2 akan terkoneksi ke mysql1 dan server mysql3 ke mysql2. Jika koneksi antara mysql2 dan mysql3 sudah terbangun, komunikasi antara mysql1 ke mysql3 akan dibentuk secara otomatis dengan komunikasi satu arah (termasuk ke semua node pada kluster).
[sourcecode language="bash"]
# apt-get install libaio1 libdbi-perl libdbd-mysql-perl mysql-client rsync
[/sourcecode]
Debian/Ubuntu 32 bits:
[sourcecode language="bash"]
# wget https://launchpad.net/codership-mysql/5.5/5.5.28-23.7/+download/mysql-server-wsrep-5.5.28-23.7-i386.deb && dpkg -i mysql-server-wsrep-5.5.28-23.7-i386.deb
[/sourcecode]
Debian/Ubuntu 64 bits:
[sourcecode language="bash"]
# wget https://launchpad.net/codership-mysql/5.5/5.5.28-23.7/+download/mysql-server-wsrep-5.5.28-23.7-amd64.deb && dpkg -i mysql-server-wsrep-5.5.28-23.7-amd64.deb
[/sourcecode]
- 32 bits:
[sourcecode language="bash"]
# wget https://launchpad.net/galera/2.x/23.2.2/+download/galera-23.2.2-i386.deb && dpkg -i galera-23.2.2-i386.deb
[/sourcecode]
- 64 bits:
[sourcecode language="bash"]
# wget https://launchpad.net/galera/2.x/23.2.2/+download/galera-23.2.2-amd64.deb && dpkg -i galera-23.2.2-amd64.deb
[/sourcecode]
[sourcecode language="bash"]
# /etc/init.d/mysql start
# mysql -u root
[/sourcecode]
[sourcecode language="bash"]
mysql> DELETE FROM mysql.user WHERE user='';
mysql> GRANT ALL ON *.* TO root@'%' IDENTIFIED BY 'P@ssw0rd';
mysql> UPDATE mysql.user SET Password=PASSWORD('P@ssw0rd') WHERE User='root';
mysql> GRANT ALL ON *.* to sst@'%' IDENTIFIED BY 'sstpasswd';
[/sourcecode]
Atur supaya mysqld dijalankan sesaat setelah booting up:
[sourcecode language="bash"]
# update-rc.d mysql defaults
[/sourcecode]
[sourcecode]
# vi /etc/mysql/conf.d/wsrep.cnf
# Full path to wsrep provider library or 'none'
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Group communication system handle
wsrep_cluster_address="gcomm://"
# State Snapshot Transfer method
wsrep_sst_method=rsync
# SST authentication string. This will be used to send SST to joining nodes.
# Depends on SST method. For mysqldump method it is root:
wsrep_sst_auth=sst:sstpasswd
[/sourcecode]
[sourcecode]
# /etc/init.d/mysql restart[/sourcecode]
NB. Untuk sementara, alamat node masih gcomm://, selanjutnya akan dikonfigurasi pada server 3.
[sourcecode]
# vi /etc/mysql/conf.d/wsrep.cnf
# Full path to wsrep provider library or 'none'
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Group communication system handle
wsrep_cluster_address="gcomm://192.168.1.138:4567"
# State Snapshot Transfer method
wsrep_sst_method=rsync
# SST authentication string. This will be used to send SST to joining nodes.
# Depends on SST method. For mysqldump method it is root:
wsrep_sst_auth=sst:sstpasswd
[/sourcecode]
[sourcecode]
# /etc/init.d/mysql restart
[/sourcecode]
[sourcecode]
# vi /etc/mysql/conf.d/wsrep.cnf
# Full path to wsrep provider library or 'none'
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Group communication system handle
wsrep_cluster_address="gcomm://192.168.1.139:4567"
# State Snapshot Transfer method
wsrep_sst_method=rsync
# SST authentication string. This will be used to send SST to joining nodes.
# Depends on SST method. For mysqldump method it is root:
wsrep_sst_auth=sst:sstpasswd
[/sourcecode]
[sourcecode]
# /etc/init.d/mysql restart
[/sourcecode]
[sourcecode]
# vi /etc/mysql/conf.d/wsrep.cnf
wsrep_cluster_address="gcomm://192.168.1.140:4567"
[/sourcecode]
[sourcecode]
# mysql -u root -p
[/sourcecode]
[sourcecode]
mysql> set global wsrep_cluster_address='gcomm://192.168.1.140:4567';
[/sourcecode]
Checking wsrep variables
[sourcecode]
mysql> show status like 'wsrep%';
+----------------------------+----------------------------------------------------------+
| Variable_name | Value |
+----------------------------+----------------------------------------------------------+
| wsrep_local_state_uuid | 17048124-4c6e-11e2-0800-5c8217cefd3f |
| wsrep_protocol_version | 4 |
| wsrep_last_committed | 1 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_received | 10 |
| wsrep_received_bytes | 1039 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_avg | 0.000000 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_cert_deps_distance | 0.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 0.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 0.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 0 |
| wsrep_causal_reads | 0 |
| wsrep_incoming_addresses | 192.168.1.138:3306,192.168.1.140:3306,192.168.1.139:3306 |
| wsrep_cluster_conf_id | 5 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | 17048124-4c6e-11e2-0800-5c8217cefd3f |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 23.2.2(r137) |
| wsrep_ready | ON |
+----------------------------+----------------------------------------------------------+
40 rows in set (0.00 sec)
[/sourcecode]
The most important variables are wsrep_ready, if the value is ON it means that the cluster is working, and wsrep_cluster_size that is equals to the number of nodes that is composed the cluster.
Tutorial ini menggunakan skenario 3 server replika untuk menangani seluruh request dari Client dengan ip address sebagai berikut:
mysql1: 192.168.1.138/24
mysql2: 192.168.1.139/24
mysql3: 192.168.1.140/24
Dalam kluster ini, server mysql2 akan terkoneksi ke mysql1 dan server mysql3 ke mysql2. Jika koneksi antara mysql2 dan mysql3 sudah terbangun, komunikasi antara mysql1 ke mysql3 akan dibentuk secara otomatis dengan komunikasi satu arah (termasuk ke semua node pada kluster).
Instalasi MySQL
[sourcecode language="bash"]
# apt-get install libaio1 libdbi-perl libdbd-mysql-perl mysql-client rsync
[/sourcecode]
Install MySQL server dengan wsrep patch
Debian/Ubuntu 32 bits:
[sourcecode language="bash"]
# wget https://launchpad.net/codership-mysql/5.5/5.5.28-23.7/+download/mysql-server-wsrep-5.5.28-23.7-i386.deb && dpkg -i mysql-server-wsrep-5.5.28-23.7-i386.deb
[/sourcecode]
Debian/Ubuntu 64 bits:
[sourcecode language="bash"]
# wget https://launchpad.net/codership-mysql/5.5/5.5.28-23.7/+download/mysql-server-wsrep-5.5.28-23.7-amd64.deb && dpkg -i mysql-server-wsrep-5.5.28-23.7-amd64.deb
[/sourcecode]
Download dan install Galera
- 32 bits:
[sourcecode language="bash"]
# wget https://launchpad.net/galera/2.x/23.2.2/+download/galera-23.2.2-i386.deb && dpkg -i galera-23.2.2-i386.deb
[/sourcecode]
- 64 bits:
[sourcecode language="bash"]
# wget https://launchpad.net/galera/2.x/23.2.2/+download/galera-23.2.2-amd64.deb && dpkg -i galera-23.2.2-amd64.deb
[/sourcecode]
Konfigurasi Awal MySQL
[sourcecode language="bash"]
# /etc/init.d/mysql start
# mysql -u root
[/sourcecode]
[sourcecode language="bash"]
mysql> DELETE FROM mysql.user WHERE user='';
mysql> GRANT ALL ON *.* TO root@'%' IDENTIFIED BY 'P@ssw0rd';
mysql> UPDATE mysql.user SET Password=PASSWORD('P@ssw0rd') WHERE User='root';
mysql> GRANT ALL ON *.* to sst@'%' IDENTIFIED BY 'sstpasswd';
[/sourcecode]
Atur supaya mysqld dijalankan sesaat setelah booting up:
[sourcecode language="bash"]
# update-rc.d mysql defaults
[/sourcecode]
Konfigurasi Server MySQL1
[sourcecode]
# vi /etc/mysql/conf.d/wsrep.cnf
# Full path to wsrep provider library or 'none'
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Group communication system handle
wsrep_cluster_address="gcomm://"
# State Snapshot Transfer method
wsrep_sst_method=rsync
# SST authentication string. This will be used to send SST to joining nodes.
# Depends on SST method. For mysqldump method it is root:
wsrep_sst_auth=sst:sstpasswd
[/sourcecode]
[sourcecode]
# /etc/init.d/mysql restart[/sourcecode]
NB. Untuk sementara, alamat node masih gcomm://, selanjutnya akan dikonfigurasi pada server 3.
Konfigurasi Server MySQL2
[sourcecode]
# vi /etc/mysql/conf.d/wsrep.cnf
# Full path to wsrep provider library or 'none'
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Group communication system handle
wsrep_cluster_address="gcomm://192.168.1.138:4567"
# State Snapshot Transfer method
wsrep_sst_method=rsync
# SST authentication string. This will be used to send SST to joining nodes.
# Depends on SST method. For mysqldump method it is root:
wsrep_sst_auth=sst:sstpasswd
[/sourcecode]
[sourcecode]
# /etc/init.d/mysql restart
[/sourcecode]
Konfigurasi Server MySQL3
[sourcecode]
# vi /etc/mysql/conf.d/wsrep.cnf
# Full path to wsrep provider library or 'none'
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Group communication system handle
wsrep_cluster_address="gcomm://192.168.1.139:4567"
# State Snapshot Transfer method
wsrep_sst_method=rsync
# SST authentication string. This will be used to send SST to joining nodes.
# Depends on SST method. For mysqldump method it is root:
wsrep_sst_auth=sst:sstpasswd
[/sourcecode]
[sourcecode]
# /etc/init.d/mysql restart
[/sourcecode]
Konfigurasi Ulang Server MySQL1
[sourcecode]
# vi /etc/mysql/conf.d/wsrep.cnf
wsrep_cluster_address="gcomm://192.168.1.140:4567"
[/sourcecode]
[sourcecode]
# mysql -u root -p
[/sourcecode]
[sourcecode]
mysql> set global wsrep_cluster_address='gcomm://192.168.1.140:4567';
[/sourcecode]
Checking wsrep variables
[sourcecode]
mysql> show status like 'wsrep%';
+----------------------------+----------------------------------------------------------+
| Variable_name | Value |
+----------------------------+----------------------------------------------------------+
| wsrep_local_state_uuid | 17048124-4c6e-11e2-0800-5c8217cefd3f |
| wsrep_protocol_version | 4 |
| wsrep_last_committed | 1 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_received | 10 |
| wsrep_received_bytes | 1039 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_avg | 0.000000 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_cert_deps_distance | 0.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 0.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 0.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 0 |
| wsrep_causal_reads | 0 |
| wsrep_incoming_addresses | 192.168.1.138:3306,192.168.1.140:3306,192.168.1.139:3306 |
| wsrep_cluster_conf_id | 5 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | 17048124-4c6e-11e2-0800-5c8217cefd3f |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 23.2.2(r137) |
| wsrep_ready | ON |
+----------------------------+----------------------------------------------------------+
40 rows in set (0.00 sec)
[/sourcecode]
The most important variables are wsrep_ready, if the value is ON it means that the cluster is working, and wsrep_cluster_size that is equals to the number of nodes that is composed the cluster.
