Mysql: Dicas, truques e resolucão de problemas

Introducão

Pagina dedicada para solucão dos problemas mais comuns encontrados com o banco de dados MySQL para ambiente Linux, para cada caso deverá ser colocado o ambiente onde o problema foi identificado e a solucão executada.

Criando um banco de dados

Exemplo para criar um database e dar permissões para um dado usuário na linha de comandos, usando a continuacão em múltiplas linhas

mysql -uroot -pSENHA <<EOF
CREATE DATABASE bancolegal /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
GRANT ALL PRIVILEGES ON bancolegal.* TO usuario@localhost IDENTIFIED BY 'senha';
quit
EOF

Usando um usuario

CREATE USER 'calls'@'localhost' IDENTIFIED BY 'atenda';
GRANT ALL PRIVILEGES ON *.* TO 'calls'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Usando o mysqldump

Comando para exportar um único database

mysqldump --user=NOME-USUARIO --password=SENHA NOME-BANCO --host=localhost > /tmp/NOME-BANCO.dmp

Script para exportar vários bancos em arquivos separados

User=root
Password=cancella

for Database in mail mysar mysql openfire
do
   mysqldump --user=${User} --password=${Password} ${Database} --host=localhost > ${Database}.dmp
done

Importando um arquivo gerado pelo mysqldump

Para realizar via linha de comandos

# mysql -uUSUARIOr -pSENHA -h localhost BANCO < /tmp/ARQUIVO.dmp 

Usando o comando SURCE através de uma sessão aberta com o cliente mysql

mysql> use BANCO
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A 

Database changed
mysql> SOURCE /tmp/ARQUIVO.dmp

Alterando a senha de usuários em tabelas

Alterar um valor de tabela onde contém senha criptografada MD5, para uma nova senha.

mysql> select * from TABELA;
+----+--------------------------+----------------------------------+---------------+---------------------+---------------------+----------+
| id | email                    | senha                            | nome          | created             | modified            | excluido |
+----+--------------------------+----------------------------------+---------------+---------------------+---------------------+----------+
|  1 | usuario@yahoo.com.br     | b47548810d98a868ea7cdaa0c5c78553 | usuario       | 2013-01-14 15:53:15 | 0000-00-00 00:00:00 | n        |
+----+--------------------------+----------------------------------+---------------+---------------------+---------------------+----------+

mysql> UPDATE TABELA set senha = MD5('novasenha') where email = 'usuario@yahoo.com.br'; 

Removendo a senha da conta root do MySQL

Efetue login no banco com uma conta com privilégios

mysql> SET PASSWORD FOR root@localhost=PASSWORD();

Erro Out of memory

Erro gerado ao importar um arquivo de aproximadamente 20Gb

mysql: Out of memory (Needed 1073743928 bytes)
mysql: Out of memory (Needed 1073739832 bytes)

Usando o mysqlimport para importar dados de um arquivo externo

Utilitario para improtar arquivo externo para o MySQL

Configurando o my.cnf para gerar log de transações e erros

Criando um diretório específico para os logs do mysql

# mkdir /var/log/mysql
# touch /var/log/mysql/mysqld-error.log
# touch /var/log/mysql/mysqld.log
# chown -R mysql:mysql /var/log/mysql

• /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log=/var/log/mysql/mysqld.log

[mysqld_safe]
log-error=/var/log/mysql/mysqld-error.log
pid-file=/var/run/mysqld/mysqld.pid

Reinicie os serviços

# service mysqld restat
# ls -l /var/log/mysql

Quebrando a senha de root

Testado em um ambiente Fedora Core 10 e CentOS 5.8

# service mysqld stop
# mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root
mysql> use mysql;
mysql> UPDATE user SET Password=PASSWORD("*********") WHERE User="root";
mysql> quit

Reinicie o serviço

# service mysqld restart

Reparando tabelas corrompidas

Erro apresentado ao exportar um database

mysqldump: Error 1194: Table 'maillog' is marked as crashed and should be repaired when dumping table `maillog` at row: 930047

Procedimento executado para verificar a tabela - em produção

# mysqlcheck -uroot mailscanner maillog
mailscanner.maillog
warning  : 6 clients are using or haven't closed the table properly
error    : Wrong bytesec: 0-0-0 at linkstart: 1602204088
error    : Corrupt

Procedimento executado para reparar a tabela - em produção

# mysqlcheck -uroot --repair mailscanner maillog
mailscanner.maillog
info     : Wrong bytesec:   0-  0-  0 at 1602204088; Skipped
warning  : Number of rows changed from 1045090 to 1045089
status   : OK

Procedimento executado para otimizar a tabela em produção

# mysqlcheck -uroot --optimize mailscanner maillog
mailscanner.maillog 

Fonte de referência

http://www.databasejournal.com/features/mysql/article.php/3300511/Repairing-Database-Corruption-in-MySQL.htm

Expurgando dados de tabelas - Dias corridos

Configuração do cron

0 22 * * * /usr/bin/mysql mailscanner < /opt/cit/work/utils/clean_maillog.sql >> /opt/cit/work/utils/clean_maillog.log 2>&1

Código do script SQL

delete low_priority quick from maillog where timestamp < (now() - interval 90 day);
optimize table maillog;

Script Shell

#!/bin/bash

mysql -uroot mailscanner <<!
delete low_priority quick from maillog where timestamp < (now() - interval 90 day);
optimize table maillog;
!

Danton Cancella (31-8456-6829) (discussão)