MariaDB Server

The FeeNIX system runs MariaDB 5.5. Connections to the server must be made by a MySQL client.

Connecting to the Server

You can connect to the MariaDB database using the following clients.

• Command line
• MySQL Workbench (Windows, macOS and Linux)
• phpMyAdmin - Web client

Host Name: feenix-mariadb.swin.edu.au
Port: 3306 (This is the default)
Connections permitted from: %.swin.edu.au

Username

A lowercase 's' followed by your student number.

Password

Initially this will be your six digit date of birth in ddmmyy format. You should change this when you first log in.

You can change your password from the MySQL command line client by running the following SQL statements:

SET SESSION old_passwords=0;
SET PASSWORD = PASSWORD('YourNewPassword');

• Do not save your MySQL password as it could be viewed in plain text by other users.
• The password change function within phpMyAdmin does not work. Please use the above command.

Client Programs

Command line

Users with a Mercury account can access the command line client by entering the command:

mysql

The MySQL command line client is also available on Windows and can be downloaded for use on home computers.

Command to access team database:

mysql -u hit1234_05 -h hostname -p

• Team passwords can be obtained from your Unit Convenor.

phpMyAdmin

phpMyAdmin is available at http://feenix-mariadb-web.swin.edu.au.

MySQL Workbench

Some versions of MySQL workbench will refuse to connect with the error

authentication protocol refused

To resolve this issue, edit the advanced connection settings and add

useLegacyAuth=1

Backup and Restore

mysqldump

mysqldump is a handy tool which can be used to backup & restore MySQL data.

mysqldump Backup

mysqldump --skip-lock-tables --skip-add-locks my_database > my_database.sql

This will write the database to a file called _mydatabase.sql. This file can be transferred between systems if required.

mysqldump Restore

mysql my_database < my_database.sql

Connecting to MariaDB from off campus

You must be using the Swinburne VPN client in order to connect directly to MariaDB from off campus locations.

Manual

A copy of the manual is available on the MariaDB web site.

Programming

Java

   /**
    * Sample mysql JDBC code
    * Written By John Newbigin
    * 
    * To compile this test code on mercury
    * $ javac mysql.java
    *
    * To run this test code on mercury
    * $ java -classpath mysql-connector-java-5.1.10-bin.jar:. mysql
    *
    * You can get mysql-connector-java-5.1.10-bin.jar file from here:
    *  http://downloads.it.swin.edu.au/mysql/mysql-connector-java-5.1.10/mysql-connector-java-5.1.10-bin.jar
    *  http://dev.mysql.com/downloads/connector/j/5.1.html
    *
    * You will have to change the getConnection parameters as appropriate
    */

   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.SQLException;
   import java.sql.Statement;
   import java.sql.ResultSet;
   import java.sql.ResultSetMetaData;

   public class mysql
   {
       Connection conn = null;

       public static void main(String [] args)
       {
           mysql m = new mysql();

           String username = "my_username";
           String password = "my_password";
           String database = "my_database";

           if(m.connect(username, password, database))
           {
               m.test("SELECT * FROM my_table");
           }
       }

       boolean connect(String username, String password, String database)
       {
           try
           {
               conn = DriverManager.getConnection("jdbc:mysql://feenix-mariadb.swin.edu.au/"+database, username, password);
               return true;
           }
           catch (SQLException ex)
           {
               System.out.println("SQLException: " + ex.getMessage());
               System.out.println("SQLState: " + ex.getSQLState());
               System.out.println("VendorError: " + ex.getErrorCode());
           }
           return false;
       }

       void test(String sql)
       {
           Statement stmt = null;
           ResultSet rs = null;

           try
           {
               stmt = conn.createStatement();
               if (stmt.execute(sql))
               {
                   rs = stmt.getResultSet();
                   ResultSetMetaData rsMetaData = rs.getMetaData();
                   int columns = rsMetaData.getColumnCount();
                   for(int i=1; i <= columns; i++)
                   {
                       System.out.print(rsMetaData.getColumnName(i));
                       if(i == columns)
                       {
                           System.out.println("");
                       }
                       else
                       {
                           System.out.print(", ");
                       }
                   }
                   while (rs.next())
                   {
                       for(int i=1; i <= columns; i++)
                       {
                           String val = rs.getString(i);
                           System.out.print(val);
                           if(i == columns)
                           {
                               System.out.println("");
                           }
                           else
                           {
                               System.out.print(", ");
                           }
                       }
                   }
               }
           }
           catch (SQLException ex)
           {
               System.out.println("SQLException: " + ex.getMessage());
               System.out.println("SQLState: " + ex.getSQLState());
               System.out.println("VendorError: " + ex.getErrorCode());
           }
           finally
           {
               if (rs != null)
               {
                   try
                   {
                       rs.close();
                   }
                   catch (SQLException sqlEx) { } // ignore
                   rs = null;
               }

               if (stmt != null)
               {
                   try
                   {
                       stmt.close();
                   }
                   catch (SQLException sqlEx) { } // ignore
                   stmt = null;
               }
           }
       }
   }

PHP with MySQL driver

Users are encouraged to use the PHP manual on php.net

   <?
      $database = 'my_database';
      $username = 'my_username';
      $password = 'my_password';

      $link = mysql_pconnect('feenix-mariadb.swin.edu.au', $username, $password)
       or die('Could not connect: ' . mysql_error());
      mysql_select_db($database) or die('Could not select database');

      $query = 'SELECT * FROM my_table';
      $result = mysql_query($query) or die('Query failed: ' . mysql_error());

      while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
      {
         print_r($row);
      }

      mysql_free_result($result);

      mysql_close($link);
   ?>

PHP with PDO

The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP.

   <?
      $database = 'my_database';
      $username = 'my_username';
      $password = 'my_password';
      $dbh = new PDO("mysql:host=feenix-mariadb.swin.edu.au;dbname=$database", $username, $password);
      foreach($dbh->query('SELECT * from my_table') as $row)
      {
         print_r($row);
      }
   ?>