top curve

MySQL

General Information

We give out MySQL accounts according to our account policy. There is not a 1:1 ratio of accounts to databases. One account can access multiple databases, and one database can be accessed by multiple users. Typically, unless there is a specific technical reason we name the databases as usernames to ensure greater ease in database maintenance. A user can create as many tables as s/he likes within the database s/he is given.

Accessing Databases

There are two different ways to gain access to your databases. You can either use the web interface or the command line. The web interface is: www.cs.virginia.edu/csdb.
Or you can login using one of the interactives or power nodes, and access the command line interface. To login to the mysql server, you need to use the command:
mysql -h mysql -u username -p

MySQL Tools

The departmental MySQL Tools are located within /usr/cs/bin. Listed below are some of the tools along with the description of what they do from the dev.mysql.com website.
  • mysql: Run SQL statements from the command line
  • msql2mysql: Convert mSQL programs to be used with MySQL
  • mysqlbug: Create a bug report and mail it
  • mysqldumpslow: Parse and summarize the slow query log
  • mysqldump: Dump a database(s) for backup or for transferring the data to another SQL server
  • mysqlhotcopy: Hot backup
  • mysql_convert_table_format: Conversion, e.g. from ISAM to MyISAM
  • mysql_find_rows: Search for queries containing
  • mysql_setpermission: Aid to add users or databases, sets privileges
  • mysql_zap: Kill processes that match pattern
  • mysqlaccess: Reports the access-privileges for a user from a host to a db
  • mysqladmin: Client for performing administrative operations
  • mysqlcheck: Client checks, repairs, optimizes, and analyzes tables
  • mysqlimport: Client provides a command-line interface to the LOAD DATA INFILE SQL statement
  • mysqlshow: Client shows which databases exist, their tables, or a table’s columns or indexes
  • Additional tools are available. All can be found within /usr/cs/bin. Please contatct root@cs.virginia.edu if you run into any problems or need additional assistance.

    Helpful Links

    • For general information on PHP: www.php.net
    • For documentation on how to actually use PHP: dev.mysql.com/doc
    • To get on the CS Departments PHPMyAdmin site (login required): www.cs.virginia.edu/csdb
    • O'Reilly's books on both MySQL and PHP are great resources for all users. These can be found at most book stores and if you are On-Grounds, are available through the Brown Library Safari Virtual Book Resource. It has both the MySQL Cookbook as well as the PHP Cookbook available.