Database Administration

From Server rent store
Revision as of 10:24, 15 April 2025 by Admin (talk | contribs) (Automated server configuration article)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search
  1. 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*)

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:

  • `SELECT`
  • `INSERT`
  • `UPDATE`
  • `DELETE`
  • `CREATE`
  • `ALTER`
  • `INDEX`
  • `DROP`
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:

  • **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.
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

  • **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.

Troubleshooting Database Connection Issues

If MediaWiki cannot connect to the database, check the following:

  • **`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.
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 configuration

Need Assistance?

⚠️ *Note: All benchmark scores are approximate and may vary based on configuration. Server availability subject to stock.* ⚠️

Retrieved from "https://server-rental.store/index.php?title=Database_Administration&oldid=1497"