MySQL

There are five actions that can be taken to deal with MySQL database access on CCC UNIX systems:

All databases are homed on a MySQL server running on mysql.WPI.EDU. When creating a database, you select a database name and a managing user name. If creation is successful, a password will be issued for that user name. This user name and password are for use within the database only and do not correspond to your UNIX login and password.

For tracking purposes, the database is associated with your UNIX login, and your UNIX login is used for subsequent database change operations you might issue from this page, so that others cannot modify your database. Again, your UNIX login is not used to directly reference the database.

Please see the manual for help with MySQL

This self-service page is meant to support courses, projects, clubs, and personal experiments. It has run trouble-free for years in a self-regulating way. We did find a problem once where a student had stored large blobs (unstructured data) which caused a problem, so we recommend that if you have a large chunk of data to store that you store it on the file system and only store a pointer to the data in the database. That will provide faster response for your application and also will not clog the database. Thanks.

Create the database

Choose a database name and a name for the controlling user of the database. Assuming the selection is successful, the database will be created, the user will have full access, and a password will be issued.

This may be the only name you will need to access the database, unless you want to have other names with less complete access to the database (i.e. might only view data, but not be able to alter data). If you need other user names for this sort of purpose, you can create those after you create the database and choose the controlling user name.

Please provide a short description of your reason for using the database:

Database name
User name

Verification of your UNIX identity will associate your login with this database (the controlling user name need not be your name), so you can use this web page to change or drop the database in the future.

Add a user to the database

If the application indicates that other users should be created with lesser access to the database (the controlling user can add or drop tables and change any or all data), then this form can create users with less complete access.

Just choose a new user name and specify the privileges to associate this new name with your existing database. This new user will be granted no privileges unless you check boxes below.

Database name
User name

Privileges:

Select
Insert
Update
Delete
Create
Drop
Grant
References
Index
Alter
Create_tmp_table
Lock_tables
Create_view
Show_view
Create_routine
Alter_Routine
Execute

Note: as of mysql 4.1.10, installed March 2005, the priv columns must be capital Y or N, so if you are dealing with db, table, or user privs, be sure to use upper case. Prior mysql versions didn't care about case.

Set a user's password

Use this form to set a user's password.

User name

Remove a user from the database

Use this form to remove a user from the database. This cannot remove the controlling user; that user remains until the database is removed. This can remove users which were installed with lesser access, using the form above.

If the database is being removed, do not bother with removing the individual users. Simply use the drop the database option, since that will remove all users along with the database.

Database name
User name

Drop the database

Use this form when done with the database. It will remove the database, with all tables and data, as well as all the users of the database.

Database name

Linking mysql client library to a c program

Compiling and linking against mysql should look like:

cc -I/usr/local/mysql-current/include your-program.c -L/usr/local/mysql-current/lib/mysql -lmysqlclient -ldl

In your program, you should include the mysql symbols:

#include <mysql/mysql.h>

which will define the mysql API as documented at http://dev.mysql.com/doc in the chapter on APIs and Libraries

Last modified: Tuesday, 23-Nov-2010 15:25:40 EST
root@wpi.edu

[WPI Homepage]