Outils pour utilisateurs

Outils du site


informatique:linux:mysql

Ceci est une ancienne révision du document !


MySql

:!: Ce tutoriel aborde uniquement l'administration de MySQL, pour les requêtes SQL voir : sql

Configuration

Pour que MySQL travaille avec un encodage UTF-8, insérer les lignes suivantes dans my.cnf dans la section [mysqld] :

character-set-server=utf8
default-collation=utf8_unicode_ci

Pour que MySQL travaille uniquement en minuscule, insérer les lignes suivantes dans my.cnf dans la section [mysqld] :

lower_case_table_names=1

Pour que l'importation de données soit fait en UTF-8, insérer les lignes suivantes dans my.cnf dans la section [mysql] :

default-character-set=utf8

Utilisation

Lancement du client avec authentification :

mysql --user=user_name --password=your_password db_name

ou bien

mysql -u root -p'your_password db_name' db_name

SHOW

Pour obtenir par exemple de l'aide sur SHOW :

help SHOW;

Pour connaître les variables de mysql:

SHOW VARIABLES;

Pour lister les bases de données :

SHOW databases;

Pour voir les tables de la base de données :

SHOW tables;

Pour montrer les index de la table users :

SHOW INDEX FROM users;

Pour afficher le statut des tables d'une base :

use my_database
show table status;

USE

Pour sélectionner par exemple la base de données dbperso

use dbperso;

DESCRIBE

Pour voir la définition des champs de la table users :

describe users;

ou

desc users;

GRANT

Ajouter un utilisateur myuser sur la base dbperso :

GRANT CREATE,INSERT,DELETE,UPDATE,SELECT on dbperso.* to 'myuser'@'localhost' IDENTIFIED BY '<mypassword>';

ou pour tous les droits sur la base :

GRANT ALL PRIVILEGES on dbperso.* to 'myuser'@'localhost' IDENTIFIED BY '<mypassword>';

Pour autoriser l'utilisateur à donner en plus des droits à un autre utilisation sur la base :

GRANT ALL PRIVILEGES on dbperso.* to 'myuser'@'localhost' IDENTIFIED BY '<mypassword>' WITH GRANT OPTION;

Pour que les droits soient pris en compte lancer :

FLUSH PRIVILEGES;

REVOKE

Pour retirer les droits à un utilisateur :

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'myuser'@'localhost';

Remarque : ceci supprime pas l'utilisateur.

Batch

Pour executer une requêtes dans un batch :

Ecrire la requête dans un fichier temporaire :

echo "SELECT email FROM dbperso.users WHERE username='user45696';" > select.sql

Lancer la requête :

mysql < select.sql

:!: En cas de problème d'accent, insérer la ligne suivante en tête du script SQL pour que l'import se fasse avec un encodage en UTF-8

SET NAMES utf8;

Affichage :

email
user45696@domaine.com

Pour ne pas afficher le nom des colonnes :

mysql --skip-column-names < select.sql

Monitoring

Pour voir les process mysql :

mysqladmin processlist

Activer les log sur les requêtes SQL

Si l'on a des doutes sur des requêtes SQL clientes, il peut être très utile de logger les requetes. L'acivation se fait ainsi :

Editer le fichier /etc/my.cnf et ajouter le paramètre suivant :

set-variable = log

ou

set-variable = log=/var/log/mysql-queries.log

Redémarrer Mysql :

/etc/init.d/mysql restart

Pour vérifier que l'option est bien activée :

mysql
SHOW VARIABLES;

Note :!: : Si aucun chemin de fichier n'a été précisé pour la variable set-variable = log le fichier log se trouvera par défaut dans /var/lib/mysql/HOSTNAME.log.

Maintenance et réparation

Pour vérifier s'il y a des corruptions sur toutes les tables des bases de données :

mysqlcheck -u root -p --all-databases

Pour vérifier et corriger les problèmes sur toutes les bases et tables :

mysqlcheck -u root -p --all-databases --auto-repair

Pour réparer une table

mysql -u root -p
use database
repair table table_name;

Voir http://tutos.tangui.eu.org/15-reparer-une-table-innodb-corrompue-sous-mysql pour réparer des table en InnoDB.

Réinitialiser le mot de passe root

Pour réinitialiser il suffit d'arrêter MySQL et de le redémarrer manuellement en ajoutant l'option –skip-grant-tables :

Récuperer la commande lancé par le script d'init de MySQL :

ps aux |grep mysql

Exemple de résultat attendu :

/usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock

La ligne à récupérer est celle contenant toutes les options.

Arrêter le service MySQL :

/etc/init.d/mysql stop

Lancer la commande manuellement avec l'option –skip-grant-tables :

/usr/sbin/mysqld --skip-grant-tables --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock &

Lancer le client mysql et réinitialiser le mot de passe :

mysql
UPDATE mysql.user SET Password=PASSWORD('MyNewPassword') WHERE User='root';
quit

Arrêter MySQL manuellement :

kill $(cat /var/run/mysqld/mysqld.pid)

Démarrer MySQL :

/etc/init.d/mysql start

Sauvegarde

Pour sauvegarder toutes les bases :

./mysqldump -u root -A -p > /repertoire/svg_SQL/Toutes_les_bases.sql

Pour sauvegarder une base et insérer les DROP de base et de table :

mysqldump -u root -p ma_base --add-drop-database --add-drop-table -p > dump_ma_base.sql

Pour sauvegarder et compresser un export de base :

mysqldump -u root -p ma_base --add-drop-database --add-drop-table -p |gzip > dump_ma_base.sql.gz

Pour restaurer un fichier sql.gz :

zcat dump_ma_base.sql.gz | mysql -u root -p ma_base

Pour exporter certaines tables :

mysqldump -u root -p ma_base table1 table2 > dump_mes_tables.sql

SSL

Création des certificats pour MySQL

Se positionner sur le répertoire dans lequel vous souhaitez stocker les certificats :

Puis créer les certificats CA, serveur et client :

openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

openssl req -newkey rsa:1024 -days 1000 -nodes -keyout server-key.pem > server-req.pem
openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

openssl req -newkey rsa:1024 -days 1000 -nodes -keyout client-key.pem > client-req.pem
openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

Modifier le fichier my.cnf en ajoutant les chemins des certificats:

ssl-ca=/etc/mysql/certs/ca-cert.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem

Sur le client, il faut préciser le certificat client pour se connecter au serveur, par exemple :

mysql -h server.domain.com -u mysqluser -P 4020 --ssl-ca=/etc/mysql/certs/client-mysql-cert.pem -p

Réplication

Note :!: : La réplication se met en place une fois la configuration de Mysql terminée et validée et une fois les données importées sur le serveur maître.

Configuration du serveur maître

Se connecter sur la base du serveur maître :

mysql -u root -p'motdepasse'

Créer les utilisateurs de réplication sur la base de façon à pouvoir répliquer dans les deux sens :

grant replication client on *.* to 'userrepl'@'Serveur-Mysql-Maître' identified by 'xxxxxxxx';
grant replication client on *.* to 'userrepl'@'Serveur-Mysql-Esclave' identified by 'xxxxxxxx';

Se déconnecter de la base, éditer le fichier de configuration /etc/my.cnf puis ajouter dans la section [mysqld] les lignes suivantes :

server-id=1
log-bin=redo-bin

Arrêter et démarrer le service MySQL

Se connecter de nouveau sur la base du serveur maître :

mysql -u root -p'motdepasse'

Verrouiller les tables contre les écritures

flush tables with read lock;

Relever la position dans les redo de mysql :

show master status;
+---------------------+----------+--------------+------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| redo-bin.000005     |       98 |              |                  |
+---------------------+----------+--------------+------------------+

Arrêter le service MySQL

Recopier les données de la base maître vers la base esclave :

scp -rp /var/lib/mysql/* Serveur-Mysql-Esclave:/var/lib/mysql/

Démarrer le service MySQL

Configuration du serveur esclave

Editer le fichier de configuration /etc/my.cnf puis ajouter dans la section [mysqld] les lignes suivantes :

server_id=2
log-bin=redo-bin
log-slave-updates
master-host=Serveur-Mysql-Maître
master-port=4020
master-user=userrepl
master-password=xxxxxxxxx

Démarrer le service MySQL

Se connecter à la base :

mysql -u root -p'motdepasse'

FIXME Initialiser la configuration de réplication :

change master to
  master_host='Serveur-Mysql-Maître',
  master_user='userrepl',
  master_port='4020'
  master_password='xxxxxxxxx',
  master_log_file='redo-bin.000005',
  master_log_pos='98'
  ;

Lancer la réplication :

start slave;

Pour connaître le statut de la réplication :

show slave status\G;

Pour plus de détails

informatique/linux/mysql.1358491649.txt.gz · Dernière modification: 2018/09/06 19:00 (modification externe)