Indiana University

IU Webmaster

Test and Production MySQL Database Service User Guide for the IU Central WWW Servers

Please Note: This document may have additional information added to it.

Table of Contents

Who should read this document?

Account holders on the University web servers, Webserve, who develop web applications that need access to a MySQL database server in order to store and retrieve data from a moderate-sized database.

Return to Top of Page

What are the test and production MySQL database services?

The test and production MySQL database services are optional 'add-ons' for use with your web-publishing account on Webserve, the central web server. Web application programming languages on Webserve, such as Perl and PHP, are equipped to access these database services.

The production MySQL service consists of an account name and port number assignment on the production MySQL database server named mysql.iu.edu. The test MySQL service consists of an account name and port number assignment on the test MySQL database server named mysql-test.iu.edu.

The version of MySQL that is in use on the mysql-test.iu.edu server may be somewhat higher than the one in use on mysql.iu.edu. This provides the MySQL production service user a completely separate environment in which to test their applications before the version of MySQL in use on the production server is upgraded.

You will have virtually 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 (on either or both of the mysql-test.iu.edu or mysql.iu.edu servers). The appropriate use of the account and its associated MySQL server is to provide a database service only for use by web applications on your Webserve account. Each MySQL account is provided 1GB worth of space free of charge.

The test and production services are for those users who are already familiar with relational database design, creation, maintenance, and administration using MySQL. Additionally, users of these services should be familiar with the web programming techniques used to access a MySQL database over a network.

The goal of these services is to provide both a testing/development non-production database service (mysql-test.iu.edu), and a stable, responsive, production, MySQL database server infrastructure for responsible Webserve users who will need little support in their use of the services.

Return to Top of Page

How much does the MySQL service cost?

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

Return to Top of Page

What University web servers have access to the MySQL database service?
Your account is set to accept connections from only the web servers that are associated with your Webserve account. In addition, when you are logged into your account on the MySQL service host (mysql.iu.edu, mysql-test.iu.edu), you can connect to your MySQL server using the hostname: localhost, and this is the default setting in your account's MySQL configuration/settings file.

The mysql.iu.edu or mysql-test.iu.edu services are not offered as general access MySQL database services. Both services are offered as optional functionality enhancements to a Webserve account holder's existing web applications within their account.

Increasing the security of both services is the primary reason that no other hosts (inside or outside the IU Network) are permitted access to the mysql.iu.edu or mysql-test.iu.edu databases. The IU firewall enforces this access limitation.

Return to Top of Page

How is my account set up for MySQL?

Note: Regardless of the manner in which you connect to the host server providing the MySQL service (e.g., via ssh (Secure Shell), via scp (Secure Copy), sftp (Secure FTP), or from a web application) you should refer to the host providing the MySQL service as the name mysql.iu.edu or mysql-test.iu.edu.

  • Regarding Core database:
    • The core database is contained within the mysql directory, /ip/account_name/mysql/data/mysql. This directory should not be deleted or corrupted.
    • The process id of the running mysql server process is contained in account_name.pid.
    • The triton-bin.* files should not be disturbed, they are internal log files kept by the mysql database server while it is running, a new one is created each time the mysql server is started.If the database server is NOT RUNNING, they can generally be completely cleaned out and the server, when restarted, will start a new set.

  • Interactive login: You can log in to the server providing the MySQL service from an SSH (secure shell) client capable of using the SSH2 connection protocol.

  • File transfer using SFTP (Secure FTP), and scp (Secure Copy): You can transfer data and other files to and from the server providing the MySQL service using either scp (Secure Copy)-equipped UNIX platforms or the SFTP (Secure FTP) component of the Windows-based SSH (Secure Shell) client program.

  • CRON-scheduled Background Processing: All MySQL service accounts are pre-enabled to use the CRON scheduled background processing system. This enables you to run database checkpoint and backup processing at night (and sometimes during the day) without significantly impacting database or system performance because CRON scheduled processing runs at a lower priority than your account's MySQL server.

    We strongly recommend doing a DB-level checkpoint mysqldump as an additional form of backup beyond the standard file-level backups that are done each day. Simple file-level backups are performed daily on mysql.iu.edu and mysql-test.iu.edu. Unless you are confident that simply restoring files will yield a usable database, you should use the MySQL utilities, such as a CRON (scheduled background job), to perform a checkpoint mysqldump from time to time depending on how volatile the database is in terms of changes. If a file-level restore does not restore the database properly (in many cases it cannot, because of relationships between data elements within the database files needing to be in complete synchronization), UITS will not be able to provide assistance with further attempts at alternative forms of restoration.

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

  • The master MySQL configuration/settings file: Your account contains a master configuration file for it's MySQL server. This file has been customized for your account. This file can be found in the login directory of your account as the file: .my.cnf. UITS's recommendation is that you make changes to this file with great care as some incorrect changes may cause unwanted changes to the operation of your database server (starting, stopping, behavior, accessibility, performance) or interfere with the operation of other account holder's MySQL servers. If you are uncertain about the effects that a change to your master configuration file may have, please contact the IU Webmaster and you will be routed to an appropriate member of the UITS staff.

  • Online MySQL reference manuals:
    A complete online MySQL reference manual is available for the version of MySQL that is currently being used to provide the MySQL service on mysql.iu.edu .

Return to Top of Page

What guidelines do I need to follow as I use the service?

  • The MySQL test and production database services are community services, which means that all of its users must act responsibly and be mindful of their use of the services to avoid negatively affecting the quality of the service experienced by other users.

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

  • Both the contact and owner of the MySQL service account must understand that it is their responsibility to ensure that proper security for personal information (entry of this by a user should only be done on the /wwws side of their Webserve account house) is maintained in their web applications on Webserve (www.indiana.edu, www.iupui.edu, www.iun.edu, www.iuk.edu) and associated databases on mysql.iu.edu or mysql-test.iu.edu.

  • Both the contact and owner of the MySQL service account must understand that it is their responsibility to ensure that their database(s) are backed up properly. It is also the responsibility of the contact and owner to ensure the backups allow them to perform the desired restores.

  • The owner of your Webserve account is also responsible for the appropriate use of the account associated with the MySQL database service. "Appropriate use" also applies to the nature and security of the content of your database(s) and the security of your account and the databases that it houses. Please refer to the University Information Policy Office and the University Information Security Office websites for important information in these areas.

  • User 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.

  • The policies and charges for disk space use on the MySQL server are the same as those that apply to your Webserve account.

  • 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 are unable to obtain the root password for your database. It is therefore imperative that you keep this password secure - if you lose it or forget it, we will not be able to assist you with the recovery.

Return to Top of Page

What types of support will the IU Webmaster and UITS be able to provide me as a user of the service?

At this time, the IU/UITS Webmaster and IU/UITS WebHost groups will not be providing services related to database design, creation, and management (often referred to as the DBA, or DataBase Administrator role) or Web to database application programming services as part of the MySQL database service. However, we can provide consultation in the following areas:

  • Database startup and shutdown procedures and practices both manually ('on the fly') and during the startup and shutdown of the underlying server's operating system.

  • A complete online MySQL reference manual is available for the version that is used to provide the production MySQL service (mysql.iu.edu).

  • Consultation about database checkpoint, backup and recovery procedures above and beyond the standard directory/file backup that is provided for your database service account.

  • Account holders needing MySQL files restored must contact the webmaster with the account and server name, and a list of the filename(s) and date(s), to be restored. File restores will be performed during normal business hours as soon as possible after receiving a complete request from the account holder. If waiting until the request can be processed during business hours is not acceptable, then assure you have adequate file backups that you can use to restore your database.

    If the file in question is in your account on mysql.iu.edu, you should request that the file be restored to the server named: Janus rather than Webserve. Likewise, if the file in question is in your account on mysql-test.iu.edu, you should request that the file be restored to Europa rather than Webserve.

    Keep in mind though, that correct database operation often depends on a set of files changing together in coordination with each other, and that replacing one file of the set may not be sufficient to restore correct database operation. This is why you should enact your own Backup and Recovery plan.

    Files are backed up as follows:

    • First, all your files are copied and kept until you modify or delete them.
    • The previous 14 versions of modified files are each kept for 14 days.
    • When you delete a file the most recent 3 versions are kept for 14 days and the last version is kept for 60 days.
    • Backups are first written to disk and then written to 2 tape pools.
    • The contact and owner are responsible for any additional backups required.

  • General database tuning recommendations, or questions about changes to your master MySQL configuration file (.my.cnf), should the standard configuration provided with your account need adjustment.

If you need assistance in these areas, please contact the IU Webmaster and they will route your request to an appropriate UITS staff member.

Return to Top of Page

How do I obtain a MySQL database service account?

Please Note:You must already have one or more existing central server Web-publishing accounts on Webserve to apply for a MySQL database service account. Your MySQL service account must share the name of one of your existing Web-publishing accounts on Webserve. This allows all of the account information and responsibilities of your Webserve account (owner, contact, etc.) to also apply to your MySQL service account. The policies and charges for disk space use on the test and production MySQL servers are the same as those that apply to your Webserve account.

  • Keep in mind when utilizing the test MySQL database service on mysql-test.iu.edu that this physical server has significantly less load capacity than mysql.iu.edu in keeping with its role as a test server. The database access portions of your web applications using mysql-test.iu.edu may appear to execute more slowly than if they were interacting with a database on mysql.iu.edu

  • Before applying for a mysql.iu.edu and/or mysql-test.iu.edu account, please read over the guidelines found in this document and note the limited scope of support that we can offer.

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.

Please contact the IU Webmaster with any questions about this service. It is strongly recommended that all test/development MySQL accounts reside on the mysql-test.iu.edu server and NOT on the production mysql.iu.edu server. If you need to move existing test accounts from mysql.iu.edu to mysql-test.iu.edu, please contact Webmaster and we can provide you the information necessary to move the data.

Return to Top of Page

How will I receive the information about my account and what should I do first after I receive this information?

The IU Webmaster will contact you with the port number which your MySQL server will use to accept connections and the root password for the database. To login, you will use the account's network passphrase. Your MySQL server will not be running until you log in to your account and start it manually (see instructions below). Follow the steps below to log in to your MySQL service account for the first time.

  1. First, log into your account for the first time: You will need to use a Secure Shell client program capable of the SSH2 protocol, such as PuTTY.

    Once you have a Secure Shell client program, use it to connect to the server name 'mysql.iu.edu' or 'mysql-test.iu.edu'. The login password will be the network passphrase for that account name. The password you received when the account was created is the root user password for your account and not the password that you use to login to the MySQL servers.

    Be sure and read the login banner information as it contains useful links that will help you in the use of your account. Once the login process is completed, you will receive a prompt similar to: 

    janus:myaccount>

  2. Next, start your MySQL server: Enter the command below to start your account's MySQL server. 

    janus:myaccount> cmd_mysql start

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

  3. Next, change the password for the 'root' user of your account's MySQL server: This is the password you were given by the IU Webmaster when your account was created. It is strongly recommended that you change the password for the root MySQL user for connections coming from your MySQL service account which is also known as 'localhost'.

    You should select a very robust password of no less than 8 characters consisting of combinations of unpronounceable letters and numbers. Remember you and the owner for the account are responsible for taking steps to make your MySQL server as secure as possible.

    To change the password for the 'root' user of your MySQL server for connections coming 'localhost', follow the procedure below:

    Note: Be sure to use the "" marks around "new password" just in the next step. (Please note that if using special characters like $ or ! in the root password, you may need to use single quotes around the password). 

    janus:myaccount> mysqladmin -p password "new password"
Enter password: {enter the old password}
janus:myaccount>

    Replace "new password" with the password you wish to use. For example, if the current root user password is rtdfsc37 and you want to change it to dfgh67tr, the following is what the sequence would look like: 

    janus:myaccount> mysqladmin -p password "dfgh67tr"
Enter password: rtdfsc37
janus:myaccount>

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

  4. Next, set privileges for the 'root' user for connections coming from the web servers:

    Your MySQL server is set to accept connections as the root MySQL user from the web servers.

    The user table must refer to the the web servers as subnet of 129.79.78.% for Webserve accounts.

    Note: Be sure to use the single quote marks around the subnet number and password where it occurs below. You should also substitute 'new password' with the actual password for that username.

    janus:myaccount> mysql --user=root -p
mysql> use mysql
mysql> grant all privileges on *.* to root@'129.79.78.%'
mysql> identified by 'new password' with grant option;
mysql> \q

    You must 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 users with lesser privileges granted to them should be created. Please see MySQL User Privileges document for how-to-do instructions.

    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.

    Regardless of what method you use (via phpMyAdmin or by logging into mysql.iu.edu or mysql-test.iu.edu) to perform database server administration tasks, you should delete the entries for those remote web servers from which you do not wish to connect as the root user and change the password for those that you do.

Return to Top of Page
How do I stop and start the MySQL server associated with my account?

A command-line UNIX utility called 'cmd_mysql' is provided for starting and stopping your MySQL server. This can be run by logging into your account on Janus (mysql.iu.edu) or Europa (mysql-test.iu.edu) and typing the following command: 

janus:myaccount> cmd_mysql stop

-- and --

janus:myaccount> cmd_mysql start

During both the 'stop' and 'start' operations, the procedure attempts to locate your master MySQL configuration file at the location: /ip/myaccount/.my.cnf and read it for any necessary directives.

Important Note: You should always take care that your configuration file contains the correct port number for your MySQL server to use to accept connections. If this number is incorrect, your server may not start because the port number is in use by another program, or it may start and prevent some other MySQL account holder's server from starting.

Return to Top of Page

When is the MySQL service host computer shut down and restarted?

As with any server, there are weekly server restarts. For Janus (mysql.iu.edu) this occurs every Sunday from 6:00am to 7:00. The maintenance window for the MySQL service is listed at http://itnotices.iu.edu/maintenance.aspx

Keep in mind that it is important that your web-based application on any web server accessing your MySQL server should be able to gracefully handle an inability to connect to the server or some interruption in data transfer to and/or from the server during both scheduled and unscheduled periods when mysql.iu.edu or mysql-test.iu.edu are not available.

Return to Top of Page

What are some ways I can tell if my MySQL server is running?

For the purpose of explanation, let's assume that your account name on the MySQL server (and on Webserve) is 'mysql01'.

The method below can give an indication that the mysql.iu.edu server is running.

  1. Log in to your Webserve account.

  2. webserve:mysql01> ping mysql.iu.edu 
    PING mysql.iu.edu (129.79.78.28): 56 data bytes
64 bytes from 129.79.78.28: icmp_seq=0 ttl=64 time=0 ms
64 bytes from 129.79.78.28: icmp_seq=1 ttl=64 time=0 ms
64 bytes from 129.79.78.28: icmp_seq=2 ttl=64 time=0 ms
64 bytes from 129.79.78.28: icmp_seq=3 ttl=64 time=0 ms
64 bytes from 129.79.78.28: icmp_seq=4 ttl=64 time=0 ms
64 bytes from 129.79.78.28: icmp_seq=5 ttl=64 time=0 ms
Enter ^C (control-c) to stop the display 

----mysql.iu.edu PING Statistics----
6 packets transmitted, 6 packets received, 0% packet loss
round-trip (ms)  min/avg/max = 0/0/0 ms
webserve:mysql01>

  3. In the example above, the mysql.iu.edu server was able to respond multiple times to the requests made by the 'ping' command. It can be assumed that the mysql.iu.edu server is running.

This method provides an indication that the processes for the MySQL server for your account are known to the operating system:

  1. Log in to your MySQL service account.

  2. janus:mysql01> ps -ef | grep mysql01

    mysql01 22156 21604 0.1 13:58:07 ttyp2        0:00.16 -bash (bash)
    mysql01 22532 1 0.0 13:58:42 ttyp2        0:00.07 sh /usr/local/mysql/bin/safe_mysqld
                --defaults-file=/ip/mysql01/.my.cnf
    mysql01 22564 22532 0.0 13:58:43 ttyp2        0:00.16 /usr/local/mysql/libexec/mysqld
                --defaults-file=/ip/mysql01/.my.cnf --base
    dir=/usr/local/mysql --datadir=/ip/mysql01/mysql/data --pid-file=/ip/mysql01/mysql/data/mysql01.pid

This method provides an indication that you can make a basic status inquiry to your running database server while you are logged in to your account:

  1. Log in to your MySQL service account.

  2. janus:mysql01> mysqladmin -p status

        Enter password: {enter the root MySQL user password for 'localhost'}
        Uptime: 344834 Threads: 1 Questions: 105 Slow queries: 0 Opens: 12
        Flush tables: 1 Open tables: 1 Queries per second avg: 0.000
        janus:mysql01>
Return to Top of Page
Connecting to a database with the 'mysql' command to mysql.iu.edu and mysql-test.iu.edu from Webserve (while logged into Webserve)

Make sure that your search path includes:

/usr/local/mysql-5.0.45/bin
or
/usr/local/mysql/bin, but not both.

/usr/local/mysql will be a symbolic link to the *current* version of the mysql client software that is installed on Webserve, but other versions may also be installed for needed backward compatibility if applicable in either your .login (tcsh shell) or .profile (ksh, sh, bash, etc) login file.

Note that the 2 examples below are just that, examples. Substitute values that are applicable to your own databases on mysql.iu.edu or mysql-test.iu.edu.

Note that the target database server *must* be set up to allow connections for whatever username and password and client host (Webserve) you are connecting from. In that way, it can 'expect' connections with a specific combination of user+password+hostname, and will assign the privileges that match that combination.

Examples:

        mysql --no-defaults --host=mysql.iu.edu --port=3001 --user=root --password
        or
        mysql --no-defaults --host=mysql-test.iu.edu --port=3001 --user=root --password

Never put your password on the command line after --password or it will show up in a process listing for all to see.

The no-defaults parameter means "don't read any parameters from any .my.cnf file, either one in the login directory of this account or any default .my.cnf that may have been provided in the mysql client software installation." You may decide of course to put a .my.cnf file in your Webserve login directory.

Return to Top of Page

Where MySQL Stores Temporary Files

MySQL uses the value of the TMPDIR environment variable as the pathname of the directory in which to store temporary files. For all accounts, the variable is set to: 

TMPDIR to be /ip/{account_name}/mysql/tmp

Some mysql command line utilities will look for a variable called TMPDIR and use it if temporary files need to be created. This tmp directory within each account is created at the time the account is created.

If you want this directory to be used when you are working with mysql utilities at the command line, you will need to alter the .profile and add in: 

TMPDIR="/ip/${USER}/mysql/tmp" 
export TMPDIR
Then log out and log back in again.

More information about how mysql uses temporary files can be found at http://www.mysql.org/doc/refman/4.1/en/temporary-files.html.

Return to Top of Page

I'm having difficulty accessing mysql.iu.edu from Webserve (or phpMyAdmin). How can I fix this?

If you are getting an error message that is similar to: 

-------------------------------------------

Warning: Access denied for user: 'newUser@hyperion.ucs.indiana.edu' 
  (Using password: YES) in /ip/account_name/wwws/index.php on line 5

-------------------------------------------

To fix this problem, please see these instructions in the MySQL FAQ.

Return to Top of Page

What are some websites, software tools, and books that might be useful as I create, manage and access my MySQL database(s)?

Disclaimer: The suggestions of materials or information sources below do not constitute an official endorsement of their use by UITS or Indiana University.

  • Software tools:

    phpMyAdmin is a web application written in the PHP language. Information about a version that has been customized by UITS to easily install and function in your Webserve account is available. This version installs ready for you to enter a few lines of configuration information and then connect to your MySQL server on mysql.iu.edu.

  • Websites:

  • Books:
    • MySQL/PHP Database Applications by Jay Greenspan and Brad Bulger, M&T Books, ISBN: 0-7645-3537-4
    • MySQL by Paul Dubois, New Riders, ISBN: 0-7357-0921-1

Return to Top of Page