Ceci est une ancienne révision du document !
Table des matières
Version: 2020/01/30 03:29
DBA201 - Présentation, Installation et Configuration
Présentation de MySQL
MySQL comprend les outils suivants :
- Un Serveur SQL
- Un moteur qui permet d'accéder aux bases de données.
- Les programmes clients pour accéder aux serveurs
- Un programme interactif permet de saisir directement les requêtes et d'afficher les résultats
- Plusieurs utilitaires et outils d'administration vous permettent de gérer votre site.
- Une bibliothèque client pour écrire vos propres programmes
- Les programmes clients peuvent être écrits en C, car la bibliothèque est elle-même écrite en C.
- Elle intègre également toutes les bases nécessaires pour supporter d'autres langages.
Développement
Le développement de MySQL depuis son rachat par la société Oracle en 2009 produit des versions différentes selon le cycle suivant :
- Création de une ou plusieurs versions successives dites DMR (Development Milestone Release) destinée(s) à être testée(s) par des personnes intéressées par des nouvelles fonctionnalités,
- La sortie d'une ou de plusieurs versions successives dites RC (Release Candidate) qui sont des versions qui deviendront stables,
- La sortie d'une version dite GA ((Generally Available) qui correspond à la version stable actuelle.
Important - A noter que MySQL existe en version 32 bits et 64 bits.
MySQL existe en deux versions, la version communautaire qui est disponible sous la licence GNU GPL v2 et la version Enterprise qui :
- bénéficie du support d'Oracle,
- contient des outils tels Enterprise Monitor, Query Analyzer et Enterprise Backup.
Important - A noter qu'à un instant t, la version communautaire et la version Entreprise sont indentique.
Ce cours se concentre sur la version communautaire.
Protocoles de Communication
MySQL propose 4 protocoles de communication :
| Protocole | Connexion | OS | Commentaire |
|---|---|---|---|
| TCP | Local et distant | Unix, Windows | Seul protocole de connexion à distance |
| Socket Unix | Local | Unix | Protocole par défaut pour les connexions locales |
| Shared Memory | Local | Windows | Zone de mémoire partagée entre le serveur et le client |
| Named Pipes | Local | Windows | Fichier permettant deux processus sans lien de parenté de communiquer |
Important - Le port TCP par défaut de MySQL est le 3306. Il est possible de changer le port de communication de MySQL en éditant le fichier my.cnf. Pour se connecter sur un serveur distant il convient d'utiliser la commande suivante mysql -u root -p -h nom_serveur - -protocol=tcp.
Architecture
L'architecture est la suivante :
- Le serveur reçoit une requête du client,
- Le serveur regarde dans le cache des requêtes, si activé,
- Si la requête s'y trouve, le serveur renvoie le résultat du requête stocké dans le cache,
- Si la requête ne s'y trouve pas, le serveur analyse et optimise la requête avant de l'exécuter en utilisant le moteur d’exécution des requêtes, le met dans le cache et retourne le résultat au client.
MariaDB
MariaDB est un système de gestion de base de données édité sous licence GPL. Il s'agit d'un fork communautaire de MySQL. La gouvernance du projet est assurée par la fondation MariaDB, et sa maintenance par la société Monty Program AB, créateur du projet.
En 2009, à la suite du rachat de MySQL par Sun Microsystems et des annonces du rachat de Sun Microsystems par Oracle Corporation, Michael Widenius, fondateur de MySQL, quitte la société afin de lancer le projet MariaDB.
Wikipédia annonce, fin 2012, la migration de MySQL à MariaDB8. En septembre 2013 Google annonce l'adoption de MariaDB et affecte un de ses ingénieurs à la Fondation MariaDB.
Un consortium baptisé Open Database Alliance a été créé pour assurer le développement du logiciel.
Utilisation du Disque
Les bases de données de MariaDB, aussi appelées Schémas sont représentées sur disque par un répertoire du même nom que la base de données.
Ce répertoire existe dans un répertoire de données appelé le datadir.
Pour connaître l'emplacement du datadir, il convient d'utiliser la commande suivante :
MariaDB [(none)]> SHOW VARIABLES LIKE 'datadir'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | datadir | /var/lib/mysql/ | +---------------+-----------------+ 1 row in set (0.00 sec) MariaDB [(none)]>
L’examen de ce répertoire montre la présence des répertoires des schémas :
[root@centos7 ~]# ls -l /var/lib/mysql total 28704 -rw-rw----. 1 mysql mysql 18874368 Oct 30 15:59 ibdata1 -rw-rw----. 1 mysql mysql 5242880 Oct 30 15:59 ib_logfile0 -rw-rw----. 1 mysql mysql 5242880 Oct 30 10:09 ib_logfile1 drwx------. 2 mysql mysql 4096 Oct 30 11:39 mysql srwxrwxrwx. 1 mysql mysql 0 Oct 30 10:09 mysql.sock drwx------. 2 mysql mysql 4096 Oct 30 10:09 performance_schema drwx------. 2 mysql mysql 6 Oct 30 11:37 test
Important - Il est possible de configurer MariaDB pour utiliser plusieurs supports différents pour stocker les journaux, fichiers temporaires, binaires etc.
Utilisation de la Mémoire
L'allocation de la mémoire est de deux types différentes :
Allocation par Instance
C'est la mémoire allouée par le serveur au démarrage de celui-ci. Elle est partagée par le serveur mariadb et les threads (connexions). Elle comprend, entre autre :
- le key_buffer_size : le cache d'index des tables MyISAM,
- le innodb_buffer_pool_size : le cache de données InnoDB,
- le table_definition_cache et le table_open_cache : le cache des tables,
- le query_cache : le cache des requêtes.
Allocation par Threads
C'est la mémoire allouée d'une manière dynamique en fonction des besoins de chaque client. Elle comprend, entre autre :
- le sort_buffer_size : le buffer de tri,
- le read_buffer_size : le buffer de lecture,
- le tmp_table_size : l'espace mémoire des tables temporaires,
- le max_heap_table_size : l'espace mémoire des tables Memory.
Installation
MariaDB
Pour installer MariaDB, utilisez yum :
[root@centos7 ~]# yum install mariadb mariadb-server
Important Vous pouvez également installer MariaDB à partir des sources disponibles sur le site https://downloads.mariadb.org/.
MySQL
Téléchargez le rpm du dépôt yum :
[root@centos7 ~]# wget https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
Installez le rpm :
[root@centos7 ~]# rpm -ivh mysql57-community-release-el7-9.noarch.rpm
Maintenant installez mysql-server :
[root@centos7 ~]# yum install mysql-server
Démarrage du Serveur
Le serveur MariaDB ou MySQL peut être démarré par l'utilisation d'une de deux méthodes différentes.
Le Service
Le Service MariaDB
Pour activer le service mariadb il convient d'utiliser la commande systemctl :
[root@centos7 ~]# systemctl enable mariadb Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
Le démarrage se fait également avec systemctl :
[root@centos7 ~]# systemctl start mariadb
[root@centos7 ~]# systemctl status mariadb
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2017-10-31 12:34:38 CET; 5s ago
Process: 9769 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
Process: 9737 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
Main PID: 9768 (mysqld_safe)
CGroup: /system.slice/mariadb.service
├─9768 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
└─9930 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql...
Oct 31 12:34:35 centos7.fenestros.loc systemd[1]: Starting MariaDB database s...
Oct 31 12:34:35 centos7.fenestros.loc mariadb-prepare-db-dir[9737]: Database ...
Oct 31 12:34:36 centos7.fenestros.loc mysqld_safe[9768]: 171031 12:34:36 mysq...
Oct 31 12:34:36 centos7.fenestros.loc mysqld_safe[9768]: 171031 12:34:36 mysq...
Oct 31 12:34:38 centos7.fenestros.loc systemd[1]: Started MariaDB database se...
Hint: Some lines were ellipsized, use -l to show in full.
Le service mariadb appel un script appelé mysqld_safe qui lance le serveur et crée un journal d'erreur. Il relance le serveur en cas d'arrêt intempestif.
Le Service MySQL
Le démarrage se fait également avec systemctl :
[root@centos7 ~]# systemctl start mysqld
Le service mysqld appel un script appelé mysqld_safe qui lance le serveur et crée un journal d'erreur. Il relance le serveur en cas d'arrêt intempestif.
Invocation Directe
Il est aussi possible d'invoquer directement le binaire mysqld en spécifiant manuellement le fichier de configuration de MariaDB, le fichier d'erreurs ainsi que le nom de l'utilisateur. Par exemple :
# /usr/libexec/mysqld --defaults-file=/chemin/my.cnf --log-error=/chemin/nom_log --user=mysql &
Arrêt du Serveur
Le Service MariaDB
Il est possible d'arrêter le service mariadb avec la commande stop :
[root@centos7 ~]# systemctl stop mariadb
[root@centos7 ~]# systemctl status mariadb
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Active: inactive (dead) since Tue 2017-10-31 12:38:12 CET; 3s ago
Process: 9769 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
Process: 9768 ExecStart=/usr/bin/mysqld_safe --basedir=/usr (code=exited, status=0/SUCCESS)
Process: 9737 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
Main PID: 9768 (code=exited, status=0/SUCCESS)
Oct 31 12:34:35 centos7.fenestros.loc systemd[1]: Starting MariaDB database s...
Oct 31 12:34:35 centos7.fenestros.loc mariadb-prepare-db-dir[9737]: Database ...
Oct 31 12:34:36 centos7.fenestros.loc mysqld_safe[9768]: 171031 12:34:36 mysq...
Oct 31 12:34:36 centos7.fenestros.loc mysqld_safe[9768]: 171031 12:34:36 mysq...
Oct 31 12:34:38 centos7.fenestros.loc systemd[1]: Started MariaDB database se...
Oct 31 12:38:09 centos7.fenestros.loc systemd[1]: Stopping MariaDB database s...
Oct 31 12:38:12 centos7.fenestros.loc systemd[1]: Stopped MariaDB database se...
Hint: Some lines were ellipsized, use -l to show in full.
[root@centos7 ~]# systemctl start mariadb
[root@centos7 ~]# systemctl status mariadb
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2017-10-31 12:38:25 CET; 1s ago
Process: 11254 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
Process: 11222 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
Main PID: 11253 (mysqld_safe)
CGroup: /system.slice/mariadb.service
├─11253 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
└─11420 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysq...
Oct 31 12:38:22 centos7.fenestros.loc systemd[1]: Starting MariaDB database s...
Oct 31 12:38:23 centos7.fenestros.loc mariadb-prepare-db-dir[11222]: Database...
Oct 31 12:38:23 centos7.fenestros.loc mysqld_safe[11253]: 171031 12:38:23 mys...
Oct 31 12:38:23 centos7.fenestros.loc mysqld_safe[11253]: 171031 12:38:23 mys...
Oct 31 12:38:25 centos7.fenestros.loc systemd[1]: Started MariaDB database se...
Hint: Some lines were ellipsized, use -l to show in full.
La Commande mysqladmin
La comande mysqladmin peut aussi être utilisée pour arrêter le serveur à condition que l'utilisateur qui l'invoque possède le privilège shutdown. Par exemple :
[root@centos7 ~]# /bin/mysqladmin -uroot -p shutdown Enter password: [root@centos7 ~]# systemctl status mariadb ● mariadb.service - MariaDB database server Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled) Active: inactive (dead) since Tue 2017-10-31 12:53:09 CET; 4s ago Process: 11254 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS) Process: 11253 ExecStart=/usr/bin/mysqld_safe --basedir=/usr (code=exited, status=0/SUCCESS) Process: 11222 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS) Main PID: 11253 (code=exited, status=0/SUCCESS) Oct 31 12:38:22 centos7.fenestros.loc systemd[1]: Starting MariaDB database s... Oct 31 12:38:23 centos7.fenestros.loc mariadb-prepare-db-dir[11222]: Database... Oct 31 12:38:23 centos7.fenestros.loc mysqld_safe[11253]: 171031 12:38:23 mys... Oct 31 12:38:23 centos7.fenestros.loc mysqld_safe[11253]: 171031 12:38:23 mys... Oct 31 12:38:25 centos7.fenestros.loc systemd[1]: Started MariaDB database se... Hint: Some lines were ellipsized, use -l to show in full.
Important - Le nom d'utilisateur peut être accoler ou non à l'option -u. Par exemple -uroot et -u root sont tous les deux correctes. Par contre, si vous souhaitez spécifier le mot de passe de l'utilisateur dans la ligne de commande, celui-ci doit être accoler à l'option -p.
Configuration
Votre première prise en mains de MariaDB doit débuter par la commande mysql :
Le Client MySQL
Utilisation
MariaDB dispose d’un outil client permettant de se connecter et d’envoyer des commandes SQL au serveur.
Pour démarrer une connexion au serveur MariaDB nous pouvons utiliser un C.L.I. sous Linux.
Les paramètres les plus courants sont :
$ mysql –u root –p Databasename [Entrée]
Où :
- -u
- donne le nom de l’utilisateur. Si vous renoncez à l’option –u , le nom de login sera utilisé sous Unix/Linux et le nom ODBC sous Windows.
- -p
- permet de saisir le mot de passe de MariaDB, cette option est obligatoire lorsque les utilisateurs MariaDB sont sécurisés, c’est-à-dire qu’ils ont un mot de passe.
- -h
- permet de préciser le nom de l’hôte qui héberge MariaDB. Par défaut le port de communication est 3306 il est possible de changer ce port soit en configurant l’instance MariaDB soit en configurant le fichier My.ini sous Windows™ ou my.cnf sous Unix/Linux. (Attention: vérifiez qu’un par feu ne bloque pas ce port). Enfin vous devez vous assurer que le serveur MariaDB est configuré de sorte à autoriser les accès depuis votre ordinateur local.
- - -protocol = name
- cette option permet de spécifier le protocole de communication à utiliser. Il est rarement nécessaire d’indiquer cette option, puisque MariaDB choisit le bon protocole par défaut. Lorsque le client mysql et le serveur MariaDB ne tournent pas sur le même ordinateur, le seul protocole réseau possible est le TCP, dans ce cas n’oubliez pas de préciser le paramètre –h.
- -P n
- permet de préciser le port de communication utilisée pour se connecter à MariaDB. Cette option est effective que lorsque la communication passe par TCP/IP.
- - -default-character-set = nom
- cette option indique le jeu de caractères utilisés dans le cadre de communication entre mysql et le serveur MariaDB. En théorie, il s’agit du même jeu de caractères que celui utilisé par défaut dans la vie de commandes sous Windows ou la console sous Linux. Les jeux de caractères pris en charge par MariaDB sont notamment Latin 1 (ISO-8559-1), latin 2 (ISO-8559-2), UTF-8 (unicode) et cp850 (le jeu de caractères DOS pour l’Europe occidentale).
- Databasename
- ce dernier paramètre indique à MariaDB le nom d’une base de données, ce qui permet de l’utiliser directement dès l’ouverture de MariaDB. Si vous désirez changer ici de base de données après sa connexion vous pouvez utiliser la commande SQL USE <NOM_DB>. Exemple : $ mysql -u root -p -h server –protocol=tcp [Entrée] $ Password : [Entrée]
Options
Dans la console MariaDB nous avons diverses options.
| Abréviation | Commande | Signification |
|---|---|---|
| \c | clear | Annule une commande en cours de saisie |
| \h | help | Affiche la liste des commandes |
| \q | exit ou quit | Ferme la connexion à MariaDB. Sous Unix/Linux, il est possible utilisé le raccourci ctrl+D |
| \s | status | Affiche les informations de statut du serveur MariaDB |
| \T[f] | tee[filename] | Enregistre tous ce qui apparaît dans la fenêtre de commandes dans le fichier indiqué |
| L | notee | Ferme tee. Le protocole doit être repris à tout instant avec tee ou \T. Il n’est pas nécessaire de saisir de nouveau le nom de fichier. |
| \u db | use database | La base de données saisie devient la base de données par défaut |
| \. Fn | source file name | exécute les commandes SQL contenues dans le fichier spécifié. Les commandes doivent à séparer par des pointsvirgules. |
NB : la fonction \c n’a aucun effet dans les chaînes de caractères( “” ou ' ').
Notez aussi que MariaDB se souvient des dernières commandes grâce aux touches flèche vers le haut et flèche vers le bas.
LAB #1 - Configuration de Base
Saisissez donc la commande mysql :
[root@centos7 ~]# systemctl start mariadb [root@centos7 ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.56-MariaDB MariaDB Server 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)]>
pour visualiser la liste des bases de données par défaut :
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 7 rows in set (0.01 sec) MariaDB [(none)]>
Ensuite changez de base de données avec la commande :
MariaDB [(none)]> USE mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [mysql]>
Afin de consulter les tables présentes dans la base, utilisez la commande :
MariaDB [mysql]> SHOW TABLES; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | host | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | servers | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 24 rows in set (0.00 sec) MariaDB [mysql]>
Pour consulter une table spécifique, utilisez la commande DESCRIBE :
MariaDB [mysql]> DESCRIBE user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) | NO | | 0 | |
| plugin | char(64) | NO | | | |
| authentication_string | text | NO | | NULL | |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.00 sec)
MariaDB [mysql]>
Pour visualiser la liste des utilisateurs autorisés pour MariaDB, utilisez la commande suivante :
MariaDB [mysql]> SELECT host, user, password FROM user; +-----------------------+-------+-----------+ | host | user | password | +-----------------------+-------+-----------+ | localhost | root | | | centos7.fenestros.loc | root | | | 127.0.0.1 | root | | | ::1 | root | | +-----------------------+-------+-----------+ 7 rows in set (0.00 sec) MariaDB [mysql]>
Vous noterez que l'utilisateur root, l'administrateur de MariaDB, n'a pas de mot de passe !
Il faut par conséquence en définir un en urgence !
Pour sortir de l'administration de MariaDB, utilisez la commande exit :
MariaDB [mysql]> exit Bye [root@centos7 ~]#
Pour définir le mot de passe fenestros pour root, il convient de saisir la commande suivante :
[root@centos7 ~]# mysqladmin -u root password fenestros
Lors de la prochaine tentative de connexion en tant que root, vous obtiendrez un message d'erreur car le mot de passe est maintenant non-null :
[root@centos7 ~]# mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Pour vous connecter en tant que l'administrateur de la base il faut maintenant utiliser la commande suivante :
[root@centos7 ~]# mysql -u root -p mysql Enter password: fenestros Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 5 Server version: 5.5.56-MariaDB MariaDB Server 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 [mysql]>
Important - Notez l'utilisation de l'option -p qui indique à MariaDB que vous souhaitez saisir un mot de passe. Le mot de passe n'est pas mysql mais fenestros, comme démontre l'exemple ci-dessus. Bien entendu le mot de passe fenestros n'apparaît pas réellement en clair. L'utilisation de l'option mysql dans la ligne de commande indique simplement que nous souhaitons se connecter à la base mysql dès la connexion.
Saisissez la commande suivante pour vérifier la table des utilisateurs :
MariaDB [mysql]> SELECT host, user, password FROM user; +-----------------------+-------+-------------------------------------------+ | host | user | password | +-----------------------+-------+-------------------------------------------+ | localhost | root | *00269BA49BEC800F9CCF34C20C1FD83E0236B89A | | centos7.fenestros.loc | root | | | 127.0.0.1 | root | | | ::1 | root | | +-----------------------+-------+-------------------------------------------+ 7 rows in set (0.00 sec) MariaDB [mysql]>
Important - Notez que non seulement le mot de passe de root est présent mais qu'il a été crypté. Vous avez aussi la possibilité de sécuriser votre installation de MariaDB en utilisant le script /usr/bin/mysql_secure_installation.
LAB #2 - Configuration Avancée
La configuration avancée du serveur MariaDB se fait soit :
- en éditant le fichier /etc/my.cnf,
- en passant des paramètres à l'exécutable mysqld,
- en paramétrant le serveur dynamiquement.
Le fichier my.cnf
[root@centos7 ~]# cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid # # include all files from the config directory # !includedir /etc/my.cnf.d
Ce fichier n'est pas cependant le seul endroit où est configuré le serveur. En effet, le serveur lit des directives des fichiers /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf dans l'ordre, comme démontre la sortie de la commande /usr/libexec/mysqld - -help - -verbose :
[root@centos7 ~]# /usr/libexec/mysqld --help --verbose | grep my.cnf
171031 13:10:57 [Note] Plugin 'FEEDBACK' is disabled.
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default
my.cnf files
Important : En cas de duplicité de directives, c'est la dernière lue qui l'emporte. Si vous avez un autre fichier de configuration qui n'est pas un de ceux mentionné, vous pouvez l'utiliser ainsi : /usr/libexec/mysqld –defaults-file=/chemin/votre_fichier.
Le fichier my.cnf est organisé en sections, aussi appelées des groupes. Ces sections font référence au programme concerné par la configuration. Par exemple la section [mysqld] fait référence au serveur, tandis qu'une section [mysql] ferait référence au client en mode texte. Il est aussi possible de trouver les sections suivantes :
- [mysqldump],
- [mysqladmin],
- [mysqlhotcopy],
- [myisamchk],
- [client],
- etc.
Important - [client] est une section qui configure tous les clients. [mysqld] peut être remplacé par [server]. Toute ligne commençant par le caractère # ou ; est un commentaire.
Le fichier de configuration peut aussi contenir des lignes !include qui référencent des fichiers tiers de configuration ainsi que des lignes !includedir qui référencent des répertoires contenant plusieurs fichiers de configuration. Attention, dans ce dernier cas, l'administrateur n'a aucun contrôle sur l'ordre de lecture des fichiers par le serveur :
[root@centos7 ~]# ls -l /etc/my.cnf.d total 12 -rw-r--r--. 1 root root 295 Apr 30 2017 client.cnf -rw-r--r--. 1 root root 232 Apr 30 2017 mysql-clients.cnf -rw-r--r--. 1 root root 744 Apr 30 2017 server.cnf
[root@centos7 ~]# ls -l /etc/my.cnf.d total 12 -rw-r--r--. 1 root root 295 Apr 30 2017 client.cnf -rw-r--r--. 1 root root 232 Apr 30 2017 mysql-clients.cnf -rw-r--r--. 1 root root 744 Apr 30 2017 server.cnf [root@centos7 ~]# cat /etc/my.cnf.d/client.cnf # # These two groups are read by the client library # Use it for options that affect all clients, but not the server # [client] # This group is not read by mysql client library, # If you use the same .cnf file for MySQL and MariaDB, # use it for MariaDB-only client options [client-mariadb] [root@centos7 ~]# cat /etc/my.cnf.d/mysql-clients.cnf # # These groups are read by MariaDB command-line tools # Use it for options that affect only one utility # [mysql] [mysql_upgrade] [mysqladmin] [mysqlbinlog] [mysqlcheck] [mysqldump] [mysqlimport] [mysqlshow] [mysqlslap] [root@centos7 ~]# cat /etc/my.cnf.d/server.cnf # # These groups are read by MariaDB server. # Use it for options that only the server (but not clients) should see # # See the examples of server my.cnf files in /usr/share/mysql/ # # this is read by the standalone daemon and embedded servers [server] # this is only for the mysqld standalone daemon [mysqld] # this is only for embedded server [embedded] # This group is only read by MariaDB-5.5 servers. # If you use the same .cnf file for MariaDB of different versions, # use this group for options that older servers don't understand [mysqld-5.5] # These two groups are only read by MariaDB servers, not by MySQL. # If you use the same .cnf file for MySQL and MariaDB, # you can put MariaDB-only options here [mariadb] [mariadb-5.5]
Chaque section contient des directives au format option=valeur ou au format binaire, par exemple enable-federated.
Avec presque 300 options possibles et avec autant d'emplacements de fichiers de configuration, deux problèmes se posent.
- Comment savoir bien configurer le serveur,
- Comment connaître avec exactitude la configuration actuelle.
Comment Savoir Bien Configurer le Serveur
La réponse au premier problème se trouve dans les fichiers exemples fournis par Oracle. Ces fichiers se trouvent dans le répertoire support-files. Oracle propose des fichiers de configuration en fonction de la mémoire du serveur :
[root@centos7 ~]# ls -l /usr/share/mysql | grep cnf -rw-r--r--. 1 root root 4920 Aug 4 20:28 my-huge.cnf -rw-r--r--. 1 root root 20438 Aug 4 20:28 my-innodb-heavy-4G.cnf -rw-r--r--. 1 root root 4907 Aug 4 20:28 my-large.cnf -rw-r--r--. 1 root root 4920 Aug 4 20:28 my-medium.cnf -rw-r--r--. 1 root root 2846 Aug 4 20:28 my-small.cnf -rw-r--r--. 1 root root 656 Jun 8 16:25 README.mysql-cnf
Par exemple :
[root@centos7 ~]# cat /usr/share/mysql/my-huge.cnf # Example MariaDB config file for very large systems. # # This is for a large system with memory of 1G-2G where the system runs mainly # MariaDB. # # MariaDB programs look for option files in a set of # locations which depend on the deployment platform. # You can copy this option file to one of those # locations. For information about these locations, do: # 'my_print_defaults --help' and see what is printed under # Default options are read from the following files in the given order: # More information at: http://dev.mysql.com/doc/mysql/en/option-files.html # # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the "--help" option. # The following options will be passed to all MySQL clients [client] #password = your_password port = 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-external-locking key_buffer_size = 384M max_allowed_packet = 1M table_open_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 # Point the following paths to a dedicated disk #tmpdir = /tmp/ # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the "enable-named-pipe" option) will render mysqld useless! # #skip-networking # Replication Master Server (default) # binary logging is required for replication log-bin=mysql-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - # the syntax is: # # CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>, # MASTER_USER=<user>, MASTER_PASSWORD=<password> ; # # where you replace <host>, <user>, <password> by quoted strings and # <port> by the master's port number (3306 by default). # # Example: # # CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, # MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then # start replication for the first time (even unsuccessfully, for example # if you mistyped the password in master-password and the slave fails to # connect), the slave will create a master.info file, and any later # change in this file to the variables' values below will be ignored and # overridden by the content of the master.info file, unless you shutdown # the slave server, delete master.info and restart the slaver server. # For that reason, you may want to leave the lines below untouched # (commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted #server-id = 2 # # The replication master for this slave - required #master-host = <hostname> # # The username the slave will use for authentication when connecting # to the master - required #master-user = <username> # # The password the slave will authenticate with when connecting to # the master - required #master-password = <password> # # The port the master is listening on. # optional - defaults to 3306 #master-port = <port> # # binary logging - not required for slaves, but recommended #log-bin=mysql-bin # # binary logging format - mixed recommended #binlog_format=mixed # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir = /var/lib/mysql #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend #innodb_log_group_home_dir = /var/lib/mysql # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high #innodb_buffer_pool_size = 384M #innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 100M #innodb_log_buffer_size = 8M #innodb_flush_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [myisamchk] key_buffer_size = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
Pour connaître toutes les options valides pour le serveur, il convient de saisir la commande suivante :
[root@centos7 ~]# /usr/libexec/mysqld --help --verbose | more
171031 13:17:19 [Note] Plugin 'FEEDBACK' is disabled.
/usr/libexec/mysqld Ver 5.5.56-MariaDB for Linux on x86_64 (MariaDB Server)
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Starts the MariaDB database server.
Usage: /usr/libexec/mysqld [OPTIONS]
Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
The following groups are read: mysqld server mysqld-5.5 mariadb mariadb-5.5 clie
nt-server
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit.
--no-defaults Don't read default options from any option file.
--defaults-file=# Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--allow-suspicious-udfs
Allows use of UDFs consisting of only one symbol xxx()
without corresponding xxx_init() or xxx_deinit(). That
also means that one can load any function from any
library, for example exit() from libc.so
-a, --ansi Use ANSI SQL syntax instead of MySQL syntax. This mode
--More--
Pour connaître les options valides pour le client mysql, l'option de la ligne de commande –verbose n'est pas requise :
[root@centos7 ~]# mysql --help | more
mysql Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Usage: mysql [OPTIONS] [database]
Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
The following groups are read: mysql client client-server client-mariadb
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit.
--no-defaults Don't read default options from any option file.
--defaults-file=# Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
-?, --help Display this help and exit.
-I, --help Synonym for -?
--abort-source-on-error
Abort 'source filename' operations in case of errors
--auto-rehash Enable automatic rehashing. One doesn't need to use
'rehash' to get table and field completion, but startup
and reconnecting may take a longer time. Disable with
--disable-auto-rehash.
(Defaults to on; use --skip-auto-rehash to disable.)
--More--
Comment Connaître avec Exactitude la Configuration Actuelle
La réponse à la deuxième question est obtenue en utilisant la commande mysqladmin :
[root@centos7 ~]# mysqladmin -uroot -p variables | more
Enter password:
+---------------------------------------------------+---------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value
|
+---------------------------------------------------+---------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------+
| aria_block_size | 8192
|
| aria_checkpoint_interval | 30
|
| aria_checkpoint_log_activity | 1048576
|
| aria_force_start_after_recovery_failures | 0
|
--More--
Passer des Paramètres à l'Exécutable mysqld
Le serveur mysqld peut être paramétrer en passant des options à l'exécutable lors de son lancement. Dans ce cas, les options sont les mêmes que celles dans le fichier my.cnf, précédées par deux tirés.
Paramétrer le Serveur Dynamiquement
Pour paramétrer le serveur à chaud, il convient d'utiliser la commande SET. Pour utiliser la commande SET, il faut posséder le privilège SUPER.
La portée des options peut être SESSION, c'est-à-dire pour la session en cours, ou GLOBAL pour toutes le sessions.
SESSION
Prenant l'example de l'option tmp_table_size :
[root@centos7 ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 8 Server version: 5.5.56-MariaDB MariaDB Server 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)]> SHOW GLOBAL VARIABLES LIKE 'tmp_table_size'; +----------------+----------+ | Variable_name | Value | +----------------+----------+ | tmp_table_size | 16777216 | +----------------+----------+ 1 row in set (0.00 sec) MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'tmp_table_size'; +----------------+----------+ | Variable_name | Value | +----------------+----------+ | tmp_table_size | 16777216 | +----------------+----------+ 1 row in set (0.01 sec) MariaDB [(none)]> SET SESSION tmp_table_size=8388608; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'tmp_table_size'; +----------------+----------+ | Variable_name | Value | +----------------+----------+ | tmp_table_size | 16777216 | +----------------+----------+ 1 row in set (0.00 sec) MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'tmp_table_size'; +----------------+---------+ | Variable_name | Value | +----------------+---------+ | tmp_table_size | 8388608 | +----------------+---------+ 1 row in set (0.00 sec) MariaDB [(none)]>
Important - Notez que la modification est immédiate.
GLOBAL
En utilisant la même option :
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'tmp_table_size'; +----------------+----------+ | Variable_name | Value | +----------------+----------+ | tmp_table_size | 16777216 | +----------------+----------+ 1 row in set (0.00 sec) MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'tmp_table_size'; +----------------+---------+ | Variable_name | Value | +----------------+---------+ | tmp_table_size | 8388608 | +----------------+---------+ 1 row in set (0.01 sec) MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'tmp_table_size'; +----------------+---------+ | Variable_name | Value | +----------------+---------+ | tmp_table_size | 8388608 | +----------------+---------+ 1 row in set (0.01 sec) MariaDB [(none)]>
Important - Notez que pour une option ayant à la fois une portée globale et une portée session, la modification de la valeur globale n'est pas prise en compte dans la session active.
Prenons maintenant le cas d'une option qui n'a qu'une portée globale :
MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_size'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | query_cache_size | 0 | +------------------+-------+ 1 row in set (0.01 sec) MariaDB [(none)]> SET SESSION query_cache_size=122880; ERROR 1229 (HY000): Variable 'query_cache_size' is a GLOBAL variable and should be set with SET GLOBAL MariaDB [(none)]> SET GLOBAL query_cache_size=122880; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_size'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | query_cache_size | 122880 | +------------------+--------+ 1 row in set (0.01 sec) MariaDB [(none)]>
Important - Notez que pour une option ayant uniquement une portée globale, la modification de la valeur globale est prise en compte dans la session active. Notez aussi que les modifications à chaud ne sont pas persistants.
LAB #3 - Le Mode SQL
Le mode SQL est utilisé principalement pour empêcher l'insertion de données invalides. La valeur par défaut du mode SQL peut être visualisée avec la commande suivante :
MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'sql_mode'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_mode | | +---------------+-------+ 1 row in set (0.01 sec) MariaDB [(none)]>
Les valeurs de l'option sql_mode peuvent être combinées. Pour aider l'administrateur, MariaDB propose un mode sql appelé TRADITIONAL :
MariaDB [(none)]> SET SESSION sql_mode='TRADITIONAL'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'sql_mode'; +---------------+------------------------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+------------------------------------------------------------------------------------------------------------------------------------------------------+ | sql_mode | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------+------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [(none)]>
<html> <center> Copyright © 2011-2018 I2TCH LIMITED </center> </html>
