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.
  • This service is intended for those with modest disk usage requirements, as such 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 MySQL backups.

  • Data Collection
    IMPORTANT

    Please note that you cannot use this service for storing of critical data.

    • 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://datamgmt.iu.edu/dm.shtml.
    • Use of the MySQL Service requires adherence to policies outlined by both the University Information 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.
    • Critical data may not be stored using this MySQL service. Please see Classifications of Institutional Data for a list of examples.
    • You are encouraged to avoid collecting unnecessary data and to make use of existing official data sources, such as HRMS or SIS, when possible. Please see What is the HRMS/SIS project?
    • Passwords that are used as part of a web application 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.

Applying for a MySQL Account

You must already have a Webserve account in order to add a MySQL database to your account. Your MySQL account will 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 visit the WebTech Services Management. 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:account> cmd_mysql start
    

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

  3. IMPORTANT

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

  4. 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:account> mysqladmin -p password "new password"
    Enter password: 
    {enter the password IU Webmaster provided}
    

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

  5. 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:account> 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.%' 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.

    Create additional MySQL users
    You can create a new MySQL user to use for your web applications.

    mysql> CREATE USER 'account'@'129.79.78.%' IDENTIFIED BY 'password';
    

    In the example shown above, replace 'account' with the MySQL username you wish to create and replace 'password' with the password you would like that MySQL user to have.

    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 'account', 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 account@'129.79.78.%' identified by 'password';
    mysql> quit
    

    In the example shown above, replace 'account' 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.

  6. 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:account>
cmd_mysql stop

-- and --

janus:account>
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.