Configuration of MySQL

From Wiki Against Mines

Back to installation manual.

This part of the MediaWiki installation and configuration manual is about the MySQL Database configuration for MediaWiki.

There are three possible set-up approaches:

• Usage of a newly installed MySQL Database System
• Usage of an existing MySQL Database System using a newly installed database for MediaWiki
• Usage of an existing database on a MySQL Server

Depending on the approach different configuration steps are required.

Contents 1 Reader requirements 2 Approach 1 2.1 Logon the database system 2.2 Secure the MySQL-System 2.3 Creating the Database 2.4 Creating the Users 2.5 Set user rights 2.6 logoff the Database 3 Approach 2 4 Approach 3 5 Assessment

Reader requirements

For the following manual a certain amount of knowledge is required. Therefore fundamentals in following domains are required:

• Logon and logoff a Linux-System
• SQL and database management systems knowledge
• The root password is reuquired

Before continuing this manual please logon the Linux-System and start the prompt (bash) if not started yet. It needs to be mentioned that any database management system command ends with a semicolon (;)

Approach 1

Usage of a newly installed MySQL Database System: To install and configure the database system, following steps are required. To protect the system from unauthorized access the database root account needs to be provided with a password. A user and a database need to be created and implemented within the database system to use the MediaWiki. Furthermore the user needs to get required rights to access the database.

Logon the database system

To change the database settings you need to be logged on at the database management system as root user. Therefore please type in the following command.

mysql -u root -p

The MySQL-Server root password is required. Therefore please type in the password given during the installation. For instance the Linux root password. At Debian no password is required.

Secure the MySQL-System

To gain suitable security solution for the database system a root password needs to be set. (In most cases this is done during the installation). In this particular case the password for the root user is: P@ssw0rd1

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('P@ssw0rd1');

The sample code above sets the password P@ssw0rd1 for the root user. The user is restricted to logon only on the system the database management system is installed on.

Creating the Database

MediaWiki needs access to a database. Therefore to create a database please type in the following code:

mysql> CREATE database wikidb;

The database wikidb will be created.

Creating the Users

MediaWiki needs a database user to access the database wikidb created before. To create a user please type in the following code:

mysql> CREATE USER 'wikiuser'@'localhost' identified BY 'P@ssw0rd';

The user wikiuser with its password P@ssw0rd will be created. Additionally he is restricted to access the database only from the server localhost. These kind of secure settings are recommended. With this settings, to get unauthorized access to the database the Linux-System root-password as well as the database user’s password need to be hacked.

Set user rights

The database user wikiuser, created before, do not have any rights to work on the database, jet. Therefore to provide proper MediaWiki usage the user needs to get several rights. Typing in the following code provides the database user wikiuser@localhost with needed rights. create, select, insert, update, delete, alter, lock tabeles für die Datenbank wikidb:

mysql> GRANT create, select, insert, update, delete, alter, lock tables on wikidb.* TO 'wikiuser'@'localhost';
mysql> FLUSH privileges;

The command FLUSH privileges, instructs the database system to read changed access rights. To activate the settings restarting the database system is not necessary.

logoff the Database

To logoff the database please type in the command quit and press the enter button.

mysql> quit

Afterwards you are back on the Linux prompt (bash).

Approach 2

Usage of an existing MySQL Database System while using a newly installed database for MediaWiki: For this approach an appropriately secured database system is required. Therefore only the following configuration steps need to be done.

• Logon the database system
• Create the database
• Create a user
• Set the user rights
• Logoff the database

Approach 3

Usage of an existing My-SQL database with one database user For this approach an appropriately secured database system is required. Additionally an existent and ready to use database, and a database user with all required rights are needed. To check whether the user has all the required rights or not following commands are available:

• Logon the database as root q.v. Logon the database system
• Check the user rights (available as root only):

mysql> use mysql;
mysql> select Host,DB,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Alter_priv,Lock_Tables_priv
    -> from db
    -> where User='dbuser';

All columns with a _priv - Prefix need to have Y as attribute value. Is this the case other settings are not necessary. If not please provide the database user with required rights (q.v. Set user rights). With the command use mysql the database system instruct the database to use mysql. This is a system database and only users with required rights are able to access this one (e.g. root user).

• Logoff the database system: q.v. logoff the database system.

Assessment

Please answer the following the questions. For checking please klick the button “marking”. In case of reaching less then six marks it is recommended to read the configuration MySQL paragraph, again.

Continue whith Apache HTTP Server Installation

Back to installation manual