..
  2019-??-?? -      - created
  2021-02-16 - paul - added important block about default database name.

===============
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 
<https://mariadb.com/kb/en/library/mariadb-vs-mysql-compatibility/>`_, 
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:

.. code-block:: shell

    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:

.. code-block:: mysql

    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:

.. code-block:: mysql

    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:

.. code-block:: mysql

    mysql> SHOW TABLES;

Delete a table:

.. code-block:: mysql

    mysql> DROP TABLE <tablename>;

Show all data in a table:

.. code-block:: mysql

    mysql> SELECT * FROM <tablename>;

Show column information pertaining to a table:

.. code-block:: mysql

    mysql> DESCRIBE <tablename>;

The commands above are far from exhaustive. More information is
in the `MariaDB website Knowledge Base <https://mariadb.com/kb/en/library/>`_.

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:

.. code-block:: shell

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

Backup a table:

.. code-block:: shell

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

Restore a database (or a table) from backup:

.. code-block:: shell

    /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.