Installing MySQL 5.0

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

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

   
   
   

   <a name="install_db">cd mysql<br />./scripts/mysql_install_db</pre></blockquote><br /><br /><p>The script informs you that a root password should be set. You will do this in a few more steps.</p></li><li><a name="my_cnf"></a><br /><p>If you are <a href="http://www.washington.edu/computing/web/publishing/mysql-upgrade.html">upgrading an existing version of MySQL</a>, move back your <tt>.my.cnf</tt> file:</p><br /><br /><blockquote class="code">mv ~/.my.cnf.temp ~/.my.cnf</blockquote><br /><br /><p>This requires that you keep the same port number for your MySQL server when installing the new software.</p><br /></li><li><br /><a name="config"></a><br /><p>If you are installing MySQL for the first time,<br />get the path to your home directory:</p><br /><br /><blockquote class="code">echo $HOME</blockquote><br /><br /><p>Note this down, as you'll need the information in the next step.</p><br /><br /><p>Create a new file called <tt>.my.cnf</tt> in your home directory. This file<br />contains account-specific settings for your MySQL server.</p><br /><br /><blockquote class="code">pico ~/.my.cnf</blockquote><br /><br /><p>Copy and paste the following lines into the file, making the substitutions listed below:</p><br /><br /><blockquote class="code"><pre>[mysqld]<br />port=<span class="hilite">XXXXX</span><br />socket=<span class="hilite">/hw13/d06/accountname</span>/mysql.sock<br />basedir=<span class="hilite">/hw13/d06/accountname</span>/mysql<br />datadir=<span class="hilite">/hw13/d06/accountname</span>/mysql/data<br /><br />[client]<br />port=<span class="hilite">XXXXX</span><br />socket=<span class="hilite">/hw13/d06/accountname</span>/mysql.sock</pre></blockquote><br /><br /><p>Replace the two instances of <span class="code"><span class="hilite">XXXXX</span></span> with a number between 1024 and 65000 (use<br />the same number both times).  Write the number down if you plan to install <a href="http://www.washington.edu/computing/web/publishing/phpmyadmin.html">phpMyAdmin</a>.  This is the port that MySQL will use to listen for<br />connections.</p><br /><br /><p><br /><b>Note:</b> You must use a port number that is not already in use.  You can test a<br />port number by typing <span class="code">telnet localhost XXXXX</span>(again<br />replacing XXXXX with the port number).  If it<br />says "Connection Refused", then you have a good number.  If it says something<br />ending in "Connection closed by foreign host." then there is already a server<br />running on that port, so you should choose a different number.<br /></p><br /><br /><p>Replace <span class="code"><span class="hilite">/hw13/d06/accountname</span></span> with the path to your home directory.</p><br /><br /><p><br /><b>Note:</b> If you're not planning to use <a href="http://dev.mysql.com/doc/mysql/en/innodb.html">the innodb storage<br />engine</a>, then now is a good time to turn it off.  This will save you some<br />space and memory.  You can disable innodb by including a line that says <span class="code">skip-innodb</span> underneath the 'datadir' line in your<br /><tt>.my.cnf</tt> file.</p><br /><br /><p>Write the file and exit Pico.</p></li><li><br /><a name="step10"></a><br /><p>If you are following the directions to <a href="http://www.washington.edu/computing/web/publishing/mysql-upgrade.html">upgrade an existing version of MySQL</a>, you should now copy your databases back into your new MySQL installation:</p><br /><br /><blockquote class="code"><pre>rm -R ~/mysql/data<br />cp -R ~/mysql-bak/data ~/mysql/data</pre></blockquote><br /></li><li><br /><a name="start"></a><br /><p>You are now ready to start your MySQL server.<br /><br /></p><p><b>Make sure you are in the web-development environment, in the mysql <br />	directory (see steps 1 & 2)</b>, and type:</p><br /><br /><blockquote class="code">./bin/mysqld_safe &&lt;/blockquote><br /><br /><p>Be sure to include the ampersand (&) at the end of the command;<br />it is an instruction to run the process in the background.<br />If you forget to type it, you won't be able to continue your<br />terminal session, and you should close your terminal window and open another.<br /></p><br /><br /><p>If everything has gone correctly, a message similar to the following will appear:</p><br /><br /><blockquote class="code"><pre>[1] 67786<br />% Starting mysqld daemon with databases from /hw13/d06/accountname/mysql/data</pre></blockquote><br /><br /><p>If you don't see such a message, MySQL has not started correctly.  Refer to the <br /><a href="http://www.washington.edu/computing/web/publishing/mysql-install.html#trouble">troubleshooting section</a> at the bottom of this page.</p><br /><br /><p>Otherwise, press <tt>Enter</tt> to return to the shell prompt.<br />Your MySQL server is now running as a background job and it will keep<br />running even after you log out.</p></li></ol><br /><br /><h3>Set up permissions and passwords</h3><br /><br /><b>Note:</b> If you are upgrading, you can return to the <a href="http://www.washington.edu/computing/web/publishing/mysql-upgrade.html">upgrade<br />documentation</a> now. Otherwise, if this is a new MySQL installation, continue<br />with setting up the permissions and passwords.<br /><br /><ol start="11"><li><br /><a name="step11"></a><br /><p>At this point your MySQL password is still empty. Use the following command to set a new root password:</p><br /><br /><blockquote class="code">./bin/mysqladmin -u root password "<span class="hilite">mypassword</span>"</blockquote><br /><br /><p>Replace <span class="code hilite">mypassword</span> with a password of your<br />choice<!--; <br />do not enclose your password in any quotation marks-->.</p><br /></li><li><br /><a name="step12"></a><br /><p>You have now created a "root account" and given it a password. This will<br />enable you to connect to your MySQL server with the built-in command-line MySQL<br />client using this account and password.</p><br /><br /><p><br />If you are installing MySQL for the first time, type the following command to connect to the server:</p><br /><br /><blockquote class="code">./bin/mysql -u root -p</blockquote><br /><br /><p>You'll be prompted for the MySQL root password. Enter the password you picked in the previous step.</p><br /><br /><blockquote class="code"><pre>Enter password: <span class="hilite">mypassword</span><br />Welcome to the MySQL monitor.  Commands end with ; or \g.<br />Your MySQL connection id is 4 to server version: 5.0.27-standard<br /><br />Type 'help;' or '\h' for help. Type '\c' to clear the buffer.<br /><br />mysql></pre></blockquote><br /><br /><p>At the <span class="code">mysql></span> prompt, type the commands that follow, replacing <span class="code hilite">mypassword</span> with the root password.  Press [enter] after each semicolon.</p><br /><br /><blockquote class="code"><pre>mysql> use mysql;<br />mysql> delete from user where Host like "%";<br />mysql> grant all privileges on *.* to root@"%.washington.edu" identified by '<span class="code"><span class="hilite">mypassword</span></span>' with grant option;<br />mysql> grant all privileges on *.* to root@localhost identified by '<span class="code"><span class="hilite">mypassword</span></span>' with grant option;<br />mysql> flush privileges;<br />mysql> exit;</pre></blockquote><br /><br /><p>This step allows you to connect to your MySQL server as 'root' from any UW computer.</p><br /></li><li><br /><a name="verify"></a><br /><p>Once back at your shell prompt, you can verify that your MySQL server is running with the following command:</p><br /><br /><blockquote class="code">./bin/mysqladmin -u root -p version</blockquote><br /><br /><p>You'll be prompted for the root password again.</p><br /><br /><p>If MySQL is running, a message similar to the following will be displayed:</p><br /><br /><blockquote class="code"><pre>Enter password:<br />./bin/mysqladmin  Ver 8.41 Distrib 5.0.27, for pc-linux-gnu on i686<br />Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB<br />This software comes with ABSOLUTELY NO WARRANTY. This is free software,<br />and you are welcome to modify and redistribute it under the GPL license<br /><br />Server version          5.0.27-standard<br />Protocol version        10<br />Connection              Localhost via UNIX socket<br />UNIX socket             /hw13/d06/accountname/mysql5.sock<br />Uptime:                 1 min 20 sec<br /><br />Threads: 1  Questions: 2  Slow queries: 0  Opens: 11  Flush tables: 1  Open tables: 6                 <br /> Queries per second avg: 0.025</pre></blockquote><br /></li><li><br /><a name="finish"></a><br /><p>You're done!  A MySQL server is now running in your account and is ready to<br />accept connections. At this point you can <a href="http://www.washington.edu/computing/web/publishing/mysql-admin.html">learn<br />about MySQL administration</a> to get more familiar with MySQL, and you can <a href="http://www.washington.edu/computing/web/publishing/phpmyadmin.html">install phpMyAdmin</a> to help you administer your new<br />database server.</p><br /><br /><p>You can delete the file used to install MySQL with the<br />following command:</p><br /><br /><blockquote class="code">rm<br />~/mysql-standard-5.0.27-linux-i686.tar.gz</blockquote><br /><br /></li></ol><br /><br /><!-- TROUBLESHOOTING --><br /><h2><a name="trouble">Troubleshooting</a></h2><br /><br /><p><b>Error Logs</b></p><br /><br /><p>The MySQL server logs all status and error messages in a file called <tt><i>somehost</i>.err</tt>, where <i>somehost</i> is the name of the host from which a connection was attempted. The file is located in the <tt>mysql/data</tt> directory and contains useful information for debugging problems with your MySQL server.</p><br /><br /><p>To see the 10 most recent messages that were logged, cd into the mysql<br />directory and type the following, replacing <span class="hilite">somehost</span><br />with <em>vergil21.u.washington.edu</em> if your site runs on students.washington.edu,<br /><em>socrates11.myuw.net</em> for home.myuw.net, or <em>ovid21.u.washington.edu</em> otherwise:</p><br /><br /><blockquote class="code">tail -10 ./data/<span class="hilite">somehost</span>.err</blockquote><br /><br />The following are some common errors with their respective fixes:<br /><br /><ul><li><p><b>Misconfigured <tt>~/.my.cnf</tt></b>.</p><br /><p>If only the first numeric line appears (you do not see a <tt>"Starting mysqld<br />daemon..."</tt> message) when you execute <tt>./bin/mysqld_safe<br />&&lt;/tt>, you probably entered at least one incorrect path in your <tt>.my.cnf</tt> file.  Go<br />back and check your <tt>.my.cnf</tt> entries against your path information.  You<br />can find your home directory path by typing <span class="code">echo ~</span> or <span class="code">echo<br />$HOME</span> in the Web Development environment.</p></li><li><p><b>Port in use</b>.</p><br /><p>If the MySQL server starts but then reports <tt>msqld<br />daemon ended</tt>, you probably picked a port in step 8 already in use by<br />someone else.  The error message in <tt><i>somehost</i>.err</tt> will look like this:</p><br /><br /><blockquote class="code"><pre>mysqld started on  Thu Sep 28 14:56:42 PDT 2006<br />060928 14:56:43  Can't start server: Bind on TCP/IP port: Address already in use<br />060928 14:56:43  Do you already have another mysqld server running on port: 3306 ?<br />060928 14:56:43  Aborting</pre></blockquote><br /><br /><p>To fix this, go back and enter another port number in your <tt>.my.cnf</tt> file.</p></li><li><p><b>Incorrect socket path</b>.</p><br /><p>You may see a message in <tt><i>somehost</i>.err</tt> similar to the following:</p><br /><br /><blockquote class="code"><pre>mysqld started on  Sat Nov 4 09:15:02 PDT 2006<br />061104  9:15:02  Can't start server : Bind on unix socket: No such file or directory<br />061104  9:15:02  Do you already have another mysqld server running on socket: /hw13/d06/accountname/mysql.sock ?<br />061104  9:15:02  Aborting<br /></a>

   
   
   

   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.

   
   






Resources




• An Introduction to MySQL from MySQL.com.
  
• Official MySQL documentation from MySQL.com.