Install

The installation of MySQL is straighforward on debian-like systems. Just type:

sudo aptitude install mysql-server mysql-client -y

In the installation process you'll be asked to set up the password for the admin user. Remember this password because you'll need it in a moment. One thing, though, this username must NOT be used by your applications because it has ALL privileges possible, and you don't want your application to have the right to drop other databases installed in the same MySQL. Limited users must be created for each application.

Once installed you should have it up and running listening the port 3306:

netstat -ptln | grep 3306
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      8935/mysqld

If is not running, use the 'service' command to start/stop it:

service mysql [start/stop]

Configuration files

The configuration files of MySQL are under /etc/mysql and the most important one is the /etc/mysql/my.cnf where you define all the parameters of the daemon. The default parameters are good enough for development, but I'll make a guide in a future post about MySQL tuning.

Console

MySQL is a service, that means that you need a MySQL client to connect to it; there are some visual clients, but you should learn to use the 'mysql' command instead.

To connect to a database you usally want to define:

$ mysql -h localhost -u root -p

The sometimes will see the same syntax without spaces between the modifiers:

$ mysql -hlocalhost -uroot -p

Note that the password is NOT specified on the line. If you leave the -p parameter empty, you'll be requested for a password when connecting. If you put the password in the comand line it will be stored in the history of the shell, in clear text. And you don't want that. NEVER put a cleartext password in the command line, there are always better ways of doing it. More about it in a second.

Once in the console there are some basic commands global and always useful:

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cmdpoint           |
| industrial         |
| mysql              |
| performance_schema |
| sysadmin           |
| test               |
+--------------------+

In order to do operations with a database you need to 'select' it first. To do that, use the 'use' command:

use sysadmin
Database changed

show tables;
+--------------------+
| Tables_in_sysadmin |
+--------------------+
| exercice           |
| server             |
| tag                |
| tag_exercice       |
| user               |
| user_exercice      |
+--------------------+

If you want to know the structure of a table just do:

describe server;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| name        | varchar(150) | NO   | PRI | NULL    |       |
| id          | int(11)      | YES  |     | NULL    |       |
| provider    | varchar(50)  | NO   |     | NULL    |       |
| parent_name | varchar(150) | YES  | MUL | NULL    |       |
| type        | varchar(15)  | NO   |     | NULL    |       |
| status      | varchar(50)  | YES  |     | NULL    |       |
| url_control | varchar(150) | YES  |     | NULL    |       |
| user        | varchar(50)  | YES  |     | NULL    |       |
| password    | varchar(50)  | YES  |     | NULL    |       |
| ip          | varchar(16)  | YES  |     | NULL    |       |
| ssh_port    | int(11)      | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

If you want the DDLs instead:

show create table server;
CREATE TABLE `server` (
  `name` varchar(150) NOT NULL,
  `id` int(11) DEFAULT NULL,
  `provider` varchar(50) NOT NULL,
  `parent_name` varchar(150) DEFAULT NULL,
  `type` varchar(15) NOT NULL,
  `status` varchar(50) DEFAULT NULL,
  `url_control` varchar(150) DEFAULT NULL,
  `user` varchar(50) DEFAULT NULL,
  `password` varchar(50) DEFAULT NULL,
  `ip` varchar(16) DEFAULT NULL,
  `ssh_port` int(11) DEFAULT NULL,
  PRIMARY KEY (`name`),
  KEY `parent_name` (`parent_name`),
  CONSTRAINT `server_ibfk_1` FOREIGN KEY (`parent_name`) REFERENCES `server` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Console tips

If you use a lot the mysql client, you may find useful some of this tips:

Using external comands via 'pager'. If you run:

pager less

Every result of a query will be rendered on a less command. This is really useful if you want to find substrings in the result, of see in a more easy way the result, being able to do vertical and horizontal scroll. Really useful.

Another tip for when you are interested in substrings in the resulting query. You can filter the results using grep like:

pager grep banana

This will filter the output with the sentences that include 'banana'. To disable the feature write 'pager' alone.

Using 'tee' like in the *nix like utility. When you want to save the output of your session to a file, just execute the following command and everything will be recorded:

tee /tmp/queries.log

If you want to compare if two tables contain the exact same information, you can use:

pager md5sum

Then execute the select agains both tables and see if the result is the same. I recommend to use order by on the query, though, because sometimes on different databases the ordering of the result can be done different if any order by is specified.

You can change the format in which the output of a SQL query is displayed. See:

mysql> select * from server\g
+------------+------+------------+-------------+------+--------+-------------+------+-----------+--------------+----------+
| name       | id   | provider   | parent_name | type | status | url_control | user | password  | ip           | ssh_port |
+------------+------+------------+-------------+------+--------+-------------+------+-----------+--------------+----------+
| dockertest | NULL | virtualbox | NULL        | vps  | active |             | jan  | qweqweqwe | 192.168.1.38 |       22 |
+------------+------+------------+-------------+------+--------+-------------+------+-----------+--------------+----------+


mysql> select * from server\G
*************************** 1. row ***************************
       name: dockertest
         id: NULL
   provider: virtualbox
parent_name: NULL
       type: vps
     status: active
url_control: 
       user: jan
   password: qweqweqwe
         ip: 192.168.1.38
   ssh_port: 22

The "\g" is equivalent at the ";" and the \G modifier displays the result vertically.

.myconf

I've promised to show you a way of not having to put the password in cleartext in the CLI.

There is a way of doing that by creating a configuration file; I've already wrote about it in the past so here is the post. After reading it, I have an extra tip for you:

There is an option to prevent catastrophes by limiting the use of the UPDATE command; the limit applied is that it MUST have a WHERE statement. If you don't put it the query won't be executed.

If you add the configuration rule 'safe-updates' in your .my.cnf file you will be safe(er).

Limting users

You don't log in to your *nix systems as root (right?!!!!). The reason is because... there is not reason to. 'root' user has too many privileges, not required in your daily usage, so the right approach is to use a limited account and scale privileges when required.

The same happens in MySQL. If you are using a user that is using a 'grant all privileges' statement you are doing it wrong.

The documentation defined all the possible privileges that you can assign to a user. I strongly recommend you to read the hole page. If don't don't want to read the page, read ahead; I've made a selection of privileges that may fit the 90% of the use cases of MySQL.

First of all, let's see how a user is created:

CREATE USER 'new_user'@'host_name' IDENTIFIED BY 'password';

Example:

CREATE USER 'jan'@'%' IDENTIFIED BY '1234';

The 'new_user' is the username. No funny characters and limited to 16 characters. Beware! The limit of MySQL user name length is hard-coded in the MySQL servers and clients, try to circunvent it by modifing the definitions of the tables DOES NOT WORK (you are not THAT smart, after all...).

The 'host_name' section is from which IP you can access to. '%' character is used as wildcard such as '' in bash or '.' in regular expressions. You could define a host_name like '192.168.%' to let all the people in the private range of your company to access. The '%' means 'allow this user from everywhere. Try to set the most restrictive permission, but if you don't really know from where you'll access, just put the '%' and filter the 3306 mysql port with iptables or alike.

About the password, its recommended to use only ASCII characters, because if not you may have problems with enconding (as always in this life).

So, if we execute the previous query, as user root we'll create a user. If we show the permissions of this user by executing:

show grants for 'jan'@'%';
GRANT USAGE ON *.* TO 'jan'@'%' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF'

As you can see the password is no longer visible (hashed) and you have the USAGE grant. USAGE means.... well, nothing. As the documentation says:

 The USAGE privilege specifier stands for “no privileges.” It is used at
 the global level with GRANT to modify account attributes such as resource
 limits or SSL characteristics without affecting existing account
 privileges.

So USAGE, despite it may seem it's 'no privilege'.

Which grants should you use:

GRANT SELECT, INSERT, UPDATE, DELETE, DROP, ALTER, INDEX, CREATE TEMPORARY TABLES ON sysadmin.* TO 'jan'@'%';

And that will be it. If you see the permissons table in the link above, this query grants privileges for all the privileges that have, as context 'tables' and 'columns'. Again, the 95% of the application should behave allright with this permissions.

The 'jan'@'%' is the users to which the permissions are assigned to, and the 'sysadmin.' means, "all this permissions are applied to the DB 'sysadmin'. If you want a user to have permissions to all the databases you can use '.*' but I advise you otherwise.

And this is it for today. In the second part of this post I'll teach you how to properly do backups in MySQL, using different approaches.

Stay tuned.