Personalize this online document
by providing values relevant to you.
Replace the generic LOGIN by the actual login:
ok
Replace the generic
MACHINE name by its actual name:
ok
Replace the generic
TAZLOGIN by your actual taz login:
ok
Installation
Install relevant the packages:
$ sudo apt-get install mysql-server mysql-client
The Ubuntu post-installation presents a dialog multiple
times (probably 3)
in which you have the option to give the MySQL server a root password.
The reason for the multiplicity of root password entry is that
there are multiple (redundant) hosts: localhost, 127.0.0.1, and
the system hostname.
For simplicity, each time:
tab to OK, press Enter without providing a password
In a production or mulitiuser environment, you would certainly want to
give a password, but it's convenient to not do so when you're learning
how to administer MySQL.
The installation process starts the MySQL service automatically.
The controller script is /etc/init.d/mysql which can be used in the usual ways:
The MySQL databases are held in the directory /var/lib/mysql.
Initially this directory
does not exist and
the helper script mysql_install_db is executed to perform the
initial setup which creates to create the mysql (administrative)
database and the root user.
The MySQL adminstrator's user name is "root".
The MySQL DBMS contains an adminstrative database named
mysql
in which all access information is stored.
The root user has global priviledges to do any modifications
or additions.
We only ever want to use the administrative database, mysql,
for it's intended purpose, to control access to other databases.
Furthermore,
we usually want to avoid accessing the MySQL DBMS as root user
because there is always the remote possibility that we
inadvertently alter the tables in the
mysql database and thereby foul up the DBMS
—
not every author shares my reticence of being MySQL root user.
The MySQL shell client
To run some basic tests
we create an unpriviledged, password-less, "guest" user
which can access a test database.
On various systems, the MySQL initialization may create the
test database which can be accessed by any MySQL user.
An anonymous user (empty user name) is also often created.
$ mysql -u root
mysql> create database test;
mysql> create user guest@localhost;
mysql> grant all on test.* to guest@localhost;
mysql> quit
Test the effectiveness by accessing the test database as
the guest user:
$ mysql -u guest test
If for some reason this doesn't work,
try forcing a reload of the administrative
database:
$ mysqladmin -u root reload
Compare the differences in priviledges between the root
and guest users
(the \G statement terminator is used to field information in
list form instead of tabular form):
$ mysql -u root mysql
mysql> select * from user where user='root' and host='localhost' \G
mysql> select * from user where user='guest' and host='localhost' \G
SQL syntax learning examples
Here are some examples you can use to help learn basic SQL commands.
The test database accessible by the guest user
without password is assumed.
Start a command shell and, from the command-line, execute:
$ mysql -u guest
mysql> use test;
mysql> create table things (thing varchar(10), qty int);
mysql> show tables;
mysql> describe things;
mysql> insert into things values ( 'book', 10 );
mysql> insert into things values ( 'pencil', 4 ), ( 'book', 5 );
mysql> select * from things;
mysql> delete from things where thing='book';
mysql> select * from things;
mysql> insert into things values ('table', 2), ('chair',12);
mysql> select * from things;
mysql> update things set qty=qty-1 where qty>10;
mysql> select * from things;
mysql> update things set qty=qty+1 where thing='table';
mysql> select * from things;
mysql> drop table things;
mysql> quit
Using passwords with mysql client
If a user access is password-protected, the mysql client
requires the usage of the -p option to provide the password.
This can be done in one of two ways:
The simple, unadorned -p option, with prompt:
$ mysql -p -u ....
Enter password: MY-PASSWORD
The password-p option with password appended
(useful for testing, but not a good idea in general):