De ULBwiki

--Kamus Hadenes 14:11, 21 Abril 2008 (EDT)

MySQL

Acesse sua base MySQL remotamente via SSH

Imagine que você possui um banco de dados MySQL no seu servidor web, mas ele só pode ser acessado localmente por razões de segurança. Se você deseja acessar seu banco de dados remotamente, você tem que liberar acesso do seu atual IP, o que não é muito seguro.

Então, ao invés disso, você pode usar um port-forwarding através de um túnel SSH, então seu client MySQL pensa que está conectando em sua máquina local, mas na verdade está conectando de outro server através deste túnel.

Se você está usando o ssh via linha de comando, o comando deve se parecer com este.

ssh -L 3306:localhost:3306 geek@webserver.com

A sintaxe é ssh -L <portalocal>hostname<portaremota> <usuario>@<server>. Nós estamos usando localhost como hostname pois estamos remotamente acessando diretamente o MySQL via ssh. Você pode usar esta técnica também para um port-forward de um servidor ssh para outro.

Se você já possui um servidor MySQL rodando em sua máquina local você pode usas uma porta local diferente para o port-forwarding, e simplesmente setar seu client para acessar o servidor MySQL por uma porta diferente.

Após ter setado o túnel SSH você pode abrir seu MySQL Query Browser e entrar os detalhes para seu servidor remoto, usando localhost como host, e simplesmente ajustar a porta que você usou.

Usando um Script Analizador de Perfomance no MySQL

Download

Quando você está trabalhando para aumentar a perfomance do seu site, uma peça muito importante é ter certeza de que você está usando toda a perfomance que seu database pode oferecer. Infelizmente, para a maiaoria de nós que não somos administradores de database, esta pode ser uma proposta difícil.

Aqui está um script que irá analizar as configurações e os status do seu servidor e lhe recomendar alterações. Você não precisa seguir todas essas recomendações, mas é sempre bom dar uma olhada.

Este script lhe da recomendações sobre o seguinte:

1. Slow Query Log
2. Max Connections
3. Worker Threads
4. Key Buffer
5. Query Cache
6. Sort Buffer
7. Joins
8. Temp Tables
9. Table (Open & Definition) Cache
10. Table Locking
11. Table Scans (read_buffer)
12. Innodb Status

Após ter feito o download, você precisa torná-lo executável com o seguinte comando:

chmod u+x tuning-primer.sh

Se você rodar este script como um usuário normal, ele vai pedir pela sua senha, então tenha certeza de que você setou o acesso de acordo. Se você rodar como root ele vai pegar a password pelo Plesk, se você o tiver isntalado.

Eu cortei bastante output, pois ele não caberia na página.

./tuning-primer.sh
 
            — MYSQL PERFORMANCE TUNING PRIMER –
                 - By: Matthew Montgomery -
 
    MySQL Version 4.1.20 i686
 
    Uptime = 5 days 10 hrs 46 min 5 sec
    Avg. qps = 4
    Total Questions = 2020809
    Threads Connected = 1
 
    Server has been running for over 48hrs.
    It should be safe to follow these recommendations
 
    ———– snipped ————–
 
    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 8 M
    Current query_cache_used = 7 M
    Current query_cach_limit = 1 M
    Current Query cache fill ratio = 89.38 %
    However, 254246 queries have been removed from the query cache due to lack of memory
    Perhaps you should raise query_cache_size
    MySQL won't cache query results that are larger than query_cache_limit in size
 
    ———– snipped ————–

Parece que eu preciso aumentar meu cache do query. Eu setei ele para apenas 8MB.

———– snipped ————–
 
    TEMP TABLES
    Current max_heap_table_size = 16 M
    Current tmp_table_size = 32 M
    Of 35170 temp tables, 74% were created on disk
    Effective in-memory tmp_table_size is limited to max_heap_table_size.
    Perhaps you should increase your tmp_table_size and/or max_heap_table_size
    to reduce the number of disk-based temporary tables
    Note! BLOB and TEXT columns are not allow in memory tables.
    If you are using these columns raising these values might not impact your
    ratio of on disk temp tables.
 
    ———– snipped ————–

Essa informação é impagável quando você está aumentando a perfomance do seu site.

Rastreie Queries do MySQL com o mysqlsniffer

Você tem uma servidor database de produção, e você não pode habilitar o log do query, então como você verá os queries sdendo executados contra seu database?

Resposta: use um sniffer modificado para rastrear pacotes MySQL e decodificar eles. Você terá que fazer uma pequena compilação, mas não é nada difícil. Note que isso provavelmente não funcionará em conexões locais, mas não custa nada testar.

Primeiro, você precisa instalar o pacote libpcap-dev, que é uma biblioteca de desnvolvimento que permite que uma aplicação monitore pacotes na rede.

sudo apt-get install libpcap-dev

Agora vamos criar um diretório, fazer o download e compilar o programa.

mkdir mysqlsniffer
 
    cd mysqlsniffer
 
    wget hackmysql.com/code/mysqlsniffer.tgz
 
    tar xvfz mysqlsniffer.tgz
 
    gcc -O2 -lpcap -o mysqlsniffer mysqlsniffer.c packet_handlers.c misc.c

Neste ponto, nós temos um brilhante novo executável chamado mysqlsniffer no nosso diretório source. Você pode copiar para onde você quiser At this point, we have a shiny new executable named mysqlsniffer in our source directory. You can copy it wherever you like (recomendo que coloque em /bin)

Para rodar o sniffer, você precisa especificar a interface de rede em que o MySQL está ounvindo. Para mim é eth0.

sudo /path/to/mysqlsniffer eth0

O monitoramento já começou. Vamos filtrar mais um pouco para pularmos o lixo.

$ sudo /bin/mysqlsniffer –no-mysql-hdrs eth0 | grep COM_QUERY
 
    192.168.73.1.2622 > server: COM_QUERY: SELECT @@sql_mode
    192.168.73.1.2622 > server: COM_QUERY: SET SESSION sql_mode=''
    192.168.73.1.2622 > server: COM_QUERY: SET NAMES utf8
    192.168.73.1.1636 > server: COM_QUERY: SELECT @@SQL_MODE
    192.168.73.1.1636 > server: COM_QUERY: SHOW FULL COLUMNS FROM `db2842_howto`.`wp_users`

Ah, aqui estamos nós. Todo tipo de informação sem ter que reiniciar o MySQL.

Aqui estão as opções completas para o comando:

Usage: mysqlsniffer [OPTIONS] INTERFACE
 
OPTIONS:
–port N Listen for MySQL on port number N (default 3306)
–verbose Show extra packet information
–tcp-ctrl Show TCP control packets (SYN, FIN, RST, ACK)
–net-hdrs Show major IP and TCP header values
–no-mysql-hdrs Do not show MySQL header (packet ID and length)
–state Show state
–v40 MySQL server is version 4.0
–dump Dump all packets in hex
–help Print this

--Kamus Hadenes 14:11, 21 Abril 2008 (EDT)