Différences entre les versions de « Installation et configuration de MySQL »

De Linux Server Wiki
Aller à la navigation Aller à la recherche
(Page créée avec « Ce howto a été écrit au départ pour debian etch puis a été adapté pour debian lenny. Il reste cependant valable la plupart du temps pour ces deux versions de debian. ... »)
 
 
(47 versions intermédiaires par le même utilisateur non affichées)
Ligne 1 : Ligne 1 :
Ce howto a été écrit au départ pour debian etch puis a été adapté pour debian lenny. Il reste cependant valable la plupart du temps pour ces deux versions de debian.
+
[[Category:serveur]]
 +
[[Category:debian]]
 +
=Installation et configuration=
  
=Installation et configuration=
+
Pour installer le serveur mariadb, tapez :
 +
<pre>aptitude install mariadb-server mariadb-client</pre>
 +
 
 +
Puis lancez
 +
<pre>mysql_secure_installation</pre>
 +
 
 +
Mariadb recommande d'éviter au maximum l'[https://mariadb.com/kb/en/configuring-swappiness/ utilisation du swap]. Définissez la ligne suivante par exemple dans <code>/etc/sysctl.d/local.conf</code> :
 +
<pre>vm.swappiness = 1</pre>
 +
 
 +
==Changement du datadir==
  
Pour installer mysql, tapez :
+
Stoppez le serveur :
<pre>aptitude install mysql-server mysql-client</pre>
+
<pre>/etc/init.d/mysql stop</pre>
  
 
Nous allons modifier la configuration de mysql afin de stocker les bases de données à un endroit plus propre. Pour cela, éditez le fichier <code>/etc/mysql/my.cnf</code>, recherchez la ligne <code>datadir</code> et remplacez la par :
 
Nous allons modifier la configuration de mysql afin de stocker les bases de données à un endroit plus propre. Pour cela, éditez le fichier <code>/etc/mysql/my.cnf</code>, recherchez la ligne <code>datadir</code> et remplacez la par :
Ligne 11 : Ligne 22 :
 
Copiez l'ancien répertoire de la base de donnée au nouvel emplacement et attribuez lui les bons privilèges :
 
Copiez l'ancien répertoire de la base de donnée au nouvel emplacement et attribuez lui les bons privilèges :
 
<pre>
 
<pre>
cp -R /var/lib/mysql/ /srv/
+
rsync -av /var/lib/mysql/ /srv/mysql/
chown -R  mysql /srv/mysql
 
chgrp -R  mysql /srv/mysql
 
 
</pre>
 
</pre>
  
Ligne 21 : Ligne 30 :
 
</pre>
 
</pre>
  
Si mysql refuse de démarrer, c'est probablement un problème de permission. Le dossier mysql et tous les sous dossiers présent dans <code>/srv/mysql</code> doivent appartenir à <code>mysql:mysql</code> et avoir les permissions <code>rwxr-x---</code>
+
Si mysql refuse de démarrer, c'est probablement un problème de permission.
<pre>chmod 750 /srv/mysql/</pre>
 
  
Essayez de relancer mysql.
+
=Servir des requêtes sur le réseau=
<pre>/etc/init.d/mysql start</pre>
 
  
Vous voudrez peut-être empêcher l'accès à MySQL par le réseau. Ajoutez la ligne suivante dans le fichier <code>/etc/mysql/my.cnf</code> :
+
Par défaut sous debian, mysql/mariadb n'écoute que sur localhost. Si vous souhaitez écouter sur une ip autre (attention aux problèmes de sécurité que cela peut poser !), éditez la ligne suivante dans le fichier <code>/etc/mysql/mariadb.conf.d/50-server.cnf</code> :
<pre>bind-address            = 127.0.0.1</pre>
+
<pre>bind-address            = 192.168.50.5</pre>
  
Cette directive remplace l'ancienne directive <code>skip-networking</code> de mysql.
+
=Le login root de MySQL=
  
=Le mot de passe root de MySQL=
+
Après avoir lancé <code>mysql_secure_installation</code> vous avez probablement du définir un mot-de-passe root et désactivé le login extérieur avec l'utilisateur mysql root.
  
Par défaut, il n'y a pas de mot-de-passe root de défini. Vous pouvez donc vous loguer sans problème sous mysql en root:
+
Par défaut sous Debian Buster, mariadb log l'utilisateur root avec le socket unix, sans mot-de-passe nécessaire :
<pre>mysql -u root -p</pre>
+
<pre>mysql -u root</pre>
  
Faites simplement enter lorsque le mot de passe vous sera demandé. Une fois au prompt <code>mysql></code>, vous pouvez définir le mot-de-passe root en tapant :
+
Le défaut de cette mécanique est qu'il va empêcher, par exemple, le login root avec phpmyadmin. Pour ré-activer le login root par mot-de-passe :
 
<pre>
 
<pre>
UPDATE mysql.user SET Password = PASSWORD('motdepasse') WHERE User = 'root';
+
mysql -u root
FLUSH PRIVILEGES;
+
use mysql;
 +
update user set plugin='' where user='root';
 +
flush privileges;
 
</pre>
 
</pre>
  
=Récupérer le mot-de-passe root perdu=
+
Désormais pour vous connecter à mysl vous devrez entrer un mot-de-passe :
 +
<pre>mysql -u root -p</pre>
  
Arrêtez votre serveur mysql :
+
Je vous recommande également d'entrer le mot-de-passe root que vous avez défini dans <code>/etc/mysql/debian.cnf</code> sinon certains scripts risquent de ne plus fonctionner. Anciennement, debian créait un utilisateur debian-sys-maint à cet usage, ce n'est plus le cas depuis debian 9 qui configuré désormais l'utilisateur root à cet usage dans <code>/etc/mysql/debian.cnf</code> et parfois dans <code>/etc/dbconfig-common/*</code>
<pre>/etc/init.d/mysql stop</pre>
+
 
 +
Éventuellement, vous pouvez stocker le mot-de-passe root comme ceci dans <code>/root/.my.cnf</code> (veillez bien à ce que ce fichier ne soit lisible que par l'utilisateur unix root) :
 +
<pre>
 +
[client]
 +
user=root
 +
password=
 +
socket=/var/run/mysqld/mysqld.sock
 +
</pre>
  
Démarrez mysql de cette manière :
+
Si vous souhaitez revenir à la configuration "passwordless" pour l'utilisateur root, tapez dans la console mysql :
<pre>mysqld --skip-grant-tables --skip-networking & </pre>
+
<pre>use mysql; update user set plugin='unix_socket' where user='root'; flush privileges;</pre>
  
Cela permet de démarrer le serveur mysql sans qu'il prenne en compte les droits (pour modifier le mot de passe) et sans qu'il n'écoute sur le réseau (histoire d'être certain que personne ne s'y connecte en root pendant que vous changez le mot de passe).
+
=Créer un utilisateur et lui attribuer les droits sur une base de donnée=
  
Loguez vous sous mysql :
+
Nous allons voir comment créer une base de donnée nommée plouf, puis un utilisateur plop ayant tous les droits sur cette base.
<pre>mysql</pre>
 
  
Et tapez la requête sql suivante pour changer votre mot de passe root :
+
Pour commencer, créer la base de donnée <code>plouf</code> :
 
<pre>
 
<pre>
UPDATE mysql.user SET Password = PASSWORD('motdepasse') WHERE User = 'root';
+
mysql -u root -p
FLUSH PRIVILEGES;
+
mysql> create database plouf;
 +
GRANT ALL PRIVILEGES ON plouf.* TO "plop"@"localhost" IDENTIFIED BY 'password';
 
</pre>
 
</pre>
  
Délogez vous de la console mysql en tapant <code>quit;</code>. Vous pouvez maintenant relancer mysql normalement :
+
Vous pourrez désormais utiliser cette base avec l'utilisateur plop.
 
 
<pre>/etc/init.d/mysql restart</pre>
 
  
 
=Sauvegarder et restaurer une base de donnée MySQL=
 
=Sauvegarder et restaurer une base de donnée MySQL=
 
==Sauvegarde==
 
==Sauvegarde==
  
Pour sauvegarder une base entière :
+
Pour sauvegarder une base entière afin de la restaurer sur le même serveur mysql :
 
<pre>mysqldump -u root -p --opt nom_de_la_base > sauvegarde.sql</pre>
 
<pre>mysqldump -u root -p --opt nom_de_la_base > sauvegarde.sql</pre>
 +
 +
Pour sauvegarder une base entière afin de la restaurer sur un autre serveur mysql (ajout d'un champ créant la base de donnée):
 +
<pre>mysqldump -u root -p --databases --opt nom_de_la_base > sauvegarde.sql</pre>
  
 
Pour sauvegarder uniquement une table d'une base :
 
Pour sauvegarder uniquement une table d'une base :
 
<pre>mysqldump -u root -p --opt nom__de_la_base nom__de_la_table > sauvegarde.sql</pre>
 
<pre>mysqldump -u root -p --opt nom__de_la_base nom__de_la_table > sauvegarde.sql</pre>
 +
 +
<br>
 +
L'option <code>--opt</code> active les flags <code>--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset</code>
  
 
==Restauration==
 
==Restauration==
Ligne 85 : Ligne 106 :
 
Commençons par installer phpmyadmin :
 
Commençons par installer phpmyadmin :
 
<pre>aptitude install phpmyadmin</pre>
 
<pre>aptitude install phpmyadmin</pre>
 +
 +
Lorsque l'installeur vous demandera s'il faut configurer automatiquement phpmyadmin sur un serveur web, ne cochez aucune case et validez.
 +
 +
Si par erreur vous avez validé l'autoconfiguration pour apache, vous pouvez toujours désactiver la configuration ajoutée avec
 +
<pre>a2disconf phpmyadmin</pre>
 +
 +
L'installeur vous demandera ensuite s'il doit configurer la base de donnée de phpmyadmin avec dbconfig-common. Choisissez oui. A l'écran suivant, vous devrez entrer le mot-de-passe de votre utilisateur mysql root.
 +
Enfin, un invite vous demandera de choisir un mot-de-passe pour l'utilisateur phpmyadmin puis de le confirmer.
  
 
Par défaut, phpmyadmin s'installe dans <code>/usr/share/phpmyadmin/</code>
 
Par défaut, phpmyadmin s'installe dans <code>/usr/share/phpmyadmin/</code>
Ligne 90 : Ligne 119 :
 
<pre>Include /etc/phpmyadmin/apache.conf</pre>
 
<pre>Include /etc/phpmyadmin/apache.conf</pre>
  
Si vous avez sécurisé votre installation php avec <code>open_basedir</code> il faut préciser les arguments suivants pour <code>php_admin_value open_basedir</code> dans la configuration de la vhost (<code>/etc/apache2/sites/admin.csnu.org.conf</code> dans mon cas) :
+
Si vous avez sécurisé votre installation php avec <code>open_basedir</code> il faut préciser les arguments suivants pour <code>php_admin_value open_basedir</code> dans la configuration de la vhost (<code>/etc/apache2/sites/admin.domain.tld.conf</code> dans mon cas) :
<pre>php_admin_value open_basedir /srv/http/csnu.org/admin.csnu.org/:/usr/share/:/etc/phpmyadmin/:/var/lib/phpmyadmin/</pre>
+
<pre>php_admin_value open_basedir /srv/http/domain.tld/admin.domain.tld/:/usr/share/phpmyadmin/:/etc/phpmyadmin/:/var/lib/phpmyadmin/</pre>
 +
 
 +
Et enfin, si vous utilisez à la fois les modules php et suphp de apache, activez php pour phpmyadmin en ajoutant les lignes suivantes dans <code>/etc/apache2/mods-enabled/php5.conf :
 +
<pre>
 +
  <Directory /usr/share/phpmyadmin>
 +
    php_flag engine on
 +
  </Directory>
 +
</pre>
  
 
Désormais, vous pouvez accéder à phpmyadmin par l'url correspondant au site pour lequel vous avez créé le lien. Dans mon cas :
 
Désormais, vous pouvez accéder à phpmyadmin par l'url correspondant au site pour lequel vous avez créé le lien. Dans mon cas :
<pre>http://admin.csnu.org/phpmyadmin/</pre>
+
<pre>http://admin.domain.tld/phpmyadmin/</pre>
 +
 
 +
Pour plus de sécurité, vous pouvez placer un fichier <code>.htaccess</code> dans <code>/usr/share/phpmyadmin/</code> afin de bloquer l'accès à phpmyadmin aux utilisateurs ne s'étant pas logué sur <code>http://admin.domain.tld/</code> :
 +
<pre>
 +
AuthType Digest
 +
AuthName "administration interface"
 +
AuthDigestProvider file
 +
AuthDigestDomain /
 +
AuthUserFile /srv/http/admin.domain.tld/.htpasswd
 +
AuthGroupFile /srv/http/admin.domain.tld.htgroup
 +
require group root user
 +
</pre>
  
 
=Optimiser les réglages de MySQL=
 
=Optimiser les réglages de MySQL=
 +
MySQLTuner est un petit script perl qui vous proposera des optimisations pour votre configuration MySQL en fonction des statistiques de votre utilisation.<pre>
 +
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
 +
chmod +x mysqltuner.pl
 +
./mysqltuner.pl
 +
</pre>
 +
 +
Vous pouvez aussi essayer  https://launchpad.net/mysql-tuning-primer
 +
 +
=Optimiser les tables=
 +
 +
<pre>mysqlcheck -o --all-databases</pre>
 +
 +
=Quelques commandes MySQL=
 +
 +
Je vais ici vous donner quelques requêtes mysql utiles ainsi qu'une courte description de leurs actions.
 +
 +
Donner la liste des bases de données :
 +
<pre>mysql> SHOW DATABASES;</pre>
 +
 +
se placer dans la base blah. Il est nécessaire de se placer de cette manière dans une base avant de pouvoir en modifier les tables, champs, ...
 +
<pre>mysql> use blah;</pre>
 +
 +
Donner la liste des tables de la base de donnée active :
 +
<pre>mysql> SHOW TABLES;</pre>
 +
 +
Renvoyer tous les champs de la table "table" :
 +
<pre>mysql> select * from table;</pre>
 +
 +
Renvoyer toute la colonne "column" de la table "table" :
 +
<pre>mysql> select column from table;</pre>
 +
 +
Renvoyer la colonne "column" de la table "table" où le champ "pom" vaut "blah" :
 +
<pre>mysql> select column from table where pom='blah'</pre>
 +
 +
Changer la valeur de la colonne "column" dans la table "table" à la ligne où "column2" vaut "blah" :
 +
<pre>mysql> UPDATE table SET column='gnu' WHERE column2='blah';</pre>
 +
 +
Vider la table "table"
 +
<pre>mysql> DELETE FROM table;</pre>
 +
 +
Supprimer la ligne où la colonne "column" vaut "blah" dans la table "table"
 +
<pre>mysql> DELETE FROM table WHERE column='blah'; </pre>
  
Téléchargez tuning-primer.sh puis tapez :
+
Supprimer la table "table" :
<pre>sh tuning-primer.sh</pre>
+
<pre>mysql> drop table table; </pre>
  
Le programme analysera votre configuration MySQL en fonction des logs et vous donnera quelques conseils au sujet de votre configuration.
+
Supprimer la base blah :
 +
<pre>mysql> drop database blah; </pre>

Version actuelle datée du 31 octobre 2021 à 13:45

1 Installation et configuration

Pour installer le serveur mariadb, tapez :

aptitude install mariadb-server mariadb-client

Puis lancez

mysql_secure_installation

Mariadb recommande d'éviter au maximum l'utilisation du swap. Définissez la ligne suivante par exemple dans /etc/sysctl.d/local.conf :

vm.swappiness = 1

1.1 Changement du datadir

Stoppez le serveur :

/etc/init.d/mysql stop

Nous allons modifier la configuration de mysql afin de stocker les bases de données à un endroit plus propre. Pour cela, éditez le fichier /etc/mysql/my.cnf, recherchez la ligne datadir et remplacez la par :

datadir /srv/mysql

Copiez l'ancien répertoire de la base de donnée au nouvel emplacement et attribuez lui les bons privilèges :

rsync -av /var/lib/mysql/ /srv/mysql/

Essayez de lancer mysql :

/etc/init.d/mysql start

Si mysql refuse de démarrer, c'est probablement un problème de permission.

2 Servir des requêtes sur le réseau

Par défaut sous debian, mysql/mariadb n'écoute que sur localhost. Si vous souhaitez écouter sur une ip autre (attention aux problèmes de sécurité que cela peut poser !), éditez la ligne suivante dans le fichier /etc/mysql/mariadb.conf.d/50-server.cnf :

bind-address            = 192.168.50.5

3 Le login root de MySQL

Après avoir lancé mysql_secure_installation vous avez probablement du définir un mot-de-passe root et désactivé le login extérieur avec l'utilisateur mysql root.

Par défaut sous Debian Buster, mariadb log l'utilisateur root avec le socket unix, sans mot-de-passe nécessaire :

mysql -u root

Le défaut de cette mécanique est qu'il va empêcher, par exemple, le login root avec phpmyadmin. Pour ré-activer le login root par mot-de-passe :

mysql -u root
use mysql;
update user set plugin='' where user='root';
flush privileges;

Désormais pour vous connecter à mysl vous devrez entrer un mot-de-passe :

mysql -u root -p

Je vous recommande également d'entrer le mot-de-passe root que vous avez défini dans /etc/mysql/debian.cnf sinon certains scripts risquent de ne plus fonctionner. Anciennement, debian créait un utilisateur debian-sys-maint à cet usage, ce n'est plus le cas depuis debian 9 qui configuré désormais l'utilisateur root à cet usage dans /etc/mysql/debian.cnf et parfois dans /etc/dbconfig-common/*

Éventuellement, vous pouvez stocker le mot-de-passe root comme ceci dans /root/.my.cnf (veillez bien à ce que ce fichier ne soit lisible que par l'utilisateur unix root) :

[client]
user=root
password=
socket=/var/run/mysqld/mysqld.sock

Si vous souhaitez revenir à la configuration "passwordless" pour l'utilisateur root, tapez dans la console mysql :

use mysql; update user set plugin='unix_socket' where user='root'; flush privileges;

4 Créer un utilisateur et lui attribuer les droits sur une base de donnée

Nous allons voir comment créer une base de donnée nommée plouf, puis un utilisateur plop ayant tous les droits sur cette base.

Pour commencer, créer la base de donnée plouf :

mysql -u root -p
mysql> create database plouf;
GRANT ALL PRIVILEGES ON plouf.* TO "plop"@"localhost" IDENTIFIED BY 'password';

Vous pourrez désormais utiliser cette base avec l'utilisateur plop.

5 Sauvegarder et restaurer une base de donnée MySQL

5.1 Sauvegarde

Pour sauvegarder une base entière afin de la restaurer sur le même serveur mysql :

mysqldump -u root -p --opt nom_de_la_base > sauvegarde.sql

Pour sauvegarder une base entière afin de la restaurer sur un autre serveur mysql (ajout d'un champ créant la base de donnée):

mysqldump -u root -p --databases --opt nom_de_la_base > sauvegarde.sql

Pour sauvegarder uniquement une table d'une base :

mysqldump -u root -p --opt nom__de_la_base nom__de_la_table > sauvegarde.sql


L'option --opt active les flags --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset

5.2 Restauration

Pour restaurer une table d'une base ou une base complète :

mysql -u root -p ma_base < sauvegarde.sql

6 Gérer facilement ses bases de données avec PhpMyAdmin

Phpmyadmin permet de gérer ses bases de données MySQL très facilement grâce à une interface web. Commençons par installer phpmyadmin :

aptitude install phpmyadmin

Lorsque l'installeur vous demandera s'il faut configurer automatiquement phpmyadmin sur un serveur web, ne cochez aucune case et validez.

Si par erreur vous avez validé l'autoconfiguration pour apache, vous pouvez toujours désactiver la configuration ajoutée avec

a2disconf phpmyadmin

L'installeur vous demandera ensuite s'il doit configurer la base de donnée de phpmyadmin avec dbconfig-common. Choisissez oui. A l'écran suivant, vous devrez entrer le mot-de-passe de votre utilisateur mysql root. Enfin, un invite vous demandera de choisir un mot-de-passe pour l'utilisateur phpmyadmin puis de le confirmer.

Par défaut, phpmyadmin s'installe dans /usr/share/phpmyadmin/ Bien-sur, vous voudrez probablement que phpmyadmin soit accessible grâce à l'un de vos site internet. Il suffit d'ajouter la ligne suivante dans le fichier de configuration de la vhost apache correspondante :

Include /etc/phpmyadmin/apache.conf

Si vous avez sécurisé votre installation php avec open_basedir il faut préciser les arguments suivants pour php_admin_value open_basedir dans la configuration de la vhost (/etc/apache2/sites/admin.domain.tld.conf dans mon cas) :

php_admin_value open_basedir /srv/http/domain.tld/admin.domain.tld/:/usr/share/phpmyadmin/:/etc/phpmyadmin/:/var/lib/phpmyadmin/

Et enfin, si vous utilisez à la fois les modules php et suphp de apache, activez php pour phpmyadmin en ajoutant les lignes suivantes dans /etc/apache2/mods-enabled/php5.conf :

  <Directory /usr/share/phpmyadmin>
    php_flag engine on
  </Directory>

Désormais, vous pouvez accéder à phpmyadmin par l'url correspondant au site pour lequel vous avez créé le lien. Dans mon cas :

http://admin.domain.tld/phpmyadmin/

Pour plus de sécurité, vous pouvez placer un fichier .htaccess dans /usr/share/phpmyadmin/ afin de bloquer l'accès à phpmyadmin aux utilisateurs ne s'étant pas logué sur http://admin.domain.tld/ :

AuthType Digest
AuthName "administration interface"
AuthDigestProvider file
AuthDigestDomain /
AuthUserFile /srv/http/admin.domain.tld/.htpasswd
AuthGroupFile /srv/http/admin.domain.tld.htgroup
require group root user

7 Optimiser les réglages de MySQL

MySQLTuner est un petit script perl qui vous proposera des optimisations pour votre configuration MySQL en fonction des statistiques de votre utilisation.

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl 
./mysqltuner.pl 

Vous pouvez aussi essayer https://launchpad.net/mysql-tuning-primer

8 Optimiser les tables

mysqlcheck -o --all-databases

9 Quelques commandes MySQL

Je vais ici vous donner quelques requêtes mysql utiles ainsi qu'une courte description de leurs actions.

Donner la liste des bases de données :

mysql> SHOW DATABASES;

se placer dans la base blah. Il est nécessaire de se placer de cette manière dans une base avant de pouvoir en modifier les tables, champs, ...

mysql> use blah;

Donner la liste des tables de la base de donnée active :

mysql> SHOW TABLES;

Renvoyer tous les champs de la table "table" :

mysql> select * from table;

Renvoyer toute la colonne "column" de la table "table" :

mysql> select column from table;

Renvoyer la colonne "column" de la table "table" où le champ "pom" vaut "blah" :

mysql> select column from table where pom='blah'

Changer la valeur de la colonne "column" dans la table "table" à la ligne où "column2" vaut "blah" :

mysql> UPDATE table SET column='gnu' WHERE column2='blah';

Vider la table "table"

mysql> DELETE FROM table;

Supprimer la ligne où la colonne "column" vaut "blah" dans la table "table"

mysql> DELETE FROM table WHERE column='blah'; 

Supprimer la table "table" :

mysql> drop table table; 

Supprimer la base blah :

mysql> drop database blah;