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.

mysql> alter user username identified by 'new-password';

Use Database

Show Database

	| 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 [email protected]’hostname’ IDENTIFIED BY 'some_pass';

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


CREATE USER [email protected]'%.umich.edu' IDENTIFIED BY 'some_pass';

CREATE USER [email protected]’localhost’ IDENTIFIED BY 'some_pass';

The [email protected]'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 [email protected]’%.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 [email protected]’%.umich.edu';

GRANT EXECUTE ON PROCEDURE mydatabasename.myproc to [email protected]’%.umich.edu';

Show Tables

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

	| Tables in databasename |
	| people                 |
	| states                 |
	2 rows in set (0.00 sec)

Export/Import Data


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


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.

How Can We Help You?

For assistance or questions regarding your service, please contact [email protected], or contact the MiDatabase Service Manager directly: Kranthi Bandaru, [email protected]

For other topics, contact the ITS Service Center.

Last Updated: 
Friday, February 24, 2017