Managing the MySQL Database

Use the mysqladmin command to create a database. For this example, create a database named osmsdb by entering the following command:

# mysqladmin –u root –p create osmsdb

At the prompt, enter the MySQL root password.

Start the MySQL client by entering the following command:

# mysql –u root –p osmsdb

Create a sample table by entering the following:

mysql> create table sample (
						 id int(10) auto_increment not null primary key, 
						 name char(20),
						 index(id) 
						 );

This creates a new table named sample with two columns, one called id of type integer and the other called name of type string. The table is indexed by the id column, which is also the table's primary key.

If the table is created without any errors, the following is displayed:

Query OK, 0 rows affected (0.04 sec)

For a detailed explanation of the SQL commands available in MySQL, see “SQL Statement Syntax” in the MySQL 5.0 Reference Manual, which can be found at:

http://dev.mysql.com/doc/refman/5.0/en/sql-syntax.html

To delete the database, use the mysqladmin command as follows:

# mysqladmin –u root –p drop osmsdb

	CAUTION: This command permanently removes all the tables and data contained in the specified database. Use the mysqladmin drop command with caution.

Enter the database root password, at the prompt.

Enter y to confirm deletion of the database. The following should be displayed:

# mysqladmin -u root -p drop osmsdb
Enter password:
Dropping the database is potentially a very bad thing to do. 
Any data stored in the database will be destroyed.

Do you really want to drop the 'osmsdb' database [y/N] y
Database "osmsdb" dropped

Start the MySQL command-line client by entering the following command:

# mysql –u root –p

At the prompt, enter the MySQL root password.

To create a database user, use the following command:

mysql> create user username ;

For example, enter the following:

mysql> create user osmsusr ;

	CAUTION: This command creates a user named osmsusr, which can connect to the MySQL database from any system.

To verify creation of the database user account, the following command is used:

mysql> show grants for username ;

For example, enter the following:

mysql> show grants for osmsusr ;

Start the MySQL command-line client by entering the following command:

# mysql –u root –p

At the prompt, enter the MySQL root password.

To delete a database user, use the following command:

mysql> drop user username ;

For example, enter the following:

mysql> drop user osmsusr ;

Start the MySQL command-line client by entering the following command:

# mysql –u root –p

At the prompt, enter the MySQL root password.

To grant privileges to a database user account, use the following command:

mysql> grant all privileges on database.* to username@'hostname' \

identified by 'password' ;

For example, enter the following:

mysql> grant all privileges on osmsdb.* to osmsuser@'%' \

identified by 'osmspass' ;

	NOTE: The preceding command grants access to any table in the osmdb database for the user osmsuser@% with the password osmpass, connecting from any host name. In the command, the percent character (%) acts as a wildcard and grants access to the user from any host name. A user in MySQL is identified by username@hostname. If you do not provide a value for the hostname during the creation of the database, the default value is any host (@%). Similarly, a value of %.%.%.% matches any IP address, such as 192.168.0.2 or 10.200.1.65.

For more information about the grant command, see “MySQL Access System” in the MySQL 5.0 Reference Manual located at:

http://dev.mysql.com/doc/refman/5.0/en/privilege-system.html

Immediately after granting a database user permissions, activate the permissions by entering the following:

mysql> flush privileges;

Using your favorite text editor, create the following SQL script to generate the sample database:

drop database if exists osmsdb;
create database osmsdb ;
use osmsdb ;
create table customer
(
   customerID   int(10) auto_increment not null primary key,
   name         char(20),
   purchased    datetime not null,
   product      varchar(30),
   productID    int,
   INDEX (customerID)
)
TYPE = InnoDB;
create table employee
(
  id    int(5) auto_increment not null  primary key,
  depno int(5),
  name  varchar(20) not null,
  cardnumber    bigint(15) not null
);
 
insert into customer(name,purchased,product,productID) \ 
values( 'Jenny','2006-07-07 13:00:33','HP Laptop nc6220',5001), \
('Kate','2006-07-09 09:38:12', 'HP LaserJet 5M',9076);
insert into employee(depno,name,cardnumber) values( 10,'Mike',100081),\
(10,'Tom',100082),(10,'Jack',100083), (20,'Mary',100084),(20,'Cherry',100085),\
(20,'Jones',100086);
select * from customer ;
select * from employee ;

grant all on osmsdb.* to osmsusr@'localhost' identified by 'osmspass';
grant all on osmsdb.* to osmsusr@'%' identified by 'osmspass';
grant select on osmsdb.* to osmsusr2@'localhost' identified by 'osmspass2' ;
grant select on osmsdb.* to osmsusr2@'%' identified by 'osmspass2' ;
flush privileges;

Save the script as osmsample.sql.

Import the script by entering the following command:

# mysql –u root –p < osmsample.sql

At the prompt, enter the database password.

The following results are displayed:

# mysql -u root -p < osmsample.sql
Enter password:
customerID	name		purchased		product	productID
1		Jenny	2006-07-07 13:00:33	HP Laptop nc6220	5001
2		Kate		2006-07-09 09:38:12	HP LaserJet 5M  9076
id		depno	name		cardnumber
1		10			Mike		100081
2		10			Tom		100082
3		10			Jack		100083
4		20			Mary		100084
5		20			Cherry	100085
6		20			Jones	100086
#