Hands On: Database dalliances

If no database server is shown, click to add one and, on the next screen, specify the hostname as ‘localhost’ – that’s the shorthand for the same server – and make sure the type is set to MySQL. Give a name and password for an administrative user, and click OK.

If the database server is set up for you, it’s most likely been configured with the same administrative username and password that you use to log in to Plesk.

So far, all you’ve effectively done is ‘wake up’ the database server; it’s now running in the background, but before you can use it for anything, you need to create a database and assign access permissions. That’s not done in the main web interface, but by using a tool called phpMyAdmin.

To access that, click the name of the database – in the screenshot at the top of this page that’s the link text ‘Local MySQL server’ – and then on the next screen click Databases. At the top of that screen, you’ll see an icon labelled ‘DB WebAdmin’. Click that to launch phpMyAdmin.

Configuring a new database
In the left-hand panel, there’s a dropdown menu to select databases – there will already be one called mysql, which is where the database server stores permissions, plus some entries for phpMyAdmin and, if the server’s running Plesk, probably a database called psa as well; you may see some others for pre-installed web applications.

To create a new database, say for a web forum or a blogging system, type in the name of the database into the box below ‘Create new database’ in the main part of the window, and choose the ‘collation’, which is how the data will be sorted; for an explanation of the different options, click ‘Character sets and collations’. Not sure which one to choose? Use either latin1-general or one of the general Unicode options.

Usually, you don’t have to give a database a specific name, though some sets of scripts can be picky. Let’s say we need a database for a forum for a Hands On discussion; we’ll call it hoForum, so type that in the box, choose the collation and click the Create button.

The database still isn’t ready, however; for starters, there are no tables in it, so no data can be stored. That’s not too much of a worry, though. Most web applications that require a database also include a script of SQL commands that will set up all the tables and indices needed. But before they can do that, they need permission to access the database.

The buttons at the top of the screen look as if they might help, but that’s a red herring here. What you need to do is to update the information in the MySQL database, which stores permissions for users to all the others. So, click the home icon in the left-hand panel, then click the ‘Privileges’ link, and scroll down to the bottom of the screen to find the link ‘Add a new user’ and click that to reach the next screen.

I suggest giving the user the same name as the database you want them to access, so in this case it’s ‘hoForum’; make sure the host is set to ‘localhost’ so only scripts on the server can access the database, and type a password twice.

The admin page can generate a random password, if you prefer – just copy it into the boxes and make a note somewhere safe, so you can add it to your scripts. Click Go and you’ll see a confirmation screen. Scroll down and there’s a dropdown box next to ‘Add privileges on the following database’ – so far, they can’t change any databases, whatever the privileges you’ve given them. After picking the database, you’ll be taken to another page. Here is where you give the user permissions for the database you’ve just created.