Accessing a MySQL database


Attention

Connecting to a MySQL database from AU_WiFi OR off-campus requires an Auburn University VPN connection. Learn more about the recommended VPN Client. Note: the VPN connection requires multi-factor authentication.

When did you request your database?

You will need the information below to use the instructions further down on the page

Connect via MySQL Workbench

If you are more comfortable with command-line edits, use the instructions below.  Some people prefer to use a GUI interface, such as MySQL Workbench.

  1. If you are off-campus, connect to the VPN Client
  2. Install and Open MySQL Workbench (IMPORTANT: If your server name (above) is mysql.auburn.edu you will specifically have to install  MySQL Workbench 6.3.10 newer versions will NOT work)
  3. In the top menu, select DatabaseManage Connections...



  4. Press the New button.
  5. You may name your connection whatever you wish but then use the following settings:
    1. Connection Method: Standard (TCP/IP)
    2. Hostname: use the server_name from above
    3. Port: 3306
    4. Username: the username associated with the database (usually is your Auburn username)
    5. Password: enter the password associated with the database in the Vault

      Manage Server Connections Dialog Box

  6. Press the Test Connection button
  7. In the top menu, select DatabaseConnect to Database
  8. Choose the connection you just created

Connect via SSH or SecureCRT

  1. If you are off-campus, connect to the VPN Client
  2. Open an SSH session or application (SecureCRT is recommended)
  3. Connect to Mallard with the following settings:
    1. Connection type: SSH2
    2. Hostname: mallard.auburn.edu
    3. Port: 22
    4. Username: your Auburn Univ. username
    5. Authentication: Password
    6. Key exchange: diffie-hellmandiffie-hellman-group
    7. When you attempt to connection, you will be prompted to enter your Auburn Univ. password
  4. Once connected, at the prompt, enter:

    mysql -h sever_name -u username -p

    Note: replace "server_name" with the server_name from the top of the page and "username" with the username associated with the database (usually is your Auburn username)

  5. Press Enter
  6. When prompted, enter the password associated with the database (This is the password that you chose when you requested your database and is not the same as the password you use for most other Auburn resources.)

    Note: as you type the password, you will not see anything happen on-screen -- this is normal

  7. Press Enter
  8. When you get a mysql> prompt, type in the following:

    use name;

    Replace "name" with the database name in your confirmation email. (This is usually in the format usernamedb where "username" is your Auburn username.)

    You're now connected to your MySQL database via telnet.

Connect via a PHP Script

  1. Connect to your MySQL database using a MySQLi extension or PHP Data Objects (PDO) connection.

    W3Schools Tutorial

    Example (mysqli):

    <?php

    $servername = "server_name";  /*(This is the server-name from the top of this instructions page.)*/
    $username = "whatever the db username is (typically your username)";
    $password = "whatever the db password is";
    $db = "databasename";
     
    // Create connection
    $conn = new mysqli($servername, $username, $password, $db);

    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
     
    ?>
  2. You can now start issuing queries from the PHP script.

    Example (mysqli):

    <?php

      $sql = "SELECT * FROM tablename";
      $result = $conn->query( $sql );
     
      $newArray = array();

      if ( $result->num_rows > 0 ) {
        while ( $row = $result->fetch_assoc() ) {
          $newArray[] = $row;
        }
      }

      $arrayCount = count( $newArray );

    ?>