Skip to content

Adding a MySQL replica to a cPanel account for read scaling without confusing the account map

cPanel does not natively manage replicas — the dbmap covers one MySQL instance. Adding a read replica means wiring it up below cPanel's visibility, and routing reads in the application.

A cPanel customer’s WooCommerce store hits MySQL hard on every product page — the catalog query alone runs five times per request. The right answer is a read replica: a second MySQL instance that takes a continuous binlog stream from the primary and serves SELECTs from local storage. The wrong assumption is that cPanel will manage this for you. cPanel’s MySQL integration is single-instance — the dbmap, the Remote MySQL UI, the backup scheduler all assume there’s one database server. Replicas live outside cPanel’s awareness and need to be wired in below it.

What’s happening

A MySQL/MariaDB primary writes a binary log of every change. A replica connects to the primary, copies the binlog, and applies the changes in order. Reads on the replica can be lagged by milliseconds to seconds depending on workload, but for read-heavy workloads like a product catalog or a search results page, the staleness is tolerable.

cPanel’s UI shows the database hostname as localhost by default. Applications hardcode localhost in their config. To use a replica, the application has to know that “read connections go to replica.internal:3306, write connections stay on localhost.” cPanel doesn’t expose a configuration knob for this — it’s an application-side concern.

The primary needs its binlog enabled, a replication user created, and server-id set distinctly from the replica. The replica needs a snapshot of the primary’s data as its starting point, then CHANGE MASTER TO (or CHANGE REPLICATION SOURCE TO on newer versions) to start streaming.

How to verify

On the primary:

mysql -e "SHOW VARIABLES LIKE 'log_bin'"
mysql -e "SHOW VARIABLES LIKE 'server_id'"
mysql -e "SHOW MASTER STATUS"
mysql -e "SELECT User, Host FROM mysql.user WHERE User='repl'"

On the replica (once configured):

mysql -e "SHOW REPLICA STATUS\G" | grep -E "Replica_IO_Running|Replica_SQL_Running|Seconds_Behind|Last_Error"
mysql -e "SELECT @@server_id"

Seconds_Behind_Master (or Seconds_Behind_Source) of 0–5 is normal under load. Anything climbing means the replica can’t keep up. Both Running flags should be Yes.

The fix

  1. Enable binlog on the primary:
    /etc/my.cnf.d/cpanel.cnf:
    [mysqld]
    log_bin = mysql-bin
    binlog_format = ROW
    server_id = 1
    gtid_strict_mode = ON
    Restart MySQL. The binlog will start writing to /var/lib/mysql/mysql-bin.*. Plan disk space — the binlog can grow fast.
  2. Create the replication user:
    mysql -e "CREATE USER 'repl'@'10.0.0.%' IDENTIFIED BY 'strong-password'"
    mysql -e "GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%'"
    10.0.0.% should be the replica’s subnet, not the public internet. Replication traffic should be on a private network or TLS-wrapped.
  3. Take a consistent snapshot of the primary to seed the replica:
    mysqldump --all-databases --single-transaction --master-data=2 --triggers --routines --events | gzip > primary-snapshot.sql.gz
    --master-data=2 embeds the binlog position in the dump as a comment — you need this to start replication at the right point.
  4. Set up the replica server with MariaDB matching the primary’s major version. Different server_id:
    /etc/my.cnf.d/cpanel.cnf:
    [mysqld]
    server_id = 2
    read_only = ON
    relay_log = relay-bin
  5. Load the snapshot on the replica, then point it at the primary:
    zcat primary-snapshot.sql.gz | mysql
    grep "MASTER_LOG_FILE" primary-snapshot.sql  # extract the position
    mysql -e "CHANGE MASTER TO MASTER_HOST='primary.internal', MASTER_USER='repl', MASTER_PASSWORD='strong-password', MASTER_LOG_FILE='mysql-bin.000042', MASTER_LOG_POS=12345"
    mysql -e "START SLAVE"
  6. Wire the application to use the replica for reads. For WordPress, the HyperDB plugin or a connection-routing plugin reads from a configured replica. For custom apps, change the database connection layer to split reads (SELECT) from writes (INSERT, UPDATE, DELETE). The application is the one that has to know about the replica — cPanel does not.
  7. Monitor lag continuously: SHOW REPLICA STATUS\G from a scheduled check. Alert if Seconds_Behind_Master > 30. If it climbs persistently, the replica is undersized or the workload has a serialization bottleneck on the SQL thread — switch to a multi-threaded replica (slave_parallel_workers = 4).

For environments where read replicas are part of the architecture, Stack Harbor’s environment management team operates the primary-replica pair below cPanel, including failover playbooks if the replica needs to be promoted.