# data[].attributes.headline
C'est un vrai bonheur d'installer PostgreSQL sur Linux !

# data[].attributes.subject
C'est un vrai bonheur d'installer PostgreSQL sur Linux !

# data[].attributes.attachment_link.readable_text
L'installation du moteur de bases de données relationnelles PostgreSQL 11 sur Linux CentOS 7 est d'une simplicité béate.

Le dépôt YUM

Vous devez tout d'abord installer le dépôt yum fourni par l'éditeur EnterpriseDB :

yum install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm

Les paquets RPM

Ensuite, il vous faudra mettre en place les deux paquets RPM suivants et leurs dépendances :

yum install postgresql11-server postgresql11-contrib

Création de l'instance

Chez PostgreSQL, on ne parle pas d'instance, mais de cluster. Vous devez vous connecter sous le compte Linux postgres et, à l'aide de la commande initdb, initialiser votre base de données.

sudo -u postgres /usr/pgsql-11/bin/initdb -D /var/lib/pgsql/11/data

Lancement du service PostgreSQL

Vous devez désormais lancer votre service à chaud et à froid :

systemctl start postgresql-11 && systemctl enable postgresql-11

# data[].attributes.attachment_link.title
Installer PostgreSQL 11 sur Linux CentOS 7

# data[].attributes.attachment_link.description
L'installation du moteur de bases de données relationnelles PostgreSQL 11 sur Linux CentOS 7 est d'une simplicité béate. Le dépôt YUM Vous devez tout d'abord installer le dépôt yum fourni par l'éditeur EnterpriseDB : yum install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm Les paquets RPM Ensuite, il vous faudra mettre en place les deux paquets RPM suivants et leurs dépendances : yum install postgresql11-server postgresql11-contrib Création de l'instance Chez PostgreSQL, on ne parle pas d'instance, mais de cluster.

# data[].attributes.headline
Je vous ai compilé tout un tas de sites de documentation relatif à PostgreSQL

# data[].attributes.subject
Je vous ai compilé tout un tas de sites de documentation relatif à PostgreSQL

# data[].attributes.attachment_link.readable_text
J'espère que ces quelques sites viendront compléter la documentation officielle pas toujours explicite, lorsqu'il s'agit de mettre en œuvre les " archive logs " ou bien la réplication en mode " log shipping " dans PostgreSQL ! ;+)

La documentation sur PostgreSQL
Billet publié initialement le 4 décembre 2016

# data[].attributes.attachment_link.description
J'espère que ces quelques sites viendront compléter la documentation officielle pas toujours explicite, lorsqu'il s'agit de mettre en œuvre les " archive logs " ou bien la réplication en mode " log shipping " dans PostgreSQL ! ;+) La documentation sur PostgreSQL Billet publié initialement le 4 décembre 2016

# data[].attributes.headline
Si vous n'y prenez pas garde, pgAdmin risque de vous plomber vos instances PostgreSQL !

# data[].attributes.subject
Si vous n'y prenez pas garde, pgAdmin risque de vous plomber vos instances PostgreSQL !

# data[].attributes.attachment_link.readable_text
PgAdmin 4 est la nouvelle version de l'outil d'administration des bases PostgreSQL. Il s'exécute indifféremment sur Windows ou Linux.

Or, le tableau de bord qu'il présente, aussi intéressant soit-il, envoie, par défaut, des dizaines de requêtes toutes les 2 secondes. Afin de minimiser l'impact de cet outil sur les performances de la base, je vous recommande chaudement de procéder à quelques menus réglages.

Désactiver/Réactiver le tableau de bord (dashboard)

La 1ère que je fais, afin d'éviter d'impacter les performances de la base, c'est de fermer le tableau de bord. Je ne l'affiche que si j'en ai l'utilité, notamment en matière de sessions bloquées par la pose de verrous.

Vous pouvez le rajouter à tout moment par un clic droit sur l'un des panneaux au dessous de la barre de menus.

Réglage des préférences

Allez dans File > Preferences.

Visualisation des objets système

Par défaut, les modèles de base template0 et template1, les schémas pg_temp_1, pg_toast et pg_toast_temp_1 ne sont pas affichés dans PgAdmin 4. Afin de les visualiser, allez dans File > Preferences > Browser > Display. Passez la valeur de Show system objects à True.

Intervalle de rafraîchissement du tableau de bord

L'autre solution, si vous tenez aux données actualisées du tableau de bord, c'est de changer la fréquence d'actualisation des graphiques. Allez dans Dashboard > Graphs à partir des préférences de PgAdmin 4. L'unité utilisée est la seconde.

Configuration de l'éditeur de requêtes

Une fois connecté à la base de votre choix, l'accès à l'éditeur de requêtes se fait à partir du menu Tools > Query Tool ou bien par simple droit sur la base au schéma auquel vous êtes connecté. Vous pouvez, dans les préférences, procéder à quelques petits réglages, comme l'ouverture d'un onglet pour chaque nouvelle requête ou bien le temps de persistance de la notification du temps d'exécution de la requête. Allez dans SQL Editor > Display.

Options des plans d'exécution

La visualisation le plan d'exécution d'une requête se fait à partir de l'éditeur de requêtes, par la touche F7 ou la combinaison de touches Shift+ F7. PgAdmin vous propose de disposer des buffers consommés, du coût et du temps d'exécution de la requête. L'activation de ces options se fait, au niveau des préférences, dans SQL Editor > Explain.

# data[].attributes.attachment_link.title
| Baisser l'impact de l'utilisation de PgAdmin 4 sur PostgreSQL

# data[].attributes.attachment_link.description
PgAdmin 4 est la nouvelle version de l'outil d'administration des bases PostgreSQL. Il s'exécute indifféremment sur Windows ou Linux. Or, le tableau de bord qu'il présente, aussi intéressant soit-il, envoie, par défaut, des dizaines de requêtes toutes les 2 secondes.

# data[].attributes.headline
Un script système sous Windows pour recharger les modifications apportées à la configuration de PostgreSQL

# data[].attributes.subject
Un script système sous Windows pour recharger les modifications apportées à la configuration de PostgreSQL

# data[].attributes.attachment_link.readable_text
Lorsque vous voulez que soient prises en compte les modifications apportées à votre fichier pg_hba.conf, il suffit sous Linux d'exécuter la commande systemctl reload postgresql-server. Sous Windows, il n'existe aucun moyen à partir de la console des services ( services.msc) de recharger la configuration de PostgreSQL.

Un batch pour recharger la configuration PostgreSQL sous Windows

C'est à l'aide de la commande pg_ctl, invoquée implicitement par systemctl reload [...] sous Linux, que nous allons pouvoir prendre en compte les modifications intervenues dans le fichier pg_hba.conf, dont le rôle est de gérer l'authentification et la connexion aux bases de données PostgreSQL.

J'ai donc écrit un fichier reloadpg.cmd, qui comprend les commandes suivantes :

@ECHO OFF
SETLOCAL
SET PGDATA="C:\Program Files\PostgreSQL\9.6\data"
CD /D "C:\Program Files\PostgreSQL\9.6\bin"
pg_ctl reload -D %PGDATA%
ENDLOCAL
EXIT

# data[].attributes.attachment_link.title
| Rechargement de la configuration PostgreSQL sous Windows

# data[].attributes.attachment_link.description
Lorsque vous voulez que soient prises en compte les modifications apportées à votre fichier pg_hba.conf, il suffit sous Linux d'exécuter la commande systemctl reload postgresql-server. Sous Windows, il n'existe aucun moyen à partir de la console des services ( services.msc) de recharger la configuration de PostgreSQL.

# data[].attributes.attachment_link.readable_text
Il y a deux méthodes de stockage des binaires dans des champs BLOB au niveau de PostgreSQL. La 1ère consiste à créer un champ de type OID, qui stockera la référence au fichier contenant les données binaires. Dans ce mode, PostgreSQL fait une copie du fichier dans le tablespace de la base. La 2e est de créer un champ BYTEA, qui contiendra le contenu du fichier binaire. La donnée est intégrée à la table !

La table d'exemple

Je crée tout d'abord une table comprenant trois champs :

CREATE TABLE public.affiche
 (
 affiche_nom character VARYING(1024),
 affiche_image_oid OID,
 affiche_image_bytea BYTEA
 );

Création du lien symbolique

Sous Windows, à partir du dossier PGDATA ( C:\Program Files\PostgreSQL\9.6\data), je crée le lien symbolique images qui pointe vers le dossier D:\EXPIMP :

MKLINK /D images d:\expimp

Et oui, on peut aussi créer des liens symboliques sous Windows !

Chargement des images

Les méthodes pour charger les images en mode OID et BYTEA ne sont pas les mêmes : lo_import pour la 1ère gère les chemins absolus ; pg_read_binary pour la seconde n'autorise que les chemins relatifs à partir de PGDATA. Dans les deux cas, le compte de processus de PostgreSQL doit disposer des droits en lecture sur le répertoire.

INSERT INTO affiche VALUES ('Le prénom', lo_import('d:\expimp\le-prenom.jpg'),pg_read_binary_file('images/le-prenom.jpg'));

Export des images

En mode OID, la commande d'exportation du fichier est relativement simple. Quel que soit le mode retenu de stockage, vous devez disposer des droits en écriture sur D:\EXPIMP pour le compte de processus qui lance PostgreSQL !

SELECT lo_export(affiche_image_oid, 'd:\expimp\le-prenom-oid.jpg')
FROM affiche
WHERE affiche_nom = 'Le prénom';

En mode BYTEA, vous devrez recourir au programme xxd.exe. Sur Windows, afin de disposer de cet exécutable, il faut installer gvim et copier le fichier xxd.exe contenu dans C:\Program Files (x86)\Vim\vim80 vers c:\windows, par exemple.

COPY (SELECT encode(affiche_image_bytea,'hex') FROM affiche WHERE affiche_nom = 'Le prénom')
 TO PROGRAM 'xxd -p -r > d:\expimp\le-prenom-bytea.jpg';

Vsou pouvez vérifier que les trois fichiers (l'original, l'export OID, l'export BYTEA) ont exactement la même taille à l'octet près.

# data[].attributes.attachment_link.title
| Import et export des champs BLOB dans PostgreSQL

# data[].attributes.attachment_link.description
Il y a deux méthodes de stockage des binaires dans des champs BLOB au niveau de PostgreSQL. La 1ère consiste à créer un champ de type OID, qui stockera la référence au fichier contenant les données binaires. Dans ce mode, PostgreSQL fait une copie du fichier dans le tablespace de la base.

# data[].attributes.headline
Les fonctionnalités introduites dans PostgreSQL au fil des versions

# data[].attributes.subject
Les fonctionnalités introduites dans PostgreSQL au fil des versions

# data[].attributes.attachment_link.readable_text
Dans le cadre de la préparation de deux formations que je dispenserai à Nantes la semaine prochaine, j'ai pris le temps, ce matin, de compiler les différentes notes de version mis en ligne par l'éditeur de PostgreSQL. J'y ai découvert de très nombreuses fonctionnalités que je ne connaissais pas encore. L'effeuillage des notes de version est un excellent moyen de faire votre veille sur un produit !

La liste fournie ci-dessous est loin, très loin d'être exhaustive. Elle retrace l'histoire de ce moteur de bases de données relationnel né en 1995.

# data[].attributes.attachment_link.title
| Principaux ajouts fonctionnels à PostgreSQL

# data[].attributes.attachment_link.description
Dans le cadre de la préparation de deux formations que je dispenserai à Nantes la semaine prochaine, j'ai pris le temps, ce matin, de compiler les différentes notes de version mis en ligne par l'éditeur de PostgreSQL. J'y ai découvert de très nombreuses fonctionnalités que je ne connaissais pas encore.

# data[].attributes.headline
Dbeaver pour faire vos requêtes sur vos instances PostgreSQL

# data[].attributes.subject
Dbeaver pour faire vos requêtes sur vos instances PostgreSQL

# data[].attributes.attachment_link.readable_text
Pour fuir la lenteur de PgAdmin 4, je vous ai recommandé chaudement d'utiliser HeidiSQL pour faire vos requêtes sur vos instances PostgreSQL. J'aurais pu vous parler de TOra, hélas incompatible avec les toutes dernières versions de PostgreSQL. L'autre alternative Open Source à PgAdmin s'appelle DBeaver. Ce logiciel est connu de tout bon DBA qui se respecte. ;+)

Installer pgJDBC, le driver JDBC PostgreSQL

Sous Windows, la 1ère chose à faire est d'installer le driver JDBC pour PostgreSQL. Cette installation se fait à l'aide de Stackbuilder.

Modification dans DBeaver des propriétés du driver PostgreSQL

Après avoir installé DBeaver, allez dans Database > Driver Manager. Sélectionnez le driver PostgreSQL et cliquez sur le bouton Edit.

Dans l'onglet Libraries, cliquez sur le bouton Add File et allez rechercher le fichier jar installé avec Stackbuilder lors de la 1ère étape, afin de l'associer au driver PostgreSQL.

Création de la connexion PostgreSQL

Il faut ensuite créer votre connexion à votre instance (cluster) PosgreSQL. A partir du menu DBeaver, allez dans Database > New connection. Choisissez le driver PostgreSQL.

Renseignez l'adresse Ip ou la machine, la base de données auxquelles vous vous connectez, ainsi que les login et mot de passe utilisés.

Le test de connexion montre que le driver installé a bel et bien été utilisé.

Le MPD, Modèle Physique des Données, proposé par DBeaver

L'intérêt majeur de DBeaver est de proposer le diagramme du modèle physique des données, par un clic droit sur le schéma contenant les tables de l'application.

# data[].attributes.attachment_link.description
Pour fuir la lenteur de PgAdmin 4, je vous ai recommandé chaudement d'utiliser HeidiSQL pour faire vos requêtes sur vos instances PostgreSQL. J'aurais pu vous parler de TOra, hélas incompatible avec les toutes dernières versions de PostgreSQL. L'autre alternative Open Source à PgAdmin s'appelle DBeaver.

# data[].attributes.headline
Benchmarking Avec pgBench pour PostgreSQL

# data[].attributes.subject
Benchmarking Avec pgBench pour PostgreSQL

# data[].attributes.attachment_link.readable_text
Pgbench est un outil fort utile pour tout DBA qui tâte un jour à PostgreSQL. Il s'agit d'un logiciel de benchmarking vous donnant le temps mis par l'exécution de transactions sur le moteur de données Open Source. La commande appartient au paquet , qui est installé lorsque vous installez les binaires du paquet postgresql96-server :

# S'il est installé sur votre système
rpm -ql postgresql96|grep pgbench
# Interroge les dépôts et nécessite le paquet yum-utils
repoquery -l postgresql96|grep pgbench

Pgbench est fourni en standard dans l'installeur PostgreSQL sur Windows.

Création de la base pgbench

Avant de pouvoir faire vos tests, pgbench a besoin que vous créiez une base de données. Dans l'exemple ci-dessous, je l'ai appelé pgbench !

sudo -u postgres /usr/pgsql-9.6/bin/psql -c "CREATE DATABASE pgbench"
sudo -u postgres /usr/pgsql-9.6/bin/pgbench -i pgbench

Trois types de benchmarks

J'ai pris le parti de fixer le nombre de connexions à 10 (-c 10), le nombre de jobs à 2 (-j 2) et le nombre de transactions à 100 (-t 100), en effectuant un vacuum avant l'exécution des instructions (-v).

Il existe trois types de test :

tpbc-like, qui réalise des instructions SELECT, UPDATE, INSERT

simple-update, qui se contente d'effectuer des UPDATE

select-only, pour exécuter des ordres SELECT

Les données du benchmark sont exprimés en tps, i.e. en transactions par secondes.

sudo -u postgres /usr/pgsql-9.6/bin/pgbench -b tpcb-like -c 10 -j 2 -t 100 -v pgbench
sudo -u postgres /usr/pgsql-9.6/bin/pgbench -b simple-update -c 10 -j 2 -t 100 -v pgbench
sudo -u postgres /usr/pgsql-9.6/bin/pgbench -b select-only -c 10 -j 2 -t 100 -v pgbench

Les indicateurs obtenus

Les temps et le nombre de transactions figurant dans le tableau ci-dessous sont ceux obtenus à la 2e exécution de la commande.

# data[].attributes.attachment_link.title
PostgreSQL : dans mon pgbench, bench, bench...

# data[].attributes.attachment_link.description
Pgbench est un outil fort utile pour tout DBA qui tâte un jour à PostgreSQL. Il s'agit d'un logiciel de benchmarking vous donnant le temps mis par l'exécution de transactions sur le moteur de données Open Source.

# data[].attributes.headline
Configurer le planificateur de tâches pgAgent sous Windows 

# data[].attributes.subject
Configurer le planificateur de tâches pgAgent sous Windows 

# data[].attributes.attachment_link.readable_text
est un planificateur de tâches pour PostgreSQL qui permet d'exécuter des scripts sans recourir au planificateur de tâches système. Les jobs que vous créez sont stockés dans le catalogue pgAgent de la base postgres !

Installation de pgAgent sous Windows

Avant d'installer pgAgent, créez le fichier %APPDATA%\postgresql\pgpass.conf, dans lequel vous ajoutez les paramètres de connexion à votre instance à l'image de l'exemple ci-dessous :

localhost:5432:postgres:postgres:postgres

Sous Windows, le logiciel s'installe avec l' installeur fourni par EDB après qu'il a été téléchargé via le gestionnaire de paquets StackBuilder fourni par EDB.

Modifier les paramètres de lancement du service pgAgent

Par défaut, sous Windows, l'accès local à la base exige que vous saisissiez votre mot de passe. C'est la raison pour laquelle vous pourriez être amené à modifier, dans le registre au niveau de la clé HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\pgAgent\ImagePath, les paramètres de lancement du service pgAgent en y ajoutant le mot de passe. En fait, cela dépend de la configuration de votre fichier pg_hba.conf.

C:\PROGRA~2\pgAgent\bin\pgagent.exe RUN pgAgent host=localhost port=5432 user=postgrespassword=postgres dbname=postgres

Puis, vous devez redémarrer le service.

net stop pgagent
net start pgagent

Pas terrible au niveau sécurité, n'est-ce pas ?

Création du job pgAgent

J'ai rencontré un bogue majeur sous la version pgAdmin 4, lors de la création du job, au niveau de la planification. Préférez la saisie des dates à l'utilisation des agendas qui fige pgAdmin 4 et qui vous oblige à terminer la tâche.

Le code SQL de création de la tâche planifiée

Une fois créé, faites un clic droit au niveau du job et choisissez CREATE SCRIPT. Dans le code suivant qui consiste à insérer la date du jour dans la table test du schéma public de la base test, le job s'exécute toutes les heures +1 minute !

DO $$
DECLARE
 jid integer;
 scid integer;
BEGIN
--Code SQL pour supprimer les jobs existants
--DELETE FROM pgagent.pga_job;
--DELETE FROM pgagent.pga_jobstep;
--DELETE FROM pgagent.pga_schedule;
-- Creating a new job
INSERT INTO pgagent.pga_job(
 jobjclid, jobname, jobdesc, jobhostagent, jobenabled
) VALUES (
 1::integer, 'job_test'::text, ''::text, ''::text, true
) RETURNING jobid INTO jid;

-- Steps
-- Inserting a step (jobid: NULL)
INSERT INTO pgagent.pga_jobstep (
 jstjobid, jstname, jstenabled, jstkind,
 jstconnstr, jstdbname, jstonerror,
 jstcode, jstdesc
) VALUES (
 jid, 'step_test'::text, true, 's'::character(1),
 ''::text, 'test'::name, 'f'::character(1),
 'INSERT INTO public.test VALUES(NOW());'::text, ''::text
) ;

-- Schedules
-- Inserting a schedule
INSERT INTO pgagent.pga_schedule(
 jscjobid, jscname, jscdesc, jscenabled,
 jscstart, jscend, jscminutes, jschours, jscweekdays, jscmonthdays, jscmonths
) VALUES (
 jid, 'schedule_test'::text, ''::text, true,
 '2017-07-03 21:05:00+02'::timestamp with time zone, '2099-07-31 20:30:00+02'::timestamp with time zone,
 -- Minutes
 ARRAY[false, true, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false]::boolean[],
 -- Hours
 ARRAY[false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false]::boolean[],
 -- Week days
 ARRAY[false, false, false, false, false, false, false]::boolean[],
 -- Month days
 ARRAY[false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false, false]::boolean[],
 -- Months
 ARRAY[false, false, false, false, false, false, false, false, false, false, false, false]::boolean[]
) RETURNING jscid INTO scid;
END
$$;

# data[].attributes.attachment_link.description
est un planificateur de tâches pour PostgreSQL qui permet d'exécuter des scripts sans recourir au planificateur de tâches système. Les jobs que vous créez sont stockés dans le catalogue pgAgent de la base postgres !

# data[].attributes.headline
HeidiSQL comme alternative à PgAdmin pour gérer au quotidien une base PostgreSQL

# data[].attributes.subject
HeidiSQL comme alternative à PgAdmin pour gérer au quotidien une base PostgreSQL

# data[].attributes.attachment_link.readable_text
L'utilisation de PgAdmin 4 est devenue incontournable pour administrer le moteur de base de données Open Source PostgreSQL à partir de la version 9.5.2. Le passage à la version 4 sur Windows n'est pas une réussite. PgAdmin est devenu d'une lenteur inouïe, rendant totalement insupportable son utilisation !

HeidiSQL, alternative OSS à PgAdmin 4

HeidiSQL a été mis en lumière par l'équipe de MariaDB qui le propose en standard dans son installeur pour Windows. C'est un outil Open Source d'administration et de requêtage simple et rapide à l'utilisation, qui s'exécute exclusivement sous Windows. Je ne l'ai pas encore testé avec Wine sous Linux.

Depuis quelques temps, HeidiSQL propose une nouvelle fonction expérimentale, en vous permettant de vous connecter à PostgreSQL. Pour l'instant, le logiciel ne permet pas d'administrer une base PostgreSQL comme peut le faire PgAdmin 4. Il vous permettra juste d'effectuer des requêtes.

Je viens de tester HeidiSQL avec une base de données PosgreSQL 9.6.3 qui s'exécute sous Linux CentOS 7.

# data[].attributes.attachment_link.title
| HeidiSQL, alternative partielle à PgAdmin 4

# data[].attributes.attachment_link.description
L'utilisation de PgAdmin 4 est devenue incontournable pour administrer le moteur de base de données Open Source PostgreSQL à partir de la version 9.5.2. Le passage à la version 4 sur Windows n'est pas une réussite. PgAdmin est devenu d'une lenteur inouïe, rendant totalement insupportable son utilisation !

# data[].attributes.headline
Dbeaver : un excelent GUI pour pour Firebird

# data[].attributes.subject
Dbeaver : un excelent GUI pour pour Firebird

# data[].attributes.attachment_link.readable_text
Après avoir testé différents GUI Open Source, je dois dire que le seul qui m'a permis d'attaquer Firebird 3.0, c'est Dbeaver 3.6.5 ! Les autres outils sont ou bien de simples archives au format JAR à l'image de Squirrel ou bien ne permettent pas de se connecter à la nouvelle version de Firebird compte tenu de leur grand âge. L'inconvénient de DBeaver est qu'il ne propose aucune possibilité d'administrer Firebird 3.0 à distance.

L'absence de GUI est peut-être là l'épine du projet Firebird !

# data[].attributes.attachment_link.description
Après avoir testé différents GUI Open Source, je dois dire que le seul qui m'a permis d'attaquer Firebird 3.0, c'est Dbeaver 3.6.5 ! Les autres outils sont ou bien de simples archives au format JAR à l'image de Squirrel ou bien ne permettent pas de se connecter à la nouvelle version de Firebird compte tenu de leur grand âge.

# data[].attributes.headline
De la documentation à la pelle sur SQLite !

# data[].attributes.subject
De la documentation à la pelle sur SQLite !

# data[].attributes.attachment_link.readable_text
L'essentiel de la documentation SQLite se trouve sur le site de son éditeur, D. Richard Hill. Elle est en anglais et plutôt bien faite. Je vous ai compilé l'essentiel de cette documentation, au travers de quelques liens.

Documentation SQLite

Vous pouvez accéder à toutes ces thématiques au travers d'un index.

Vous disposez également d'un moteur de recherche interne très imparfait, de mon point de vue. Une recherche sur l'expression CREATE TABLE se fait comme suit :

https://www.sqlite.org/search?s=d&q=CREATE+TABLE

# data[].attributes.attachment_link.description
L'essentiel de la documentation SQLite se trouve sur le site de son éditeur, D. Richard Hill. Elle est en anglais et plutôt bien faite. Je vous ai compilé l'essentiel de cette documentation, au travers de quelques liens. Documentation SQLite Vous pouvez accéder à toutes ces thématiques au travers d'un index.

# data[].attributes.headline
SQLite: une gestion de verrous très bloquante 

# data[].attributes.subject
SQLite: une gestion de verrous très bloquante 

# data[].attributes.attachment_link.readable_text
La gestion des verrous dans SQLite est d'une étrange bizarreté. Lorsque vous faites une opération DML ou DDL en écriture (UPDATE, DELETE, INSERT mais aussi CREATE, DROP, ALTER), vous posez un verrou sur la database, c'est-à-dire sur le fichier contenant l'ensemble des tables. Il n'y a donc plus aucune possibilité de modifier, de supprimer, d'insérer des données de manière concurrente sur les tables de la base.

Autrement dit, en cas de modification en masse des lignes d'une table d'une base (d'un fichier sqlite), vous devrez attendre la fin des opérations pour pouvoir procéder à des modifications sur cette table ou sur une autre table de la base au niveau d'un accès ou d'une opération concurrente. Ou bien, vous obtiendrez le message :

Error: database is locked

Remarquez qu'en mode MyISAM ou Aria 1.5, MySQL ou MariaDB posent également un verrou exclusif sur table. Seul, le choix du moteur InnoDB permet les accès concurrents en écriture.

Seule bonne nouvelle : les modifications n'empêchent pas aux autres utilisateurs de consulter - via des SELECT - le contenu des tables en cours de modification.

# data[].attributes.attachment_link.description
La gestion des verrous dans SQLite est d'une étrange bizarreté. Lorsque vous faites une opération DML ou DDL en écriture (UPDATE, DELETE, INSERT mais aussi CREATE, DROP, ALTER), vous posez un verrou sur la database, c'est-à-dire sur le fichier contenant l'ensemble des tables.

# data[].attributes.headline
Des clients lourds pour gérer vos bases SQLite

# data[].attributes.subject
Des clients lourds pour gérer vos bases SQLite

# data[].attributes.attachment_link.readable_text
SQLite n'est pas à proprement un moteur de bases de données. C'est un format de fichier dans lequel les données sont manipulées au travers des instructions traditionnelles DDL et DML du SQL. Depuis que ces gros andouilles de la fondation Mozilla ont décidé d'en finir avec les extensions XUL, l' extension Firefox SQLite Manager ne fonctionne plus à partir de Firefox Quantum. Elle marche encore sur Firefox 55.0.3, que je continue à utiliser en parallèle avec Chromium. Pour SQLite, nous allons devoir revenir au client lourd !

Le(s) freeware GUI SQLite

En fait, je n'ai trouvé qu'un seul GUI SQLite en licence freeware, limité à un usage exclusivement personnel et donc inutilisable en entreprise. Il s'agit de SQLite Expert Personal et il présente, disons-le, plutôt bien.

Les GUI SQLite Open Source

De très nombreuses interfaces graphiques pour administrer les fichiers SQLite sont tombés en désuétude. Citons dans ce registre :

L'un des rares outils Open Source fonctionnel et à jour que j'ai trouvé c'est DB Browser for SQLite . Et autant vous dire, il est plutôt pas mal et , contrairement à SQLite Expert, il dispose d'une interface en français !

Et puis, cette étude ne serait pas complète sans vous parler de Dbeaver qui nécessite d'abord que vous téléchargiez une version récente du driver JDBC pour SQLite. L'intérêt de Dbeaver est de permettre de disposer d'un schéma de la base de données.

# data[].attributes.attachment_link.description
SQLite n'est pas à proprement un moteur de bases de données. C'est un format de fichier dans lequel les données sont manipulées au travers des instructions traditionnelles DDL et DML du SQL. Depuis que ces gros andouilles de la fondation Mozilla ont décidé d'en finir avec les extensions XUL, l' extension Firefox SQLite Manager ne fonctionne plus à partir de Firefox Quantum.

# data[].attributes.headline
Un exemple pour réduire et compacter vos bases de données SQL

# data[].attributes.subject
Un exemple pour réduire et compacter vos bases de données SQL

# data[].attributes.attachment_link.readable_text
Il existe différentes extensions sous Firefox qui compactent les bases SQLite utilisées par le navigateur de la fondation Mozilla :

Le souci est que ces extensions Firefox à l'exception de Places Maintenance (Merci à SckyzO), si elles compactent tables et index, ne permettent pas de les réindexer. Je ne sais d'ailleurs pas dire avec exactitude si cela présente un intérêt quelconque.

Pour exécuter le code présenté ci-dessous, vous devez préalablement vous assurer de fermer Firefox.

Batch sous Windows pour maintenir les bases SQLite de Firefox

Après avoir téléchargé et décompressé le shell SQLite pour Windows, copiez le fichier sqlite3.exe dans le répertoire c:\windows\system32. C'est plus simple ! L'emplacement de votre profil Firefox est défini dans le fichier %USERPROFILE%\AppData\Roaming\Mozilla\Firefox\profiles.ini. Dans l'exemple ci-dessous, le répertoire du profil Firefox se situe dans d:\firefox\profil.

@echo off
d:
cd \firefox\profil
for %%i in (*.sqlite) do sqlite3 %%i "vacuum"
for %%i in (*.sqlite) do sqlite3 %%i "reindex"
exit

Script Bash sous Linux pour compacter et réindexer les bases SQLite associées à Mozilla Firefox

Téléchargez le paquet pour Linux à partir du lien ci-dessus. Dézippez-le dans le répertoire de votre profil. Créez le script suivant dans le même dossier en prenant soin de le rendre exécutable.

#!/bin/bash
find ~ -type f *.sqlite -exec ./sqlite3 {} "vacuum" \;
find ~ -type f *.sqlite -exec ./sqlite3 {} "reindex" \;

# data[].attributes.attachment_link.title
| Compactage et réindexation des bases SQLite Firefox

# data[].attributes.attachment_link.description
Il existe différentes extensions sous Firefox qui compactent les bases SQLite utilisées par le navigateur de la fondation Mozilla : Le souci est que ces extensions Firefox à l'exception de Places Maintenance (Merci à SckyzO), si elles compactent tables et index, ne permettent pas de les réindexer.

# data[].attributes.headline
Désinstaller Oracle sur Windows : pas toujours si simple !

# data[].attributes.subject
Désinstaller Oracle sur Windows : pas toujours si simple !

# data[].attributes.attachment_link.readable_text
Pour désinstaller Oracle 11g R2 sur Windows Server 2008 R2, il faut exécuter à partir de la ligne de commandes deinstall.bat qui se situe dans le répertoire ORACLE_HOME/deinstall. Attention de l'appeler à partir d'un autre chemin afin que le nettoyage du répertoire d'installation d'Oracle Database s'effectue dans de bonnes conditions.

oci.dll

Pour effacer complètement le répertoire d'installation d'Oracle dans lequel reste stockée la Dll oci.dll, vous devez killer le processus msdtc.exe qui la verrouille, à partir du gestionnaire de tâches Windows (Ctrl+Maj+Echap) !

NB Je n'ai pas testé la commande regsvr32 /u oci.dll !

# data[].attributes.attachment_link.title
Désinstaller Oracle 11 g R2 sur Windows Server 2008 R2

# data[].attributes.attachment_link.description
Pour désinstaller Oracle 11g R2 sur Windows Server 2008 R2, il faut exécuter à partir de la ligne de commandes deinstall.bat qui se situe dans le répertoire ORACLE_HOME/deinstall. Attention de l'appeler à partir d'un autre chemin afin que le nettoyage du répertoire d'installation d'Oracle Database s'effectue dans de bonnes conditions.

# data[].attributes.attachment_link.readable_text
Dans le dernier snaphot de la Wayback Machine relatif à la page de téléchargement du moteur de bases de données relationnel d'Oracle du 20 juin 2011, la version 10g y figurait encore en lien. Lundi dernier, je me suis aperçu que Oracle Database 10g avait cette fois disparu des pages de téléchargement de l'éditeur ! Pour les formateurs qui n'auront pas pris la précaution d'en faire des copies, ils seront désormais dans l'incapacité de proposer des formations à l'aide d'anciennes versions d'Oracle, toujours très utilisées dans le monde professionnel.

Mémoire numérique

Depuis 1996, j'ai pris soin inlassablement de graver tout ce que je téléchargeais, en indexant soigneusement toutes ces informations dans une base de données qui constitue l'essentiel de la mémoire numérique. La priorité faite au buzz et à l'actualité a fait disparaître de très nombreux logiciels des pages de recherche des moteurs. Même des sites comme AlternativeTo ne nous montrent qu'une infime partie de la logithèque commerciale, gratuite et Open Source existante.

Je trouve, pour ma part, tout à fait regrettable de la part des éditeurs commerciaux, comme Microsoft ou Oracle, de ne pas mettre à disposition toutes les anciennes versions de leurs logiciels, quitte à en limiter les conditions d'utilisation. Il serait temps de s'inspirer de ce que fait aujourd'hui la fondation Mozilla qui laisse sur ses serveurs téléchargement, à disposition, toutes les versions de Firefox !

# data[].attributes.attachment_link.title
| Oracle Database 10g a disparu de nos écrans radars !

# data[].attributes.attachment_link.description
Dans le dernier snaphot de la Wayback Machine relatif à la page de téléchargement du moteur de bases de données relationnel d'Oracle du 20 juin 2011, la version 10g y figurait encore en lien. Lundi dernier, je me suis aperçu que Oracle Database 10g avait cette fois disparu des pages de téléchargement de l'éditeur !

# data[].attributes.headline
Un script SQL pour réduire et réindexer vos tables de vos bases Oracle.

# data[].attributes.subject
Un script SQL pour réduire et réindexer vos tables de vos bases Oracle.

# data[].attributes.attachment_link.readable_text
L'objet de la procédure stockée que je vais vous présenter est de réduire les tables et reconstruire les index d'un schéma. Ainsi, vous disposerez d'un accès plus rapide à vos données !

La procédure ne fonctionne pas pour les tables mises en cluster !

Réduction des tables et reconstruction des index

CREATE OR REPLACE PROCEDURE maintenance (user VARCHAR2) IS
    cursor cTables IS
        SELECT table_name FROM dba_tables WHERE owner=user;
    cursor cIndexes IS
        SELECT index_name FROM dba_indexes WHERE owner=user;
    sql_stmt  VARCHAR2(1024);
BEGIN
   FOR rTable in cTables
   LOOP
      sql_stmt := 'ALTER TABLE ' || user || '.'|| rTable.TABLE_NAME || ' ENABLE ROW MOVEMENT';
      EXECUTE IMMEDIATE sql_stmt;
      sql_stmt := 'ALTER TABLE ' || user|| '.' || rTable.TABLE_NAME || ' SHRINK SPACE COMPACT';
      EXECUTE IMMEDIATE sql_stmt;
      sql_stmt := 'ALTER TABLE ' || user || '.'|| rTable.TABLE_NAME || ' DISABLE ROW MOVEMENT';
      EXECUTE IMMEDIATE sql_stmt;
   END LOOP;
   FOR rIndex in cIndexes
   LOOP
      sql_stmt := 'ALTER INDEX ' || user || '.' || rIndex.index_name || ' REBUILD';
      EXECUTE IMMEDIATE sql_stmt;
   END LOOP;
END;

Utilisation de la procédure

Vous devez passer en paramètre le nom du schéma - le nom de l'utilisateur - auquel appartiennent tables et index ! L'utilisateur qui exécute la procédure stockée doit disposer des droits sur les objets de l'utilisateur. Il peut s'agir de SYS ou de SYSTEM !

EXEC maintenance('CRM');

# data[].attributes.attachment_link.title
| Oracle : maintenance des tables et des index

# data[].attributes.attachment_link.description
Réduire les tables et reconstruire les index que ce soit sous Oracle ou sous un autre moteur de bases de données est l'une des principales tâches de maintenance à réaliser pour obtenir une plus grande célérité à l'exécution des requêtes !

# data[].attributes.headline
Il faut aussi pouvoir faire une sauvegarde à froid des bases de données Oracle sur Windows.

# data[].attributes.subject
Il faut aussi pouvoir faire une sauvegarde à froid des bases de données Oracle sur Windows.

# data[].attributes.attachment_link.readable_text
Le script de sauvegarde à froid présenté ci-dessous s'applique à une installation d'Oracle Database 11g sous Windows, réalisée dans le répertoire c:\oracle\dbhome.

L'objet d'une sauvegarde à froid est de permettre, après réinstallation, de refaire partir l'instance dans les mêmes conditions où elle fonctionnait. Au cas où vous seriez amené à changer des paramètres, il faut éditer les fichiers listener.ora et tnsnames.ora stocké dans le répertoire NETWORK\ADMIN avant de relancer les services de l'instance et du listener. Passez par des noms résolus plutôt que par les adresses IP.

@echo off
set SID=orcl
set REPSAV=c:\sauve
set REPORA=c:\oracle
net stop OracleService%SID%
net stop OracleOraDb11g_home1TNSListener
if not exist %REPSAV% mkdir %REPSAV%
if not exist %REPSAV%\oracle\admin\%SID%\pfile mkdir %REPSAV%\oracle\admin\%SID%\pfile\
if not exist %REPSAV%\oracle\oradata\%SID% mkdir %REPSAV%\oracle\oradata\%SID%
if not exist %REPSAV%\oracle\flash_recovery_area\%SID% mkdir %REPSAV%\oracle\flash_recovery_area\%SID%
if not exist %REPSAV%\oracle\dbhome\NETWORK\ADMIN mkdir %REPSAV%\oracle\dbhome\NETWORK\ADMIN
if not exist %REPSAV%\oracle\dbhome\database mkdir %REPSAV%\oracle\dbhome\database
xcopy %REPORA%\admin\%SID%\pfile\init.ora.* %REPSAV%\oracle\admin\%SID%\pfile /Y
xcopy %REPORA%\oradata\%SID%\*.* %REPSAV%\oracle\oradata\%SID% /Y
xcopy %REPORA%\flash_recovery_area\%SID%\*.* %REPSAV%\oracle\flash_recovery_area\%SID% /Y
xcopy %REPORA%\dbhome\NETWORK\ADMIN\*.ora %REPSAV%\oracle\dbhome\NETWORK\ADMIN /Y
xcopy %REPORA%\dbhome\database\*%SID%* %REPSAV%\oracle\dbhome\database /Y
net start OracleOraDb11g_home1TNSListener
net start OracleService%SID%
exit

# data[].attributes.attachment_link.title
| Sauvegarde à froid d'une base Oracle 11g sur Windows

# data[].attributes.attachment_link.description
Le script de sauvegarde à froid présenté ci-dessous s'applique à une installation d'Oracle Database 11g sous Windows, réalisée dans le répertoire c:\oracle\dbhome. L'objet d'une sauvegarde à froid est de permettre, après réinstallation, de refaire partir l'instance dans les mêmes conditions où elle fonctionnait.

# data[].attributes.headline
Lancement d'Oracle en mode rc.local sous Fedora

# data[].attributes.subject
Lancement d'Oracle en mode rc.local sous Fedora

# data[].attributes.attachment_link.readable_text
L' installation d'Oracle 11g sous Linux Red Hat 7 ou CentOS 7 est loin d'être sinécure. Je tâcherais, dans les jours qui viennent, de vous faire un tuto aux petits oignons sur ce sujet ! ;+) En 2010, j'en avais réalisé un pour l' installation d'Oracle 10g sous CentOS 5.5. Que le temps passe vite, n'est-ce pas ?

Une fois installé, il faut lancer Oracle automatiquement au démarrage de votre système Linux CentOS 7. Je recours, pour ma part, à l'utilisation du fichier /etc/rc.local. Pour qu'il soit pris en compte, il faut simplement le rendre exécutable par la commande chmod a+x /etc/rc.local. C'est un peu différent sous Red Hat/CentOS, par rapport à ce qui se passe pour le rc.local sous Fedora.

Pour qu'Oracle se lance automatiquement au boot de votre système Linux CentOS 7, il faut donc ajouter au fichier /etc/rc.local les deux lignes suivantes :

su oracle -c '/oracle/product/11.2.0.4/db_1/bin/lsnrctl start LISTENER'
su oracle -c '/oracle/product/11.2.0.4/db_1/bin/dbstart $ORACLE_HOME'

Je n'ai rien lu dans la documentation officielle fournie par Oracle, relatif à l'utilisation de systemd pour lancer le moteur de bases de données sous Linux.

Pour arrêter Oracle, il suffit de créer un script Bash contenant les deux commandes :

su oracle -c '/oracle/product/11.2.0.4/db_1/bin/dbshut $ORACLE_HOME'
su oracle -c '/oracle/product/11.2.0.4/db_1/bin/lsnrctl stop LISTENER'

Autre(s) référence(s) : Stopping and Starting Oracle Software

# data[].attributes.attachment_link.title
| Lancer Oracle 11g automatiquement au démarrage de Linux CentOS / Red Hat 7

# data[].attributes.attachment_link.description
L' installation d'Oracle 11g sous Linux Red Hat 7 ou CentOS 7 est loin d'être sinécure. Je tâcherais, dans les jours qui viennent, de vous faire un tuto aux petits oignons sur ce sujet ! ;+) En 2010, j'en avais réalisé un pour l' installation d'Oracle 10g sous CentOS 5.5.

# data[].attributes.headline
Une installation pour Oracle Database 12c grandement facilitée !

# data[].attributes.subject
Une installation pour Oracle Database 12c grandement facilitée !

# data[].attributes.attachment_link.readable_text
L'intérêt de Oracle Linux est de disposer d'un paquet de configuration de la distribution en vue d'accueillir Oracle Database 12c R1. L'installation de la base de données relationnelles est, de ce fait, beaucoup plus simple à réaliser que sur les distributions Red Hat, CentOS, Fedora, SUSE ou OpenSUSE. Après avoir configuré les dépôts sous Oracle Linux et afin d'installer le paquet de " préinstallation ", tapez à partir de la ligne de commande :

yum install oracle-rdbms-server-12cR1-preinstall

Pour la version 11g, il s'agit du paquet RPM oracle-rdbms-server-11gR2-preinstall. J'ai d'ailleurs rencontré récemment de grosses complications à installer Oracle Database 11g sur une distribution Oracle Linux 7.2. J'ai d'ailleurs dû repasser en 7.1.

La liste des modifications apportées

Après avoir installé quelques dépendances nécessaires à l'installation d'Oracle, le paquet modifie la configuration de la distribution Oracle Linux. Toutes les modifications apportées sont consultables à partir du fichier /var/log/oracle-rdbms-server-12cR1-preinstall/results/orakernel.log :

Adding group oinstall with gid 54321
 Adding group dba
 Adding user oracle with user id 54321, initial login group oinstall, supplementary group dba and home directory /home/oracle
 Changing ownership of /home/oracle to oracle:oinstall
 Please set password for oracle user
 uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba)
 Creating oracle user passed

 Verifying kernel parameters as per Oracle recommendations...
 Adding fs.file-max = 6815744
 Adding kernel.sem = 250 32000 100 128
 Adding kernel.shmmni = 4096
 Adding kernel.shmall = 1073741824
 Adding kernel.shmmax = 4398046511104
 Adding kernel.panic_on_oops = 1
 Adding net.core.rmem_default = 262144
 Adding net.core.rmem_max = 4194304
 Adding net.core.wmem_default = 262144
 Adding net.core.wmem_max = 1048576
 Adding net.ipv4.conf.all.rp_filter = 2
 Adding net.ipv4.conf.default.rp_filter = 2
 Adding fs.aio-max-nr = 1048576
 Adding net.ipv4.ip_local_port_range = 9000 65500
 Setting kernel parameters as per oracle recommendations...
 Altered file /etc/sysctl.conf
 Original file backed up at /etc/sysctl.conf.orabackup
 Verifying & setting of kernel parameters passed

 Setting user limits using /etc/security/limits.d/oracle-rdbms-server-12cR1-preinstall.conf

Verifying oracle user OS limits as per Oracle recommendations...
 Adding oracle soft nofile 1024
 Adding oracle hard nofile 65536
 Adding oracle soft nproc 16384
 Adding oracle hard nproc 16384
 Adding oracle soft stack 10240
 Adding oracle hard stack 32768
 Adding oracle hard memlock 134217728
 Adding oracle soft memlock 134217728
 Setting oracle user OS limits as per Oracle recommendations...
 Altered file /etc/security/limits.d/oracle-rdbms-server-12cR1-preinstall.conf
 Original file backed up at /var/log/oracle-rdbms-server-12cR1-preinstall/backup/Feb-29-2016-20-55-56
 Verifying & setting of user limits passed

Verifying kernel boot parameters as per Oracle recommendations...
 old boot params: "rhgb quiet", new boot params: "rhgb quiet numa=off"

old boot params: "rhgb quiet numa=off", new boot params: "rhgb quiet numa=off transparent_hugepage=never"

Setting kernel boot parameters as per Oracle recommendations...
 Generating grub configuration file ...
 Found linux image: /boot/vmlinuz-4.1.12-32.2.1.el7uek.x86_64
 Found initrd image: /boot/initramfs-4.1.12-32.2.1.el7uek.x86_64.img
 Found linux image: /boot/vmlinuz-3.10.0-327.10.1.el7.x86_64
 Found initrd image: /boot/initramfs-3.10.0-327.10.1.el7.x86_64.img
 Found linux image: /boot/vmlinuz-3.10.0-327.el7.x86_64
 Found initrd image: /boot/initramfs-3.10.0-327.el7.x86_64.img
 Found linux image: /boot/vmlinuz-3.8.13-118.3.2.el7uek.x86_64
 Found initrd image: /boot/initramfs-3.8.13-118.3.2.el7uek.x86_64.img
 Found linux image: /boot/vmlinuz-3.8.13-98.7.1.el7uek.x86_64
 Found initrd image: /boot/initramfs-3.8.13-98.7.1.el7uek.x86_64.img
 Found linux image: /boot/vmlinuz-0-rescue-73404e47839844c3abc33fc226d1ec43
 Found initrd image: /boot/initramfs-0-rescue-73404e47839844c3abc33fc226d1ec43.img
 done
 Boot parameters will be effected on next reboot
 Altered file /etc/default/grub
 Original file backed up at /etc/default/grub.orabackup
 Verifying & setting of boot parameters passed

Trying to add NOZEROCONF parameter...
 Taking a backup of existing file to /etc/sysconfig/network.orabackup
 Successfully added parameter NOZEROCONF to /etc/sysconfig/network
 Setting /etc/sysconfig/network parameters passed

Disabling Transparent Hugepages.
 Refer Oracle Note:1557478.1

Disabling defrag.
 Refer Oracle Note:1557478.1

Une copie des 4 fichiers modifiés /etc/sysconfig/network, /etc/default/grub, /boot/grub2/grub.cfg et /etc/sysctl.conf est faite dans leurs répertoires initiaux, affublés du suffixe .orabackup. Pensez à les mettre au chaud !

Le mot de passe de l'utilisateur Oracle

Il vous reste à mettre un mot de passe à l'utilisateur Oracle :

passwd oracle

L'installation va pouvoir commencer !

# data[].attributes.attachment_link.title
| Le paquet preinstall pour Oracle Database 12c R1

# data[].attributes.attachment_link.description
L'intérêt de Oracle Linux est de disposer d'un paquet de configuration de la distribution en vue d'accueillir Oracle Database 12c R1. L'installation de la base de données relationnelles est, de ce fait, beaucoup plus simple à réaliser que sur les distributions Red Hat, CentOS, Fedora, SUSE ou OpenSUSE.

# data[].attributes.headline
Le fichier de lancement pour Oracle 12c sous Systemd

# data[].attributes.subject
Le fichier de lancement pour Oracle 12c sous Systemd

# data[].attributes.attachment_link.readable_text
A l'occasion d'un commentaire, Etienne s'était étonné que je fasse encore appel à ce bon vieux rc.local sous systemd. Sous Linux, tout finit par être compliqué ! ;+) A l'occasion de l'installation de Oracle Database 12c sous Oracle Linux 7.2, je viens de décider de passer sous systemd. Non sans mal.

Création du fichier oraorcl.service

La création des fichiers de lancements de service se fait sous Oracle Linux / CentOS / Fedora / Red Hat dans le dossier /usr/lib/systemd/system de votre système de fichiers. Dans le cas présent, je l'ai nommé oraorcl.service :

[Unit]
Description=oraorcl
After=syslog.target
After=network.target

[Service]
Type=forking
Environment=ORACLE_HOME=/oracle/product/12.1.0/dbhome_1
User=oracle
Group=oinstall
ExecStart=/oracle/product/12.1.0/dbhome_1/bin/dbstart ${ORACLE_HOME}
ExecStop=/oracle/product/12.1.0/dbhome_1/bin/dbshut ${ORACLE_HOME}
ReStart=yes

[Install]
WantedBy=multi-user.target

Activation, démarrage et arrêt du service

Une fois créé conformément au modèle indiqué ci-dessus, vous n'avez plus qu'à l'activer, puis à le démarrer comme suit :

systemctl enable oraorcl
systemctl start oraorcl

Pour arrêter le service, tapez systemctl stop oraorcl.

# data[].attributes.attachment_link.title
| Démarrer Oracle Database 12c avec systemd

# data[].attributes.attachment_link.description
A l'occasion d'un commentaire, Etienne s'était étonné que je fasse encore appel à ce bon vieux rc.local sous systemd. Sous Linux, tout finit par être compliqué ! ;+) A l'occasion de l'installation de Oracle Database 12c sous Oracle Linux 7.2, je viens de décider de passer sous systemd. Non sans mal.

# data[].attributes.headline
Limites de taille des bases sous Oracle Database

# data[].attributes.subject
Limites de taille des bases sous Oracle Database

# data[].attributes.attachment_link.readable_text
Les limites des tablespaces utilisés dans les bases de données Oracle n'ont pas changé depuis la version 10g. Elle est toujours de 128 To par tablespace, que ce soit en mode mono-fichier ( bigfile) ou multi-fichiers ( smallfile).

SmallFile

1022 fichiers x 4 millions de blocs x 32 Ko/bloc = 128 To

BigFile

4 milliards de blocs x 32 Ko/bloc = 128 To

Limites des systèmes de fichiers

Les limites ne viennent pas des systèmes de fichiers aux capacités de stockage théorique très supérieures aux limites des fichiers Oracle Database, à l'exception notoire des systèmes Ext3 et Ext4

Les limites

# data[].attributes.attachment_link.title
| Oracle : limites des tablespaces SmallFile et BigFile

# data[].attributes.attachment_link.description
Les limites des tablespaces utilisés dans les bases de données Oracle n'ont pas changé depuis la version 10g. Elle est toujours de 128 To par tablespace, que ce soit en mode mono-fichier ( bigfile) ou multi-fichiers ( smallfile).

# data[].attributes.headline
Vous avez perdu votre mot de passe sys sous Oracle : pas grave !

# data[].attributes.subject
Vous avez perdu votre mot de passe sys sous Oracle : pas grave !

# data[].attributes.attachment_link.readable_text
Il peut arriver au niveau de vieilles instances Oracle de perdre le mot de passe de l'utilisateur SYS. Il existe deux techniques pour en changer sans le connaître.

Utilisation de la commande ALTER USER

A partir du serveur Linux, connectez-vous en tant qu'utilisateur oracle :

su oracle

Vous devez ensuite vous connecter à l'instance Oracle via sqlplus. L'authentification se fait sans que vous ayez à connaître le mot de passe de l'utilisateur SYS :

$ORACLE_HOME/bin/sqlplus / AS SYSDBA

Enfin, pour passer le mot de passe de l'utilisateur sys à pwd à l'aide de la commande ALTER USER, tapez sous l'interpréteur SQL la commande :

ALTER USER sys IDENTIFIED BY "pwd";
EXIT

Utilisation de la commande orapwd

La modification du mot de passe de l'utilisateur SYS modifie le fichier $ORACLE_HOME/dbs/orapworcl, si le SID de l'instance de la base de données Oracle est .

Toujours avec le compte oracle, déplacez vous dans le répertoire $ORACLE_HOME/dbs pour y exécuter la commande $ORACLE_HOME/bin/orapwd. Faites tout d'abord une copie du fichier $ORACLE_HOME/dbs/orapworcl si le SID de l'instance est :

su oracle
cd $ORACLE_HOME/dbs
cp orapworcl orapworcl.ori
$ORACLE_HOME/bin/orapwd force=y file=orapworcl entries=10

L'argument entries représente le nombre de comptes autorisés à se connecter sous les rôles SYSDBA et SYSOPER. Quant au paramètre passé à force, il permet d'écraser le fichier existant !

# data[].attributes.attachment_link.title
| Oracle : perte du mot de passe du compte SYSDBA

# data[].attributes.attachment_link.description
Il peut arriver au niveau de vieilles instances Oracle de perdre le mot de passe de l'utilisateur SYS. Il existe deux techniques pour en changer sans le connaître. Utilisation de la commande ALTER USER A partir du serveur Linux, connectez-vous en tant qu'utilisateur oracle : su oracle Vous devez ensuite vous connecter à l'instance Oracle via sqlplus.

# data[].attributes.headline
Réduire les bases temporaires sous Oracle Database

# data[].attributes.subject
Réduire les bases temporaires sous Oracle Database

# data[].attributes.attachment_link.readable_text
Bien que fonctionnant sur la version 12c, je n'ai pas été fichu de remettre la main sur l'instruction qui permet de réduire le tablespace TEMP à sa portion congrue. Du coup, il a fallu que je reprenne la documentation de la version 11g d'Oracle Database.

Le tablespace temporaire permet de gérer des opérations temporaires telles que les jointures et les tris lorsque la RAM ne s'avère plus suffisante pour les réaliser.

Réduire et retailler le tablespace TEMP

Les deux commandes suivantes réduisent les données du tablespace TEMP et retaillent le fichier qui lui est associé à 1 Mo :

ALTER TABLESPACE temp SHRINK SPACE KEEP 0M;
ALTER DATABASE TEMPFILE '/oracle/oradata/orcl/temp01.dbf' RESIZE 1232896;

# data[].attributes.attachment_link.title
| Oracle : réduire la taille du tablespace TEMP

# data[].attributes.attachment_link.description
Bien que fonctionnant sur la version 12c, je n'ai pas été fichu de remettre la main sur l'instruction qui permet de réduire le tablespace TEMP à sa portion congrue. Du coup, il a fallu que je reprenne la documentation de la version 11g d'Oracle Database.

# data[].attributes.headline
Tiens, un problème sur Listener sur Oracle Database 11g !

# data[].attributes.subject
Tiens, un problème sur Listener sur Oracle Database 11g !

# data[].attributes.attachment_link.readable_text
A l'occasion d'une formation récente où nous avons installé témérairement Oracle Database 11g sur Oracle Linux 7.1, nous avons eu une erreur - sur certains postes - au lancement du listener que je n'avais encore jamais rencontrée.

En lançant malencontreusement la commande $ORACLE_HOME/bin/dbstart $ORACLE_HOME avec le compte root, les droits sur le dossier /var/tmp/.oracle ont été modifiés. Si l'instance a pu se lancer, le listener ne s'est, quant à lui, jamais exécuté.

Après avoir arrêté l'instance avec la commande dbshut, nous avons dû modifier les droits sur ce répertoire comme suit :

chown oracle:oinstall /var/tmp/.oracle -R

Après vérification, sur la version 12c s'exécutant sur Oracle Linux 7.2, le fait que l'utilisateur root soit propriétaire ne semble poser aucun problème particulier.

# data[].attributes.attachment_link.title
| Problème de listener sur Oracle Database 11g

# data[].attributes.attachment_link.description
A l'occasion d'une formation récente où nous avons installé témérairement Oracle Database 11g sur Oracle Linux 7.1, nous avons eu une erreur - sur certains postes - au lancement du listener que je n'avais encore jamais rencontrée. En lançant malencontreusement la commande $ORACLE_HOME/bin/dbstart $ORACLE_HOME avec le compte root, les droits sur le dossier /var/tmp/.oracle ont été modifiés.

# data[].attributes.attachment_link.readable_text
Compte tenu de la complexité du moteur de bases de données édité par Oracle, j'ai toujours eu un doute sur l'intérêt du parallélisme. Le mécanisme consiste à augmenter le nombre de threads affecté à une table, à une requête, ... Le risque est tout de même de mettre un biais dans le fonctionnement de la base de données. La ressource utilisée doit être proportionnelle à la volumétrie des tables utilisées.

Tester le parallélisme sur Oracle Database

Vous pouvez demander à Oracle de configurer automatiquement le nombre de threads dans votre session sur une requête, selon des besoins de test.

ALTER SESSION SET parallel_degree_policy = auto;
SELECT /*+parallel */ ename, dname FROM scott.emp e, dept d WHERE e.deptno=d.deptno;

Source : How Parallel Execution Works

# data[].attributes.attachment_link.title
| Oracle Database : tester le parallélisme sur une requête

# data[].attributes.attachment_link.description
Compte tenu de la complexité du moteur de bases de données édité par Oracle, j'ai toujours eu un doute sur l'intérêt du parallélisme. Le mécanisme consiste à augmenter le nombre de threads affecté à une table, à une requête, ... Le risque est tout de même de mettre un biais dans le fonctionnement de la base de données.

# data[].attributes.headline
L'installation d'Oracle sur Oracle Linux est aujourd'hui beaucoup plus simple.

# data[].attributes.subject
L'installation d'Oracle sur Oracle Linux est aujourd'hui beaucoup plus simple.

# data[].attributes.attachment_link.readable_text
Je ne suis pas sûr que tous les paquets soient indispensables. Mais ça marche.

Paquets à installer

Vous devez installer Centos avec l'interface Gnome. Puis, installez les paquets suivants :

yum install gcc*compat-gcc*

yum install glibc* compat-glibc*

yum install compat-db*

yum install libxp* gnome-libs* openmotif

yum install cpp* libstdc++* compat-libstdc++*

yum install libaio*

yum install binutils pdksh ksh sysstat elfutils* setarch

Fichier /etc/redhat-release

Faites une copie du fichier /etc/redhat-release en /etc/redhat-release. ori et remplacez le nom de version actuelle par redhat-4.

Fichier /etc/sysconfig/networking/profiles/default/network et /etc/sysconfig/networking/profiles/default/hosts

Passez votre machine en adresse IP Fixe et définissez votre FQDN (Full Qualified Domain Name) dans les fichiers hosts et network du répertoire /etc/sysconfig/networking/profiles/default/. Rebootez le système pour intégrer les changements.

Fichier /etc/sysctl.conf

Ajoutez à votre fichier

kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 net.ipv4.ip_local_port_range = 1024 65535 net.core.rmem_default=262144 net.core.rmem_max=262144 net.core.wmem_default=262144 net.core.wmem_max=262144

Après modification, rechargez votre configuration par la commande sysctl -p.

Fichier /etc/security/limits.conf

Ajoutez au fichier /etc/security/limits.conf les lignes suivantes :

oracle soft nproc 2048 oracle hard nproc 16384 oracle soft nofile 1024 oracle hard nofile 65536

Ajoutez Au fichier /etc/pam.d/login :

session required pam_limits.so

Désactiver SELinux

Editez le fichier /etc/selinux/config et passez la valeur de SELINUX à disabled :

SELINUX=disabled

Groupes, utilisateurs et droits

Ajoutez les trois groupes suivants :

groupadd oinstall groupadd dba groupadd oper

Ajoutez le compte utilisateur oracle :

useradd -g oinstall -G dba oracle passwd oracle

Créez le dossier /oracle/product/10.2.0/db_1 :

mkdir -p /oracle/product/10.2.0/db_1 chown -R oracle:oinstall /oracle

Ajoutez au fichier /home/oracle/.bash_profile de l'utilisateur oracle les directives suivantes :

TMP=/tmp; export TMP TMPDIR=$TMP; export TMPDIR ORACLE_BASE=/oracle; export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME ORACLE_SID=ORCL; export ORACLE_SID ORACLE_TERM=xterm; export ORACLE_TERM PATH=$ORACLE_HOME/bin:$PATH; export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH ulimit -u 16384 -n 65536

Installation d'Oracle 10g

A partir de l'interface graphique, connectez-vous en mode graphique (sous Gnome). Téléchargez la version correspondant à votre distribution. Dézippez l'archive au format zip. A partir de la console graphique gnome-terminal, allez dans le répertoire de l'archive dézippée et lancez ./runInstaller.

Post-installation

Editez le fichier /etc/oratab et modifiez la dernière ligne du fichier comme suit :

orcl:/oracle/product/10.2.0/db_1:Y

Editez le fichier /etc/rc.local en ajoutant :

ORA_HOME=/oracle/product/10.2.0/db_1 ORA_OWNER=oracle export ORA_HOME export ORA_OWNER su - $ORA_OWNER -c " $ORA_HOME/bin/dbstart $ORA_HOME "

Remplacez enfin la ligne 78 du fichier /oracle/product/10.2.0/db_1/bin/dbstart par :

ORACLE_HOME_LISTNER=$ORACLE_HOME

Ecrasez le fichier /etc/redhat-release par le fichier /etc/redhat-release.ori/

Gestion de l'instance Oracle

Pour arrêter manuellement l'instance Oracle, tapez :

su - oracle -c " /oracle/product/10.2.0/db_1/bin/dbshut /oracle/product/10.2.0/db_1 "

Pour démarrer manuellement l'instance Oracle, tapez :

su - oracle -c " /oracle/product/10.2.0/db_1/bin/dbstart /oracle/product/10.2.0/db_1 "

Qui a dit que c'était simple Linux ? Sources : Nix World, ORACLE-BASE, All about my dear Linux, Ivan Kartik

# data[].attributes.attachment_link.description
Je ne suis pas sûr que tous les paquets soient indispensables. Mais ça marche. Vous devez installer Centos avec l'interface Gnome. Puis, installez les paquets suivants : yum install gcc*compat-gcc* yum install glibc* compat-glibc* yum install compat-db* yum install libxp* gnome-libs* openmotif yum install cpp* libstdc++* compat-libstdc++* yum install libaio* yum install binutils pdksh ksh sysstat elfutils* setarch Faites une copie du fichier /etc/redhat-release en /etc/redhat-release.

# data[].attributes.headline
Quelques sites pour vous aider à administrer une base de données sur Oracle

# data[].attributes.subject
Quelques sites pour vous aider à administrer une base de données sur Oracle

# data[].attributes.attachment_link.keywords[]
système de gestion de bases de données relationnel

# data[].attributes.attachment_link.title
| Ressources sur le moteur de base de données relationnel Oracle

# data[].attributes.attachment_link.description
Ressources sur le moteur de base de données relationnel Oracle

# data[].attributes.headline
Pour bénéficier du support Oracle, vous ne pouvez pas utilisez n'importe quelle distribution Linux !

# data[].attributes.subject
Pour bénéficier du support Oracle, vous ne pouvez pas utilisez n'importe quelle distribution Linux !

# data[].attributes.attachment_link.readable_text
C'est une des raisons qui m'ont amené à choisir de travailler sous Linux en environnement Red Hat et SUSE, sur Centos et openSUSE. Il m'arrive d'utiliser la Ubuntu Server, lorsque le projet du client ne nécessite pas l'installation de logiciels commerciaux.

Oracle ne prend en charge le support pour Oracle Database 10g et 11g que sur trois distributions Linux :

Source : Linux - Oracle FAQ

# data[].attributes.attachment_link.title
| Distributions Linux supportées par Oracle Database 10g et 11g

# data[].attributes.attachment_link.description
C'est une des raisons qui m'ont amené à choisir de travailler sous Linux en environnement Red Hat et SUSE, sur Centos et openSUSE. Il m'arrive d'utiliser la Ubuntu Server, lorsque le projet du client ne nécessite pas l'installation de logiciels commerciaux.

# data[].attributes.headline
Une formation pour développeurs sur PostgreSQL !

# data[].attributes.subject
Une formation pour développeurs sur PostgreSQL !

# data[].attributes.attachment_link.readable_text
Suite à une demande concernant une formation PostgreSQL pour développeurs, j'ai rédigé une nouvelle offre de formation concernant la base de données Open Source.

DDL, DML, PL/pgSQL

Au cours des 3 jours de formation, je vous présenterai les spécificités du langage DDL et DML utilisé dans PostgreSQL. Nous verrons également comment concevoir des procédures stockées, des fonctions et des curseurs dans le langage PL/pgSQL de l'éditeur très proche de celui utilisé par Oracle.

Gestion des données

La dernière partie de la formation sera notamment consacrée à la gestion des tables distantes soit à l'aide des dblinks, soit avec les Foreign Data Wrappers. Nous verrons les dumps et aussi les techniques d'import/export des données de vos bases PostgreSQL.

Cette formation est strictement réservée à des développeurs ou des DBA qui ont déjà des connaissances de base en SQL sur les commandes SELECT, INSERT, UPDATE, DELETE, les clauses WHERE, GROUP BY ainsi que sur les jointures.

# data[].attributes.attachment_link.title
| 3 jours pour une formation PostgreSQL Développement

# data[].attributes.attachment_link.description
Suite à une demande concernant une formation PostgreSQL pour développeurs, j'ai rédigé une nouvelle offre de formation concernant la base de données Open Source. DDL, DML, PL/pgSQL Au cours des 3 jours de formation, je vous présenterai les spécificités du langage DDL et DML utilisé dans PostgreSQL.

# data[].attributes.headline
Des logs qui gonflent au point de ne plus permettre le redémarrage de l'instance

# data[].attributes.subject
Des logs qui gonflent au point de ne plus permettre le redémarrage de l'instance

# data[].attributes.attachment_link.readable_text
J'ai passé un merveilleux moment la semaine dernière à former Gaëtan et ses collègues d'une très grande entreprise amiénoise sur Oracle PL/SQL. Au cours de cette formation, Gaëtan m'a fait part du blocage du listener sur des systèmes Windows 32 sur lesquels étaient installés Oracle Database 11g, dès lors que le fichier listener.log atteignait les 4 Go.

Désactiver la consignation des activités de connexions au niveau du Listener

La solution consiste, en fait, à désactiver la consignation de l'activité du listener, en ajoutant au fichier listener.ora :

LOGGING_LISTENER=off

Cette méthode exige que vous redémarriez le listener dans la console des services Windows.

L'autre solution, à chaud, est de taper la commande :

lsnrctl set log_status off

Les deux actions sont d'ailleurs complémentaires. Et prout (1) alors !

(1) Désolé, chers lecteurs, de vous prendre en otage d'un pari stupide passé avec Gaëtan ! ;+)

# data[].attributes.attachment_link.description
J'ai passé un merveilleux moment la semaine dernière à former Gaëtan et ses collègues d'une très grande entreprise amiénoise sur Oracle PL/SQL. Au cours de cette formation, Gaëtan m'a fait part du blocage du listener sur des systèmes Windows 32 sur lesquels étaient installés Oracle Database 11g, dès lors que le fichier listener.log atteignait les 4 Go.

# data[].attributes.attachment_link.readable_text
Les ETL ( Extract-Transform-Load) ont le vent en poupe. Pour ceux d'entre vous qui ne savent pas de quoi il s'agit, sachez que les ETL ont pour rôle de charger les données des bases de données relationnelles associées aux applications métier pour alimenter les entrepôts de données (DataWare House) afin de fournir aux décideurs l'information dont ils ont besoin pour piloter de magnifiques tableaux de bord. Je simplifie. Les principaux ETL utilisés aujourd'hui sont :

Talend, un logiciel Open Source écrit en Java, qui, avec Talend Data Integration, propose aux entreprises une version payante répondant à des besoins plus " professionnels " ;

Microsoft SQL Server Integration Services.

Les raisons d'un succès

Si les DSI poussent aujourd'hui à l'utilisation de ce type de produits, c'est avant tout parce qu'ils ne veulent plus entendre des procédures stockées faites " maison " difficiles à maintenir, du fait que bien de peu d'informaticiens ont encore la maîtrise du SQL. L'abandon de l'offre de formations longues - ou ce qu'il en reste - en matière de bases de données aura eu raison d'une technologie pourtant beaucoup plus efficace. Je vous incite si vous n'en êtes pas convaincu à prendre le temps de tester les instructions FORALL et BULK INSERT utilisables dans le moteur Oracle 11g au sein de vos procédures stockées.

Serveur lié, DBLINK et DATALINK

Que ce soit sur SQL Server, Oracle ou PostgreSQL, la solution alternative à l'ETL passe par la mise en place d'un serveur lié, d'un DBLINK ou encore d'un DATALINK. Si SQL Server prend assez simplement en charge ODBC, c'est un peu plus compliqué pour leurs homologues. Chez Oracle, cela passe par la configuration du composant Oracle Database Gateway for ODBC pour pouvoir ensuite créer un DBLINK ! Pour PostgreSQL, il faut recourir à la mise en place d'une extension du type Foreign Data Wrappers. La version 9.6 du moteur de bases de données Open Source PostgreSQL prend en charge désormais les jointures, les tris, les UPDATE et DELETE sur des bases distantes. Les trois moteurs disposent de l'instruction MERGE ( UPSERT pour PostgreSQL).

Vous l'aurez compris, l'autre alternative aux ETL et à leur lenteur relative, c'est de refaire de la procédure stockée, du SQL, du PL/SQL, du Transact-SQL. Cela vous évitera de débourser 100 000 euros pour l'achat d'un ETL comme Informatica, comme a pu le faire une grande assurance française, dont je formais l'un de ses salariés au PL/SQL. A la fois, que représentent 100 000 euros lorsque vous êtes amené à verser au minimum plus de 60 000 euros - charges patronales incluses - par an à un DBA chargé de maintenir des procédures stockées, lorsque vous faites plus de 17 milliards de chiffre d'affaires ?

Le vieux monde s'en va. ;+)

# data[].attributes.attachment_link.keywords[]
formateur sql server integration services

# data[].attributes.attachment_link.description
Les ETL ( Extract-Transform-Load) ont le vent en poupe. Pour ceux d'entre vous qui ne savent pas de quoi il s'agit, sachez que les ETL ont pour rôle de charger les données des bases de données relationnelles associées aux applications métier pour alimenter les entrepôts de données (DataWare House) afin de fournir aux décideurs l'information dont ils ont besoin pour piloter de magnifiques tableaux de bord.

# data[].attributes.body
Les datalinks dans Postgr

# data[].attributes.attachment_link.readable_text
Un datalink - dblink dans PostgreSQL - peut se définir comme un moyen de se connecter à partir d'un cluster - ou instance PostgreSQL - à un autre cluster localisé ou non sur la même machine. Autrement dit, à l'aide d'un datalink, vous pouvez interroger une base de données distante, extraire des informations, ajouter des informations à partir de cette base.

Installation sous Ubuntu Server

L'installation sous Ubuntu Server 11.10 se fait très simplement :

apt-get install postgresql-contrib-9.1

Mettre en place l'extension dblink

Vous devez tout d'abord vous connecter en local à PostgreSQL :

Une fois connecté(e), vous devez exécuter le script dblink et créer l'extension :

\i /usr/share/postgresql/9.1/extension/dblink-1.0.sql
CREATE EXTENSION dblink;
\q

Extraction de données

L'exemple ci-dessous permet d'extraire les données du champ client_rs de la table crm.client de la base erp de l'instance de la machine dont l'ip est 147.99.98.255, qui s'exécute sur le port tcp par défaut. Vous pouvez taper tous ces ordres SQL à partir de pgAdmin.

SELECT
*
FROM
dblink('hostaddr=147.99.98.255 port=5432 user=postgres dbname=erp password=postgres',
'SELECT client_rs FROM crm.client')
AS clients(client varchar(100));

Ajout de données à une table existante (1re méthode)

Cet autre exemple illustre la manière de récupérer des données d'une instance à une autre, en les ajoutant à partir d'une table distante.

INSERT INTO crm.client(client_rs,client_cp,client_ville)
SELECT
*
FROM
dblink('hostaddr=147.99.98.255 port=5432 user=postgres dbname=erp password=postgres',
'SELECT client_rs,client_cp,client_ville FROM crm.client WHERE client_rs= "linux "')
AS clients(client varchar(100), cp integer,ville varchar(50));

Ajout de données à une table existante (2e méthode)

L'exemple est une variante, s'appuyant sur le SELECT... INTO plutôt que d'utiliser l'instruction INSERT INTO.

SELECT
*
INTO TABLE crm.client
FROM
dblink('hostaddr=147.99.98.255 port=5432 user=postgres dbname=erp password=postgres',
'SELECT client_rs,client_cp,client_ville FROM crm.client')
AS clients(client varchar(100), cp integer,ville varchar(50));

Création de table

Je termine ce petit tuto par un script SQL de création de table locale à partir d'une table d'une machine distante.

CREATE TABLE crm.client AS
SELECT
*
FROM
dblink('hostaddr=147.99.98.255 port=5432 user=postgres dbname=postgres password=postgres',
'SELECT * FROM crm.client')
AS clients(client_id integer,client_rs varchar(100), client_cp integer, client_ville varchar(50));

# data[].attributes.attachment_link.title
| PostgreSQL : créer et utiliser des datalinks

# data[].attributes.attachment_link.description
Un datalink - dblink dans PostgreSQL - peut se définir comme un moyen de se connecter à partir d'un cluster - ou instance PostgreSQL - à un autre cluster localisé ou non sur la même machine. Autrement dit, à l'aide d'un datalink, vous pouvez interroger une base de données distante, extraire des informations, ajouter des informations à partir de cette base.

# data[].attributes.attachment_link.readable_text
C'est un des reproches qu'on peut faire à PostgreSQL : la gestion des triggers - ou déclencheurs - est beaucoup plus compliquée que dans Oracle.

Création d'un trigger

Le trigger, dans l'exemple ci-dessous, a pour objet de passer automatiquement le contenu du champ client_ville de la table crm.client en majuscules. La création d'un déclencheur se passe en deux temps.

Vous devez tout d'abord créer une fonction.

CREATE OR REPLACE FUNCTION crm.client_func_before() RETURNS trigger AS $ client_func_before$
BEGIN
NEW.client_ville := UPPER(NEW.client_ville);
RETURN NEW;
END;
$ client_func_before$ LANGUAGE plpgsql;

Vous pouvez ensuite créer votre trigger.

CREATE TRIGGER client_trg_before
BEFORE INSERT OR UPDATE ON crm.client
FOR EACH ROW
EXECUTE PROCEDURE crm.client_func_before() ;

La suppression du trigger

La suppression de la fonction associée au trigger entraîne, à l'aide de la clause CASCADE, la suppression du trigger.

DROP FUNCTION crm.client_func_before() CASCADE;

Mais vous pouvez aussi décider de la suppression du trigger sans pour autant détruire la fonction, notamment si elle est utilisée par d'autres triggers.

DROP TRIGGER IF EXISTS tiers_trg_before ON gestion.tiers CASCADE;

# data[].attributes.attachment_link.title
| PostgreSQL : créer, supprimer un trigger

# data[].attributes.attachment_link.description
C'est un des reproches qu'on peut faire à PostgreSQL : la gestion des triggers - ou déclencheurs - est beaucoup plus compliquée que dans Oracle. Création d'un trigger Le trigger, dans l'exemple ci-dessous, a pour objet de passer automatiquement le contenu du champ client_ville de la table crm.client en majuscules.

# data[].attributes.headline
Surveillez votre agent SQL Server dont sont tributaires vos jobs et vos plans de maintenance !

# data[].attributes.subject
Surveillez votre agent SQL Server dont sont tributaires vos jobs et vos plans de maintenance !

# data[].attributes.attachment_link.readable_text
L'arrêt de l'agent SQL Server est extrêmement critique. Sans agent, les jobs n'exécutent pas les plans de maintenance et surtout les sauvegardes planifiées. Windows Server propose d'associer une tâche à un événement consigné dans le journal d'événements.

Afin de créer une alerte mail à chaque arrêt de l'agent SQL Server, je vais me servir de la commande , que j'utilise depuis de nombreuses années maintenant.

Mise en place de mailsend

Après avoir décompressé le programme mailsend, je renomme l'exécutable en mailsend.exe et je le copie dans c:\windows\system32.

Syntaxe de mailsend

L'aide de la commande mailsend peut être obtenue, en tapant à partir de l'interpréteur de commandes Windows, mailsend -h :

 Version: @(#) mailsend v1.19

 Copyright: BSD. It is illegal to use this software for Spamming

 (Compiled with OpenSSL version: OpenSSL 1.0.2h  3 May 2016)
 usage: mailsend [options]
 Where the options are:
  -copyright            - show copyright information
  -4                    - Force to use IPv4 address of SMTP server
  -6                    - Force to use IPv6 address of SMTP server
 -smtp hostname/IP* - Hostname/IP address of the SMTP server -port SMTP port - SMTP port -domain domain - domain name for SMTP HELO/EHLO -t to,to..* - email address/es of the recipient/s -cc cc,cc.. - carbon copy address/es +cc - do not ask for Carbon Copy -ct seconds - Connect timeout. Default is 5 seconds -read-timeout seconds - Read timeout. Default is 5 seconds -bc bcc,bcc.. - blind carbon copy address/es +bc - do not ask for Blind carbon copy +D - do not add Date header -f address* - email address of the sender -sub subject - subject -list_address file - a file containing a list of email addresses -log file - write log messages to this file -cs character set - for text/plain attachments (default is us-ascii) -separator character - separator used with -attach. Default is comma (,) If used must be specified before -attach -enc-type type - encoding type. base64, 8bit, 7bit etc. Default is base64. Special type is "none" -aname name - name of the attachment. Default is filename -content-id id - content-id in the attachment -mime-type type - MIME type -dispostion val - "attachment" or "inline". Default is "attachment" -attach file,mime_type,[i/a] (i=inline,a=attachment) - attach this file as attachment or inline -show-attach - show attachment in verbose mode, default is no -show-mime-types - show the compiled in MIME types -M "one line msg" - attach this one line text message -content-type type - Content type. Default: multipart/mixed -msg-body path - Path of the file to include as body of mail -embed-image image - Path of image to embed in HTML -H "header" - Add custom Header -name "Full Name" - add name in the From header -v - verbose mode -V - show version info -w - wait for a CR after sending the mail -rt email_address - add Reply-To header -rrr email_address - request read receipts to this address -rp - return-path address -ssl - SMTP over SSL -starttls - use STARTTLS if the server supports it -auth - try CRAM-MD5,LOGIN,PLAIN in that order -auth-cram-md5 - use AUTH CRAM-MD5 authentication -auth-plain - use AUTH PLAIN authentication -auth-login - use AUTH LOGIN authentication -user username - username for ESMTP authentication -pass password - password for ESMTP authentication -example - show examples -ehlo - force EHLO -info - show SMTP server information -help - shows this help -q - quiet The options with * must be specified Environment variables: SMTP_USER_PASS for plain text password (-pass)

Exemple d'utilisation de mailsend

L'utilisation de mailsend se fait comme suit :

mailsend -smtp mail.gmail.com -t dszalkowski@gmail.com -f dszalkowski@gmail.com  -auth -starttls -user "dszalkowski" -pass "mon_mot_de_passe" -sub "Objet / Test" -M "Message / Test"

Création d'une alerte événementielle

A partir du journal d'application, faites un clic droit sur l'événement relatif à l'arrêt de l'agent SQL Server.

Donnez un nom à la tâche.

L'assistant rappelle l'événement qui sera enregistré.

Vous devez choisir de démarrer un programme. Les autres méthodes sont obsolètes.

Indiquez le chemin vers le programme mailsend.exe, en ajoutant tous les arguments de la commande dans Ajouter des arguments (facultatif).

Pour modifier la tâche nouvellement créée , vous devez exécuter le planificateur de tâches de Windows, taskschd.msc.

Dans les propriétés de la tâche, choisissez et cochez :

Exécuter même si l'utilisateur n'est pas connecté

Exécuter avec les autorisations maximales

# data[].attributes.attachment_link.title
Alerte mail sur l'arrêt de l'agent SQL Server

# data[].attributes.attachment_link.description
L'arrêt de l'agent SQL Server est extrêmement critique. Sans agent, les jobs n'exécutent pas les plans de maintenance et surtout les sauvegardes planifiées. Windows Server propose d'associer une tâche à un événement consigné dans le journal d'événements.

# data[].attributes.headline
Un script PowerShell pour faire la sauvegarde à froid de vos bases master, msdb et model de vos instances SQL Server.

# data[].attributes.subject
Un script PowerShell pour faire la sauvegarde à froid de vos bases master, msdb et model de vos instances SQL Server.

# data[].attributes.attachment_link.readable_text
Les bases master, msdb et model sont des bases critiques pour SQL Server. S'il est assez facile de remplacer la base model par celle provenant d'une autre installation de la version SQL server installée sur votre machine, il n'en est pas de même pour master et msdb. La base master contient des informations liées à l'installation et la configuration de l'instance, les comptes de connexions à l'instance. Quant à msdb, elle contient les plans de maintenance et les travaux.

Avant de mettre en production votre base, je vous conseille de réaliser une sauvegarde à froid des fichiers liés à ces bases à l'aide de ce script PowerShell :

Clear-Host
#L'instruction  Invoke-Sqlcmd exécutera la requête dans le contexte défini par Set-Location
Set-Location SQLSERVER:\SQL\CHEZNOI\DSFC
[String]$sql="
    SELECT
        physical_name as path
    FROM
        sys.databases AS db
        INNER JOIN sys.master_files AS mf ON db.database_id=mf.database_id
    WHERE
        db.name IN ('master','msdb','model')
"
$files=Invoke-Sqlcmd -Query $sql
#Arrêt des services pour pouvoir procéder à la copie consistante des fichiers
[String]$sqlagent='SQLAgent$DSFC'
[String]$mssql='MSSQL$DSFC'
Stop-Service -Name $sqlagent -Force:$true
Stop-Service -Name $mssql -Force:$true
#Copie de l'emplacement d'origine et des fichiers des bases master, msdb, model
[String]$destination='d:\sauve'
$files|Export-Csv -Path "$destination\emplacements.csv" -Force:$true -NoTypeInformation
foreach($file in $files)
{
    Copy-Item -Path ($file).path -Destination $destination -Force:$true
}
#Redémarrage des services
Start-Service -Name $mssql
Start-Service -Name $sqlagent
#Nettoyage des objets PowerShell
$file=$null
$files=$null

# data[].attributes.attachment_link.title
SQL Server : sauvegarde à froid des bases master, msdb et model

# data[].attributes.attachment_link.description
Les bases master, msdb et model sont des bases critiques pour SQL Server. S'il est assez facile de remplacer la base model par celle provenant d'une autre installation de la version SQL server installée sur votre machine, il n'en est pas de même pour master et msdb.

# data[].attributes.headline
La télémétrie envahit SQL Server, SSMS et SSDT.

# data[].attributes.subject
La télémétrie envahit SQL Server, SSMS et SSDT.

# data[].attributes.attachment_link.readable_text
En dehors des services de télémétrie associés à SQL Server qu'il convient de désactiver, j'ai détecté des données suffisamment explicites dans les clés de registre telles que CustomerFeedBack, Track Changes, EnableErrorReporting, UserFeedBackOptin ou encore Optin, pour comprendre que Microsoft utilisait d'autres méthodes de collecte de l'expérience utilisateur.

Je vous avoue ma grande perplexité à voir autant de " talents " à se mobiliser autour du Pentest et donc à chercher à défoncer des portes ouvertes, là où il me semble plus judicieux de s'intéresser à ce qui peut sortir de nos PC. Il y a là un enjeu de souveraineté numérique dont se foutent les adeptes du Bug Bounty, attirés par des primes issues des éditeurs mobilisant des armées de nouveaux esclaves. Le Bug Bounty est aux " informaticiens " ce que Zidane est aux quartiers nord de Marseille.

Clés de registre

Autour de SQL Server 2017, SSMS 17, SSDT 15, j'ai trouvé ces quelques données dont la valeur doit être passée à 0

[HKEY_CURRENT_USER\SOFTWARE\Microsoft\Microsoft SQL Server\140]
"CustomerFeedback"=dword:00000000

[HKEY_CURRENT_USER\SOFTWARE\Microsoft\SQL Server Management Studio\14.0\Text Editor]
"Track Changes"=dword:00000000

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\140]
"EnableErrorReporting"=dword:00000000
"CustomerFeedback"=dword:00000000

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\140\Tools\Setup]
"UserFeedbackOptIn"=dword:00000000

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\CPE]
"EnableErrorReporting"=dword:00000000
"CustomerFeedback"=dword:00000000

[HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server\140]
"EnableErrorReporting"=dword:00000000
"CustomerFeedback"=dword:00000000

[HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server\140\Tools\Setup]
"UserFeedbackOptIn"=dword:00000000

[HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\VSCommon\15.0\SQM]
"OptIn"=dword:00000000

Un script PowerShell

Je vous ai concocté un script PowerShell chargé de modifier les valeurs des données des clés de registres.

Clear-Host
$paths=
    'HKCU:\SOFTWARE\Microsoft\Microsoft SQL Server\140',
    'HKCU:\SOFTWARE\Microsoft\SQL Server Management Studio\14.0\Text Editor',
    'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\140',
    'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\140\Tools\Setup',
    'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\CPE',
    'HKLM:\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server\140',
    'HKLM:\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server\140\Tools\Setup',
    'HKLM:\SOFTWARE\WOW6432Node\Microsoft\VSCommon\15.0\SQM'
$datas=
    'CustomerFeedback',
    'Track Changes',
    'EnableErrorReporting',
    'UserFeedbackOptIn',
    'EnableErrorReporting',
    'EnableErrorReporting',
    'UserFeedbackOptIn',
    'OptIn'
$values=0,0,0,0,0,0,0,0
$n=$paths.Length
For($i=0;$i -lt $n;$i++)
{
    $path=$paths[$i]
    $data=$datas[$i]
    $value=$values[$i]
    If (Test-Path -Path $path)
    {
        Set-ItemProperty -Path $path -Name $data -Value $value
        If ($data -eq 'EnableErrorReporting')
        {
            Set-ItemProperty -Path $path -Name 'CustomerFeedback' -Value 0
        }
    }
}

# data[].attributes.attachment_link.title
La télémétrie dans SQL Server 2017, SSMS et SSDT... suite

# data[].attributes.attachment_link.description
En dehors des services de télémétrie associés à SQL Server qu'il convient de désactiver, j'ai détecté des données suffisamment explicites dans les clés de registre telles que CustomerFeedBack, Track Changes, EnableErrorReporting, UserFeedBackOptin ou encore Optin, pour comprendre que Microsoft utilisait d'autres méthodes de collecte de l'expérience utilisateur.

# data[].attributes.headline
Des services SQL Server qui envoient vos données d'utilisation relative à SQL Server !

# data[].attributes.subject
Des services SQL Server qui envoient vos données d'utilisation relative à SQL Server !

# data[].attributes.attachment_link.readable_text
Depuis la version 2014, l'installeur de Microsoft ajoute des services de télémétrie afin que l'éditeur dispose des données d'usage des services SSIS, SSAS, SSRS installés. Je ne sais pas, à vrai dire, de quelle nature sont les informations qui sortent de nos machines. Et je m'en moque ! Sauf à disposer d'un pare-feu correctement configuré, elles sortent de nos machines, sans que nulle part nous ayons été conviés à activer cette fonctionnalité. J'ose croire que les entreprises françaises travaillant dans des secteurs fortement concurrentiels ou stratégiques prennent le soin de désactiver ces services CEIP, Customer Experience Improvement Program !

Un script PowerShell pour désactiver les services de télémétrie associés à SQL Server

Je vous propose deux syntaxes pour un script PowerShell dont l'objet est de désactiver les services dits CEIP associés à SQL Server :

<#
Get-Service | Where Name -Like '*TELEMETRY*'|ForEach {
    Stop-Service -Name $_.Name
    Set-Service -Name $_.Name -StartupType Disabled
}
#>
Clear-Host
$services=Get-Service | Where Name -Like '*TELEMETRY*'
#$services=Get-Service | Where Name -Like '*CEIP*'
ForEach($service in $services)
{
    Stop-Service -Name $service.Name
    Set-Service -Name $service.Name
}
$service=$null
$services=$null

# data[].attributes.attachment_link.title
SQL Server : désactiver la télémétrie des services CEIP

# data[].attributes.attachment_link.description
Depuis la version 2014, l'installeur de Microsoft ajoute des services de télémétrie afin que l'éditeur dispose des données d'usage des services SSIS, SSAS, SSRS installés. Je ne sais pas, à vrai dire, de quelle nature sont les informations qui sortent de nos machines. Et je m'en moque !

# data[].attributes.headline
Une fonction non documentée de SQL Server relative aux journaux de transaction ! Fort utile.

# data[].attributes.subject
Une fonction non documentée de SQL Server relative aux journaux de transaction ! Fort utile.

# data[].attributes.attachment_link.readable_text
A l'occasion d'une perte de données sur une base de données SQL Server du fait de l'exécution d'instructions DML ou DDL tels que UPDATE, INSERT, TRUNCATE TABLE ou CREATE/ALTER TABLE, il peut être intéressant d'en connaître l'origine, avant de remonter votre sauvegarde et de rejouer vos transactions à l'aide de votre sauvegarde complète BAK et les fichiers TRN de sauvegarde des transactions.

Des logiciels inutiles

Il existe des outils commerciaux et payants pour le faire, à l'image de ApexSQL Log ou bien de SysTools SQL Log Analyzer. Ils sont inutiles ou presque ! Vous disposez, sur SQL Server, de la fonction table sys.fn_dblog - non documentée - qui vous renvoie le contenu des transactions contenues dans votre fichier LDF, associé à votre fichier de données MDF. Tant que vous ne l'avez pas tronqué à l'occasion de votre plan de maintenance et/ou d'une sauvegarde complète, vous pouvez lister les transactions effectuées, après avoir choisi la base de données concernée à l'aide de l'instruction USE.

Utilisation de la fonction table sys.fn_dblog

USE Northwind;
GO
WITH
	BT AS
	(
		SELECT
			[Begin Time] AS debut,
			[Transaction Name] AS type,
			Operation AS operation,
			[Transaction ID] AS numtrans
			--AllocUnitName,
		FROM
			sys.fn_dblog(NULL,NULL)	AS BT
		WHERE [Begin Time] IS NOT NULL
	),
	ET AS
	(
		SELECT
			Operation AS operation,
			[End Time] AS fin,
			[Transaction ID] AS numtrans
		FROM
			sys.fn_dblog(NULL,NULL)
		WHERE
			[End Time] IS NOT NULL
	),
	A AS
	(
		SELECT
			[Transaction ID] AS numtrans,
			AllocUnitName AS objet
		FROM
			sys.fn_dblog(NULL,NULL)
		WHERE
			AllocUnitName IS NOT NULL
	)
SELECT DISTINCT
	BT.type,
	BT.debut,
	ET.fin,
	BT.operation,
	ET.operation,
	A.objet
FROM
	BT
	INNER JOIN ET ON BT.numtrans=ET.numtrans
	INNER JOIN A ON ET.numtrans=A.numtrans
WHERE
	ET.operation != 'LOP_ABORT_XACT'
AND
	BT.type IN ('UPDATE','DELETE','TRUNCATE TABLE','INSERT')
ORDER BY
	debut,fin;
GO

Résultat de la requête

La requête présentée ci-dessus permet de visualiser le début et la fin de la transaction, l'instruction DML/DDL qui lui est associée ainsi que la table sur laquelle elle porte.
Billet publié initialement le 9 octobre 2018

# data[].attributes.attachment_link.title
SQL Server : lire les journaux des transactions

# data[].attributes.attachment_link.description
A l'occasion d'une perte de données sur une base de données SQL Server du fait de l'exécution d'instructions DML ou DDL tels que UPDATE, INSERT, TRUNCATE TABLE ou CREATE/ALTER TABLE, il peut être intéressant d'en connaître l'origine, avant de remonter votre sauvegarde et de rejouer vos transactions à l'aide de votre sauvegarde complète BAK et les fichiers TRN de sauvegarde des transactions.

# data[].attributes.headline
Pensez à nettoyer les logs de votre serveur MSSQL

# data[].attributes.subject
Pensez à nettoyer les logs de votre serveur MSSQL

# data[].attributes.attachment_link.readable_text
Dans le répertoire Log de SQL Server, s'entassent, pêle-mêle, des tas de fichiers aux extensions trc, xel, txt, mdmp, audit, etc. En production, j'ai déjà vu ce répertoire cumuler plusieurs centaines de Mo, voire quelques Go. Il n'y a pas lieu de conserver ces fichiers au delà d'une durée de 3-4 semaines, à des fins d'analyse post-mortem.

Certains de ces fichiers étant verrouillés par le processus sqlservr.exe, il n'y a aucun risque particulier à nettoyer le contenu de ce répertoire. C'est à l'aide de la commande windows que vous pouvez nettoyer le contenu de ce répertoire. En donnant la possibilité très temporaire d'exécuter des commandes système à partir de SQL Server, vous pouvez utiliser le script suivant écrit en Transact-SQL, dans lequel les fichiers d'activités sont stockés dans le dossier C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log :

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO
xp_cmdshell 'forfiles /p "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log" /s /m *.* /D -21 /C "cmd /c del /Q @path"'
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 0;
GO
RECONFIGURE;
GO

# data[].attributes.attachment_link.title
Un script SQL pour nettoyer le répertoire Log de SQL Server

# data[].attributes.attachment_link.description
Dans le répertoire Log de SQL Server, s'entassent, pêle-mêle, des tas de fichiers aux extensions trc, xel, txt, mdmp, audit, etc. En production, j'ai déjà vu ce répertoire cumuler plusieurs centaines de Mo, voire quelques Go. Il n'y a pas lieu de conserver ces fichiers au delà d'une durée de 3-4 semaines, à des fins d'analyse post-mortem.

# data[].attributes.headline
Les limitations fort nombreuses de SQL Server sur Linux

# data[].attributes.subject
Les limitations fort nombreuses de SQL Server sur Linux

# data[].attributes.attachment_link.readable_text
Je n'ai pas bien compris quel était l'intérêt de Microsoft à porter SQL Server sur Linux. Le seul point positif, c'est la vitesse et la simplicité de son installation par rapport à celles proposées par l'installeur Windows.

Ajout des dépôts Microsoft à Linux CentOS

Après avoir installé CentOS et fait la mise à jour ( yum update), ajoutez le dépôt fourni par Microsoft pour pouvoir installer SQL Server 2017 sur CentOS :

cd /etc/yum.repos.d/
wget https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo
wget https://packages.microsoft.com/config/rhel/7/prod.repo

Installation du moteur SQL Server et de Integration Services

Par défaut, les binaires et les librairies nécessaires au fonctionnement de SQL Server sur Linux s'installent dans /opt/mssql :

yum install mssql-server mssql-tools unixODBC-devel mssql-server-agent mssql-server-is -y

Configuration du moteur SQL Server, de l'agent et de Integration Services

La configuration se fait à l'aide des commandes suivantes :

/opt/mssql/bin/mssql-conf setup
/opt/ssis/bin/ssis-conf setup
/opt/mssql/bin/mssql-conf set sqlagent.enabled true
/opt/mssql/bin/mssql-conf set mssqlis.enabled true

Lancement des services

Contrairement à Windows où le moteur, l'agent et Integration Services mobilisent des services distincts, sous Linux, vous avez besoin de lancer un seul service :

systemctl enable mssql-server
systemctl start mssql-server

Répertoires par défaut

Par défaut, les données sont stockées dans le dossier /var/opt/mssql/data ; les journaux d'erreurs dans /var/opt/mssql/log.

Fichiers de configuration

Concernant l'agent et le serveur, le données de configuration sont stockées dans /var/opt/mssql/mssql.conf. Pour Integration Services, il s'agit du fichier /var/opt/ssis/ssis.conf. Pensez à éditer le fichier ssis.conf en modifiant la directive relative à l'envoi de vos données d'utilisation via la télémétrie activée par défaut :

[TELEMETRY]
enabled=N

Limitations

Pour l'heure, ni SSRS, ni SSAS ne sont pris en charge sur la version SQL Server pour Linux. Concernant Integration Services, SQL Server Management Studio - qui s'exécute exclusivement sous Windows - ne permet pas la création de plans de maintenance sur Linux. Il ne permet pas non plus de se connecter à Integration Services, ni de créer un catalogue Integration Services.

La seule possibilité est de les faire à partir d'une station Windows dotée de SQL Server Data Tools, puis de les exécuter et/ou de les planifier à l'aide de la commande dtexec exécutée ci-dessous par l'utilisateur root sous Linux :

export PATH=/opt/ssis/bin:$PATH
gpasswd -a root ssis
dtexec /F fichier_plan_de_maintenance.dtsx /DE "mot_de_passe_de_protection_du_package"

# data[].attributes.attachment_link.title
Installation, utilisation et limitations de SQL Server 2017 sur Linux

# data[].attributes.attachment_link.description
Je n'ai pas bien compris quel était l'intérêt de Microsoft à porter SQL Server sur Linux. Le seul point positif, c'est la vitesse et la simplicité de son installation par rapport à celles proposées par l'installeur Windows.

# data[].attributes.headline
Je reste très sceptique quant à l'utilité sociale de l'installation de SQL Server sur Linux.

# data[].attributes.subject
Je reste très sceptique quant à l'utilité sociale de l'installation de SQL Server sur Linux.

# data[].attributes.attachment_link.readable_text
Ca y est ! SQL Server est disponible pour Linux et aussi pour MacOS X, en préversion. Vous pouvez d'ores et déjà installer SQL Server sur Red Hat / CentOS 7.2 2 / Fedora, ainsi que sur Ubuntu 16.04 / Linux Mint et très vraisemblablement Debian. Pour SUSE / OpenSUSE, vous devrez attender. Vous pouvez également installer les SQL Server Tools sur votre distribution Linux. Il s'agit, pour l'instant, d'une préversion.

Installation de SQL Server sur Red Hat / CentOS

Elle est d'une simplicité béate :

curl https://packages.microsoft.com/config/rhel/7/mssql-server.repo > /etc/yum.repos.d/mssql-server.repo
yum install -y mssql-server
/opt/mssql/bin/sqlservr-setup
systemctl status mssql-server
systemctl enable mssql-server
systemctl enable mssql-server

Installation de SQL Server sur Ubuntu

curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list | tee /etc/apt/sources.list.d/mssql-server.list
apt-get update
apt-get install -y mssql-server
/opt/mssql/bin/sqlservr-setup

Documentations techniques SQL Server pour Linux

# data[].attributes.attachment_link.title
| Installer SQL Server sur Linux Red Hat et Ubuntu

# data[].attributes.attachment_link.description
Ca y est ! SQL Server est disponible pour Linux et aussi pour MacOS X, en préversion. Vous pouvez d'ores et déjà installer SQL Server sur Red Hat / CentOS 7.2 2 / Fedora, ainsi que sur Ubuntu 16.04 / Linux Mint et très vraisemblablement Debian. Pour SUSE / OpenSUSE, vous devrez attender.

# data[].attributes.headline
Les limites mémoire des différentes versions SQL Server

# data[].attributes.subject
Les limites mémoire des différentes versions SQL Server

# data[].attributes.attachment_link.readable_text
Lors d'une formation que j'ai donnée récemment sur SQL Server 2016, nous nous sommes intéressés aux limites mémoire d'une instance. Il y a clairement un réel intérêt à passer sur du SQL Server 2014/2016 ! Les bases de données relationnelles sont d'autant plus performantes qu'elles disposent de mémoire pour mettre en cache les blocs lus sur disques et les données issues de l'exécution des requêtes SELECT !

Limites Mémoire SQL Server

Limites Mémoire Windows Server

# data[].attributes.attachment_link.description
Lors d'une formation que j'ai donnée récemment sur SQL Server 2016, nous nous sommes intéressés aux limites mémoire d'une instance. Il y a clairement un réel intérêt à passer sur du SQL Server 2014/2016 !

# data[].attributes.headline
Je vous propose une formation pour administrer MSSQL !

# data[].attributes.subject
Je vous propose une formation pour administrer MSSQL !

# data[].attributes.attachment_link.readable_text
Avec les vagues de départ des générations qui ont commencé dans l'informatique au début des années 80, les entreprises vont avoir besoin de bras. Elles vont surtout avoir besoin de DBA. Au milieu des années 2000, il y avait en Haute-Normandie deux centres de formation qui proposaient des formations à l'administration des bases de données. Quand un organisme propose un cursus pour se former au métier de DBA, c'est pour y parler de tout, sauf de bases de données.

J'ai commencé de travailler sur SQL Server à partir de la version 7 en 1998, sous Windows Server NT 4. C'est véritablement à partir de la version 2008 que le moteur de bases de données a pris son envol, au point où il est choisi par de plus en plus d'éditeurs d'applications informatique.

Une formation de 4 jours

Fort de cette expérience, je vous propose une formation de 4 jours pour L'objectif est, à l'issue de la formation, d'être en capacité d' installer, d'administrer, de sauvegarder, de maintenir, de gérer les droits des bases de données des instances SQL Server.

# data[].attributes.attachment_link.description
Avec les vagues de départ des générations qui ont commencé dans l'informatique au début des années 80, les entreprises vont avoir besoin de bras. Elles vont surtout avoir besoin de DBA. Au milieu des années 2000, il y avait en Haute-Normandie deux centres de formation qui proposaient des formations à l'administration des bases de données.

# data[].attributes.headline
Bien placer votre DBCC SHRINK DATABASE dans vos plans de maintenance des bases SQL Server

# data[].attributes.subject
Bien placer votre DBCC SHRINK DATABASE dans vos plans de maintenance des bases SQL Server

# data[].attributes.attachment_link.readable_text
Lors de la reconstruction des index, la taille de vos fichiers de données (.MDF) et des journaux de transactions (.LDF) peut connaître un accroissement spectaculaire. Il convient donc d'en réduire la taille après que la réindexation est terminée.

La séquence à appliquer en la matière est de :

1. désactiver le mode de récupération complet sur la base de données en passant en mode simple,

2. reconstruire les index ,

3. réduire les fichiers de la bases de données (data et journaux de transactions),

4. réactiver le mode de récupération complet ,

5. faire une sauvegarde complète.

Le script de maintenance d'une base de données SQL Server

BEGIN
    USE master;
    --Mode simple
    ALTER DATABASE ma_database SET RECOVERY SIMPLE WITH NO_WAIT;
    USE ma_database;
    --Réindexation : on suppose que les tables sont dans le schém dbo et que les tables n'ont pas d'espace ou de caractères spéciaux dans leur nom.
    DECLARE curs_it CURSOR FOR
        SELECT
            i.name i_name,
            t.name t_name
        FROM
            sys.indexes i
            INNER JOIN sys.tables t
            ON i.object_id=t.object_id;
    OPEN curs_it;
    DECLARE @indexname sysname;
    DECLARE @tablename sysname;
    FETCH NEXT FROM curs_it INTO @indexname, @tablename;
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN;
         EXECUTE ('ALTER INDEX ' + @indexname + ' ON dbo.' + @tablename + ' REBUILD;');
         FETCH NEXT FROM curs_it INTO @indexname, @tablename;
    END;
    CLOSE curs_it ;
    DEALLOCATE curs_it;
    --Réduction
    DBCC SHRINKDATABASE('ma_database', 5 );
    --Mode complet
    ALTER DATABASE ma_database SET RECOVERY FULL WITH NO_WAIT;
    BACKUP DATABASE ma_database TO  DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\ma_database.bak' WITH  RETAINDAYS = 7, NOFORMAT, NOINIT,  NAME = 'ma_database', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
END
GO

# data[].attributes.attachment_link.keywords[]
maintenance des bases de données sql server

# data[].attributes.attachment_link.title
| SQL Server : nécessité de réduire après réindexation

# data[].attributes.attachment_link.description
Lors de la reconstruction des index, la taille de vos fichiers de données (.MDF) et des journaux de transactions (.LDF) peut connaître un accroissement spectaculaire. Il convient donc d'en réduire la taille après que la réindexation est terminée.

# data[].attributes.headline
Quelles nouveautés dans SQL Server 2012 et 2014 ?

# data[].attributes.subject
Quelles nouveautés dans SQL Server 2012 et 2014 ?

# data[].attributes.attachment_link.readable_text
De nombreuses fonctionnalités sont apparues à partir de la version 2012. Quant à SQL Server 2014, elle apporte très peu de fonctionnalités nouvelles sur le plan du moteur de bases de données !

Améliorations relatives au moteur de bases de données

Nouvelles fonctions (2012)

Fonctions analytiques (2012)

Nouvelles instructions pour les procédures stockées (2012)

Index compressés en colonnes (2012)

Incorporation des comptes de connexion en mode Contained DB (2012)

Nouvelles vues système (2012 - 2014)

Introduction des séquences (2012)

Extension du pool de mémoires tampons (Enterprise 2014)

Nouveaux rôles de serveur (2014)

Les tables Filetable (2012)

Le catalogue SSISDB (2012)

Le mode Distributed Replay (2012)

Il faut ajouter à ces fonctionnalités les Master Data Services et Data Quality Services, le mode Always On disponible uniquement sur la version Enterprise.

# data[].attributes.attachment_link.title
| Actualisation des connaissances SQL Server 2012 - 2014

# data[].attributes.attachment_link.description
De nombreuses fonctionnalités sont apparues à partir de la version 2012. Quant à SQL Server 2014, elle apporte très peu de fonctionnalités nouvelles sur le plan du moteur de bases de données !

# data[].attributes.headline
Une formation de 3 jours pour se former à SQL Server Reporting Services

# data[].attributes.subject
Une formation de 3 jours pour se former à SQL Server Reporting Services

# data[].attributes.attachment_link.readable_text
Avec une licence SQL Server 2008 R2, vous disposez aussi de la possibilité de créer des rapports sous Reporting Services.

L'objet de cette formation est la création de rapports sous SQL Server 2008 R2 ou SQL Server 2012 en s'appuyant notamment sur Report Builder.

Public / Pré-requis

Cette formation s'adresse à des administrateurs de base de données, des développeurs disposant de connaissances préalables sur SQL Server et SQL.

Présentation

La configuration de SQL Server Reporting Services

Construction des datasets : vues ou procédures stockées ?

Création d'un premier rapport avec Report Builder 3.0

Source de données et dataset

Le choix des paramètres

Les lookups sur datasets

Groupes de lignes et de colonnes

Filtres de groupes

Ajout de totaux et cumuls

Les champs calculés

Les formules conditionnelles

Application des agrégats sur des datasets et des groupes

La mise en forme du rapport

Gestion des sauts de page et de colonne

Mise en forme conditionnelle

Les indicateurs

Ajout d'autres rapports

Les sous-rapports

Les graphiques

Administration de SQL Server Reporting Services

Gestion des droits sur les rapports

La création de source de données

Les options de traitement

La gestion du cache

L'historique et les instantanés

Les abonnements par la messagerie et le partage de documents

Les rapports liés

Plan de maintenance

La sauvegarde des bases liées au service Reporting Services

Prix et tarifs : Voir la rubrique Formations

# data[].attributes.attachment_link.title
| Formation SSRS SQL Server Reporting Services

# data[].attributes.attachment_link.description
Avec une licence SQL Server 2008 R2, vous disposez aussi de la possibilité de créer des rapports sous Reporting Services. L'objet de cette formation est la création de rapports sous SQL Server 2008 R2 ou SQL Server 2012 en s'appuyant notamment sur Report Builder.

# data[].attributes.headline
Pas de trigger BEFORE sur SQL Server ! Il y a une solution

# data[].attributes.subject
Pas de trigger BEFORE sur SQL Server ! Il y a une solution

# data[].attributes.attachment_link.readable_text
Les triggers en mode Before, à l'instar de ce dont nous disposons sous Oracle ou sous PostgreSQL n'existent pas sous SQL Server. Dans Microsoft SQL Server, vous devez passer par un trigger du type INSTEAD OF. L'exemple ci-dessous permet de passer en majuscule la colonne film_titre de la table film lors d'une nouvelle saisie ou d'une modification du contenu de cette colonne !

La table FILM

CREATE TABLE film
(
film_id INT IDENTITY(1,1) NOT NULL ,
film_titre VARCHAR(100),
film_annee INT,
film_duree INT ,
film_genre VARCHAR(50),
CONSTRAINT film_pk PRIMARY KEY (film_id),
CONSTRAINT film_uk UNIQUE(film_titre ,film_annee ),
CONSTRAINT film_chk_annee CHECK(film_annee >= 1895)
CONSTRAINT film_chk_duree CHECK (film_duree BETWEEN 30 AND 270),
CONSTRAINT film_chk_genre CHECK (film_genre IS NOT NULL),
CONSTRAINT film_chk_titre CHECK (film_titre IS NOT NULL)
);

Trigger INSTEAD OF INSERT

CREATE TRIGGER instead_of_insert ON  film
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO film(film_titre,film_genre,    film_duree,
    film_annee
    )
    SELECT
        UPPER(film_titre),film_genre,film_duree,film_annee
    FROM inserted
    IF @@ERROR <>0
        ROLLBACK TRANSACTION
END
GO

Trigger INSTEAD OF UPDATE

CREATE TRIGGER instead_of_update ON  film
INSTEAD OF UPDATE
AS
BEGIN
    IF UPDATE(film_titre)
    BEGIN
        UPDATE film
        SET film_titre=UPPER(inserted.film_titre)
        FROM inserted INNER JOIN film
        ON inserted.film_id=film.film_id
    END
    IF UPDATE(film_annee)
    BEGIN
        UPDATE film
        SET film_annee=inserted.film_annee
        FROM inserted INNER JOIN film
        ON inserted.film_id=film.film_id
    END
    IF UPDATE(film_duree)
    BEGIN
        UPDATE film
        SET film_duree=inserted.film_duree
        FROM inserted INNER JOIN film
        ON inserted.film_id=film.film_id
    END
    IF UPDATE(film_genre)
    BEGIN
        UPDATE film
        SET film_genre=inserted.film_genre
        FROM inserted INNER JOIN film
        ON inserted.film_id=film.film_id
    END
    IF @@ERROR <>0
        ROLLBACK TRANSACTION
END
GO

# data[].attributes.attachment_link.description
Les triggers en mode Before, à l'instar de ce dont nous disposons sous Oracle ou sous PostgreSQL n'existent pas sous SQL Server. Dans Microsoft SQL Server, vous devez passer par un trigger du type INSTEAD OF. L'exemple ci-dessous permet de passer en majuscule la colonne film_titre de la table film lors d'une nouvelle saisie ou d'une modification du contenu de cette colonne !

# data[].attributes.headline
Les antivirus et SLQ Server font très mauvais ménage !

# data[].attributes.subject
Les antivirus et SLQ Server font très mauvais ménage !

# data[].attributes.attachment_link.readable_text
En contrôlant en permanence l'activité des disques, les antivirus ne font pas bon ménage avec les bases de données. SQL Server est souvent incriminé à tort comme étant responsable des problèmes de performance des applications qui lui sont associées ! Après avoir minimisé les services et autres résidents avec msconfig ou autoruns sur votre système Windows, il convient de gérer les exceptions au niveau de l'antivirus pour ne pas voir les performances de SQL Server totalement s'effondrer !

Exceptions au niveau des répertoires SQL Server

Vous pouvez le faire bien plus finement aussi, en spécifiant les répertoires des logs et des data.

Exception au niveau du processus sqlservr.exe

Et là, c'est beaucoup plus contestable !

# data[].attributes.attachment_link.title
| Antivirus et base de données SQL Server

# data[].attributes.attachment_link.description
En contrôlant en permanence l'activité des disques, les antivirus ne font pas bon ménage avec les bases de données. SQL Server est souvent incriminé à tort comme étant responsable des problèmes de performance des applications qui lui sont associées !

# data[].attributes.headline
Une formation à SQL Server Analysis Services

# data[].attributes.subject
Une formation à SQL Server Analysis Services

# data[].attributes.attachment_link.readable_text
Avec une licence SQL Server 2008 R2, vous disposez non seulement de l'accès à un moteur de bases de données performant, mais aussi à des outils de Business Intelligence dans lequel s'inscrit Analysis Services.

L'objet de cette formation est la mise en place de cubes sous SQL Server 2008 R2 ou SQL Server 2012 dans le cadre de l'analyse décisionnelle. Cette formation n'a pas pour objet l'étude du langage MDX.

Public / Pré-requis

Cette formation s'adresse à des administrateurs de base de données, des développeurs disposant de connaissances préalables sur SQL Server.

La démarche préparatoire

Création d'un cube

Création d'une projet de type Analysis Services avec SSBIDS

Le choix de la source de données

L'intégration des vues : l'établissement des relations entre dimensions et mesures

Ajout d'une table de temps

Les hiérarchies

Explorer les données du cube

Traitement et déploiement du cube

Utilisation du cube dans Excel

Analyse des données avec PowerPivot

Spécifier le type des données

Les données calculées

Utilisation des tableaux croisés dynamiques

Actualisation du cube

Création d'un package SSIS

Importation et exportation de packages

Création et planification d'un travail

Prix et tarifs : Voir la rubrique Formations

# data[].attributes.attachment_link.title
| Formation BI Analysis Services : les cubes dans SQL Server

# data[].attributes.attachment_link.description
Avec une licence SQL Server 2008 R2, vous disposez non seulement de l'accès à un moteur de bases de données performant, mais aussi à des outils de Business Intelligence dans lequel s'inscrit Analysis Services. L'objet de cette formation est la mise en place de cubes sous SQL Server 2008 R2 ou SQL Server 2012 dans le cadre de l'analyse décisionnelle.

# data[].attributes.attachment_link.readable_text
PostgreSQL 11 comprend de nombreuses nouveautés.

Parallélisme amélioré

Le parallélisme est désormais pris en charge au niveau des instructions CREATE TABLE ... AS, SELECT INTO et CREATE MATERIALIZED VIEW, CREATE INDEX. Le parallélisme se règle à l'aide des directives max_parallel_workers et max_parallel_workers_per_gather.

Valeurs par défaut

Dans la nouvelle version du RDBMS, l'affectation d'une valeur par défaut remplit la nouvelle colonne avec cette valeur, là où dans les versions antérieures, PostgreSQL inscrivait la valeur nulle. L'instruction ALTER TABLE ... ADD COLUMN ... DEFAULT n'amène plus la réécriture complète d'une table.

Partitionnement de tables

Les tables partitionnées prennent désormais en charge les clés primaires et les index. L'exécution d'un ordre UPDATE entraîne le déplacement des données dans les partitions de tables. Les requêtes sur ces tables utilisent le regroupement, si vous l'activez dans le fichier de configuration postgresql.conf, à l'aide de la directive enable_partitionwise_aggregate passée à on. Attention à laisser la valeur de la directive enable_partition_pruning à on, de manière à prendre en compte les tables partitionnées au niveau de l'optimiseur de requêtes !

Procédures stockées

La création et l'appel de procédures stockées peuvent se faire à l'aide des instructions CREATE PROCEDURE et CALL. Les transactions et les instructions COMMIT et ROLLBACK sont maintenant supportées dans les procédures stockées.

CREATE TABLE test (a int);

CREATE PROCEDURE transaction_test()
AS $$
BEGIN
   FOR i IN 0..9 LOOP
       INSERT INTO test (a) VALUES (i);
       IF i % 2 = 0 THEN
           RAISE NOTICE 'i=%, txid=% a été annulée', i, txid_current();
           COMMIT;
       ELSE
           RAISE NOTICE 'i=%, txid=% a été validée', i, txid_current();
           ROLLBACK;
       END IF;
   END LOOP;
END
$$
LANGUAGE PLPGSQL;

CALL transaction_test();

PostgreSQL 11 prend en charge la compilation Just In Time et accélère l'exécution des clauses WHERE dans les instructions SQL, à la condition d'inscrire de passer la directive jit à on dans le fichier de configuration de l'instance, après avoir installé le paquet .

Fonctions Window

La nouvelle version du SGBDR Open Source utilise désormais les fonctions Window ( OVER... PARTITION BY... ORDER BY... ) définies dans le standard SQL 2011.

Les développeurs de PostgreSQL permettent l'utilisation de QUIT et EXIT dans l'interpréteur psql. Cette fonctionnalité était très attendue.

Index couvrant

A la manière de SQL Server, vous pouvez désormais intégrer une ou plusieurs colonne de données à vos index en utilisant la clause INCLUDE.

Source : PostgreSQL 11 release notes

# data[].attributes.attachment_link.description
PostgreSQL 11 comprend de nombreuses nouveautés. Parallélisme amélioré Le parallélisme est désormais pris en charge au niveau des instructions CREATE TABLE ... AS, SELECT INTO et CREATE MATERIALIZED VIEW, CREATE INDEX. Le parallélisme se règle à l'aide des directives max_parallel_workers et max_parallel_workers_per_gather.

# data[].attributes.headline
Ça devient compliquer d'installer SQL Server Data Tools !

# data[].attributes.subject
Ça devient compliquer d'installer SQL Server Data Tools !

# data[].attributes.attachment_link.readable_text
Pour ceux d'entre vous qui ne connaîtraient pas bien SQL Server, SSDT - SQL Server Data Tools - est une version allégée de Visual Studio dont l'objet est de permettre de créer les lots DTSX Integration Services (un ETL intégré à SQL Server), les cubes Analysis Services et aussi les états Reporting Services. Pour Reporting Services, je préfère de très loin utiliser Report Builder. SSDT s'installe sur la station de travail afin de permettre aux développeurs d'ajouter des composants de Business Intelligence à leurs bases de données SQL Server.

Télécharger SSDT 15.7.0

Pour une raison que la raison ignore, Microsoft nous a, une fois de plus, rendu le téléchargement de la version offline de SQL Server Data Tools très compliqué. Dans un 1er temps, vous devez récupérer le logiciel SSDT. Une fois téléchargé, vous devez lancer à partir de la ligne de commande sous Windows :

cd /d e:\ssdt
SSDT-Setup-FRA.exe /layout

Dans l'exemple ci-dessus, e:\ssdt désigne le dossier où l'ensemble des fichiers nécessaires à l'installation seront téléchargés.

Installation de SSDT 15.7.0

Après avoir téléchargé SSDT sur votre disque dur, l'installation du logiciel se fait à l'aide du programme SSDT-Setup-FRA.exe, au côté duquel doivent se trouver les dossiers redist et payload, téléchargés lors de l'étape précédente.

# data[].attributes.attachment_link.title
SQL Server : télécharger la version offline de SSDT

# data[].attributes.attachment_link.description
Pour ceux d'entre vous qui ne connaîtraient pas bien SQL Server, SSDT - SQL Server Data Tools - est une version allégée de Visual Studio dont l'objet est de permettre de créer les lots DTSX Integration Services (un ETL intégré à SQL Server), les cubes Analysis Services et aussi les états Reporting Services.

# data[].attributes.headline
Optimiser la gestion mémoire de PostgreSQL sous 

# data[].attributes.subject
Optimiser la gestion mémoire de PostgreSQL sous 

# data[].attributes.attachment_link.readable_text
Concernant les réglages de la mémoire à opérer pour PostgreSQL, la documentation officielle est claire comme du jus de chique. Et si, comme Boileau, je pense que " Ce que l'on conçoit bien s'énonce clairement " , il est évident que les rédacteurs de la documentation officielle de PostgreSQL me semblent passablement embrouillés ! De là à dire qu'ils ne comprennent pas grand chose à ce qu'ils écrivent... ;+)

Mémoire disponible

C'est à l'aide de la commande free sous Linux que vous pouvez déterminer la mémoire disponible de votre système. Ici, la mémoire disponible est égale à free+ buff/cache, soit 961556 octets !

              total        used        free      shared  buff/cache   available
Mem:        1016452       54896      504176        7240      457380      805208
Swap:       1048572           0     1048572

Sous Linux, PostgreSQL n'utilise plus le mécanisme de la Shared Memory depuis la version 9.4 de la base de données Open Source. Il n'y a donc plus lieu de modifier le fichier /etc/sysctl.conf, comme nous le faisions auparavant !

effective_cache_size

effective_cache_size est une directive du fichier postgresql.conf, qui détermine la quantité de mémoire utilisée par PostgreSQL. Vous pouvez aller jusqu'à la fixer à la totalité de la mémoire disponible obtenue précédemment et calculée avec free. L'éditeur, quant à lui, préconise 75% de la RAM totale de la machine dans le cas d'un serveur dédié. Dans mon exemple, je le fixe à 958454 octets , soit la valeur inférieure la plus proche de la division de 961556 par 8 Ko.

Ce paramètre shared_buffers doit être mis en fait à 1/3 du effective_cache_size. L'éditeur recommande de le régler à 25% de la RAM totale de la machine. Ce paramètre règle la quantité de mémoire pour la mise en cache des blocs du système de fichiers contenant tables et index. Dans mon exemple, ce paramètre est égal à 319488 octets.

Concernant le work_mem, c'est la détermination de la taille mémoire pour les opérations de tris, de regroupement au niveau des requêtes effectués par les utilisateurs. Elle est égale au 1/3 du effective_cache_size, divisée par le nombre maxi de connections géré par la directive max_connections. Dans mon exemple, pour 20 connexions maximum, je l'ai arrondi à 16384 octets, soit le multiple de 8 ko le plus proche de la division de 319488/20.

maintenance_work_mem

Concernant ce paramètre, les valeurs produites par et Alexey Vasiliev proposent une quantité de 64 Mo par Go de RAM pour une base OLTP (base métier). D'autres proposent 50 Mo par Go. Cette mémoire est utilisée lors des opérations liées à la réindexation, au vacuum et au pg_dump. Dans mon exemple, j'ai mis ce paramètre à 64 Mo pour 1 Go.

Le site d'Alexey Vasiliev

Alexey Vasiliev, alias leopard, a développé un service en ligne vous permettant de calculer ces différents paramètres à ajouter à votre fichier postgresql.conf.

C'est un outil disponible sur les distributions Linux. Pour l'installer sous CentOS, il faut ajouter les dépôts EPEL.

yum install epel-release
yum install pgtune

Le souci est que pgtune n'est plus maintenu depuis octobre 2009 ! Concernant le calcul du paramètre work_mem, les données obtenues semblent extrêmement " agressives ". La commande s'emploie de la manière suivante :

pgtune -M 1073741824 -T OLTP -c 20 -i /var/lib/pgsql/9.6/data/postgresql.conf -o /var/lib/pgsql/9.6/data/postgresql.conf.new

En dehors du work_mem, les résultats de pgtune sont proches de ceux d'Alexey Vasiliev

maintenance_work_mem = 64MB
effective_cache_size = 768MB
work_mem = 48MB shared_buffers = 256MB max_connections = 20

Documentation

# data[].attributes.attachment_link.title
| Tuning PostgreSQL : configuration mémoire pour Linux

# data[].attributes.attachment_link.description
Concernant les réglages de la mémoire à opérer pour PostgreSQL, la documentation officielle est claire comme du jus de chique. Et si, comme Boileau, je pense que " Ce que l'on conçoit bien s'énonce clairement " , il est évident que les rédacteurs de la documentation officielle de PostgreSQL me semblent passablement embrouillés !

# data[].attributes.headline
De grandes nouveautés pour PostgreSQL 10 !

# data[].attributes.subject
De grandes nouveautés pour PostgreSQL 10 !

# data[].attributes.attachment_link.readable_text
La version de PostgreSQL 10 apporte d'importantes améliorations. Mais il lui manque toujours et encore un mécanisme de réplication Master-Master à la Gallera, si simple à mettre en oeuvre dans MariaDB !

Changement du nom du dossier xlog

Sous la version 10 de PostgreSQL, le dossier $PGDATA/xlog a été mué en $PGDATA/wal !

Réplication Publication-Abonnement

Désormais, PostgreSQL 10 dispose d'un système de réplication logique, de type Master-Slave, à l'image de ce qui existe dans MariaDB/MySQL ou dans SQL Server avec la publication-abonnement. Le mécanisme est d'une simplicité béate et utilise les instructions CREATE PUBLICATION et CREATE SUSBSCRIPTION ! Bien vu.

Partitionnement de tables

Même si le partitionnement de tables existait dans les versions antérieures de PostgreSQL, il était extrêmement complexe à mettre en oeuvre. La version 10 de PostgreSQL propose une grande simplification ! Sur le fond maintenant, le partitionnement de tables, lorsque vos données sont stockées sur une baie de disques, n'a pas grand intérêt du point de vue de la performance !

Parallélisme des requêtes

Introduit dans la version 9.6, PostgreSQL permet le parallélisme dans l'accès aux index, lors des jointures et aussi au niveau de l'exécution des sous-requêtes. Il faut rester extrêmement prudent dans l'utilisation du parallélisme du fait de la consommation de ressources entrant en concurrence avec l'activité des autres process ! Par défaut, le nombre de process réservé au parallélisme des requêtes est limité à 2 et se configure avec la directive max_parallel_workers_per_gather. L'augmentation de ce paramètre exige d'ajuster les directives max_worker_processes, ainsi que max_parallel_workers.

Statistiques étendues

PostgreSQL 10 offre désormais la possibilité de disposer de statistiques étendues sur les colonnes de tables. Vous disposez désormais des instructions CREATE STATISTICS, ALTER STATISTICS et DROP STATISTICS.

La fonction pg_current_logfile() permet d'obtenir le fichier de logs courant.

Colonne identity

Enfin, PostgreSQL propose désormais un type de colonne IDENTITY.

Tables de transition dans les triggers AFTER

Avec l'ajout des clauses REFERENCING OLD TABLE et REFERENCING NEW TABLE, vous disposez désormais de la possibilité de stocker les lignes d'une table avant et après modification au niveau des triggers AFTER.

Authentification SCRAM-SHA-256

PostgreSQL 10 propose désormais une méthode plus forte de chiffrement pour la transmission du mot de passe entre le client et le serveur lors de la connexion. Il est conseillé d'abandonner l'authentification md5 définie dans votre fichier pg_hba.conf par le chiffrement scram-sha-256. Pour chiffrer vos données, il faut recourir à l'utilisation d'un certificat SSL.

Exécution de programmes via FDW

Introduit dans les versions précédents, le mécanisme dit de Foreign Data Wrapper permet de lier une instance Oracle, MySQL/MariaDB ou Firebird à votre cluster PostgreSQL. Très utile lorsqu'il s'agit de migrer vos données vers PostgreSQL ou à l'occasion de la constitution d'un DataWareHouse ! Vous pouvez désormais exécuter un programme système à partir de PostgreSQL via file_fdw. N'oubliez pas de donner les droits nécessaire à l'utilisateur postgres, lorsque vous exécutez PostgreSQL sur un système Linux !

Statistiques multi-colonnes

Les développeurs de PostgreSQL ont ajouté les instructions CREATE STATISTICS, ALTER STATISTICS, DROP STATISTICS permettant un meilleur fonctionnement des requêtes utilisant les colonnes au niveau de la clause WHERE.

A lire sur les nouveautés de PostgreSQL 10

# data[].attributes.attachment_link.description
La version de PostgreSQL 10 apporte d'importantes améliorations. Mais il lui manque toujours et encore un mécanisme de réplication Master-Master à la Gallera, si simple à mettre en oeuvre dans MariaDB ! Changement du nom du dossier xlog Sous la version 10 de PostgreSQL, le dossier $PGDATA/xlog a été mué en $PGDATA/wal !

# data[].attributes.headline
Oracle est devenu trop complexe pour la génération d'informaticiens d'aujourd'hui.

# data[].attributes.subject
Oracle est devenu trop complexe pour la génération d'informaticiens d'aujourd'hui.

# data[].attributes.attachment_link.readable_text
Je sors d'une grippe totalement ereinté, vidé, sans énergie. Et comme vous avez pu le voir, je n'ai pas beaucoup écrit ces derniers temps.

J'ai, malgré tout, pris cette semaine le temps de travailler. Et j'ai formé le responsable d'un grand CCAS d'une ville normande sur Oracle 11g. Sur 3 applications s'exécutant sur Oracle, deux d'entre elles étaient sauvegardées via DataPump. Pour la 3e, il y avait Veeam Backup ! Pas de plan de maintenance, pas de réindexation, pas de mise à jour du système statistique, pas de purge pour la table SYS.AUD$. D'où des lenteurs...

A moins de clauses spécifiques au niveau du contrat qui lie le client au fournisseur du logiciel, la sauvegarde incombe à la structure utilisatrice. Autrement dit, le fournisseur de l'application ne saurait être tenu responsable en cas de perte de données. J'avais lu, il y a quelques temps, que 60% des entreprises utilisant Oracle avaient au moins une fois perdu leurs données. Il faut dire qu'Oracle dispose d'une multitude de techniques de sauvegarde plus ou moins " exotiques " :

Les SSII sont en train d'abandonner massivement Oracle pour deux raisons. Elles n'ont plus de compétences internes sur ce moteur de bases de données relationnel, ardu à maîtriser. Ajoutez-y une politique tarifaire d'Oracle totalement incompréhensible, notamment quand il s'agit de calculer le nombre de licences pour une instance s'exécutant dans une ferme de serveurs Vsphere Esxi ! Et le prix vient à dissuader le client final de choisir la solution du fournisseur. C'est la raison pour laquelle les SSII s'orientent vers PostgreSQL, MariaDB ou SQL Server.

Le destin d'Oracle est aujourd'hui clairement lié aux DBA qui s'en vont massivement en retraite. Oracle ne leur survivra pas !

# data[].attributes.attachment_link.title
| Oracle : la croyance dans les entrailles de dinde

# data[].attributes.attachment_link.description
Je sors d'une grippe totalement ereinté, vidé, sans énergie. Et comme vous avez pu le voir, je n'ai pas beaucoup écrit ces derniers temps. J'ai, malgré tout, pris cette semaine le temps de travailler. Et j'ai formé le responsable d'un grand CCAS d'une ville normande sur Oracle 11g.

# data[].attributes.headline
Installer  PostgreSQL sur Fedora : un jeu d'enfants !

# data[].attributes.subject
Installer  PostgreSQL sur Fedora : un jeu d'enfants !

# data[].attributes.attachment_link.readable_text
La procédure ci-dessous vaut pour Fedora 22 & 23, Centos 7 et Red Hat 7, à la condition de remplacer la commande dnf par yum. Pour le reste, tout est identique !

Désactiver SELinux

Editez le fichier /etc/selinux/config et passez la directive SELINUX à disabled :

SELINUX=disabled

Pour éviter de rebooter pour que la nouvelle configuration soit prise en compte, tapez à partir de la ligne de commande:

setenforce 0

Vous pouvez au choix décider d'ouvrir l'application Tcp/5432 pour les adresses du réseau local ou d'arrêter le pare-feu comme indiqué ci-dessous :

systemctl stop firewalld
systemctl disable firewalld

Mise à jour de la Fedora

Elle se fait à l'aide de la commande dnf depuis la version 23 de la Fedora (c'est encore yum pour CentOS et Red Hat) :

dnf update

Installation par les dépôts

A l'heure où j'écris ce billet, la version de PostgreSQL disponible dans les dépôts Fedora est la version 9.4.8 :

dnf install postgresql-server

Installation à partir des dépôts fournis par l'éditeur

Sur le site de l'éditeur consacré aux distributions RPM, vous disposez du lien de téléchargement des dépôts à ajouter dans la Fedora et du mode opératoire à employer pour l'installation de la version PostgreSQL 9.5.3 :

dnf install https://download.postgresql.org/pub/repos/yum/9.5/fedora/fedora-23-x86_64/pgdg-fedora95-9.5-3.noarch.rpm
dnf install postgresql95-server postgresql95-contrib
/usr/pgsql-9.5/bin/postgresql95-setup initdb
systemctl enable postgresql-9.5
systemctl start postgresql-9.5

Test de connexion locale

su - postgres
psql

Je forme à l' administration de PostgreSQL Server 9.3, 9.4 et 9.5 !

# data[].attributes.attachment_link.description
La procédure ci-dessous vaut pour Fedora 22 & 23, Centos 7 et Red Hat 7, à la condition de remplacer la commande dnf par yum. Pour le reste, tout est identique !

# data[].attributes.headline
Il faut toujours qu'ils se singularisent tous ces Debianoeuds ! ;+)

# data[].attributes.subject
Il faut toujours qu'ils se singularisent tous ces Debianoeuds ! ;+)

# data[].attributes.attachment_link.readable_text
Ma venue " forcée " sous Ubuntu ne m'empêche de dire tout le mal que je pense de cette distribution et de sa grande sœur, Debian, que j'ai, pour ma part, arrêté de déployer en environnement serveur !!!

Là où le comportement des paquets RPM sous CentOs ou Fedora ou Red Hat respectent les standards, les éditeurs de paquets de Debian produisent des choses quelque peu ésotériques. Je prends exemple sur le paquet mysql-server qui pose un script de lancement /etc/init.d/mysql et un fichier de configuration /etc/mysql/my.cnf parfaitement spécifiques. La raison ? Permettre les mises à jour !!!

Je vous recommande ces quelques petites manœuvres afin de vous affranchir de la tutelle des concepteurs du paquet.

Décompresser le fichier de configuration taillé pour votre machine à partir du répertoire /usr/share/doc/mysql-server-5.1/examples/. Dans mon cas, j'ai décompressé le fichier my-medium.cnf.gz par gunzip.

Arrêter mysql par killall mysqld. C'est plus sûr.

Copier le fichier my-medium.cnf dans /etc/mysql/my.cnf.

Editez le fichier /etc/init.d/mysql et changer la ligne :

MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf"
par
MYADMIN="/usr/bin/mysqladmin -u root --password=votre_mot_de_passe"

Passer en commentaire tout ce bloc en mettant des dièses devant chaque ligne:

if mysqld_status check_alive warn; then
  log_end_msg 0
  #Now start mysqlcheck or whatever the admin wants.
  output=$(/etc/mysql/debian-start)
  [ -n "$output" ] && log_action_msg "$output"
else
  log_end_msg 1
  log_failure_msg "Please take a look at the syslog"
fi

Relancer mysql par /etc/init.d/mysql start

# data[].attributes.attachment_link.title
| Le paquet Mysql-Server : exemple de contorsion sous Debian et Ubuntu

# data[].attributes.attachment_link.description
Ma venue " forcée " sous Ubuntu ne m'empêche de dire tout le mal que je pense de cette distribution et de sa grande sœur, Debian, que j'ai, pour ma part, arrêté de déployer en environnement serveur !!!

# data[].attributes.headline
Il y a plusieurs manières de tuer la session d'un utilisateur dans PostgreSQL.

# data[].attributes.subject
Il y a plusieurs manières de tuer la session d'un utilisateur dans PostgreSQL.

# data[].attributes.attachment_link.readable_text
PostgreSQL ne possède pas d'instruction ALTER SYSTEM KILL SESSION, comme Oracle, ou encore KILL, comme SQL Server. Alors, pour détruire la session d'un utilisateur qui contribuerait, par la pose d'un verrou exclusif, à bloquer les autres utilisateurs, vous devez au préalable interroger le dictionnaire de données fourni par PostgreSQL, au travers de la vue pg_locks .

SELECT * FROM pg_locks l where lower(mode) LIKE '%exclusive%'

Connecté en local ou via SSH sur la machine qui héberge l'instance PostgreSQL, tapez la commande sous l'interpréteur Linux, en reprenant le numéro du processus ou pid fourni par la commande précédente :

# data[].attributes.attachment_link.title
| PostgreSQL : tuer la session d'un utilisateur

# data[].attributes.attachment_link.description
PostgreSQL ne possède pas d'instruction ALTER SYSTEM KILL SESSION, comme Oracle, ou encore KILL, comme SQL Server. Alors, pour détruire la session d'un utilisateur qui contribuerait, par la pose d'un verrou exclusif, à bloquer les autres utilisateurs, vous devez au préalable interroger le dictionnaire de données fourni par PostgreSQL, au travers de la vue pg_locks .

# data[].attributes.headline
Quelle est la date de démarrage de mon instance MySQL / MariaD

# data[].attributes.subject
Quelle est la date de démarrage de mon instance MySQL / MariaD

# data[].attributes.attachment_link.readable_text
Dans MySQL / MariaDB, vous disposez d'une variable de statut, uptime, vous indiquant le nombre de secondes depuis le dernier redémarrage de votre instance.

SHOW GLOBAL STATUS LIKE 'uptime';

Pour calculer l'heure et la date à laquelle ce redémarrage a eu lieu, vous devrez taper la requête suivante :

SELECT
	DATE_SUB(NOW(),INTERVAL variable_value SECOND) AS redemarrage
FROM
	information_schema.global_status
WHERE
	variable_name='uptime';

# data[].attributes.attachment_link.title
Date et heure du dernier démarrage de votre serveur MySQL / MariaDB

# data[].attributes.attachment_link.description
Dans MySQL / MariaDB, vous disposez d'une variable de statut, uptime, vous indiquant le nombre de secondes depuis le dernier redémarrage de votre instance.

# data[].attributes.headline
Les requêtes lentes pour déterminer où poser vos index !

# data[].attributes.subject
Les requêtes lentes pour déterminer où poser vos index !

# data[].attributes.attachment_link.readable_text
Lors d'un audit d'une base de données MySQL / MariaDB, il faut être en capacité d'analyser les requêtes qui consomment le plus de temps.

Fixer le seuil de déclenchement des logs

En matière d'administration de bases de données, sont, en général, considérées comme lentes les requêtes d'une durée supérieure à une seconde. C'est d'ailleurs le seuil minimal pris en charge par MySQL / MariaDB.

SET GLOBAL long_query_time=1;

Activer la consignation des requêtes lentes dans la table mysql.slow_log

Vous pouvez au choix envoyer vos requêtes dans le fichier /var/lib/mysql/$(hostname)-slow.log ou dans la table mysql.slow_log. Là, je choisis le mode table :

SET GLOBAL log_output='TABLE';
SET GLOBAL slow_query_log=1;

Adjonction d'autres requêtes

Par défaut, ni les requêtes relatives à l'administration, ni les requêtes n'utilisant pas d'index ne sont consignées dans les requêtes lentes. Vous pouvez toutefois les historiciser :

SET GLOBAL log_slow_admin_statements=1;
SET GLOBAL log_queries_not_using_indexes=1;

Visualiser les requêtes lentes

Vous pouvez visualiser la durée moyenne de l'exécution des requêtes :

SELECT
	db AS base_de_donnees,
	user_host AS utilisateur_machine,
	sql_text AS commande_sql,
	AVG(query_time) AS duree_secondes
FROM
	mysql.slow_log
GROUP BY
	db,
	user_host,
	sql_text;

Vous pouvez exporter les données de la table mysql.slow_log dans un fichier CSV.

SELECT *
FROM mysql.slow_log
INTO OUTFILE '/var/lib/mysql/slow_log.csv'
FIELDS TERMINATED BY '\t'
ENCLOSED BY ''
LINES TERMINATED BY '\n';

Arrêt de l'enregistrement et purge des données de la table

Une fois l'analyse terminée, vous pouvez arrêter l'enregistrement et purger les données.

SET GLOBAL log_output='NONE';
SET GLOBAL slow_query_log=0;
TRUNCATE TABLE mysql.slow_log;

# data[].attributes.attachment_link.title
Analyse des requêtes lentes dans MySQL / MariaDB

# data[].attributes.attachment_link.description
Lors d'un audit d'une base de données MySQL / MariaDB, il faut être en capacité d'analyser les requêtes qui consomment le plus de temps. Fixer le seuil de déclenchement des logs En matière d'administration de bases de données, sont, en général, considérées comme lentes les requêtes d'une durée supérieure à une seconde.

# data[].attributes.headline
Consigner toute l'activité d'une base MySQL / MariaDB

# data[].attributes.subject
Consigner toute l'activité d'une base MySQL / MariaDB

# data[].attributes.attachment_link.readable_text
Je dois, à partir de demain, réaliser un audit sur une base de données MySQL version 5.5 d'une durée de deux jours. La 1ère journée sera consacrée à la collecte des données ; la 2e à l'écriture d'un rapport préconisant un ensemble de points d'amélioration.

Consigner l'activité de MySQL / MariaDB dans la table mysql.general_log

L'activation et la désactivation de la consignation générale des logs peut se faire dynamiquement sur MySQL/MariaDB, sans avoir à

Pour spécifier la destination des logs, vous disposez de la directive log_output. Elle peut prendre trois valeurs : FILE, TABLE, NONE. Par défaut, sur Linux, les logs sont consignés dans le fichier /var/lib/mysql/$(hostname).log. Il n'y a d'ailleurs aucun moyen de les stocker dans un autre répertoire, même en recourant à l'usage du bind mount ou du lien symbolique.

Pour envoyer les données dans la table mysql.general_log, vous devez tout d'abord, à partir de l'interpréteur de commandes MySQL ou de l'interface de HeidiSQL indiquer la destination des logs :

SET GLOBAL log_output='TABLE';

Activer l'écriture des logs dans la table mysql.general_log.

Après avoir choisi la destination, vous devez activer la consignation de tous les logs.

SET GLOBAL general_log=1;

Lecture des requêtes exécutées

Dans le cadre d'un audit, ce qui est intéressant, c'est de voir les requêtes les plus exécutées, ainsi que les mauvaises pratiques comme l'exécution de la commande SELECT * FROM..., au lieu de préciser les colonnes à afficher, empêchant accessoirement l'utilisation des index par l'optimiseur.

SELECT
	user_host AS utilisateur_machine,
	argument AS ordre_sql,
	COUNT(*) AS nb
FROM
	mysql.general_log
WHERE
	command_type='Query'
GROUP BY
	user_host,
	argument
ORDER BY
	3 DESC;

Exportation des données de la table

Il peut être intéressant d'exporter les données de la table mysql.general_log dans un fichier CSV. L'utilisateur MySQL doit posséder les droits en écriture au niveau du dossier où vous écrivez le fichier.

SELECT *
FROM mysql.general_log
INTO OUTFILE '/var/lib/mysql/general_log.csv'
FIELDS TERMINATED BY '\t'
ENCLOSED BY ''
LINES TERMINATED BY '\n';

Désactivation de l'enregistrement et purge des données de la table

Une fois l'enregistrement des données terminé, vous devrez dans un 1er temps désactiver l'enregistrement des logs.

SET GLOBAL general_log=0;
SET GLOBAL log_output='NONE'

Une fois l'analyse terminée, il ne vous reste plus qu'à purger les données de la table mysql.general_log.

TRUNCATE TABLE mysql.general_log;

# data[].attributes.attachment_link.title
Les journaux d'activité en mode table dans MySQL / MariaDB

# data[].attributes.attachment_link.description
Je dois, à partir de demain, réaliser un audit sur une base de données MySQL version 5.5 d'une durée de deux jours. La 1ère journée sera consacrée à la collecte des données ; la 2e à l'écriture d'un rapport préconisant un ensemble de points d'amélioration.

# data[].attributes.headline
Sur quelle version du moteur MySQL / MariaDB pouvez-vous donc bien être ?

# data[].attributes.subject
Sur quelle version du moteur MySQL / MariaDB pouvez-vous donc bien être ?

# data[].attributes.attachment_link.readable_text
En hébergement mutualisé, il n'est pas aisé de connaître la version de MySQL / MariaDB que vous utilisez. En vous connectant via phpMyAdmin, vous pouvez taper cette requête pour disposer de différentes informations de version :

SHOW GLOBAL VARIABLES LIKE '%version%';

Vous pouvez aussi obtenir la version du moteur plus simplement par cette requête :

SELECT @@version;

En hébergement dédié, vous pouvez recourir aux commandes Linux pour disposer des versions installées de MySQL / MariaDB.

Sur CentOS / Red Hat / Fedora Server :

rpm -qa|grep -iP "(mariadb|mysql)-server"

Sur Debian / Ubuntu Server :

dpkg -l|grep -iP "(mariadb|mysql)-server"

Il existe de très nombreuses autres méthodes.

# data[].attributes.attachment_link.title
Informations de version du moteur MySQL / MariaDB

# data[].attributes.attachment_link.description
En hébergement mutualisé, il n'est pas aisé de connaître la version de MySQL / MariaDB que vous utilisez. En vous connectant via phpMyAdmin, vous pouvez taper cette requête pour disposer de différentes informations de version : SHOW GLOBAL VARIABLES LIKE '%version%'; Vous pouvez aussi obtenir la version du moteur plus simplement par cette requête : SELECT @@version; En hébergement dédié, vous pouvez recourir aux commandes Linux pour disposer des versions installées de MySQL / MariaDB.

# data[].attributes.headline
Vous avez besoin d'installer un plugin dans MySQL 8.0

# data[].attributes.subject
Vous avez besoin d'installer un plugin dans MySQL 8.0

# data[].attributes.attachment_link.readable_text
Je voulais trouver un exemple d'utilisation de plugin dans MySQL Server 8. J'ai choisi de tester le plugin auth_socket qui permet de se connecter avec votre seul compte UNIX à MySQL, sans avoir à saisir de mot de passe lors de la connexion au moteur de bases de données.

Dossier des plugins

Sous Linux, les plugins sont installés dans le dossier /usr/lib/mysql/plugin. Ce dossier peut être modifié à l'aide de la variable plugin_dir. Sa modification ne peut se faire qu'à froid dans un des fichiers de configuration de votre instance MySQL situé dans /etc/mysql/mysql.conf.d, de préférence :

[mysqld]
plugin_dir=/usr/lib/mysql/plugin

Si vous changez la valeur de cette variable, vous devrez ensuite redémarrer le serveur MySQL :

systemctl restart mysql

Installer un plugin

Les plugins sont des fichiers .so ( Shared Object Dynamic Library assimilable à des DLL sous Windows). Pour installer le plugin auth_socket par exemple, tapez à partir de l'interpréteur MySQL :

INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';

Le nom du plugin peut être différent du nom de fichier.

Liste des plugins

Pour visualiser la liste des plugins installés, tapez l'une des deux commandes suivantes :

SHOW PLUGINS;
SELECT * FROM information_schema.plugins;

Utilisation du plugin auth_socket

Dans l'exemple suivant, je crée un administrateur local de MySQL :

CREATE USER 'admin' IDENTIFIED WITH auth_socket;
GRANT ALL PRIVILEGES ON *.* TO 'admin' WITH GRANT OPTION;

Pour vous connecter en mode peer/socket, tapez seulement la commande mysql après vous être connecté en tant qu'utilisateur Linux admin.

Un autre plugin : InnoDB Memcached

MySQL 8.0 propose d'autres plugins fort intéressants comme le plugin InnoDB Memcached :

INSTALL PLUGIN daemon_memcached SONAME 'libmemcached.so';

Ce plugin permet de créer des tables en mémoire. Il nécessite que vous installiez et configuriez le démon memcached sur votre système Linux.

Désinstaller un plugin

La désinstallation du plugin auth_socket se fait très simplement.

UNINSTALL auth_socket;

# data[].attributes.attachment_link.title
Utilisation de plugin dans MySQL Server 8.0

# data[].attributes.attachment_link.description
Je voulais trouver un exemple d'utilisation de plugin dans MySQL Server 8. J'ai choisi de tester le plugin auth_socket qui permet de se connecter avec votre seul compte UNIX à MySQL, sans avoir à saisir de mot de passe lors de la connexion au moteur de bases de données.

# data[].attributes.headline
Une meilleure gestion des mots de passe dans MySQL 8.0, plus sûre !

# data[].attributes.subject
Une meilleure gestion des mots de passe dans MySQL 8.0, plus sûre !

# data[].attributes.attachment_link.readable_text
Depuis plusieurs versions, MySQL propose un module de renforcement de la sécurité des mots de passe. L'exécution du script de post-installation mysql_secure_installation est censé pouvoir le configurer. J'ai pu constater hier sur deux machines que ce n'était pas toujours le cas. Pour quelle raison ? Mystère et boule de gomme !

Emplacement des modules et des plugins

Vous pouvez connaître l'emplacement des modules/composants/plugins de votre installation MySQL par la commande :

SHOW GLOBAL VARIABLES LIKE 'plugin_dir';

Par défaut, sous Linux, les plugins et les composants sont stockés dans /usr/lib/mysql/plugin.

Cela se fait très simplement à l'aide de l'instruction suivante que vous devez saisir après vous être connecté à votre instance MySQL :

UNINSTALL COMPONENT 'file://component_validate_password';

Pour réactiver le module de renforcement de la sécurité de vos mots de passe, tapez à partir de l'interpréteur MySQL :

INSTALL COMPONENT 'file://component_validate_password';

Configurer le niveau de renforcement des mots de passe

Pour visualiser les options du module, tapez à partir de votre interpréteur MySQL ou d'un GUI :

SHOW GLOBAL VARIABLES LIKE 'validate_password.%';

Vous pouvez empêcher l'utilisateur d'utiliser son login comme mot de passe ( validate_password.check_user_name -> ON). Vous pouvez fixer la longueur minimale du mot de passe ( validate_password.length -> 8). Vous pouvez aussi forcer à mélanger minuscules et majuscules ( validate_password.mixed_case_count -> 1). Vous pouvez encore déterminer le nombre de chiffres à utiliser dans le mot de passe ( validate_password.number_count -> 1). Vous pouvez fixer le nombre de caractères spéciaux ( validate_password.special_char_count -> 1) ainsi que le niveau de complexité des mots de passe ( validate_password.policy -> LOW, MEDIUM, STRONG). Le mode STRONG permet de définir un fichier au niveau de la variable validate_password.dictionary_file, dans laquelle vous indiquez une blacklist de mots à ne pas employer pour définir le mot de passe des utilisateurs. Par défaut, ce fichier doit être stocké dans /var/lib/mysql. Le propriétaire de ce fichier doit être l'utilisateur mysql.

L'exemple ci-dessous équivaut, de fait, à désactiver le niveau de complexité des mots de passe.

SET PERSIST validate_password.check_user_name=OFF;
SET PERSIST validate_password.length=0;
SET PERSIST validate_password.mixed_case_count=0;
SET PERSIST validate_password.number_count=0;
SET PERSIST validate_password.special_char_count=0;
SET PERSIST validate_password.policy=LOW;
SET PERSIST validate_password.dictionary_file=;

Pour rappel, l'instruction SET PERSIST - dans MySQL Server 8 et supérieur - ajoute les variables dans le fichier /var/lib/mysql/mysql-auto.cnf.

Source : The Password Validation Component

# data[].attributes.attachment_link.title
Renforcement de la sécurité des mots de passe dans MySQL Server 8.0

# data[].attributes.attachment_link.description
Depuis plusieurs versions, MySQL propose un module de renforcement de la sécurité des mots de passe. L'exécution du script de post-installation mysql_secure_installation est censé pouvoir le configurer. J'ai pu constater hier sur deux machines que ce n'était pas toujours le cas. Pour quelle raison ? Mystère et boule de gomme !

# data[].attributes.headline
Inutile de laisser des motorisations dont vous ne vous servez pas !

# data[].attributes.subject
Inutile de laisser des motorisations dont vous ne vous servez pas !

# data[].attributes.attachment_link.readable_text
Juste après avoir installé MySQL Server 8, j'ai voulu voir quels étaient les moteurs utilisés au niveau de l'instance. Oracle a décidé d'abandonner purement et simplement MyISAM pour le stockage des tables de la base MySQL.

La liste des moteurs utilisés

Pour disposer de la liste des moteurs utilisés, vous pouvez exécuter cette requête :

SELECT DISTINCT engine FROM information_schema.tables;

Les motorisations installées

Et grâce à cette autre requête, vous disposerez de l'ensemble des motorisations présentes et possibles dans votre instance MySQL :

SELECT
	plugin_name
FROM
	information_schema.plugins
WHERE
	plugin_type='STORAGE ENGINE'
AND
	plugin_status='ACTIVE'

Désactiver les moteurs inutilisés

Afin de désactiver les moteurs MyISAM, MRG_MYISAM, BLACKHOLE et ARCHIVE, créez le fichier de configuration /etc/mysql/mysql.conf.d/engines.cnf et ajoutez-y les lignes suivantes :

[mysqld]
disabled_storage_engines = "MyISAM,MRG_MYISAM,BLACKHOLE,ARCHIVE"

Cette ligne de commande ne peut pas être ajoutée, hélas, avec SET PERSIST dans le fichier mysql-auto.conf du fait que la variable disabled_storage_engines est en lecture seule et non dynamique ( Dynamic -> no).

Vérification

J'ai essayé, après avoir redémarré l'instance, de créer une table MyISAM sous MySQL Workbench. En vain. CQFD.

# data[].attributes.attachment_link.title
MySQL Server 8 : désactiver les moteurs inutilisés

# data[].attributes.attachment_link.description
Juste après avoir installé MySQL Server 8, j'ai voulu voir quels étaient les moteurs utilisés au niveau de l'instance. Oracle a décidé d'abandonner purement et simplement MyISAM pour le stockage des tables de la base MySQL.

# data[].attributes.headline
Percona XtraBackup permet de faire un hot backup de vos instances MySQL 8.0.

# data[].attributes.subject
Percona XtraBackup permet de faire un hot backup de vos instances MySQL 8.0.

# data[].attributes.attachment_link.readable_text
La version communautaire de MySQL Server ne possède pas d'outils capables de faire de hot backup. Seule la version commerciale dispose de cette fonctionnalité ! Le logiciel Open Source Percona Xtrabackup permet de réaliser ce type de sauvegarde indispensable à l'exploitation d'une base de données transactionnelle et relationnelle.

Au cas où vous feriez le choix de compresser vos sauvegardes, vous devrez installer qpress sur votre distribution Linux. J'ai même fait le choix de compiler qzip, qunzip et qcat. C'est, au passage, parfaitement inutile.

wget -U firefox http://www.quicklz.com/qpress-11-linux-x64.tar
tar xvf qpress-11-linux-x64.tar
mv qpress /usr/bin
aptitude install make gcc python-dev
wget -U firefox http://www.quicklz.com/qzip.tar
tar xvf qzip.tar
make
mv qzip /usr/bin
mv qunzip /usr/bin
mv qcat /usr/bin

Installer Percona Xtrabackup sur Ubuntu 18.04

L'installation sur Ubuntu Bionic se fait en deux commandes, à l'aide de gdebi :

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-8.0.4/binary/debian/bionic/x86_64/percona-xtrabackup-80_8.0.4-1.bionic_amd64.deb
gdebi percona-xtrabackup-80_8.0.4-1.bionic_amd64.deb

La sauvegarde complète

Que ce soit avec RMAN et les ARCHIVE LOG dans Oracle Database, le archive mode de PostgreSQL ou dans SQL Server, la récupération d'une base à un instant donné de son activité suppose au préalable de disposer d'une sauvegarde complète incluant les données de l'ordonnanceur interne et les transactions validées et en cours de validation. Le répertoire de sauvegarde /backups/complete/ utilisé dans l'exemple doit être vide. S'il n'existe pas, la commande le crée !

xtrabackup --backup --target-dir=/backups/complete/ -u root --compress --compress-threads=4 --parallel=4 -p

Les 196 Mo de l'instance pèsent 7 Mo à l'arrivée. La sauvegarde aura pris moins de 5 secondes, du fait de l'utilisation de 4 threads calés sur les 4 cœurs de la machine.

La sauvegarde incrémentale

Au cours de la journée, à espace régulier, vous devrez planifier une sauvegarde incrémentale afin de disposer de l'historique des transactions et des modifications apportées à l'instance MySQL. Remarquez que la 1ère sauvegarde incrémentale a comme point de référence la sauvegarde complète.

xtrabackup --backup --target-dir=/backups/incremental/1 --incremental-basedir=/backups/complete -u root -p --compress --compress-threads=4 --parallel=4

La 2e sauvegarde incrémentale va avoir la 1ère incrémentale comme nouveau point de référence. Et ainsi de suite.

xtrabackup --backup --target-dir=/backups/incremental/2/ --incremental-basedir=/backups/incremental/1/ -u root -p --compress --compress-threads=4 --parallel=4

Restauration de la sauvegarde complète

Au travers des commandes qui suivent, je décompresse, puis je " prépare " le backup en vue de la restauration. Après l'arrêt du service, le répertoire /var/lib/mysql/ doit être vidé de ses données. Une fois la restauration effectuée, vous devez ensuite remettre les droits à l'utilisateur mysql. Enfin, relancez le service.

xtrabackup --decompress --parallel=4 --target-dir=/backups/complete/
xtrabackup --prepare --parallel=4 --target-dir=/backups/complete/
systemctl stop mysql
rm -fr /var/lib/mysql/*
xtrabackup --copy-back --parallel=4 --target-dir=/backups/complete/
chown mysql:mysql /var/lib/mysql -R
systemctl start mysql

Restauration de la sauvegarde incrémentale

La restauration d'une sauvegarde incrémentale suppose de connaître le point précis de l'incident qui a entraîné la perte de données. Le seul élément à notre disposition est en général l'heure à laquelle la sauvegarde incrémentale a été effectuée. Il faut donc rejouer les sauvegardes incrémentales réalisées au plus près de l'incident, un peu avant. Le risque de pertes de données n'est donc pas complètement négligeable. Xtrabackup, comme la version communautaire de MySQL Server, reste extrêmement perfectible !

Après la décompression des sauvegardes incrémentales, préparez votre restauration. Arrêtez le service. videz le répertoire des données. Restaurez. Remettez les droits aux fichiers du répertoire /var/lib/mysql/ à l'utilisateur mysql, avant de relancer le service.

xtrabackup --decompress --parallel=4 --target-dir=/backups/incremental/1/
xtrabackup --decompress --parallel=4 --target-dir=/backups/incremental/2/
xtrabackup --prepare --parallel=4 --apply-log-only --target-dir=/backups/complete/
xtrabackup --prepare --parallel=4 --apply-log-only --target-dir=/backups/complete/ --incremental-dir=/backups/incremental/1/
xtrabackup --prepare --parallel=4 --apply-log-only --target-dir=/backups/complete/ --incremental-dir=/backups/incremental/2/
systemctl stop mysql
rm -fr /var/lib/mysql/*
xtrabackup --copy-back --parallel=4 --target-dir=/backups/complete/
chown mysql:mysql /var/lib/mysql -R
systemctl start mysql

1. J'avais eu cette très mauvaise idée de stocker mes fichiers pem liés à la prise en charge du SSL dans le dossier /var/lib/mysql. Ils n'ont pas survécu à l'exécution de la commande rm. Idem pour mon fichier mysql-auto.cnf. Curieusement, mon fichier auto.cnf a lui aussi disparu après la restauration et le redémarrage de l'instance ! La dernière version de Xtrabackup avec MySQL Server 8 pose encore de sérieux problèmes de compatibilité, qui m'amènent à douter de son utilisation en production.

2. Prenez soin de conserver une copie de vos fichiers binlog, après un si vous avez la main sur l'instance. Les fichiers binlog, s'ils ne sont pas sauvegardés, seront tout bonnement effacés. Avec un dump et les fichiers binlog, vous finirez toujours par retrouver vos petits !

Conclusion

Xtrabackup est une véritable usine à gaz très perfectible ! L'outil a toutefois le mérite d'exister.

# data[].attributes.attachment_link.title
La sauvegarde de MySQL Server avec Percona Xtrabackup

# data[].attributes.attachment_link.description
La version communautaire de MySQL Server ne possède pas d'outils capables de faire de hot backup. Seule la version commerciale dispose de cette fonctionnalité ! Le logiciel Open Source Percona Xtrabackup permet de réaliser ce type de sauvegarde indispensable à l'exploitation d'une base de données transactionnelle et relationnelle.

# data[].attributes.headline
La configuration de MySQL 8.0 ressemble de plus en plus à celle de PostgreSQL

# data[].attributes.subject
La configuration de MySQL 8.0 ressemble de plus en plus à celle de PostgreSQL

# data[].attributes.attachment_link.readable_text
Dans les versions antérieures à MySQL Server 8, les directives de configuration se situaient dans /etc/my.cnf ou bien dans /etc/mysql/my.cnf. Au fil des évolutions, ce fichier de configuration my.cnf a été complété par des fichiers complémentaires chargés à l'aide de la directive !includedir.

mysql-auto.conf

MySQL Server 8 introduit une grande nouveauté équivalente à la commande ALTER SYSTEM ajoutée au moteur de bases de données relationnelles PostgreSQL 9.4 et supérieur. Plutôt que d'éditer l'un des nombreux fichiers de configuration de MySQL, la commande SET PERSISTENT ajoute au fichier /var/lib/mysql/mysqld-auto.conf les directives de configuration de l'instance, tout en changeant la valeur de l'option à la manière d'un SET GLOBAL au niveau de l'instance en cours d'exécution. Au redémarrage, les directives ajoutées au fichier sont automatiquement prises en compte et exécutées. Le format mysql-auto.conf est un fichier au format JSON.

Le contenu de ce fichier peut être réinitialisé à l'aide de la commande RESET PERSISTENT.

Le /var/lib/mysql/auto.cnf fichier contient la directive server-uuid qui identifie le serveur MySQL dans les opérations de réplication. Pour changer le numéro du serveur, effacez ce fichier et relancez l'instance :

rm -f /var/lib/mysql/auto.cnf
systemctl restart mysql

# data[].attributes.attachment_link.title
Du nouveau dans la configuration de MySQL Server 8

# data[].attributes.attachment_link.description
Dans les versions antérieures à MySQL Server 8, les directives de configuration se situaient dans /etc/my.cnf ou bien dans /etc/mysql/my.cnf. Au fil des évolutions, ce fichier de configuration my.cnf a été complété par des fichiers complémentaires chargés à l'aide de la directive !includedir.

# data[].attributes.headline
Un tuto pour savoir installer MySQL 8.0 sur Ubuntu Server LTS 18.04.

# data[].attributes.subject
Un tuto pour savoir installer MySQL 8.0 sur Ubuntu Server LTS 18.04.

# data[].attributes.attachment_link.readable_text
Début janvier, je donne une formation à l'administration de MySQL sur Ubuntu. Habituellement, les formations que je dispense sont réalisées à plus de 95% sur CentOS. Là, le client travaille en production dans un environnement Ubuntu.

Du coup, je me suis penché sur la manière d'installer MySQL Server 8 sur Ubuntu.

Configurer les dépôts MySQL sous Ubuntu

Vous devez tout d'abord télécharger le paquet mysql-apt-config_0.8.11-1_all.deb :

wget https://dev.mysql.com/get/mysql-apt-config_0.8.11-1_all.deb
aptitude install ./mysql-apt-config_0.8.11-1_all.deb

Le gestionnaire de configuration de paquets vous propose de choisir votre configuration.

Après validation, un fichier /etc/apt/sources.list.d/mysql.list comprenant la liste des dépôts a été ajouté à votre distribution Ubuntu, contenant, notamment :

deb http://repo.mysql.com/apt/ubuntu/ bionic mysql-apt-config
deb http://repo.mysql.com/apt/ubuntu/ bionic mysql-8.0
deb http://repo.mysql.com/apt/ubuntu/ bionic mysql-tools

Installation de MySQL Server 8 sur Ubuntu

L'installation se fait de manière très simple, à l'aide de la commande apt-get ou de aptitude :

aptitude update
aptitude install mysql-community-server

Le gestionnaire de paquets propose d'associer un mot de passe à votre compte root !

Il vous propose également de choisir le niveau de chiffrement de votre mot de passe. La méthode proposée par défaut au niveau de MySQL 8 n'est pas compatible avec l'utilitaire de benchmarking sysbench.

Lancement de MySQL Server 8

Après l'exécution du gestionnaire de paquets, MySQL Server 8 est automatiquement démarré sur Ubuntu. Vous pouvez contrôler l'exécution du service mysql à l'aide de la commande systemctl :

systemctl status mysql
systemctl restart mysql
systemctl stop mysql
systemctl start mysql
systemctl disable mysql
systemctl enable mysql

Renforcement de la sécurité

Sous Ubuntu, comme sur les autres distributions, vous disposez d'un script de renforcement de la sécurité de MySQL :

mysql_secure_installation

Chargement des bases d'exemples

Les liens vers les scripts de chargement de bases d'exemple se trouvent sur cette page de documentation MySQL.

wget http://downloads.mysql.com/docs/world.sql.zip
wget http://downloads.mysql.com/docs/world_x-db.zip
wget http://downloads.mysql.com/docs/sakila-db.zip

Pour les décompresser :

unzip world.sql.zip
unzip sakila-db.zip
unzip world_x-db.zip

Pour les injecter dans le moteur de bases de données MySQL :

mysql -u root -p < world.sql
mysql -u root -p < sakila-db/sakila-schema.sql
mysql -u root -p < sakila-db/sakila-data.sql
mysql -u root -p < world_x-db/world_x.sql

Installer MySQL Workbench

MySQL Workbench est un GUI fourni par Oracle, pour administrer plus simplement MySQL et modéliser vos bases de données. A près l'ajout du dépôt de l'éditeur (Cf. ci-dessus), vous pouvez installer le GUI sur votre Xubuntu / Kubuntu / Lubuntu, à partir de la ligne de commandes, comme suit :

aptitude install mysql-workbench-community

Utiliser HeidiSQL

La version HeidiSQL 9.5 de décembre 2017 n'est plus compatible avec MySQL 8, lorsque vous choisissez la nouvelle méthode d'authentification caching_sha2_password.

La solution consiste à reprenant l'ancienne méthode d'authentification mysql_native_password pour l'utilisateur :

 ALTER USER 'votre_compte_utilisateur'@'192.168.1.%' IDENTIFIED WITHmysql_native_password BY 'votre_mot_de_passe';

# data[].attributes.attachment_link.title
Installer MySQL Server 8 sur Ubuntu 18.04

# data[].attributes.attachment_link.description
Début janvier, je donne une formation à l'administration de MySQL sur Ubuntu. Habituellement, les formations que je dispense sont réalisées à plus de 95% sur CentOS. Là, le client travaille en production dans un environnement Ubuntu. Du coup, je me suis penché sur la manière d'installer MySQL Server 8 sur Ubuntu.

# data[].attributes.headline
Changement de syntaxe pour créer un utilisateur dans MySQL 8.0

# data[].attributes.subject
Changement de syntaxe pour créer un utilisateur dans MySQL 8.0

# data[].attributes.attachment_link.readable_text
Jusqu'à présent, que ce soit dans MariaDB ou dans MySQL, je créais mes utilisateurs de bases WordPress de la manière suivante, en une seule commande :

GRANT ALL PRIVILEGES ON wordpress.* TO 'login'@'localhost' IDENTIFIED BY 'mot_de_passe';

Cette commande n'est plus possible ! Elle est interdite sous MySQL 8. Désormais, vous devrez le taper en deux commandes :

CREATE USER 'login'@'localhost' IDENTIFIED BY 'mot_de_passe';
GRANT ALL PRIVILEGES ON wordpress.* TO 'login'@'localhost'

Changement de méthode d'authentification

Afin de renforcer le chiffrement des mots de passe, Oracle a implanté dans MySQL 8 la nouvelle méthode d'authentification Caching SHA2. Vous pouvez en changer au niveau de l'utilisateur :

ALTER USER 'login'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mot_de_passe';
ALTER USER 'login'@'localhost' IDENTIFIED WITH caching_sha2_password  BY 'mot_de_passe';

Le mode mysql_native_password est l'ancienne méthode utilisée par MySQL. Elle est compatible avec HeidiSQL et sysbench.

Méthode Windows

La synchronisation des comptes MySQL sur vos bases de compte Windows n'est aujourd'hui possible qu'avec la version commerciale de MySQL. Seule aujourd'hui MariaDB permet gratuitement de synchroniser vos comptes avec votre Active Directory.

# data[].attributes.attachment_link.description
Jusqu'à présent, que ce soit dans MariaDB ou dans MySQL, je créais mes utilisateurs de bases WordPress de la manière suivante, en une seule commande : GRANT ALL PRIVILEGES ON wordpress.* TO 'login'@'localhost' IDENTIFIED BY 'mot_de_passe'; Cette commande n'est plus possible ! Elle est interdite sous MySQL 8.

# data[].attributes.headline
Vous pouvez envoyer les logs de tous vos serveurs rsyslog Linux vers une base MySQL / MariaDB

# data[].attributes.subject
Vous pouvez envoyer les logs de tous vos serveurs rsyslog Linux vers une base MySQL / MariaDB

# data[].attributes.attachment_link.readable_text
rsyslog possède de très nombreux modules nous permettant d'envoyer les logs système vers les bases de données MySQL / MariaDB, PostgreSQL, ElasticSearch, Redis, à des fins d'analyse post-mortem par exemple.

Installation du module rsyslog-mysql

Après avoir installé au préalable une instance MySQL/MariaDB, nous allons ajouter sur Fedora 28 le paquet rsyslog-mysql :

yum install rsyslog-mysql

Création de la base Syslog

En installant le module vous disposez d'un script pour créer la base de données Syslog :

mysql -u root -p < /usr/share/doc/mysql-createDB.sql

Le script crée une base Syslog et lui rattache les tables SystemEvents et SystemEventsProperties. Pensez à créer un utilisateur disposant des droits sur cette base à partir de l'interpréteur mysql :

GRANT ALL PRIVILEGES ON Syslog.* TO utilisateur@localhost IDENTIFIED BY 'mot_de_passe';

Configuration du fichier /etc/rsyslog.conf

Dans Fedora 28, vous devez ajouter le module ommysql au fichier de configuration /etc/rsyslog.conf (finie la directive $ModLoad dans Fedora 28) :

module(load="ommysql")

Redirigez ensuite tous les messages en ajoutant tout à la fin de ce fichier :

*.* :ommysql:localhost,Syslog,utilisateur,mot_de_passe

Vous pouvez redémarrer le service rsyslog :

systemctl restart rsyslog

Requête sur la table SystemEvents

Après vous être connecté à la base Syslog nouvellement créé, tapez, par exemple, la requête pour visualiser les logs :

SELECT
    ReceivedAt,
    FromHost,
    Facility,
    Priority,
    Message,
    SysLogTag
FROM
    SystemEvents

Tables facility et severity

Afin d'établir les correspondances entre les codes des catégories et des niveaux d'erreur des messages, je vous conseille de créer les tables facility et severity :

CREATE TABLE facility
(
code INT,
keyword VARCHAR(50),
description VARCHAR(50)
);
INSERT INTO facility(code,keyword,description)
VALUES
(0,'kern','kernel messages'),
(1,'user','user-level messages'),
(2,'mail','mail system'),
(3,'daemon','system daemons'),
(4,'auth','security/authorization messages'),
(5,'syslog','messages generated internally by syslogd'),
(6,'lpr','line printer subsystem'),
(7,'news','network news subsystem'),
(8,'uucp','UUCP subsystem'),
(9,'cron','clock daemon'),
(10,'authpriv','security/authorization messages'),
(11,'ftp','FTP daemon'),
(12,'ntp','NTP subsystem'),
(13,'security','log audit'),
(14,'console','log alert'),
(15,'solaris-cron','scheduling daemon'),
(16,'local0','locally used facilities'),
(17,'local1','locally used facilities'),
(18,'local2','locally used facilities'),
(19,'local3','locally used facilities'),
(20,'local4','locally used facilities'),
(21,'local5','locally used facilities'),
(22,'local6','locally used facilities'),
(23,'local7','locally used facilities');

CREATE TABLE severity (
code INT,
title VARCHAR(20),
keyword VARCHAR(10)
);
INSERT INTO severity(code,title,keyword)
VALUES
(0,'Emergency','emerg'),
(1,'Alert','alert'),
(2,'Critical','crit'),
(3,'Error','err'),
(4,'Warning','warn'),
(5,'Notice','notice'),
(6,'Informational','info'),
(7,'Debug','debug');

Comprendre rsyslog

Vous disposez, sur Wikipédia, d'une excellente page consacrée à syslog dans laquelle vous aurez toutes les explications requises sur les valeurs figurant dans les colonnes Facility (la catégorie des messages) et Priority (le niveau de sévérité des messages).

# data[].attributes.attachment_link.title
Centraliser les logs rsyslog dans une base MySQL / MariaDB

# data[].attributes.attachment_link.description
rsyslog possède de très nombreux modules nous permettant d'envoyer les logs système vers les bases de données MySQL / MariaDB, PostgreSQL, ElasticSearch, Redis, à des fins d'analyse post-mortem par exemple.

# data[].attributes.attachment_link.readable_text
Oracle vient d'apporter des fonctionnalités supplémentaires à son moteur de bases de données MySQL 8.0, disponible en paquets pour Linux Red Hat / CentOS, Fedora, SUSE / openSUSE, Debian / Ubuntu.

J'ai sélectionné quelques nouveautés apportées à la dernière version 8 de MySQL :

Oracle fait le choix du tout InnoDB, distribué sous licence GPL. Le choix de la version communautaire de MySQL ne permet toujours pas de faire de hot backup de l'instance, contrairement à MariaDB Backup qui fonctionne avec MariaDB, le fork de MySQL. Le seul outil proposé par Oracle, InnoDB Backup, est intégré à la version payante MySQL Enterprise. Vous pouvez toutefois utiliser Percona XtraBackup avec MySQL.

# data[].attributes.attachment_link.description
Oracle vient d'apporter des fonctionnalités supplémentaires à son moteur de bases de données MySQL 8.0, disponible en paquets pour Linux Red Hat / CentOS, Fedora, SUSE / openSUSE, Debian / Ubuntu. J'ai sélectionné quelques nouveautés apportées à la dernière version 8 de MySQL : Oracle fait le choix du tout InnoDB, distribué sous licence GPL.

# data[].attributes.headline
Le fait de disposer d'un nombre insuffisant de connexions peut amener de la contention !

# data[].attributes.subject
Le fait de disposer d'un nombre insuffisant de connexions peut amener de la contention !

# data[].attributes.attachment_link.readable_text
Le nombre maximum de connexions à une instance MySQL / MariaDB est un des paramètres qui est très souvent surdimensionné.

Connaître le pic de connexions

Avec max_used_connections, vous disposez d'une variable qui vous permettra de connaître le pic de connexions :

SELECT
    variable_name,
    variable_value
FROM
    information_schema.global_status
WHERE
    variable_name = 'max_used_connections';

C'est cette valeur (max_used_connections) que j'utilise pour régler le paramètre max_connections !

Temps de déconnexion

Le fait d'augmenter le paramètre dynamique thread_pool_idle_timeout évite les déconnections incessantes et les temps de latence, du fait des reconnexions. Par défaut, le paramètre est fixé à 60 secondes.

SET GLOBAL  thread_pool_idle_timeout=30; -- Chez moi, ajusté au temps moyen de lecture des pages de mon blog

Nombre moyen de connexions

Pour connaître le cumul de connexions ( connections) et le temps de fonctionnement de l'instance ( uptime), tapez les requêtes suivantes :

SHOW GLOBAL STATUS LIKE 'connections';
SHOW GLOBAL STATUS LIKE 'uptime';

Le nombre moyen de connexions peut être très facilement déterminé par la formule de calcul suivante :

connections / uptime *  thread_pool_idle_timeout

Le souci est que ce temps moyen est lui-même tributaire du paramètre thread_pool_idle_timeout.

# data[].attributes.attachment_link.title
Déterminer max_connections dans MySQL / MariaDB

# data[].attributes.attachment_link.description
Le nombre maximum de connexions à une instance MySQL / MariaDB est un des paramètres qui est très souvent surdimensionné. Connaître le pic de connexions Avec max_used_connections, vous disposez d'une variable qui vous permettra de connaître le pic de connexions : SELECT variable_name, variable_value FROM information_schema.global_status WHERE variable_name = 'max_used_connections'; C'est cette valeur (max_used_connections) que j'utilise pour régler le paramètre max_connections !

# data[].attributes.headline
Ne laissez pas des moteurs inutilisés sur MySQL / MariaDB !

# data[].attributes.subject
Ne laissez pas des moteurs inutilisés sur MySQL / MariaDB !

# data[].attributes.attachment_link.readable_text
Dans le cadre de l'optimisation de vos bases de données WordPress motorisées par MySQL / MariaDB, il peut être intéressant de désactiver les moteurs inutilisés tels que ARCHIVE, BLACKHOLE, FEDERATED, INNODB, SEQUENCE.

La liste des moteurs pris en charge

Pour disposer de la liste des moteurs pris en charge par votre version de MySQL / MariaDB, tapez, après vous être connecté à MySQL :

SHOW ENGINES;

La liste des moteurs utilisés

Grâce à une requête sur la vue INFORMATION_SCHEMA.TABLES du dictionnaire des données, vous pourrez obtenir la liste des moteurs utilisés au niveau de votre instance MariaDB / MySQL :

SELECT DISTINCT engine FROM information_schema.tables;

Désactiver les motorisations inutilisés

Ajoutez à votre fichier /etc/my.cnf dans la section [mysqld] :

skip-archive
skip-blackhole
skip-federated
skip-innodb
skip-sequence

Pour passer de InnoDB à Aria

Avant de pouvoir désactiver InnoDB, il faut convertir vos tables en Aria. Aria est un fork introduit par MariaDB, reconnu plus performant sur le plan de la gestion du cache que MyISAM. Pour convertir votre table en Aria, tapez sous l'interpréteur mysql :

ALTER TABLE wp_posts ENGINE=Aria;

Vous pouvez aussi utiliser la procédure stockée qui automatise la conversion en Aria.

Aria, moteur par défaut

Ajoutez à votre fichier /etc/my.cnf dans la section [mysqld] :

default_storage_engine=Aria
default_tmp_storage_engine=Aria

# data[].attributes.attachment_link.title
Désactiver les moteurs inutilisés dans MariaDB / MySQL

# data[].attributes.attachment_link.description
Dans le cadre de l'optimisation de vos bases de données WordPress motorisées par MySQL / MariaDB, il peut être intéressant de désactiver les moteurs inutilisés tels que ARCHIVE, BLACKHOLE, FEDERATED, INNODB, SEQUENCE.

# data[].attributes.headline
Je ne crois pas m'être souvenu d'une telle galère en SQL !

# data[].attributes.subject
Je ne crois pas m'être souvenu d'une telle galère en SQL !

# data[].attributes.attachment_link.readable_text
J'ai appris le SQL en 1988, au cours de ma formation Administrateur de Bases de Données au GRETA Elbeuf Vallée de Seine. Je me souviens encore de la galère, lorsqu'il m'a fallu à écrire ma 1ère requête imbriquée. Ce n'était rien à côté de l'écriture de la requête ci-dessous dont l'objet est d'afficher dans la même colonne le chemin complet de l'adresse des pages d'un site WordPress. L'exemple ci-dessous est une requête récursive, s'appuyant sur les CTE Common Table Expressions. L'intérêt est d'avoir évité le recours à une procédure stockée, extrêmement fastidieuse à écrire.

Les valeurs " parentes "

Dans l'exemple ci-dessous, la 1ère requête contient la liste des pages parentes.

La requête récursive

La 2e requête, après l'opérateur ensembliste UNION, opère la jointure entre les résultats de la requête définie dans le WITH avec la liste des valeurs des pages enfantes.

WITH RECURSIVE urls AS
(
 SELECT
  parent.ID AS id,
  parent.post_parent AS parent,
  parent.post_name AS url,
  parent.post_type,parent.post_status AS status
 FROM
  wp_posts as parent
 WHERE
  parent.post_parent=0
 AND
  parent.post_type='page'
 AND
  parent.post_status='publish'
UNION
 SELECT
  enfant.ID AS id,
  enfant.post_parent AS parent,
  CONCAT(urls.url,'/',enfant.post_name) AS url,
  enfant.post_type AS type,
  enfant.post_status AS status
 FROM
  urls INNER JOIN wp_posts AS enfant ON enfant.post_parent=urls.id
 WHERE
  enfant.post_type='page'
 AND
  enfant.post_status='publish'
)
SELECT
 CONCAT('https://www.dsfc.net/',url,'/') AS url
FROM
 urls
ORDER BY
 url;

Le résultat est à la hauteur de mes espérances. Je m'en suis servi pour construire mon fichier urllist.txt.

# data[].attributes.attachment_link.title
Requête SQL récursive parent-enfant dans MySQL / MariaDB

# data[].attributes.attachment_link.description
J'ai appris le SQL en 1988, au cours de ma formation Administrateur de Bases de Données au GRETA Elbeuf Vallée de Seine. Je me souviens encore de la galère, lorsqu'il m'a fallu à écrire ma 1ère requête imbriquée.

# data[].attributes.headline
Si vous êtes développeur sur MySQL / MariaDB, alors cette formation est pour vous !

# data[].attributes.subject
Si vous êtes développeur sur MySQL / MariaDB, alors cette formation est pour vous !

# data[].attributes.attachment_link.readable_text
J'ai ajouté à mon offre de formations concernant les moteurs de bases de données une formation de 3 jours pour apprendre à développer dans les environnements MySQL / MariaDB.

Cette formation s'adresse avant tout à des développeurs, à des intégrateurs ainsi qu'aux techniciens d'exploitation et aux DBA . Il s'agit, au cours de ses 3 journées, d'apprendre le SQL (DDL, DML, DIL, DCL), mais aussi à programmer les procédures stockées en environnement transactionnel et relationnel.

Tout au long de cette cette formation Développement MySQL/MariaDB, je vous rappelerais les bonnes pratiques qui permettent d'obtenir la quintessence d'un moteur de bases de données. Au travers des plans d'exécution, des benchs et des interrogations des données statistiques, vous pourrez affiner votre code SQL en justifiant vos choix.

Nous utiliserons, au cours de la formation, trois GUI Open Source pour élaborer notre code :

MySQL WorkBench, qui permet la modélisation des bases MySQL

Dbeaver, un requêteur universel et un outil de modélisation des bases de données, qui a la faveur de très nombreux développeurs et DBA

# data[].attributes.attachment_link.description
J'ai ajouté à mon offre de formations concernant les moteurs de bases de données une formation de 3 jours pour apprendre à développer dans les environnements MySQL / MariaDB. Cette formation s'adresse avant tout à des développeurs, à des intégrateurs ainsi qu'aux techniciens d'exploitation et aux DBA .

# data[].attributes.headline
Pour vous documenter sérieusement sur MySQL / MariaDB

# data[].attributes.subject
Pour vous documenter sérieusement sur MySQL / MariaDB

# data[].attributes.attachment_link.readable_text
Je travaille très régulièrement sur MySQL / MariaDB. Les serveurs Web que j'administre exécutent tous aujourd'hui MariaDB. Je donne d'ailleurs une formation de 3 jours sur l'administration de MariaDB à Toulouse du lundi 14 au mercredi 16 octobre inclus. Je construis ma veille sur ces deux moteurs de bases de données à partir des sites officiels, d'agrégateurs de news et de blogs de référence. L'évolution fonctionnelle de ces deux logiciels exige d'actualiser vos connaissances en permanence.

Les sites officiels

Les agrégateurs

Les publications et les tutoriels

Les blogs de référence

# data[].attributes.attachment_link.description
Je travaille très régulièrement sur MySQL / MariaDB. Les serveurs Web que j'administre exécutent tous aujourd'hui MariaDB. Je donne d'ailleurs une formation de 3 jours sur l'administration de MariaDB à Toulouse du lundi 14 au mercredi 16 octobre inclus.

# data[].attributes.headline
MySQL 8.0 s'est fait sérieusement ripoliné par Oracle.

# data[].attributes.subject
MySQL 8.0 s'est fait sérieusement ripoliné par Oracle.

# data[].attributes.attachment_link.readable_text
La version MySQL 8.0 n'est toujours pas disponible en version stable à ce jour ! Mais l'éditeur - Oracle - a déjà publié depuis plusieurs mois les principales modifications qu'il entend apporter aux logiciels. Et elles ne sont pas très nombreuses. J'en ai vu trois intéressantes concernant le langage SQL :

L'éditeur a confirmé l' abandon du Query Cache. Je me suis déjà exprimé sur ce sujet et je continue de croire qu'il s'agit d'un rude coup porté à MySQL qui nous amènera à choisir définitivement MariaDB !

# data[].attributes.attachment_link.title
| Les fonctionnalités ajoutées à MySQL 8.0

# data[].attributes.attachment_link.description
La version MySQL 8.0 n'est toujours pas disponible en version stable à ce jour ! Mais l'éditeur - Oracle - a déjà publié depuis plusieurs mois les principales modifications qu'il entend apporter aux logiciels. Et elles ne sont pas très nombreuses. J'en ai vu trois intéressantes concernant le langage SQL : L'éditeur a confirmé l' abandon du Query Cache.

# data[].attributes.headline
Une procédure et un event pour planifier la maintenance de vos tables MyISAM et InnoDB !

# data[].attributes.subject
Une procédure et un event pour planifier la maintenance de vos tables MyISAM et InnoDB !

# data[].attributes.attachment_link.readable_text
Cette procédure stockée a pour objet de " défragmenter " les tables MyISAM et InnoDB à condition que ces dernières soient stockées en mode innodb_file_per_table=1. Son intérêt est aussi de réindexer les index associés aux tables. Vous pouvez appeler cette procédure par un EVENT.

/* Création de la procédure stockée*/
USE mysql;
DROP PROCEDURE IF EXISTS maintenance;
DELIMITER $$
CREATE PROCEDURE maintenance()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE val_table VARCHAR(255);
    DECLARE val_schema VARCHAR(255);
    DECLARE val_engine VARCHAR(255);
    DECLARE val_sql VARCHAR(255);
    DECLARE cur_tables CURSOR FOR
        SELECT table_schema, table_name, engine
        FROM information_schema.tables
        WHERE
            table_schema NOT IN ('mysql','information_schema','performance_schema')
        and
            engine IN ('MyISAM','InnoDB');
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur_tables;
    boucle: LOOP
        FETCH cur_tables INTO val_schema,val_table,val_engine;
        IF done THEN
            LEAVE boucle;
        END IF;
        SET @a=val_schema;
        SET @b=val_table;
        SET @c=val_engine;
        IF @c='MyISAM' THEN
            /* SET @s='REPAIR TABLE ?.?'; */
            SET @s=CONCAT('REPAIR TABLE `',@a,'`.`',@b,'`');
        ELSEIF @c='InnoDB' THEN
            /* SET @s='ALTER TABLE ?.? ENGINE=InnoDB'; */
            SET @s=CONCAT('ALTER TABLE `',@a,'`.`',@b,'` ENGINE=InnoDB');
        END IF;
        PREPARE stmt FROM @s;
        EXECUTE stmt /* USING @a,@b */;
        DEALLOCATE PREPARE stmt;
        /* SET @s='OPTIMIZE TABLE ?.?'; */
        SET @s=CONCAT('OPTIMIZE TABLE `',@a,'`.`',@b,'`');
        PREPARE stmt FROM @s;
        EXECUTE stmt /* USING @a,@b */;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur_tables;
END
$$
DELIMITER ;
/* Utilisation de la procédure stockée */
use mysql;
CALL maintenance;

# data[].attributes.attachment_link.title
| MySQL : maintenance des tables et des index MyISAM et InnoDB

# data[].attributes.attachment_link.description
Cette procédure stockée a pour objet de " défragmenter " les tables MyISAM et InnoDB à condition que ces dernières soient stockées en mode innodb_file_per_table=1. Son intérêt est aussi de réindexer les index associés aux tables. Vous pouvez appeler cette procédure par un EVENT.

# data[].attributes.headline
Franchement, c'est assez contre-productif que d'installer MySQL sous Windows !

# data[].attributes.subject
Franchement, c'est assez contre-productif que d'installer MySQL sous Windows !

# data[].attributes.attachment_link.readable_text
Malgré le fork MariaDB, MySQL continue d'être la base de données la plus utilisée au monde. Cette formation à l'administration de MySQL que nous vous proposons sur 4 journées est réservée aux DBA, administrateurs système, et techniciens d'exploitation qui connaissent les commandes de base des systèmes Linux.

Pourquoi Linux ?

Même s'il est possible d'installer MySQL sous Windows Server 2008 R2/2012 R2, l'intérêt d'utiliser un système Linux CentOS/Red Hat ou Debian/Ubuntu Server LTS, c'est la faible consommation mémoire et de CPU qu'offrent ces différentes distributions : 100 Mo maximum, là où Windows consomme 1 Go au minimum.

Quelles distributions Linux ?

Nous vous recommandons de déployer MySQL sur CentOS et Debian, connues et reconnues pour leur grande stabilité et aussi pour leur simplicité d'utilisation grâce à leur gestionnaire de packages respectifs yum et apt. Dans ce registre, n'oublions pas de mentionner openSUSE.

# data[].attributes.attachment_link.keywords[]
administrateur de bases de données mysql

# data[].attributes.attachment_link.description
Malgré le fork MariaDB, MySQL continue d'être la base de données la plus utilisée au monde. Cette formation à l'administration de MySQL que nous vous proposons sur 4 journées est réservée aux DBA, administrateurs système, et techniciens d'exploitation qui connaissent les commandes de base des systèmes Linux. Pourquoi Linux ?

# data[].attributes.headline
mysql_secure_install, le script de post-installation de MySQL / MariaDB !

# data[].attributes.subject
mysql_secure_install, le script de post-installation de MySQL / MariaDB !

# data[].attributes.attachment_link.readable_text
J'ai découvert la commande mysql_secure_installation avec MariaDB, alors qu'elle existe depuis MySQL 5.0. Très curieusement, elle est absente des installations sous Windows. Sur Linux, elle vous permet de :

changer le mot de passe au compte root de MariaDB ;

empêcher les connexions anonymes ;

désactiver les connexions distantes réalisées avec le compte root ;

supprimer la base de test.

Set root password? [Y/n] Y
Remove anonymous users? [Y/n] Y
Disallow root login remotely? [Y/n] Y
Remove test database and access to it? [Y/n] Y
Reload privilege tables now? [Y/n] Y

# data[].attributes.attachment_link.title
| Sécuriser MariaDB et MySQL avec mysql_secure_installation

# data[].attributes.attachment_link.description
J'ai découvert la commande mysql_secure_installation avec MariaDB, alors qu'elle existe depuis MySQL 5.0. Très curieusement, elle est absente des installations sous Windows. Sur Linux, elle vous permet de : changer le mot de passe au compte root de MariaDB ; empêcher les connexions anonymes ; désactiver les connexions distantes réalisées avec le compte root ; supprimer la base de test.

# data[].attributes.headline
Des différences parfois importantes entre MySQL Et MariaDB : alors lequel choisir ?

# data[].attributes.subject
Des différences parfois importantes entre MySQL Et MariaDB : alors lequel choisir ?

# data[].attributes.attachment_link.readable_text
Vous trouverez beaucoup d'articles consacrés aux différences entre MariaDB et MySQL, dans les blogs et les réseaux sociaux. Avant toute chose, je vous recommanderais, pour ma part, d'aller jeter un œil sur le site officiel de l'éditeur :

Moteurs prise en charge

L'un des intérêts majeurs de MariaDB 10 est la prise en charge de 10 moteurs de stockage supplémentaires par rapport à MySQL, dont Cassandra. A noter que l'éditeur préconise l'utilisation de Aria au lieu de MyISAM et XtraDB, fourni par Percona, à la place de InnoDB !

Incompatibilités

Elles concernent principalement l'ajout de fonctionnalités et de variables à MariaDB non prises en charge sous MySQL. L'éditeur parle également de performances accrues. La migration vers MariaDB porte en elle le fait de ne jamais revenir à MySQL !

Livre blanc MariaDB vs MySQL

Les éditeurs de MariaDB ont mis à notre disposition un livre blanc daté de septembre 2012 de 6 pages, dans lequel vous apprendrez que l'une des améliorations est la prise en charge des micro-secondes dans le format DATETIME et TIMESTAMP ! Ce document n'a strictement aucun intérêt.

Simple curiosité

Par curiosité, j'ai effectué une recherche sur le mot MariaDB dans le site d'Oracle consacré à MySQL. Pas grand chose à se mettre sous la dent ! Vous trouverez bien plus d'articles sur le site Planet MySQL.

# data[].attributes.attachment_link.title
| Les différences entre MariaDB et MySQL

# data[].attributes.attachment_link.description
Vous trouverez beaucoup d'articles consacrés aux différences entre MariaDB et MySQL, dans les blogs et les réseaux sociaux. Avant toute chose, je vous recommanderais, pour ma part, d'aller jeter un œil sur le site officiel de l'éditeur : Moteurs prise en charge L'un des intérêts majeurs de MariaDB 10 est la prise en charge de 10 moteurs de stockage supplémentaires par rapport à MySQL, dont Cassandra.

# data[].attributes.headline
Comment faire un MERGE dans MariaDB / MySQL ?

# data[].attributes.subject
Comment faire un MERGE dans MariaDB / MySQL ?

# data[].attributes.attachment_link.readable_text
L'instruction MERGE qui permet de fusionner les données d'une table à partir des données du contenu d'une autre table n'existe pas dans MariaDB / MySQL. Elle n'existe pas non plus dans PostgreSQL ! Elle existe dans Oracle Database, Microsoft SQL Server et Firebird.

Sous MariaDB / MySQL, vous disposez de la commande SQL INSERT ... ON DUPLICATE KEY UPDATE.

Script de création de tables

Créez deux tables sur le modèle du script fourni ci-dessous :

CREATE TABLE `societe` (
 `societe_id` int(6) NOT NULL AUTO_INCREMENT,
 `societe_raisonsociale` varchar(50) NOT NULL,
 `societe_cp` int(6) NOT NULL,
 `societe_ville` varchar(50) DEFAULT NULL,
 PRIMARY KEY (`societe_id`),
 UNIQUE KEY `societe_uk` (`societe_raisonsociale`,`societe_cp`),
 KEY `societe_ix_ville` (`societe_ville`),
 KEY `societe_ix_raisonsociale` (`societe_ville`),
 KEY `societe_ix_cp` (`societe_cp`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

CREATE TABLE `client` (
 `client_id` int(6) NOT NULL AUTO_INCREMENT,
 `client_raisonsociale` varchar(50) NOT NULL,
 `client_cp` int(6) NOT NULL,
 `client_ville` varchar(50) DEFAULT NULL,
 PRIMARY KEY (`client_id`),
 UNIQUE KEY `client_uk` (`client_raisonsociale`,`client_cp`),
 KEY `client_idx_ville` (`client_ville`),
 KEY `client_idx_raisonsociale` (`client_ville`),
 KEY `client_idx_cp` (`client_cp`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

La fusion des tables

Après avoir alimenté la table client par un jeu d'essai, exécutez l'ordre SQL suivant pour copier dans la table societe les données de client :

INSERT INTO societe (societe_raisonsociale,societe_cp,societe_ville)
SELECT client_raisonsociale,client_cp,client_ville FROM client

Modifiez ensuite la ville de l'une des lignes de la table client et exécutez l'ordre de fusion des deux tables :

INSERT INTO societe (societe_raisonsociale,societe_cp,societe_ville)
SELECT client_raisonsociale,client_cp,client_ville FROM client
ON DUPLICATE KEY UPDATE societe_ville=VALUES(societe_ville);

# data[].attributes.attachment_link.description
L'instruction MERGE qui permet de fusionner les données d'une table à partir des données du contenu d'une autre table n'existe pas dans MariaDB / MySQL. Elle n'existe pas non plus dans PostgreSQL ! Elle existe dans Oracle Database, Microsoft SQL Server et Firebird. Sous MariaDB / MySQL, vous disposez de la commande SQL INSERT ...

# data[].attributes.headline
Des problèmes de compatibilité entre MySQL WorkBench et MariaDB !

# data[].attributes.subject
Des problèmes de compatibilité entre MySQL WorkBench et MariaDB !

# data[].attributes.attachment_link.readable_text
MySQL WorkBench reste un outil de référence pour administrer à distance une base de données MariaDB ou MySQL. Malgré ses immenses qualités, HeidiSQL ne permet pas de faire ce que fait aujourd'hui l'outil Open Source fourni par Oracle !

Lors du passage à la version 6.3 de MySQL WorkBench, j'ai eu droit à ce message lorsque j'ai cherché à me connecter à MariaDB 10.1.13.

Pour les instances MariaDB abritant exclusivement des bases MyISAM et InnoDB, cela ne pose aucun problème de compatibilité particulier. Vous pouvez choisir Continue AnyWay ! En revanche, le choix du format Aria en lieu et place de MyISAM préconisé MariaDB vous interdira de modifier la structure de la table sous MySQL WorkBench. Vous pouvez continuer à en manipuler ses données à l'aide des ordres DIL et DML SELECT, INSERT, UPDATE, DELETE.

Comme je le disais dans un article récent consacré aux différences entre MariaDB et MySQL, le choix de MariaDB risque d'être à la base de données Open Source ce que le divorce est au régime matrimonial : un aller sans retour !

# data[].attributes.attachment_link.description
MySQL WorkBench reste un outil de référence pour administrer à distance une base de données MariaDB ou MySQL. Malgré ses immenses qualités, HeidiSQL ne permet pas de faire ce que fait aujourd'hui l'outil Open Source fourni par Oracle !

# data[].attributes.headline
Comment contourner la limite des 1000 caractères pour un index ?

# data[].attributes.subject
Comment contourner la limite des 1000 caractères pour un index ?

# data[].attributes.attachment_link.readable_text
Je suis en train de concrétiser, enfin, l'écriture d'un moteur de recherche que j'avais en projet depuis de nombreuses années. Il m'a fallu choisir un moteur de bases de données et mon choix s'est assez vite porté sur MySQL / MariaDB pour la simple raison qu'il a été utilisé à l'origine du projet Google. Et l'éditeur a très largement contribué à l'améliorer, avant qu'il ne décide de s'en affranchir totalement. Je tiens à préciser que je n'ai pas besoin de la partie relationnelle et que j'ai fait le choix de tables au format MyISAM / Aria.

La limite des 1000 caractères pour les index uniques

Il est impossible d'associer une contrainte d'unicité dans MySQL / MariaDB sur un champ de plus de 1000 caractères. Or, comme les adresses Http/Https/Ftp peuvent aller jusqu'à 2000 caractères, il faut trouver une autre solution. La solution que j'ai choisie, c'est d'associer un hash MD5 à l'adresse que j'ai passé en index unique ! La colonne ne représente plus qu'un longueur de 32 caractères. CQFD.

La fonction MD5 dans MySQL / MariaDB

L' utilisation de la fonction MD5 est très bien expliquée dans la documentation MySQL :

SELECT MD5('https://www.dsfc.net/');
+----------------------------------+
| MD5('https://www.dsfc.net/')      |
+----------------------------------+
| 949a65833e197145428e9ed18710ddb1 |
+----------------------------------+

La fonction md5 dans PHP

Comme je vais écrire mon module d'exploration en PHP, je vais être également être amené à utiliser la fonction md5 du PHP :

php -r "echo md5('https://www.dsfc.net/');"
949a65833e197145428e9ed18710ddb1

Vous pouvez également utiliser la fonction hash :

php -r "echo hash('md5','https://www.dsfc.net/');"
949a65833e197145428e9ed18710ddb1

Comme vous pouvez le remarquer, les deux valeurs obtenues en PHP et sous MySQL / MariaDB sont rigoureusement identiques. Ouf ! ;+)

# data[].attributes.attachment_link.title
| MySQL : la limite des 1000 caractères pour un index unique

# data[].attributes.attachment_link.description
Je suis en train de concrétiser, enfin, l'écriture d'un moteur de recherche que j'avais en projet depuis de nombreuses années. Il m'a fallu choisir un moteur de bases de données et mon choix s'est assez vite porté sur MySQL / MariaDB pour la simple raison qu'il a été utilisé à l'origine du projet Google.

# data[].attributes.headline
Plus de possibilité concernant les expressions régulières sur MariaDB que sur MySQL !

# data[].attributes.subject
Plus de possibilité concernant les expressions régulières sur MariaDB que sur MySQL !

# data[].attributes.attachment_link.readable_text
A l'exception notoire de SQL Server, les moteurs de bases de données MySQL/MariaDB, PostgreSQL et Oracle Database possèdent des fonctions natives pour traiter les expressions régulières. Mais c'est aujourd'hui très clairement MySQL qui est le moins bien loti des 4 moteurs, limitant l'usage des expressions régulières à la seule fonction REGEXP.

Les expressions régulières dans MariaDB

Depuis la version 10.0.5, comme Oracle (depuis la version 10.2), MariaDB dispose de 3 fonctions supplémentaires relatives au traitement des expressions régulières :

MariaDB est, en matière d'expressions régulières, très au dessus de PostgreSQL.

Exemple d'utilisation de REGEXP_SUSBSTR() dans MariaDB

SELECT
    REGEXP_SUBSTR('https://www.dsfc.net/firefox','^https?://[^/$]+') url;

# data[].attributes.attachment_link.title
| Une raison sérieuse de préférer MariaDB à MySQL ?

# data[].attributes.attachment_link.description
A l'exception notoire de SQL Server, les moteurs de bases de données MySQL/MariaDB, PostgreSQL et Oracle Database possèdent des fonctions natives pour traiter les expressions régulières. Mais c'est aujourd'hui très clairement MySQL qui est le moins bien loti des 4 moteurs, limitant l'usage des expressions régulières à la seule fonction REGEXP.

# data[].attributes.headline
Ça ressemble quand même à un baiser de la mort !

# data[].attributes.subject
Ça ressemble quand même à un baiser de la mort !

# data[].attributes.attachment_link.readable_text
Les 1ères formations que j'ai données sur MySQL datent de 2003. J'ai près de 15 années d'expérience sur ce moteur de bases de données devenu relationnel avec l'arrivée d'InnoDB à partir de la version 3.23.

Le baiser de la mort

En rachetant Sun, Oracle a récupéré ce moteur Open Source et ne l'a pas vraiment fait évolué. Pas de CHECK CONSTRAINT au niveau de la définition des tables, ce qui nous oblige à passer par des triggers. Pas d'opérateur INTERSECT, MINUS/ EXCEPT au niveau des requêtes. Pas de possibilité de combiner du LEFT JOIN et du RIGHT JOIN dans la même requête. J'ai voulu hier faire un UPDATE en utilisant une requête imbriquée dans ma clause WHERE. Refus du logiciel ! Pas de OVER... PARTITION BY. Il faut bidouiller à chaque fois que vous avez une requête complexe à réaliser ! La plupart de c es restrictions fonctionnelles sont également valables dans MariaDB, à l'exception du CHECK CONSTRAINT disponible à partir de la version 10.2.1. Cerise sur le gâteau, et comme pour mieux en finir avec MySQL, Oracle a décidé d' abandonner le Query Cache, recommandant de passer au logiciel ProxySQL qui n'existe même pas sous Windows. Les éditeurs d'application industrielles qui auront fait le choix de MySQL apprécieront !

Bref, Oracle a choisi de faire mourir MySQL. Et d'ailleurs, de très nombreuses entreprises ont d'ores et déjà basculé vers PostgreSQL, même si, dans certains cas, ce n'est pas la meilleure idée qu'elles ont pu avoir. Pour les automates, il peut être intéressant de jeter un œil sur Firebird ou bien d'utiliser SQL Server Express 2016. Quant à Oracle, il a décidé d'arrêter de fournir la version Expression Edition à partir de la version 12c de son célèbre moteur commercial.

Alors, MariaDB ?

Conscients du projet de l'éditeur, les auteurs de MySQL, qui ont maintenant tous quitté Oracle, ont forké le projet en créant MariaDB. Le travail à faire est considérable s'ils veulent mettre leur moteur de bases de données sur le même plan fonctionnel que PostgreSQL ! Le moteur MariaDB a toutefois de sérieux atouts en termes de performances par rapport à son " concurrent " Open Source.

Compte tenu des nombreuses applications Web s'appuyant sur MySQL, nous n'avons pas d'autre choix à court terme que de remplacer MySQL par MariaDB.

# data[].attributes.attachment_link.title
| Oracle a décidé d'en finir avec MySQL !

# data[].attributes.attachment_link.description
Les 1ères formations que j'ai données sur MySQL datent de 2003. J'ai près de 15 années d'expérience sur ce moteur de bases de données devenu relationnel avec l'arrivée d'InnoDB à partir de la version 3.23. Le baiser de la mort En rachetant Sun, Oracle a récupéré ce moteur Open Source et ne l'a pas vraiment fait évolué.

# data[].attributes.headline
Vous avez perdu votre mot de passe root sous MySQL ? Pas de problème !

# data[].attributes.subject
Vous avez perdu votre mot de passe root sous MySQL ? Pas de problème !

# data[].attributes.attachment_link.readable_text
Si vous avez perdu ou oublié le mot de passe associé au compte root de votre instance de bases de données MySQL / MariaDB, il existe une solution extrêmement simple pour le réinitialiser.

Redémarrer le service MySQL en mode -skip-grant-tables

systemd a désormais remplacé la méthode SysV dite SysVinit de lancement des services sous Linux.

systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
systemctl restart mariadb

Dans ce mode, la connexion au compte root ne requiert plus aucun mot de passe !

Définir un nouveau mot de passe avec mysql_secure_installation

Il existe plusieurs méthodes pour réinitialiser le mot de passe. Je vous recommande pour ma part l'utilisation de la commande :

mysql_secure_installation

Autre(s) méthode(s) à l'aide d'instructions SQL

Connectez-vous à la base de données en tapant à partir de l'invite la commande mysql. Puis, dans l'interpréteur MySQL, tapez :

SET PASSWORD FOR root@'localhost'=PASSWORD('mon_nouveau_mot-de_passe');

ou

UPDATE mysql.user SET password=PASSWORD('mon_nouveau_mot_de_passe') WHERE User='root' AND Host = 'localhost';
FLUSH PRIVILEGES;

ou

GRANT ALL PRIVILEGES ON *.* TO root@'localhost' IDENTIFIED BY 'mon_nouveau_mot_de_passe';

Redémarrer le service en désactivant le mode -skip-grant-tables

Il ne vous reste plus qu'à redémarrer le service MySQL / MariaDB, pour que le nouveau mot de passe soit pris en compte :

systemctl unset-environment MYSQLD_OPTS
systemctl restart mariadb

# data[].attributes.attachment_link.title
| Perte du mot de passe root MySQL / MariaDB

# data[].attributes.attachment_link.description
Si vous avez perdu ou oublié le mot de passe associé au compte root de votre instance de bases de données MySQL / MariaDB, il existe une solution extrêmement simple pour le réinitialiser. Redémarrer le service MySQL en mode -skip-grant-tables systemd a désormais remplacé la méthode SysV dite SysVinit de lancement des services sous Linux.

# data[].attributes.headline
Quelques réglages de la motorisation InnoDB assez méconnus.

# data[].attributes.subject
Quelques réglages de la motorisation InnoDB assez méconnus.

# data[].attributes.attachment_link.readable_text
Il est préférable que ces réglages à inscrire dans le fichier /etc/my.cnf soient réalisés avant la mise en place de vos bases et de vos tables InnoDB, voire avant d'installer MySQL ou MariaDB

innodb_data_file_path

Vous devez définir une taille minimale de 10 Mo pour le " tablespace " InnoDB ( ibdata1 par défaut), même si vous optez pour le stockage des tables dans des fichiers IBD ( innodb_file_per_table=1).

innodb_data_file_path=ibdata:10M:autoextend

innodb_autoextend_increment

Par défaut, le fichier ibdata créé par la directive précédente s'auto-incrémente de 64 Mo. Vous pouvez indiquer un incrément minimum de 1 Mo. Cela aura pour effet de limiter la croissance du fichier ibdata, défini par la directive innodb_data_file_path.

innodb_autoextend_increment=1

innodb_compression_level

Par défaut, MySQL stocke des données dans les tables InnoDB en utilisant la librairie zlib. La compression et la décompression des données écrites et lues sur disque ont un coût. Il peut être intéressant de désactiver la compression des données.

innodb_compression_level=0

innodb_file_per_table

C'est un des paramètres les plus intéressants attachés à la motorisation InnoDB. Chaque table est alors stockée de manière indépendante dans un fichier IBD. Accessoirement, cela vous permet d'utiliser la commande OPTIMIZE TABLE sur des tables InnoDB.

innodb_file_per_table=1

# data[].attributes.attachment_link.description
Il est préférable que ces réglages à inscrire dans le fichier /etc/my.cnf soient réalisés avant la mise en place de vos bases et de vos tables InnoDB, voire avant d'installer MySQL ou MariaDB innodb_data_file_path Vous devez définir une taille minimale de 10 Mo pour le " tablespace " InnoDB ( ibdata1 par défaut), même si vous optez pour le stockage des tables dans des fichiers IBD ( innodb_file_per_table=1).

# data[].attributes.headline
Encore une erreur incompréhensible Sous MySQL ! Obligé de lancer une réparation !

# data[].attributes.subject
Encore une erreur incompréhensible Sous MySQL ! Obligé de lancer une réparation !

# data[].attributes.attachment_link.readable_text
Pour une raison que la raison ignore, j'ai eu ce message étrange en voulant supprimer une base de données de mon instance MySQL 5.6 : " Table '.\\mysql\\proc' is marked as crashed and should be repaired ",145.

Le message semblait indiquer qu'il fallait réparer la table mysql.proc. J'ai donc, à l'aveugle, décidé de tout réparer :

mysqlcheck --auto-repair --all-databases

J'ai pu ensuite supprimer les bases que je ne parvenais pas à supprimer.

# data[].attributes.attachment_link.description
Pour une raison que la raison ignore, j'ai eu ce message étrange en voulant supprimer une base de données de mon instance MySQL 5.6 : " Table '.\\mysql\\proc' is marked as crashed and should be repaired ",145. Le message semblait indiquer qu'il fallait réparer la table mysql.proc.

# data[].attributes.headline
Un vrai problème de sécurité avec l'historique de commandes saisies dans l'interpréteur MySQL sous Linux !

# data[].attributes.subject
Un vrai problème de sécurité avec l'historique de commandes saisies dans l'interpréteur MySQL sous Linux !

# data[].attributes.attachment_link.readable_text
En clone parfait de MySQL, la version 5.5 de MariaDB présente sur Fedora 20 se comporte comme MySQL 5.5 fourni par Oracle.

Problématique

Par défaut, l'historique des commandes saisies par l'utilisateur sous MySQL est stocké dans $HOME/.mysql_history. Le fichier contient en clair toutes les commandes qui vous ont permis à l'aide de CREATE USER/ GRANT ou de SET PASSWORD de créer les utilisateurs ou de modifier leurs mots de passe.

Désactiver l'historique de commandes

Il existe deux solutions. La première consiste à créer un lien symbolique :

ln -s ~/.mysql_history /dev/null

La seconde consiste pour l'utilisateur (~/.bashrc ou ~/.bash_profile) ou pour tous les utilisateurs (/etc/bashrc) à éditer le fichier indiqué entre parenthèses et ajouter :

export MYSQL_HISTFILE=/dev/null

# data[].attributes.attachment_link.title
| Désactiver l'historique des commandes MariaDB / MySQL

# data[].attributes.attachment_link.description
En clone parfait de MySQL, la version 5.5 de MariaDB présente sur Fedora 20 se comporte comme MySQL 5.5 fourni par Oracle. Problématique Par défaut, l'historique des commandes saisies par l'utilisateur sous MySQL est stocké dans $HOME/.mysql_history.

# data[].attributes.headline
Si vous souhaitez apprendre le SQL (DDL, DML, DIL, DCL), alors ce support sur MySQL est pour vous !

# data[].attributes.subject
Si vous souhaitez apprendre le SQL (DDL, DML, DIL, DCL), alors ce support sur MySQL est pour vous !

# data[].attributes.attachment_link.readable_text
J'ai mis à votre disposition un premier support consacré au DDL (CREATE, ALTER, DROP, ...) sous MySQL le 17 janvier 2013 dans le cadre d'un cours que je dispense aux étudiants de 3e année de l'Hétic, école installée à Montreuil. J'ai hélas totalement oublié de vous mettre à disposition le support relatif au DML (SELECT, INSERT, UPDATE, DELETE) que j'ai fourni aux étudiants.

Je tiens à rappeler une nouvelle fois que ces supports sont distribués sous licence Creative Commons CC-by-nc-nd. Ils ne peuvent donc faire l'objet d'une exploitation commerciale sans mon accord et sans rétribution. J'attends vos remarques et vos observations.

Supports de cours MySQL

# data[].attributes.attachment_link.description
J'ai mis à votre disposition un premier support consacré au DDL (CREATE, ALTER, DROP, ...) sous MySQL le 17 janvier 2013 dans le cadre d'un cours que je dispense aux étudiants de 3e année de l'Hétic, école installée à Montreuil.

# data[].attributes.headline
Rétrospectivement, Michael Widenius a gagné son pari en forkant le projet MySQL sous l'appellation MariaDB.

# data[].attributes.subject
Rétrospectivement, Michael Widenius a gagné son pari en forkant le projet MySQL sous l'appellation MariaDB.

# data[].attributes.attachment_link.readable_text
Quelque temps avant le rachat de Sun Microsystems par Oracle en 2009, Michael Widenius - l'un des fondateurs de MySQL - avait cloné la base de données Open Source dans le projet MariaDB. Depuis 2009, de nombreux acteurs de l'Internet, du logiciel libre et de l'informatique abandonnent MySQL au profit de MariaDB. En décembre 2012, c'est Wikipédia qui bascule. Le mouvement s'accélère en février 2013 avec Fedora et SUSE qui décident de lâcher MySQL. Hier, des rumeurs faisaient état que Red Hat avait l'intention de passer à MariaDB. Il ne s'agit plus d'une rumeur !!! La version Red Hat 7 embarquera bien MariaDB.

Cette décision de l'un des acteurs majeurs de l'informatique ne doit rien au hasard pur. En avril 2013, les anciens du projet MySQL ont fusionné leurs différentes activités et se sont mis d'accord pour soutenir le projet MariaDB en y intégrant le meilleur du SQL et du NoSQL. Le renfort des équipes de SkySQL amène le crédit qui semblait faire défaut au nouveau projet MariaDB.

Quelles conséquences ?

Du fait de sa compatibilité totale, le choix de MariaDB n'aura strictement aucune conséquence sur le fonctionnement des applications qui utilisent MySQL ! Les connecteurs et les outils pour MySQL, téléchargeables à partir du site d'Oracle, marchent également avec MariaDB.

MySQL semble suivre le même chemin qu'OpenOffice. Pour Oracle, est-ce, au fond, une aussi mauvaise chose que ça, alors qu' il se fait grignoter par PostgreSQL et par SQL Server sur son cœur de métier ?

# data[].attributes.attachment_link.description
Quelque temps avant le rachat de Sun Microsystems par Oracle en 2009, Michael Widenius - l'un des fondateurs de MySQL - avait cloné la base de données Open Source dans le projet MariaDB. Depuis 2009, de nombreux acteurs de l'Internet, du logiciel libre et de l'informatique abandonnent MySQL au profit de MariaDB.

# data[].attributes.headline
Les EVENTS sont une sorte de planificateur de tâches intégré à MySQL / MariaDB. Pratique pour l'exécution de tâches de maintenance !

# data[].attributes.subject
Les EVENTS sont une sorte de planificateur de tâches intégré à MySQL / MariaDB. Pratique pour l'exécution de tâches de maintenance !

# data[].attributes.attachment_link.readable_text
Depuis la version 5.1.6, MySQL dispose d'un planificateur d'événements qui évite de recourir à des scripts que nous planifions avec le gestionnaire cron sous Linux. La méthode est bien plus sûre puisqu'elle évite que les mots de passe se baladent dans différents fichiers du serveur, en clair !

Activation du planificateur d'événements

Vous pouvez éditer le fichier my.ini ou my.cnf et ajouter dans la section [mysqld] la ligne event_scheduler=1. Vous devez alors redémarrer l'instance :

service mysqld restart, sous CentOS (la méthode reload ne marche pas pour MySQL)

net stop MySQL && net start MySQL, sous Windows

Vous pouvez aussi modifier à chaud ce paramètre par l'une ou l'autre des deux commandes ci-dessous :

Pour vérifier que le planificateur d'événements s'exécute sous MySQL, tapez :

SELECT @@global.event_scheduler

Vous pouvez, par ailleurs, vérifier le fonctionnement du planificateur d'événements avec la commande S HOW PROCESSLIST.

Réparation et optimisation planifiées de tables MyISAM

Cet événement planifié a pour objet de programmer tous les jours les commandes REPAIR et OPTIMIZE sur des tables MyISAM :

USE mysql;
DROP EVENT IF EXISTS maintenance;
DELIMITER $$
CREATE EVENT maintenance
    ON SCHEDULE EVERY 1 DAY STARTS '2014-05-10 01:00:00'
    ENABLE
    DO
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE val_table VARCHAR(255);
    DECLARE val_schema VARCHAR(255);
    DECLARE val_engine VARCHAR(255);
    DECLARE val_sql VARCHAR(255);
    DECLARE cur_tables CURSOR FOR
        SELECT table_schema, table_name, engine
        FROM information_schema.tables
        WHERE
            table_schema NOT IN ('mysql','information_schema','performance_schema')
        and
            engine IN ('MyISAM','InnoDB');
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur_tables;
    boucle: LOOP
        FETCH cur_tables INTO val_schema,val_table,val_engine;
        IF done THEN
            LEAVE boucle;
        END IF;
        SET @a=val_schema;
        SET @b=val_table;
        SET @c=val_engine;
        IF @c='MyISAM' THEN
            /* SET @s='REPAIR TABLE ?.?'; */
            SET @s=CONCAT('REPAIR TABLE `',@a,'`.`',@b,'`');
        ELSEIF @c='InnoDB' THEN
            /* SET @s='ALTER TABLE ?.? ENGINE=InnoDB'; */
            SET @s=CONCAT('ALTER TABLE `',@a,'`.`',@b,'` ENGINE=InnoDB');
        END IF;
        PREPARE stmt FROM @s;
        EXECUTE stmt /* USING @a,@b */;
        DEALLOCATE PREPARE stmt;
        /* SET @s='OPTIMIZE TABLE ?.?'; */
        SET @s=CONCAT('OPTIMIZE TABLE `',@a,'`.`',@b,'`');
        PREPARE stmt FROM @s;
        EXECUTE stmt /* USING @a,@b */;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur_tables;
END
$$
DELIMITER ;

Vérifications d'usage

Le 1er contrôle à effectuer est de vérifier que votre " event " a bien été créé. Pour ce faire, vous pouvez employer deux méthodes distinctes :

Mais cela ne suffit pas. Il vous faudra contrôler dans les logs de MySQL que la commande programmée s'est correctement exécutée. Pour les activer, là-encore, vous disposez de deux méthodes.

A froid, ajoutez general_log=1 dans la section [mysqld] du fichier my.ini ou my.cnf et redémarrez l'instance comme indiqué ci-dessus.

A chaud , tapez à partir de l'invite MySQL SET GLOBAL general_log=1 ou SET @@global.general_log=1.

Le fichier de logs du type host.log se situe par défaut à l'endroit où sont stockées les données.

Pour plus d'information sur la commande CREATE EVENT : Event Scheduler Configuration

# data[].attributes.attachment_link.description
Depuis la version 5.1.6, MySQL dispose d'un planificateur d'événements qui évite de recourir à des scripts que nous planifions avec le gestionnaire cron sous Linux. La méthode est bien plus sûre puisqu'elle évite que les mots de passe se baladent dans différents fichiers du serveur, en clair !

# data[].attributes.headline
Chez OVH, pensez de temps à autre à faire un OPTIMIZE TABLE. Vous risquez, en hébergement mutualisé, de laisser les tables gonfler très inutilement.

# data[].attributes.subject
Chez OVH, pensez de temps à autre à faire un OPTIMIZE TABLE. Vous risquez, en hébergement mutualisé, de laisser les tables gonfler très inutilement.

# data[].attributes.attachment_link.readable_text
Sur Ovh, je dispose actuellement d'un hébergement concernant ma base MySql de 40 Mo. La mise en place de PhpMyVisites est très coûteuse en terme de consommation d'espace. Depuis début septembre, la table phpmv_visit a atteint 17 Mo.

Or, après effacement des enregistrements, la base occupait toujours la même place.

La solution consiste à exécuter la commande SQL suivante :

 OPTIMIZE TABLE phpmv_visit;

Vous récupérez alors la place libérée. La base de données doit être au format MyISAM.

# data[].attributes.attachment_link.title
| Ovh : diminuer la taille des bases de données MySQL

# data[].attributes.attachment_link.description
Sur Ovh, je dispose actuellement d'un hébergement concernant ma base MySql de 40 Mo. La mise en place de PhpMyVisites est très coûteuse en terme de consommation d'espace. Depuis début septembre, la table phpmv_visit a atteint 17 Mo. Or, après effacement des enregistrements, la base occupait toujours la même place.

# data[].attributes.headline
Je ne sais plus pourquoi j'ai cherché à exporter en XML. La demande d'un client sans doute ?

# data[].attributes.subject
Je ne sais plus pourquoi j'ai cherché à exporter en XML. La demande d'un client sans doute ?

# data[].attributes.attachment_link.readable_text
Pour éviter de recourir à un mapping object-relationnel par le biais d'une classe statique, vous pouvez utiliser le schéma XML produit par MySQL :
mysqldump -X -u root -password=votre_mot_de_passe -databases votre_base_de_donnees -no-data=TRUE
Sous Php 5.0 et supérieur, vous pouvez utiliser les instructions rattachées à SimpleXml.

# data[].attributes.attachment_link.title
| Exporter la structure d'une base Mysql en XML

# data[].attributes.attachment_link.description
Pour éviter de recourir à un mapping object-relationnel par le biais d'une classe statique, vous pouvez utiliser le schéma XML produit par MySQL : mysqldump -X -u root -password=votre_mot_de_passe -databases votre_base_de_donnees -no-data=TRUE Sous Php 5.0 et supérieur, vous pouvez utiliser les instructions rattachées à SimpleXml.

# data[].attributes.headline
Un de mes meilleurs articles, consacré à la méthodologie que nous employons lors d'un audit d'une base de données

# data[].attributes.subject
Un de mes meilleurs articles, consacré à la méthodologie que nous employons lors d'un audit d'une base de données

# data[].attributes.attachment_link.readable_text
Cela faisait 6 années et demi que je n'avais pas fait d'audit de bases de données. La dernière fois, c'était pour le compte d'une SSII de l'Amienois qui basculait les données de son logiciel métier vers SQL Server. A l'issue de l'audit, la SSII créa un demi-poste consacré au tuning des bases de données et à la pose d'index bien sentis. Et puis, sur mes préconisations, elle recruta un chef de projet, aux compétences solides sur le modèle relationnel, chargé de refondre la base de données.

Les problèmes de performance des bases de données apparaissent, en général, avec l'augmentation de la volumétrie des bases, surtout quand les instances s'exécutent dans des environnements virtualisés. Le surdimensionnement des machines, s'il permet dans un 1er temps de voir venir, ne peut pas occulter bien longtemps la faiblesse de la mise en œuvre du modèle relationnel. Des compétences insuffisantes en SQL et sur la compréhension du fonctionnement d'un moteur de bases de données relationnelles finissent par transformer une Ferrari en une simple pétrolette !

L'environnement de la base de données

L'un des critères les plus importants pour le confort de fonctionnement d'une base de données relationnelle est la mémoire dont dispose la machine. Il faut également être en capacité d'évaluer en quoi des processus concurrents sont susceptibles d'impacter la performance du moteur de bases de données. Dans le cadre d'un serveur LAMP hébergeant des applications PHP, il ne peut pas y avoir d'optimisation du moteur de bases de données sans optimisation du fonctionnement du serveur Web Apache ou Nginx, par quelques directives bien senties concernant notamment la mise en cache des fichiers. Sans opcode, les calculs en PHP seront également beaucoup plus lents ! La mise en œuvre du PHP-FPM vous permettra, quant à elle, d'isoler et d'optimiser la configuration PHP de chacune des applications installées.

La qualité du modèle relationnel

Les éléments objectifs pour évaluer la qualité du modèle relationnel sont tout d'abord les règles de nommage utilisées. Nom de tables au pluriel ( contacts), préfixe au nom de tables ( t_contact), noms de champ identiques pour désigner des données différentes ( auto, id), répétition indicée du nom d'un champ ( contact1, contact2, etc) sont des éléments immédiats montrant la faible qualité du modèle relationnel. L'absence de clés primaires, de contraintes d'intégrité relationnelle, d' index uniques pour la gestion des doublons, de check constraints est un indicateur de la faible compétence - voire de l'incompétence - des équipes en charge de la mise en place du schéma de la base de données relationnelle.

La performance d'un moteur de bases de données relationnel

Pour l'essentiel, la performance des instances MySQL, MariaDB, PostgreSQL, SQL Server ou Oracle Database est intimement liée à la quantité de mémoire utilisée pour la mise en cache des blocs lus sur disque et accessoirement, selon les motorisations, des données issues de l'exécution des requêtes. L'accès aux données se fait par la lecture des blocs de pages d'index mis en cache. Il faut à tout prix, au niveau du SQL, éviter le SELECT *, l'usage du SELECT sans clause WHERE qui contribuent à épuiser le cache de manière rédhibitoire. Les index simples ou combinés doivent porter sur les champs utilisés dans les clauses WHERE. La pose d'index suppose la mise en place de plans de maintenance permettant leur réorganisation et leur reconstruction régulières. Ces plans permettent également de prendre en charge la défragmentation des blocs de données !

La politique de sauvegarde des données

L'une des erreurs assez communes des personnes en charge de l'infrastructure est de croire que la mise en œuvre d'un cluster synchrone est susceptible de résoudre tous les problèmes en matière de disponibilité. Le cluster n'est là que pour palier le crash d'une machine. En mode synchrone, sa mise en œuvre va propager toutes les erreurs de manipulation d'une instance vers l'autre. Le cluster n'empêchera jamais à un DBA, un utilisateur ou un développeur de faire un DROP, un TRUNCATE ou un DELETE. En matière de disponibilité, il est plus sûr d'utiliser les mécanismes de sauvegarde des journaux de transactions fournis par l'éditeur et de mettre en œuvre une réplication master-slave asynchrone.

# data[].attributes.attachment_link.title
Audit d'une base de données relationnelle

# data[].attributes.attachment_link.description
Cela faisait 6 années et demi que je n'avais pas fait d'audit de bases de données. La dernière fois, c'était pour le compte d'une SSII de l'Amienois qui basculait les données de son logiciel métier vers SQL Server. A l'issue de l'audit, la SSII créa un demi-poste consacré au tuning des bases de données et à la pose d'index bien sentis.

# data[].attributes.headline
OPTIMIZE TABLE, exécuté à partir d'un script PHP

# data[].attributes.subject
OPTIMIZE TABLE, exécuté à partir d'un script PHP

# data[].attributes.attachment_link.readable_text
Comme le PHP s'exécute en ligne de commande dès lors que vous avez installé le paquet php-cli sur votre serveur Linux, vous pourrez planifier ce script avec CRON pour réduire la taille de vos bases MySql en mode de stockage MyIsam :

<?php
$db='votre_base_de_donnees';
$conn=mysql_connect('votre_machine', 'votre_utilisateur', 'votre_mot_de_passe');
$sql = " SHOW TABLES FROM $db ";
$req = mysql_query($sql);
mysql_select_db($db,$conn);
while ($enr = mysql_fetch_row($req))
{
mysql_query('OPTIMIZE TABLE '.$row[0]);
}
mysql_free_result($req);
unset($req);
mysql_close($conn);
unset($conn);
?>

# data[].attributes.attachment_link.title
| Php : réduire la taille de vos bases MySql MyIsam

# data[].attributes.attachment_link.description
Comme le PHP s'exécute en ligne de commande dès lors que vous avez installé le paquet php-cli sur votre serveur Linux, vous pourrez planifier ce script avec CRON pour réduire la taille de vos bases MySql en mode de stockage MyIsam :

# data[].attributes.headline
Oracle recommande de passer à MySQL WorkBench, un GUI de l'éditeur

# data[].attributes.subject
Oracle recommande de passer à MySQL WorkBench, un GUI de l'éditeur

# data[].attributes.attachment_link.readable_text
Les outils d'administration graphiques, MySQL GUI tools Bundle (MySQL Administrator, MySQL Query Browser, MySQL Migration Toolkit), ne sont désormais plus maintenus. Oracle recommande désormais d'évoluer vers l'outil qui était naguère réservé à la modélisation, MySQL Workbench. Seule la version Beta intègre les outils d'administration et de " requêtage " qui étaient inclus dans MySQL GUI Tools. Elle est disponible sous forme de paquets RPM et DEB respectivement pour Fedora 12 et Ubuntu 9.10.

Alternatives propriétaires

Parmi les alternatives à MySql GUI Tools, signalons en premier lieu Navicat qui propose une version gratuite, limitée à un usage non commercial. En langue anglaise, la version gratuite permet de réaliser des " dumps " et dispose de son propre " requêteur ". Elle offre une interface de gestion des utilisateurs et des droits. La version Linux de Navicat n'est pas bien " fraîche " !

EMS SQL Manager for MySQL est bien plus " riche " que son concurrent. Son interface reste en langue anglaise : backup, restore, maintenance des tables au niveau de la base ! L'éditeur ne propose aucune version pour Linux. Toad for MySQL, quant à lui, est en tout point similaire à EMS SQL Manager for MySQL. Il propose, de surcroît, un outil de modélisation ! Vous devrez vous enregistrer afin de pouvoir le télécharger. C'est sans doute l'outil le plus complet et, aussi, le plus lourd pour administrer vos bases MySQL. Comme les autres, il s'agit d'un produit " propriétaire ". Pas de version Linux !

Et l'Open Source

Dans l'offre de produits Open Source, citons enfin Sqlyog. Sur le plan fonctionnel, il reste très en dessous de Toad for MySQL ! Pas de version Linux. Dans un autre registre, bien moins pratique, citons enfin PhpMyAdmin qui requiert l'installation et la configuration d'un serveur Web.

# data[].attributes.attachment_link.description
Les outils d'administration graphiques, MySQL GUI tools Bundle (MySQL Administrator, MySQL Query Browser, MySQL Migration Toolkit), ne sont désormais plus maintenus. Oracle recommande désormais d'évoluer vers l'outil qui était naguère réservé à la modélisation, MySQL Workbench. Seule la version Beta intègre les outils d'administration et de " requêtage " qui étaient inclus dans MySQL GUI Tools.

# data[].attributes.attachment_link.readable_text
Après avoir réinstallé le serveur qui héberge ce site et un autre beaucoup plus lu que celui-ci, j'ai pu constaté grâce à la commande htop que la charge du CPU du serveur atteignait très fréquemment 100%. Elle est, pour l'essentiel, liée à des montées en charge du serveur Apache. Certains de ces pics sont provoqués par le temps d'attente liée à l'exécution de requêtes Sql sur-numéraires et mal écrites.

Analyse des requêtes lentes (slow queries) sous MySql (hébergement dédié seulement)

Ces opérations ne sont réalisables que sur un serveur dédié de type dedibox, disposant d'un accès SSH. Après vous être connecté à votre serveur, créez le répertoire /var/log/mysql en donnant les droits à l'utilisateur mysql (sous Centos) :

mkdir /var/log/mysql
chown mysql:mysql /var/log/mysql -R

Editez le fichier /etc/my.cnf et ajoutez-y, dans la section [mysqld] les lignes suivantes :

log-slow-queries=/var/log/mysql/slow-queries.log
log-error=/var/log/mysql/error.log
long_query_time = 1

L'unité de la directive l ong_query_type est la seconde. Or une seconde pour une requête, ça commence à faire !!! L'installation de très nombreux plugins sous WordPress peut avoir des conséquences assez désastreuses, compte tenu de l'incompétence des développeurs en matière de SQL. Pour vous en rendre compte, il suffit de taper à partir de la ligne de commandes et de naviguer sur votre blog WordPress :
tail -f /var/log/mysql/slow-queries.log
Action n°1 : créez des index ! (pour tous les types d'hébergement)

Vous devez, au préalable, vous connecter à MySql : mysql -u root -p

Pour accélérer les accès à la base de vos billets, ajoutez un index sur la colonne post_status de la table wp_posts, la colonne autoload de la table wp_options, la colonne meta_value de la table wp_postmeta à l'aide de la commande :

CREATE INDEX idx_post_status ON wp_posts(post_status)
CREATE INDEX idx_meta_value ON wp_postmeta(meta_value);
CREATE INDEX idx_autoload ON wp_options(autoload);

Si vous utilisez les plugins Similar Posts et WP-Sticky, là-encore, vous pouvez ajouter un index :

CREATE INDEX idx_pid ON wp_similar_posts(pID);
CREATE INDEX idx_sticky_status ON wp_sticky(sticky_status);

Cette liste n'est hélas pas exhaustive.

Action n°2 : diminuez la quantité de tags (pour tous les types d'hébergement)

La gestion des tags (mots-clefs), utile au référencement, est sous WordPress consommatrice de ressources importantes. Vous devez impérativement en diminuer le nombre.

A partir de l'interface d'administration de WordPress, allez dans Articles-> Mots-clefs pour procéder à la suppression des catégories.

Action n°3 : suppression des révisions (pour tous les types d'hébergement)

Par défaut, WordPress consigne les révisions de vos articles. C'est autant de copies des originaux qui contribuent à augmenter la taille de votre base de posts, wp_posts. Pour effacer vos révisions :

DELETE FROM wp_posts WHERE post_type='revision' "

Vous pouvez désactiver les révisions en modifiant votre fichier wp-config.php :

define('WP_POST_REVISIONS',false);

Vous pouvez faire aussi le choix d'une requête que vous exécutez à l'aide du planificateur de tâches sous Linux, cron (paquet vixie-cron sous Centos), qui exécute un script php ou mysql, effaçant l'ensemble des versions de révision, à l'aide de la commande présentée ci-dessus.

# data[].attributes.attachment_link.title
| Performances WordPress : surveillez les requêtes MySql !

# data[].attributes.attachment_link.description
Après avoir réinstallé le serveur qui héberge ce site et un autre beaucoup plus lu que celui-ci, j'ai pu constaté grâce à la commande htop que la charge du CPU du serveur atteignait très fréquemment 100%. Elle est, pour l'essentiel, liée à des montées en charge du serveur Apache.