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