Two-node MariaDB-galera-cluster

  • Written by
    Alex Boonstra
  • Published on

Caveats

  • arbiter - 10.10.10.1
  • node1 - 10.10.10.10
  • node1 - 10.10.10.11

Installing the nodes

update /etc/hosts to make sure all the machines are resolvable, perform this on all 3 hosts (2 active mysql nodes and the machine the arbiter resides on). Update the hostnames and addresses accordingly.


$ cat  << EOF >>  /etc/hosts
10.10.10.1    arbiter
10.10.10.10   node1
10.10.10.11   node2
EOF

Setup APT to use the MariaDB repositories


$ apt-get install python-software-properties
$ apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db
$ cat > /etc/apt/sources.list.d/mariadb.list <<-EOF
deb http://mirror.1000mbps.com/mariadb/mariadb-5.5.34/repo/ubuntu precise main
deb-src http://mirror.1000mbps.com/mariadb/mariadb-5.5.34/repo/ubuntu precise main
EOF

Perform apt-get update on all machines

$ apt-get update

Installing mariadb-galera-cluster on the mysql nodes

On both the mysql nodes install mariadb-galera-server and galera. On the the machine thats going to be arbiter only galera is neccesary.

$ apt-get install mariadb-galera-server galera

note: When performing these steps Ubuntu had newer version of certain packages, this generates installation errors. Get the mariadb versions and install them specificly

apt-cache show mysql-common | grep Version
apt-cache show libmysqlclient18 | grep Version

apt-get install mariadb-galera-server galera mariadb-client-5.5 libmysqlclient18=<version> mysql-common=<version>

When asked for a root password you can use the same password (The password from the instance we bootstrap from will be used).

Verify that mysql is started succesfully on both machines by performing ping (for example).

$ mysqladmin --defaults-file=/etc/mysql/debian.cnf ping
mysqld is alive

All good, now shut them down!

$ service mysql stop

If all is well move on the the next step.

Installing the Galera Arbitrator

On the machine thats going to be arbiter we install galera from the mariadb repositories we added earlier.

$ apt-get install galera

Test that garbd is availlable, perform the command and check if gives a FATAL error (which is normall because we didnt set any options)

$ garbd
FATAL: Group address not specified: 89 (Destination address required)
    at garb/garb_config.cpp:Config():94

All good! movin on…

Configuring the mysql hosts

Below is an example config for the my.cnf you can use. The values you might want to take a good look at are innodb_buffer_pool_size and innodb_log_file_size.

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

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

[mysqld]
basedir = /usr
bind-address = 0.0.0.0
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

[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

Next to the normall config we also need a wsrep config, make sure node1 uses gcomm://node2 and the other way around. Also change the name to something usefull that identifies your cluster.

[mysqld]
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://node1?pc.wait_prim=no"
wsrep_sst_method=rsync
wsrep_cluster_name="usefull_name"

Bootstrappig and joining the cluster

Now its time to initalize the cluster. For this we need one instance that will bootstrap the cluster. We use mysqld command for this with an extra option. Perform this option on any of the 2 mysql nodes.

$ mysqld --wsrep_cluster_address=gcomm://

The terminal will be filled with startup output (normally this goes to syslog). Once this is done, go ahead and start mysql on the other node.

$ service mysql start

This time all the output will go to syslog, verify that everything went well. On the bootstrapping node you should see new output that a machine has joined the cluster. Members should be on 2/2 (joined/total)

...
members    = 2/2 (joined/total),
...

Now, for us to test if all is well we need the contents of the debian.cnf file from the bootstrapping node. Perform an scp or copy the contents to the newly added node’s debian.cnf

$ scp root@node1:/etc/mysql/debian.cnf /etc/mysql/debian.cnf

Verify that mysql is working on the newly added node

$ mysql --defaults-file=/etc/mysql/debian.cnf

You should be presented with a nice mariadb cli

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]>

Also check the wsrep options

MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_%';
+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid     | random-generated-uuid                |
| wsrep_protocol_version     | 4                                    |
| wsrep_last_committed       | 5                                    |
| wsrep_replicated           | 1                                    |
| wsrep_replicated_bytes     | 401                                  |
| wsrep_received             | 7                                    |
| wsrep_received_bytes       | 1111                                 |
| wsrep_local_commits        | 0                                    |
| wsrep_local_cert_failures  | 0                                    |
| wsrep_local_bf_aborts      | 0                                    |
| wsrep_local_replays        | 0                                    |
| wsrep_local_send_queue     | 0                                    |
| wsrep_local_send_queue_avg | 0.250000                             |
| wsrep_local_recv_queue     | 0                                    |
| wsrep_local_recv_queue_avg | 0.000000                             |
| wsrep_flow_control_paused  | 0.000000                             |
| wsrep_flow_control_sent    | 0                                    |
| wsrep_flow_control_recv    | 0                                    |
| wsrep_cert_deps_distance   | 1.000000                             |
| wsrep_apply_oooe           | 0.000000                             |
| wsrep_apply_oool           | 0.000000                             |
| wsrep_apply_window         | 1.000000                             |
| wsrep_commit_oooe          | 0.000000                             |
| wsrep_commit_oool          | 0.000000                             |
| wsrep_commit_window        | 1.000000                             |
| wsrep_local_state          | 4                                    |
| wsrep_local_state_comment  | Synced                               |
| wsrep_cert_index_size      | 2                                    |
| wsrep_causal_reads         | 0                                    |
| wsrep_incoming_addresses   | ,10.10.10.11:3306,10.10.10.10:3306   |
| wsrep_cluster_conf_id      | 29                                   |
| wsrep_cluster_size         | 2                                    |
| wsrep_cluster_state_uuid   | random-generated-uuid                |
| wsrep_cluster_status       | Primary                              |
| wsrep_connected            | ON                                   |
| wsrep_local_index          | 2                                    |
| wsrep_provider_name        | Galera                               |
| wsrep_provider_vendor      | Codership Oy                         |
| wsrep_provider_version     | 23.2.4(r147)                         |
| wsrep_ready                | ON                                   |
+----------------------------+--------------------------------------+
40 rows in set (0.03 sec)

MariaDB [(none)]>

All good, lets add the arbiter to the mix

On the arbiter node perform the following command (make sure to use the correct name)

garbd -a gcomm://10.10.10.10,10.10.10.11?pc.wait_prim=no -g usefull_name

Keep an eye on the syslog from the recently joined mysql node and the bootstrapping node. Total members should jump to 3/3

...
members    = 3/3 (joined/total),
...

If all is well, stop the garbd process and edit the garb defaults file


$ cat > /etc/default/garb <<-EOF

# Copyright (C) 2012 Coedership Oy
# This config file is to be sourced by garb service script.

# A space-separated list of node addresses (address[:port]) in the cluster
GALERA_NODES="10.10.10.10:4567 10.10.10.11:4567"

# Galera cluster name, should be the same as on the rest of the nodes.
GALERA_GROUP="usefull_name"

# Optional Galera internal options string (e.g. SSL settings)
# see http://www.codership.com/wiki/doku.php?id=galera_parameters
GALERA_OPTIONS="pc.wait_prim=no"

# Log file for garbd. Optional, by default logs to syslog
LOG_FILE="/var/log/garbd.log"
EOF

Now you can start garbd with

$ service garbd start

Thats it! Now stop mysqld on the bootstrapping node, also make sure that the rsync daemon gets stopped, otherwise stop that aswell.

$ service mysql stop
$ ps aux | grep mysql | grep -v grep

If the ps aux returns nothing we are good to go, start mysql the old-fasion way.

$ service mysql start

In the syslog from the other node (or the log from the arbiter) you should see this node “rejoining”.

verify that mysql us working by performing the same mysql-commands you did earlier on the other node. Troubleshooting

When mysql wont start, take a look at the following:

  • Check syslog for something obvious
  • Make sure the mysqld.sock is not there (/var/run/mysqld/mysqld.sock), otherwise remove it
  • Make sure the rsync daemon is stopped, mysql will start it. If rsync daemon is running, stop it

When your entire cluster goes down, you have to bootstrap it again by performing the same steps in “Bootstrapping and joining the cluster”

Edit: October 09 2013 When mysql crashes, sometimes rsync keeps running which prevents the node to be started/re-added to the cluster. Adding the following to the /etc/init.d/mysql startup file can solve this:


rsync_checks() {
    rsync_pid=$(ps aux | grep "rsync --daemon --port 4444" | grep -v grep | awk '{print $2}')
    if [ ! -z $rsync_pid ]; then
        log_warning_msg "rsync daemon is still running, probably because mysql was not shutdown nicely, killing $rsync_pid ..."
        kill -9 $rsync_pid
        while kill -0 $rsync_pid; do echo '.'; done
        log_warning_msg "rsync daemon killed succesfully"
    fi
}

...
case "${1:-''}" in
  'start')
    sanity_checks;
    rsync_checks; # Add this function call
....


Back to overview Newer post: virtualenv / pil pillow mess Older post: webserver ssl configuration cheatsheet