MySQL CS Primer¶
MySQL is a relational database available for general use by students and faculty in the Computer Science Department. The database server 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
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");
CHANGE YOUR PASSWORD THE FIRST TIME YOU LOGIN!!
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.
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
The above commands are run from the normal shell and not within MySQL.