Monday, November 9, 2015

Quick Steps on how to setup MariaDB Enterprise Cluster (Galera Cluster)

Repeat below steps on servers “mysql01”, “mysql02” & “mysql03”

[root@mysql01 ~]# groupadd -g 500 mysql
[root@mysql01 ~]# useradd -g mysql -u 500 -m -d /home/mysql mysql
[mysql@mysql01 my]$ cd /my
[mysql@mysql01 my]$ tar zxvf repo/mariadb-enterprise-cluster-10.0.21-linux-x86_64.tar.gz
[mysql@mysql01 my]$ ln -s mariadb-enterprise-cluster-10.0.21-linux-x86_64 mysql
[mysql@mysql01 mysql]$ mkdir /my/data

Follow the steps based on server name. (Sample my.cnf referring to end of this post)


[mysql@mysql01 mysql]$ cp mariadb-enterprise.cnf /my/my.cnf
[mysql@mysql01 mysql]$ ./scripts/mysql_install_db --defaults-file=/my/my.cnf

[mysql@mysql01 my]$ scp my.cnf  mysql02:/my
[mysql@mysql01 my]$ scp my.cnf  mysql03:/my
[mysql@mysql01 my]$ scp -rp data mysql02:/my/
[mysql@mysql01 my]$ scp -rp data mysql03:/my/
[mysql@mysql01 my]$ /my/mysql/bin/mysqld --defaults-file=/my/my.cnf --wsrep-new-cluster &
[mysql@mysql02 my]$ /my/mysql/bin/mysqld --defaults-file=/my/my.cnf --wsrep_cluster_address=gcomm://192.168.6.61 &
[mysql@mysql03 my]$ /my/mysql/bin/mysqld --defaults-file=/my/my.cnf --wsrep_cluster_address=gcomm://192.168.6.61 &
[mysql@mysql01 my]$ /my/mysql/bin/mysqladmin -u root -S /tmp/mysql.sock shutdown
[mysql@mysql02 my]$ /my/mysql/bin/mysqladmin -u root -S /tmp/mysql.sock shutdown
[mysql@mysql03 my]$ /my/mysql/bin/mysqladmin -u root -S /tmp/mysql.sock shutdown
[mysql@mysql01 my]$ /my/mysql/bin/mysqld --defaults-file=/my/my.cnf --wsrep_cluster_address=gcomm:// &
[mysql@mysql02 my]$ /my/mysql/bin/mysqld --defaults-file=/my/my.cnf --wsrep_cluster_address=gcomm://192.168.6.61,192.168.6.63 &
[mysql@mysql03 my]$ /my/mysql/bin/mysqld --defaults-file=/my/my.cnf --wsrep_cluster_address=gcomm://192.168.6.61,192.168.6.62 &


MariaDB [(none)]> show status like 'wsre%';
+------------------------------+-------------------------------------------------------+
| Variable_name                | Value                                                 |
+------------------------------+-------------------------------------------------------+
| wsrep_local_state_uuid       | ec8dda0f-86f0-11e5-945a-53d2400df46b                  |
| wsrep_protocol_version       | 7                                                     |
| wsrep_last_committed         | 0                                                     |
| wsrep_replicated             | 0                                                     |
| wsrep_replicated_bytes       | 0                                                     |
| wsrep_repl_keys              | 0                                                     |
| wsrep_repl_keys_bytes        | 0                                                     |
| wsrep_repl_data_bytes        | 0                                                     |
| wsrep_repl_other_bytes       | 0                                                     |
| wsrep_received               | 10                                                    |
| wsrep_received_bytes         | 832                                                   |
| wsrep_local_commits          | 0                                                     |
| wsrep_local_cert_failures    | 0                                                     |
| wsrep_local_replays          | 0                                                     |
| wsrep_local_send_queue       | 0                                                     |
| wsrep_local_send_queue_max   | 1                                                     |
| wsrep_local_send_queue_min   | 0                                                     |
| wsrep_local_send_queue_avg   | 0.000000                                              |
| wsrep_local_recv_queue       | 0                                                     |
| wsrep_local_recv_queue_max   | 1                                                     |
| wsrep_local_recv_queue_min   | 0                                                     |
| wsrep_local_recv_queue_avg   | 0.000000                                              |
| wsrep_local_cached_downto    | 18446744073709551615                                  |
| wsrep_flow_control_paused_ns | 0                                                     |
| 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_cert_interval          | 0.000000                                              |
| wsrep_incoming_addresses     | 192.168.6.61:3306,192.168.6.62:3306,192.168.6.63:3306 |
| wsrep_evs_delayed            |                                                       |
| wsrep_evs_evict_list         |                                                       |
| wsrep_evs_repl_latency       | 0/0/0/0/0                                             |
| wsrep_evs_state              | OPERATIONAL                                           |
| wsrep_gcomm_uuid             | ec8ccb15-86f0-11e5-9d6e-0632737bbcb8                  |
| wsrep_cluster_conf_id        | 3                                                     |
| wsrep_cluster_size           | 3                                                     |
| wsrep_cluster_state_uuid     | ec8dda0f-86f0-11e5-945a-53d2400df46b                  |
| wsrep_cluster_status         | Primary                                               |
| wsrep_connected              | ON                                                    |
| wsrep_local_bf_aborts        | 0                                                     |
| wsrep_local_index            | 0                                                     |
| wsrep_provider_name          | Galera                                                |
| wsrep_provider_vendor        | Codership Oy <info@codership.com>                     |
| wsrep_provider_version       | 25.3.9(r3387)                                         |
| wsrep_ready                  | ON                                                    |
| wsrep_thread_count           | 2                                                     |
+------------------------------+-------------------------------------------------------+
57 rows in set (0.00 sec)


Sample “/my/my.cnf” on mysql01.

[mariadb]
plugin-maturity=gamma

feedback-user-info='enterprise'

## -- Remove the following line to enable feedback reporting to mariadb.org
feedback-url=''

[mysqld]
# -- Disable for applications using old auth protocol
enable-secure-auth
# -- Auditing - pre-load Plugin
plugin-load=server_audit

###########################################
[mysqld]

# General
datadir=/my/data
basedir=/my/mysql
# bind_address = 127.0.0.1
skip-name-resolve
max-allowed-packet                       = 64M
open-files-limit                         = 65535
max-connect-errors                       = 100000
max-connections                          = 1000
thread-cache-size                        = 200
back-log                                 = 128
#net_buffer_length                       = 16K
#connect_timeout                         = 10
#net_read_timeout                        = 30
#net_write_timeout                       = 60
#interactive_timeout                     = 28800
#wait_timeout                            = 28800
#net_retry_count                         = 10
character-set-server                     = 'utf8'
collation-server                         = 'utf8_general_ci'
# -- Do not change these defaults, unless you really know.
#read_rnd_buffer_size                    = 256K
#sort_buffer_size                        = 2M
#read_buffer_size                        = 128K
#max_tmp_tables                          = 32
tmp-table-size                           = 32M
max-heap-table-size                      = 32M
# -- This will enable a stricter SQL mode to avoid bad data
sql-mode                                 = STRICT_TRANS_TABLES

optimizer_search_depth                   = 0
optimizer_switch                         = 'index_merge=on'
optimizer_switch                         = 'index_merge_union=on'
optimizer_switch                         = 'index_merge_sort_union=on'
optimizer_switch                         = 'index_merge_intersection=on'
optimizer_switch                         = 'index_merge_sort_intersection=off'
optimizer_switch                         = 'index_condition_pushdown=on'
optimizer_switch                         = 'derived_merge=on'
optimizer_switch                         = 'derived_with_keys=on'
optimizer_switch                         = 'firstmatch=on'
optimizer_switch                         = 'loosescan=on'
optimizer_switch                         = 'materialization=on'
optimizer_switch                         = 'in_to_exists=on'
optimizer_switch                         = 'semijoin=on'
optimizer_switch                         = 'partial_match_rowid_merge=on'
optimizer_switch                         = 'partial_match_table_scan=on'
optimizer_switch                         = 'subquery_cache=on,mrr=on'
optimizer_switch                         = 'mrr_cost_based=on'
optimizer_switch                         = 'mrr_sort_keys=off'
optimizer_switch                         = 'outer_join_with_cache=on'
optimizer_switch                         = 'semijoin_with_cache=on'
optimizer_switch                         = 'join_cache_incremental=on'
optimizer_switch                         = 'join_cache_hashed=on'
optimizer_switch                         = 'join_cache_bka=on'
optimizer_switch                         = 'optimize_join_buffer_size=on'
optimizer_switch                         = 'table_elimination=on'
optimizer_switch                         = 'extended_keys=on'

join-buffer-space-limit                  = 4M
join-cache-level                         = 6
join-buffer-size                         = 4M

# Security
symbolic-links                           = 0
local-infile                             = 0

# Logging
log-warnings                             = 2
slow-query-log                           = 1
long-query-time                          = 5
log-slow-verbosity                       = 'query_plan,innodb'
#log_slow_rate_limit                     = 1000

# Replication / Binary logs
# -- Set to unique number on each server participating in replication
server-id                                 = 1
slave_net_timeout                         = 60
#max_prepared_stmt_count                   = 16382
# -- Annotate queries as comments in binary log, for row based events (Breaks replication to MySQL)
#If you plan to downgrade to MySQL don’t set binlog_annotate_row_events to ON.
binlog-annotate-row-events               = ON
log-bin=mariadb-bin
# BIN.2  Binlog File
binlog-format                            = ROW
expire-logs-days                         = 7
max-binlog-size                          = 1024M
# -- Recommended for binary log durability. Expect 60% performance hit without write cache on controller
sync-binlog                              = 1
binlog-stmt-cache-size                   = 128K
binlog-cache-size                        = 256K
#slave_compressed_protocol                = ON
slave-transaction-retries                = 10
#relay_log_recovery                       = ON
#sync_master_info                         = 1
#sync_relay_log                           = 0
#sync_relay_log_info                      = 1

# InnoDB
default-storage-engine                   = 'InnoDB'
innodb-stats-on-metadata                 = 0
innodb-stats-sample-pages                = 32
table-definition-cache                   = 2048
table-open-cache                         = 2048
transaction-isolation                    = READ-COMMITTED
# To be set to 0 if not multi transactional storage engine
innodb-support-xa                        = ON

# Query Cache
query-cache-size                         = 0
query-cache-type                         = 0
innodb-buffer-pool-instances             = 8
# -- Set the following to maximum 60/70% of physical RAM.
innodb-buffer-pool-size                  = 2G
# innodb_max_dirty_pages_pct should be compute with redo log size in mind: It’s recommended to set the dirty pages smaller than redo log space. The more you have dirty page, the less io will be produced on disk.
innodb-max-dirty-pages-pct               = 50
innodb-file-per-table                    = 1
innodb-file-format                       = Barracuda
innodb-flush-log-at-trx-commit           = 1
#Save and restore buffer pool to be transparent for user
#innodb_flush_method                      = O_DIRECT
innodb-log-buffer-size                   = 64M
innodb-log-files-in-group                = 2
#innodb-log-file-size                     = 1024M
#innodb_purge_threads                     = 1
# -- Raid1: 200. Raid10: 200 x write arrays. SSD: 5000. FusionIO: 20000.
#innodb_io_capacity                      = 200
# -- SSD & FusionIO can flush pages in random disk order, keep default for spinning disks
#innodb_flush_neighbors                   = 0
# -- Increase these numbers to increase global throughput on SAN (16,32,64)
innodb-read-io-threads                   = 8
innodb-write-io-threads                  = 8
# -- Set this to 2-4 times # of Cpus, maximum recommended is 64.
# -- iostat report io service time: io_capacity / service_time is a good starting point for innodb_write_io_threads
# -- innodb_read_io_threads starting point monitoring status # read ahead per second
#innodb-thread-concurrency                = 64

innodb-open-files                       = 2048

# MyISAM
key-buffer-size                          = 64M
flush                                    = OFF
myisam-recover-options                   = BACKUP,FORCE
myisam-sort-buffer-size                  = 64M

# Other
# -- Enable INFORMATION_SCHEMA.%_STATISTICS tables
userstat                                 = ON
archive                                  = OFF
blackhole                                = OFF
#federated                               = OFF
#innodb                                  = FORCE

[mysql]
default-character-set                    = 'utf8'
auto-rehash                              = FALSE
local-infile                             = 1
max-allowed-packet                       = 64M
secure-auth                              = TRUE

[mysqldump]
max-allowed-packet                       = 1G
default-character-set                    = 'utf8'

[myisamchk]
key-buffer-size                          = 1G
sort-buffer-size                         = 1G
read-buffer-size                         = 8M
write-buffer-size                        = 8M

#
# * Galera-related settings
#

[galera]
# Mandatory settings:
# -- The path to the galea library, usually its
# -- /usr/lib(64)/galera/libgalera_smm.so
wsrep_provider=/my/mysql/lib/libgalera_smm.so
# -- Address of any single member node of the cluster. You can also specify
# -- addresses of multiple nodes in the cluster (recommended). It takes the
# -- address(es) in the following format : gcomm://ip[:port][,ip[:port]].
# -- An empty address (gcomm://) would cause the node to bootstrap.
# below Bootstrap for first time initialization on mysql01
#wsrep_cluster_address=gcomm://

#After this single-node cluster is started,
#variable wsrep_cluster_address should be updated to the list of all nodes in the cluster
wsrep_cluster_address=gcomm://192.168.6.61,192.168.6.62,192.168.6.63


# -- Binary log format for write sets.
binlog_format=row
# -- Default storage engine to create tables.
default_storage_engine=InnoDB
# -- interleaved lock-mode
innodb_autoinc_lock_mode=2
# -- Accept remote connections (for mysqldump state-transfer method).
bind-address=0.0.0.0
#
# Optional settings:
# -- Number of worker threads to apply replicated transactions in parallel.
#wsrep_slave_threads=1
# -- Do not write log buffer to log file at transaction commit, but once per
# -- second.
innodb_flush_log_at_trx_commit=0
# -- Do not allow to create table without primary key.
innodb_force_primary_key=1