ZikossWiki

Cette page est en lecture seule. Vous pouvez afficher le texte source, mais ne pourrez pas le modifier. Contactez votre administrateur si vous pensez qu'il s'agit d'une erreur.
====== MySql ======

:!: Ce tutoriel aborde uniquement l'administration de MySQL, pour les requêtes SQL voir : [[informatique: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;

Voici quelques syntaxes qui donnent un résultat équivalent à ''show table status'' mais qui permet de filtrer :
  SELECT * FROM information_schema.tables WHERE table_schema = DATABASE();
  SELECT TABLE_NAME,TABLE_ROWS FROM information_schema.tables WHERE table_schema = DATABASE();
  SELECT TABLE_NAME,TABLE_ROWS FROM information_schema.tables WHERE table_schema = DATABASE() ORDER BY TABLE_ROWS;
  SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_ROWS FROM information_schema.tables WHERE table_schema = DATABASE() ORDER BY TABLE_ROWS;

Exporter un tables vers un fichier CSV :
  SELECT * FROM my_table
  INTO OUTFILE '/tmp/my_table.csv'
  FIELDS TERMINATED BY ','
  ENCLOSED BY '"'
  LINES TERMINATED BY '\n';

==== 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;

Pour voir le nombre de ligne par tables :
  SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'ma_base';

==== 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

ou bien :
  mysql -u root -p dbperso
  source 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

Pour faire un requête en mode non intéractif :
  mysql --user=root --password='*******' -e "SHOW DATABASES;"






===== Monitoring =====
Pour voir les process mysql (dans la console MySQL) :
  SHOW PROCESSLIST

Pour voir les process mysql avec les requêtes détaillées (dans la console MySQL) :
  SHOW FULL PROCESSLIST

Pour voir les process mysql sans la console 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''.

Obtenir la taille des bases de données :
  SELECT table_schema AS "Database", 
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" 
  FROM information_schema.TABLES 
  GROUP BY table_schema;

Obtenir la taille de chacunes des tables :
  use ma_table;
  SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
  FROM information_schema.TABLES 
  ORDER BY (data_length + index_length) DESC;
===== 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 exporter uniquement le schémas de la base (pas les données):
  mysqldump --no-data -u root -p ma_base

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
  
Pour exporter selon un critère "WHERE" dans une table :
  mysqldump --where "country = 'France'" ma_base table1 > dump_where_France.sql

Pour exporter une base de données à travers SSH :
  mysqldump -u root -p database | gzip -c | ssh root@192.168.1.1 'cat > /tmp/export.sql.gz'

===== 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;

===== Optimisations =====

Quantité de mémoire que MySQL peut mettre des requêtes en buffer. La valeur recommandé est 25% de la mémoire disponible.
Exemple si le serveur dispose de 4 Go de mémoire dédié à MySQL :

Editer ''/etc/mysql/my.cnf'' et ajouter/modifier :

  key_buffer 1G 

Exemple pour augmenter le nombre de table en mémoire cache :

  table_cache            = 2000

===== Pour plus de détails =====
  * Description de SQL : http://fr.wikipedia.org/wiki/Structured_Query_Language
  * Syntaxes de définition des données : http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-data-definition.html
  * Syntaxes de manipulation des données : http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-data-manipulation.html
ZikossWiki

Outils pour utilisateurs

Outils du site

Outils de la page
