{"id":295,"date":"2008-12-06T00:27:07","date_gmt":"2008-12-05T23:27:07","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=295"},"modified":"2008-12-13T17:28:04","modified_gmt":"2008-12-13T16:28:04","slug":"les-nouveautes-de-mysql-51-part-35","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2008\/12\/06\/les-nouveautes-de-mysql-51-part-35\/","title":{"rendered":"Les nouveaut\u00e9s de MySQL 5.1 — (part 3\/5)"},"content":{"rendered":"


\n(<- pr\u00e9c\u00e9dent<\/a>)<\/em>
\n<\/p>\n

MySQL Cluster : support des donn\u00e9es sur disque<\/h2>\n

MySQL propose une solution de haute disponibilit\u00e9 : MySQL Cluster. Cette technologie permet de mettre en place une architecture cluster shared nothing<\/strong><\/em> \u00e0 l’aide de tables au format NDBCluster<\/strong><\/em>. Jusqu’\u00e0 MySQL 5.0, MySQL Cluster ne fonctionnait qu’enti\u00e8rement en m\u00e9moire. Ceci excluait donc certaines bases de donn\u00e9es trop volumineuses. La nouvelle version 5.1 l\u00e8ve cette limitation en permettant de mettre les donn\u00e9es sur disque.<\/p>\n

Pour enregistrer ses donn\u00e9es sur le disque, il faut au pr\u00e9alable d\u00e9finir deux nouveaux objets : un LOGFILE GROUP<\/strong> et un TABLESPACE<\/strong>.<\/p>\n

LOGFILE GROUP<\/strong> : permet g\u00e9rer les undo log<\/strong><\/em> et le crash-recovery<\/strong><\/em><\/p>\n

Pour cr\u00e9er un logfile group:<\/p>\n

CREATE LOGFILE GROUP lg_1\r\n   ADD UNDOFILE 'undo_1.dat'\r\n   INITIAL_SIZE 16M\r\n   UNDO_BUFFER_SIZE 2M\r\n   ENGINE NDB;\r\nALTER LOGFILE GROUP lg_1\r\n   ADD UNDOFILE 'undo_2.dat'\r\n   INITIAL_SIZE 12M\r\n   ENGINE NDB;<\/pre>\n
TABLESPACE<\/strong> : pour stocker les donn\u00e9es. A noter que le tablespace<\/strong><\/em> utilise le logfile group<\/strong><\/em> cr\u00e9\u00e9<\/p>\n
\n
On peut maintenant cr\u00e9er la table et l’associer au tablespace<\/strong><\/em><\/p>\n
\n
Cr\u00e9ation d’un tablespace:<\/p>\n
CREATE TABLESPACE ts_1\r\n   ADD DATAFILE 'data_1.dat'\r\n   USE LOGFILE GROUP lg_1\r\n   INITIAL_SIZE 32M\r\n   ENGINE NDB;\r\nALTER TABLESPACE ts_1\r\n   ADD DATAFILE 'data_2.dat'\r\n   INITIAL_SIZE 48M\r\n   ENGINE NDB;<\/pre>\n
\n
Cr\u00e9ation d’une table au format NDBCluster avec donn\u00e9es sur disque:<\/p>\n
CREATE TABLE 'City' (\r\n   'ID' int(11) NOT NULL AUTO_INCREMENT,\r\n   'Name' char(35) NOT NULL DEFAULT '',\r\n   'Population' int(11) NOT NULL DEFAULT '0',\r\n   PRIMARY KEY ('ID')\r\n) TABLESPACE ts_1\r\n   STORAGE DISK\r\n   ENGINE=NDB;<\/pre>\n
Les tables de journalisation<\/h2>\n
MySQL offre la possibilit\u00e9 de journaliser l’activit\u00e9 du serveur. en activant le general log<\/strong>. Il est aussi est possible de journaliser seulement les requ\u00eates lentes (celles qui s’ex\u00e9cutent en un temps sup\u00e9rieur au seuil que vous aurez pr\u00e9alablement fix\u00e9) en activant le slow_query_log<\/strong>.<\/p>\n
MySQL 5.1 permet toujours de journaliser ces informations dans un fichier, et ajoute la possibilit\u00e9 de le faire en plus dans une table.<\/p>\n
Cerise sur le g\u00e2teau, la journalisation peut \u00eatre d\u00e9marr\u00e9e ou arr\u00eat\u00e9e \u00e0 chaud.<\/p>\n
Les commandes SHOW VARIABLES LIKE ‘general_log’<\/strong> et SHOW VARIABLES LIKE ‘log_output’<\/strong> permettent, respectivement, de savoir si le general_log<\/strong> est activ\u00e9 et sous quelle forme.<\/p>\n
Pour afficher l’\u00e9tat du general_log:<\/p>\n
mysql> SHOW VARIABLES LIKE 'general_log';\r\n+---------------+-------+\r\n| Variable_name | Value |\r\n+---------------+-------+\r\n| general_log   | OFF   |\r\n+---------------+-------+\r\n1 row in set (0.03 sec)\r\n\r\nmysql> SHOW VARIABLES LIKE 'log_output';\r\n+---------------+-------+\r\n| Variable_name | Value |\r\n+---------------+-------+\r\n| log_output    | TABLE |\r\n+---------------+-------+<\/pre>\n
\n
Le general_log<\/strong> peut \u00eatre activ\u00e9 dynamiquement avec la commande SET<\/strong> :<\/p>\n
\n
mysql> SET GLOBAL general_log = 1;\r\nQuery OK, 0 rows affected (0.04 sec)<\/pre>\n
Le format de sortie peut lui aussi \u00eatre chang\u00e9 dynamiquement :<\/p>\n
+---------------------+-------------------------+-----------+-----------\r\n+--------------+---------------------------------------+\r\n| event_time          | user_host               | thread_id | server_id\r\n| command_type | argument                              |\r\n+---------------------+-------------------------+-----------+-----------\r\n+--------------+---------------------------------------+\r\n| 2008-02-21 15:51:06 | daz[daz] @ localhost [] |        10 |        51\r\n| Query        | SET GLOBAL log_output = \u2018TABLE, FILE' |\r\n| 2008-02-21 15:51:06 | daz[daz] @ localhost [] |        10 |        51\r\n| Query        | SELECT * FROM mysql.general_log       |\r\n+---------------------+-------------------------+-----------+-----------\r\n+--------------+---------------------------------------+\r\n2 rows in set (0.00 sec)<\/pre>\n
mysql> SET GLOBAL log_output = 'TABLE,FILE';\r\nQuery OK, 0 rows affected (0.00 sec)<\/pre>\n
<\/p>\n
Les m\u00eames op\u00e9rations sont possibles sur le slow_query_log<\/strong> :<\/p>\n
mysql> SHOW VARIABLES LIKE 'slow_query_log';\r\n+----------------+-------+\r\n| Variable_name | Value  |\r\n+----------------+-------+\r\n| slow_query_log | ON    |\r\n+----------------+-------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> SELECT * FROM mysql.slow_log;\r\n+------------+----------------------+-----------+-----------+---------------+-----+\r\n| start_time | user_host query_time | lock_time | rows_sent | rows_examined | db |\r\n      | last_insert_id | insert_id | server_id | sql_text\r\n-----------------------------------------------------------------------------------\r\n| 2008-02-21 15:57:13 | daz[daz] @ localhost [] | 00:00:00 | 00:00:00 | 0 | 0 |\r\n      | test | 0 | 0 | 51 | SELECT * FROM mysql.slow_log |\r\n| 2008-02-21 15:57:38 | daz[daz] @ 640m [192.168.1.106] | 00:00:00 | 00:00:00 | 268 |\r\n      | 268 | mysql | 0 | 0 | 51 | SELECT * FROM information_schema.\r\n      GLOBAL_VARIABLES G |\r\n| 2008-02-21 15:57:43 | daz[daz] @ localhost [] | 00:00:00 | 00:00:00 | 2 | 2 | test |\r\n      | 0 | 0 | 51 | SELECT * FROM mysql.slow_log |\r\n+------------------------------------------------------------------------------------+\r\n3 rows in set (0.00 sec)<\/pre>\n
(\u00e0 suivre… Le moteur de stockage CSV & Le support de XML\/XPath<\/a>)<\/em><\/p>\n
\n","protected":false},"excerpt":{"rendered":"
MySQL propose une solution de haute disponibilit\u00e9 : MySQL Cluster. Cette technologie permet de mettre en place une architecture cluster shared nothing \u00e0 l’aide de tables au format NDBCluster. Jusqu’\u00e0 MySQL 5.0, MySQL Cluster ne fonctionnait qu’enti\u00e8rement en m\u00e9moire. Ceci excluait donc certaines bases de donn\u00e9es trop volumineuses. La nouvelle version 5.1 l\u00e8ve cette limitation en permettant de mettre les donn\u00e9es sur disque.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"categories":[8],"tags":[70,241,59,69,71],"class_list":["post-295","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-general-log","tag-mysql","tag-mysql-51","tag-mysql-cluster","tag-slow-query-log"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-4L","jetpack-related-posts":[{"id":237,"url":"https:\/\/dasini.net\/blog\/2008\/11\/28\/les-nouveautes-de-mysql-51-part-15\/","url_meta":{"origin":295,"position":0},"title":"Les nouveaut\u00e9s de MySQL 5.1 — (part 1\/5)","author":"Olivier DASINI","date":"28 novembre 2008","format":false,"excerpt":"Que de chemin parcouru depuis ce 5 juillet 1999, date de lancement de MySQL 3.23.0. En plus des objectifs de simplicit\u00e9 d'administration, de hautes performances et de fiabilit\u00e9, se sont greff\u00e9es, au fur et \u00e0 mesure, les fonctionnalit\u00e9s les plus demand\u00e9es par les utilisateurs. La nouvelle version du SGBDR open-source\u2026","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":275,"url":"https:\/\/dasini.net\/blog\/2008\/12\/03\/les-nouveautes-de-mysql-51-part-25\/","url_meta":{"origin":295,"position":1},"title":"Les nouveaut\u00e9s de MySQL 5.1 — (part 2\/5)","author":"Olivier DASINI","date":"3 d\u00e9cembre 2008","format":false,"excerpt":"Pouvoir automatiser ses t\u00e2ches de mani\u00e8re fiable et simple est le r\u00eave de tout administrateur de base de donn\u00e9es. Le programmateur d'\u00e9v\u00e8nements (Event Scheduler) est un planificateur de t\u00e2ches (CRON-like) embarqu\u00e9 dans MySQL 5.1.","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3751,"url":"https:\/\/dasini.net\/blog\/2020\/08\/27\/resume-des-nouveautes-de-mysql-8-0-21\/","url_meta":{"origin":295,"position":2},"title":"R\u00e9sum\u00e9 des nouveaut\u00e9s de MySQL 8.0.21","author":"Olivier DASINI","date":"27 ao\u00fbt 2020","format":false,"excerpt":"J'ai cr\u00e9\u00e9, sp\u00e9cialement pour toi, une pr\u00e9sentation qui r\u00e9sume les nouvelles fonctionnalit\u00e9s de la derni\u00e8re version de MySQL, sortie le 13 juillet 2020, la 8.0.21.","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1654,"url":"https:\/\/dasini.net\/blog\/2016\/10\/05\/mysql-a-oracle-openworld-2016\/","url_meta":{"origin":295,"position":3},"title":"MySQL \u00e0 Oracle OpenWorld 2016","author":"Olivier DASINI","date":"5 octobre 2016","format":false,"excerpt":"R\u00e9sum\u00e9 des annonces MySQL \u00e0 Oracle OpenWorld: MySQL dans le cloud, MySQL Group Replication, MySQL InnoDB Cluster, MySQL 8,...","rel":"","context":"Dans "Group Replication"","block_context":{"text":"Group Replication","link":"https:\/\/dasini.net\/blog\/category\/group-replication\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/Oracle_MySQL_Cloud_Service.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":5463,"url":"https:\/\/dasini.net\/blog\/2022\/01\/17\/webinar-architectures-de-bases-de-donnees-mysql-gestion-de-la-continuite-dactivite\/","url_meta":{"origin":295,"position":4},"title":"Webinar \u2013 Architectures de bases de donn\u00e9es MySQL – Gestion de la continuit\u00e9 d’activit\u00e9","author":"Olivier DASINI","date":"17 janvier 2022","format":false,"excerpt":"Oracle MySQL propose des solutions qui permettent un d\u00e9ploiement facile et rapide d'architectures de hautes disponibilit\u00e9s avec MySQL InnoDB Cluster et MySQL InnoDB ReplicaSet. En ce qui concerne le plan de continuit\u00e9 d'activit\u00e9, une toute nouvelle solution a \u00e9t\u00e9 impl\u00e9ment\u00e9e: MySQL InnoDB ClusterSet. Ces solutions, cr\u00e9\u00e9es et support\u00e9es par l'\u00e9quipe\u2026","rel":"","context":"Dans "Haute Disponibilit\u00e9"","block_context":{"text":"Haute Disponibilit\u00e9","link":"https:\/\/dasini.net\/blog\/category\/haute-disponibilite\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2022\/01\/MySQL_InnoDB_Cluster.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":474,"url":"https:\/\/dasini.net\/blog\/2009\/03\/02\/presentation-vue-densemble-de-mysql-51\/","url_meta":{"origin":295,"position":5},"title":"Pr\u00e9sentation: Vue d’ensemble de MySQL 5.1","author":"Olivier DASINI","date":"2 mars 2009","format":false,"excerpt":"Num\u00e9ro un pour les applications en ligne, MySQL se positionne maintenant sur le secteur des applications d\u2019entrep\u00f4ts de donn\u00e9es et d\u2019informatique d\u00e9cisionnelle\u2026 http:\/\/dasini.net\/blog\/presentations\/?#presentation_mysql51","rel":"","context":"Dans "Pr\u00e9sentation"","block_context":{"text":"Pr\u00e9sentation","link":"https:\/\/dasini.net\/blog\/category\/presentation\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/295","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/comments?post=295"}],"version-history":[{"count":8,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/295\/revisions"}],"predecessor-version":[{"id":335,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/295\/revisions\/335"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=295"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=295"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=295"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}