MySQL CS Primer

MySQL is a relational database available for general use by students and faculty in the Computer Science Department. The database server program is actually MariaDB, a binary drop-in replacement for MySQL, due to the acquisition of MySQL by Oracle.

Logging in the first time

We create a MySQL account for all students and faculty along with their Linux account. The user name is the same as in Linux, but the MySQL password is stored separately and is different. The default initial password is u followed by the last seven (7) digits of the LinkBlue ID number.

For example: Joe’s LinkBlue ID number is 0123456789, so his password to log in the first time is u3456789.

Logging in from a Linux machine:

mysql -h mysql.cs.uky.edu -p -u username

Where username is your login id (linkblue or any login assigned to you).

This command connects to the MySQL server running on the host mysql.cs.uky.edu (an alias for delphi.cs.uky.edu) as the user and prompts for a password. Once connected, the program prompts with mysql> and accepts SQL commands.

Changing your password:

mysql> set password = password("yournewpassword");

Important

CHANGE YOUR PASSWORD THE FIRST TIME YOU LOGIN!!

Important

A database is created for you at the time of account creation. The name of this database is the same as your login name (usually your LinkblueID). You must use this database to create tables for any work.

Connect to a database:

mysql> USE mylogin;

The command above connects to the mylogin database on the MySQL server, where mylogin is the same as your user id. By default, the only database you may access is the one named after your user name.

Once connected to your database, you can create and manipulate tables and perform other SQL activities. Here are some common commands you may find useful. Case is not important in MySQL, but we use CAPS to show reserved words here.

See all tables in the database:

mysql> SHOW TABLES;

Delete a table:

mysql> DROP TABLE <tablename>;

Show all data in a table:

mysql> SELECT * FROM <tablename>;

Show column information pertaining to a table:

mysql> DESCRIBE <tablename>;

The commands above are far from exhaustive. More information is in the MariaDB website Knowledge Base.

Accessing from a web app

The MySQL host should be mysql.cs.uky.edu.

Backups

The MySQL server is available for learning and research purposes. No live data should exist only in the database. There are no data dumps of the database tables, so you should keep your own in case of a failure or of an accidental deletion/modification.

Backup your entire database:

/usr/bin/mysqldump -h mysql -p --databases databasename > ~/database/backup/databasename.sql

Backup a table:

/usr/bin/mysqldump -c -h mysql -p databasename tablename >~/database/backup/databasename.tablename.sql

Restore a database (or a table) from backup:

/usr/bin/mysql -h mysql -p databasename < ~/database/backup/databasename.sql

Note

The above commands are run from the normal shell and not within MySQL.