{"id":3,"date":"2007-02-21T00:00:00","date_gmt":"2007-02-21T00:00:00","guid":{"rendered":"http:\/\/netexpertise\/fr\/?p=3"},"modified":"2021-10-11T21:12:26","modified_gmt":"2021-10-11T20:12:26","slug":"archiver-accounting","status":"publish","type":"post","link":"http:\/\/www.netexpertise.eu\/fr\/database\/mysql\/archiver-accounting.html","title":{"rendered":"Archiver la Table Accounting Freeradius sous Mysql"},"content":{"rendered":"\n

Enregistrer les sessions Radius, notamment accounting dans une base Mysql<\/a> peut g\u00e9n\u00e9rer des tables de tr\u00e8s grosse taille. Des probl\u00e8mes de performance vont appara\u00eetre apr\u00e8s un certain temps, surtout si des scripts sont cr\u00e9\u00e9s pour g\u00e9n\u00e9rer des rapports sur le trafic utilis\u00e9 par les clients. C’est pourquoi j’ai ajout\u00e9 une nouvelle table pour enregistrer les vieilles donn\u00e9es, afin d’archiver la table accounting de Freeradius.

<\/p>\n\n\n\n

\"Archiver<\/figure><\/div>\n\n\n\n


Motivations<\/h2>\n\n\n\n

Je pourrais bien s\u00fbr effacer tous les enregistrements d\u00e9passant un certain temps. Toutefois, ces enregistrements peuvent fournir des informations importantes, notamment pour les FAI:
– Rapport sur le trafic des clients
– Les logs des propri\u00e9taires d’une adresse IP
Je ne suis pas s\u00fbr combien de temps les FAI sont cens\u00e9s garder ce type d’information l\u00e9gallement, mais je pense que 3 mois est un bon compromis. Pourquoi ne pas effacer les donn\u00e9es qui ont plus de 3 mois? C’est peut-\u00eatre personnel mais j’aime conserver toutes les donn\u00e9es pour de futures v\u00e9rifications. Des clients pourraient se plaindre avoir \u00e9t\u00e9 factur\u00e9s pour une connexion qu’ils n’ont pas utilis\u00e9e pendant 10 mois par exemple. Comment v\u00e9rifier si cela est vrai? Nous en serions dans l’incapacit\u00e9 \u00e9videmment…<\/p>\n\n\n\n


Cr\u00e9er la Nouvelle Table d’Archivage<\/h2>\n\n\n\n

Nous partons d’une installation de FreeRadius<\/a> avec une base Mysql comme backend. Les enregistrements pour l’accounting sont sauv\u00e9s dans la table Radacct. Mysql propose un moteur \u00ab\u00a0storage\u00a0\u00bb qui permet de stoker des quantit\u00e9s importantes de donn\u00e9es comme les logs. Le moteur ARCHIVE<\/a> ne supporte ni la suppression ni l’indexage donc les requ\u00eates sont forc\u00e9ment plus lentes \u00e0 ex\u00e9cuter. C’est un probl\u00e8me mineur puisque cette table ne sera acc\u00e9d\u00e9e que tr\u00e8s rarement. Par contre, les donn\u00e9es sont compress\u00e9s automatiquement par le moteur pour un gain d’espace \u00e9vident. Vous trouverez un article tr\u00e8s int\u00e9ressant \u00e9crit par Robin Schumacher, le directeur management de produits MySQL, \u00e0 http:\/\/dev.mysql.com\/tech-resources\/articles\/storage-engine.html<\/a>. Voici le code pour la cr\u00e9ation de la table:

<\/p>\n\n\n\n

CREATE TABLE `radacctold` (\n  `RadAcctId` bigint(21) NOT NULL,\n  `AcctSessionId` varchar(128) default NULL,\n  `AcctUniqueId` varchar(32) NOT NULL default '',\n  `UserName` varchar(64) NOT NULL default '',\n  `Realm` varchar(64) default '',\n  `NASIPAddress` varchar(15) NOT NULL default '',\n  `NASPortId` int(12) default NULL,\n  `NASPortType` varchar(32) default NULL,\n  `AcctStartTime` datetime NOT NULL default '0000-00-00 00:00:00',\n  `AcctStopTime` datetime NOT NULL default '0000-00-00 00:00:00',\n  `AcctSessionTime` int(12) default NULL,\n  `AcctAuthentic` varchar(32) default NULL,\n  `ConnectInfo_start` varchar(32) default NULL,\n  `ConnectInfo_stop` varchar(32) default NULL,\n  `AcctInputOctets` int(12) unsigned default NULL,\n  `AcctOutputOctets` bigint(12) default NULL,\n  `CalledStationId` varchar(50) NOT NULL default '',\n  `CallingStationId` varchar(50) NOT NULL default '',\n  `AcctTerminateCause` varchar(32) NOT NULL default '',\n  `ServiceType` varchar(32) default NULL,\n  `FramedProtocol` varchar(32) default NULL,\n  `FramedIPAddress` varchar(15) NOT NULL default '',\n  `AcctStartDelay` int(12) default NULL,\n  `AcctStopDelay` int(12) default NULL\n) ENGINE = ARCHIVE;<\/code><\/pre>\n\n\n\n

C’est quasiment une copie de la table originale sans les indexes et la cl\u00e9 primaire. Seul le moteur diff\u00e8re.
Note<\/i> Vous pouvez v\u00e9rifier que le moteur archive est disponible gr\u00e2ce \u00e0 la commande suivante: SHOW ENGINES’;<\/i>. <\/p>\n\n\n\n

Transfert de la Table Accounting<\/h2>\n\n\n\n
La seconde \u00e9tape est de migrer les vieilles donn\u00e9es de la table originale vers celle pr\u00e9c\u00e9demment cr\u00e9\u00e9e. Une simple proc\u00e9dure interne permet d’effectuer cette t\u00e2che:

<\/p>\n\n\n\n
CREATE PROCEDURE radius.archive_acct()\nBEGIN\n  INSERT INTO radacctold\n    SELECT * FROM radacct\n    WHERE acctstoptime > 0\n    AND date(acctstarttime) < (CURDATE() - INTERVAL 3 MONTH);\n  DELETE FROM radacct\n    WHERE acctstoptime > 0\n    AND date(acctstarttime) < (CURDATE() - INTERVAL 3 MONTH);\nEND<\/code><\/pre>\n\n\n\n

Il suffit d’ex\u00e9cuter cette proc\u00e9dure une fois par jour et vous \u00eates sortis d’affaire. Aucune maintenance n’est n\u00e9cessaire et les bonnes performances de la base sont maintenues. Vous pouvez \u00e9galement modifier cette proc\u00e9dure pour effacer les vieux enregistrements sur une base journali\u00e8re.
Au moment o\u00f9 cet article a \u00e9t\u00e9 \u00e9crit, Mysql5.1 est toujours en version Beta, c’est pourquoi je n’utilise pas les fonctions \u00e9v\u00e8nementielles pour lancer le script. Une simple ligne dans cron fera le m\u00eame travail en attendant.
Je sugg\u00e8re de segmenter la table en diff\u00e9rentes partitions (Le moteur Archive supporte le partitionnement<\/a>) pour des requ\u00eates plus rapides et avoir la possibilit\u00e9 de supprimer les donn\u00e9es par ann\u00e9e. Archiver la table accounting Freeradius est simple et rapide.<\/p>\n","protected":false},"excerpt":{"rendered":"
Enregistrer les sessions Radius, notamment accounting dans une base Mysql peut g\u00e9n\u00e9rer des tables de tr\u00e8s grosse taille. Des probl\u00e8mes de performance vont appara\u00eetre apr\u00e8s un certain temps, surtout si des scripts sont cr\u00e9\u00e9s pour g\u00e9n\u00e9rer des rapports sur le trafic utilis\u00e9 par les clients. C’est pourquoi j’ai ajout\u00e9 une nouvelle table pour enregistrer les […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false},"categories":[3,4],"tags":[16,403,407,404,9,7],"yoast_head":"\nNetexpertise - Archiver la Table Accounting Freeradius sous Mysql<\/title>\n<meta name=\"description\" content=\"Archivez la table accounting de Freeradius, gagnez de la place avec le moteur Archive, et conservez de bonnes performances\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/www.netexpertise.eu\/fr\/database\/mysql\/archiver-accounting.html\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Netexpertise - Archiver la Table Accounting Freeradius sous Mysql\" \/>\n<meta property=\"og:description\" content=\"Archivez la table accounting de Freeradius, gagnez de la place avec le moteur Archive, et conservez de bonnes performances\" \/>\n<meta property=\"og:url\" content=\"http:\/\/www.netexpertise.eu\/fr\/database\/mysql\/archiver-accounting.html\" \/>\n<meta property=\"og:site_name\" content=\"Netexpertise\" \/>\n<meta property=\"article:published_time\" content=\"2007-02-21T00:00:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-10-11T20:12:26+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.netexpertise.eu\/uploads\/archive-boxes-shelf-folders-1850170.jpg\" \/>\n<meta name=\"author\" content=\"dave\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@netexpertise\" \/>\n<meta name=\"twitter:site\" content=\"@netexpertise\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"http:\/\/www.netexpertise.eu\/fr\/database\/mysql\/archiver-accounting.html\",\"url\":\"http:\/\/www.netexpertise.eu\/fr\/database\/mysql\/archiver-accounting.html\",\"name\":\"Netexpertise - Archiver la Table Accounting Freeradius sous Mysql\",\"isPartOf\":{\"@id\":\"https:\/\/www.netexpertise.eu\/fr\/#website\"},\"datePublished\":\"2007-02-21T00:00:00+00:00\",\"dateModified\":\"2021-10-11T20:12:26+00:00\",\"author\":{\"@id\":\"https:\/\/www.netexpertise.eu\/fr\/#\/schema\/person\/e398f0307e2b167f6b884c4953be2632\"},\"description\":\"Archivez la table accounting de Freeradius, gagnez de la place avec le moteur Archive, et conservez de bonnes performances\",\"breadcrumb\":{\"@id\":\"http:\/\/www.netexpertise.eu\/fr\/database\/mysql\/archiver-accounting.html#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/www.netexpertise.eu\/fr\/database\/mysql\/archiver-accounting.html\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/www.netexpertise.eu\/fr\/database\/mysql\/archiver-accounting.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.netexpertise.eu\/fr\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Archiver la Table Accounting Freeradius sous Mysql\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.netexpertise.eu\/fr\/#website\",\"url\":\"https:\/\/www.netexpertise.eu\/fr\/\",\"name\":\"Netexpertise\",\"description\":\"Syst\u00e8mes \/ R\u00e9seaux \/ DevOps\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.netexpertise.eu\/fr\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.netexpertise.eu\/fr\/#\/schema\/person\/e398f0307e2b167f6b884c4953be2632\",\"name\":\"dave\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\/\/www.netexpertise.eu\/fr\/#\/schema\/person\/image\/\",\"url\":\"http:\/\/1.gravatar.com\/avatar\/1129916e1f4955bd632f27f836f64e55?s=96&d=mm&r=g\",\"contentUrl\":\"http:\/\/1.gravatar.com\/avatar\/1129916e1f4955bd632f27f836f64e55?s=96&d=mm&r=g\",\"caption\":\"dave\"},\"sameAs\":[\"http:\/\/www.netexpertise.eu\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Netexpertise - Archiver la Table Accounting Freeradius sous Mysql","description":"Archivez la table accounting de Freeradius, gagnez de la place avec le moteur Archive, et conservez de bonnes performances","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"http:\/\/www.netexpertise.eu\/fr\/database\/mysql\/archiver-accounting.html","og_locale":"fr_FR","og_type":"article","og_title":"Netexpertise - Archiver la Table Accounting Freeradius sous Mysql","og_description":"Archivez la table accounting de Freeradius, gagnez de la place avec le moteur Archive, et conservez de bonnes performances","og_url":"http:\/\/www.netexpertise.eu\/fr\/database\/mysql\/archiver-accounting.html","og_site_name":"Netexpertise","article_published_time":"2007-02-21T00:00:00+00:00","article_modified_time":"2021-10-11T20:12:26+00:00","og_image":[{"url":"http:\/\/www.netexpertise.eu\/uploads\/archive-boxes-shelf-folders-1850170.jpg"}],"author":"dave","twitter_card":"summary_large_image","twitter_creator":"@netexpertise","twitter_site":"@netexpertise","schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"http:\/\/www.netexpertise.eu\/fr\/database\/mysql\/archiver-accounting.html","url":"http:\/\/www.netexpertise.eu\/fr\/database\/mysql\/archiver-accounting.html","name":"Netexpertise - Archiver la Table Accounting Freeradius sous Mysql","isPartOf":{"@id":"https:\/\/www.netexpertise.eu\/fr\/#website"},"datePublished":"2007-02-21T00:00:00+00:00","dateModified":"2021-10-11T20:12:26+00:00","author":{"@id":"https:\/\/www.netexpertise.eu\/fr\/#\/schema\/person\/e398f0307e2b167f6b884c4953be2632"},"description":"Archivez la table accounting de Freeradius, gagnez de la place avec le moteur Archive, et conservez de bonnes performances","breadcrumb":{"@id":"http:\/\/www.netexpertise.eu\/fr\/database\/mysql\/archiver-accounting.html#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["http:\/\/www.netexpertise.eu\/fr\/database\/mysql\/archiver-accounting.html"]}]},{"@type":"BreadcrumbList","@id":"http:\/\/www.netexpertise.eu\/fr\/database\/mysql\/archiver-accounting.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.netexpertise.eu\/fr"},{"@type":"ListItem","position":2,"name":"Archiver la Table Accounting Freeradius sous Mysql"}]},{"@type":"WebSite","@id":"https:\/\/www.netexpertise.eu\/fr\/#website","url":"https:\/\/www.netexpertise.eu\/fr\/","name":"Netexpertise","description":"Syst\u00e8mes \/ R\u00e9seaux \/ DevOps","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.netexpertise.eu\/fr\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"fr-FR"},{"@type":"Person","@id":"https:\/\/www.netexpertise.eu\/fr\/#\/schema\/person\/e398f0307e2b167f6b884c4953be2632","name":"dave","image":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/www.netexpertise.eu\/fr\/#\/schema\/person\/image\/","url":"http:\/\/1.gravatar.com\/avatar\/1129916e1f4955bd632f27f836f64e55?s=96&d=mm&r=g","contentUrl":"http:\/\/1.gravatar.com\/avatar\/1129916e1f4955bd632f27f836f64e55?s=96&d=mm&r=g","caption":"dave"},"sameAs":["http:\/\/www.netexpertise.eu"]}]}},"_links":{"self":[{"href":"http:\/\/www.netexpertise.eu\/fr\/wp-json\/wp\/v2\/posts\/3"}],"collection":[{"href":"http:\/\/www.netexpertise.eu\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.netexpertise.eu\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.netexpertise.eu\/fr\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.netexpertise.eu\/fr\/wp-json\/wp\/v2\/comments?post=3"}],"version-history":[{"count":0,"href":"http:\/\/www.netexpertise.eu\/fr\/wp-json\/wp\/v2\/posts\/3\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.netexpertise.eu\/fr\/wp-json\/wp\/v2\/media?parent=3"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.netexpertise.eu\/fr\/wp-json\/wp\/v2\/categories?post=3"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.netexpertise.eu\/fr\/wp-json\/wp\/v2\/tags?post=3"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}