Estou tentando configurar um cluster MySQL de alta disponibilidade, usando uma configuração mestre/escravo. Eu quero que o escravo se torne mestre (por exemplo, não somente leitura, mas gravável) se o primeiro nó cair (como nesta pergunta: https://stackoverflow.com/questions/9409111/database-fail-over-in-jboss- fontes de dados )
Então, estou usando uma fonte de dados com drivers Connector/J:
<datasources>
<local-tx-datasource>
<jndi-name>alfresco-datasource</jndi-name>
<connection-url>jdbc:mysql://10.1.2.13,10.1.2.14:3306/alfresco</connection-url>
<driver-class>org.gjt.mm.mysql.Driver</driver-class>
<user-name>alfresco</user-name>
<password>alfresco</password>
<exception-sorter-class-name>
org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter
</exception-sorter-class-name>
<connection-property name="readOnly">false</connection-property>
<failOverReadOnly>false</failOverReadOnly>
<check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
<metadata>
<type-mapping>mySQL</type-mapping>
</metadata>
</local-tx-datasource>
</datasources>
Eu li muitos posts aqui tratando sobre isso, mas ainda não tenho certeza se isso é suficiente (na verdade, tenho certeza que não ;)).
Para testar minha configuração, apenas paro a primeira instância:
service mysql stop
Assim que meu aplicativo (Alfresco, contando com Activiti), que está usando Ibatis, detecta que há uma falha, está me inundando de mensagem dizendo que a conexão foi perdida / recusada, .... Então o failover não funciona.
GRAVE: Error while closing command context
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 1 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.
### The error may exist in org/activiti/db/mapping/entity/Job.xml
### The error may involve org.activiti.engine.impl.persistence.entity.JobEntity.selectNextJobsToExecute_mysql
### The error occurred while executing a query
### SQL: select * from ACT_RU_JOB where (RETRIES_ > 0) and (DUEDATE_ is null or DUEDATE_ <= ?) and (LOCK_OWNER_ is null or LOCK_EXP_TIME_ <= ?)
### Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 1 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:77)
at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:191)
at org.activiti.engine.impl.persistence.entity.JobManager.findNextJobsToExecute(JobManager.java:97)
at org.activiti.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:49)
at org.activiti.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:32)
at org.activiti.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24)
at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:42)
at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40)
at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)
at org.activiti.engine.impl.jobexecutor.JobAcquisitionThread.run(JobAcquisitionThread.java:63)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
[...]
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2552)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3002)
... 45 more
09:54:00,270 WARN [LocalManagedConnectionFactory] Destroying connection that is not valid, due to the following exception: com.mysql.jdbc.JDBC4LoadBalancedMySQLConnection@33dd0edd
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 31 875 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.
[...]
09:54:00,355 WARN [JBossManagedConnectionPool] Destroying connection that could not be successfully matched: org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@34775a91[state=DESTROYED mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@244f22a3 handles=0 lastUse=1339055604851 permit=false trackByTx=false mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@3d01c1a context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@53e4f0ea xaResource=org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource@476b0df4 txSync=null]
09:54:01,702 ERROR [STDERR] Thu Jun 07 09:54:01 CEST 2012 WARN: Connection to primary host failed
EXCEPTION STACK TRACE:
** BEGIN NESTED EXCEPTION **
E assim por diante ...
Eu tive problemas antes, porque meu usuário do servidor de aplicativos (alfresco@10.1.2.11) não foi concedido no banco de dados. Mas por enquanto está resolvido.
Aqui estão meus arquivos de configuração do MySQL (caso eu tenha esquecido algo):
Master (10.1.2.13) [mysqld] user = s-mysql port = 3306 basedir =/app/mysql/current datadir =/app/data/mysql log-error =/app/log/mysql/master log =/app/ log/mysql/query.log
#clustering
server_id =1
log-bin =/app/log/mysql/log-bin/log-bin.master
innodb_flush_log_at_trx_commit=1
sync_binlog =1
skip-slave-start
expire_logs_days=10
binlog-do-db =alfresco
Escravo (10.1.2.14)
[mysqld]
user = s-mysql
port = 3306
basedir =/app/mysql/current
datadir =/app/data/mysql
log-error =/app/log/mysql/slave
log =/app/log/mysql/query.log
# Clustering
server_id =2
log-bin =/app/log/mysql/log-bin/log-bin.slave
expire_logs_days=10
skip-slave-start
relay-log-index =/app/log/mysql/log-relay/relay-log.slave.index
relay-log =/app/log/mysql/log-relay/relay-log.slave
binlog-do-db =alfresco
replicate-do-db =alfresco
Privilégios de escravo
SHOW GRANTS for 'alfresco'@'10.1.2.11';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for alfresco@10.1.2.11 |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'alfresco'@'10.1.2.11' IDENTIFIED BY PASSWORD '*F9F457310EF9364D42AABE6D8C24A21712C000B5' |
| GRANT ALL PRIVILEGES ON `alfresco`.* TO 'alfresco'@'10.1.2.11' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------+
Alguém poderia me dar dicas, para resolver este problema, por favor?