MariaDB Galera Cluster – Water's Home

Install Database On Controller Nodes

[root@controller1 ~]# yum install memcached python-memcached -y

[root@controller1 ~]# yum install mariadb-galera-server mariadb-galera-common galera rsync -y

Database configuration(controller1)

```
[root@controller1 ~]# cat /etc/my.cnf
```
```
[mysqld]
```
```
datadir=/var/lib/mysql
```
```
socket=/var/lib/mysql/mysql.sock
```
```
user=mysql
```
```
binlog_format=ROW
```

bind-address=192.168.220.21 # The management IP address of the controller node

```
 
```
```
# InnoDB Configuration
```
```
default_storage_engine=innodb
```
```
innodb_autoinc_lock_mode=2
```
```
innodb_flush_log_at_trx_commit=0
```
```
innodb_buffer_pool_size=122M
```
```
 
```
```
!includedir /etc/my.cnf.d/
```
```
 
```

[root@controller1 ~]# cat /etc/my.cnf.d/galera.cnf | grep ^[^#].*

```
[mysqld]
```
```
binlog_format=ROW
```
```
default-storage-engine=innodb
```
```
innodb_autoinc_lock_mode=2
```
```
bind-address=0.0.0.0
```
```
wsrep_on=1
```

wsrep_provider=/usr/lib64/galera/libgalera_smm.so

```
wsrep_cluster_name="my_wsrep_cluster"
```

wsrep_cluster_address="gcomm://controller1,controller2,controller3"

```
wsrep_node_name=controller1
```
```
wsrep_node_address=192.168.220.21
```
```
wsrep_slave_threads=1
```
```
wsrep_certify_nonPK=1
```
```
wsrep_max_ws_rows=131072
```
```
wsrep_max_ws_size=1073741824
```
```
wsrep_debug=0
```
```
wsrep_convert_LOCK_to_trx=0
```
```
wsrep_retry_autocommit=1
```
```
wsrep_auto_increment_control=1
```
```
wsrep_drupal_282555_workaround=0
```
```
wsrep_causal_reads=0
```
```
wsrep_notify_cmd=
```
```
wsrep_sst_method=rsync
```
```
wsrep_sst_auth=root:
```

Database configuration(controller2,controller3)

[root@controller1 ~]# rsync -avzP  -e 'ssh -p 22'  /etc/my.cnf controller2:/etc/

[root@controller1 ~]# rsync -avzP  -e 'ssh -p 22'  /etc/my.cnf.d/galera.cnf controller2:/etc/my.cnf.d/

[root@controller1 ~]# rsync -avzP  -e 'ssh -p 22'  /etc/my.cnf controller3:/etc/

[root@controller1 ~]# rsync -avzP  -e 'ssh -p 22'  /etc/my.cnf.d/galera.cnf controller3:/etc/my.cnf.d/

```
 
```
```
[root@controller2 ~]# cat /etc/my.cnf
```
```
[mysqld]
```
```
datadir=/var/lib/mysql
```
```
socket=/var/lib/mysql/mysql.sock
```
```
user=mysql
```
```
binlog_format=ROW
```

bind-address=192.168.220.22 # The management IP address of the controller node

```
 
```
```
# InnoDB Configuration
```
```
default_storage_engine=innodb
```
```
innodb_autoinc_lock_mode=2
```
```
innodb_flush_log_at_trx_commit=0
```
```
innodb_buffer_pool_size=122M
```
```
 
```
```
!includedir /etc/my.cnf.d/
```
```
 
```

[root@controller2 ~]# cat /etc/my.cnf.d/galera.cnf | grep ^[^#].*

```
[mysqld]
```
```
binlog_format=ROW
```
```
default-storage-engine=innodb
```
```
innodb_autoinc_lock_mode=2
```
```
bind-address=0.0.0.0
```
```
wsrep_on=1
```

wsrep_provider=/usr/lib64/galera/libgalera_smm.so

```
wsrep_cluster_name="my_wsrep_cluster"
```

wsrep_cluster_address="gcomm://controller1,controller2,controller3"

```
wsrep_node_name=controller2
```
```
wsrep_node_address=192.168.220.22
```
```
wsrep_slave_threads=1
```
```
wsrep_certify_nonPK=1
```
```
wsrep_max_ws_rows=131072
```
```
wsrep_max_ws_size=1073741824
```
```
wsrep_debug=0
```
```
wsrep_convert_LOCK_to_trx=0
```
```
wsrep_retry_autocommit=1
```
```
wsrep_auto_increment_control=1
```
```
wsrep_drupal_282555_workaround=0
```
```
wsrep_causal_reads=0
```
```
wsrep_notify_cmd=
```
```
wsrep_sst_method=rsync
```
```
wsrep_sst_auth=root:
```
```
 
```
```
[root@controller3 ~]# cat /etc/my.cnf
```
```
[mysqld]
```
```
datadir=/var/lib/mysql
```
```
socket=/var/lib/mysql/mysql.sock
```
```
user=mysql
```
```
binlog_format=ROW
```

bind-address=192.168.220.23 # The management IP address of the controller node

```
 
```
```
# InnoDB Configuration
```
```
default_storage_engine=innodb
```
```
innodb_autoinc_lock_mode=2
```
```
innodb_flush_log_at_trx_commit=0
```
```
innodb_buffer_pool_size=122M
```
```
 
```
```
!includedir /etc/my.cnf.d/
```
```
 
```

[root@controller3 ~]# cat /etc/my.cnf.d/galera.cnf | grep ^[^#].*

```
[mysqld]
```
```
binlog_format=ROW
```
```
default-storage-engine=innodb
```
```
innodb_autoinc_lock_mode=2
```
```
bind-address=0.0.0.0
```
```
wsrep_on=1
```

wsrep_provider=/usr/lib64/galera/libgalera_smm.so

```
wsrep_cluster_name="my_wsrep_cluster"
```

wsrep_cluster_address="gcomm://controller1,controller2,controller3"

```
wsrep_node_name=controller3
```
```
wsrep_node_address=192.168.220.23
```
```
wsrep_slave_threads=1
```
```
wsrep_certify_nonPK=1
```
```
wsrep_max_ws_rows=131072
```
```
wsrep_max_ws_size=1073741824
```
```
wsrep_debug=0
```
```
wsrep_convert_LOCK_to_trx=0
```
```
wsrep_retry_autocommit=1
```
```
wsrep_auto_increment_control=1
```
```
wsrep_drupal_282555_workaround=0
```
```
wsrep_causal_reads=0
```
```
wsrep_notify_cmd=
```
```
wsrep_sst_method=rsync
```
```
wsrep_sst_auth=root:
```

Database Management

Initialize the Primary Component on one cluster node

[root@controller1 ~]# /usr/libexec/mysqld --wsrep-new-cluster --user=root &

Start the database server on all other cluster nodes

[root@controller2 ~]# systemctl start mariadb

[root@controller3 ~]# systemctl start mariadb

Check MaribDB Cluster

The Key Value :

wsrep_connected ON

wsrep_cluster_size 3

wsrep_local_index 0 (controller1 : 0 controller2 : 1 controller3 : 2)

wsrep_incoming_addresses 192.168.220.21:3306,192.168.220.22:3306,192.168.220.23:3306

[root@controller1 ~]# mysql -uroot -proot -e "show status like 'wsrep_%'"

+------------------------------+-------------------------------------------------------------+

| Variable_name                | Value                                                       |

+------------------------------+-------------------------------------------------------------+

| wsrep_apply_oooe             | 0.000000                                                    |

| wsrep_apply_oool             | 0.000000                                                    |

| wsrep_apply_window           | 0.000000                                                    |

| wsrep_causal_reads           | 0                                                           |

| wsrep_cert_deps_distance     | 0.000000                                                    |

| wsrep_cert_index_size        | 0                                                           |

| wsrep_cert_interval          | 0.000000                                                    |

| wsrep_cluster_conf_id        | 3                                                           |

| wsrep_cluster_size           | 3                                                           |

| wsrep_cluster_state_uuid     | 37649504-e137-11e7-815b-1ec404fbf23e                        |

| wsrep_cluster_status         | Primary                                                     |

| wsrep_commit_oooe            | 0.000000                                                    |

| wsrep_commit_oool            | 0.000000                                                    |

| wsrep_commit_window          | 0.000000                                                    |

| wsrep_connected              | ON                                                          |

| wsrep_desync_count           | 0                                                           |

| wsrep_evs_delayed            |                                                             |

| wsrep_evs_evict_list         |                                                             |

| wsrep_evs_repl_latency       | 0/0/0/0/0                                                   |

| wsrep_evs_state              | OPERATIONAL                                                 |

| wsrep_flow_control_paused    | 0.000000                                                    |

| wsrep_flow_control_paused_ns | 0                                                           |

| wsrep_flow_control_recv      | 0                                                           |

| wsrep_flow_control_sent      | 0                                                           |

| wsrep_gcomm_uuid             | 37644373-e137-11e7-8fef-c76eb92175c6                        |

| wsrep_incoming_addresses     | 192.168.220.21:3306,192.168.220.22:3306,192.168.220.23:3306 |

| wsrep_last_committed         | 0                                                           |

| wsrep_local_bf_aborts        | 0                                                           |

| wsrep_local_cached_downto    | 18446744073709551615                                        |

| wsrep_local_cert_failures    | 0                                                           |

| wsrep_local_commits          | 0                                                           |

| wsrep_local_index            | 0                                                           |

| wsrep_local_recv_queue       | 0                                                           |

| wsrep_local_recv_queue_avg   | 0.100000                                                    |

| wsrep_local_recv_queue_max   | 2                                                           |

| wsrep_local_recv_queue_min   | 0                                                           |

| wsrep_local_replays          | 0                                                           |

| wsrep_local_send_queue       | 0                                                           |

| wsrep_local_send_queue_avg   | 0.000000                                                    |

| wsrep_local_send_queue_max   | 1                                                           |

| wsrep_local_send_queue_min   | 0                                                           |

| wsrep_local_state            | 4                                                           |

| wsrep_local_state_comment    | Synced                                                      |

| wsrep_local_state_uuid       | 37649504-e137-11e7-815b-1ec404fbf23e                        |

| wsrep_protocol_version       | 7                                                           |

| wsrep_provider_name          | Galera                                                      |

| wsrep_provider_vendor        | Codership Oy <info@codership.com>                           |

| wsrep_provider_version       | 3.16(r5c765eb)                                              |

| wsrep_ready                  | ON                                                          |

| wsrep_received               | 10                                                          |

| wsrep_received_bytes         | 794                                                         |

| wsrep_repl_data_bytes        | 0                                                           |

| wsrep_repl_keys              | 0                                                           |

| wsrep_repl_keys_bytes        | 0                                                           |

| wsrep_repl_other_bytes       | 0                                                           |

| wsrep_replicated             | 0                                                           |

| wsrep_replicated_bytes       | 0                                                           |

| wsrep_thread_count           | 2                                                           |

+------------------------------+-------------------------------------------------------------+

[root@controller2 ~]# mysql -uroot -proot -e "show status like 'wsrep_%'"

+------------------------------+-------------------------------------------------------------+

| Variable_name                | Value                                                       |

+------------------------------+-------------------------------------------------------------+

| wsrep_apply_oooe             | 0.000000                                                    |

| wsrep_apply_oool             | 0.000000                                                    |

| wsrep_apply_window           | 0.000000                                                    |

| wsrep_causal_reads           | 0                                                           |

| wsrep_cert_deps_distance     | 0.000000                                                    |

| wsrep_cert_index_size        | 0                                                           |

| wsrep_cert_interval          | 0.000000                                                    |

| wsrep_cluster_conf_id        | 3                                                           |

| wsrep_cluster_size           | 3                                                           |

| wsrep_cluster_state_uuid     | 37649504-e137-11e7-815b-1ec404fbf23e                        |

| wsrep_cluster_status         | Primary                                                     |

| wsrep_commit_oooe            | 0.000000                                                    |

| wsrep_commit_oool            | 0.000000                                                    |

| wsrep_commit_window          | 0.000000                                                    |

| wsrep_connected              | ON                                                          |

| wsrep_desync_count           | 0                                                           |

| wsrep_evs_delayed            |                                                             |

| wsrep_evs_evict_list         |                                                             |

| wsrep_evs_repl_latency       | 0.000204825/0.00047307/0.000741314/0.000268244/2            |

| wsrep_evs_state              | OPERATIONAL                                                 |

| wsrep_flow_control_paused    | 0.000000                                                    |

| wsrep_flow_control_paused_ns | 0                                                           |

| wsrep_flow_control_recv      | 0                                                           |

| wsrep_flow_control_sent      | 0                                                           |

| wsrep_gcomm_uuid             | 781401a8-e137-11e7-bba3-9e3f83e88f32                        |

| wsrep_incoming_addresses     | 192.168.220.21:3306,192.168.220.22:3306,192.168.220.23:3306 |

| wsrep_last_committed         | 0                                                           |

| wsrep_local_bf_aborts        | 0                                                           |

| wsrep_local_cached_downto    | 18446744073709551615                                        |

| wsrep_local_cert_failures    | 0                                                           |

| wsrep_local_commits          | 0                                                           |

| wsrep_local_index            | 1                                                           |

| wsrep_local_recv_queue       | 0                                                           |

| wsrep_local_recv_queue_avg   | 0.000000                                                    |

| wsrep_local_recv_queue_max   | 1                                                           |

| wsrep_local_recv_queue_min   | 0                                                           |

| wsrep_local_replays          | 0                                                           |

| wsrep_local_send_queue       | 0                                                           |

| wsrep_local_send_queue_avg   | 0.000000                                                    |

| wsrep_local_send_queue_max   | 1                                                           |

| wsrep_local_send_queue_min   | 0                                                           |

| wsrep_local_state            | 4                                                           |

| wsrep_local_state_comment    | Synced                                                      |

| wsrep_local_state_uuid       | 37649504-e137-11e7-815b-1ec404fbf23e                        |

| wsrep_protocol_version       | 7                                                           |

| wsrep_provider_name          | Galera                                                      |

| wsrep_provider_vendor        | Codership Oy <info@codership.com>                           |

| wsrep_provider_version       | 3.16(r5c765eb)                                              |

| wsrep_ready                  | ON                                                          |

| wsrep_received               | 4                                                           |

| wsrep_received_bytes         | 529                                                         |

| wsrep_repl_data_bytes        | 0                                                           |

| wsrep_repl_keys              | 0                                                           |

| wsrep_repl_keys_bytes        | 0                                                           |

| wsrep_repl_other_bytes       | 0                                                           |

| wsrep_replicated             | 0                                                           |

| wsrep_replicated_bytes       | 0                                                           |

| wsrep_thread_count           | 2                                                           |

+------------------------------+-------------------------------------------------------------+

[root@controller3 ~]# mysql -uroot -proot -e "show status like 'wsrep_%'"

+------------------------------+-------------------------------------------------------------+

| Variable_name                | Value                                                       |

+------------------------------+-------------------------------------------------------------+

| wsrep_apply_oooe             | 0.000000                                                    |

| wsrep_apply_oool             | 0.000000                                                    |

| wsrep_apply_window           | 0.000000                                                    |

| wsrep_causal_reads           | 0                                                           |

| wsrep_cert_deps_distance     | 0.000000                                                    |

| wsrep_cert_index_size        | 0                                                           |

| wsrep_cert_interval          | 0.000000                                                    |

| wsrep_cluster_conf_id        | 3                                                           |

| wsrep_cluster_size           | 3                                                           |

| wsrep_cluster_state_uuid     | 37649504-e137-11e7-815b-1ec404fbf23e                        |

| wsrep_cluster_status         | Primary                                                     |

| wsrep_commit_oooe            | 0.000000                                                    |

| wsrep_commit_oool            | 0.000000                                                    |

| wsrep_commit_window          | 0.000000                                                    |

| wsrep_connected              | ON                                                          |

| wsrep_desync_count           | 0                                                           |

| wsrep_evs_delayed            |                                                             |

| wsrep_evs_evict_list         |                                                             |

| wsrep_evs_repl_latency       | 0.000224679/0.000465885/0.000643407/0.000164076/5           |

| wsrep_evs_state              | OPERATIONAL                                                 |

| wsrep_flow_control_paused    | 0.000000                                                    |

| wsrep_flow_control_paused_ns | 0                                                           |

| wsrep_flow_control_recv      | 0                                                           |

| wsrep_flow_control_sent      | 0                                                           |

| wsrep_gcomm_uuid             | 7ce71d07-e137-11e7-b720-baa5cd647a15                        |

| wsrep_incoming_addresses     | 192.168.220.21:3306,192.168.220.22:3306,192.168.220.23:3306 |

| wsrep_last_committed         | 0                                                           |

| wsrep_local_bf_aborts        | 0                                                           |

| wsrep_local_cached_downto    | 18446744073709551615                                        |

| wsrep_local_cert_failures    | 0                                                           |

| wsrep_local_commits          | 0                                                           |

| wsrep_local_index            | 2                                                           |

| wsrep_local_recv_queue       | 0                                                           |

| wsrep_local_recv_queue_avg   | 0.000000                                                    |

| wsrep_local_recv_queue_max   | 1                                                           |

| wsrep_local_recv_queue_min   | 0                                                           |

| wsrep_local_replays          | 0                                                           |

| wsrep_local_send_queue       | 0                                                           |

| wsrep_local_send_queue_avg   | 0.333333                                                    |

| wsrep_local_send_queue_max   | 2                                                           |

| wsrep_local_send_queue_min   | 0                                                           |

| wsrep_local_state            | 4                                                           |

| wsrep_local_state_comment    | Synced                                                      |

| wsrep_local_state_uuid       | 37649504-e137-11e7-815b-1ec404fbf23e                        |

| wsrep_protocol_version       | 7                                                           |

| wsrep_provider_name          | Galera                                                      |

| wsrep_provider_vendor        | Codership Oy <info@codership.com>                           |

| wsrep_provider_version       | 3.16(r5c765eb)                                              |

| wsrep_ready                  | ON                                                          |

| wsrep_received               | 3                                                           |

| wsrep_received_bytes         | 311                                                         |

| wsrep_repl_data_bytes        | 0                                                           |

| wsrep_repl_keys              | 0                                                           |

| wsrep_repl_keys_bytes        | 0                                                           |

| wsrep_repl_other_bytes       | 0                                                           |

| wsrep_replicated             | 0                                                           |

| wsrep_replicated_bytes       | 0                                                           |

| wsrep_thread_count           | 2                                                           |

+------------------------------+-------------------------------------------------------------+

Test MaribDB Cluster

[root@controller1 ~]# mysql -uroot -proot -e "show databases"

```
+--------------------+
```
```
| Database           |
```
```
+--------------------+
```
```
| information_schema |
```
```
| mysql              |
```
```
| performance_schema |
```
```
+--------------------+
```

[root@controller2 ~]# mysql -uroot -proot -e "show databases"

```
+--------------------+
```
```
| Database           |
```
```
+--------------------+
```
```
| information_schema |
```
```
| mysql              |
```
```
| performance_schema |
```
```
+--------------------+
```

[root@controller3 ~]# mysql -uroot -proot -e "show databases"

```
+--------------------+
```
```
| Database           |
```
```
+--------------------+
```
```
| information_schema |
```
```
| mysql              |
```
```
| performance_schema |
```
```
+--------------------+
```

[root@controller1 ~]# mysql -uroot -proot -e "create database galera_test"

[root@controller1 ~]# mysql -uroot -proot -e "show databases"

```
+--------------------+
```
```
| Database           |
```
```
+--------------------+
```
```
| galera_test        |
```
```
| information_schema |
```
```
| mysql              |
```
```
| performance_schema |
```
```
+--------------------+
```

[root@controller2 ~]# mysql -uroot -proot -e "show databases"

```
+--------------------+
```
```
| Database           |
```
```
+--------------------+
```
```
| galera_test        |
```
```
| information_schema |
```
```
| mysql              |
```
```
| performance_schema |
```
```
+--------------------+
```

[root@controller3 ~]# mysql -uroot -proot -e "show databases"

```
+--------------------+
```
```
| Database           |
```
```
+--------------------+
```
```
| galera_test        |
```
```
| information_schema |
```
```
| mysql              |
```
```
| performance_schema |
```
```
+--------------------+
```

Database Management

```
echo '
```
```
MYSQL_USERNAME="clustercheck_user"
```

MYSQL_PASSWORD="my_clustercheck_password"

```
MYSQL_HOST="localhost"
```
```
MYSQL_PORT="3306"
```
```
' > /etc/sysconfig/clustercheck
```

Grant the clustercheck user

[[email protected] ~]# mysql -uroot -proot -e "GRANT PROCESS ON *.* TO 'clustercheck_user'@'localhost' IDENTIFIED BY 'my_clustercheck_password';FLUSH PRIVILEGES;"

[[email protected] ~]# mysql -uroot -proot -e "SELECT User, Host, Password FROM mysql.user;"

+-------------------+-----------+-------------------------------------------+

| User              | Host      | Password                                  |

+-------------------+-----------+-------------------------------------------+

| root              | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |

| root              | 127.0.0.1 | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |

| root              | ::1       | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |

| clustercheck_user | localhost | *B7ECF4F5C3B1DDB87695D91C5D27631AEC569993 |

+-------------------+-----------+-------------------------------------------+

Create a configuration file for the HAProxy monitor service

```
echo '
```
```
service galera-monitor
```
```
{
```
```
   port = 9200
```
```
   disable = no
```
```
   socket_type = stream
```
```
   protocol = tcp
```
```
   wait = no
```
```
   user = root
```
```
   group = root
```
```
   groups = yes
```
```
   server = /usr/bin/clustercheck
```
```
   type = UNLISTED
```
```
   per_source = UNLIMITED
```
```
   log_on_success =
```
```
   log_on_failure = HOST
```
```
   flags = REUSE
```
```
}
```
```
' > /etc/xinetd.d/galera-monitor
```

Create OpenStack Users & Databases

[root@controller1 ~]# mysql -u root -proot -e "

```
> create database keystone;
```

> grant all privileges on keystone.* to 'keystone'@'localhost' identified by 'keystone';

> grant all privileges on keystone.* to 'keystone'@'%' identified by 'keystone';

```
> create database glance;
```

> grant all privileges on glance.* to 'glance'@'localhost' identified by 'glance';

> grant all privileges on glance.* to 'glance'@'%' identified by 'glance';

```
> 
```
```
> create database nova;
```

> grant all privileges on nova.* to 'nova'@'localhost' identified by 'nova';

> grant all privileges on nova.* to 'nova'@'%' identified by 'nova';

```
> create database nova_api;
```

> grant all privileges on nova_api.* to 'nova'@'localhost' identified by 'nova';

> grant all privileges on nova_api.* to 'nova'@'%' identified by 'nova';

```
> create database nova_cell0;
```

> grant all privileges on nova_cell0.* to 'nova'@'localhost' identified by 'nova';

> grant all privileges on nova_cell0.* to 'nova'@'%' identified by 'nova';

```
> 
```
```
> create database neutron;
```

> grant all privileges on neutron.* to 'neutron'@'localhost' identified by 'neutron';

> grant all privileges on neutron.* to 'neutron'@'%' identified by 'neutron';

```
> 
```
```
> flush privileges;
```
```
> select user,host from mysql.user;
```
```
> show databases;
```
```
> "
```
```
"
```
```
+-------------------+-----------+
```
```
| user              | host      |
```
```
+-------------------+-----------+
```
```
| glance            | %         |
```
```
| keystone          | %         |
```
```
| neutron           | %         |
```
```
| nova              | %         |
```
```
| root              | 127.0.0.1 |
```
```
| root              | ::1       |
```
```
| clustercheck_user | localhost |
```
```
| glance            | localhost |
```
```
| keystone          | localhost |
```
```
| neutron           | localhost |
```
```
| nova              | localhost |
```
```
| root              | localhost |
```
```
+-------------------+-----------+
```
```
+--------------------+
```
```
| Database           |
```
```
+--------------------+
```
```
| galera_test        |
```
```
| glance             |
```
```
| information_schema |
```
```
| keystone           |
```
```
| mysql              |
```
```
| neutron            |
```
```
| nova               |
```
```
| nova_api           |
```
```
| nova_cell0         |
```
```
| performance_schema |
```
```
+--------------------+
```

Start the xinetd daemon for clustercheck

[root@controller1 ~]# systemctl daemon-reload

[root@controller1 ~]# systemctl enable xinetd

[root@controller1 ~]# systemctl start xinetd