Tag: mariadb

OS: Ubuntu 14.04
Untuk situasi dimana kita hanya ada 2 server yang boleh digunakan.

2 node mariadb + 1 node garb/arbiter (elak split-brain)

Pastikan /etc/hosts ada entri ketiga-tiga node. Contoh:

[code lang=’plain’]127.0.0.1 localhost
127.0.1.1 ubuntu
192.168.56.215 db1
192.168.56.216 db2
192.168.56.217 garb[/code]

Pasang mariadb pada kedua-dua node mariadb

sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
sudo add-apt-repository 'deb http://mariadb.nethub.com.hk//repo/10.0/ubuntu trusty main'
sudo apt-get update
sudo apt-get install mariadb-galera-server galera

Pasang garbd pada node garb

sudo apt-get install galera

Matikan service mariadb pada kedua-dua node

sudo service mysql stop

Contoh setting /etc/mysql/my.cnf. Sesuaikan untuk setiap node. srep_cluster_address="gcomm://db2?pc.wait_prim=no" perlu set terbalik seperti ditunjukkan.

[code lang=’plain’][client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
nice = 0
socket = /var/run/mysqld/mysqld.sock

[mysqld]
basedir = /var/lib/mysql
bind-address = 192.168.56.215
binlog_format = ROW
character_set_server = utf8
collation_server = utf8_general_ci
datadir = /var/lib/mysql
default-storage-engine = InnoDB
expire_logs_days = 10
innodb_autoinc_lock_mode = 2
innodb_buffer_pool_size = 1G
innodb_log_file_size = 512M
innodb_doublewrite = 1
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 60
innodb_locks_unsafe_for_binlog = 1
innodb_stats_on_metadata = 0
key_buffer = 256M
lc-messages-dir = /usr/share/mysql
lock_wait_timeout = 300
max_allowed_packet = 128M
max_binlog_size = 128M
max_connections = 64
myisam-recover = BACKUP
myisam_sort_buffer_size = 64M
net_buffer_length = 8K
open-files-limit = 65535
pid-file = /var/run/mysqld/mysqld.pid
port = 3306
query_cache_limit = 8M
query_cache_size = 16M
read_buffer_size = 8M
read_rnd_buffer_size = 8M
skip-external-locking
socket = /var/run/mysqld/mysqld.sock
sort_buffer_size = 16M
table_cache = 2M
table_definition_cache = 65535
table_open_cache = 65535
thread_cache_size = 8
thread_concurrency = 8
tmpdir = /tmp
user = mysql
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address=”gcomm://db2?pc.wait_prim=no”
wsrep_sst_method=rsync
wsrep_cluster_name=”joomla_db_cluster”

[mysqldump]
max_allowed_packet = 16M
quick
quote-names

[mysql]

[isamchk]
!includedir /etc/mysql/conf.d/
key_buffer = 256M
read_buffer = 16M
sort_buffer_size = 256M
write_buffer = 16M
[/code]

Pada node db1, mulakan cluster mariadb

mysqld --wsrep_cluster_address=gcomm://

Pada nod db1, salin fail debian.cnf ke nod db2

scp /etc/mysql/debian.cnf user1@192.168.56.216:/home/user1/debian.cnf
sudo cp debian.cnf /etc/mysql/

Mulakan mariadb pada node2

sudo service mysql start

Cuba mulakan garbd pada nod garb

sudo garbd -a gcomm://db1:4567,db2:4567?pc.wait_prim=no -g "joomla_db_cluster"

Contoh setting /etc/default/garb pada node garb

[code lang=’plain’]GALERA_NODES=”db1:4567 db2:4567″
GALERA_GROUP=”joomla_db_cluster”
GALERA_OPTIONS=”pc.wait_prim=no”
LOG_FILE=”/var/log/garbd.log”[/code]

Mulakan garbd pada node garb

sudo service garb start

Matikan mariadb pada node db1

sudo service mysql stop

Mulakan semula mariadb pada node db1

sudo service mysql start

Contoh log cluster dengan kesemua node berjalan (semak dalam /var/log/garb.log)

[code lang=’plain’]2015-01-28 22:46:37.658 INFO: Quorum results:
version = 3,
component = PRIMARY,
conf_id = 6,
members = 3/3 (joined/total),
act_id = 0,
last_appl. = 0,
protocols = 0/5/3 (gcs/repl/appl),
group UUID = 1828d0b2-a6ef-11e4-bf0a-3217c0075751
2015-01-28 22:46:37.658 INFO: Flow-control interval: [9999999, 9999999]
2015-01-28 22:46:37.665 INFO: Member 2.0 (db1) synced with group.[/code]