Installing and Starting MySQL 5.0

No comments

Summary

MySQL 5.0 can be installed on the new Web Development Environment. MySQL is a free database server which is well suited as a backend for small database-driven Web sites developed in PHP or Perl. While UW Technology does not support MySQL, these instructions outline the steps for installing, configuring, and troubleshooting MySQL as a server on your own account.

Notes:

  • Please read about running servers on your own account before getting started with MySQL.
  • MySQL requires a significant amount of disk space. A new installation may take ~140 MB. You might want to check your quota.
  • You cannot run two MySQL servers concurrently on the same machine. If you're currently running a MySQL server, you'll need to stop and backup this server according to the instructions for Upgrading MySQL.

Included on this page:

  • Instructions
  • Troubleshooting
  • Resources

Instructions

Setting up a MySQL server on your account is an involved process, but it boils down to three main acts:

  • Download and place the MySQL program in your home directory (steps 1-5)
  • Configure MySQL's basic settings, create the default databases, and start the MySQL server (steps 6-10)
  • Set up access permissions (steps 11-14)

Download and place the MySQL program in your home directory

  1. Log in to your Homer, Dante or shell.myuw.net account with Tera Term or another terminal emulator.

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

    Tip: Follow the instructions in this article very carefully! A single typo could render the entire installation unsuccessful.

  3. Download MySQL 5.0.27 for Linux (i686). This is the most recent version of MySQL.

    wget http://www.washington.edu/computing/web/publishing/mysql-standard-5.0.27-linux-i686.tar.gz

    Note: You can obtain the full source code for this database from the www.mysql.com website.

  4. Unzip the file you just downloaded:

    tar -xzvf mysql-standard-5.0.27-linux-i686.tar.gz

  5. Create a symbolic link to the MySQL directory:

    ln -s mysql-standard-5.0.27-linux-i686 mysql

Configure MySQL's basic settings, create the default databases, and start the MySQL server

  1. Change directories and run the script that sets up default permissions for users of your MySQL server:

    cd mysql
    ./scripts/mysql_install_db

    The script informs you that a root password should be set. You will do this in a few more steps.

  2. If you are upgrading an existing version of MySQL, move back your .my.cnf file:

    mv ~/.my.cnf.temp ~/.my.cnf

    This requires that you keep the same port number for your MySQL server when installing the new software.

  3. If you are installing MySQL for the first time, get the path to your home directory:

    echo $HOME

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

    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

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

    Note: If you're not planning to use the innodb storage engine, then now is a good time to turn it off. This will save you some space and memory. You can disable innodb by including a line that says skip-innodb underneath the 'datadir' line in your .my.cnf file.

    Write the file and exit Pico.

  4. If you are following the directions to upgrade an existing version of MySQL, you should now copy your databases back into your new MySQL installation:

    rm -R ~/mysql/data
    cp -R ~/mysql-bak/data ~/mysql/data

  5. You are now ready to start your MySQL server.

    Make sure you are in the web-development environment, in the mysql directory (see steps 1 & 2), and type:

    ./bin/mysqld_safe &

    Be sure to include the ampersand (&) at the end of the command; it is an instruction to run the process in the background. If you forget to type it, you won't be able to continue your terminal session, and you should close your terminal window and open another.

    If everything has gone correctly, a message similar to the following will appear:

    [1] 67786
    % Starting mysqld daemon with databases from /hw13/d06/accountname/mysql/data

    If you don't see such a message, MySQL has not started correctly. Refer to the troubleshooting section at the bottom of this page.

    Otherwise, press Enter to return to the shell prompt. Your MySQL server is now running as a background job and it will keep running even after you log out.

Set up permissions and passwords

Note: If you are upgrading, you can return to the upgrade documentation now. Otherwise, if this is a new MySQL installation, continue with setting up the permissions and passwords.

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

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

    If you are installing MySQL for the first time, 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.27-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.

  3. 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:
    ./bin/mysqladmin  Ver 8.41 Distrib 5.0.27, for pc-linux-gnu on i686
    Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult 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.27-standard
    Protocol version        10
    Connection              Localhost via UNIX socket
    UNIX socket             /hw13/d06/accountname/mysql5.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

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

    You can delete the file used to install MySQL with the following command:

    rm ~/mysql-standard-5.0.27-linux-i686.tar.gz

Troubleshooting

Error Logs

The MySQL server logs all status and error messages in a file called somehost.err, where somehost is the name of the host from which a connection was attempted. The file is located in the mysql/data directory and contains useful information for debugging problems with your MySQL server.

To see the 10 most recent messages that were logged, cd into the mysql directory and type the following, replacing somehost with vergil21.u.washington.edu if your site runs on students.washington.edu, socrates11.myuw.net for home.myuw.net, or ovid21.u.washington.edu otherwise:

tail -10 ./data/somehost.err
The following are some common errors with their respective fixes:

  • Misconfigured ~/.my.cnf.

    If only the first numeric line appears (you do not see a "Starting mysqld daemon..." message) when you execute ./bin/mysqld_safe &, you probably entered at least one incorrect path in your .my.cnf file. Go back and check your .my.cnf entries against your path information. You can find your home directory path by typing echo ~ or echo $HOME in the Web Development environment.

  • Port in use.

    If the MySQL server starts but then reports msqld daemon ended, you probably picked a port in step 8 already in use by someone else. The error message in somehost.err will look like this:

    mysqld started on  Thu Sep 28 14:56:42 PDT 2006
    060928 14:56:43  Can't start server: Bind on TCP/IP port: Address already in use
    060928 14:56:43  Do you already have another mysqld server running on port: 3306 ?
    060928 14:56:43  Aborting

    To fix this, go back and enter another port number in your .my.cnf file.

  • Incorrect socket path.

    You may see a message in somehost.err similar to the following:

    mysqld started on  Sat Nov 4 09:15:02 PDT 2006
    061104  9:15:02  Can't start server : Bind on unix socket: No such file or directory
    061104  9:15:02  Do you already have another mysqld server running on socket: /hw13/d06/accountname/mysql.sock ?
    061104  9:15:02  Aborting

    In this case, you have incorrectly specified the path to mysql.sock in your home directory. Look at step 8 and fix your ~/.my.cnf file.

    For more information about MySQL error logging, see the MySQL Error Log Page.

0 $type={blogger}:

Post a Comment