MYSQL: les bases

6 minute de lecture

Mis à jour :

Base de Données: généralités

MYSQL fait partie des Systèmes de Gestion de Base de Données Relationnelle SGBDDR.

Les SGBDDR permettent de gérer, stocker, et manipuler des données à travers un langage normalisé: le SQL.

Il faut comprendre ici, que les mécanismes mis en place permettent de protéger les données, car elle ne sont manipulable qu’au travers de SQL.

Langage SQL

Il existe 4 groupes permettant d’agir sur une base de données.

Data Definition Langage: DDL

  • CREATE
  • ALTER
  • DROP
  • RENAME

Data Manipulation Langage

  • INSERT
  • DELETE
  • UPDATE

Data Query Langage

La principale…

  • SELECT

Data Controle Langage

  • GRANT
  • REVOQUE
  • COMMIT
  • ROLLBACK

Verification du service

Avant de tenter de se connecter vérifions que le service soit bien actif.

# service mysql status
● mariadb.service - MariaDB database server
   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
   Active: active (running) since Thu 2018-06-28 16:45:36 CEST; 3min 14s ago
 Main PID: 5995 (mysqld)
   Status: "Taking your SQL requests now..."
    Tasks: 26 (limit: 4915)
   CGroup: /system.slice/mariadb.service
           └─5995 /usr/sbin/mysqld

juin 28 16:45:35 gally-reborn systemd[1]: Starting MariaDB database server...
juin 28 16:45:36 gally-reborn mysqld[5995]: 2018-06-28 16:45:36 140478532932160 [Note] /usr/sbin/mysqld (mysqld 10.1.26-MariaDB-0+deb9u1) starting as process 5995 ...
juin 28 16:45:36 gally-reborn systemd[1]: Started MariaDB database server.

A priori ça tourne..

Sécurité

Il existe un script permettant de sécuriser l’installation de la base de données MYSQL.

Commande

En root entrer la commande suivante: mysql_secure_installation

Vérification du mot de passe ROOT

Un message de mise en garde puis on s’occupe du mot de passe ROOT

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] n
 ... skipping.

Utilisateur Anonyme

By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] Y
 ... Success!

Accés ROOT distant

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] Y
 ... Success!

Base TEST

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] Y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Privilege table

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] Y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

Voilà ; la base MYSQL est sécurisée .

Le script aura :

  • vérifié:
    • Le mot de passe ROOT
  • Détruit
    • Le compte Anonyme
  • Désactivé:
    • l’accés ROOT distant
  • Détruit:
    • La base test
  • Validé:
    • Les changements immédiatement

Connexion

Voici la commande pour se connecter à la base MYSQL: mysql --user=root -p

-p indique qu’il faut un mot de passe.

# mysql --user=root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.1.26-MariaDB-0+deb9u1 Debian 9.1

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Le prompt

Le prompt de connexion manque un peu de détail.

Entrer cette commande à la suite du prompt: \R \U [\d]

Résultat:

MariaDB [(none)]> \R \U [\d]
PROMPT set to '\U [\d]'
root@localhost [(none)]

Il est possible de configurer ce comportement directement dans /etc/mysql/conf.d/mysql.cnf

Il s’y trouve une unique section [mysql] en dessous de laquelle il faut indiquer simplement: prompt = \U [\d]> \_

Et on se reconnecte:

# mysql --user=root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.1.26-MariaDB-0+deb9u1 Debian 9.1

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]>

Menu Aide

comme indiqué une aide est disponible via la l’option \h , ou --help.


root@localhost [(none)]> \h

General information about MariaDB can be found at
http://mariadb.org

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

Voilà pour un rapide apperçu de mysql, dans la partie 2 , nous allons voir comment créer du base de données et des tables.

Voir très rapidement des principe du sql par l’exemple.