Accessing the SI MySQL Database Server

The hostname for the SI MySQL database server is sqldb.si.umich.edu. This machine is running MySQL version 4.0.13 on RedHat Linux 8.0. It replaced the old MySQL server named ebola.si.umich.edu.

For the most part, your database name will be your uniqname. There are a number of databases on the server that are named for the group or project that uses the database.

Direct SCP, SFTP and SSH access to the SI MySQL server is not allowed. To manage your database, you can connect to the Database server using the mysql command-line client from of the following login Unix hosts:

• projects.si.umich.edu
• login.itd.umich.edu
• some other Unix machine with a MySQL command-line client that is on the UM subnet (it's DNS name ends in ".umich.edu"):

    % mysql -h sqldb.si.umich.edu -u jlockard -p
    Enter password: <typed_password>
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 465 to server version: 4.0.13

    Type 'help' for help.

    mysql> show databases;
    +------------+
    | Database   |
    +------------+
    | jlockard   |
    | test       |
    +------------+
    2 rows in set (0.00 sec)

    mysql> use jlockard;
    Database changed
    mysql>

Your database name on the new server is tied to your login name. This is probably different from the database that you used on ebola.si.umich.edu (if you've been around SI awhile). On ebola.si.umich.edu we had created a completely new database server for each user and gave each user root access to the whole database. Now we have one database server instance and have created a database within this server for each user. Each user has root-like control over their own database, but is not be able to create additional databases or additional users. If you need an additional user created, you will need to send in a request to si.computing@umich.edu.

Changing Your MySQL Database Password

Please change your temporary MySQL password (that you receive from SI Computing) the first time you login. This is easily accomplished within the mysql command-line client.

If you received an email from me, regarding your new mysql database, your password was included in the email. If the email you received did not contain your password, then you will need to email SI.Computing or stop in the SI Computing office at 400 West Hall to get/set your password. If you are working on a faculty sponsored project (i.e. the database you use is not your personal project database), we will need to have faculty approval to give you access to the new database.

    % mysql -h sqldb.si.umich.edu -u jlockard -p
    Enter password: <typed_password>
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 467 to server version: 4.0.13

    Type 'help' for help.

    mysql> set password=password('new_password');
    Query OK, 0 rows affected (0.01 sec)

You would put the new password you want to set in place of the "new_password" above. Your password is now set.

The following is only for those of you who had databases on Ebola several years ago:

Exporting Your Old Database From Ebola

If you would like to export your database from ebola.si.umich.edu and import it into sqldb.si.umich.edu, you will need to use the command called mysqldump.

Here's a sample session using mysqldump to export data from ebola:

    % mysqldump -h ebola.si.umich.edu -u root -P 3364 -p iplist > exportfile
    Enter password: <typed_password>

In this example I dumped the contents of a database named "iplist" to a file named "exportfile". The contents of the exportfile will look similar to this:

    # MySQL dump 7.1
    #
    # Host: ebola.si.umich.edu    Database: iplist
    #--------------------------------------------------------
    # Server version        3.22.25

    #
    # Table structure for table 'iplastping'
    #
    CREATE TABLE iplastping (
      Ip3 int(3) DEFAULT '0' NOT NULL,
      Ip4 int(3) DEFAULT '0' NOT NULL,
      Hostname char(60) DEFAULT '' NOT NULL,
      LResponse timestamp(14),
      LAttempt timestamp(14),
      PRIMARY KEY (Ip3,Ip4)
    );

    #
    # Dumping data for table 'iplastping'
    #

    INSERT INTO iplastping VALUES ('203','32','eddie.si.umich.edu','','');

Importing Into Your New Database On SQLDB

Prior to importing this file into your new database you will need to add a line the beginning of the export file which tells the system which database this will be imported into. To import this file into my own database on sqldb.si.umich.edu the line I would add at the beginning of the file would be:

    use jlockard;

Once this line is added, you can use the mysql command to import the file into your new database. Here's an example:

    % mysql -h sqldb.si.umich.edu -u jlockard -p < exportfile
    Enter password: <typed_password>

Now, if I check the contents of my database on sqldb.si.umich.edu I find this:

    % mysql -h sqldb.si.umich.edu -u jlockard -p
    Enter password: <typed_password>
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 467 to server version: 4.0.13

    Type 'help' for help.

    mysql> use jlockard;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

    Database changed
    mysql> show tables;
    +--------------------+
    | Tables_in_jlockard |
    +--------------------+
    | iplastping         |
    +--------------------+
    1 row in set (0.00 sec)

    mysql> select * from iplastping;
    +-----+-----+--------------------+----------------+----------------+
    | Ip3 | Ip4 | Hostname           | LResponse      | LAttempt       |
    +-----+-----+--------------------+----------------+----------------+
    | 203 |  32 | eddie.si.umich.edu | 00000000000000 | 00000000000000 |
    +-----+-----+--------------------+----------------+----------------+
    1 row in set (0.00 sec)

    mysql>

Using Your New Database

You would use your database much the same way you are currently using your database ebola.si.umich.edu. If you're connecting to your database through the web you're likely using PHP's MySQL extensions to do so and your code looks similar to this:

    <?php
        $link = mysql_connect("ebola.si.umich.edu:3364", "root", "password.mysql")
            or die("Could not connect: " . mysql_error());
        print ("Connected successfully");
        mysql_select_db("iplist") or die("Could not select database");

        /* Performing SQL query */
        $query = "SELECT * FROM iplastping";
        $result = mysql_query($query) or die("Query failed : " . mysql_error());
        mysql_close($link);
    ?>

This will need to change slightly to:

    <?php
        $link = mysql_connect("sqldb.si.umich.edu", "jlockard", "new_new_new_password")
            or die("Could not connect: " . mysql_error());
        print ("Connected successfully");
        mysql_select_db("jlockard") or die("Could not select database");

        /* Performing SQL query */
        $query = "SELECT * FROM iplastping";
        $result = mysql_query($query) or die("Query failed : " . mysql_error());
        mysql_close($link);
    ?>

[Changes highlighted in bold.] Please note that the port number (:3364 in the example) is no longer needed. Your user will be your uniqname or groupname and your password may be different. Also, your database name will also be different, and again is either your uniqname or groupname.