MiDatabase: Quick Start Guide (MySQL)

Welcome to the MiDatabase Service. Here are some important steps designed to assist you with the initial access and administration of your database.

Connect to MySQL Database

NOTE: You can only connect to MySQL database using the U-M Virtual Private Network (VPN).

To begin working with your database, you can use a command line MySQL client program. The MySQL client is installed on login.itd.umich.edu UNIX machines. You can use SSH to connect to login.itd.umich.edu using your UMICH password to start using MySQL client:

unix% mysql -u username -h hostname databasename -p

Enter password when prompted. Optionally, you can specify the name of the database that you're going to use.

You can use phpMyAdmin tool to work with your database. This also requires you to login using your UMICH password.

Change Password

Changing your password is a wise thing to do when you're assigned a new account, if you suspect that someone else has access, or every few months as a preventative measure.

Short Version

mysql> set password = password('new-password');

Explicit Version

mysql> select current_user();

mysql> set password for username@`hostname` = password('new-password');

NOTE: The hostname parameter should be the name of the host from which you'll be connecting. In addition, we host an instance of phpMyAdmin which can be used to change a MySQL password.

Use Database

Show Database

mysql> SHOW DATABASES;
+----------------+
| Database       |
+----------------+
| alpha          |
| beta           |
| mysql          |
+----------------+

If you did not specify a databasename when you logged in or if you want to change which database you're using mid-session, then use the command below to start working with your database.

mysql> USE databasename;

Database changed

Create Database

This privilege is given only to dedicated instance customers.

If your database does not exist, or if you need to create another, you can make a new one:

mysql> CREATE DATABASE mydatabasename;

Grant Permissions

Create User

NOTE: This privilege is given to dedicated instance customers only.

For shared database instance customers, the Database Administration team provisions a username when the database is created. If you need additional user profiles associated with your account, ask us - we'll freely accommodate your request.

CREATE USER newuser@’hostname’ IDENTIFIED BY 'some_pass';

NOTE: The hostname parameter should be the name of the host from which you'll be connecting.

Examples

CREATE USER newuser@'%.umich.edu' IDENTIFIED BY 'some_pass';

CREATE USER newuser@’localhost’ IDENTIFIED BY 'some_pass';

The newuser@'localhost' account can be used only when connecting from the local host.

Grant Privileges

NOTE: This privilege is given to dedicated instance customers only.

Shared database customers get the following privileges to their database by default. If you need additional privileges associated with your account, ask us - we'll freely accommodate your request.

Grant select,insert,update,delete,create,drop,references,index,alter,create temporary tables,lock tables,create view, show view on mydatabasename.* to newuser@’%.umich.edu';

Grant stored procedure privilege

If the above user needs privileges to execute stored procedure or triggers, the grant statement will look like this:

grant CREATE ROUTINE, ALTER ROUTINE on mydatabasename.* to newuser@’%.umich.edu';

GRANT EXECUTE ON PROCEDURE mydatabasename.myproc to newuser@’%.umich.edu';

Show Tables

This allows you to see which tables have been created inside the database selected.

mysql> SHOW TABLES;
+------------------------+
| Tables in databasename |
+------------------------+
| people                 |
| states                 |
+------------------------+
2 rows in set (0.00 sec)

 

Export/Import Data

Export

Exporting information from a database is useful for many purposes. Two of the most common are backups and relocation to either another RDBMS or a different machine.

To export from MySQL, you'll want to use the UNIX mysqldump program. It dumps the data into a plain-text format with SQL statements so the database can be easily recreated. Binary data is preserved, represented in ASCII text.

The data is sent to STDOUT, so it may be easier dealt with by redirecting to a file.

unix% mysqldump -u username -h hostname databasename -p > dump-file.sql

Import

Your tables will likely have much more data than is feasible to enter manually. For that reason, most database systems will support the ability to easily export and import data from a delimited text file (tab, pipe '|', or comma are common examples of delimiters).

unix% mysqlimport -u username -h hostname databasename -p tablename.txt

You can also import a SQL file. This is commonly created by mysqldump, but can also be made by hand.

unix% mysql -u username -h hostname databasename -p < dump_file.sql.txt

Join the MCommunity Group

To ensure that you are aware of all system maintenance issues and announcements, please join the MiDatabase MySQL Notify MCommunity group. For instructions on joining this group, visit the MiDatabase MySQL Notify directory site.

Getting Help

For help, please contact the Service Center:

Tags: 
MiDatabase
Last Updated: 
Wednesday, September 28, 2016 - 00:00