Contents

MySQL Database Information

MySQL Server

The server runs mysql 5.1. Connections to the server must be made by a mysql client. You can not log into the mysql server using ssh/putty.

Connecting to the Server

The connection details are:

Host Name: mysql.ict.swin.edu.au
Port: 3306 (This is the default)
Connections permitted from: %.swin.edu.au

Note: Connections can not currently be made from eduroam (wireless).

Several other names have been used for the mysql server in the past. These are retained for legacy compatibility but the use of the new name is encouraged. The old names are:

mysql.it.swin.edu.au
esrmysql.it.swin.edu.au
neptune.it.swin.edu.au

For most users, your database username will be the same as your mercury username and your password will be your initial password (See Doc_mercury_student_password).

Your mysql password is not connected to your SIMS password or your mercury password. It is recommended that you do not use the same password for your mysql account.

Once you are connected to the server you can find out your database name(s) with the command

show databases

Permissions

In most cases, a suitable set of default permissions is configured. If you require extra privileges, students should contact their subject convenor. Staff should contact the service desk (servicedesk@swin.edu.au).

Client Programs

Command line

The mysql command line client called mysql is the prefered method of connection. Users with a shell account on mercury.it.swin.edu.au can access the command line client by entering the command

mysql

The configuration file on mercury.it.swin.edu.au ensures that you are prompted for a password and connected to the correct server.

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

If you are accessing a team database you must supply the username with the -u parameter like this

mysql -u hit1234_05

You will be prompted for the team password. You must obtain the team password from your convenor.

phpMyAdmin

A web interface called phpMyAdmin is available at http://mercury.ict.swin.edu.au/mysql

Web Interface

There are plans to add mysql support to iSQLJunioR. Look for this soon.

MySQL Workbench

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

authentication protocol refused

To work around this error, edit the advanced connection settings and add

useLegacyAuth=1

Manual

A local copy of the manual is available at http://mirror.it.swin.edu.au/mysql/doc/refman/5.1/en/index.html

Programming

Java

  1. /**
  2. * Sample mysql JDBC code
  3. * Written By John Newbigin
  4. * For more information please use the following URL's
  5. * http://mirror.it.swin.edu.au/mysql/doc/refman/5.0/en/java-connector.html
  6. *
  7. * To compile this test code on mercury
  8. * $ javac mysql.java
  9. *
  10. * To run this test code on mercury
  11. * $ java -classpath mysql-connector-java-5.1.10-bin.jar:. mysql
  12. *
  13. * You can get mysql-connector-java-5.1.10-bin.jar file from here:
  14. * http://downloads.it.swin.edu.au/mysql/mysql-connector-java-5.1.10/mysql-connector-java-5.1.10-bin.jar
  15. * http://dev.mysql.com/downloads/connector/j/5.1.html
  16. *
  17. * You will have to change the getConnection parameters as appropriate
  18. */
  19.  
  20. import java.sql.Connection;
  21. import java.sql.DriverManager;
  22. import java.sql.SQLException;
  23. import java.sql.Statement;
  24. import java.sql.ResultSet;
  25. import java.sql.ResultSetMetaData;
  26.  
  27. public class mysql
  28. {
  29. Connection conn = null;
  30.  
  31. public static void main(String [] args)
  32. {
  33. mysql m = new mysql();
  34.  
  35. String username = "my_username";
  36. String password = "my_password";
  37. String database = "my_database";
  38.  
  39. if(m.connect(username, password, database))
  40. {
  41. m.test("SELECT * FROM my_table");
  42. }
  43. }
  44.  
  45. boolean connect(String username, String password, String database)
  46. {
  47. try
  48. {
  49. conn = DriverManager.getConnection("jdbc:mysql://mysql.ict.swin.edu.au/"+database, username, password);
  50. return true;
  51. }
  52. catch (SQLException ex)
  53. {
  54. System.out.println("SQLException: " + ex.getMessage());
  55. System.out.println("SQLState: " + ex.getSQLState());
  56. System.out.println("VendorError: " + ex.getErrorCode());
  57. }
  58. return false;
  59. }
  60.  
  61. void test(String sql)
  62. {
  63. Statement stmt = null;
  64. ResultSet rs = null;
  65.  
  66. try
  67. {
  68. stmt = conn.createStatement();
  69. if (stmt.execute(sql))
  70. {
  71. rs = stmt.getResultSet();
  72. ResultSetMetaData rsMetaData = rs.getMetaData();
  73. int columns = rsMetaData.getColumnCount();
  74. for(int i=1; i <= columns; i++)
  75. {
  76. System.out.print(rsMetaData.getColumnName(i));
  77. if(i == columns)
  78. {
  79. System.out.println("");
  80. }
  81. else
  82. {
  83. System.out.print(", ");
  84. }
  85. }
  86. while (rs.next())
  87. {
  88. for(int i=1; i <= columns; i++)
  89. {
  90. String val = rs.getString(i);
  91. System.out.print(val);
  92. if(i == columns)
  93. {
  94. System.out.println("");
  95. }
  96. else
  97. {
  98. System.out.print(", ");
  99. }
  100. }
  101. }
  102. }
  103. }
  104. catch (SQLException ex)
  105. {
  106. System.out.println("SQLException: " + ex.getMessage());
  107. System.out.println("SQLState: " + ex.getSQLState());
  108. System.out.println("VendorError: " + ex.getErrorCode());
  109. }
  110. finally
  111. {
  112. if (rs != null)
  113. {
  114. try
  115. {
  116. rs.close();
  117. }
  118. catch (SQLException sqlEx) { } // ignore
  119. rs = null;
  120. }
  121.  
  122. if (stmt != null)
  123. {
  124. try
  125. {
  126. stmt.close();
  127. }
  128. catch (SQLException sqlEx) { } // ignore
  129. stmt = null;
  130. }
  131. }
  132. }
  133. }

PHP with mysql driver

Users are encouraged to use the PHP manual http://php.it.swin.edu.au/manual/en/book.mysql.php

  1. <?
  2. $database = 'my_database';
  3. $username = 'my_username';
  4. $password = 'my_password';
  5.  
  6. $link = mysql_pconnect('mysql.ict.swin.edu.au', $username, $password)
  7. or die('Could not connect: ' . mysql_error());
  8. mysql_select_db($database) or die('Could not select database');
  9.  
  10. $query = 'SELECT * FROM my_table';
  11. $result = mysql_query($query) or die('Query failed: ' . mysql_error());
  12.  
  13. while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
  14. {
  15. print_r($row);
  16. }
  17.  
  18.  
  19. mysql_close($link);
  20. ?>

PHP with PDO

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

  1. <?
  2. $database = 'my_database';
  3. $username = 'my_username';
  4. $password = 'my_password';
  5. $dbh = new PDO("mysql:host=mysql.ict.swin.edu.au;dbname=$database", $username, $password);
  6. foreach($dbh->query('SELECT * from my_table') as $row)
  7. {
  8. print_r($row);
  9. }
  10. ?>

Changing your password

You can change your password by logging into mysql with the command line client and running these SQL statements:

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

Do not use your SIMS, Novell or mercury etc. password. If you save your mysql password a .php script or similar there is a chance that it could be viewed in plain text by other users.

Using mysqldump

mysqldump is a handy tool which can be used to backup & restore mysql data. It has lots of options but normally the defaults work well. You may not have lock permissions on your database so you can skip the locking commands.

Backup

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

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

Restore

mysql my_database < my_database.sql