Software Development: MySQL

[article] [edit page] [discussion] [history]

From Humanitarian-FOSS Project Development Site

Contents

Installation instructions

  1. Install MySQL. Go to the MySQL Downloads page and follow the link to "Community Downloads." Follow the link appropriate to your operating system; my suggestions:
    • Windows: it looks like you only have a choice between Windows and 64-bit Windows. Choose the right one. This takes you to the Windows Downloads section.
      1. Skim through the "platform notes" before downloading so you know what to expect and where to look if you encounter problems.
      2. Download the Windows Essentials package by following the "Pick a mirror" link. You can register if you like, but there is a link that allows you to bypass registration. Choose a mirror that is geographically close, and click on HTTP (which will usually be faster than FTP--I don't know why).
      3. Install the package. It is probably easiest in the long run to configure MySQL to run as a service.
    • OS X: I recommend the "package format" link, not the "TAR packages." The "package format" is a standard OS X disk image with an installer, and also comes with a Control Panel for turning MySQL on and off. This takes you to a section where you can choose the package appropriate to your hardware.
      1. Download the appropriate package by following the "Pick a mirror" link and click on the "HTTP" link for a site that is geographically close. This will download a disk image (DMG) file with the package installer. Although it is not completely clear, I believe everything in the ReadMe.txt file following "If you are running Mac OS X Server" applies only to that case.
      2. Double-click on the mysql-5.0.x-...pkg file to install MySQL.
      3. Although it is not necessary, you can install the Preference Pane just by double-clicking on MySQL.prefPane. This will let you click a button to start and stop MySQL (you will need an administrator password).
    • GNU/Linux: too many options here. Talk to me if you need help here.
  2. Install the MySQL Tools applications for a nice graphical front-end to MySQL. Go to the MySQL Downloads page and scroll down to the "MySQL Tools" section. Follow the "MySQL GUI Tools" link. Choose the appropriate package. For Windows I recommend the "x86" package (not the "Without installer" package). The Mac OS 10.4 binary does work on 10.5. Note: if you are already familiar with using phpMyAdmin to administer your MySQL installation, there is no need for this step.

Starting and stopping MySQL

Like Tomcat, MySQL runs as a process on your computer that listens for network connections, in this case on port 3306 Like any application that listens for network connections, it is safest to only have it running when you need it. It will always be running on marlow.cs.wesleyan.edu, so if you are using that server, you do not need to start and stop MySQL.

Starting and stopping from the command-line (GNU/Linux and Mac OS X)

Assuming you have installed MySQL into $MYSQL_HOME, you should have $MYSQL_HOME/bin in your path (see above). Typically MySQL is installed so that the mysql user account is the only one that can read and write databases. Thus you will have to start MySQL so that it runs as though it were started by the mysql user. The easiest way to accomplish this is to use an account that can use the sudo command. On Mac OS X, this is any account with administrator privileges. On GNU/Linux, read up on how to configure /etc/sudoers or ask me for help. From this account, type the following command at the shell prompt:

$ sudo mysqld_safe

You will be prompted for your password (not the administrator password). Once you have entered the password, you will see a couple of messages. Then type Ctrl-Z and then bg to put the process in the background and get control of your shell back (see

Section 5.3 of the Unix Tutorial for more on putting processes in the background). Although you have used sudo to start MySQL, it will actually switch to the mysql user to run, which is much safer than running MySQL as root.

To stop MySQL, type the following command at the prompt:

$ mysqladmin -u root -p shutdown

If you have not yet set a password for the root account, omit the

-p option. Otherwise, type in the MySQL root account password when prompted.

Starting and stopping with the Mac OS X preference panel

If you installed the MySQL preference pane, then you can start and stop MySQL from that just by clicking on the button. You will need to have an account with administrator access to do this.

Starting and stopping under Windows

If you have installed MySQL to run as a service, then there should be an icon in your taskbar for starting and stopping MySQL.

Securing your MySQL installation

MySQL keeps its own list of accounts and passwords. These are completely separate from operating-system related accounts. A MySQL account has the form user@host where

user is a username and host is a hostname. The existence of such an account means that a user named user may log into the MySQL server from the host host. When installed, MySQL is very insecure; the MySQL root (i.e., administrator) account has no password and there is an "anonymous" account that allows anyone to log into your MySQL process from your computer. That is what we will fix here. I will assume you are using the MySQL Tools. MySQL is already secured on marlow, and those of you using marlow already have MySQL accounts with appropriate privileges, so you can ignore this step.

  1. Start MySQL.
  2. Start MySQL Administrator from the MySQL Tools. Enter localhost for the Server Hostname and root for the Username. Do not enter a password. The Port should be 3306.
  3. Select the "Accounts" tab and then root. This is the MySQL root account, not your system root account. But, as expected, it is the MySQL account that has full access to the entire MySQL installation. Assign the account a password of your choosing. Don't forget this password! Verify that this has worked by quitting and re-starting MySQL Administrator. This time you should have to enter your password in the login window.
  4. There is probably an account with no name in the "Accounts" window. If you click on it, the MySLQ User Name will be Anonymous User. Delete this account by selecting it in the "Accounts" window and clicking on the button with a "-" underneath (on GNU/Linux, right-click on the account to get a menu with "Remove user").
  5. Make an account for yourself.
    1. Create your account name. Click on the "+" button beneath the accounts to add a new account (on GNU/Linux, right-click in the accounts area to get a menu with "New user"). Give a MySQL User Name that corresponds to your e-mail id (do not include @wesleyan.edu, @conncoll.edu, or @trincoll.edu). Give yourself a password. Click on "Save Changes."
    2. Allow logins to your account from the local machine. Click on your username in the Accounts window, and then click on the "+" button in the right-hand pair of buttons (the tooltip for this button is "Add a host..."; I don't know what the picture is) (on GNU/Linux, right-click on the account name to get a menu with "Add Host"). Select localhost in the popup window and click "Add."
    3. Disable access to your account from any machine other than the local machine. Untie the twistie next to your username in the Accounts window; there should be two entries: % and localhost. Select % and click on the "-" button in the right-hand pair of buttons (toolip "Remove the selected host..."). Note: % is the MySQL wildcard character; in this context, it means "any host."
  6. Give yourself privileges to create and modify databases with names that start with your username. This can only be done from the command-line. Enter the MySQL command-line environment with the following command:
    $ mysql -u root -p
    

    This will prompt you for the root password. Type the following at the resulting prompt:

    mysql> grant all on `username\_%`.* to username@localhost ;
    mysql> quit
    

    Note that the quotes here are backquotes! Replace username with the username you gave yourself. You may now create and modify databases with names that start with username_. So if my username is ndanner, I can create databases named ndanner_hr, ndanner_petstore,

    ndanner_currencies, etc. Note: this step is not truly necessary if you are the only user of MySQL on your computer, but something like this is necessary when multiple users use the same MySQL server, as when working on a central server like marlow. To ensure that everyone is working under the same environment (which in turn will make it much easier for me to deal with project submissions), I want everyone using the same conventions for database names.

Additional details for using MySQL on marlow

As already mentioned, MySQL is set up and running on marlow. Because of some stupidity with the way the lab is configured, MySQL runs on port 3360, not 3306. The command-line tools are configured to use the correct port; you will have to change the port number on the GUI tools. If you are using marlow, contact me for your MySQL username and password. You will only be able to access MySQL when logged onto marlow. If you wish to use the MySQL Tools, add

/opt/mysql-gui-tools-5.0 to your path. The Administrator and Query Browser programs are named mysql-administrator and mysql-query-browser, respectively. Note that you'll get some complaints from GTK when you start either of these programs, but this does not seem to affect their functionality.

Play around with a sample database

Now you should play around a little with a sample database.

  1. Start up the Query Browser and log in under your own account.
  2. Create a database. Right-click on the "def" folder in the Schemas window (on Mac OS X, I have to click on the folder first, then right-click), and select "Create schema..." (a "schema" is a database for us). Name your database username_hr where username is your username.
  3. In the query window, type
    use username_hr
    

    and click on "Execute." This ensures that all future commands are made relative to the username_hr database, so that we can, e.g., refer to the table employees instead of username_hr.employees.

  4. Populate the database. Download <a href="examples/db/hr.sql">hr.sql</a>, an SQL script that will create a couple of tables and add some rows to each. In the Query Browser, Select "File->New Script Tab", then click on "Open" and select hr.sql. The script should appear in the window. Read through the script so you can get a sense of what SQL commands look like. Then click on "Execute."
  5. Go back to the Query tab and enter
    select * from employees
    

    and click "Execute." You should get the rows of the employees table that was just created and populated.

  6. Now play around with the database. Do some simple queries, then more complex ones with joins and constraints (where clauses). Add some rows to the tables. Add columns to the tables by untying the twistie for the username_hr database right-clicking on one of the tables, and selecting "Edit table." Figure out how to add another table to the database. And so on.
  7. Restart the Query Browser and work through the tutorial in Chapter 3 of the MySQL Reference Manual. It assumes that you are using the MySQL command-line environment, but anything you can type into that environment you can execute in the MySQL Query Browser.
Personal tools