MySQL Replication for Bitrix: Speed and Fault Tolerance

At peak load — a sale or ad campaign launch — the Bitrix database cannot handle SELECT queries. Catalog page takes 10 seconds to load, managers cannot load the order list. The solution is MySQL replication: master handles writes, replicas serve reads. Without replication, the database is a single point of failure. We configure fault-tolerant replication with a guaranteed result. Our experience: over 5 years and 50 successful projects. Infrastructure savings after implementation reach 30% by reducing master load. Average support cost drops by 40%. According to our data, on projects with 10k+ unique visitors per day, MySQL response time during peak loads increases 3-5 times. Replication reduces catalog page load time by 40-60% and completely eliminates downtime during backups. Contact us for a preliminary assessment of your project.

Problems That Replication Solves

• Read scaling: SELECT queries for catalog, search, and listings are executed on replicas. Master load drops by 70-90%.
• Backup without load: mysqldump on a replica does not block the master, even with terabyte-sized databases.
• Failover: On master failure, a replica becomes master in minutes. Downtime is no more than 5 minutes with manual switchover.

Configuring Replication for Bitrix

Master Configuration

/etc/mysql/conf.d/master.cnf:

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
binlog_row_image = MINIMAL
expire_logs_days = 7
max_binlog_size = 100M

gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON

sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

binlog_format = ROW — row-based replication. More reliable than STATEMENT for Bitrix, where non-deterministic functions (NOW(), RAND()) occur.

binlog_row_image = MINIMAL — only changed columns are written to binlog. Reduces binlog volume by 60-80% for wide Bitrix tables.

Create the replication user: GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'10.0.0.%' IDENTIFIED BY 'strong_password'.

Replica Configuration

/etc/mysql/conf.d/replica.cnf:

[mysqld]
server-id = 2
relay_log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
log_slave_updates = ON

gtid_mode = ON
enforce_gtid_consistency = ON

read_only = ON
super_read_only = ON

slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 4

super_read_only = ON — prevents writes even by users with SUPER privilege. Prevents accidental writes that would break replication.

Initialize replication with GTID: mysqldump --single-transaction --master-data=2 --gtid, then load the dump onto the replica. Start replication:

CHANGE MASTER TO
    MASTER_HOST = '10.0.0.10',
    MASTER_USER = 'replicator',
    MASTER_PASSWORD = 'strong_password',
    MASTER_AUTO_POSITION = 1;

START SLAVE;
SHOW SLAVE STATUS\G

Check Slave_IO_Running: Yes, Slave_SQL_Running: Yes, Seconds_Behind_Master: 0.

Connecting Bitrix to the Replica

Configuration via the cluster module or manually in /bitrix/.settings.php:

'connections' => [
    'value' => [
        'default' => [
            'className' => '\Bitrix\Main\DB\MysqlConnection',
            'host'      => '10.0.0.10',
            'database'  => 'bitrix',
            'login'     => 'bitrix',
            'password'  => 'pass',
        ],
        'replica' => [
            'className' => '\Bitrix\Main\DB\MysqlConnection',
            'host'      => '10.0.0.11',
            'database'  => 'bitrix',
            'login'     => 'bitrix_ro',
            'password'  => 'pass_ro',
            'options'   => ['slave' => true],
        ],
    ],
],

Catalog, search, and listing components send SELECT to the replica. Cart, orders, and authentication always go to the master.

How to Choose Replication Type?

Comparison of asynchronous, semi-synchronous, and synchronous replication:

For 95% of Bitrix projects, asynchronous replication with GTID is sufficient. Semi-synchronous is used when loss of any transaction is critical (e.g., online stores with payments).

When Is Automatic Failover Needed?

If site downtime is critical (more than 5 minutes unacceptable), configure automatic failover via Orchestrator or ProxySQL. These tools monitor master and replica health, automatically promote a replica on failure, and redirect traffic. Recovery time drops to seconds. Order failover setup together with replication.

Monitoring Replication

Check status with SHOW SLAVE STATUS\G. Key parameter: Seconds_Behind_Master. If lag exceeds 30 seconds, data may be stale. Causes: heavy transactions, insufficient parallel workers, I/O bottleneck.

Step-by-Step Replication Setup Guide

1. Configure master (server-id, binlog, GTID).
2. Configure replica (server-id, relay log, read_only, parallel replication).
3. Create replication user with REPLICATION SLAVE privileges.
4. Dump master consistently with GTID position.
5. Load dump onto replica.
6. Start replication with CHANGE MASTER and START SLAVE.
7. Verify status (Slave_IO_Running, Slave_SQL_Running, Seconds_Behind_Master).
8. Configure Bitrix replica connection.
9. Set up monitoring and, if needed, automatic failover.
10. Perform load testing.

Performing Failover on Master Failure

Manual failover: promote replica to master.

STOP SLAVE;
RESET SLAVE ALL;
SET GLOBAL read_only = OFF;
SET GLOBAL super_read_only = OFF;

Change host in .settings.php to the new master node IP.

GTID (global transaction identifiers) simplify switching — no need to find binlog position. More details in MySQL documentation and Wikipedia: GTID.

Our Work Process for Replication Setup

What’s Included in the Work

• Configuration of master.cnf and replica.cnf tailored to your hardware.
• Creation of replication users with limited privileges.
• Data transfer from master to replica with zero downtime.
• Configuration of Bitrix connection (cluster module or .settings.php).
• Monitoring (lag, errors, status).
• Documentation: topology diagram, failover instructions, monitoring scripts.
• Training: how to check status and actions on errors.
• Support: 2 weeks post-release.

Timeline: 1 to 3 days depending on complexity. Pricing is determined after analysis — contact us for a free assessment within one business day. Get a consultation on replication setup and fault-tolerant Bitrix.

1C-Bitrix Clustering

Imagine: a flash sale, 10,000 users simultaneously on the site, the server goes down with a 502 error, carts disappear, managers call support. We have seen this dozens of times. The solution is clustering: load balancing between servers, database replication, and automatic failover. Order an audit of your current infrastructure — in 2 days we will determine if and what kind of cluster is needed. Our experience: 40+ high-load projects on Bitrix.

Why is 1C-Bitrix clustering critical for fault tolerance?

80-90% of requests in a typical project are SELECT. Catalog, product pages, filters — all reads. Master-slave replication routes SELECTs to slave servers, leaving the master for writes only. The 'Web Cluster' module (Business edition and higher) routes requests automatically.

Common stumbling blocks: on master binlog_format = ROW. STATEMENT-based replication with NOW() or UUID() causes inconsistencies — leading to a week of debugging. Unique server-id, binary log enabled. On slave — read_only = ON, relay-log. Initialization via xtrabackup (not mysqldump, which locks tables for half an hour on a 20 GB database).

Metric #1 — Seconds_Behind_Master. If a slave lags by 5+ seconds, a customer places an order, returns to their personal account — and the order is missing (SELECT went to a lagging slave). The module allows manual exclusion of critical queries from slave routing.

Failover: Orchestrator or ProxySQL promote a slave to master in 15-30 seconds. The module supports up to 9 slave connections with configurable weights. Integrity check — pt-table-checksum from Percona Toolkit. Savings from inefficient infrastructure can be up to 40% of the budget, representing a significant annual amount for projects with 50,000+ unique visitors. For more information on replication, refer to MySQL Replication Documentation and Wikipedia: Database Replication.

When is clustering necessary?

Not every project needs it. Specific markers:

• 50,000-100,000 unique visitors per day — a single server starts returning 502 errors during peak hours
• Peak spikes of 5-10 times (sales, flash sales) — load grows in minutes, vertical scaling is not enough
• SLA 99.9% (no more than 8.7 hours of downtime per year) — unattainable with a single server
• Geographic distribution of users

Sometimes composite caching, SQL optimization, and vertical scaling are sufficient. We will honestly tell you if a cluster is not yet needed. Investments in clustering typically pay off within 3-6 months under peak loads. The average project budget is determined individually.

What does the cluster architecture consist of?

Load balancer. HAProxy, nginx upstream, or cloud LB. Round-robin for even distribution, ip-hash for session stickiness, least connections for adaptive balancing. Health checks remove dead servers from the pool. SSL termination on the balancer offloads web nodes.

Web servers. Identical nginx + php-fpm, each with a full copy of the code. Sessions in Redis/Memcached, not on disk (otherwise users lose their cart when switching servers). In the cloud — auto-scaling: load increases — servers are added, load decreases — they are removed.

Cache. Redis Cluster with data sharding across nodes. Redis Sentinel for small clusters. Memcached is fast but lacks persistence. Configuration in .settings.php — servers, weights, sharding strategy.

File storage. Uploads, images — accessible from each node. NFS for 2-3 servers, but it is a single point of failure. GlusterFS — distributed file system without single point of failure. S3 (MinIO, AWS, Yandex Object Storage) — offload static files to object storage, the Bitrix module works out of the box.

How to ensure failover at each cluster level?

The cluster is 5 times more reliable than a single server — if any node fails, the service continues to operate.

What are common clustering setup mistakes?

• Sessions on files — when a server goes down, users lose cart and authentication.
• Unmonitored Seconds_Behind_Master — sales suffer and SLA is unmet.
• Single point of failure at the file storage level (NFS without replication).
• Lack of replication monitoring — data inconsistencies go undetected.

We include checks for all these points in our audit and testing.

What is the clustering process?

1. Load audit — load profile, bottlenecks, load testing. We find the ceiling of a single server.
2. Design — components tailored to requirements and budget. Not everyone needs GlusterFS — sometimes NFS and backups suffice.
3. Infrastructure — servers, network, firewalls. Ansible for automation — any node can be recreated in minutes.
4. Migration — transfer with minimal downtime. Components are connected sequentially, each step verified.
5. Testing — simulation of peak conditions. We crash the master, disconnect a web server, kill Redis — see how the system behaves.
6. Documentation — architecture diagram, runbook, disaster recovery plans.

What does clustering work include?

What are the typical timelines?

Contact us to get an engineer consultation and a preliminary project estimate within 2 days. We will calculate the cost based on your specific needs. Order an audit to find out the exact architecture and budget.