Skip Navigation

UW IT Connect

UW Directories | Calendar | Tools

IT Connect

Connecting You to Information Technology at the UW

Using the UW Technology-installed MySQL binaries

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

UW Technology provides support for MySQL through a limited set of documentation for advanced-level, do-it-yourself use, as well as a set of binaries on the Web development systems that will be updated periodically.

Why use the UW Technology-installed MySQL binaries?

• Using system-installed binaries saves you storage space in your account. (A MySQL installation is now over 200 MB.)
• We installed it so you don't have to.
• We will upgrade it so you don't have to install upgrades. (You may have to update your privilege tables.)

• On This Page
• MySQL Paths
• Migrating from self-installed MySQL
• Setting up MySQL
• Upgrading MySQL when using the UW Technology-installed copy

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*

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 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

1. Change directories to your MySQL installation directory.

   cd mysql

2. Stop your MySQL Server.

   bin/mysqladmin -u root -p shutdown

3. 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

4. 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."

5. Start the MySQL server.

   bin/mysqld_safe &

   Press enter to return to the command prompt.

6. 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.

7. 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.

Setting up MySQL

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

1. StepsActions

1. Log in to your Homer or Dante account with Tera Term or another terminal emulator. Mac and Linux users can open a terminal and run ssh hostname to log in, where hostname is the name of the host, such as homer.u.washington.edu.

2. Press the O key for Other, then press the W key to drop into the Web development environment. Stay in your home directory; at no point during this process should you cd to public_html.

3. Run the MySQL upgrade 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

   Note this down, as you'll need the information in the next step.

5. 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, making the substitutions listed below:

   [mysqld]
   port=xxxxx
   socket=/hw13/d06/accountname/mysql.sock
   basedir=/hw13/d06/accountname/mysql
   datadir=/hw13/d06/accountname/mysql/data
   skip-innodb
   
   [client]
   port=xxxxx
   socket=/hw13/d06/accountname/mysql.sock
   

   Replace the two instances of xxxxx with a number between 1024 and 65000 (use the same number both times). Write the number down if you plan to install phpMyAdmin. This is the port that MySQL will use to listen for connections.

   Note: You must use a port number that is not already in use. You can test a port number by typing telnet localhost xxxxx(again replacing xxxxx with the port number). If it says "Connection Refused", then you have a good number. 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.

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

   Write the file and exit Pico.

6. 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. You will do this in a few more steps.

7. Start MySQL

   bin/mysqld_safe &

   Press enter to return to the command prompt.

8. 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.

9. 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.

   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>
   

   At the mysql> prompt, type the commands that follow, replacing mypassword with the root password. Press [enter] after each semicolon.

   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;
   mysql> exit;
   

   This step allows you to connect to your MySQL server as 'root' from any UW computer.

10. 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
    

11. 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.

12. 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.

Upgrading MySQL when using the UW Technology-installed copy

New versions of MySQL can be requested via the Software Request Form.

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

1. Stop the mysql server.

   bin/mysqladmin -u root -p shutdown

2. Make a backup copy of your data directory.

   cp -r data ~/mysql-data-bak

3. 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.

4. Start MySQL

   bin/mysqld_safe &

   You should now be running the new version of MySQL.

5. 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 IT Connect

IT Connect is provided by
UW Technology