Last Modified: 28.11.2022
Reserve database connection and setup for removing load from the main database
You can connect additional database servers to reduce the load on the main database. In this case, data is read from auxiliary (slave) databases, and data is written to the main database.
Connecting and configuring extra (slave) databases is done at the Slave database page (Settings > Web Cluster > Group #1 > Replication).
When using several web servers, you must indicate connection address in the file
in the connections section. Parameters for connection with the database and other data sources.
to the main database (master). It's preferable to indicate a direct IP address or ensure that each web server is connected with the main database server specifically if querying without IP address. Using the address type
is prohibited with such configuration.
For replication you need to use different servers with an established quick communication channel between them.
Inside the Web Cluster
module you can indicate slave database delay for data received from the master database, after which it will be automatically disconnected to reduce the risk of data unsynchronization.
replication requires for the records of the main and reserve master/slave-servers to have, in addition to standard, the following privileges: SUPER
, REPLICATION CLIENT
, REPLICATION SLAVE
There are three account entries, but for sake of convenient administration, it's recommended to merge them into a single entry.
- Account entry for the application to handle the database for all cluster nodes (database is defined in dbconn.php).
GRANT SELECT,LOCK TABLES,CREATE TEMPORARY TABLES,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX, REPLICATION CLIENT, RELOAD ON bitrixcluster.* TO 'bitrixcluster'@'%' IDENTIFIED BY '123456';
REPLICATION CLIENT - displays the master statistics.
RELOAD- employed in the master database for
"FLUSH TABLES WITH READ LOCK" when transferring the tables to slave database.
The system will attempt to launch slave streams under the same account entry (specifically login/password) when adding slave database via administrative interface (
CHANGE MASTER TO #login/password for account entry in master-database#), that's why it also requires the following privileges: REPLICATION SLAVE.
- Account entry for managing slave nodes from web cluster admin interface. It's created in each slave database and indicated when connecting slave database to web cluster admin interface.
GRANT REPLICATION CLIENT on *.* TO 'bitrixcluster_slave'@'%' IDENTIFIED BY '123456'; - shows statistics for slave in admin interface.
GRANT SUPER on *.* TO 'bitrixcluster_slave'@'%'; - shows start/stop for slave and replication parameter updates from admin interface of web cluster.
GRANT SELECT,LOCK TABLES,CREATE TEMPORARY TABLES,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX ON bitrixcluster.* TO 'bitrixcluster_slave'@'%' IDENTIFIED BY '123456'; - needed for initial data export from the master database to a slave database.
- Account entry for uploading bin logs from master DB to slave DB (to organize the replication process). This entry is created at the master DB for each slave DB connection (a single account entry can be used for all slaves).
GRANT REPLICATION SLAVE on *.* TO 'bitrixcluster_replica'@'%';
- Click the button Add Slave Database. It opens the window with wizard for adding new slave database.
- The first window shows
recommendations for system setup
, critical for operation. All observations must be rectified until the check verifies that all is green-lighted and proceed to complete all wizard steps.
After wizard is finished, the database will appear in the list, but will be inactive.
You can click the drop down action menu Use database item in the list of slave databases and proceed with the wizard recommendations.
Attention! Replication is initiated from copying of database content. While being copied, the website's public section will be unavailable during data transfer. and the admin section will be available.
Any unregistered data modifications during the copying process may affect the web-site correct operation.
After successfully connecting a DB slave server, you can view its
Database parameter setup
If required, edit the database connection settings. Double click on the target base in the list or use the Edit menu item. The system will show the Slave Database Parameters form with basic config, such as Name, Description and For Backup option.
The value in the field Load, % (0..100) is indicated in each database individually. This option re-distributes the majority of load to faster and more powerful servers within cluster.
Primary database has a separate option that allows to
Master database querying specifics
If data is written to the master database on a hit, the subsequent read operations (queries) will be performed at the master database onwards, not at slave DB.
This behaviour is associated with cluster architecture specifics: this is the only way to ensure that the requested data is up to date, due to synchronization with slaves always having a consistent delay (slaves may not contain the data at all).
Note for developers: user-triggered write queries in the system (for example, within web analytics) are framed with special methods of CDatabase: StartUsingMasterOnly and StopUsingMasterOnly. Such methods, accordingly, start and finish the use of the master DB only.
You can wrap your API calls for writing with such constructions, however you must be sure that this database data won't be read in the further code.