Install MySQL

UW Information Technology provides support for MySQL through a limited set of documentation for advanced-level, do-it-yourself use, as well as a set of centrally-managed MySQL binaries on the Web development systems.

Set up MySQL

  1. MySQL requires occasional maintenance. Be sure to read and understand the Guidelines and Considerations when Running MySQL.
  2. If you haven't already done so, you'll need to activate the Shared Web Hosting service.
  3. Faculty, Staff, Courses and Departments: Set up a localhome if you haven't yet.  Set up Localhome.
  4. Log into your web development server using a terminal emulator. If you're not sure how, click here for instructions.
  5. At the command prompt, type mysql-local-setup
    • To get the script to run successfully, you have to answer its prompt by typing the word "yes."
    • Explanation: This command will install and link the central MySQL binaries to your home directory.
  6. Next, you will identify a 'port' which will be the address for your MySQL instance. To identify a port you will guess at an available port number then test to see if the port number you picked is available. Select a number between 1,024 and 65,000. Then test the availability of your number by typing this at the command prompt:

    telnet localhost number
    • Your port number is acceptable if you see "connection refused," write down this number and continue to the next step.
    • If you see anything but "connection refused," you will need to choose and test another number.  So pick another number and test its availability.
    • Explanation: Each MySQL server requires a unique UNIX port in order to work properly. This step helps you locate an unused port that your server will use.
  7. Create and open a new file called .my.cnf in your home directory by typing the following at the command prompt:

    pico ~/.my.cnf
  8. 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 step 6. 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 can find by typing echo $HOME at the command prompt.

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

  10. Type the following two lines at the command prompt.

    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, you will set a password in a few more steps.

    • Explanation: These install the base data files and information into your new MySQL server.
  11. Type the following at the command prompt to start MySQL.

    bin/mysqld_safe &
  12. Press enter to return to the command prompt.

  13. Set a new root password by typing this at the command prompt.

    bin/mysqladmin -u root password "mypassword"
    • Replace mypassword with a password of your choice. And DO put quote characters around your password.

    • Do not use dollar signs ($) in your password.

  14. This is the root password for your MySQL server. Be sure to write it down.

  15. Type the following command to connect to the server:

    bin/mysql -u root -p
    • When prompted, enter the password set up previously in this procedure.

  16. At the mysql> prompt, carefully, one at a time, type the following five commands. After typing the final character (;)  in each command, press the "Enter" key to execute that command.  

    use mysql;
    delete from user where Host like "%";
    grant all privileges on *.* to root@"%.washington.edu" identified by "mypassword" with grant option;
    grant all privileges on *.* to root@localhost identified by "mypassword" with grant option;
    flush privileges;
    • The quote characters shown above are all required.
    • Replace mypassword with the password you selected earlier in this procedure.

    • Explanation: This step allows other systems, including the web servers, to connect to your MySQL databases.
  17. At the mysql> prompt, check to see that the users were properly set up by running this command:

    select user,host from mysql.user;
  18. 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 15 over again.

  19. Exit the MySQL command line by typing the following at the mysql> command prompt:

    exit;
  20. Verify that MySQL is properly responding by typing the following into the command prompt:

    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
         
    
  21. Set up an automated script to restart your MySQL server in case the host system reboots by using a cron job: learn more

    • Without setting up a cron job, your website may be down until you manually restart your server following a system reboot.

  22. Sign up for MySQL User Mailman lists to be notified regarding server reboots, maintenance, or other information regarding MySQL servers: MySQL User Mailman Lists

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

Last modified: May 29, 2014