MySQL

MySQL est un système de gestion de bases de données relationnelles (SGBDR) libre, gratuit, performant, très populaire, multi-threadé, multi-utilisateurs…

Installation

Installez, en ligne de commande, le paquet mysql-server.

L'installation de ce paquet doit se faire en utilisant dans un terminal la commande
sudo apt-get install mysql-server

En effet, en passant par le terminal, l'installation se déroule correctement et notamment vous demande le mot de passe mysql-server

Le support de l'extension PHP Data Objects PHP Data Objects (PDO) est installé par défaut avec php5-mysql lors de l'installation de php5.

Utilisation

Démarrage

Pour démarrer le serveur MySQL, tapez la commande suivante dans un terminal:

sudo service mysql start

Redémarrage

Pour redémarrer le serveur MySQL, tapez la commande suivante dans un terminal:

sudo service mysql restart

Arrêt

Pour arrêter le serveur MySQL, tapez la commande suivante dans un terminal:

sudo service mysql stop

Rechargement de la configuration

Pour que MySQL prenne en compte les modifications de sa configuration, commande suivante dans un terminal:

sudo service mysql reload

Forcer la prise en compte de la nouvelle configuration

Pour forcer MySQL à recharger ses fichiers de configuration, commande suivante dans un terminal:

sudo service mysql force-reload

Configuration

Lancer la console MySQL

Bogue possible rencontré avec le client texte de MySQL : la touche [Suppr] du clavier insère le tilde ~ (au lieu de supprimer).

Pour palier ce bogue, installez le programme rlwrap (pour en savoir plus : man rlwrap), installez le paquet rlwrap et ajoutez celui-ci avant la commande mysql, exemple :

rlwrap -a mysql -uroot -pvotremotdepasse votrebase
  • Si vous n'avez pas défini de mot de passe (déconseillé) :
    mysql -u root
  • Si vous avez défini un mot de passe :
    mysql -u root -p

    et tapez votre mot de passe.

Vous arriverez alors sur un prompt du type :

mysql>

Vous pouvez alors taper vos requêtes MySQL. N'oubliez pas le point-virgule à la fin de la requête. Si vous l'oubliez le prompt passe à la ligne.

mysql> select * from
    ->

Ce passage vous indique que la requête n'est pas terminée. Vous pouvez entrer vos requêtes de cette façon sur plusieurs lignes et enfin la clôturer avec le « ; ».

Sélectionner la base de données à utiliser

Soit directement lorsque vous lancez le client MySQL en ligne de commande:

mysql -u root -D votre_base

Soit une fois connecté à MySQL en tapant la commande suivante dans un terminal:

mysql> use votre_base

Ajouter ou changer le mot de passe de root

Depuis mysql 5.7 le champ PASSWORD de la description de la base de donnée a été changé en authentication_string. À partir de cette version, il faut donc remplacer le mot password dans les instructions ci-dessous par authentication_string.

Pour changer le mot de passe de l'utilisateur root (qui n'est pas le même que l'utilisateur root du système) :

Si vous êtes déjà connecté dans mysql :

SET password FOR "root"@"localhost" = password('Nouveau_mot_de_passe');

Sinon depuis un terminal :

sudo mysqladmin -u root password Nouveau_mot_de_passe -p Ancien_mot_de_passe

Vous pourrez avoir besoin de mettre votre mot de passe entre guillemet si celui ci comporte des caractères spéciaux.

Lorsque l'utilisateur root ne possède aucun mot de passe, utiliser cette commande :

sudo mysqladmin -u root password Nouveau_mot_de_passe -p ""

Redéfinir de force le mot de passe de root

Méthode 1

Une méthode simple :

sudo service mysql reset-password

Méthode 2

Une autre méthode simple qui fonctionne sur Ubuntu versions 8.04 & + :

sudo dpkg-reconfigure mysql-server-5.5

Depuis Ubuntu 14.04 (et peut-etre même avant) c'est le paquet mysql-server-5.7 qui est installé. La commande est donc :

sudo dpkg-reconfigure mysql-server-5.7

Méthode 3

Une méthode plus compliquée :

Si vous avez perdu votre mot de passe root pour MySQL, suivez ces quelques étapes :

On stoppe le serveur MySQL :

sudo  service mysql stop

On se connecte au serveur sans tenir compte des privilèges utilisateurs :

sudo /usr/bin/mysqld_safe --skip-grant-tables &
mysql -h localhost

Nous allons utiliser la table contenant les paramètres de MySQL :

USE mysql

On met en place le nouveau mot de passe :

UPDATE `user`
SET    password = password('<votre_mot_de_passe>')
WHERE  USER = 'root' AND host = 'localhost';

(Placez le nom de la table `user` entre quotes (AltGr + 7) et remplacez <votre_mot_de_passe> par le mot de passe de votre choix)

À partir de mysql 5.7 cette instruction deviendra :
UPDATE mysql.user
SET authentication_string=PASSWORD('NouveauMdP')
WHERE user='root' AND host='localhost';

On quitte le prompt de MySQL :

quit

On stoppe totalement mysqld & mysqld_safe :

sudo mysqladmin shutdown

On redémarre le serveur MySQL en utilisation normale :

sudo service mysql start

Utiliser un autre numéro de port

Pour activer la connexion à MySQL via le port 3306, activez l’accès distant en commentant la ligne suivante dans le fichier /etc/mysql/mysql.conf.d/mysqld.cnf ou [/etc/mysql/mysql.conf.d/mysqld.cnf avec la version 16 d'Ubuntu] (en ajoutant # au début de la ligne) :

skip-networking

ou

bind-address = 127.0.0.1

Ensuite, si vous voulez changer de port, il suffit de modifier la ligne suivante du bloc [mysqld] pour y mettre le port voulu

port            = 3306

FIXME

Connexions entrantes

Par défaut, MySQL n'écoute que les connexions venant de localhost (127.0.0.1), c'est-à-dire du système sur lequel il est installé.

Pour pouvoir accéder à MySQL depuis un autre ordinateur, ajoutez la ligne suivante au fichier /etc/mysql/my.cnf dans la section « [mysqld] » :

bind-address = x.x.x.x

où x.x.x.x est l'adresse ip de l'interface sur laquelle MySQL doit écouter. Cela permet de n'écouter que sur une ip privée si vous avez plusieurs interfaces réseaux.

Pour forcer mysql à écouter sur toutes les interfaces réseau, il suffit de commenter la ligne "bind-address = 127.0.0.1" (ou écrire bind-address = 0.0.0.0) Cela peut être dangereux si votre serveur est connecté à Internet directement.

Redémarrez le service après avoir modifié le fichier de configuration :

sudo /etc/init.d/mysql restart

Pour créer un utilisateur mysql qui est autorisé à se connecter à distance :

se connecter sur mysql en root :

mysql -u root -p

une fois connecté :

grant all privileges on *.* to 'remote_user'@'123.123.123.123' identified by 'user_password';

le *.* représente l'accès à l'ensemble des bases de données, si vous voulez autoriser l'accès de l'utilisateur à une seule base de données, remplacez *.* par le nom de la base de données.

le remote_user est le nom de l'utilisateur qui sera créé.

le 123.123.123.123 représente l'ip à partir de laquelle nous pourrons nous connecter à distance.

Pour autoriser l'accès venant de toutes machines (limité par l'authentification), remplacer l'ip par le symbole '%'.

Pour attribuer un mot de passe à l'utilisateur, changer user_password par le nouveau mot de passe..

Pour rendre ces privilèges effectifs :

flush privileges;

Configuration en UTF-8

Il suffit d'ajouter les deux lignes suivantes à la fin de la section « [mysqld] » du fichier /etc/mysql/my.cnf :

character-set-server=utf8
skip-character-set-client-handshake

NB: en 12.04 remplacez character-set-server=utf8 par character_set_server=utf8

puis de relancer le serveur mysql :

sudo service mysql restart

Vous pouvez vérifier le résultat une fois loggé dans mysql grâce à la commande suivante :

SHOW VARIABLES LIKE 'char%';

Résultat :

mysqld> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       | 
| character_set_connection | utf8                       | 
| character_set_database   | utf8                       | 
| character_set_filesystem | binary                     | 
| character_set_results    | utf8                       | 
| character_set_server     | utf8                       | 
| character_set_system     | utf8                       | 
| character_sets_dir       | /usr/share/mysqld/charsets/ | 
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

FIXME

Quelques requêtes SQL

Toutes ces commandes sont à entrer dans le prompt de MySQL une fois connecté au serveur. (Voir comment lancer la console MySQL ci dessus.)

Base de donnée

Utiliser une base de données existante

USE nom_de_la_base;

Créer une base de données

CREATE DATABASE nom_de_la_base;

Liste des bases de données

SHOW DATABASES ;

Supprimer une base de données

DROP DATABASE nom_de_la_base;

Importer une base de données située dans le répertoire courant (base de donnée exportée en .sql)

SOURCE fichier.sql;

Tables

Liste des tables de la base de données active

SHOW TABLES;

Structure d'une table

DESCRIBE nom_table;

Renommer une table

ALTER TABLE nom_table RENAME AS nouveau_nom;

Pour la syntaxe des requêtes SQL, reportez-vous à la documentation MySQL.

Utilisateurs

Créer un utilisateur

CREATE USER "nom_utilisateur"@"localhost";

Définir un mot de passe

SET password FOR "nom_utilisateur"@"localhost" = password('mot_de_passe');

Supprimer un utilisateur

DROP USER "nom_utilisateur"@"localhost";

Lister les utilisateurs

SELECT USER FROM mysql.user;

Privilèges

Donner tous les droits sur une base de données à un utilisateur précis

GRANT ALL ON nom_base.* TO "nom_utilisateur"@"localhost";

Supprimer les droits sur une base de données à un utilisateur précis

REVOKE ALL privileges ON nom_base.* FROM "nom_utilisateur"@"localhost";

Autres opérations

Qt4 avec Mysql:

Installer la librairie pour mysql afin de l'utiliser sous QT :

sudo apt-get install libqt4-sql-mysql

Sauvegarde et importation

Pour exporter la base de donnée « maBase », utilisez la commande :

mysqldump -u root -p maBase > maBase_backup.sql

Ceci fera l'export dans un fichier « maBase_backup.sql ».

Pour exporter une table (par exemple ma_table )appartenant à une base des données (par exemple maBase) :

mysqldump -u root -p maBase ma_table > ma_table.sql

Ainsi nous aurons une exportation seulement de la table ma_table sans toucher à d'autres tables de la base des données

Pour importer une base de données ou une sauvegarde sur « maBase », utilisez la commande :

mysql -u root -p maBase < maBase_backup.sql

Puis tapez votre mot de passe.

Si vous souhaitez mettre en place une solution de sauvegarde automatique de bases de données à intervalle régulier, rendez-vous sur le tutoriel traitant de la sauvegarde automatique de bases de données via cron.

D'autres commandes telles que la suppression des tables, de la base, l'ajout des données dans la base des données, etc. peuvent être trouvées sur le site de documentation de MySQL.

Restauration à partir du dump du datadir

Ne fonctionne que pour les tables au format MyISAM.

Si vous n'avez pas pu faire un backup pour une raison quelconque (par exemple, vous récupérez un disque dur dont le système est mort), on peut sauvegarder le répertoire « /var/lib/mysql/ » dans lequel chacune des bases est enregistrée dans un répertoire portant son nom.

Pour restaurer vos bases une par une, suivez la procédure ci-dessous (après avoir d'abord installé une version de mysql-server).

Créer une base de données du même nom que celle que vous voulez restaurer :

echo "CREATE DATABASE MaBaseDeDonnee" | mysql -u root -h localhost -p

Copier les fichiers du dump de la base de données (les *.FRM, *.MYD, *.MYI, *.opt), dans le dossier que MySQL a créé pour votre base de données :

cd ~/MonDossierContenantLeDump/MaBaseDeDonnee
sudo cp * /var/lib/mysql/MaBaseDeDonnee

Changer les droits sur les fichiers copiés :

sudo chmod -R ug+rw /var/lib/mysql/MaBaseDeDonnee/
sudo chown -R mysql:mysql /var/lib/mysql/MaBaseDeDonnee/

Voilà, votre base doit correctement fonctionner à présent.

Compacter l'espace utilisé par ibdata1

Mysql stocke ses fichiers dans le dossier /var/lib/mysql. Il arrive avec le temps et l'utilisation que le fichier ibdata1 occupe un espace disque très important qui ne sera pas récupéré même en supprimant toutes les bases.

Pistes pour comprendre et corriger ce problème :

Déplacer ses bases de données MySQL

Attention !! lors d'une migration ubuntu14.04 → 16.04 ne lancez pas la migration sans avoir remis mysql en état d'origine (/var/lib/mysql). Faites les opérations inverses décrites ci-dessous pour remettre vos données dans /var/lib/mysql.

L'emplacement des données de MySQL est indiqué dans la variable datadir qui se trouve dans le fichier my.cnf. Toutefois, il peut exister plusieurs fichiers my.cnf :

$ mysqld --verbose --help | head -n13 | tail -n2
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 

Éventuellement ce fichier contient des directives !includedir. Il faut alors chercher dans ces répertoires le fichier qui contient datadir.

Dans la suite, nous allons déplacer les données dans /home/mysql.

Tout d'abord on arrête le service :

$ sudo service mysql stop

On édite le fichier qui contient datadir et on met la nouvelle valeur :

datadir		= /home/mysql

On suppose que l'ancienne valeur de datadir était /var/lib/mysql. On déplace les données :

$ sudo mv /var/lib/mysql /home

Malheureusement cela ne suffit pas. Le premier problème vient du fichier/usr/share/mysql/mysql-systemd-start :

$ head  /usr/share/mysql/mysql-systemd-start -n25 | tail -n16
sanity () {
  if [ ! -r /etc/mysql/my.cnf ]; then
    echo "MySQL configuration not found at /etc/mysql/my.cnf. Please create one."
    exit 1
  fi

  if [ ! -d /var/lib/mysql ] && [ ! -L /var/lib/mysql ]; then
    echo "MySQL data dir not found at /var/lib/mysql. Please create one."
    exit 1
  fi

  if [ ! -d /var/lib/mysql/mysql ] && [ ! -L /var/lib/mysql/mysql ]; then
    echo "MySQL system database not found. Please run mysql_install_db tool."
    exit 1
  fi
}

Il faut donc créer le répertoire /var/lib/mysql/mysql avec les bons droits :

$ sudo mkdir -p /var/lib/mysql/mysql
$ sudo chown -R mysql:mysql /var/lib/mysql
$ sudo chmod -R 700 /var/lib/mysql

Il faut maintenant configurer AppArmor. D'après ce qui suit, nous devons modifier le fichier local/usr.sbin.mysqld :

$ tail /etc/apparmor.d/usr.sbin.mysqld -n3
  # Site-specific additions and overrides. See local/README for details.
  #include <local/usr.sbin.mysqld>

Modifier le fichier /etc/apparmor.d/local/usr.sbin.mysqld en ajoutant :

# Allow data dir access
  /home/mysql/ r,
  /home/mysql/** rwk,

Rechargez :

sudo service apparmor reload

Redémarrez le service :

sudo service mysql start

Vérifiez le changement :

$ mysql -u root -p -e 'select @@datadir'
Enter password: 
+--------------+
| @@datadir    |
+--------------+
| /home/mysql/ |
+--------------+

Transférer une base de données entre deux serveurs via SSH

Préambule

Dans le cadre d'une infra sans serveur spécifique de base de données, et que l'on souhaite partager une base commune.

Mise en place

Dans un premier temps il faut remplir deux conditions :

  • que l'utilisateur de la base de données soit reconnu par le second serveur
  • que l'utilisateur de la base de données soit reconnu par les deux serveurs MySql.

Pour une reconnaissance utilisateur d'un serveur à un autre : Authentification par clés SSH (sans passphrase)

Pour que MySql reconnaisse l'utilisateur sans devoir saisir son mot de passe à chaque commande (dans le cas d'un tâche CRON par exemple), il faut ajouter un fichier dans le répertoire personnel de cet utilisateur et ce sur chaque serveur. Éditer/créer le fichier ~/.my.cnf

[mysql]
user=nom_utilisateur
password=mot_de_passe_utilisateur

[mysqldump]
user=nom_utilisateur
password=mot_de_passe_utilisateur

Protéger le :

chmod 600 ~/.my.cnf

Transférer depuis le serveur A vers le B :

mysqldump nom_bdd_serveur_A | ssh utilisateur_serveur_B@ip_serveur_B mysql nom_bbd_serveur_B

Exemple de commande en tache CRON :

10 0 * * * mysqldump nom_bdd_serveur_A | ssh utilisateur_serveur_B@ip_serveur_B mysql nom_bbd_serveur_B >/dev/null  #Mise à jour BDD

Administrer MySQL via une une interface Web

Il est possible d’administrer un serveur MySQL via une interface Web avec phpMyAdmin. D'autres outils sont également disponibles et vous en trouverez une liste non exhaustive sur la page Outils MySQL.

Liens


Contributeurs : _Enchained, Hugo, Oscar Nsarhaza, Ner0lph, david96, Gounlaf, McPeter.Rewiiz, ernest-le-voyage

  • mysql.txt
  • Dernière modification: Le 06/05/2018, 01:17
  • par 82.230.119.244