« MYSQL » : différence entre les versions
De TwisterWiki
Aucun résumé des modifications |
|||
Ligne 73 : | Ligne 73 : | ||
== Commandes mysql utiles == | == Commandes mysql utiles == | ||
=== Purge des logs binaires === | === Purge des logs binaires === | ||
FLUSH LOGS; | mysql><span style="color: blue;">FLUSH LOGS;</span> | ||
RESET MASTER; | mysql><span style="color: blue;"> RESET MASTER;</span> | ||
PURGE BINARY LOGS TO 'mysql-bin. | mysql><span style="color: blue;">PURGE BINARY LOGS TO 'mysql-bin.0002560';</span> | ||
PURGE BINARY LOGS BEFORE '2013-06-13 22:46:26'; | mysql><span style="color: blue;">PURGE BINARY LOGS BEFORE '2013-06-13 22:46:26';</span> | ||
=== Lister les index === | === Lister les index === | ||
SHOW INDEX FROM table.db; | mysql><span style="color: blue;">SHOW INDEX FROM table.db;</span> | ||
SHOW INDEX FROM table [FROM db]; | mysql><span style="color: blue;">SHOW INDEX FROM table [FROM db];</span> | ||
=== créer un index === | === créer un index === | ||
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...) | mysql><span style="color: blue;">CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...)</span> | ||
[[category:Basededonnées]] | [[category:Basededonnées]] | ||
[[category:Linux]] | [[category:Linux]] |
Version du 14 juin 2013 à 14:28
Réaliser des dumps
mysqldump --extended-insert=FALSE --add-drop-table --triggers --routines --add-drop-database --user=$USER --password=$PASS $DATABASE > dump.sql mysqldump --extended-insert=FALSE --add-drop-table --triggers --routines --add-drop-database --no-data --user=$USER --password=$PASS $DATABASE > dump_schema.sql
mysqldump --opt --triggers --routines --add-drop-database --user=$USER --password=$PASS $DATABASE > dump.sql
Basculer un slave en master et le master en slave
- faire passer le master en "read only"
- mysql -u root -p
- mysql> use DATABASE;
- mysql> flush tables with read lock;
- stopper le slave
- mysql -u root -p
- mysql> stop SLAVE;
- noter la position du serveur en slave
- mysql -u root -p
- mysql> show master status\G
- sur le master (passage en slave)
- mysql> CHANGE MASTER TO MASTER_HOST=, MASTER_USER='replic', MASTER_PASSWORD='PASSWORD', MASTER_PORT=3306 ,MASTER_LOG_FILE='FILE', MASTER_LOG_POS=POS;
- repasser le slave en "read write"
- mysql> use DATABASE;
- mysql> unlock tables;
- Une fois toute ces opération réalisée le master est devenu le slave, Il reste à supprimer le fichier /var/lib/mysql/master.info
- mysql -u root -p
- mysql> stop SLAVE;
- rm /var/lib/mysql/master.info
- mysql -u root -p
- mysql> start SLAVE;
Documentation sur MySQL
Identifier les tables en innodb
use information_schema SELECT CONCAT(`TABLE_SCHEMA`,".",`TABLE_NAME`) FROM TABLES WHERE ENGINE = "InnoDB";
les variables MySQL
- Les globales
- key_buffer
- query_cache_size
- query_cache_limit
- table_cache
- open_files_limit
- max_connections
- innodb_buffer_pool_size
- innodb_log_buffer_size
- max_heap_table_size
- tmp_table_size
- les variables de sessions (à multiplié par le max_connection)
- read_buffer_size (par défaut à 128k)
- read_rnd_buffer_size (par défaut à 256k)
- join_buffer_size (par défaut à 256k)
- sort_buffer_size (par défaut à 2M)
Calcul de la quantité de mémoire utilisée par mysql
key_buffer + query_cache_size + innodb_buffer_pool_size + innodb_log_buffer_size + max_heap_table_size + tmp_table_size + ( max_connexion x (read_buffer_size + read_rnd_buffer_size + join_buffer_size + sort_buffer_size ))
Commandes mysql utiles
Purge des logs binaires
mysql>FLUSH LOGS; mysql> RESET MASTER;
mysql>PURGE BINARY LOGS TO 'mysql-bin.0002560'; mysql>PURGE BINARY LOGS BEFORE '2013-06-13 22:46:26';
Lister les index
mysql>SHOW INDEX FROM table.db; mysql>SHOW INDEX FROM table [FROM db];
créer un index
mysql>CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...)