{"id":325,"date":"2012-05-13T15:13:00","date_gmt":"2012-05-13T18:13:00","guid":{"rendered":"http:\/\/wagnerbianchi.com\/blog\/?p=325"},"modified":"2012-05-13T17:45:19","modified_gmt":"2012-05-13T20:45:19","slug":"agilizando-a-carga-de-dados-e-restore-no-mysql","status":"publish","type":"post","link":"http:\/\/wagnerbianchi.com\/blog\/?p=325","title":{"rendered":"Agilizando a carga de dados e restore no MySQL"},"content":{"rendered":"<p>Muitos s\u00e3o os amigos que escrevem perguntando como agilizar a carga de dados ou restore de um backup no MySQL. Realmente, dependendo do tamanho do seu hardware, configura\u00e7\u00e3o dos Storage Engines e vari\u00e1veis per-client e design do seu banco de dados, esse processo poder\u00e1 levar v\u00e1rias horas caso alguns cuidados n\u00e3o sejam tomados antes do in\u00edcio do processo. H\u00e1 pouco tempo atr\u00e1s, trabalhando em uma consultoria aonde o cliente precisava fazer uma carga di\u00e1ria de toda a movimenta\u00e7\u00e3o nas contas onde todas as informa\u00e7\u00f5es eram consistidas em arquivos texto, finalizamos a nossa presta\u00e7\u00e3o de servi\u00e7os ap\u00f3s termos desenvolvido um aplicativo que, al\u00e9m de fazer a carga dos dados e v\u00e1rios tratamentos em meio aos LOAD DATA INFILE, configuramos v\u00e1rios pontos do MySQL no runtime do aplicativo para que o processo fosse realmente &#8220;agilizado&#8221;.<\/p>\n<p>S\u00e3o v\u00e1rios os pontos a serem observados:<\/p>\n<ul>\n<li>\u00cdndices KEY, UNIQUE e FULLTEXT, para tabelas MyISAM;<\/li>\n<li>Chaves estrangeiras ou foreign keys, para tabelas InnoDB;<\/li>\n<li>o modo AUTOCOMMIT, para tabelas InnoDB.<\/li>\n<\/ul>\n<div>Para os testes que faremos neste post, utilizaremos uma m\u00e1quina virtual rodando o CentOS 6.0, com o MySQL 5.6.<\/div>\n<p><code>[root@master ~]# mysqladmin -u root -p123456 version<br \/>\nmysqladmin Ver 8.42 Distrib 5.6.4-m7, for Linux on i686<\/code><\/p>\n<p><code><br \/>\nCopyright (c) 2000, 2011, Oracle and\/or its affiliates. All rights reserved.<br \/>\nOracle is a registered trademark of Oracle Corporation and\/or its<br \/>\naffiliates. Other names may be trademarks of their respective<br \/>\nowners.<br \/>\n<\/code><\/p>\n<p><code>Server version 5.6.4-m7-log<br \/>\nProtocol version 10<br \/>\nConnection Localhost via UNIX socket<br \/>\nUNIX socket \/var\/lib\/mysql\/mysql.sock<br \/>\nUptime: 42 min 17 sec<\/code><\/p>\n<div>Para desabilitar \u00cdndices KEY e UNIQUE, basta que voc\u00ea crie um select ou mesmo um script para percorrer tabela por tabela do seu modelo f\u00edsico de bancos de dados para desabilitar os \u00edndices de cada uma delas. Gosto de fazer isso via mysql client com a op\u00e7\u00e3o -B mai\u00fasculo, que executa a conex\u00e3o com o mysqld em modo batch. Caso voc\u00ea tenha mais seguran\u00e7a em utilizar os recursos do MySQL em conjunto com o file system, voc\u00ea pode utilizar o SELECT &#8230; INTO OUTFILE.<\/div>\n<p><code># criamos as tabelas com \u00edndices KEY, ou seja, \u00edndices que s\u00e3o estruturas utilizadas para melhoria da performance na busca de dados<br \/>\n[root@master ~]# for i in {1..5}; do mysql -u root -p123456 test -e \"create table tb$i(id$i int,key(id$i)) engine=myisam;\"; done<\/code><\/p>\n<p><code> # exibimos as tabelas criadas<br \/>\n[root@master ~]# mysql -u root -p123456 -e \"show tables from test like 'tb%'\"<br \/>\n+----------------------+<br \/>\n| Tables_in_test (tb%) |<br \/>\n+----------------------+<br \/>\n| tb1 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|<br \/>\n| tb2 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|<br \/>\n| tb3 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|<br \/>\n| tb4 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|<br \/>\n| tb5 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|<br \/>\n+----------------------+<br \/>\n<\/code><\/p>\n<p><code># exibimos os \u00edndices criados nas colunas id das tabelas que acabamos de criar<br \/>\n[root@master ~]# mysql -u root -p123456 -e \"select column_name, column_key from information_schema.columns where table_schema='test' and table_name like 'tb%'\"<br \/>\n+-------------+------------+<br \/>\n| column_name | column_key |<br \/>\n+-------------+------------+<br \/>\n| id1 \u00a0 \u00a0 \u00a0 \u00a0 | MUL \u00a0 \u00a0 \u00a0 \u00a0|<br \/>\n| id2 \u00a0 \u00a0 \u00a0 \u00a0 | MUL \u00a0 \u00a0 \u00a0 \u00a0|<br \/>\n| id3 \u00a0 \u00a0 \u00a0 \u00a0 | MUL \u00a0 \u00a0 \u00a0 \u00a0|<br \/>\n| id4 \u00a0 \u00a0 \u00a0 \u00a0 | MUL \u00a0 \u00a0 \u00a0 \u00a0|<br \/>\n| id5 \u00a0 \u00a0 \u00a0 \u00a0 | MUL \u00a0 \u00a0 \u00a0 \u00a0|<br \/>\n+-------------+------------+<\/code><\/p>\n<div>Agora que temos \u00edndices \u00e0 desabilitar, podemos rodar um SELECT que nos devolver\u00e1 os comandos ALTER TABLE necess\u00e1rios para desabilitar os \u00edndices das tabelas do banco de dados alvo da carga de dados.<\/div>\n<p><code># executando em modo batch<br \/>\n[root@master ~]# mysql -u root -p123456 -B -e \"select concat('alter table ',table_name,' disable keys;') from information_schema.tables where table_schema='test'\"<br \/>\nconcat('alter table ',table_name,' disable_keys;')<br \/>\nalter table t1 disable keys;<br \/>\nalter table t2 disable keys;<br \/>\nalter table t3 disable keys;<br \/>\nalter table tb1 disable keys;<br \/>\nalter table tb2 disable keys;<br \/>\nalter table tb3 disable keys;<br \/>\nalter table tb4 disable keys;<br \/>\nalter table tb5 disable keys;<br \/>\n<\/code><\/p>\n<p><code># executando com SELECT ... INTO OUFILE<br \/>\n[root@master ~]# mysql -u root -p123456 -e \"select concat('alter table ',table_name,' disable keys;') into outfile '\/tmp\/alterDisableKey' from information_schema.tables where table_schema='test'\"<br \/>\n[root@master ~]#<\/code><\/p>\n<div>Considerando a segunda op\u00e7\u00e3o, volte ao mysql e execute o conte\u00fado do arquivo que foi salvo em \/tmp<\/div>\n<div><\/div>\n<div># executando o arquivo via source<\/div>\n<p><code>[root@master ~]# mysql -u root -p123456 test -e \"source \/tmp\/alterDisableKey;\"<br \/>\n# confirmando que os \u00edndices foram desabilitados<\/div>\n<p>mysql&gt; show index from tb1\\G<br \/>\n*************************** 1. row ***************************<br \/>\nTable: tb1<br \/>\nNon_unique: 1<br \/>\nKey_name: id1<br \/>\nSeq_in_index: 1<br \/>\nColumn_name: id1<br \/>\nCollation: A<br \/>\nCardinality: NULL<br \/>\nSub_part: NULL<br \/>\nPacked: NULL<br \/>\nNull: YES<br \/>\nIndex_type: BTREE<br \/>\n<strong>Comment: disabled # desabilitado!<\/strong><br \/>\nIndex_comment:<br \/>\n1 row in set (0.00 sec)<\/code><\/p>\n<p>Ap\u00f3s realizar a carga de dados, ALTER TABLE &lt;table_name&gt; ENABLE KEYS!<\/p>\n<p>Para que as foreign keys ou chaves estrangeiras em tabelas InnoDB tenham suas checagens desabilitadas (o processo de checagem de integridade referencial realmente atrasa o restore de dados) \u00e9 um processo mais tranquilo que o anterior. Basta que voc\u00ea, na sua sess\u00e3o, reconfigure o valor da vari\u00e1vel de ambiente foreign_key_checks, como vemos logo abaixo:<\/p>\n<p><code>mysql&gt; show variables like 'foreign%';<br \/>\n+--------------------+-------+<br \/>\n| Variable_name \u00a0 \u00a0 \u00a0| Value |<br \/>\n+--------------------+-------+<br \/>\n| foreign_key_checks | ON \u00a0 \u00a0|<br \/>\n+--------------------+-------+<br \/>\n1 row in set (0.00 sec)<br \/>\n<\/code><\/p>\n<p><code>mysql&gt; SET FOREIGN_KEY_CHECKS=OFF;<br \/>\nQuery OK, 0 rows affected (0.05 sec)<\/code><\/p>\n<p><code> mysql&gt; SET FOREIGN_KEY_CHECKS=0;<br \/>\nQuery OK, 0 rows affected (0.00 sec)<br \/>\n<\/code><\/p>\n<p><code>mysql&gt; show variables like 'foreign%';<br \/>\n+--------------------+-------+<br \/>\n| Variable_name \u00a0 \u00a0 \u00a0| Value |<br \/>\n+--------------------+-------+<br \/>\n| foreign_key_checks | OFF \u00a0 |<br \/>\n+--------------------+-------+<br \/>\n1 row in set (0.00 sec)<\/code><\/p>\n<p>O ponto final para finalizarmos este post, o AUTOCOMMIT! Primeiro, vamos entender o que esse cara faz e o que ele controla. Como o InnoDB \u00e9 um Storage Engine transacional, a cada UPDATE, INSERT ou DELETE que \u00e9 executado, o InnoDB cuida para enviar um COMMIT logo ap\u00f3s tais consultas; isso, quando AUTOCOMMIT est\u00e1 configurado como 1 ou ON, que \u00e9 o valor default. Como queremos fazer v\u00e1rias opera\u00e7\u00f5es e somente ao final dar um COMMIT expl\u00edcito, o que \u00e9 feito pelo mysqldump com um arquivo de backup gerado com a op\u00e7\u00e3o -e, precisamos configurar o AUTOCOMMIT com o valor OFF ou 0.<\/p>\n<p><code># configurando autocomit no arquivo de configura\u00e7\u00e3o do MySQL, salev o mesmo e reinicie o MySQL<br \/>\n[root@master ~]# vim \/etc\/my.cnf<\/code><\/p>\n<p><code> [mysqld]<br \/>\nautocommit=0<br \/>\n<\/code><\/p>\n<p><code>[root@master ~]# service mysql restart<br \/>\nShutting down MySQL ... [ OK ]<br \/>\nStarting MySQL \u00a0 \u00a0 \u00a0... [ OK ]<\/code><\/p>\n<p>Pronto, agora o seu servidor de bancos de dados MySQL j\u00e1 est\u00e1 configurado para passar por processos de restore de forma mais r\u00e1pida e tamb\u00e9m ser alvo de cargas de dados pesadas. Um adicional \u00e9, procure saber como funciona a vari\u00e1vel <a title=\"WBConsulting - MySQL Performance Tuning\" href=\"http:\/\/www.slideshare.net\/wbcon\/wbconsulting-faminas-performancetuning\" target=\"_blank\">bulk_insert_buffer_size<\/a>, ela tamb\u00e9m ajudar\u00e1 neste quesito.<\/p>\n<p>At\u00e9.<\/p>\n<div><\/div>\n<div><\/div>\n<div><\/div>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Muitos s\u00e3o os amigos que escrevem perguntando como agilizar a carga de dados ou restore de um backup no MySQL. Realmente, dependendo do tamanho do seu hardware, configura\u00e7\u00e3o dos Storage Engines e vari\u00e1veis per-client e design do seu banco de dados, esse processo poder\u00e1 levar v\u00e1rias horas caso alguns cuidados n\u00e3o sejam tomados antes do [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[3,8,17,1],"tags":[],"_links":{"self":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/325"}],"collection":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=325"}],"version-history":[{"count":7,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/325\/revisions"}],"predecessor-version":[{"id":333,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/325\/revisions\/333"}],"wp:attachment":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=325"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=325"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=325"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}