Information technology tools and resources at the UW
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.
- MySQL requires occasional maintenance. Be sure to read and understand the Guidelines and Considerations when Running MySQL.
- If you haven’t already done so, you’ll need to activate the Shared Web Hosting service.
- Faculty, Staff, Courses and Departments: Set up a localhome if you haven’t yet. Set up Localhome.
- Log into your web development server using a terminal emulator. If you’re not sure how, click here for instructions.
- At the command prompt, type
- 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.
- Next, you will identify a ‘port’ which will be the address for your MySQL instance. To identify the port, start by making up a port number and test to see if the port number you’ve picked is available. The number you select must be between 1,024 and 65,000. Now test the availability of your port number by typing this line at the command prompt:
telnet localhost number
Example: telnet localhost 1234
- If you see “connection refused,” you have successfully found an available open port! write down this number and continue to the next step.
- If you see anything but “connection refused,” it means that the port is already used by another process and you will need to choose and test another number.
- Explanation: Each MySQL server requires a unique UNIX port in order to work properly. Although counter-intuitive, unused ports refuse connections and already used ports ‘listen to’ and accept connections. Setting up your MySQL server on a port that is in use by another application will cause MySQL server to fail to start and also generate an error message.
- Create and open a new file called .my.cnf in your home directory by typing the following at the command prompt:
- Copy and paste the following lines into the file, replacing the highlighted text with the substitutions listed below:
- 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 /rc13/d06/accountname with the path to your home directory, which you can find by typing
echo $HOMEat the command prompt.
- Save and close the file using Pico. (Click for instructions on how to use pico).
- Type the following 2 lines from the command prompt:
- 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: The script installs the base data files and information into your new MySQL server.
- Now, type the following from the command prompt to start MySQL.
- Press enter to return to the command prompt.
- Set a new root password by typing this at the command prompt.
~/mysql/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!
- This is the root password for your MySQL server. Be sure to write it down.
- Type the following command to connect to the server:
~/mysql/bin/mysql -u root -p
- When prompted, enter the password set up previously in this procedure.
- At the mysql> prompt, carefully, one at a time, type the following five commands. After typing the semicolon character (;) in each command, press the “Enter” key to execute that command line.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.
- At the mysql> prompt, check to see that the users were properly set up by running this command:
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 15 over again.
- Exit the MySQL command line by typing the following at the mysql> command prompt:
- Verify that MySQL is properly responding by typing the following into the command prompt:
~/mysql/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
- 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.
- Sign up for MySQL User Mailman lists to be notified regarding server reboots, maintenance, or other information regarding MySQL servers: MySQL User Mailman Lists
- 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.