{"id":4352,"date":"2016-11-21T20:17:23","date_gmt":"2016-11-21T20:17:23","guid":{"rendered":"http:\/\/www.deuzebranaweb.com.br\/?p=4352"},"modified":"2016-11-21T20:17:23","modified_gmt":"2016-11-21T20:17:23","slug":"instalando-e-otimizando-o-mysql-para-alto-trafego-de-dados","status":"publish","type":"post","link":"https:\/\/blog.deuzebranaweb.com.br\/index.php\/2016\/11\/21\/instalando-e-otimizando-o-mysql-para-alto-trafego-de-dados\/","title":{"rendered":"Instalando e otimizando o MySQL para alto tr\u00e1fego de dados"},"content":{"rendered":"

Instalando o MySQL<\/h2>\n

Realize o download e descompacta\u00e7\u00e3o do MySQL com os comandos abaixo<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n<\/td>\n
\n
\n
wget http:<\/code>\/\/cdn<\/code>.mysql.com<\/code>\/archives\/mysql-5<\/code>.5<\/code>\/mysql-5<\/code>.5.47-linux2.6-x86_64.<\/code>tar<\/code>.gz<\/code><\/div>\n
tar<\/code> -zxvf mysql-5.5.47-linux2.6-x86_64.<\/code>tar<\/code>.gz -C <\/code>\/usr\/local<\/code><\/div>\n
ln<\/code> -sf <\/code>\/usr\/local\/mysql-5<\/code>.5.47-linux2.6-x86_64 <\/code>\/usr\/local\/mysql<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Copie o script de inicializa\u00e7\u00e3o do MySQL<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n<\/td>\n
\n
\n
cp<\/code> \/usr\/local\/mysql\/support-files\/mysql<\/code>.server <\/code>\/etc\/init<\/code>.d<\/code>\/mysql<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Edite o arquivo\u00a0\/etc\/init.d\/mysql<\/strong> alterando as linhas conforme exemplo abaixo<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n<\/td>\n
\n
\n
basedir=<\/code>\/usr\/local\/mysql<\/code><\/div>\n
datadir=<\/code>\/databases\/mysql\/bases<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Crie o arquivo\u00a0\/databases\/mysql\/my.cnf<\/strong> \u00a0com o conte\u00fado abaixo<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n
14<\/div>\n
15<\/div>\n
16<\/div>\n
17<\/div>\n
18<\/div>\n
19<\/div>\n
20<\/div>\n
21<\/div>\n
22<\/div>\n
23<\/div>\n
24<\/div>\n
25<\/div>\n
26<\/div>\n
27<\/div>\n
28<\/div>\n
29<\/div>\n
30<\/div>\n
31<\/div>\n
32<\/div>\n
33<\/div>\n
34<\/div>\n
35<\/div>\n
36<\/div>\n
37<\/div>\n
38<\/div>\n
39<\/div>\n
40<\/div>\n
41<\/div>\n
42<\/div>\n
43<\/div>\n
44<\/div>\n
45<\/div>\n
46<\/div>\n
47<\/div>\n
48<\/div>\n
49<\/div>\n
50<\/div>\n
51<\/div>\n
52<\/div>\n
53<\/div>\n
54<\/div>\n
55<\/div>\n
56<\/div>\n
57<\/div>\n
58<\/div>\n
59<\/div>\n
60<\/div>\n
61<\/div>\n
62<\/div>\n
63<\/div>\n
64<\/div>\n
65<\/div>\n
66<\/div>\n
67<\/div>\n
68<\/div>\n
69<\/div>\n
70<\/div>\n
71<\/div>\n
72<\/div>\n
73<\/div>\n
74<\/div>\n
75<\/div>\n
76<\/div>\n
77<\/div>\n
78<\/div>\n
79<\/div>\n
80<\/div>\n
81<\/div>\n
82<\/div>\n
83<\/div>\n
84<\/div>\n
85<\/div>\n
86<\/div>\n
87<\/div>\n
88<\/div>\n
89<\/div>\n
90<\/div>\n
91<\/div>\n
92<\/div>\n
93<\/div>\n
94<\/div>\n
95<\/div>\n
96<\/div>\n
97<\/div>\n
98<\/div>\n
99<\/div>\n
100<\/div>\n
101<\/div>\n
102<\/div>\n
103<\/div>\n
104<\/div>\n
105<\/div>\n
106<\/div>\n
107<\/div>\n
108<\/div>\n
109<\/div>\n
110<\/div>\n
111<\/div>\n
112<\/div>\n
113<\/div>\n
114<\/div>\n
115<\/div>\n
116<\/div>\n
117<\/div>\n
118<\/div>\n
119<\/div>\n
120<\/div>\n
121<\/div>\n
122<\/div>\n
123<\/div>\n
124<\/div>\n
125<\/div>\n
126<\/div>\n
127<\/div>\n
128<\/div>\n
129<\/div>\n
130<\/div>\n
131<\/div>\n
132<\/div>\n<\/td>\n
\n
\n
## Helvio Junior \u2013 my.cnf template<\/code><\/div>\n
### my.cnf<\/code><\/div>\n
<\/div>\n
[client]<\/code><\/div>\n
port\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = 3306<\/code><\/div>\n
socket\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = <\/code>\/databases\/mysql\/mysql<\/code>.sock<\/code><\/div>\n
<\/div>\n
[mysqld_safe]<\/code><\/div>\n
open_files_limit = 1024000<\/code><\/div>\n
basedir\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = <\/code>\/usr\/local\/mysql<\/code><\/div>\n
timezone\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = America<\/code>\/Sao_Paulo<\/code><\/div>\n
socket\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = <\/code>\/databases\/mysql\/mysql<\/code>.sock<\/code><\/div>\n
nice<\/code>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = 0<\/code><\/div>\n
<\/div>\n
# Diret\u00f3rio de logs e consultas lentas<\/code><\/div>\n
log-error\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = <\/code>\/databases\/mysql\/logs\/error<\/code>.log<\/code><\/div>\n
pid-<\/code>file<\/code>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = <\/code>\/databases\/mysql\/bases\/mysql<\/code>.pid<\/code><\/div>\n
<\/div>\n
[mysqld]<\/code><\/div>\n
open_files_limit = 1024000<\/code><\/div>\n
general_log\u00a0\u00a0\u00a0\u00a0 = 0<\/code><\/div>\n
log_warnings\u00a0\u00a0\u00a0 = 1<\/code><\/div>\n
general_log_file = <\/code>\/databases\/mysql\/logs\/mysqld<\/code>.log<\/code><\/div>\n
log-error\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = <\/code>\/databases\/mysql\/logs\/error<\/code>.log<\/code><\/div>\n
log-slow-admin-statements = 0<\/code><\/div>\n
<\/div>\n
#Consultas lentas<\/code><\/div>\n
long_query_time\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = 5<\/code><\/div>\n
log-slow-queries\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = <\/code>\/databases\/mysql\/logs\/mysql-slow<\/code>.log<\/code><\/div>\n
log-queries-not-using-indexes = 0<\/code><\/div>\n
<\/div>\n
user\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = mysql<\/code><\/div>\n
pid-<\/code>file<\/code>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = <\/code>\/databases\/mysql\/bases\/mysql<\/code>.pid<\/code><\/div>\n
socket\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = <\/code>\/databases\/mysql\/mysql<\/code>.socket<\/code><\/div>\n
port\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = 3306<\/code><\/div>\n
basedir\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = <\/code>\/usr\/local\/mysql<\/code><\/div>\n
datadir\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = <\/code>\/databases\/mysql\/bases\/<\/code><\/div>\n
tmpdir\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = <\/code>\/databases\/mysql\/tmpdir<\/code><\/div>\n
lc-messages-<\/code>dir<\/code> = <\/code>\/usr\/local\/mysql\/share\/<\/code><\/div>\n
<\/div>\n
# Performance Analsys<\/code><\/div>\n
performance_schema = off<\/code><\/div>\n
<\/div>\n
# Desativa o LOAD FILE<\/code><\/div>\n
local<\/code>-infile = 0<\/code><\/div>\n
old_passwords=0<\/code><\/div>\n
<\/div>\n
# 0x = MASTER<\/code><\/div>\n
# 1x = Slave Level 1<\/code><\/div>\n
# 2x = Slave em baixo de Slave<\/code><\/div>\n
server-<\/code>id<\/code>=01<\/code><\/div>\n
<\/div>\n
# Master Setup (Caso tenha replica\u00e7\u00e3o)<\/code><\/div>\n
#binlog_format = ROW<\/code><\/div>\n
#log-bin\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = \/databases\/mysql\/binlog\/mysql-bin<\/code><\/div>\n
#log_slave_updates = 1<\/code><\/div>\n
#log_bin_trust_function_creators = 1<\/code><\/div>\n
#expire_logs_days = 1<\/code><\/div>\n
<\/div>\n
# Configuracoes Diversas<\/code><\/div>\n
#Compatibilidade<\/code><\/div>\n
sql_mode = <\/code>''<\/code><\/div>\n
<\/div>\n
skip-name-resolve<\/code><\/div>\n
max_connections = 10000<\/code><\/div>\n
query_cache_size = 80M<\/code><\/div>\n
query_cache_min_res_unit = 2K<\/code><\/div>\n
query_cache_type = 1<\/code><\/div>\n
sort_buffer_size = 2M<\/code><\/div>\n
read_buffer_size = 128k<\/code><\/div>\n
join_buffer_size = 5M<\/code><\/div>\n
myisam_sort_buffer_size = 128M<\/code><\/div>\n
bulk_insert_buffer_size = 128M<\/code><\/div>\n
max_allowed_packet = 1G<\/code><\/div>\n
thread_cache_size = 100<\/code><\/div>\n
table_cache = 512<\/code><\/div>\n
<\/div>\n
# Tabelas tempor\u00e1rias<\/code><\/div>\n
# Configura o tamanho maximo para tabela do tipo MEMORY<\/code><\/div>\n
max_heap_table_size = 1G<\/code><\/div>\n
<\/div>\n
# Configura o tamanho maximo antes de converter para MyISAM<\/code><\/div>\n
tmp_table_size = 1G<\/code><\/div>\n
<\/div>\n
# Federated Store Engine<\/code><\/div>\n
federated<\/code><\/div>\n
<\/div>\n
# MyISAM Store Engine<\/code><\/div>\n
# depreciado...<\/code><\/div>\n
key_buffer = 256M<\/code><\/div>\n
myisam_repair_threads = 1<\/code><\/div>\n
myisam_recover = FORCE<\/code><\/div>\n
<\/div>\n
# InnoDB (Default)<\/code><\/div>\n
<\/div>\n
# Depreciado na 5.6 - Armazena dicionario de dados na ram<\/code><\/div>\n
innodb_additional_mem_pool_size = 16M<\/code><\/div>\n
<\/div>\n
# BUFFER POOL<\/code><\/div>\n
#<\/code><\/div>\n
<\/div>\n
# Alterar este valor para +- 80 da mem\u00f3ria do servidor<\/code><\/div>\n
innodb_buffer_pool_size = 1G<\/code><\/div>\n
<\/div>\n
# Segregacao do buffer_pool - Performance para algoritmo LRU (qtd cpu)<\/code><\/div>\n
innodb_buffer_pool_instance = 6<\/code><\/div>\n
<\/div>\n
# Redo Log<\/code><\/div>\n
innodb_log_buffer_size = 1G<\/code><\/div>\n
innodb_log_group_home_dir = <\/code>\/databases\/mysql\/innolog<\/code><\/div>\n
innodb_log_files_in_group = 7<\/code><\/div>\n
innodb_log_file_size = 512M<\/code><\/div>\n
<\/div>\n
# Manipulacao de arquivos<\/code><\/div>\n
innodb_open_files = 1024000<\/code><\/div>\n
innodb_file_per_table = 1<\/code><\/div>\n
innodb_data_file_path = ibdata1:1G:autoextend<\/code><\/div>\n
innodb_data_home_dir=<\/code>\/databases\/mysql\/bases\/<\/code><\/div>\n
<\/div>\n
# O_DIRECT para fazer by-pass (O EBS controla)<\/code><\/div>\n
innodb_flush_method = O_DIRECT<\/code><\/div>\n
innodb_file_format = BARRACUDA<\/code><\/div>\n
<\/div>\n
# QTD de IOPS que esta dispon\u00edvel para o datadir<\/code><\/div>\n
innodb_io_capacity = 1000<\/code><\/div>\n
<\/div>\n
# Controle Transacional<\/code><\/div>\n
transaction-isolation=READ-COMMITTED<\/code><\/div>\n
innodb_support_xa = 0<\/code><\/div>\n
<\/div>\n
# Qtd de segundos antes de um Lock wait timeout exceeded<\/code><\/div>\n
innodb_lock_wait_timeout = 120<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Altere o par\u00e2metro\u00a0innodb_buffer_pool_size<\/strong> deste arquivo rec\u00e9m criado para um valor de +- 80% da mem\u00f3ria do ser servidor.<\/p>\n

Crie um link simb\u00f3lico do arquivo de configura\u00e7\u00e3o para o diret\u00f3rio \/etc<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n<\/td>\n
\n
\n
ln<\/code> -s <\/code>\/databases\/mysql\/my<\/code>.cnf <\/code>\/etc\/my<\/code>.cnf<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Crie as bases de dados iniciais do MySQL<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n<\/td>\n
\n
\n
cd<\/code> \/usr\/local\/mysql<\/code><\/div>\n
scripts<\/code>\/mysql_install_db<\/code> --datadir=<\/code>\/databases\/mysql\/bases\/<\/code> --basedir=<\/code>\/usr\/local\/mysql<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Configure para que o MySQL inicie automaticamente na inicializa\u00e7\u00e3o do sistema operacional e inicie o servi\u00e7o<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n<\/td>\n
\n
\n
update-rc.d -f mysql defaults<\/code><\/div>\n
service mysql start<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Por quest\u00f5es de seguran\u00e7a, altere a senha padr\u00e3o do usu\u00e1rio root do banco de dados<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n<\/td>\n
\n
\n
\/usr\/local\/mysql\/bin\/mysqladmin<\/code> -u root password <\/code>'new-password'<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Pronto, o seu servidor de MySQL est\u00e1 instalado e pronto para uso.<\/p>\n

Caso deseje outras otimiza\u00e7\u00f5es n\u00e3o citadas neste post segue uma sugest\u00e3o se refer\u00eancia para consulta:\u00a0http:\/\/blog.neweb.co\/pt\/how-to-optimize-a-mysql-server\/<\/a><\/p>\n

 <\/p>\n

http:\/\/www.helviojunior.com.br\/it\/mysql\/instalando-e-otimizando-mysql-para-alto-trafego-de-dados\/<\/p>\n","protected":false},"excerpt":{"rendered":"

Instalando o MySQL Realize o download e descompacta\u00e7\u00e3o do MySQL com os comandos abaixo 1 2 3 wget http:\/\/cdn.mysql.com\/archives\/mysql-5.5\/mysql-5.5.47-linux2.6-x86_64.tar.gz tar -zxvf mysql-5.5.47-linux2.6-x86_64.tar.gz -C \/usr\/local ln -sf \/usr\/local\/mysql-5.5.47-linux2.6-x86_64 \/usr\/local\/mysql Copie o script de inicializa\u00e7\u00e3o do MySQL 1 cp \/usr\/local\/mysql\/support-files\/mysql.server \/etc\/init.d\/mysql Edite o arquivo\u00a0\/etc\/init.d\/mysql alterando as linhas…<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_angie_page":false,"page_builder":"","footnotes":""},"categories":[13],"tags":[],"class_list":["post-4352","post","type-post","status-publish","format-standard","hentry","category-msql"],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/blog.deuzebranaweb.com.br\/index.php\/wp-json\/wp\/v2\/posts\/4352","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.deuzebranaweb.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.deuzebranaweb.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.deuzebranaweb.com.br\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.deuzebranaweb.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=4352"}],"version-history":[{"count":0,"href":"https:\/\/blog.deuzebranaweb.com.br\/index.php\/wp-json\/wp\/v2\/posts\/4352\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.deuzebranaweb.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=4352"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.deuzebranaweb.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=4352"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.deuzebranaweb.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=4352"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}