Figure 4.5 Dropping a database using mysqladmin.
You may have noticed that when using mysqladmin, you are prompted before deleting the database.
This is very helpful for the beginning database administrator, as well as the seasoned veteran. It allows
one last moment of reflection before all your data is lost.
The CREATE and DROP arguments of the mysqladmin utility are not case sensitive, but the name of the
database is case sensitive. Another notable point is that you must have the authority to use CREATE
and DROP. As root, you have this authority, but if you are not an administrator, you will not be able to
use these commands.
Adding Users
Now that you have your database up and running, you should give other users the ability to use the
database. Today, you will learn how to add users; explaining permissions and user privileges are covered in
more detail on Day 17, "MySQL Database Security."
To allow a user from your local machine—referred to hereafter as localhost—to gain access to your
database, the user must exist in several places. The MySQL RDBMS contains a database named
mysql. This database holds all the permissions for all MySQL databases. This database consists of the
following tables:
User The table that holds all the names, passwords, hosts, and privileges of all the
users of this MySQL RDBMS
db The table that contains all the users, databases, and hostnames for this MySQL
RDBMS.
host The table that contains all hostnames, databases, and privileges they hold for
this MySQL RDBMS
For a person to use your database, the hostname of the machine from which he or she will be
connecting must exist in the host table. The user must exist in the user table, and the database must
exist in the db table. Complete the following steps to give another user the ability to use your database
from the local machine.
- First, make sure the daemon is running and that you are currently in the mysql
directory. - Add the hostname and database to the host table. To do this, you must use the
MySQL monitor. - bin/mysql –p
- Next, you must make the mysql database the active database. To do this, type the
following: - USE mysql;
Note Remember, commands are not case sensitive, but the database objects
are. - To add the hostname/database combination to this MySQL RDBMS, you must use an
SQL INSERT command. Type the following from the command line: - INSERT INTO mysql VALUES('localhost','sample_db',
- 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
Remember that if you do not type a a \g, the MySQL monitor will continue your statement on the
following line. This is helpful because it allows for easily readable commands, and, if you make a
mistake, you can use the history key to bring it back.
Your output should look like that in Figure 4.6.