====== MySQL Databases ======

====== Systems ======
Databases can be created on:
  * The ACM server, acm.cs.uic.edu
  * The ACM workstations via the script <code>cs480</code>


Databases can be accessed using:
  * The mysql command on the command line in a remote or local shell
  * Via [[https://acm.cs.uic.edu/phpmyadmin|PHPMyAdmin]]

====== Local Workstations Databases ======

A local database can be created on our workstations using a script call "cs480"

This script will setup and run a database stored in your home directory under your account

When run it will start your mysql server as well as an HTTP server on localhost:8080 with a PHPMyAdmin instance

**Usage:**
<code >
cs480 - Prints help test
cs480 setup - Creates needed files
cs480 start - Starts servers
cs480 stop - Stop server
</code>

====== Creating a database ======

to access it:


  mysql -u root -p

**SysAdmin has root db password**

databases are created like this: 
  create database mydatabase;
  GRANT ALL PRIVILEGES ON mydatabase.* to 'myuser'@'localhost' IDENTIFIED BY 'mypassword' with GRANT OPTION;

most of the time myuser is the same as the database name.
mysql users are **not** at all related to system users.

====== Backups ======

backups on acmdb are kept in /backup
==== Backing up ====
=== A Simple Database Dump ===

You can use mysqldump to create a simple backup of your database using the following syntax.

    mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]

          o [username] - this is your database username
          o [password] - this is the password for your database
          o [databasename] - the name of your database
          o [backupfile.sql] - the file to which the backup should be written. 
=== Backing Up Specific Tables ===

If you'd like restrict the backup to only certain tables of your database, you can also specify the tables you want to backup. 

    mysqldump -u [username] -p [password] [databasename] [table1 table2 ....] > [backupfile.sql]

          o [username] - this is your database username
          o [password] - this is the password for your database
          o [databasename] - the name of your database
          o [table1 table2 ....] - the names of the tables you want to back up separated by spaces 
          o [backupfile.sql] - the file to which the backup should be written. 

=== Backing Up Multiple Databases ===
If you want to specify the databases to backup, you can use the --databases parameter followed by the list of databases you would like to backup. Each database name has to be separated by at least one space when you type in the command. So if you have to backup 3 databases, let say Customers, Orders and Comments, you can issue the following command to back them up. Make sure the username you specify has permissions to access the databases you would like to backup.

    mysqldump -u [username] -p [password] --databases [database1 database2 ...] > [multibackup.sql] 
    
          o [username] - this is your database username
          o [password] - this is the password for your database
          o [database1 database2 ...] - the names of the databases you want to back up separated by spaces 
          o [multibackup.sql] - the file to which the backup should be written. 
          
=== A Shell Script for Automating Backups ===
You can automate the backup process by making a small shell script which will create a daily backup file. How do you get cron to back up your database without overwriting the older backup? You can use a tiny shell script to add the date to your backup file. An example of a shell script you could use is shown below. This script will also compress the backup file and it will need to be uncompressed before being used to restore a database.

    #!/bin/sh
    date=`date -I`
    mysqldump --all-databases | gzip > /var/backup/backup-$date.sql.gz 

==== Restoring ====
If you have to re-build your database from scratch, you can easily restore the mysqldump file by using the mysql command. This method is usually used to recreate or rebuild the database from scratch.

Here's the general format you would follow:

    mysql -u [username] -p [password] [database_to_restore] < [backupfile] 
    
          o [username] - this is your database username
          o [password] - this is the password for your database
          o [database_to_restore] - the name of your database to restore
          o [backupfile] - the file from which to retore