Scaling MySQL with TCP Load Balancing and Galera Cluster

dashboard.conf

server {
    listen 8080;
    location /api { # Enable JSON status API
        write=on;
    }

    location = /dashboard.html {
        root /usr/share/nginx/html;
    }

    # Redirect requests made to the old dashboard
    location = /status.html {
        return 301 /dashboard.html;
    }

    #deny all;             # Protect from remote access in production
    #allow 192.168.0.0/16; # Allow access from private networks only
}

# vim: syntax=nginx

my.cnf

[mysqld]
user = mysql
bind-address = 0.0.0.0
wsrep_provider = /usr/lib/galera/libgalera_smm.so
wsrep_sst_method = rsync
default_storage_engine = innodb
binlog_format = row
innodb_autoinc_lock_mode = 2
innodb_flush_log_at_trx_commit = 0
query_cache_size = 0
query_cache_type = 0

nginx.conf

stream {
    include stream.conf;
}

sql_method.js

var method = "-"; // Global variable
var client_messages = 0;

function getSqlMethod(s) {
    s.on('upload', function (data, flags) {
        client_messages++;
        if ( client_messages == 3 ) { // SQL query appears in 3rd client packet
            var query_text = data.substr(1,10).toUpperCase();
            var methods = ["SELECT", "UPDATE", "INSERT", "SHOW", "CREATE", "DROP"];
            var i = 0;
            for (; i < methods.length; i++ ) {
                if ( query_text.search(methods[i]) > 0 ) {
                    s.log("SQL method: " + methods[i]); // To error_log [info]
                    method = methods[i];
                    s.allow(); // Stop searching
                }
            }
        }
        s.allow();
    });
}

function setSqlMethod() {
    return method;
}

stream.conf

log_format mysql '$remote_addr [$time_local] $protocol $status $bytes_received '
                 '$bytes_sent $upstream_addr $upstream_connect_time '
                 '$upstream_first_byte_time $upstream_session_time $session_time';

upstream galera_cluster {
    server 127.0.0.1:33061; #node1
    server 127.0.0.1:33062; #node2
    server 127.0.0.1:33063; #node3
    zone tcp_mem 64k;
    least_conn;
}

match mysql_handshake {
    send \x00;
    expect ~* \x00\x00; # NullNull "filler" in handshake response packet
}

server {
    listen 3306; # MySQL default
    proxy_pass galera_cluster;
    proxy_timeout 2s;
    health_check match=mysql_handshake interval=20 fails=1 passes=2;
    # ...
    access_log /var/log/nginx/galera_access.log mysql;
    status_zone galera_cluster;
}

# vim: syntax=nginx

stream_njs.conf

log_format mysql '$remote_addr [$time_local] $protocol $status $bytes_received '
                 '$bytes_sent $upstream_addr $upstream_connect_time '
                 '$upstream_first_byte_time $upstream_session_time $session_time'
		 '$sql_method'; # Set by NGINX JavaScript;

upstream galera_cluster {
    server 127.0.0.1:33061; #node1
    server 127.0.0.1:33062; #node2
    server 127.0.0.1:33063; #node3
    zone tcp_mem 64k;
    least_conn;
}

match mysql_handshake {
    send \x00;
    expect ~* \x00\x00; # NullNull "filler" in handshake response packet
}

js_import  /etc/nginx/sql_method.js;
js_set     $sql_method sql_method.setSqlMethod;

server {
    listen 3306; # MySQL default
    proxy_pass galera_cluster;
    proxy_timeout 2s;
    health_check match=mysql_handshake interval=20 fails=1 passes=2;
    # ...
    js_filter  sql_method.getSqlMethod;
    error_log  /var/log/nginx/galera_error.log info; #For NGINX JavaScript s.log() calls
    access_log /var/log/nginx/galera_access.log mysql;
}

# vim: syntax=nginx

For a discussion of these files, see Scaling MySQL with TCP Load Balancing and Galera Cluster