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):
$ mysql -pMY-PASSWORD -u ....
Navicat
Navicat is a good GUI tool for manipulating databases. The "Lite" version
is free for non-commercial usage. Go to Navicat's download site:
Install it into /usr/local as follows
(assuming the download goes to Desktop):
$ cd ~/Desktop
$ sudo tar xvozf navicat8lite_mysql_en.tar.gz.tar.gz -C /usr/local
For your information about the tar command
options, "xz" is used
for extracting a ".tar.gz" file; "f", being adjacent to
the target file indicates to use this file; "o" makes the
extracted files be owned by root (rather than some arbitrary,
non-existent user id); "v" (verbose) means show me what's happening.
Make a desktop launcher for Navicat.
Right-click on the desktop and select Create Launcher,
use the default Type of Application and
set the Name to navicat.
Set the Command from the Browse button
(go through the File System) to:
Click on the icon (upper left) to replace it. Browse to
/usr/local/navicat8lite_mysql_en.tar.gz/, select this folder, and
in the next popup select the navicat8_mysql.png image.
Click OK.
Create a root connection and clean up the mysql database
Start it up and press the Connection button.
Set the Connection Name to root.
Everything else should be in place already.
Click the Test Connection button to double-check.
Make sure the Save Password checkbox is checked.
Double-click on the root connection on the left-hand side to open it and
see the databases (mysql and, perhaps, test).
Open the mysql database, find the users table
and delete all of the following records
those for which the host field is not explicitly
"localhost"
those with empty user field
You can achieve the same effect using the mysql shell client
with the following:
$ mysql -u root
mysql> delete from user where host != 'localhost' or user = '';
Discussion points
Creating a MySQL users with desired access
We saw above how the mysql command-line interpreter
can grant priviledges by which users can access the databases.
Here are some other examples:
To create a new priviledged user, priv@host
(host the desired entry host):
$ mysql -u root
mysql> grant all on *.* to priv@host;
To give a user restricted@host
"read only" priviledges on somedb, we replace
the "all" in grant by the desired restricted access:
$ mysql -u root
mysql> grant select on somedb.* to restricted@host;
MySQL Command-line administration
MySQL software provides a number of useful commands to
manipulate its databases, including:
mysqladmin: basic administration commands
mysqldump: dump the contents of table(s) in a database
mysqlshow: show table/fields in a database
mysql command line interpreter for entering SQL commands
You can see the entire set of choices from the shell using
tab completion by doing:
$ mysql[TAB][TAB]
For example, try these commands:
$ mysqlshow -u root
$ mysqlshow -u root mysql
$ mysqlshow -u root mysql user
$ mysqldump -u root mysql user
To ensure that changes made to the mysql database are not picked up,
do one of these:
The mysql commands provide an excellent scheme by which
a database can be "backed up" and then "reloaded".
Do the backup like this:
$ mysqldump -u root somedb > somedb.sql
The somedb.sql file contains
the data in all the tables
plus the commands needed
to recreate these tables.
A restoration from an non-existent database would be done
like this:
$ mysql -u root
mysql> create somedb
mysql> use somedb
mysql> source somedb.sql
MySQL access principles
MySQL is a network-oriented DBMS. Client programs may
reside on different hosts than the server.
The access rights of MySQL client has to a MySQL DBMS database
is determined by three factors:
the user specified by the client
the host on which the client operates
the database that the client is trying to access
The mysql database consults three tables to determine access rights:
user table:
When the client on host connects to the MySQL server, the pair
(user, host)
is matched against the (user,host) values in the
rows of the user table.
If no such user or host exists, an empty
is used to match. If the password field is non-empty
the client must provide the password.
The client obtains global priviledges from the remaining fields
in the record.
host table:
When a client on host attempts to use a specific database, the pair:
(host, database)
is used to match against the entries in the host table, and
thereby augment the priviledges available to the
client when accessing from this host.
db table:
Each database, with user information is listed in the db table.
(user, database)
is used to match against the entries in the db table, and
therefore further augment the priviledges available to the client
as this particular user.
Of the three tables,
The db table is the most common place where priviledges are assigned
for non-root users since it
is the most specific to the database.
A few tests exhibit some of the ideas discussed here:
$ mysql -u root mysql
mysql> describe user;
mysql> select user,host,password from user;
mysql> describe host;
mysql> select host,db from host;
mysql> describe db;
mysql> select user,db from db;
mysql> select * from db where db='test' \G (mostly 'Y')
The root user has all global priviledges
and the anonymous user has none. Nevertheless,
the anonymous user gains priviledges for the test
database by matching an entry in the db table.