====== 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 ''; ou pour tous les droits sur la base : GRANT ALL PRIVILEGES on dbperso.* to 'myuser'@'localhost' IDENTIFIED BY ''; 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 '' 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