Database Administration
# Database Administration
This article provides a comprehensive overview of database administration for a MediaWiki 1.40 installation. Effective database management is crucial for the performance, stability, and scalability of your wiki. This guide is aimed at newcomers to server administration and assumes a basic understanding of command-line interfaces.
Supported Databases
MediaWiki 1.40 officially supports the following database management systems (DBMS):
- MySQL (MariaDB is also fully supported and often preferred)
- PostgreSQL
- SQLite (primarily for small, single-user wikis; *not recommended for production environments*)
- `SELECT`
- `INSERT`
- `UPDATE`
- `DELETE`
- `CREATE`
- `ALTER`
- `INDEX`
- `DROP`
- **Backups:** Regularly back up your database. Use tools like `mysqldump` (for MySQL/MariaDB) or `pg_dump` (for PostgreSQL). Automate this process whenever possible. See Database Backups for more information.
- **Optimization:** Periodically optimize the database tables. For MySQL/MariaDB, use `OPTIMIZE TABLE`. For PostgreSQL, use `VACUUM ANALYZE`.
- **Repair:** If you suspect database corruption, use the appropriate repair tools for your database system.
- **Archiving:** Consider archiving old revisions of pages to reduce database size. See Archiving for details.
- **Indexing:** Ensure appropriate indexes are created on frequently queried columns. MediaWiki automatically creates some indexes, but you may need to add more based on your wiki's usage patterns. See Database Indexing for guidance.
- **Caching:** Leverage database caching mechanisms to reduce load. Configure the database server's cache settings appropriately.
- **Hardware:** Ensure your database server has sufficient resources (CPU, memory, disk I/O) to handle the wiki's workload. See Server Requirements for details.
- **Query Optimization:** Analyze slow queries and optimize them for better performance. Use database profiling tools to identify bottlenecks.
- **`LocalSettings.php`:** Verify that the database configuration settings are correct.
- **Database Server:** Ensure the database server is running and accessible from the web server.
- **Firewall:** Check that no firewall rules are blocking communication between the web server and the database server.
- **User Permissions:** Confirm that the MediaWiki database user has the necessary privileges.
- **Error Logs:** Examine the MediaWiki error logs and the database server logs for any clues. See Error Logs for locating these log files.
- Manual:Configuration Settings
- Manual:Database Backups
- Manual:Database Security
- Manual:Database Indexing
- Manual:Server Requirements
- Manual:Error Logs
- MySQL Documentation
- PostgreSQL Documentation
- Telegram: @powervps Servers at a discounted price
The choice of database depends on your wiki's size, expected traffic, and administrative preferences. MySQL/MariaDB are commonly used due to their widespread availability and ease of administration. PostgreSQL is known for its robustness and advanced features, while SQLite is suitable for testing or very small-scale deployments.
Database User and Permissions
It’s *critical* to create a dedicated database user for MediaWiki. **Never** use the root user for your wiki's database. This user should have only the necessary privileges to access and modify the wiki's database.
The specific permissions required depend on the chosen database system. Generally, these include:
| Database System | User Creation Command (Example) | Required Privileges |
|---|---|---|
| MySQL/MariaDB | `CREATE USER 'mediawiki'@'localhost' IDENTIFIED BY 'your_password';` | SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP |
| PostgreSQL | `CREATE USER mediawiki WITH PASSWORD 'your_password';` | SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP |
| SQLite | N/A (File-based access) | Read/Write access to the database file |
Always replace `'your_password'` with a strong, unique password. The `'localhost'` in the MySQL example restricts access to the database from the local server only; adjust this if you need remote access, but be aware of the security implications. Consider using a more specific host, like the IP address of your web server. For more details on database security, see Database Security.
Database Configuration in LocalSettings.php
MediaWiki connects to the database through configuration settings in the `LocalSettings.php` file. These settings define the database type, hostname, database name, username, and password.
Here’s an example configuration for MySQL/MariaDB:
```php $wgDBtype = 'mysqli'; $wgDBserver = 'localhost'; $wgDBname = 'your_database_name'; $wgDBuser = 'mediawiki'; $wgDBpassword = 'your_password'; ```
For PostgreSQL:
```php $wgDBtype = 'pgsql'; $wgDBserver = 'localhost'; $wgDBname = 'your_database_name'; $wgDBuser = 'mediawiki'; $wgDBpassword = 'your_password'; ```
Ensure that the values match your database setup exactly. Incorrect settings will prevent MediaWiki from connecting to the database. Refer to Configuration Settings for a complete list of available database configuration options.
Database Maintenance
Regular database maintenance is essential for optimal performance. This includes:
| Maintenance Task | MySQL/MariaDB Command | PostgreSQL Command |
|---|---|---|
| Backup | `mysqldump -u mediawiki -p your_database_name > backup.sql` | `pg_dump -U mediawiki -W your_database_name > backup.sql` |
| Optimize/Analyze | `OPTIMIZE TABLE your_table_name;` | `VACUUM ANALYZE your_table_name;` |
| Repair | `REPAIR TABLE your_table_name;` | `REINDEX TABLE your_table_name;` |
Database Performance Considerations
Troubleshooting Database Connection Issues
If MediaWiki cannot connect to the database, check the following:
| Problem | Possible Cause | Solution |
|---|---|---|
| Connection Refused | Database server not running, firewall blocking connection | Start database server, configure firewall |
| Access Denied | Incorrect username or password, insufficient permissions | Verify credentials, grant necessary privileges |
| Database Not Found | Database does not exist | Create the database |
| Incorrect Database Type | `$wgDBtype` is set incorrectly | Correct the `$wgDBtype` setting in `LocalSettings.php` |
Further Resources
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.* ⚠️