I’ve set up a Pacemaker cluster with MariaDB and fence_pve so that we have 1 writeable master and 2 read-only slaves. But writes are not made to either of them directly, instead all queries are routed through ProxySQL and it distributes queries across the three nodes. I first didn’t understand why the developers of ProxySQL advised against a pure read/write split of queries. Surely we don’t want SELECTs to go to the writeable node? It’s busy with INSERTs and UPDATEs. But now I get it; it’s an issue of replication lag. If a client makes an INSERT and then shortly thereafter a SELECT that needs to display the changes that were just made, things can fall apart if the SELECT goes to a slave that hasn’t received the changes from the master yet. So we only want to send queries that we know aren’t millisecond-sensitive to slaves. Well, the replication lag sets the time element… If your slaves are five seconds behind the master then you should only send queries that can tolerate a five second discrepancy to the slaves.
Update 2021-07-30: I realized it was waaaay better to set read-only=1 in the conf files for MariaDB to avoid situations where proxysql accidentally sends INSERT-operations to a non-master because it happens to start out read-write.
Aaaanyway… So installing ProxySQL is pretty much fire-and-forget so the interesting thing is how to feed it the right data. I let each server in the cluster run ProxySQL and whichever node happens to host the master-instance of MariaDB also gets to route the traffic. You could let one of the slaves host the active ProxySQL instance but this was easier for me to implement. Here’s a diagram of how things work:
The dashed lines indicate replication of data and the solid lines represent database queries. The ellipse shows where the virtual IP for the database cluster is located(always on the master node). So with thus setup I can use the exact same config for all three ProxySQL instances, which I’ve turned into a single SQL-file proxyinit.sql:
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.1.181',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.1.182',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.1.183',3306);
-- Since hostgroup 0 is the default it makes sense to make it the writer hostgroup
-- To avoid a situation where INSERTs are accidentally sent to a read-only node
INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) VALUES (0,1,'cluster1');
-- These are two queries I've identified that take more time than usual and are safe to run on replicas
INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply) VALUES (1,1,'0xA1D7DC02F2B414BB',1,1);
INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply) VALUES (2,1,'0x112A8E68C94FFE30',1,1);
-- By default all queries should go to the master so default hostgroup should be 0
INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('zabbix','MYZABBIXPASSWORD',0);
-- Now we have to load our data into the active runtime
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
-- And I don't want to have to enter this data all the time so better save it to disk
SAVE MYSQL USERS TO DISK;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;To load this into each ProxySQL instance I just run:
mysql -u admin -pMYADMINPASSWORD -h 0.0.0.0 -P6032 < proxyinit.sqlTesting
Let’s try to take pcmk2 out of the cluster so that some other node has to take over the role of primary:
[root@pcmk2 ~]# pcs resource
* db_primary_ip (ocf::heartbeat:IPaddr2): Started pcmk2.svealiden.se
* Clone Set: mariadb_server-clone [mariadb_server] (promotable):
* Masters: [ pcmk2.svealiden.se ]
* Slaves: [ pcmk1.svealiden.se pcmk3.svealiden.se ]
[root@pcmk2 ~]# pcs cluster stop
Stopping Cluster (pacemaker)...
Stopping Cluster (corosync)...
[root@pcmk2 ~]# Okey, let’s see who is the new master.
[root@pcmk1 ~]# pcs resource
* db_primary_ip (ocf::heartbeat:IPaddr2): Started pcmk1.svealiden.se
* Clone Set: mariadb_server-clone [mariadb_server] (promotable):
* Masters: [ pcmk1.svealiden.se ]
* Slaves: [ pcmk3.svealiden.se ]
* Stopped: [ pcmk2.svealiden.se ]Okey, pcmk1. Let’s see if we see writes being made from ProxySQL to pcmk1:
[root@pcmk1 ~]# tcpdump -nnX -i lo port 3306 | grep -i insert
dropped privs to tcpdump
tcpdump: verbose output suppressed, use -v[v]… for full protocol decode
listening on lo, link-type EN10MB (Ethernet), snapshot length 262144 bytes
0x0030: 54af e6dc 9500 0000 0369 6e73 6572 7420 T……..insert.
0x0030: 54af e31e 4d01 0000 0369 6e73 6572 7420 T…M….insert.
0x0030: 54af e83e 8302 0000 0369 6e73 6572 7420 T..>…..insert.
0x0030: 54af ee9f 1b01 0000 0369 6e73 6572 7420 T……..insert.
0x0030: 54af f287 5401 0000 0369 6e73 6572 7420 T…T….insert.
0x0030: 54af eac0 ff01 0000 0369 6e73 6572 7420 T……..insert.
0x0030: 54af f3f9 1601 0000 0369 6e73 6572 7420 T……..insert.
0x0030: 54af eabc a900 0000 0369 6e73 6572 7420 T……..insert.
0x0030: 54af f29f c301 0000 0369 6e73 6572 7420 T……..insert.
0x0030: 54af f6b0 a501 0000 0369 6e73 6572 7420 T……..insert.
0x0030: 54af fa9b 0b02 0000 0369 6e73 6572 7420 T……..insert.
Seems about right. We can check that ProxySQL correctly identifies pcmk1 as master now:
ProxySQL Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
+--------------+---------------+--------+
| hostgroup_id | hostname | status |
+--------------+---------------+--------+
| 0 | 192.168.1.181 | ONLINE | <-- Yes, pcmk1 is the only writable node
| 1 | 192.168.1.183 | ONLINE |
| 1 | 192.168.1.182 | ONLINE |
| 1 | 192.168.1.181 | ONLINE |
+--------------+---------------+--------+
4 rows in set (0.001 sec)Let’s see what happens when I bring pcmk2 back up again. It should be a slave.
[root@pcmk1 ~]# pcs resource
* db_primary_ip (ocf::heartbeat:IPaddr2): Started pcmk1.svealiden.se
* Clone Set: mariadb_server-clone [mariadb_server] (promotable):
* mariadb_server (ocf::heartbeat:mariadb): Starting pcmk2.svealiden.se
* Masters: [ pcmk1.svealiden.se ]
* Slaves: [ pcmk3.svealiden.se ]
[root@pcmk1 ~]# pcs resource
* db_primary_ip (ocf::heartbeat:IPaddr2): Started pcmk1.svealiden.se
* Clone Set: mariadb_server-clone [mariadb_server] (promotable):
* Masters: [ pcmk1.svealiden.se ]
* Slaves: [ pcmk2.svealiden.se pcmk3.svealiden.se ]So far so good. Let’s just double-check to see that pcmk2 isn’t out of sync and emitting errors:
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: pcmk1
Master_User: replication_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: pcmk1-bin.000005
Read_Master_Log_Pos: 931920
Relay_Log_File: pcmk2-relay-bin.000002
Relay_Log_Pos: 932262
Relay_Master_Log_File: pcmk1-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 931920
Relay_Log_Space: 932571
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: Current_Pos
Gtid_IO_Pos: 0-1-309263
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: optimistic
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 1512
1 row in set (0.000 sec)All good!
Purpose
I think this is pretty attractive as a way of merging high-availability with load balancing. I started tinkering with this when fiddling with Kubernetes and WordPress. No point in having multiple pods spread out over multiple servers if you can’t run multiple database-instances. Well, not in my book anyway. So we’re left to choose between Galera multi-master or the more traditional master-slave setup. There are constraints placed on what can be run in Galera(like a hard primary key requirement) so I think master-slave is more reliable when you yourself might not have complete control over database schemas. ProxySQL would work just fine with Galera too but I wanted to test out the slightly more challenging setup.
A big downside to ProxySQL is that it doesn’t do pass-through authentication. So user added to MariaDB and any user password changed would also need to be made to all ProxySQL instances. That’s fine when the setup is meant for a large WordPress webshop which has one(1) database user that you as an admin manage but for a shared hosting setup it’s a nightmare.
MariaDB MaxScale is an alternative to ProxySQL that solves the issue of replication lag using some fancy footwork and therefore claims to be able to do read/write split automagically and it offers pass-through authentication. Not sure if it has the fancy statistics function that ProxySQL has though.
Behind the scenes
I had to have data to test out this setup and that’s not trivial. Lots of data is sent to my Galera-cluster from Zabbix though… Hmm… Can’t ProxySQL do data mirroring? Indeed it can! First we need to add both the usual database cluster virtual IP and the new testing-cluster’s virtual IP as separate hostgroups:
ProxySQL Admin> SELECT hostgroup_id,hostname,port,status FROM mysql_servers;
+--------------+---------------+------+--------+
| hostgroup_id | hostname | port | status |
+--------------+---------------+------+--------+
| 0 | 192.168.1.62 | 3306 | ONLINE |
| 1 | 192.168.1.180 | 6033 | ONLINE |
+--------------+---------------+------+--------+
2 rows in set (0.000 sec)And then a query rule that sends all traffic for the zabbix-user to both hostgroups(not load-balanced, every query goes to both):
ProxySQL Admin> SELECT rule_id,active,username,destination_hostgroup,mirror_hostgroup,apply FROM mysql_query_rules;
+---------+--------+----------+-----------------------+------------------+-------+
| rule_id | active | username | destination_hostgroup | mirror_hostgroup | apply |
+---------+--------+----------+-----------------------+------------------+-------+
| 1 | 1 | zabbix | 0 | 1 | 1 |
+---------+--------+----------+-----------------------+------------------+-------+
1 row in set (0.000 sec)