Sunday, 9 February 2014

MySQL on Ubuntu

By Tech Zone 4 IT at 08:03    No comments

MySQL on Ubuntu

MySQL Installation

Install relevant the packages:
$ sudo apt-get install mysql-client mysql-server
The Ubuntu post-installation presents a dialog three times 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
The installation process starts the MySQL service automatically. 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 MySQL databases are held in the directory /var/lib/mysql. If you ever need to do so, the helper script mysql_install_db is executed to perform the initial setup which creates to create themysql (administrative) database and the root user.

The MySQL shell client

Assuming that the MySQL installation directory's bin subdiretory in your PATH, you can use the mysql executable to connect to and deliver SQL commands to your MySQL database via a command-line SQL interpreter.
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 asroot 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.
To run some basic tests it's useful to have an unpriviledged, password-less, "guest" user which can access a test database. On other systems, the MySQL initialization may create the testdatabase which can be accessed by any MySQL user. An anonymous user may also often created. The recent Ubuntu versions do none of these creations.
The following sequence of commands are meant to create some basic setup features and show the effects (highlighted ones most important): There are a number of repeated commands which can easily be accessed by using the "up arrow" key.
$ mysql -u root
mysql> show databases;
mysql> create database test;                         (it may already exist)
mysql> show databases;
mysql> select user,host,password from mysql.user;
mysql> create user guest@localhost;                  (it may already exist)
mysql> select user,host,password from mysql.user;
mysql> select user,host from mysql.db;
mysql> grant all on test.* to guest@localhost;
mysql> select user,host from mysql.db;
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:
  1. The simple, unadorned -p option, with prompt:
    $ mysql -p  -u ....
Enter password: MY-PASSWORD
  2. The -p option with password appended (useful for testing, but not a good idea in general):
    $ mysql -pMY-PASSWORD  -u ....

Discussion points

Creating a MySQL users with a password

If you want to create a user some_user with the password some_password, the replacement command would be this:
mysql> create user some_user@localhost identified by 'some password';

Establishing privileges for a mysql user

We saw above how the mysql command-line interpreter can grant privileges by which users can access the databases. Here are some other examples:
  1. To create a new highly-privileged user, priv@host (host the desired entry host):
    $ mysql -u root
mysql> grant all on *.* to priv@host;
  2. To give a user restricted@host "read only" privileges 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 this::
$ mysqladmin -u root reload
There's also the "refresh" command which has a somewhat different outcome.

Backup and reload

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 at the beginning by three factors managed by the administrative mysql database:
  • the user specified by the client
  • the host on which the client operates
  • the database that the client is trying to access
Three tables are consulted to determine access rights:
  1. 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 privileges from the remaining fields in the record.
  2. 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 privileges available to the client when accessing from this host.
  3. 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 privileges available to the client as this particular user.
Of the three tables, The db table is the most common place where privileges are assigned for non-root users since it is the most specific to the database.
Further refinements, which we will omit the discuss of these access privileges, governed by dedicated tables in the mysql database:
  • for different tables within a single database (tables_priv)
  • individual access privileges for columns within a database table (columns_priv)
  • access privileges for stored procedures within a database (proc_priv)
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 privileges and the anonymous user has none. Nevertheless, the anonymous user gains privileges for the test database by matching an entry in the db table.

Using MySQL on an external server

Assume you have a server machine running the MySQL service and you want to connect to it from an external client. This situation is common in an intranet where one might have a dedicated databse server with other clients and or servers using it. It could also be useful on a single computer running virtual machines.
For sake of definiteness, we will assume the server running MySQL is a recent Ubuntu system with Assume that it has a static IP address EXTERNAL_IP_ADDRESS. The client is external and wants to use this MySQL service on this server. Here are the steps necessary:
  1. Deal with the firewall
    If you have a firewall running, then somehow or another, you need to get through to the mysql port (3306) from the outside. On Ubuntu, using the ufw firewall management, look at the output of
    $ sudo ufw status
    to see if there is a firewall, and if it is open for the mysql port (with TCP). If not, you can open it by doing:
    $ sudo ufw allow mysql/tcp
  2. Have MySQL listen on the external network interface
    By default the MySQL server only listens on an internal socket and the localhost network interface. As root, edit the MySQL configuration file, /etc/mysql/my.cnf and look for the bind-address line for the server:
    [mysqld]
...
bind-address            = 127.0.0.1
    Right underneath that line, add this one:
    bind-address            = EXTERNAL_IP_ADDRESS
    We need to restart the service for this to take effect, but we can do it after the next change.
  3. Give access to a user from external host
    The guest user we created above only has access from localhost. We need access from all external clients of interest. The easiest way is to make a guest user with access from any host:
    $ mysql -u root
mysql> drop user guest@localhost;       (if you have already added it)
mysql> create user guest;               (unspecified host means any)
mysql> grant all on test.* to guest;
mysql> select user,host,password from mysql.user;  (host for the guest user is %)
That's it. Now restart MySQL to pick up all the changes:
$ sudo restart mysql
If you have the mysql command-line client program on the client, you would use this form to access the MySQL DBMS on the server:
$ mysql -h EXTERNAL_IP_ADDRESS -u guest test

Tunnelling to the MySQL server port

If you only have access to the server through ssh (port 22), then it is possible to set up a tunnel through ssh to the mysql port (3306) and use the tunnel port as an alternative access. This tunnelling setup would be done on the client machine and so is dependent of the operating system. On Windows the PuTTy client can be set up to do the tunneling. Otherwise, the ssh command itself supports a tunneling setup. One point about tunneling is that the mysql command-line client would no longer function because it does not support external access via an alternative port.
© Robert M. Kline

0 comments:

Post a Comment