Creating a MySQL or PostgreSQL Database on Webmin

From OCS Support Wiki

This guide will walk you through setting up a MySQL or PostgreSQL database in Webmin.

[edit] Managing Your Databases

You will first need to login to Webmin to setup a database. Once you are logged in, you will see a menu on the left similar to the one below. Select "Edit Databases" from the list.

A page similar to the one below will be displayed, listing your current databases. From this page you can modify or delete databases.

[edit] Creating a New Database

If you want to create a new one, you'll need to click "Create a new database".

The page that appears next will ask you for the database name, type (MySQL or PostgreSQL), and the character set (if you don't have a specific character set you'd like to use, the default is fine). If you only see MySQL and would like to create a PostgreSQL database please be sure that the PostgreSQL login is enabled for this domain. You can find this in the "Edit Virtual Server" link on the side menu.

[edit] Using Your Database

At this point you can use your database with the database name, your Webmin / SSH username and password, and the database server "localhost". If you would like to create a separate user to access your MySQL database, please see the following section.

[edit] Adding MySQL Database Users

Rather than using your primary username and password to access your MySQL database, you can create a new user with a different password to use the database for security reasons.

On the side menu, select "Edit Mail and FTP Users" as shown below:

When you click this link, you'll come to the manage users page. You can either create a new user or assign database access to an existing one, but for the purposes of this example we will add a new user. To add permissions to an existing user, simply click on that user instead of creating a new one and a similar option will appear.

At the bottom of the create new user / edit user page, you will see a box called "Other user permissions" that when you click the + will expand to show a dialog similar to the following:

To assign access privelages to a user, select the database (or multiple databases my holding CTRL down while clicking) and then click the "Create" (or "Save" button if you're modifying an existing user) to assign the permission. Note that when accessing the database the username is found under the POP3/FTP login column and is simply not just the username. This is usually in the format of user.master_user, where user is the username that you just created and master_user is the main username of the entire Webmin / SSH account.

[edit] A Note About MySQL Passwords

When creating a MySQL password on Webmin, do not use a special character as the first character. Use only a number or letter for the first character.