Mysql
Sommaire |
[modifier] Créer un utilisateur
A partir de mysql version 5.0.2
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password'] [, user [IDENTIFIED BY [PASSWORD] 'password']] ...
Version antérieur à la 5.0.2
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
[modifier] Administration de serveur MySQL
L'outil principale pour l'administration de mysql est le binaire mysql. Celui-ci permet de se connecter sur le serveur mysql et de l'administrer. Plusieurs commandes mysql permettant d'effectuer directement certaine tache d'administration, de surveillance, etc... sans toutefois devoir connaître et donner les commandes à la base elle même.
[modifier] Se connecter sur la base
Pour se connecter à une base et ensuite taper directement les commandes mysql, il est nécessaire d'utiliser le binaire mysql. Connection à une base:
mysql -u [user] -p -P [port] -h [host] -S [socket] [database]
Aucun des paramêtres n'est obligatoire. Toutefois, sans paramêtre, ceux par défaux seront utilisé. Voir /etc/my.cnf, ~/.my.cnf, etc... Il est possible de se connecter soit par le host, ou par la socket, ou en définissant un certain port. Voir mysql --help pour de plus amples information.
MASTER:sql2:/root# mysql -u admin -p -h localhost Enter password: xxxxxxx Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 29617 to server version: 3.23.53-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
[modifier] Informations et utilisation des bases
La plus part des commandes ci-dessous sont effectuées directement en étant loggué sur le serveur mysql par le biais de la commande mysql.
[modifier] Voir les bases disponibles
show databases;
[modifier] Activer/Utiliser une base
use <database>;
[modifier] Voir les tables d'une base
show tables;
[modifier] Voir la structure d'une table
desc <table>; ou desc <table>\G pour un affichage en colone (c'est plus plaisant à l'affichage)
[modifier] Connaitre le nombre de ligne d'une table
select count(*) from <table>;
Commande pouvant être longue sur de grosse table. Le moteur pouvant être obligé de parcourir toute la table pour donner le résultat.
Sur les tables MyISAM il est possible d'utiliser les informations de la base information_schema.
use information_schema; select table_rows from tables where table_name="<table>";
[modifier] Réplication
[modifier] Master - Slave
Connaitre le status du master. Permet de savoir si un serveur slave est indiqué pour la réplication
show master status;
Connaitre le status du slave. Permet de connaitre les paramètre de réplication en cours et si la réplication est en marche. Indique aussi la dernière erreur rencontré.
show slave status;
--- 2.3.2 Slave --- [1] Arrêter la base
[2] Copier les db :
$ scp master:/tmp/backup.tar.gz /tmp $ cd / $ tar xvfz /tmp/backup.tar.gz
[3] Démarrer la base
[4] Mettre à jour les droits afin que ceux-ci soient conformes à ceux en production
[5] Positionner les paramètres de réplication :
mysql> change master to
master_host='****',
master_user='repl',
master_password='****',
master_log_file='bin.002',
master_log_pos=789;
Note : ici, les paramètres `master_log_file' et `master_log_pos' ont été positionnés à des valeurs cohérentes avec celles prises en exemple pour le master.
[6] Lancer la réplication :
mysql> slave start;
[modifier] Check la réplication
mysql -p -uroot -e 'show slave status\G' | grep -E '(Master_Log|Slave.*Running|Seconds)'
Enter password:
Master_Log_File: bin-log.002679
Read_Master_Log_Pos: 136744805
Relay_Master_Log_File: bin-log.002678
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: 794897949
Seconds_Behind_Master: 5089
[modifier] Binlogs
[modifier] Voir la liste de binlogs
show BINARY LOGS
[modifier] Voir les requêtes des binlog
show binlog events in 'mysqld-bin.000113' from 0 limit 12172,2;
Ou utiliser mysqlbinlog pour avoir plus d'information.
mysqlbinlog -d DATABASE --stop-position=28724097 -v bin-log.001481 |grep -A 12 "INSERT INTO DATABASE.TABLE" ### INSERT INTO DATABASE.TABLE ### SET ### @1=60275 ### @2=2011-10-07 11:30:00 ### @3=2011-10-07 11:40:00 ### @4=1 ### @5=2011-10-07 11:25:02 ### @6=43543 # at 28723691 #111007 11:25:02 server id 21 end_log_pos 28723756 Query thread_id=0 exec_time=0 error_code=0 SET TIMESTAMP=1317979502/*!*/; COMMIT
[modifier] Variables
This SHOW command can also be used in conjunction with WHERE. For example, to show all variables that have a numeric setting higher then zero, you can use:
SHOW VARIABLES WHERE VALUE > 0;
Or, if you would like to see all variables, except the storage engine specific ones, you can use the following expression:
SHOW VARIABLES WHERE Variable_Name NOT LIKE '%myisam%' AND Variable_Name NOT LIKE '%innodb%';
[modifier] Requêtes Diverses
[modifier] Exports CSV avec MySQL : SELECT INTO OUTFILE
Le principe de SELECT INTO OUTFILE est simple: réaliser un export de données en écrivant un resultset (résultats d’exécution d’une requête) directement dans un fichier CSV sur le serveur.
Pour cela, l’utilisateur avec lequel vous vous connectez à MySQL doit avoir le priilège « FILE ».
Autre remarque, vous ne pourrez en aucun cas écraser un fichier déjà existant sur le serveur.
syntaxe, de la fonction SELECT INTO OUTFILE :
SELECT champ FROM TABLE WHERE champ = 'valeur cherchée' INTO OUTFILE '/var/dump.csv' FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"';
[modifier] Imports de CSV dans MySQL : LOAD DATA INFILE
LOAD DATA INFILE '/tmp/data.csv' INTO TABLE `data` FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' (id, @dummy, texte)
[modifier] Debug/Problèmes
Voir toutes les tables d'une database en utilisation ou possédant un lock sur la table.
mysql> show open tables where In_use>0 or Name_locked>0;
Voir la liste des process trié par le temps d'execution le plus long ou le moins long.
mysql> SELECT NOW(); SELECT ID,USER,HOST,TIME,STATE,LEFT(INFO,20) FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY TIME DESC;
+---------------------+ | NOW() | +---------------------+ | 2012-02-22 12:19:41 | +---------------------+ 1 row in set (0.00 sec) +------+------------------+-----------------------+-------+----------------------------------+----------------------+ | ID | USER | HOST | TIME | STATE | LEFT(INFO,20) | +------+------------------+-----------------------+-------+----------------------------------+----------------------+ | 94 | system user | | 83851 | Copying to tmp table | REPLACE INTO `COMPUT | | 4 | system user | | 43481 | Waiting for master to send event | NULL | | 2 | root | localhost | 13354 | | NULL | | 233 | monyoguser | P-BDDWEB-S-96-2:60924 | 586 | | NULL | | 6591 | root | localhost | 0 | executing | SELECT ID,USER,HOST, | | 1 | monyoguser | P-BDDWEB-S-96-2:49970 | 0 | | NULL | +------+------------------+-----------------------+-------+----------------------------------+----------------------+ 52 rows in set (0.00 sec)
mysql> SELECT NOW(); SELECT ID,USER,HOST,TIME,STATE,LEFT(INFO,20) FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY TIME ASC;
+---------------------+ | NOW() | +---------------------+ | 2012-02-22 12:28:05 | +---------------------+ 1 row in set (0.00 sec) +------+------------------+-----------------------+-------+----------------------------------+----------------------+ | ID | USER | HOST | TIME | STATE | LEFT(INFO,20) | +------+------------------+-----------------------+-------+----------------------------------+----------------------+ | 6591 | root | localhost | 0 | executing | SELECT ID,USER,HOST, | | 1 | monyoguser | P-BDDWEB-S-96-2:49970 | 0 | | NULL | | 6683 | mysqlmonitor | P-WEB-S-128-1:35561 | 185 | | NULL | | 233 | monyoguser | P-BDDWEB-S-96-2:60924 | 1090 | | NULL | | 2 | root | localhost | 13858 | | NULL | | 4 | system user | | 43985 | Waiting for master to send event | NULL | | 94 | system user | | 84355 | Copying to tmp table | REPLACE INTO `COMPUT | +------+------------------+-----------------------+-------+----------------------------------+----------------------+ 53 rows in set (0.00 sec)
[modifier] Monitorer / Debuguer l'Innodb
Have you made any progress determining which tables are involved in queries/transactions that encounter this problem?
Typically, you'll want to execute SHOW ENGINE INNODB STATUS after encountering this problem so that you can get some information about what is going on inside of InnoDB. If this is something you're encountering a lot of, you may wish to enable the InnoDB monitor in order to get some more reliable debugging data.
To enable the InnoDB Lock Monitor, create a table (in any database) called innodb_lock_monitor, like this:
CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;
This will cause InnoDB to write a considerable amount of information to the MySQL error log. You'll only want to have this enabled temporarily, as it will incur some small performance hit and will cause your MySQL error log to grow quite large. To disable the InnoDB Lock Monitor, drop the innodb_lock_monitor table:
DROP TABLE innodb_lock_monitor;
