MySQL ORDER BY RAND() – Qual a melhor forma de fazer?

Essa é uma dúvida que volta e meia aparece nos fóruns, como obter linhas aleatórias em uma query. Normalmente o que vemos é algo assim:

SELECT * FROM tabela ORDER BY rand();

Contudo, o que muitos não sabem é que uma query dessa forma causa um grande impacto na performance do SGBD.

A maneira correta para ordenar os resultados por um número aleatório é:

SELECT *, RAND() as rand FROM tabela ORDER BY rand;

Por quê? Bem, a resposta é realmente muito simples, ORDER BY RAND () faz com que a ordem seja recalculada a cada vez que é buscado uma nova linha pois cada linha RAND () retorna um valor diferente.

Quando usado na seleção o valor é calculado apenas uma vez por linha e os resultados só são ordenados.

Inspiração:
http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/

Alterando o encoding de um banco Postgresql

Bem, essa dica não serve para converter um banco já existente de UTF-8 para LATIN1, para isso existem outras técnicas. A idéia apresentada aqui é para quando você tem um Dump LATIN1 e precisa restaurar em um novo servidor com o mesmo encoding, mas  o Locale de seu Sistema Operacional não permite que você crie um banco LATIN1.

O erro de Locale aparece quando você executa:

# CREATE DATABASE xpto ENCODING ‘latin1’ TEMPLATE template0;
ERROR: encoding “LATIN1” does not match locale “pt_BR.UTF-8”
DETAIL: The chosen LC_CTYPE setting requires encoding “UTF8”.

Ocorre devido ao Locale estar configurado para UTF-8, você pode corrigir o locale, mas também pode simplesmente criar o banco em UTF-8 e depois alterar para LATIN1

# CREATE DATABASE xpto TEMPLATE template0;

Assim você acabou de criar o banco xpto com encoding UTF-8, para conferir basta dar um \l no PSQL.

Agora para alterar para LATIN1 basta executar:
# update pg_database set encoding = pg_char_to_encoding(‘LATIN1’) where datname = ‘xpto’;

Pronto, confira novamente com \l que o encoding já esta alterado.

Agora é só restaurar seu dump.

Habilitando acesso remoto ao servidor Mysql

Howto: Habilitando acesso remoto ao servidor Mysql

Dica velha que de vez em quando é necessário relembrar, por isso vou deixar registrado aqui para não ter que ficar procurando no google sempre que precisar.

Executar todos os passos abaixo como root:

1 – Edite o arquivo:
/etc/mysql/my.cnf

2 – Altere a seguinte linha:
bind-address = 127.0.0.1
Deixando assim:
bind-address = 0.0.0.0

3 – Reinicie o Mysql
# /etc/init.d/mysql restart

4 – Vamos agora dar GRANT no usuário root, logue no mysql:
# mysql -u root -p

5 – Após se logar, digite o seguinte comando:
GRANT ALL ON *.* TO root@’%’ IDENTIFIED BY ‘sua_senha’;

Dessa forma você libera o acesso ao seu servidor Mysql vindo de qualquer máquina externa, caso queira liberar somente o acesso da sua máquina, é só verificar qual é seu IP e entrar com o comando:

GRANT ALL ON *.* TO root@’192.168.0.2′ IDENTIFIED BY ‘sua_senha‘;

6. Após isso, basta dar uma reiniciada novamente no Mysql e realiazar a conexão remota.
# /etc/init.d/mysql restart

====================================================

Caso você queira desfazer o acesso remoto é bem simples também.

1 – Altere a seguinte linha:
bind-address = 0.0.0.0
Deixando assim:
bind-address = 127.0.0.1

2. Logue no Mysql:
# mysql -u root -p

3. Delete todos os privilégios remotos:
DELETE FROM mysql.user WHERE User = ‘root’ AND Host = ‘%’;
FLUSH PRIVILEGES;

4. Reinicie o Mysql e pronto, não estará mais acessível remotamente.

 
Fontes:
http://darkstrikerd.wordpress.com/
http://www.cyberciti.biz/
http://ptankov.wordpress.com/

Script de Backup (dump) Postgresql – Com bancos a escolher

Depois que publiquei o artigo Script de Backup (dump) Postgresql – Múltiplos bancos em arquivos separados, onde por meio de um script shell, é gerados dumps de todos os bancos existentes no servidor, cada banco um arquivo separado e compactado, me perguntaram se era possível, com a mesma idéia fazer um script que ao invés de realizar o dump de todas as bases, fosse possível definir quais bancos seriam backupiados.

Pois bem, é bem simples de fazer isso, vejamos:

#!/bin/bash
#
# Um simples script de backup
# Autor: Jonas Ferreira

# db_array - lista dos bancos que se deseja fazer o dump
db_array="banco1 banco2 banco3 banco4"

# logfile - arquivo que grava o log de cada execucao do script
logfile="/tmp/pgsql-backup.log"

#Diretorio de destino do arquivos
DIR=/tmp/backup

for db in $db_array
do
        /usr/bin/pg_dump $db > "$DIR/$db.sql" 1>> $logfile 2>> $logfile
        tar zcvf "$DIR/$db.tgz" "$DIR/$db.sql"
        rm -rf "$DIR/$db.sql"
done

Simples assim!

Script de Backup (dump) Mysql – Múltiplos bancos em arquivos separados

Com certeza você já deve conhecer diversas maneiras de realizar backup de sua base Mysql. Mas o objetivo deste howto é apenas uma variação de um script clássico de backup.

Vamos ao problema:

A maioria das pessoas utilizam este comando para backup:

mysqldump -u root -pPASSWORD –all-databases | gzip > /mnt/database`data ‘ %m-%d-%Y’`.sql.gz

Isso vai gerar um backup de todos os bancos de dados de seu SGBD e compactar com o nome de data 03/22/2012.sql.gz. O problema esta ai, pois ele irá gerar todas as querys em um único arquivo, ou seja, se você quiser restaurar um único banco de dados, terá que procurar o trecho no dump que corresponde ao banco desejado.

A solução apresentada abaixo é uma alternativa, onde a partir de um laço, lê todos os elementos do SGBD, e gera um dump já compactado de cada banco, separadamente.

#!/bin/bash
# backup mysql com --all-databases, mas separados cada banco em um único arquivo


DIR="/var/arquivos_backup/mysql"
USER="root"
PASSWORD="senha"
OUTPUTDIR="/var/lib/databases"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"

# elimando arquivos de backup antigos
rm -rf $DIR/*

# lendo a lista de banco de dados
databases=`$MYSQL --user=$USER --password=$PASSWORD \
-e "SHOW DATABASES;" | tr -d "| " | grep -Ev '(Database|information_schema)'`

# realizando o dump e compactando cada arquivo gerado
for db in $databases; do
echo $db
$MYSQLDUMP --force --opt --user=$USER --password=$PASSWORD \
--databases $db | gzip > "$OUTPUTDIR/$db.gz"
done

Com essa idéia, pode-se editar um pouco mais este script, como por exemplo compactar a pasta com todos os arquivos gerados, e já transferir para seu storage com Active Directory, por exemplo.

Aqui disponibilizo para download um script exatamente com essa idéia em prática.

Script de Backup – Mysql – Com depósito no Storage – AD

Depois é só colocar no CronTab.

Referência: snowfrog.net

Remover árvore de diretórios em uma compactação TAR

Se você trabalha com servidores Linux, provavelmente trabalha com processos de backups personalizados para todos os seus dados importantes. Sem dúvida as cópias de segurança acabam sendo armazenadas na forma de um arquivo.tar. Tudo funciona muito bem, as cópias são feitas, comprimidas e enviadas para um storage externo. Há apenas uma questão, sempre que você abrir um desses backups, ele irá incluir toda a árvore de diretórios acima da pasta onde os arquivos foram gerados.

Esse “problema” sempre me deixou chateado quando da criação de dumps de banco de dados. Por exemplo,vamos considerar que meu script de backup gere os arquivos de dumps e salve no diretório: /var/lib/mysql/meu_banco. Eu gostaria de fazer um backup TAR do diretório meu_banco, e chama-lo de meu_banco.tar.gz.

Acontece que quando vou descompactar o arquivo meu_banco.tar.gz, eu vou ter um monte de subpastas inúteis, eu vou ter que fazer drill-down, através da raíz onde eu descompactar, até /var/lib/mysql/meu_banco. Tudo que eu realmente quero é o diretório meu_banco, os demais (/var/lib/mysql) não me interessa.

Veja como é simples se livras desses diretórios. Antes de executar o comando TAR, primeiro você deve usar o comando CD para se mover até o diretório pai que contém o diretório que você deseja, não dentro da pasta que você irá compactar, no exemplo acima, você deve estar dentro de /var/lib/mysql e não dentro de /var/lib/mysql/meu_banco.

Eis a seqüência de comando para exemplo anterior:

cd /var/lib/mysql
tar -czf /pasta/onde/voce/ira/armazenar/meu_banco.tar.gz  meu_banco

Com isso, ao descompactar o meu_banco.tar.gz terá apenas um único subdiretório, chamado meu_banco.

Agora, provavelmente você vai executar esses comandos em um script maior de backup, neste caso o comando CD direto não irá funcionar, ainda mais se o script estiver no /etc/init.d/script_bkp.

Felizmente, há uma solução fácil: você pode acrescentar vários comandos shell em conjunto, utilizando && . Com && , cada comando será executado e concluído antes do próximo começar, simulando a digitação de comandos em uma linha.

cd /var/lib/mysql && tar -czf /pasta/onde/voce/ira/armazenar/meu_banco.tar.gz meu_banco

Pronto. 🙂

Adicionando um novo usuário no MySQL

Sei que este é um assunto muito simples, ainda mais se você já tiver o PhpMyAdmin instalado, mas como nem sempre podemos instalar essa ferramenta, o legal é lembrar como adicionar os usuários direto no terminal. Na verdade sempre preciso disso e nunca lembro a sintaxe.

Este procedimento funciona com MySQL 4.x e 5.x

1. Considerando que você já tenha o Mysql instalado e rodando 100%.

mysql --user=root mysql

Se sua conta root solicitar senha, você pode acrescentar o parâmetro -p ou –password

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 81
Server version: 5.1.61 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

2. Criando usuário com acesso full
Depois de conectar, é só executar os comandos para criar o usuário e senha, bem como a definição de privilégios.

mysql> CREATE USER 'bkpiador'@'localhost' IDENTIFIED BY 'a3$b2#c1!';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'bkpiador'@'localhost' WITH GRANT OPTION;

Vejamos o que estamos fazendo:

Na linha 1 é criado o usuário (com acesso permitido somente de localhost) e a senha de acesso.
Na linha 2 define-se as políticas de acesso do usuário, nesse exemplo, o usuário tem permissão para acessar tudo e somente apartir de localhost.

mysql> CREATE USER 'bkpiador'@'%' IDENTIFIED BY 'a3$b2#c1!';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'bkpiador'@'%' WITH GRANT OPTION;

Novamente, na linha 1 é criado o usuário, (agora para acesso de qualquer host) e a senha.
E na linha 2 o usuário tem permissão para acessar tudo apartir de qualquer host.

Howto: Gerando Dump do Postgresql e transferindo o arquivo para uma máquina com Windows Server

 

Howto: Gerando Dump do Postgresql e transferindo o arquivo para uma máquina com Windows Server

O objetivo deste howto não é discutir qual é a melhor forma de realizar um backup PostgreSQL, mas sim propor uma forma de realizar um Dump da base de dados em um servidor Linux e transferir para um servidor de arquivos que rode Windows, isso é muito comum em empresas e instituições, onde muitas vezes no servidor Windows encontra-se instalada algumas ferramentas de terceiro para gerenciar fitas, robôs e etc.

Não custa citar o ótimo artigo do Fábio Telles Rodriguez, onde o mesmo deixa claro que Dump não é considerado a melhor forma de realizar um backup. Vamos lá.

Imaginemos o seguinte cenário:

  • Uma rede controlada por um Windows Active Directory – AD (Windows 2003 ou maior);
  • Um servidor de arquivos na rede, que pode estar rodando na mesma máquina do AD ou em outra;
  • Servidor Linux (Ubuntu ou outro qualquer) e rodando o SGBD PostgreSQL 8.x;

A idéia é o seguinte:

  • Uma vez por dia o SGBD PostgreSQL irá gerar um Dump da base de dados, através de uma tarefa agendada na Crontab.
  • O dump gerado será armazenado em um diretório na mesma máquina do SGBD (não é uma boa prática deixar na mesma máquina) e uma cópia do mesmo será enviado para o servidor de arquivos Windows.

Mãos a massa:

  1. Vamos considerar que o servidor Linux seja Ubuntu (ou debian) . Se estiver utilizando CentOS  é só instalar os mesmos pacotes pelo Yum Instal;

Instalando os requisitos:

# apt-get install samba samba-common smbfs smbclient

Criando a pasta que servirá de montagem:

# mkdir /mnt/ntserver

No servidor de domínio, Windows Server – Active Directory, você deve ter um usuário com privilégio de escrita na pasta compartilhada. Basta executar a linha abaixo para testar a montagem:

# mount -t smbfs //192.168.0.10/pasta_compartilhada /mnt/ntserver -o username=usuario_de_dominio,password=senha_de_dominio

Claro trocando os termos em negrito para os da sua rede.

# ls /mnt/ntserver

Com este comando deve listar o conteudo da pasta campartilhada lá do servidor de arquivos Windows. Ok, agora, vamos desmontar:

# umount /mnt/ntserver

Criando o script de backup do PostgreSQL

Criando a pasta onde ficará armazenado localmente os Dumps

# mkdir /home/bkp_pg

Crie o arquivo chamado bkp.sh

# vim /etc/init.d/bkp.sh

Insira o seguinte conteúdo:

#!/bin/sh
# script que gera backup de bases de dados PostGreSQL
# Referencia: http://www.htmlstaff.org/postgresqlmanual/app-pgdump.html
# Jonas Ferreira - jonas.ferreira@gmail.com - http://jf.eti.br
# C 2010 03 19
#

# VARIAVEIS ##################
export PGPASSWORD=postgres            # Exportando a senha do PostGreSQL para o ambiente (Ubuntu)
vU="postgres"                          # Usuario no PostGreSQL
vP="postgres"                        # Senha
vB=nome_base                             # Base De dados
vBI="/usr/lib/postgresql/8.3/bin/"     # Diretorio raiz dos binarios do PostGreSQL
vR="/home/"                                # Diretorio raiz
vD="bkp_pg/"                     # Destino do Backup
vPre="basePG"                       # Prefixo no nome do arquivo de saida
vE=".dmp"                              # extencao do arquivo de saida
vH="localhost"                      # Host do servidor
vAno=`date +%Y`                     #Ano
vMes=`date +%m`                     #Mes
vDia=`date +%d`                     #Dia
vHor=`date +%H`                     #Hora
vMin=`date +%M`                     #Min
vCod=`date +%N`                     #date [date (GNU coreutils) 5.3.0]
vDat="$vAno$vMes$vDia-$vHor$vMin-$vCod"
vA=$vPre"-"$vB"-"$vDat$vE

# VALIDACAO ##################
if [ -z $vU ]; then
echo "Sem usuario";
exit 0;
fi
if [ -z $vP ]; then
echo "sem senha";
exit 0;
fi
if [ -z $vB ]; then
echo "informe o nome da base de dados";
exit 0;
fi

# BACKUP #####################
echo "# Gerando backup. Aguarde..."
echo "# Destino: $vR$vD"
echo "# Nome do arquivo: $vA"

echo "#################################"

echo "# Compactando backup. Aguarde..."

# Caso queira gerar com inserts (arquivo fica maior)
#$vBI""pg_dump -U $vU -h $vH --inserts $vB | gzip -c > $vR$vD$vA.out.gz

# Caso queira gerar com copy (arquivo fica menor)
$vBI""pg_dump -U $vU -h $vH  $vB | gzip -c > $vR$vD$vA.out.gz

echo "#################################"
echo "# Transferindo o o arquivo para o servidor Windows..."

# Montando a pasta
mount -t smbfs //192.168.0.10/pasta_compartilhada /mnt/ntserver -o username=usuario_de_dominio,password=senha_de_dominio

# copiando o arquivo
cp $vR$vD$vA.out.gz /mnt/ntserver

# desmontando a pasta
umount /mnt/ntserver

# limpando a variavel de ambiente
export PGPASSWORD=
exit

 

Setando permissão de execução no script:

# chmod +x /etc/init.d/bkp.sh

Colocando o script na Crontab:

# crontab -e

Adicione a seguinte linha:

#Realizando Backup do PostgreSQL – Todos os dias a meia noite
00 00 * * * /etc/init.d/bkp.sh

Salve o arquivo e pronto, já pode testar:

# /etc/init.d/bkp.sh

Como saída, você verá algo como:

# Gerando backup. Aguarde…
# Destino: /home/bkp_pg/
# Nome do arquivo: basePG-nome_base-20111116-2012-048760000.dmp
#################################
# Compactando backup. Aguarde…
#################################
# Transferindo o o arquivo para o servidor Windows…

Verifique na pasta de destino se foi criado o arquivo:

# ls /home/bkp_pg

Deve listar o arquivo compactado:

basePG-nome_base-20111116-2012-048760000.dmp.out.gz

Verifique também lá no servidor de arquivo do Windows, na pasta compartilhada, se este mesmo arquivo existi por lá.

Pronto, você acabou de implementar uma solução de Dump de uma base PostgreSQL e transferiu para um servidor de arquivos Windows. Espero que seja útil para você.