Database Replication Configuration for 1C-Bitrix

Database Replication Configuration for 1C-Bitrix

MySQL replication for 1C-Bitrix solves three problems: read scaling (SELECT queries on replicas), backup without load on the master (dump from a replica), and fast failover when the master goes down. Without replication, the database is a single point of failure for the entire project.

Configuring the Master

/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

# For GTID replication (recommended)
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON

# Data safety
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

binlog_format = ROW — row-based replication. More reliable than STATEMENT for 1C-Bitrix, which contains non-deterministic functions in queries (NOW(), RAND()).

binlog_row_image = MINIMAL — only modified columns are written to the binlog, not the entire row. Reduces binlog volume by 60–80% for wide 1C-Bitrix tables.

Create the replication user:

CREATE USER 'replicator'@'10.0.0.%' IDENTIFIED BY 'strong_password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'10.0.0.%';
FLUSH PRIVILEGES;

Configuring the Replica

/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  # for replication chains
log_slave_updates = ON

gtid_mode = ON
enforce_gtid_consistency = ON

# Replica is read-only
read_only = ON
super_read_only = ON

# Parallel replication (MySQL 5.7+)
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 4

super_read_only = ON — prevents writes even by users with SUPER privileges. Prevents accidental direct writes to the replica, which would break replication.

Initializing replication with GTID:

-- Take a dump of the master
mysqldump --single-transaction --master-data=2 --gtid \
    -u root -p bitrix > /tmp/bitrix_dump.sql

-- Restore on the replica and start replication
mysql -u root -p bitrix < /tmp/bitrix_dump.sql

-- Configure the replication source
CHANGE MASTER TO
    MASTER_HOST = '10.0.0.10',
    MASTER_USER = 'replicator',
    MASTER_PASSWORD = 'strong_password',
    MASTER_AUTO_POSITION = 1;  -- GTID: position is determined automatically

START SLAVE;
SHOW SLAVE STATUS\G

In SHOW SLAVE STATUS, check:

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes
• Seconds_Behind_Master: 0

Connecting 1C-Bitrix to the Replica

1C-Bitrix does not have built-in automatic read/write split. Configure it via the cluster module or manually:

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

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

Replication Monitoring

-- Replication lag
SHOW SLAVE STATUS\G
-- Seconds_Behind_Master: lag in seconds

-- Replication errors
SELECT * FROM performance_schema.replication_applier_status_by_worker;

When Seconds_Behind_Master > 30, users may see stale data from the replica. Causes: heavy transactions on the master, insufficient parallel workers on the replica, I/O bottleneck.

# Continuous lag monitoring
watch -n5 'mysql -u monitor -ppass -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep "Seconds_Behind"'

Failover on Master Failure

Manual failover: promote the replica to master, redirect 1C-Bitrix.

-- On the replica
STOP SLAVE;
RESET SLAVE ALL;
SET GLOBAL read_only = OFF;
SET GLOBAL super_read_only = OFF;

Update the host in .settings.php to the new master node's IP. Automated failover via Orchestrator or ProxySQL is recommended for critical projects with zero downtime tolerance.