Database Management

Managing MediaWiki Databases

A MediaWiki-based website stores website content in a database.

Each version of MediaWiki has a slightly different database table structure. Consequently, if MediaWiki is upgraded to a newer version, the database must be upgraded to meet the new data structure requirements.

Databases can be copied to create a backup. But a backup copy cannot be opened if the MediaWiki version is not the same as the version used when the backup copy was created.

If you have a few MediaWiki-based websites, each with their own database, and make copies regularly for backup purposes, and copies when upgrading to newer versions of MediaWiki, then it won't be long before you have many databases. How will you know which website they were for, which version of MediaWiki they are compatible with, and when a copy was created?

Also, of course, to run an older version of MediaWiki so you can open an older database may require the use of a version of PHP which is not compatible with other live websites.

Some Ideas

On a hosted website the hosting provider usually configures a prefix to each database which matches your username.

A database is given an initial default name when it is created, and for the purpose of solving the issues listed above, the names are typically useless.

So here are some suggestions. I build the name of each database by abbreviating several key pieces of information.

Identify the website by name.
Identify a version number for the website, if you have releases by specific version.
Identify the version of MediaWiki by number.
Add a date.

Examples of Database Names (WebX and WebY will be short abbreviations of the website name or title or domain name)

WebX_V3_MW13912 - the website X, Version 3, MediaWiki Version 1.39.12
WebY_V3_MW13912 - the website Y, Version 4, MediaWiki Version 1.39.12
WebX_V4_MW1431 - the website X, Version 4, MediaWiki Version 1.43.1
WebY_V4_MW1431- the website Y, Version 4, MediaWiki Version 1.43.1
WebX_Live_MW145 - current (live version) for website X, MediaWiki version 1.45

For each database there are also several values which need to be recorded. These are used in LocalSettings.php.

$wgDBname - same as the saved name of the database, as listed in cPanel --> Databases --> Manage My Databases
$wgDBuser - as shown in cPanel --> Databases --> Manage My Databases under the heading Privileged Users
$wgDBpassword - not visible on the cPanel --> Databases --> Manage My Databases, should be in LocalSettings.php, but can be changed if a record is lost
$wgUpgradeKey - a 16-digit string used like a password when upgrading using web-based upgrade scripts. Without this 'key' the script in /w/mw-config will not run. There is a workaround - see Upgrading MediaWiki

Checking a Database

On the page cPanel --> Databases --> Manage My Databases, there are two simple tools to Check or Repair a database.

Rename or Copy a Database

The tool cPanel --> Databases --> phpMyAdmin, is initially a bit scary, but safe to use if careful.

A list of databases fills the left pane.

Select a database and the main pane fills with a list of tables.

The top menu has button for Operations. Two useful operations are:-

Rename database to - enter a new name and click on Go
Copy database to - creates a new database with the name you entered, as a copy of the selected database - leave other options as default, and click Go.

Articles which relate to Database Management

Articles which relate to Database Management are included in Category:Database.

The CategoryTree Extension enables a listing of relevant sub-categories and pages:-

Database

no pages or subcategories

Create a New Database

From cPanel --> Databases --> Database Wizard, enter the name for a new database (54 characters max).

Add a User, generate (and record) a strong password.

Add user to the database - select All Privileges.

On completion, return to Manage My Databases. If necessary, increase the page size (number of rows) to see that the new database is listed.

To use the new database with MediaWiki, open an existing MediaWiki installation and substitute the new database name and password in Local Settings.php, $wgDBname, $wgDBuser, and $wgDBpassword.

The database is empty and will not have the structure required for MediaWiki so must be configured - update.php in Terminal.

Cannot use mw-config in a browser because there is no Upgrade Key

$ maintenance/run.php update.php permission deniedmaintenance This worked $ php update.php

Your composer.lock file is up to date with current dependencies! Going to run database updates for grantmil_Aleth_V2_MW1431 Depending on the size of your database this may take a while! Abort with control-c in the next five seconds (skip this countdown with --quick) ...0 Can not upgrade from versions older than 1.35, please upgrade to that version or later first. [grantmil@cpl84 maintenance]$

Import Data

In phpMyAdmin, the new database had no tables, so perhaps the upgrade bit wasn't necessary.

Compress the database using 7-zip and rename to ...sql.zip

In phpMyAdmin select the database, select Import on the Menu, under File To Import seelect Browse and identify the compressed database file. Maximum size to import is 128 MB.

The database now has tables and content but not the compatible structure. Go to terminal

Success rename htaccess browse to site

Links

phpMyAdmin Home Page: https://www.phpmyadmin.net/
phpMyAdmin is provided as a 'third-party application' within cPanel
MySQL® Databases: https://docs.cpanel.net/cpanel/databases/mysql-databases/
cPanel documentation for database operations