MariaDB TX 2.0 Cluster in Azure - Usage and Administration
App Access to MaxScale
Create a VM in the same virtual network as your Cluster
You'll have better performance and security if you create a VM in the same virtual network (vnet) as your cluster and run your application on that VM.
Deploy a new VM in Azure and select the same Resource group that you chose when you deployed the cluster. You'll also need to choose the same Virtual network that is used for the cluster.
After the new VM is deployed, you will be able to configure your application to connect to port 4006, 4007, or 4008 of IP address 10.0.1.10.
App running outside Azure
This is not a recommended configuration.
If you chose "Public" Load Balancer Visibility, you can connect to MaxScale services from any host on the Internet. This configuration has serious security implications, as any host on the Internet can try to connect to your cluster, your traffic between the application and cluster will not be encrypted (unless you take extra steps to enable SSL), and network performance between the application and database will be poor.
Nevertheless, this configuration can be useful for Proof of Concept (PoC) or testing deployments.
You can use any MariaDB or MySQL client to connect to port 4006, 4007, or 4008 of the public IP address configured for the cluster or to the public DNS name for the cluster.
MariaDB MaxScale in this solution exposes 3 services: "RW Split Router", "Write Connection Router, and "Read Connection Router".
RW Split Router (port 4006)
The RW Split Router is an instance of the readwritesplit service, which intelligently directs writes to a single node in the cluster and balances reads across all nodes in the cluster. It's important when using Galera to avoid writing to the same objects on multiple nodes simultaneously; doing so can lead to certification conflicts that are reported to the application as deadlocks. For most applications, the readwritesplit router solves this problem by parsing queries and identifying whether they can safely be routed to a read-only node.
The RW Split Router is most useful when using autocommit. Applications that open transactions implicitly by (disabling autocommit) or explicitly (by executing
START TRANSACTION or
BEGIN) will not be able to make use of readwritesplit's intelligent query routing functionality, since all open transactions must be routed to the "Master" node for safety and to avoid conflicts.
Write Connection Router (port 4007) and Read Connection Router (port 4008)
The Write Connection Router and Read Connection Router are two instances of the readconnroute service. These are useful for applications that already separate read and write traffic.
The Write Connection Router will send all queries to the "Master" in the cluster. The "Master" is chosen automatically by MaxScale. Galera doesn't have its own concept of "Master", but it's important to write to a single table on only one node at a time, and it's helpful to automatically elect a new "Master" if the old one becomes unavailable for some reason.
The Read Connection Router dynamically balances load across all nodes in the cluster that are "synced". If a node leaves the cluster or becomes desynced, MaxScale will automatically stop routing queries to that node until it is available again.
- If the application needs full write access to the datatabase, it might need to only connect to Write Connection Router on port 4007.
- If an application needs only read access to the database, it should connect only to Read Connection Router on port 4008.
- If different parts of the application need write and read access, it might make sense for the application to either open two connections, one to each of the Write Connection Router and Read Connection Router, or it might make sense for the application to choose intelligently which router to connect to, depending on the workload.
Administering the cluster
You can use SSH to connect directly to the MaxScale nodes from any host on the Internet. Because there are 2 MaxScale nodes, and both are accessed through the same public-facing hostname, non-standard ports are used for SSH. To SSH to the max1 node, use port 2201; to SSH to the max2 node, use port 2202.
To connect to the backend data/Galera nodes you must first "hop" through the MaxScale node. To make this easier, you can use an SSH agent on your own machine and forward the agent through the connection between your client and the MaxScale node.
To connect to the Cluster as the MariaDB root user, you must use SSH to connect to one of the backend nodes. The OS root user on each of the backend nodes can connect to MariaDB as the MariaDB root user (by way of the unix_socket authentication plugin).
To log in as root, then, you must first connect to the MaxScale node and then from there connect to the "db1" backend node. For example:
kolbe@retsina [12:57:45] ~ $ ssh -A -p 2201 email@example.com Warning: Permanently added '[mdbectest.westus.cloudapp.azure.com]:2201,[184.108.40.206]:2201' (ECDSA) to the list of known hosts. [mdbe@mdbec-max1 ~]$ ssh mdbec-db1 Warning: Permanently added 'mdbec-db1,10.0.1.4' (ECDSA) to the list of known hosts. [mdbe@mdbec-db1 ~]$ sudo mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 21 Server version: 10.2.6-MariaDB Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> select current_user(); +----------------+ | current_user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) MariaDB [(none)]> show status like 'wsrep_cluster_size'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 3 | +--------------------+-------+ 1 row in set (0.00 sec) MariaDB [(none)]> Bye [mdbe@mdbec-db1 ~]$ logout Connection to mdbec-db1 closed. [mdbe@mdbec-max1 ~]$ logout Connection to mdbectest.westus.cloudapp.azure.com closed.
You can decide if you want to allow MariaDB root access from other hosts, such as the MaxScale host. You'll manage that yourself by creating new MariaDB users using the command line client. You should not expose MariaDB root access to the Internet at large.
Authentication for the
maxadmin client is handled based on the OS username of the user executing the client program. By default, only "root" is allowed to connect. Thus, to connect to the MaxScale administration interface, execute
sudo maxadmin from a shell prompt when logged into the MaxScale node:
$ ssh -A -p 2201 firstname.lastname@example.org [mdbe@mdbec-max1 ~]$ sudo maxadmin MaxScale> show users Administration interface users: Users table data Hashtable: 0x7fe850000cc0, size 52 No. of entries: 1 Average chain length: 0.0 Longest chain length: 1 User names: admin MaxScale> quit