On the Research Database Complex at IU, how do I access my MySQL database?

Note:

The Indiana University Research Database Complex supports research-related databases and data-intensive applications that require databases. The RDC provides an environment for database-driven web applications focusing on research, and offers the following database accounts:

  • MySQL: The RDC supports MySQL Enterprise Server. Access to phpMyAdmin is created for you by default.
  • Oracle: MySQL can meet the majority of database needs. However, if your research has an immediate requirement for specific functionality in Oracle, you may request to be considered for an Oracle database account. To do so, follow the instructions in the RDC Database and Web Services Account Application.

On this page:


Connecting to the RDC

To connect to your account on the Research Database Complex (RDC) front-end server:

  1. In your SSH client, enter the following (replace username with your IU username):
      ssh username@rdc.uits.iu.edu
  2. Read the message of the day (MOTD). It contains news and information about the status of the RDC.
Note:
Your login shell is automatically set to bash.

Accessing your MySQL database from the RDC

Note:
Your RDC MySQL Database welcome letter contains information you need to connect to your database:
  • Your MySQL database login (username)
  • Information about your initial database password
  • Your database port number

If you don't have your welcome letter, email the High Performance Systems group for help.

To access your MySQL database from the RDC:

  1. Use an SSH2 client to connect to the RDC front-end server (replace username with your IU Network ID username):
  2.   ssh username@rdc.uits.iu.edu
    
  3. At the RDC command prompt, enter:
  4.   mysql --defaults-file=~/.my.cnf -u root -p
    
    Note:
    • As the database administrator (DBA) for your MySQL database, you will initially log into your database as root. By default, your database is set up to allow you to connect as root from rdc.uits.iu.edu and rdcweb.uits.iu.edu. Later, if you prefer to limit root access, you can create additional users.
    • For security reasons, do not enter your password on the MySQL command line.
  5. When prompted, enter your MySQL database password.
  6. When you've logged in successfully, you'll see:
      Welcome to the MySQL monitor.  Commands end with ; or \g.
      Your MySQL connection id is 14
      Server version: 5.5.8-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
      
      Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
      
      Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
      
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
      mysql> 

Allowing access to your MySQL database from Karst

To connect to your MySQL database from Karst, you must first add access to your database's mysql.user table.

Note:
For the purposes of this example:
  • RDC Unix account username: tester
  • MySQL user: root
  • MySQL password for root: 12tstr45
  • If you are connecting to MySQL using the MySQL root account, use the following syntax. Otherwise, substitute the appropriate MySQL username.

To allow access to MySQL from all nodes on Karst:

  1. From the RDC, log into your database as root:
      mysql --defaults-file=~/.my.cnf -u root -p
  2. At the MySQL prompt, enter:
      grant all on *.* to root@'%.karst.uits.iu.edu' identified by '12tstr45';

    MySQL should respond with:

      Query OK, 0 rows affected (0.03 sec)
  3. To reload the grant table, enter:
      flush privileges;

    MySQL should respond with:

      Query OK, 0 rows affected (0.04 sec)
  4. To verify the new grant is in the user table, enter:
      select user,host,password from mysql.user where user='root';

    In the resulting table, check for an entry similar to:

      +------+----------------------+-------------------------------------------+
      | user | host                 | password                                  |
      +------+----------------------+-------------------------------------------+
      | root | %.karst.uits.iu.edu  | *74D03D4FC2540A848F27296C062A83FA1ACDC53A |
      +------+----------------------+-------------------------------------------+
  5. To exit MySQL, enter:
      quit

    MySQL should respond with:

      Bye
  6. To connect to your MySQL database using all the defaults configured on the RDC:
    1. From your account on the RDC, enter:
        cd /N/u/tester/RDC
    2. Copy your MySQL preference file to Karst:
        cp ~/.my.cnf /N/u/tester/Karst/.my.cnf
    3. Verify the copy is complete:
        ls -al /N/u/tester/Karst/.my.cnf

You should now be able to connect to your MySQL database from Karst. If you want to connect from other systems, you'll have to add additional entries for them to the user table.

Allowing access to your MySQL database from Big Red II

To connect to your MySQL database from Big Red II, you must first add access to your database's mysql.user table.

Note:
For the purposes of this example:
  • RDC Unix account username: tester
  • MySQL user: root
  • MySQL password for root: 12tstr45
  • If you are connecting to MySQL using the MySQL root account, use the following syntax. Otherwise, substitute the appropriate MySQL username.

To allow access to MySQL from all nodes on Big Red 2:

  1. From the RDC, log into your database as root:
      mysql --defaults-file=~/.my.cnf -u root -p
  2. At the MySQL prompt, enter:
      grant all on *.* to root@'rsip.bigred2.uits.iu.edu' identified by '12tstr45';
  3. Then, enter:
      grant all on *.* to root@'login%.bigred2.uits.iu.edu' identified by '12tstr45';

    MySQL should respond with:

       Query OK, 0 rows affected (0.03 sec)
     
  4. To reload the grant table, enter:
      flush privileges;

    MySQL should respond with:

      Query OK, 0 rows affected (0.04 sec)
  5. To verify the new grant is in the user table, enter:
      select user,host,password from mysql.user where user='root';

    In the resulting table, check for two entries similar to:

    +------+----------------------------+-------------------------------------------+
    | user | host                       | password                                  |
    +------+----------------------------+-------------------------------------------+
    | root | rsip.bigred2.uits.iu.edu   | *74D03D4FC2540A848F27296C062A83FA1ACDC53A |
    | root | login%.bigred2.uits.iu.edu | *74D03D4FC2540A848F27296C062A83FA1ACDC53A | 
    +------+----------------------------+-------------------------------------------+
  6. To exit MySQL, enter:
      quit

    MySQL should respond with:

      Bye
  7. To connect to your MySQL database using all the defaults configured on the RDC:
    1. From your account on the RDC, enter:
        cd /N/u/tester/RDC
    2. Copy your MySQL preference file to Big Red II:
        cp ~/.my.cnf /N/u/tester/BigRed2/.my.cnf
    3. Verify the copy is complete:
        ls -al /N/u/tester/BigRed2/.my.cnf

You should now be able to connect to your MySQL database from Big Red II. If you want to connect from other systems, you'll have to add additional entries for them to the user table.

Using phpMyAdmin to administer your MySQL database

The phpMyAdmin application lets you administer your MySQL database from a web interface. For IU-specific instructions, see At IU, how do I use the phpMyAdmin web interface to administer my MySQL database on the Research Database Complex?

Back to top

Getting help

For help connecting to your MySQL database at IU, email the High Performance Systems group.

This is document awqb in the Knowledge Base.
Last modified on 2017-08-04 14:48:17.

  • Fill out this form to submit your issue to the UITS Support Center.
  • Please note that you must be affiliated with Indiana University to receive support.
  • All fields are required.

Please provide your IU email address. If you currently have a problem receiving email at your IU account, enter an alternate email address.

  • Fill out this form to submit your comment to the IU Knowledge Base.
  • If you are affiliated with Indiana University and need help with a computing problem, please use the I need help with a computing problem section above, or contact your campus Support Center.

Please provide your IU email address. If you currently have a problem receiving email at your IU account, enter an alternate email address.