Mysql caindo toda hora

1. Mysql caindo toda hora

felixleonardo
(usa Debian)

Enviado em 09/11/2013 - 08:08h

Bom dia amigos!!!

Estou com um problema ocorrendo em meu servidor MySQL aqui da empresa.

Ele ao iniciar, apresenta o seguinte erro no syslog do Debian:

tail /var/log/syslog
Nov 9 07:44:56 xxxxxxx mysqld: 131109 7:44:56 [Warning] IP address 'xxx.xxx.xxx.xxx' could not be resolved: Name or service not known
Nov 9 07:44:56 xxxxxxx mysqld: 131109 7:44:56 [Warning] IP address 'xxx.xxx.xxx.xxx' could not be resolved: Name or service not known
Nov 9 07:44:56 xxxxxxx mysqld: 131109 7:44:56 [Warning] IP address 'xxx.xxx.xxx.xxx' could not be resolved: Name or service not known
Nov 9 07:44:56 xxxxxxx mysqld: 131109 7:44:56 [Warning] IP address 'xxx.xxx.xxx.xxx' could not be resolved: Name or service not known
Nov 9 07:44:56 xxxxxxx mysqld: 131109 7:44:56 [Warning] IP address 'xxx.xxx.xxx.xxx' could not be resolved: Name or service not known
Nov 9 07:44:56 xxxxxxx mysqld: 131109 7:44:56 [Warning] IP address 'xxx.xxx.xxx.xxx' could not be resolved: Name or service not known
Nov 9 07:44:56 xxxxxxx mysqld: 131109 7:44:56 [Warning] IP address 'xxx.xxx.xxx.xxx' could not be resolved: Name or service not known
Nov 9 07:44:56 xxxxxxx mysqld: 131109 7:44:56 [Warning] IP address 'xxx.xxx.xxx.xxx' could not be resolved: Name or service not known
Nov 9 07:44:56 xxxxxxx mysqld: 131109 7:44:56 [Warning] IP address 'xxx.xxx.xxx.xxx' could not be resolved: Name or service not known
Nov 9 07:45:01 xxxxxxx mysqld: 131109 7:45:01 [Warning] IP address 'xxx.xxx.xxx.xxy' could not be resolved: Name or service not known

No servidor de final xxx, existem várias aplicações Java rodando em Tomcat. Já no servidor de final xxy, há apenas um site rodando. Talvez por isso apareça apenas uma vez no log. Li que acrescentando a linha skip_name_resolve no arquivo my.cnf, porém se faço isso, o mysql não sobe o serviço.

Alterei alguns parâmetros no my.cnf, de acordo com sugestões do mysqltune e melhorou um pouco, mas ainda cai às vezes.

O que seria exatamente esse erro e como corrigi-lo? Meu servidor Mysql é fechado pra fora da empresa. Os IPs que aparecem no log são nossos.

Segue meu my.cnf para ajudá-los. Agradeço desde já a ajuda.

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = xxx.xxx.xxx.xxx
#
# * Fine Tuning
#
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 16
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
max_connections = 2000
table_cache = 2000
thread_concurrency = 16
join_buffer_size = 2M
innodb_buffer_pool_size = 150M
#
# * Query Cache Configuration
#
query_cache_limit = 140M
query_cache_size = 128M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# Here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

Edit: Pra ajudar mais ainda, segue o resultado do mysqltuner rodado logo após reiniciar o MySQL:

># mysqltuner

>> MySQLTuner 1.1.1 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.31-0+wheezy1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 35M (Tables: 191)
[--] Data in InnoDB tables: 134M (Tables: 501)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 509

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 28s (2K q [88.036 qps], 456 conn, TX: 2M, RX: 326K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 326.0M global + 4.6M per thread (3000 max threads)
[!!] Maximum possible memory usage: 13.7G (87% of installed RAM)
[OK] Slow queries: 0% (0/2K)
[OK] Highest usage of available connections: 7% (224/3000)
[OK] Key buffer size / total MyISAM indexes: 16.0M/8.2M
[!!] Key buffer hit rate: 61.1% (18 cached / 7 reads)
[!!] Query cache efficiency: 0.0% (0 cached / 894 selects)
[OK] Query cache prunes per day: 0
[OK] Temporary tables created on disk: 7% (55 on disk / 715 total)
[!!] Thread cache hit rate: 50% (224 created / 456 connections)
[OK] Table cache hit rate: 24% (733 open / 2K opened)
[OK] Open file limit used: 2% (430/15K)
[OK] Table locks acquired immediately: 100% (253 immediate / 253 locks)
[OK] InnoDB data size / buffer pool: 134.5M/150.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
Variables to adjust:
query_cache_limit (> 140M, or use smaller result sets)
thread_cache_size (> 16)

0 0

Quote

2. Re: Mysql caindo toda hora

felixleonardo
(usa Debian)

Enviado em 09/11/2013 - 08:14h

Amigos, me esqueci de um detalhe: ao iniciar ou reiniciar o MySQL, ele já sobe com um erro, conforme mostrado abaixo. É o mesmo erro do syslog. Notem que aparentemente o mysql sobe normalmente, porém fica um processo (no caso deste exemplo, o 4211), que mostra o erro ao iniciar.

> /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld ..
Checking for tables which need an upgrade, are corrupt or were
not closed cleanly..

> ps ax | grep mysql
3823 pts/0 S 0:00 /bin/sh /usr/bin/mysqld_safe
4210 pts/0 Sl 0:02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
4211 pts/0 S 0:00 logger -t mysqld -p daemon.error
4957 pts/0 S+ 0:00 grep mysql

0 0

Quote

3. Re: Mysql caindo toda hora

removido
(usa Nenhuma)

Enviado em 09/11/2013 - 10:47h

Executa ai:

# mysqlcheck -A -e -u root -p

http://www.yeslinux.com.br/blog/?p=304

0 0

Quote

4. Re: Mysql caindo toda hora

felixleonardo
(usa Debian)

Enviado em 10/11/2013 - 09:21h

Caro amigo amarildosertorio, obrigado pela ajuda.

Executei o comando, mas todas as tabelas deram OK. Só que me recordo de ter rodado um Analisar tabelas, pelo phpmyadmin e cheguei a ver uma tabela corrompida, mas não me lembro qual. Mas isso eu posso fazer de novo. Se eu der um repaiur nessa tabela pelo prompt do mysql, você acha que resolve?

E tem outra coisa esquisita: utilizei o script abaixo para tentar desfragmentar as tabelas, pois o mysqltune retorna mais de 500 tabelas fragmentadas. O script retorna a grande maioria das tabelas com status OK, mas para algumas (não são poucas) ele me mostra a mensagem abaixo:

nome_da_tabela optimize note Table does not support optimize, doing recreate + analyze instead

Segue o conteúdo do script que achei pronto na net:

#!/bin/bash

### login e senha (de preferencia root para ter privilegios em todos os bancos de dados) ###
MYUSER="root"
MYPASS="password"

### faz um laco passando por todos os bancos de dados do mysql local ###
DBS="$(mysql -u$MYUSER -p$MYPASS -Bse 'SHOW DATABASES')"
for db in $DBS
do
#verifica e conserta todas as tabelas do db $db
mysqlcheck -u$MYUSER -p$MYPASS --auto-repair $db;

#otimiza tabela por tabela de todos os bancos de dados do mysql local
for i in `echo "SHOW TABLES" | mysql -u $MYUSER -p$MYPASS $db | grep -v Tables_in_`;
do
mysql -u$MYUSER -p$MYPASS -Bse "OPTIMIZE TABLE ${db}.${i}";
done
done

Eu pesquisei isso e me disseram que é problema de permissão do usuário, mas rodei como root.

Você acha que essas tabelas podem estar com problema? E se estiverem, como repará-las se dá sempre esse erro?

Se quiser, rodo o tune novamente e posto o resultado. Minhas configurações estão OK?

Obrigado por sua ajuda.

Abraços!

0 0

Quote

5. Re: Mysql caindo toda hora

evesbruno
(usa Debian)

Enviado em 18/11/2013 - 09:40h

tenta mudar isso aqui

query_cache_limit = 16M

cache_size ta em 128M

limit tem que ser menor.... pode ser umas da causa dos problema

verifica espaço da /var onde foi instalado mysql

posta ai pra nois configuracao da maquina que ta rodando esse mysql
cpu
mem hd
...

0 0

Quote