Dysmey Post > Projects & Stuff > Janovac > Database

Database

Configuring MySQL, the database behind the Web site.

Why I Still Use MySQL

First, I would like to make a short bleat about the crisis in MySQL, which was bought out first by Sun and then by Oracle (when it bought Sun), and whose original developers have subsequently bailed out on. I myself have planned to switch from MySQL to PostgreSQL, a more mature open-source database. I even bought the book to read about it.

But then I realized that all major open-source projects that use a database, such as WordPress and MediaWiki, require MySQL. It may take awhile for those projects to switch to PostgreSQL or some other substitute, and for those changes to perculate through the Internet. So I am stuck with a problematic database for the time being.

Configuration

As noted in the Fedora page, MySQL is selected as the database. But it is not launched at startup by default. That is because MySQL needs to be configured with a root account. You do this with the following command.

mysqladmin -u root -h janovac -p '◊◊◊◊◊◊◊'

Next you need to ensure that MySQL is launched at bootup. Go to System → Administration → Services. Highlight the mysqld service, and press Enable. Provide the root password when asked.

GUI Tools

One of the advantages of MySQL over PostgreSQL is its GUI Tools — MySQL Administrator and MySQL Query Browser — for graphical interfaces like Windows and Linux/GNOME. The GUI Tools are available on the MySQL site.

However, it is easier to user the menu bar and go to System → Administration → Add/Remove Programs. There search under 'mysql-gui'. Checkmark the two results (mysql-gui-tools-5.0r12 and mysql-gui-common-5.0r12) and press Apply. You will be asked to install the two individual tools as well. Enter the root password when asked.

Once installed, you can find the two GUI Tools on the menu bar by going to Applications → Programming. When you launch MySQL Administrator for the first time you will need to fill in the following:

MySQL Administrator Logon

Server Hostnamejanovac
Usernameroot
Password◊◊◊◊◊◊◊

Open the Stored Connection dropbox, press Save Connection…, and save the connection as Root.

Database Restoration

This section deals with a pre-existing database from MySQL off another computer. There are two parts to restoring such a database: The backup and the user.

backup restore

It is always a good idea to back up your data. MySQL Administrator has a backup scheduler utility to do them periodically; it is found under Backup → Schedule Backup. This assumes you have the latest SQL backup file of your data.

Let's assume your backup file is in folder /media/storage/data. In MySQL Administrator:

  1. Select Restore Backup.
  2. Press the Change Path… button on the lower left.
  3. Navigate to /media/storage/data.
  4. Click on the backup file in the list above the button.
  5. Press OK on the Select Encoding box.
  6. Under the Selection tab, make sure the database and tables are checked.
  7. Under the General tab, ensure that the Create schemas if they don't exist is checked.
  8. Press Restore Backup.

user account

A database will have at least one database user account associated with it. (It is not a secure thing to use the root account to access a database from a program.) Database users are created and managed in the User Administration section.

  1. Press the New User button.
  2. Under the User Information tab, enter the name and password for the new user.
  3. Under the Schema Privileges tab, select the database and then the privileges that the user will be allowed to handle.
  4. Press Apply Changes.

Usually a database is constructed with MySQL Administrator, so a user (really a Web program written in PHP, Perl or suchlike) needs only the privileges SELECT, INSERT, UPDATE and DELETE.

MySQL Administrator by default creates users with '%', the wildcard host name. Theoretically a user can connect to any host with this setup; in practice the user can connect to no host at all, as I have discovered. I found that the user account must connect to a specific host.

  1. On the User Accounts box on the left column, right-click on target user account.
  2. In the menu choose Add Host.
  3. Choose Hostname or IP and enter the name of the host.
  4. Press OK.
  5. Press Apply Changes.

Written by Andy West on 9 July 2009.