Software Development: MySQL

Installation instructions
  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.   Skim through the "platform notes" before downloading so you know what to expect and where to look if you encounter problems.   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).   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.   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 file following "If you are running Mac OS X Server" applies only to that case. </li>  Double-click on the  file to install MySQL. </li>

 Although it is not necessary, you can install the Preference Pane just by double-clicking on. This will let you click a button to start and stop MySQL (you will need an administrator password). </li> </ol> </li>  GNU/Linux: too many options here. Talk to me if you need help here. </li> </ul> </li>

 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. </li> </ol>

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, 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, you should have  in your path (see above). Typically MySQL is installed so that the  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

user. The easiest way to accomplish this is to use an account that can use the  command. On Mac OS X, this is any account with administrator privileges. On GNU/Linux, read up on how to configure  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  and then   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   to start MySQL, it will actually switch to the  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

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  where

is a username and  is a hostname. The existence of such an account means that a user named may log into the MySQL server from the 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, and those of you using already have MySQL accounts with appropriate privileges, so you can ignore this step. 

 Start MySQL. </li>  Start MySQL Administrator from the MySQL Tools. Enter for the Server Hostname and  for the Username. Do not enter a password. The Port should be 3306. </li>  Select the "Accounts" tab and then. 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. </li>

 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"). </li>  Make an account for yourself.   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,  , or ).  Give yourself a password. Click on "Save Changes." </li>  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  in the popup window and click "Add."

</li>  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 . 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." </ol> </li>

 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&gt; grant all on `username\_%`.* to username@localhost ; mysql&gt; quit Note that the quotes here are backquotes! Replace  with the username you gave yourself. You may now create and modify databases with names that start with. So if my username is , I can create databases named,  ,

, 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. 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. </li> </ol>

Additional details for using MySQL on
As already mentioned, MySQL is set up and running on. 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, contact me for your MySQL username and password. You will only be able to access MySQL when logged onto . If you wish to use the MySQL Tools, add

to your path. The Administrator and Query Browser programs are named and , 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.  <li> Start up the Query Browser and log in under your own account. </li> <li> 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  where   is your username.

</li> <li> In the query window, type use username_hr and click on "Execute." This ensures that all future commands are made relative to the  database, so that we can, e.g., refer to the table  instead of. </li> <li>

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-&gt;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." </li> <li> Go back to the Query tab and enter select * from employees and click "Execute." You should get the rows of the table that was just created and populated. </li> <li> Now play around with the database. Do some simple queries, then more complex ones with joins and constraints ( clauses). Add some rows to the tables. Add columns to the tables by untying the twistie for the  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.

</li> <li> 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. </li> </ol>