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.