Open Source

Get IT Done: Configure a MySQL database with Webmin

Configure MySQL with Webmin

How many times have you cringed at the thought of setting up a database? Well, cringe no more. Remember that grand application Webmin that Ed Engelking introduced you to in his Daily Feature ”Using Webmin: One amazing tool for Linux administration?” It's back and more helpful than you thought.

In this Daily Feature, I'm going to walk you through some of the basic actions of the MySQL database application. But instead of having to pound out commands like this, you will point and click your way to quickly creating MySQL databases and database tables and filling those tables with data.

Recap of Webmin
Webmin is a Linux, Web-based administration tool that allows you to point your browser to port 10000 and administer your Linux box. From this tool, you can administer Apache, Samba, MySQL, Postfix, ftp, ppp, and too many others to list.

Installing Webmin is as simple as downloading the latest version from the rpmfind site and either running the proper rpm command (as root), such as:
rpm -ivh webmin-0.87.rpm

or the proper set of commands to install from source (again as root):
tar xvzf webmin-o.8.7.tar.gz
cd webmin-0.87
./setup.sh


Then answer all the questions presented. Once the application is installed, make sure your Web server is running. (On most systems, you can run, as root, /etc/rc.d/init.d/http start to start the http daemon.) Point your browser to http://localhost:10000 and log in with the administrator name and password you configured at installation.

Once you are inside Webmin, you will (for the purposes of this Daily Feature) click on the Servers tab and then on the MySQL Database Server icon. If your MySQL Database Server is not running, you will see the Start MySQL Server button. To get to the MySQL Database Server page, you will have to start the server, so click that button. Once on the MySQL Database Server page, you are ready to begin adding and editing your MySQL tables.

Global configuration
At the bottom of the MySQL Database Server page, you will see a row of Global Permissions icons: User Permission, Database Permission, Host Permission, Table Permission, and Field Permission. Each of these entries is fairly self-explanatory. The only thing that could possibly trip you up would be seeing a configuration with All permissions but no All option to select. To give a user/database/host/table/field All permissions, you simply select all of the options from the list and click Save; that field will have the permissions set to All.

Putting together a table
First, on the main MySQL Database Server page, click on the Create New Database link. The New Database Options page (see Figure A) will ask you to enter a few details and create an initial table for this database, if you like. We are going to create a database called LinuxUsers, with an initial table called Editorial (we'll go department by department). If you wanted to create this database without an initial table, you would only have to enter a name for the database (required), choose the None radio button beside Initial Table, and click Create.

Figure A
Unfortunately, the initial table created in your new database will be limited to four fields.


For this example, we are going to create an initial table. We'll create our four-field table with the Field Names: UserName, Extension, OfficeNumber, and MachineName. Once you've entered the information, click Create; you will be returned to the main MySQL Database Server page, where you will see your new database represented by an icon like the one shown in Figure B.

Figure B
Click on the LinuxUsers icon and you will see the icon for the initial table, Editorial.


During the creation of the initial table, you should have noticed that you couldn’t define any of the entries as a primary key. Click on the Editorial icon and you will see a listing of the four entries we created for that table (see Figure C).

Figure C
If you see Yes for all Allow Nulls entries, you know you haven't set any primary keys.


Let's say we want to set the UserName field as a primary key—a field that has a high probability of uniqueness and that will always have an entry. Click on the UserName entry under the Field Name column to bring up the Field Parameters page (see Figure D).

Figure D
If a particular field is one that will hold a common entry, consider giving it a default value.


Select No for Allow Nulls and Yes for Part Of Primary Key. You can also give the primary key a default value. We don’t want to give the field UserName a default value, but we could if we wanted to. With these two values changed, click Save and you'll be returned to the Edit Table page, where you will see No under the Allow Nulls column, and Primary under the Key column (see Figure C).

The next table we’ll create is a copy of the Editorial table called Webdev. From the main MySQL Database Server page, click on the database you wish to edit and the Edit Database screen will appear. Click the Create New Table button. In the Table Name field, enter Webdev, select LinuxUsersEditorial from the Copy Fields From Table drop-down box, and click Create. You will be sent back to the Edit Database screen where you will see the new database table.

Some last thoughts
Dealing with database issues is a vast task. Webmin cannot tackle the entire scope of administering a database, but what it does will help make the initial setup of your databases quick and painless. Using Webmin to set up databases is also a great playground for the uninitiated just getting their feet wet.

Once you get beyond creating/editing your tables, you’ll have to venture into the standard syntax of MySQL. Fortunately, you can remain within Webmin by using the Execute SQL button on the Edit Database page. And don't forget the Help link at the top left of the browser page. This little button will come in very handy.

About

Jack Wallen is an award-winning writer for TechRepublic and Linux.com. He’s an avid promoter of open source and the voice of The Android Expert. For more news about Jack Wallen, visit his website getjackd.net.

0 comments

Editor's Picks