Connecting Your Application to a ProxySQL Entry Point: Read/Write Splitting Without Code Changes

Developers are told that moving to a database cluster means teaching the application about replicas: separate read and write connections, replication-lag awareness, retry logic. Sometimes it does. For a Laravel developer at an events company we will call Saltmarsh Tickets, moving to the MySQL/MariaDB/Percona Cluster package on MassiveGRID PaaS, built on Virtuozzo Application Platform (formerly Jelastic), meant changing one line, because the cluster's two ProxySQL nodes do the splitting. This post covers that line and the handful of things around it that took real thought.

The one line

After the cluster installs, the platform emails two sets of details: the phpMyAdmin panel on the primary node, and the entry point for applications, a hostname of the form proxy.<environment>.<platform domain> with a username and password. The Laravel application's .env went from the old single host to the proxy hostname.

DB_CONNECTION=mysql
DB_HOST=proxy.saltmarsh-db.<platform domain>
DB_PORT=3306
DB_DATABASE=tickets
DB_USERNAME=app
DB_PASSWORD=********

ProxySQL inspects each statement and routes writes to a primary and reads to the replicas (or to any node, in a Galera cluster), so Laravel's ordinary DB:: calls and Eloquent models are already split. Laravel does support separate read and write hosts in its configuration, and the developer left that unused: one hostname, one pool, no application knowledge of the topology.

Thing one: creating additional users

Saltmarsh runs a reporting tool with its own read-only database user. Creating that user on the primary in the usual way was not enough, because ProxySQL authenticates clients itself before opening a backend connection. The user has to exist in ProxySQL's mysql_users table too, on each of the two ProxySQL nodes.

MYSQL_PWD=admin mysql -h 127.0.0.1 -P6032 -uadmin -e \
  "INSERT INTO mysql_users (username,password,default_hostgroup) VALUES ('reports','********',2);"
MYSQL_PWD=admin mysql -h 127.0.0.1 -P6032 -uadmin -e \
  "LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;"

The developer ran this from each ProxySQL node's web SSH in the dashboard. The default hostgroup determines where a user's queries go when no routing rule matches, so a reporting user can be pointed at the replicas by default, which is a useful side effect: heavy reports never touch the writer.

Thing two: the ORM's assumptions about reading its own writes

With asynchronous replication (the primary-secondary scheme), a read that follows a write by a few milliseconds can land on a replica that has not applied the write yet. Laravel's default behaviour of sending everything through one connection masks this on a single server. Behind ProxySQL two things help. ProxySQL can be configured to send reads that occur inside a transaction to the writer, and Laravel wraps the create-then-redirect pattern in a transaction when you ask it to. The developer's rule of thumb became: wrap write-then-read sequences in DB::transaction(), and treat everything else as eventually consistent, which for ticket listings it always was.

Had Saltmarsh chosen the Galera scheme, this concern would largely vanish because every node holds the same committed data, at the cost of a small increase in commit latency. The topology post covers that trade-off.

Thing three: encrypting the connection

The payment processor's compliance questionnaire asked whether database connections were encrypted. On the old single server the answer had been "internal network". The MySQL SSL/TLS add-on generates a certificate authority plus server and client certificates for the database layer, enables and requires TLS on every node, and provides the client bundle. The developer added the CA to the Laravel connection options.

'options' => [
    PDO::MYSQL_ATTR_SSL_CA => '/var/www/certs/ca.pem',
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
],

Connections that do not present TLS are refused, which turned the questionnaire answer into "enforced".

Thing four: reaching the database from a laptop

Developers occasionally need a GUI client against the cluster, and the cluster has no public IP by design. The platform's endpoints feature maps a container port through the shared load balancer to a public port on a generated hostname, without attaching a public IP. The developer created an endpoint for port 3306 on the ProxySQL layer, and the dashboard produced an access URL and port that a desktop client connects to over the enforced TLS. The container firewall's inbound rules were tightened so the endpoint accepts connections only from the office's address range.

For the production application, no endpoint is needed: the Laravel environment and the database environment are both on the platform, so the application connects over the internal network, which is free and never leaves MassiveGRID's infrastructure.

What the developer did not have to do

• Configure replication, replication users or GTIDs. The package did.
• Write failover logic. ProxySQL excludes an unavailable or slow node and re-adds it on recovery; in a Galera cluster no promotion is needed at all.
• Reconfigure the application when the cluster scaled. A node added by horizontal scaling is discovered and added to ProxySQL automatically, so the entry point never changes.
• Tune InnoDB. The platform's Smart Auto-Configuration sizes the buffer pool to each node's cloudlet allocation, including when vertical scaling grows a node during the Saturday-night on-sale.

The migration itself was a dump from the old server, an import through the primary's admin panel, and the one-line change. The developer's retrospective note read: "The cluster is someone else's problem in the good sense. My problem is still the queries."

Frequently Asked Questions

Does ProxySQL add latency?

A small amount, typically well under a millisecond on the platform's internal network, because ProxySQL is an in-memory proxy designed for this purpose. In exchange it pools connections, which usually reduces total connection overhead for PHP applications that open a connection per request.

Can I connect directly to a database node instead of the proxy?

Yes, each node has its own hostname, and the primary's phpMyAdmin panel is provided for administration. Applications should use the ProxySQL entry point so that failover and node scaling are transparent to them.

What about frameworks other than Laravel?

The same applies to Django, Rails, Spring, WordPress and anything else with a single database host setting: point it at the ProxySQL hostname. Frameworks with built-in read/write splitting can leave that feature off, because the proxy performs it.