Indiana University

IU Webmaster

MySQL Database User Guide for Webserve Account Owners

Table of Contents
Overview of the Service

  • Intended for use as an "add-on" service for Webserve accounts

  • Connections to/from the MySQL service are restricted to Webserve and direct logins to the MySQL servers. As such, you are unable to use desktop MySQL clients to manage your databases or connect to your databases from servers other than Webserve.

  • All MySQL accounts come with a test account to provide you with a completely separate environment in which to test your applications before moving into production or to test the impact of MySQL version upgrades prior to the roll out.

  • There is no charge for the first 1GB of use. For additional billing information, please see Web Account Service Charges.

  • For additional technical details regarding MySQL accounts see:
    MySQL Account Details

Guidelines & Responsibilities

  • Appropriate Use

    • You will have complete control and the associated responsibility for your own MySQL database server running as your account name on the port number that is assigned to you with your account. The appropriate use of the account and its associated MySQL server is to provide a database service only for use by web applications in your Webserve account.

    Test databases must be located on the test server (mysql-test.iu.edu) and not on the production server (mysql.iu.edu). Likewise, production databases must not be placed on the test server but need to be located on mysql.iu.edu. If you need help or have questions about moving test MySQL databases into production, contact IU Webmaster for assistance.

  • Backups and File Restores

    • You are responsible for performing and maintaining your own backups on mysql.iu.edu and mysql-test.iu.edu.

    All accounts are pre-enabled for cron to permit you to run automated database checkpoint and backups. We strongly recommend doing a DB-level checkpoint mysqldump to ensure your data is backed up sufficiently should you need to restore data. While UITS performs standard system backups, because of the relationship between data elements within the data files needing to be in complete synchronization, a file restore done by UITS may not be sufficient to restore your database.

    For additional information see Backup and Recovery in the MySQL Reference Manual and the IU Webmaster document on General information about the use of CRON.

  • Data Collection

    • If you plan on collecting and storing personal information, you should contact the data manager for guidance and regulations that govern the use of that data. The list of data managers can be found at http://informationpolicy.iu.edu/data/dm.shtml.

    • Use of the MySQL Service requires adherence to policies outlined by both the IT Policy Office and the IU Webmaster. Please see Important Security Information for MySQL Accounts for policies and guidelines.

    • Entry of personal data on a web site should be done within the wwws directory.

    • Passwords that are used as part of a web application (or other sensitive user data) that are stored in a MySQL database must be

      (i) transmitted in a secure manner, meaning that the communications between the www browser and the server (Webserve) should encrypted by putting the web application in the secure WWW server;
      --and--
      (ii) stored in a MySQL table encrypted using MySQL data types/functions which encrypt and decrypt the value of the variable as it is read and written to the table.

    • You may not run more than one MySQL database server process (mysqld) within a single account using the same or additional port numbers. If you need additional database server processes for a special set of circumstances, you should apply for another MySQL database service account.

Important: Please note that we may be unable to obtain the root password for your database. It is imperative that you keep this information secure regardless of staff or student turnover.

Applying for a MySQL Account

You must already have a Webserve account in order to apply for a MySQL database account. Your MySQL account must share the name as your Webserve account.

  • Before applying, please read over the guidelines noted above, including the need to perform your own database backups.

To apply for an account, please fill out the MySQL Account Application. You will need to login using your personal Network ID to access this form.

Setting up your account for first time use

The IU Webmaster will contact you when the account is ready and provide you with a port number and root password for your production and test MySQL accounts. Once you have this information, you must follow the steps below to set up your account for use.

  1. Log into your MySQL account on mysql.iu.edu. You will need to use an SSH command line client, such as PuTTY (Windows) or Terminal (Mac).You may obtain PuTTY from IUware Online. To connect to your MySQL account, use the following information: 

    Host Name: mysql.iu.edu 
Username: same as your Webserve account
Password: same as your Webserve account

    If you wish to login to the test MySQL server, use the hostname mysql-test.iu.edu instead.

  2. Start your MySQL server. Enter the following command to start your server: 

    janus:myaccount> cmd_mysql start

    You should see several messages indicating the progress of the server starting.

  3. Change the root password. This is the password you were given by the IU Webmaster when your account was created. You should select a very robust password. (See "Hints for creating secure passwords and passphrases".)

    To change the root password for your account, replace "new password" with the password you wish to set:

    janus:myaccount> mysqladmin -p password "new password"
Enter password: {enter the password IU Webmaster provided}
janus:myaccount>

    If using special characters like $ or ! in the root password, you may need to use single quotes around the password in the command above.

    IMPORTANT: Please be sure to keep your root password safe. We may not be able to retrieve it should you lose it or forget it.

  4. Set privileges for the 'root' user (and other usernames of your choosing) for connections coming from the web servers. To make connections to your database from applications on Webserve, you need to set priviliges for the username making the connection. Privileges must be granted to the subnet (129.79.78.%) rather than to the hostname/machine name.

    Important Notes:

    • Replace 'new password' below with the password set for root in the previous step
    • Single quotes must be used around the password and subnet number as shown below 

    janus:myaccount> mysql --user=root -p
Password: {enter the password you set for root in the previous step}
mysql> use mysql
mysql> grant all privileges on *.* to root@'129.79.78.%'
mysql> identified by 'new password' with grant option;

    Never use the 'root' mysql username to make a connection from your web applications since this allows the application to do anything to any of your databases, even delete them entirely. Other mysql usernames with lesser privileges should be created, as shown in the next step.

    Set privileges for additional usernames
    You can set privileges for your Webserve/MySQL account name or other usernames of your choosing. Only grant the privileges that are necessary for the username to have. In this example, lesser privileges than what was set for root above are being granted to the username 'myaccount', specifically the ability to insert, update, and delete rows only from tables in the database named 'dbname'. 

    mysql> GRANT select,insert,update,delete on dbname.* 
-> to myaccount@'129.79.78.%' identified by 'password';
mysql> \q

    In the example shown above, replace 'myaccount' with the username you wish to grant privileges to and replace 'password' with the password you wish to set for that username. You should substitute the privileges listed with the ones you wish to grant to that username and you should substitute 'dbname.*' with the name of the database/tables to which this username should have access.

    Please see MySQL User Privileges document for further instructions and options when setting privileges.

  5. Your account is now ready for use. You may make database connections using the username/password combinations you set in the step above. In addition to performing database administration tasks by logging your MySQL service account on mysql.iu.edu or mysql-test.iu.edu, you can also perform these tasks using the web interface of the phpMyAdmin program which is available.

Starting/Stopping the MySQL server for your account

You may stop or start the MySQL server for your account by logging into your account on the MySQL server via an SSH client and typing the following: 

janus:myaccount> cmd_mysql stop

-- and --

janus:myaccount> cmd_mysql start

During both the 'stop' and 'start' operations, the system locates the .my.cnf file in your account and reads it for any necessary directives.

Server restarts and maintenance window

As with any server, there are weekly server restarts and a reserved maintenance window. For mysql.iu.edu and mysql-test.iu.edu those times are as follows:

Weekly Restart: Every Sunday, 6:00am - 7:00am
Reserved Maintenance Window: Every Sunday, 12:00am - 9:00am

During these time periods, the MySQL service may be unavailable. It is important that your web-based applications be able to gracefully handle an inability to connect to the MySQL server or some interruption in data transfer to and/or from the server during both scheduled and unscheduled maintenance.

The maintenance window for this and other UITS services can be found at http://itnotices.iu.edu/maintenance.aspx.

Troubleshooting MySQL issues

We have created a document that lists the most commonly reported error messages and how to correct the problem. See: MySQL Frequently Asked Questions.