
=====
Usage
=====

Galera Cluster for MySQL is a true Multimaster Cluster based on synchronous
replication. Galera Cluster is an easy-to-use, high-availability solution,
which provides high system uptime, no data loss and scalability for future
growth.

Sample pillars
==============

Galera cluster master node

.. code-block:: yaml

    galera:
      version:
        mysql: 5.6
        galera: 3
      master:
        enabled: true
        name: openstack
        bind:
          address: 192.168.0.1
          port: 3306
        members:
        - host: 192.168.0.1
          port: 4567
        - host: 192.168.0.2
          port: 4567
        admin:
          user: root
          password: pass
        sst:
          user: sstuser
          password: sstpassword
        database:
          name:
            encoding: 'utf8'
            users:
            - name: 'username'
              password: 'password'
              host: 'localhost'
              rights: 'all privileges'
              database: '*.*'

Galera cluster slave node

.. code-block:: yaml

    galera:
      slave:
        enabled: true
        name: openstack
        bind:
          address: 192.168.0.2
          port: 3306
        members:
        - host: 192.168.0.1
          port: 4567
        - host: 192.168.0.2
          port: 4567
        admin:
          user: root
          password: pass
        sst:
          user: sstuser
          password: sstpassword


Enable TLS support:

.. code-block:: yaml

    galera:
       slave or master:
         ssl:
          enabled: True
          ciphers:
            DHE-RSA-AES128-SHA:
              enabled: True
            DHE-RSA-AES256-SHA:
              enabled: True
            EDH-RSA-DES-CBC3-SHA:
              name: EDH-RSA-DES-CBC3-SHA
              enabled: True
            AES128-SHA:AES256-SHA:
              name: AES128-SHA:AES256-SHA
              enabled: True
            DES-CBC3-SHA:
              enabled: True
          # path
          cert_file: /etc/mysql/ssl/cert.pem
          key_file: /etc/mysql/ssl/key.pem
          ca_file: /etc/mysql/ssl/ca.pem

          # content (not required if files already exists)
          key: << body of key >>
          cert: << body of cert >>
          cacert_chain: << body of ca certs chain >>

Custom wsrep_provider_options:

.. code-block:: yaml

   galera:
     slave or master:
       wsrep_provider_options:
         evs.inactive_check_period: "PT2.5S"
         evs.inactive_timeout: "PT30S"
         gmcast.peer_timeout: "PT9S"

Additional mysql users:

.. code-block:: yaml

    mysql:
      server:
        users:
          - name: clustercheck
            password: clustercheck
            database: '*.*'
            grants: PROCESS
          - name: inspector
            host: 127.0.0.1
            password: password
            databases:
              mydb:
                - database: mydb
                - table: mytable
                - grant_option: True
                - grants:
                  - all privileges

Additional mysql SSL grants:

.. code-block:: yaml

    mysql:
      server:
        users:
          - name: clustercheck
            password: clustercheck
            database: '*.*'
            grants: PROCESS
            ssl_option:
              - SSL: True
              - X509: True
              - SUBJECT: <subject>
              - ISSUER: <issuer>
              - CIPHER: <cipher>

Additional check params:
========================

.. code-block:: yaml

    galera:
      clustercheck:
        - enabled: True
        - user: clustercheck
        - password: clustercheck
        - available_when_donor: 0
        - available_when_readonly: 1
        - port 9200

Configurable soft parameters
============================

- ``galera_innodb_buffer_pool_size``
   Default is ``3138M``
- ``galera_max_connections``
   Default is ``20000``
- ``galera_innodb_read_io_threads``
   Default is ``8``
- ``galera_innodb_write_io_threads``
   Default is ``8``
- ``galera_wsrep_slave_threads``
   Default is ``8``
- ``galera_xtrabackup_parallel``
   Default is 4
- ``galera_error_log_enabled``
   Default is ``true``
- ``galera_error_log_path``
   Default is ``/var/log/mysql/error.log``
- ``galera_innodb_io_capacity``
   Default is ``500``
- ``galera_innodb_io_capacity_max``
   Default is ``2000``
- ``galera_innodb_flush_neighbors``
   Default is ``1``

When the following parameters are set to 0, theirs
defaults will be calclulated automatically based on number
of cpu cores:

 - galera_innodb_read_io_threads
 - galera_innodb_write_io_threads
 - galera_wsrep_slave_threads

Usage:

.. code-block:: yaml

    _param:
      galera_innodb_buffer_pool_size: 1024M
      galera_max_connections: 200
      galera_innodb_read_io_threads: 16
      galera_innodb_write_io_threads: 16
      galera_wsrep_slave_threads: 8
      galera_xtrabackup_parallel: 2
      galera_error_log_enabled: true
      galera_error_log_path: /var/log/mysql/error.log
      galera_innodb_io_capacity: 500
      galera_innodb_io_capacity_max: 2000
      galera_innodb_flush_neighbors: 1

Usage
=====

MySQL Galera check sripts

.. code-block:: bash

    mysql> SHOW STATUS LIKE 'wsrep%';

    mysql> SHOW STATUS LIKE 'wsrep_cluster_size' ;"

Galera monitoring command, performed from extra server

.. code-block:: bash

    garbd -a gcomm://ipaddrofone:4567 -g my_wsrep_cluster -l /tmp/1.out -d

#. salt-call state.sls mysql
#. Comment everything starting wsrep* (wsrep_provider, wsrep_cluster, wsrep_sst)
#. service mysql start
#. run on each node mysql_secure_install and filling root password.

   .. code-block:: bash

    Enter current password for root (enter for none):
    OK, successfully used password, moving on...

    Setting the root password ensures that nobody can log into the MySQL
    root user without the proper authorisation.

    Set root password? [Y/n] y
    New password:
    Re-enter new password:
    Password updated successfully!
    Reloading privilege tables..
     ... Success!

    By default, a MySQL installation has an anonymous user, allowing anyone
    to log into MySQL without having to have a user account created for
    them.  This is intended only for testing, and to make the installation
    go a bit smoother.  You should remove them before moving into a
    production environment.

    Remove anonymous users? [Y/n] y
     ... Success!

    Normally, root should only be allowed to connect from 'localhost'.  This
    ensures that someone cannot guess at the root password from the network.

    Disallow root login remotely? [Y/n] n
     ... skipping.

    By default, MySQL comes with a database named 'test' that anyone can
    access.  This is also intended only for testing, and should be removed
    before moving into a production environment.

    Remove test database and access to it? [Y/n] y
     - Dropping test database...
     ... Success!
     - Removing privileges on test database...
     ... Success!

    Reloading the privilege tables will ensure that all changes made so far
    will take effect immediately.

    Reload privilege tables now? [Y/n] y
     ... Success!

    Cleaning up...

#. service mysql stop
#. uncomment all wsrep* lines except first server, where leave only in
   my.cnf wsrep_cluster_address='gcomm://';
#. start first node
#. Start third node which is connected to first one
#. Start second node which is connected to third one
#. After starting cluster, it must be change cluster address at first starting node
   without restart database and change config my.cnf.

   .. code-block:: bash

      mysql> SET GLOBAL wsrep_cluster_address='gcomm://10.0.0.2';

Read more
=========

* https://github.com/CaptTofu/ansible-galera
* http://www.sebastien-han.fr/blog/2012/04/15/active-passive-failover-cluster-on-a-mysql-galera-cluster-with-haproxy-lsb-agent/
* http://opentodo.net/2012/12/mysql-multi-master-replication-with-galera/
* http://www.codership.com/wiki/doku.php
* http://www.sebastien-han.fr/blog/2012/04/01/mysql-multi-master-replication-with-galera/