SQL Database (MariaDB)

MariaDB vs. MySQL

MariaDB is a fork of MySQL, by some lead MySQL developpers, following acquisition of the later by Oracle. It intends to be a free drop-in replacement for MySQL under GPLv2 licene

MariaDB matches MySQL API and commands, and as such, either MySQL or MariaDB can be used.

Server installation and configuration

Installation

There are 3 majors components that needs to be installed to use SQL database: 

sudo apt install mariadb-server mariadb-client php-mysql
sudo systemctl restart apache2

Security

SQL installation already creates specific user/group 'mysql' that runs the database Server, so that if the SQL database is compromised, effect will be limited by OS access control mechanism.

However, by default, the database is configured for ease of use, and that could be abused to grant access to restricted information. MariaBD and MySQL provide a script to secure the database installation that performs the following actions: 

sudo mysql_secure_installation

For added security, each database client should use a dedicated user that only has access to its relevant databases, tables and SQL commands.

A user is created as follow:

sudo mysql -u root -p
Enter password: ********
CREATE USER 'www-data'@'localhost' IDENTIFIED BY '########';
Query OK, 0 rows affected (0.00 sec)
FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
SELECT user,host,password,plugin FROM mysql.user;
+----------+-----------+-------------------------------------------+-------------+
| user     | host      | password                                  | plugin      |
+----------+-----------+-------------------------------------------+-------------+
| root     | localhost | *8106731ABDA8D2909CCAD3A43F5F987F9153D4C0 | unix_socket |
| www-data | localhost | *2C12975A747374CDBC5DB61E876BEEB280989266 |             |
+----------+-----------+-------------------------------------------+-------------+
2 rows in set (0.00 sec)
quit

Note: Linux user www-data and SQL user www-data are not related, the same name is only used for convenience.

Database initialisation

It is time now to create a database and give access to its user

mysql -u root -p
Enter password: ********
CREATE DATABASE web;
Query OK, 1 row affected (0.00 sec)
GRANT CREATE, DROP, INSERT, SELECT, UPDATE, DELETE ON web.* TO 'www-data'@'localhost';
Query OK, 0 rows affected (0.00 sec)
FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
quit

User 'www-data' can now use the 'web' database.

mysql -u www-data -p########
CREATE TABLE web.links (
  id INT NOT NULL AUTO_INCREMENT,
  url  VARCHAR(200) NOT NULL,
  name VARCHAR(50),
  last_access DATETIME,
  PRIMARY KEY (id)
);
Query OK, 0 rows affected (0.14 sec)

INSERT INTO web.links (url, name, last_access)
       VALUES ("https://www.tognoli.fr/", "Home", "2017-05-14 08:45:30.00");
Query OK, 1 row affected (0.02 sec)

INSERT INTO web.links (url) VALUES ("https://www.google.com");
Query OK, 1 row affected (0.04 sec)

SELECT * from web.links;
+----+-------------------------+------+---------------------+
| id | url                     | name | last_access         |
+----+-------------------------+------+---------------------+
|  1 | https://www.tognoli.fr/ | Home | 2017-05-14 08:45:30 |
|  2 | https://www.google.com  | NULL | NULL                |
+----+-------------------------+------+---------------------+
2 rows in set (0.00 sec)

Command-line SQL client

Database administration

To check the status of the SQL server:

systemctl status mysql

To invoke SQL client:

# mysql [-h host] [-u user] [-p[password]]
mysql -u root -p
Enter password: ********
mysql -u user -p######

To show SQL user information (as root)

SELECT User, Host, Password from mysql.user;
+------------------+-----------+-------------------------------------------+
| User             | Host      | Password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *4306E4D2162FCC73240A10B0FED25CC3CA7F97A1 |
| root             | 127.0.0.1 | *4306E4D2162FCC73240A10B0FED25CC3CA7F97A1 |
| root             | ::1       | *4306E4D2162FCC73240A10B0FED25CC3CA7F97A1 |
| debian-sys-maint | localhost | *D08EE04D9B2FD61F784DCCA6E6C05ECC7887C39B |
| www-data         | localhost | *F5B06B46C6ECEF11AA276665B91F62DC4ACDA301 |
+------------------+-----------+-------------------------------------------+
5 rows in set (0.01 sec)

SHOW GRANTS FOR 'www-data'@'localhost';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for www-data@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'www-data'@'localhost' IDENTIFIED BY PASSWORD '*F5B06B46C6ECEF11AA276665B91F62DC4ACDA301' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `web`.* TO 'www-data'@'localhost'                         |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Data structure

SHOW: Check the database structure:

SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| web                |
+--------------------+
2 rows in set (0.00 sec)

SHOW TABLES FROM web;
+---------------+
| Tables_in_web |
+---------------+
| links         |
+---------------+
1 row in set (0.01 sec)

SHOW COLUMNS FROM web.links;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| url         | varchar(200) | NO   |     | NULL    |                |
| name        | varchar(50)  | YES  |     | NULL    |                |
| last_access | datetime     | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

DROP TABLE web.links;
DROP DATABASE web;
DROP USER 'www-data'@'localhost';

Data manipulation

CREATE DATABASE howto;

CREATE TABLE howto.lists (
  id        INT NOT NULL AUTO_INCREMENT,
  name      VARCHAR(32) NOT NULL,
  sort_type ENUM('creation_date', 'due_date', 'name', 'completed') DEFAULT 'creation_date',
  sort_dir  ENUM('ascending', 'descending') DEFAULT 'ascending',
  show_all  BOOLEAN DEFAULT true,
  parent    INT,
  icon      VARCHAR(64),
  PRIMARY KEY(id)
);

CREATE TABLE web.items (
  id       INT NOT NULL AUTO_INCREMENT,
  owner    VARCHAR(16),
  public   BOOLEAN DEFAULT false,
  name     VARCHAR(32) NOT NULL,
  descr    VARCHAR(255),
  created  DATETIME DEFAULT CURRENT_TIMESTAMP,
  modified DATETIME ON UPDATE CURRENT_TIMESTAMP,
  start    DATETIME,
  due      DATETIME,
  finish   DATETIME,
  effort   INT,
  done     INT,
  position INT,
  period   ENUM('daily', 'weekly', 'monthly'),
  parentId INT,
  childsId INT,
  icon     VARCHAR(64),
  PRIMARY KEY(id)
);

insert into items(name) values ("Buy apples")
update items set name="Buy tomatoes" where id='1';
select * from items

insert into items(name) values ("Buy apples");
update items set name="Buy tomatoes"  where id='1';
select * from items;

Database modifications

Adding a column to a table

ALTER TABLE orders ADD icon VARCHAR(32) AFTER value;

Renaming/modifying a column

ALTER TABLE orders CHANGE shippedBy shipBy varchar(16);

Database backup / migration

Backup

sudo mysqldump --all-databases | gzip > backup-`date  +%Y-%M-%d`.sql.gz
gunzip < backup-*.sql.gz | sudo mysql/span>

Old server

To backup the database:

sudo mysqldump --quick web -u root -p | gzip > db_web.gz
scp db_web.gz user@host:/home/user
rm db_web.gz

New server

To restore the database:

sudo mysqladmin create web
gunzip < db_web.gz | sudo mysql web
27-Nov-2020