Setting up MySQL
- 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.
- 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.
- First, you will need to identify a ‘port’ which will be the address for the MySQL instance. Start by choosing a port number and then test to see if that port number is available. The number must be between 1,024 and 65,000. Next test the availability of your port number by typing this line at the command prompt:
telnet localhost number
Example: telnet localhost 12345
- If you see “connection refused,” then SUCCESS, you have discovered an available port! Although this may seem contrary, write down this number and continue to the next step.
- Anything but “connection refused” means that the port is already in use by an active process and is not available.
- Explanation: Each MySQL server requires a unique UNIX port in order to work properly. Although counter-intuitive, unused ports refuse connections and in-use ports ‘listen to’ and accept connections. Trying to set up MySQL server on a port that is already being used will cause the server to fail to start, generating an error message.
- Now, type the following command:
- The script will ask if you would like to install mysql, just type ‘y’ and hit enter to continue.
- The script will find an open port, but be sure and write this number down for use with other programs such as WordPress.
- The script will ask you to create a new password for the ‘root’ user and then to re-enter it again. You will also be given the option to display the password. Again, be sure to write this one down for future use.
- Finally, the script will start up MySQL and give you the port number (again) and the host name. Again, this is something that you will want to write down for later use.
- Explanation: The script installs the base data files and information into your new MySQL server.
- In order to use MySQL with a website you will need to add additional permissions. Type the following command to connect to the new server:
~/mysql/bin/mysql -u root -p
- Next, run the following commands; After typing the semicolon character (;) in each command, press the “Enter” key to execute that command line.
delete from user where Host like "%";
grant all privileges on *.* to root@"%.uw.edu" identified by "mypassword" with grant option;
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;
- 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
- Now, check to see that all users are properly set up by running this command:
SELECT user,host from mysql.user;
- You should see output similar to the following:
| user | host |
| root | %.uw.edu |
| root | %.washington.edu |
| root | localhost |
3 rows in set (0.00 sec)
That’s it, your new MySQL Server should now be ready to use!
Further Recommended Options:
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.
Set up an automated backup script in case data recovery is needed: learn more
MySQL requires a special backup script to be configured to prevent damage to the database copies created during backup.
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.