Galera / WSREP – MySQL cluster on Ubuntu 12.04 LTS – basic initial setup

mysql-logoIt took me about 4 hours of actual research, and trial and error, however I finally got a working Galera based WSREP MySQL cluster up and running. Here’s what it took and how the pieces came together:

Getting the install right took a couple of hours by itself for some reason, here’s the order that worked best on Ubuntu 12.04 LTS:

  1. Execute the following to install the necessary support libraries:
    user@localhost:~$ sudo apt-get install libaio1 libssl0.9.8
  2. Install the Galera WSREP provider for MySQL (available here):
    user@localhost:~$ sudo dpkg -i galera-23.2.2-amd64.deb
  3. Install mysql-client next:
    user@localhost:~$ sudo apt-get install mysql-client
  4. Now we should have everything we need to install the custom wsrep patched MySQL package:
    user@localhost:~$ sudo dpkg -i mysql-server-wsrep-5.5.28-23.7-amd64.deb

NOTE: The minimum recommended number of nodes is 3. This is the base set of software I had to install on each node in order to get the software working.

NOTE: Be sure to modify the bind_address=127.0.0.1 setting in /etc/mysql/my.cnf to ensure MySQL binds to the proper IP address on your system, and $ sudo /etc/init.d/mysql restart before proceeding.

(Hopefully final) NOTE: In order to create an initial cluster, you need to execute the following:
$ /usr/sbin/mysqld --wsrep_cluster_address=gcomm://
This will create the initial cluster based on /etc/mysql/conf.d/wsrep.cnf. Then you can use standard init scripts.

Now onto configuration:

NOTE: The README-wsrep file (Available on the current wsrep patched MySQL download page here) was a great resource in initial setup.

  1. Connect to each MySQL instance and remove all users without a user name:
    user@localhost:~$ mysql -u root -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    ...
    mysql> SET wsrep_on=OFF; DELETE FROM mysql.user WHERE user='';
    Query OK, 1 rows affected (0.00 sec)

    mysql>
  2. Still connected to the mysql server, add the user that will be used for global cluster variable read / write:
    mysql> SET wsrep_on=OFF; GRANT ALL ON *.* TO wsrep_sst@'%' IDENTIFIED BY 'wspass';
    Query OK, 1 rows affected (0.00 sec)

    mysql>
  3. /etc/mysql/conf.d/wsrep.cnf
    The three crucial items required to get the software working are 3 settings in /etc/mysql/conf.d/wsrep.cnf:

    wsrep_provider
    wsrep_cluster_address
    wsrep_sst_auth

    • wsrep_provider is /usr/lib/galera/libgalera_smm.so (The galera library).
      wsrep_provider=/usr/lib/galera/libgalera_smm.so
    • wsrep_cluster_address is a URL with some basic rules:
      1. If this is the first node in a cluster (Perhaps after a full cluster shutdown) the URL should not contain any nodes in the cluster: wsrep_cluster_address="gcomm://"
      2. If this is an additional node in an existing cluster, the node names should be comma separated after gcomm:// as such: wsrep_cluster_address="gcomm://galera-1,galera-2"
        excluding the node whose configuration file you are modifying.
    • wsrep_sst_auth is the user you added in configuration step 2 (If you were unaware you were adding a user to mysql in configuration step 2, glance back at it now and you should be able to find the user name and password) which should be:
      wsrep_sst_auth=wsrep_sst:wspass

Now you should be all set, restart MySQL and check for any errors in /var/log/syslog. If there are no errors, login to MySQL as root and execute the following to see server stats:

mysql> SHOW STATUS LIKE 'wsrep%';
+----------------------------+----------------------------------------------------+
| Variable_name              | Value                                              |
+----------------------------+----------------------------------------------------+
| wsrep_local_state_uuid     | 12345678-9abc-defg-hijk-lmnopqrstuvw               |
| wsrep_protocol_version     | 4                                                  |
| wsrep_last_committed       | 0                                                  |
| wsrep_replicated           | 0                                                  |
| wsrep_replicated_bytes     | 0                                                  |
| wsrep_received             | 3                                                  |
| wsrep_received_bytes       | 269                                                |
| 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.333333                                           |
| 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   | 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_incoming_addresses   | 192.168.0.1:3306,192.168.0.2:3306,192.168.0.3:3306 |
| wsrep_cluster_conf_id      | 7                                                  |
| wsrep_cluster_size         | 3                                                  |
| wsrep_cluster_state_uuid   | 12345678-9abc-defg-hijk-lmnopqrstuvw               |
| wsrep_cluster_status       | Primary                                            |
| wsrep_connected            | ON                                                 |
| wsrep_local_index          | 2                                                  |
| wsrep_provider_name        | Galera                                             |
| wsrep_provider_vendor      | Codership Oy <info@codership.com>                  |
| wsrep_provider_version     | 23.2.2(r137)                                       |
| wsrep_ready                | ON                                                 |
+----------------------------+----------------------------------------------------+
40 rows in set (0.00 sec)

mysql>

Now let the multi-master InnoDB replication madness begin! MUAHAHAHA!!!

One thought on “Galera / WSREP – MySQL cluster on Ubuntu 12.04 LTS – basic initial setup

  1. Suresh Shrestha

    Dear Sir,
    I am Suresh Shrestha, master’s student at Muroran Institute of Technology Hokkaido, Japan. I am doing research on Galera Cluster and have done same configuration with the Ubuntu OS 12.04 LTS. But it couldn’t work. I am trying to deploy galera cluster in an unstable network environment. It will be very helpful if sir could provide some detail configuration files in order to continue the research. Thank you in advance.

Leave a Reply

Your email address will not be published. Required fields are marked *