Database replication
# Database Replication for MediaWiki
Database replication is a critical component of maintaining a highly available and scalable MediaWiki installation. This article will cover the concepts, benefits, and configuration aspects of setting up database replication for your MediaWiki site. This guide assumes a basic understanding of server administration and MySQL/MariaDB databases.
== What is Database Replication?
Database replication involves creating and maintaining copies of your primary database (the master) on one or more secondary servers (slaves). Changes made to the master database are automatically propagated to the slaves. This provides several benefits:
- **Increased Read Performance:** Read operations can be distributed across multiple slave servers, reducing the load on the master.
- **High Availability:** If the master server fails, a slave can be promoted to become the new master, minimizing downtime.
- **Data Redundancy:** Replication provides a backup of your data in case of data corruption or disaster.
- **Reporting and Analytics:** Slaves can be used for running reports and analytics without impacting the performance of the live wiki.
- **Asynchronous Replication:** The master server doesn't wait for the slaves to apply changes before continuing. This provides the best performance but may result in some data loss in the event of a master failure. This is the recommended setup for most MediaWiki installations.
- **Semi-Synchronous Replication:** The master waits for at least one slave to acknowledge receipt of the changes before proceeding. This provides a stronger guarantee of data consistency but can impact performance.
- **Synchronous Replication:** The master waits for all slaves to apply changes before proceeding. This offers the highest level of data consistency but has the greatest performance overhead and is generally not practical for MediaWiki.
- **Two or more servers:** One will be the master, and the others will be slaves.
- **MySQL/MariaDB:** Both servers must be running the same version of MySQL or MariaDB. MediaWiki 1.40 supports a range of versions; consult the Manual:Configuration for details.
- **Network Connectivity:** The master and slaves must be able to communicate with each other over the network.
- **Consistent Data:** The initial data on the master and slaves must be identical. This is typically achieved using a database dump and restore.
- **Replication Errors:** Check the MySQL error logs on both the master and slaves for any errors.
- **Data Inconsistency:** If you suspect data inconsistency, compare the data on the master and slaves.
- **Slow Replication:** Investigate network latency and disk I/O performance on both the master and slaves.
- Manual:Configuration
- Manual:Load balancer
- Manual:Database
- Manual:Maintenance
- Extension:ReplicationLag – An extension to help monitor replication lag.
- Help:Database errors
- Telegram: @powervps Servers at a discounted price
== Types of Replication
There are several types of replication available, but the most common for MediaWiki is **asynchronous replication**.
== Prerequisites
Before setting up replication, ensure you have the following:
== Setting up Replication: A Step-by-Step Guide
This guide outlines the steps for setting up asynchronous replication. Remember to replace placeholders with your actual server and database details.
Step 1: Configure the Master Server
1. **Edit the MySQL Configuration File (my.cnf):** Locate the `my.cnf` file (usually in `/etc/mysql/my.cnf` or `/etc/my.cnf`). Add or modify the following settings within the `[mysqld]` section:
| Setting | Value |
|---|---|
| `server-id` | A unique integer, e.g., `1` |
| `log_bin` | `/var/log/mysql/mysql-bin.log` (or a suitable path) |
| `binlog_format` | `ROW` (recommended for MediaWiki) |
| `expire_logs_days` | `7` (adjust as needed) |
| `binlog_do_db` | `wikidb` (replace `wikidb` with your MediaWiki database name) |
2. **Restart MySQL:** `sudo systemctl restart mysql` (or the equivalent command for your system).
3. **Create a Replication User:** Connect to the MySQL server as root and execute the following SQL command:
```sql CREATE USER 'repl'@'%' IDENTIFIED BY 'your_replication_password'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; FLUSH PRIVILEGES; ```
Replace `your_replication_password` with a strong password.
4. **Lock Tables and Get Binary Log Coordinates:**
```sql FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; ```
Record the `File` and `Position` values from the output of `SHOW MASTER STATUS`. You'll need these for configuring the slaves. Keep the tables locked until you have completed the database dump.
Step 2: Configure the Slave Server(s)
1. **Edit the MySQL Configuration File (my.cnf):** Locate the `my.cnf` file on each slave server. Add or modify the following settings within the `[mysqld]` section:
| Setting | Value |
|---|---|
| `server-id` | A unique integer, different from the master, e.g., `2`, `3`, etc. |
| `relay-log` | `/var/log/mysql/mysql-relay-bin.log` (or a suitable path) |
| `relay_log_recovery` | `ON` |
2. **Restart MySQL:** `sudo systemctl restart mysql` (or the equivalent command for your system).
3. **Restore the Database Dump:** Restore the database dump taken from the master server to each slave server.
4. **Configure Replication:** Connect to the MySQL server on each slave and execute the following SQL command:
```sql CHANGE MASTER TO MASTER_HOST='your_master_ip_or_hostname', MASTER_USER='repl', MASTER_PASSWORD='your_replication_password', MASTER_LOG_FILE='the_file_from_show_master_status', MASTER_LOG_POS=the_position_from_show_master_status; ```
Replace the placeholders with the appropriate values.
5. **Start Replication:** Execute the following SQL command on each slave:
```sql START SLAVE; ```
6. **Check Replication Status:** Execute the following SQL command on each slave to verify that replication is working:
```sql SHOW SLAVE STATUS\G ```
Look for `Slave_IO_Running: Yes` and `Slave_SQL_Running: Yes`. Also, examine `Seconds_Behind_Master` – ideally, this should be close to 0.
Step 3: Unlock Tables on the Master
Once you've configured the slaves and verified that replication is running, unlock the tables on the master server:
```sql UNLOCK TABLES; ```
== Monitoring Replication
It's important to monitor replication regularly to ensure it's functioning correctly. You can use the `SHOW SLAVE STATUS` command on each slave to check the replication status. Consider setting up monitoring tools to alert you to any issues. Manual:Maintenance provides more information on monitoring.
== Configuring MediaWiki to Use Replication
Once replication is set up, you need to configure MediaWiki to use the slave databases for read operations. This is done by modifying the `$wgReplicaLag` and `$wgLoadBalancer` settings in your `LocalSettings.php` file. Refer to Manual:Load balancer for detailed instructions.
== Troubleshooting
== Related Pages
Intel-Based Server Configurations
| Configuration | Specifications | Benchmark |
|---|---|---|
| Core i7-6700K/7700 Server | 64 GB DDR4, NVMe SSD 2 x 512 GB | CPU Benchmark: 8046 |
| Core i7-8700 Server | 64 GB DDR4, NVMe SSD 2x1 TB | CPU Benchmark: 13124 |
| Core i9-9900K Server | 128 GB DDR4, NVMe SSD 2 x 1 TB | CPU Benchmark: 49969 |
| Core i9-13900 Server (64GB) | 64 GB RAM, 2x2 TB NVMe SSD | |
| Core i9-13900 Server (128GB) | 128 GB RAM, 2x2 TB NVMe SSD | |
| Core i5-13500 Server (64GB) | 64 GB RAM, 2x500 GB NVMe SSD | |
| Core i5-13500 Server (128GB) | 128 GB RAM, 2x500 GB NVMe SSD | |
| Core i5-13500 Workstation | 64 GB DDR5 RAM, 2 NVMe SSD, NVIDIA RTX 4000 |
AMD-Based Server Configurations
| Configuration | Specifications | Benchmark |
|---|---|---|
| Ryzen 5 3600 Server | 64 GB RAM, 2x480 GB NVMe | CPU Benchmark: 17849 |
| Ryzen 7 7700 Server | 64 GB DDR5 RAM, 2x1 TB NVMe | CPU Benchmark: 35224 |
| Ryzen 9 5950X Server | 128 GB RAM, 2x4 TB NVMe | CPU Benchmark: 46045 |
| Ryzen 9 7950X Server | 128 GB DDR5 ECC, 2x2 TB NVMe | CPU Benchmark: 63561 |
| EPYC 7502P Server (128GB/1TB) | 128 GB RAM, 1 TB NVMe | CPU Benchmark: 48021 |
| EPYC 7502P Server (128GB/2TB) | 128 GB RAM, 2 TB NVMe | CPU Benchmark: 48021 |
| EPYC 7502P Server (128GB/4TB) | 128 GB RAM, 2x2 TB NVMe | CPU Benchmark: 48021 |
| EPYC 7502P Server (256GB/1TB) | 256 GB RAM, 1 TB NVMe | CPU Benchmark: 48021 |
| EPYC 7502P Server (256GB/4TB) | 256 GB RAM, 2x2 TB NVMe | CPU Benchmark: 48021 |
| EPYC 9454P Server | 256 GB RAM, 2x2 TB NVMe |
Order Your Dedicated Server
Configure and order your ideal server configurationNeed Assistance?
⚠️ *Note: All benchmark scores are approximate and may vary based on configuration. Server availability subject to stock.* ⚠️