Skip Navigation
W
University of Washington
IT Connect
Connecting You to Information Technology at the UW

 

Using the UW Information Technology-installed MySQL binaries

IT Connect > Web > Publishing > Using MySQL > Using UW Information Technology Installed MySQL

MySQL Paths

The version of MySQL we're currently offering can be found (on the Web development systems) at:

/usr/local/mysql

Other MySQL versions which we are testing or waiting to remove may be found at:

/usr/local/mysql-VERSION

(where VERSION is the version of that MySQL installation)

You can see what versions are installed with the following command:

ls -d /usr/local/mysql*

Setting up MySQL

Using the UW Information Technology-installed MySQL binaries is easier than installing MySQL yourself.

  1. StepsActions

  2. Log into your web development host using a terminal emulator. Not sure how to do this? Click here for directions.

  3. Run the MySQL setup script.

    mysql-local-setup

    In order to get the script to run successfully, you have to answer the prompt by typing the word "yes."

  4. Get the path to your home directory:

    echo $HOME

    Write this down, as you'll need the information in a couple of steps.

  5. MySQL needs a unique port number on the server so it can function properly. Ports can be any number between 1024 and 65000, and cannot be used by any other server on your web development host. It is very important to test this port: selecting a port already in use will render your MySQL server unusable. In this step we will find a port for your server to use. Think of a random number between 1024 and 65000, and then type in this command, replacing xxxxx with the number you thought of:

    telnet localhost xxxxx

    If it says "Connection Refused", then you have a good number. Write this down and continue to the next step.

    If it says something ending in "Connection closed by foreign host," then there is already a server running on that port, so you should choose a different number and test it.

  6. Create a new file called .my.cnf in your home directory. This file contains account-specific settings for your MySQL server.

    pico ~/.my.cnf

    Copy and paste the following lines into the file, replacing the highlighted text with the substitutions listed below:

    [mysqld]
    port=xxxxx
    socket=/hw13/d06/accountname/mysql.sock
    basedir=/hw13/d06/accountname/mysql
    datadir=/hw13/d06/accountname/mysql/data
    innodb=off
    default-storage-engine=myisam

    [client]
    port=xxxxx
    socket=/hw13/d06/accountname/mysql.sock

    Replace the two instances of xxxxx with the port number you found in the previous step. Write this number down if you plan to install phpMyAdmin. This is the port that MySQL will use to listen for connections.

    Replace /hw13/d06/accountname with the path to your home directory, which you found in step 4.

    Save and close the file using Pico. (Click for instructions on how to use pico).

  7. Change directories and run the script that writes the privilege tables and sets up default privileges for users of your MySQL server:

    cd mysql
    scripts/mysql_install_db

    The mysql_install_db script may take a while to run. The script informs you that a root password should be set. Don't worry about this for now: you will do this in a few more steps.

  8. Start MySQL

    bin/mysqld_safe &

    Press enter to return to the command prompt.

  9. At this point your MySQL password is still empty. Use the following command to set a new root password:

    bin/mysqladmin -u root password "mypassword"

    Replace mypassword with a password of your choice. Make sure not to use dollar signs ($) in your password.

  10. You have now created a "root account" and given it a password. This will enable you to connect to your MySQL server with the built-in command-line MySQL client using this account and password.

    Type the following command to connect to the server:

    bin/mysql -u root -p

    You'll be prompted for the MySQL root password. Enter the password you picked in the previous step. Note that MySQL does not provide any visual feedback that you are typing in a password: that's normal. Just type the password in and press enter. It should work. After you are successfully logged in, you will see something like this:

    Enter password: mypassword
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 4 to server version: 5.0.45-standard

    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

    mysql>

  11. Right now your server is operating, and has a root password, but is unable to accept connections from our web servers. In this step we will make the changes required to allow you to connect to your MySQL server as 'root' from any UW computer.

    At the mysql> prompt, type the commands that follow, replacing mypassword with the root password. Press [enter] after each semicolon. Pay special attention to single/double quotes and all other punctuation. Failing to enter these commands successfully will hinder the use of your database.

    mysql> use mysql;
    mysql> delete from user where Host like "%";
    mysql> grant all privileges on *.* to root@"%.washington.edu" identified by 'mypassword' with grant option;
    mysql> grant all privileges on *.* to root@localhost identified by 'mypassword' with grant option;
    mysql> flush privileges;

    If Something Doesn't Look Right: If something doesn't look right or MySQL isn't responding, press ctrl+c to exit MySQL. Then go back to step 10 and start again.

  12. Now we will want to make sure that these commands worked successfully. Enter the following:

    mysql> select user,host from mysql.user;

    If all went well, you should see the following:

    +------+------------------+
    | user | host             |
    +------+------------------+
    | root | %.washington.edu |
    | root | localhost        |
    +------+------------------+
    2 rows in set (0.00 sec)

    If you see this, move on to the next step. If you don't see something like this, go back and enter the commands in step 11 over again.

  13. Now we will exit the MySQL command line

    mysql> exit;

  14. Once back at your shell prompt, you can verify that your MySQL server is running with the following command:

    bin/mysqladmin -u root -p version

    You'll be prompted for the root password again.

    If MySQL is running, a message similar to the following will be displayed:

    Enter password:
/usr/local/mysql/bin/mysqladmin  Ver 8.41 Distrib 5.0.45, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB 
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          5.0.45-standard
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /hw13/d06/accountname/mysql.sock
Uptime:                 1 min 20 sec

Threads: 1  Questions: 2  Slow queries: 0  Opens: 11  Flush tables: 1  Open tables: 6                 
 Queries per second avg: 0.025

  15. You should now set up a cron job that will automatically restart your MySQL database in case the main server is rebooted, you should read the following instructions to accomplish this task. Without setting up a cron job, your website may be down until you manually restart your server.

  16. On occasion, UW-IT needs to reboot the database servers for upgrades or maintenance. We highly recommend signing up for one of the MySQL User Mailman lists so you can be informed of system downtime:

    MySQL User Mailman Lists

  17. You're done! A MySQL server is now running in your account and is ready to accept connections. At this point you can learn about MySQL administration to get more familiar with MySQL, and you can install phpMyAdmin to help you administer your new database server.

Migrating from self-installed MySQL

In order to migrate from a copy of MySQL that you have installed to the copy of MySQL that UW Information Technology installed, you will need to stop your MySQL server, copy your data directory, which contains your databases, run some scripts, and then start the MySQL server again.

  1. StepsActions

  2. Change directories to your MySQL installation directory.

    cd mysql

  3. Stop your MySQL Server.

    bin/mysqladmin -u root -p shutdown

  4. Copy your data directory and its contents to a new directory in your home directory with a name that lets you know it is for your MySQL data.

    cp -r data ~/mysql-data

  5. Run the MySQL upgrade script and change to the directory it creates.

    mysql-local-setup; cd ~/mysql

    In order to get the script to run successfully, you have to answer the prompt by typing the word "yes."

  6. Start the MySQL server.

    bin/mysqld_safe &

    Press enter to return to the command prompt.

  7. Make sure that your database is running properly by accessing it.

    If you have any Web applications installed that use your database, those might be a good way of doing this.

  8. Delete the old MySQL installation to save disk space.

    cd ~
    rm -rf mysql-standard-5.0.27-linux-i686 mysql.pre

    Your MySQL installation directory may have a different name if you weren't using the latest version. You may have other MySQL-related files that can be deleted as well.

Upgrading MySQL when using the UW Information Technology-installed copy

From time-to-time, we will upgrade MySQL to a newer version.

When we have installed a new version, we will announce this to the users as well as a time when we will make the new version the default version. Users will have a chance to test the new version if they so desire, before we install the new version as the default. Once this is done, the old version will stick around for a while, in order to give people who have not upgraded a chance to get it done. Later the old version will be removed.

Upgrading MySQL from the version you're using to a newly installed version, if you're using the version at /usr/local/mysql, should just consist of stopping your server, making a backup copy of your data dir, running mysql_upgrade, and then starting the server up again.

These instructions assume that the /usr/local/mysql symbolic link has already been pointed at a newer version of MySQL than the one you're running. If this has not been done yet, you will have to use a different path in order to upgrade, but you'd have to put that different path in your .my.cnf file anyhow, so presumably you'd know about it.

  1. StepsActions

  2. Stop the mysql server.

    bin/mysqladmin -u root -p shutdown

  3. Make a backup copy of your data directory.

    cp -r data ~/mysql-data-bak

  4. Try running mysql_upgrade to make sure your privilege tables are up to date with the current MySQL version.

    bin/mysql_upgrade -u root -p

    The mysql_upgrade script should run some checks and fixes on your databases. If you have a password set for your local root account then this will fail. The mysql_upgrade script doesn't pass the password parameter correctly.

  5. Start MySQL

    bin/mysqld_safe &

    You should now be running the new version of MySQL.

  6. If you'd like MySQL to start up again after the system is rebooted, you should set up a cron job to do that for you.

About IT Connect | News Feed | News Archive | Contact ITC
UW Information Technology